More on SQL

Selecting from multiple tables

When selecting from two or more tables, typically you will need to explicitly define the columns you want to select by prepending the column name with the table name.

Example: Suppose you have two tables as follows:

To select from both tables, we would do this...

 SELECT Manufact.ManName, Car.CarName FROM Car, Manufact 

There are several ways to select data from multiple tables. The most common way to select data from multiple tables is to set common fields equal to each other.

  SELECT Manufact.ManName, Car.CarName FROM Car, Manufact WHERE Manufact.ManID = Car.ManID; 

Other ways to select data from multiple tables include using the JOIN command. An example is below:

 SELECT Manufact.ManName, Car.CarName FROM Car INNER JOIN Manufact ON Car.ManID = Manufact.ManID; 

A good online resource for the JOIN command can be found here: http://www.w3schools.com/sql/sql_join.asp

AS syntax

In SQL you can us the AS keyword on your SQL statment to generate a synonym. This can be refered to later in your SQL query.

 SELECT userTable.userName AS usr, userTable.password AS pwd FROM userTable WHERE usr='userName'; 

ORDER BY

Order By can be used to sort the result set on any column (multiple columns are allowed).

 SELECT Manufact.ManName AS ManName, Car.CarName AS CarName <br />FROM Car, Manufact WHERE Manufact.ManID = Car.ManID ORDER BY ManName, CarName; 

You can add the DESC to the ORDER BY clause to reverse the sort order

 SELECT Manufact.ManName AS ManName, Car.CarName AS CarName <br />FROM Car, Manufact WHERE Manufact.ManID = Car.ManID ORDER BY ManName, CarName DESC; 

COUNT, SUM, and GROUP BY

To count the number of rows, you use the count() clause.

 SELECT COUNT(Car.CarName) AS CarName FROM Car; 

SUM will total the values in a number (INT, BIGINT, MEDIUMINT, TINYINT) field.

 SELECT SUM(books.totalPrice) AS totalPriceSum FROM Books; 

If you are selecting more than one column you may need to tell mySQL how to group the results using the GROUP BY clause.

 SELECT SUM(books.totalPrice) AS totalPriceSum, customerName FROM Books GROUP BY customerName