CompleteSyntax for SELECT Statements
(More on Joins)
Joining More Than Two Tables
- List the title(s) of book(s) written by an author with the last name Adams.
Looking at the schema of the bookstore database, you find titles are available in the Books table, and author names are available in the Author table. Apparently, you need to include just these two tables in the FROM clause. In reality, you would need a third table, BookAuthor, which keeps track of mapping from books to the corresponding authors. Without this link, you will generate too many faulty results. Either of the following two queries may be used to answer this question.
select title
from books b, bookauthor ba, author a
where b.isbn = ba.isbn
and ba.authorid = a.authorid
and lname = ‘Adams’;
select title
from (books b join bookauthor ba
on (b.isbn = ba.isbn))
join author a on (ba.authorid = a.authorid)
where lname = ‘Adams’;
Although not necessary, parentheses are used in the second query for increasing readability.
- List the customer numbers and names of all individuals who have purchased books in the Fitness category.
Use this query as an exercise.
Non-Equality Joins
- What gift will a customer who orders the book Shortest Poems receive?
It’s not hard to see that this time we need to join the Books and Promotion tables. However, the two tables are not related through a PK-FK pair. Instead, we need to see which range, as defined with minimum and maximum retail prices, does the retail price falls into to determine the corresponding gift. In SQL, this can be expressed as
select gift
from books join promotion
on retail between minretail and maxretail
where title = ‘Shortest Poems’;
- As an exercise, write the same query without using the join syntax. Try not to use the between … and … syntax in the new query.
Self Joins
- Who are the customers that have referred another customer (and thus qualify for getting a discount certificate for a future purchase)?
Similar situations include the case that you need to list employees and their direct supervisors. You need to join two copies of the same table. To pretend they are two different tables, you need to use an alias for each copy to resolve ambiguity: the two copies share exactly the same column names!
select c_ing.firstname,c_ing.lastname
from customers c_ing join customers c_ed
on c_ing.customer# = c_ed.referred;
- As an exercise, write a query that lists all employees as stored in the Employees table who report directly to the president.
This table belongs to another database. Use the describe (or desc) command to check the schema of that table before write this query.
(The Complete Syntax)
The Group (or Total) Functions
- Determine how many books are in the Cooking category.
Obviously, we are expecting a single value as the answer to this question. Logically, we need first to screen out books in the Cooking category from all books, and then apply the COUNT function to get the answer.
select count(*)
from books
where category = ‘Cooking’;
- Determine the publishing date of the most recently published book.
- List book categories and average retail prices by category.
select category, avg(retail)
from books
group by category;
- List book categories and average retail prices by category. Omit those categories with an average retail price lower than $25.
select category, avg(retail)
from books
group by category
having avg(retail) >= 25;
With the two new clauses we learned today, GROUP BY and HAVING, the total number of clauses that may appear in a SELECT statement is 6: two mandatory and four optional.
SELECT [distinct|unique] {*|column|expr [as alias, ...]}
FROM table|view [alias, ...]
[WHERE condition]
[GROUP BY group_by_column|expr]
[HAVING group_condition]
[ORDER BY column|location];
The clauses should appear in the right order as demonstrated above. Here is an example:
select category, avg(retail)
from books
where pubdate > ’01-JUL-01’
group by category
having avg(retail) >= 25
order by avg(retail);
Can you explain what this query is asking for? Comparing the results from the last two queries may give you some clues.
SQL> select category, avg(retail)2 from books
3 group by category
4 having avg(retail) >= 25;
CATEGORY AVG(RETAIL)
------
BUSINESS 31.95
CHILDREN 34.45
COMPUTER 52.85
FAMILY LIFE 55.975
FITNESS 30.95
LITERATURE 39.95
SELF HELP 29.95 / SQL> select category, avg(retail)
2 from books
3 where pubdate > '01-JUL-01'
4 group by category
5 having avg(retail) >= 25
6 order by avg(retail);
CATEGORY AVG(RETAIL)
------
SELF HELP 29.95
CHILDREN 34.45
COMPUTER 65.225