Complex Queries

SQL provides powerful ways to summarize data in terms of row counts, computed averages, sums and so on. Aggregate functions include statistical functions like min, max, sddev and so on. These functions are used to calculate totals over entire columns or groups of rows. For example, let us say that the Book Store Owner wants to know the total number of books sold that cost over $100. The following query would give him this information. The AS keyword provides an alternate name or ALIAS for the calculated value. Regular columns may also be renamed temporarily using the AS keyword. This query returns '12'

SELECT SUM(quantity) AS TotalBooks
from orderdetails
WHERE unitprice>= 100

To calculate the average price of all books sold that cost over $100, the owner may use the following query (this returns 113.5). The round function is used to round the returned average to two decimal places:

SELECT round(avg(unitprice),2)
AS AveragePrice
from orderdetails
WHERE unitprice >= 100

The GROUP BY clause offers a way to group date in relevant columns to calculate sums, count, averages etc. by group. For example, the following command returns a subject-wise average book price:

SELECT subjectid,round(avg(price),2) AS AveragePrice
from book
GROUP BY subjectid
order by subjectid

Following is a table that lists out common SQL aggregate functions. Try out the examples in in a Query window to see the results

Function	Example	Explanation
AVG(column)	SELECT AVG(price) from book where pubdate <='2000'	The average price of books published in or after 2000
COUNT(column)	SELECT Count(bookid) from book where subjectid = 1	The total number of books on subject 1.
MAX(column)	SELECT MAX(quantity) from orderdetails	The largest order for a single book
MIN(column)	SELECT MIN(price) from book	The lowest priced book
SUM(column)	SELECT SUM(unitprice*quantity) from orderdetails group by orderid	The value of each order before application of discount

 

SQL allows nested queries in which the result of one query is obtained via a sub-query.

• The following query returns all books that belong to subjects that come under the 'Dramas and Plays' category with ID 1.

  select title, price from book where subjectid in
      (select subjectid from subject where categoryid = 1)

• The following query returns details of all orders made during 2006.

  select * from orderdetails where orderid in
      (select orderid from custorder where orderdate >= '1-Jan-2006')

The any and all keywords are used in subqueries to evaluate query results. For example, the following subquery returns the title and price of books that cost more than all the books with subject = 1.

select title, price from book where price
> all (select price from book where subjectid = 1)

Let us consider the following query: Give me the authors and subjects of all the books under category fiction

How do we get this information?

• The book table contains book titles, the author and subject ids but no information about categories
• The category table simply contains category description and id.
• The author table contains author information alone.
• The subject table contains information about categories

To get this information using SQL, we JOIN the four concerned tables together. JOIN can be confusing because the actual JOIN clause is not really necessary while writing JOIN queries. However, once we have the fundamentals of combining tables straight, using JOIN becomes easy and intuitive. First, let us look at the query that will answer the above question. Partial results follow the query; 64 rows are returned in all:

SELECT title, subjectdesc,
lastname+', '+firstname AS AUTHOR
FROM book, author, subject, category
WHERE category.categorydesc like 'Fiction'
AND book.authorid = author.authorid
AND book.subjectid = subject.subjectid
AND subject.categoryid = category.categoryid

 

What was the problem we had? The table that we needed information from contained only IDs and not names. The filter provided was a string (Fiction) and the output sought was also string data and descriptions. The authorID-to-name mapping is in the author table while the subjectid-to-name mapping is in the subject table. A good understanding of the concept of foreign keys is essential to writing JOIN queries in SQL as most common JOINs are performed using primary key-foreign key pairs. When a primary key of a table appears as a column in any other table, it is called a foreign key. In the book table, we have two foreign keys: subjectid and authorid. To hook up tables, all we have to do is hookup the foreign key in one table with the corresponding primary key in the other table after the WHERE clause. This is exactly what the following parts of the command did

AND book.authorid = author.authorid
AND book.subjectid = subject.subjectid
AND subject.categoryid = category.categoryid 

In each case, we used the syntax Table Name.Column Name in order to not confuse the database engine - the names of the columns are the same, so this is the only way to distinguish between them.

Also, we listed all concerned tables (book, author, subject, and category) after the FROM clause with a comma between each table's name. The hooking up of the foreign keys with primary keys and the listing of all the tables’ names constitute the bulk of the join. What is left over is simply setting the input and getting the output.

SELECT title, subjectdesc,
lastname+', '+firstname AS AUTHOR 

The above statement gets us the output we want. We combine the lastname of the author with the firstname and a comma in between through the '+' concatenation operator and assign the alias 'AUTHOR' to this field. Interestingly, specifying the table name before the column name is unnecessary here because there is only one column called 'title' and one column called 'lastname' in all four tables. The following line indicates our input condition:

WHERE category.categorydesc LIKE 'Fiction' 

Here are a few more joins that you can try out:

• SELECT title, subjectdesc,
      lastname+', '+firstname AS AUTHOR
      FROM book, author, subject, category
      WHERE category.categorydesc like 'Fiction'
      AND book.authorid = author.authorid
      AND book.subjectid = subject.subjectid
      AND subject.categoryid = category.categoryid
      ORDER BY title

  All this command does is perform the same JOIN we looked at earlier. Additionally, it sorts the result by title.
  
  

• SELECT custorder.orderid, lastname, firstname,
      sum(unitprice*quantity) as totalprice
      from customer, custorder,orderdetails
      where customer.customerid = custorder.customerid
      and custorder.orderid = orderdetails.orderid
      group by custorder.orderid, customer.lastname, customer.firstname
      order by custorder.orderid

  
  
  The above query returns the value of each customer's total order with the customer name.
  
  

• SELECT lastname, firstname, orderdate, orderid from
      custorder, customer
      where custorder.customerid = customer.customerid
      order by orderdate

  
  
  Retrieves orders with dates for each customer
  
  

When the JOIN keyword is left out, SQL performs an INNER JOIN by default. That is, for each hooked up column, it brings up only the rows of information that match conditions in both tables. A LEFT JOIN brings up all the rows of the table on the left hand side of the LEFT JOIN keyword and only rows that where both fields are equal from the table on the right, while a RIGHT JOIN brings up all the rows of the table on the right hand side of the RIGHT JOIN keyword and only rows where both fields are equal from the table on the left. Let us use an example outside our database to understand the differences between the three types of joins.

Table Authors:

AuthorID	Name
01	Sample Author 1
02	Sample Author 2
03	Sample Author 3
04	Sample Author 4

Table SubjectAuthors:

SubjectID	SubjectName	AuthorID
1	Horror	04
2	Children	04
3	Western	02
4	Adventure

 

Join Types

INNER
Example:
SELECT Authors.Name, SubjectAuthors.SubjectName
FROM Authors, SubjectAuthors
WHERE Authors.AuthorID = SubjectAuthors.AuthorID

Output

Name	SubjectName
Sample Author 4	Horror
Sample Author 4	Children
Sample Author 2	Western

LEFT

Example:
SELECT Authors.Name, SubjectAuthors.SubjectName
FROM Authors LEFT JOIN SubjectAuthors
WHERE Authors.AuthorID = SubjectAuthors.AuthorID

Output:

Name	SubjectName
Sample Author 1
Sample Author 2	Western
Sample Author 3
Sample Author 4	Horror
Sample Author 4	Children

RIGHT

Example:
SELECT Authors.Name, SubjectAuthors.SubjectName
FROM Authors RIGHT JOIN SubjectAuthors
WHERE Authors.AuthorID = SubjectAuthors.AuthorID

Output:

Name	Product
Sample Author 2	Western
Sample Author 4	Horror
Sample Author 4	Children
	Adventure