This week we learned about grouping results (summaries) and subqueries before moving on to database design.
Summaries
When creating summary queries, you use one or more aggregate functions: AVG, SUM, MMIN, MAX, COUNT (or COUNT (*) ). Any SELECT clause having an aggregate function CANNOT have a non-aggregate column UNLESS it is used to GROUP results in a GROUP BY clause. As an example, you can display a SUM OF INVOICES (aggregate column) GROUPED BY vendor name (non-aggregate column).
Syntax is very important when crafting summary queries. The GROUP BY clause needs to occur before the search condition in the HAVING clause and the order by list in the ORDER BY clause to produce the intended results of a summary query.
Subqueries
Most JOINs can be restated as a subquery and vice versa.
Subqueries can be coded in an outer SELECT statement in a:
- WHERE clause as a search condition
- HAVING clause as a search condition
- FROM clause as a table specification
- SELECT clause as a column specification
Subqueries can return a:
- single value (generally used in WHERE and HAVING)
- list of values, i.e. one column (generally used the IN of a WHERE / HAVING clause or in a SELECT clause
- table of values, i.e. multiple columns (generally used in FROM)
JOINs versus Subqueries
| JOINS | SUBQUERIES |
|---|---|
| SELECT clause of JOIN can include columns from both tables. | Can pass an aggregate value to main query |
| more intuitive when existing relationships between tables (primary and foreign keys) | more intuitive when forming an ad hoc relationship between tables |
| long/complex queries sometimes easier to code/read |
- both used for queries that interact with 2+ tables
- usually they translate back and forth
Database Design
An important aspect of database design is normalization–separating data in a data structures to separate and related tables to reduce data redundancy. However, in the real world, databases are not completely normalized. Out of the seven normal forms outlined by the Murach text, only the first, second, and third normal forms are generally used in the real world.
Below is the first draft of my team’s database design for a system that will be able to give users the ability to search for a teacher’s schedule (the classes they teach, where they teach them, and when they teach them).
