Help regarding databases

Username99

Hi, I want to start learning a bit about databases. I have some Java, HTML and CSS experience and have just started to learn some SQL (when I say just started I mean I watched a few videos on Youtube last night).

I understand the basic concept of a database but as this stage I have no clue how you would call methods to retrieve the data from your database etc...

To give an example lets say that you have a small website and a database which stores the login details for all the registered members of your website and you wanted weekly analysis on the number of registered members who visited your website more than once in the week. How would you go about achieving this.

I know this is a very general question and there are more than likely a million ways to answer it but i'm just looking to build up an image around the use of databases.

Procasinator

If it's an existing database, you would check the schema and see if the information you want can be derived from the database. To achieve the one you have listed, for instance, you'd want to know whether the logins are persisted in the database. If not, you'd either be out of luck or need to corroborate with another data source (logs, analytics, e.t.c).

This information would not be automatically stored in the database. The only things that are stored in the database are things you explicitly decide to store.

MagicMarker

Well, assuming you have all the info required, and you only wanted the number a members who logged in more than once in any given week. It'd be something like this...


SELECT
COUNT(member_id) as Count_of_Members

FROM
(
SELECT
member_id,
COUNT(LoginDate) as Count_of_Login
FROM
LoginTable
WHERE
Login_Date >= @FromDate AND Login_Date < ToDate
) SubQ

WHERE
SubQ.Count_of_Login > 1


So the above query would give you the number of members who logged in more than once within the date parameters you set. No doubt there's a better way, I'm not expert, but this is an example of how to do it.

Procasinator

MagicMarker, to me, that query looks like it wouldn't work, unless you changed the subquery to be a group by on member_id.

MagicMarker

You are indeed correct.


SELECT
COUNT(member_id) as Count_of_Members

FROM
(
SELECT
member_id,
COUNT(LoginDate) as Count_of_Login
FROM
LoginTable
WHERE
Login_Date >= @FromDate AND Login_Date < ToDate
GROUP BY
member_id
) SubQ

WHERE
SubQ.Count_of_Login > 1

Stainless_Steel

Why the sub query? How about;

Select count(LoginDate)
From LoginTable
Where
Count(logindate) > 1

Procasinator

Stainless_Steel wrote: »

Why the sub query? How about;

Select count(LoginDate)
From LoginTable
Where
Count(logindate) > 1

That would give you the total number of logins, as long as there has been more than one login.

That doesn't really apply to the OPs question.

oscarBravo

Maybe something along the lines of:

SELECT user, COUNT(*) 
FROM logins 
GROUP BY user 
HAVING COUNT(*) > 1;

It's hard to know exactly how to do it without knowing the database schema.

Stiochkov

Databases is an incredibly dense subject. Even for a basic website, you need to have an understanding of schemas, tables, primary keys, foreign keys, associations, relationships and integrity. That is only the tip of the iceberg from a conceptual standpoint.

When you have an understanding of those topics, you then need to learn to practically apply that knowledge using a query language like MySQL (like described in the above posts). SQL also requires knowledge of a server side language that you can embed your SQL commands in (Ruby, PHP).

A reputable book is a good place to start, as well as video courses like those available at Lynda.com.

Media999

db4free.net for a start. Really handy resource.

Then just mess around with java JDBC or PHP and it will eventually click.

onemorechance

A website and a database.

Likely the most common option is a PHP website and a MySQL database.

Both are free to use software; likely the most widely and cheaply supported technologies by website hosting companies and are one of the easiest to set-up on a home pc.

Windows, Linux, Mac OS X.

MySQL is your DBMS, DatabaseManagementSystem. This software allows you to create, query and otherwsie manage multiple databases. Free software is available to allow you to create, query and otherwise use and manage databases.

You will have the option to do this using sql statements and variants of sql or using a graphical user interfaces. A DBMS can offer the option of running queries based on certain events, such as a certain date every week.

To intergrate a database with a website, PHP will allow you to both create webpages and allow you to create SQL queries which you can use to create, query etc. your databases.

W3Schools website tutorials will have you up and running with PHP and MySQL in no time. You could use PHP to trigger a weekly event to query a db and create a report from the query result, then email it, store it as a file etc.

You can of course try other combinations, such as Java / JSP and MySQL, PHP and PostgreSQL etc.