CPSC 203 Tutorial

1. CPSC 203 Tutorial Xin 2010-11-01

2. SQL - SELECT • Syntax • SELECT field1 AS title1, field2 AS title2, ... • FROM table1, table2 • WHERE conditions • Make a query that returns all records from a table • Create a query that shows (1) title name and (2) type from bktblTitles • Create a query that displays (1) title name along with (2) book’s publisher and (3) its author’s last name.

3. SQL - WHERE clause • Functions • Set conditions for retrieved records • Similar to criteria in Design View • Build relationships between tables • Practice • Create a query that returns the titles of “biography” books • Create a query that displays (1) title name along with (2) book’s publisher and (3) its author’s last name. Ensure that each record is correct. • Create a query that displays (1) book title ID, (2) book title name, (3) publisher’s name, and (4) author’s last name. Only show California published entries.

4. SQL – Aggregate queries • Aggregate functions • Avg, count, min, max, ... • Practices • Create a query to compute the total and average sales • GROUP BY clause • Practices • Modify the just created query, so that it shows the total and average sales of books of each type • HAVING clause • Set conditions to results of aggregate functions • In comparison, WHERE  individual records • Must work with GROUP BY • Example • SELECT type, AVG(sales) AS AvgOfsales • FROM bktblTitles • WHERE pages>100 • GROUP BY bktblTitles.type • HAVING SUM(sales)>100000;

5. SQL – Aggregate queries • Practices • Create a query that displays the # of books each publisher has published. • Modify the query, so that it includes records of books with authors living in “NY”. Only groups of records with page numbers of titles greater than 100.