Overview
This lecture recaps essential SQL query features and relational algebra operations using examples. The focus is on practicing SQL syntax and understanding query logic in relational databases.
SQL Query Structure
- SELECT-FROM-WHERE is the basic SQL structure, supporting projection (SELECT) and selection (WHERE).
- By default, SELECT returns a multiset (duplicates allowed), use DISTINCT to remove duplicates.
- The FROM clause specifies tables (relations) to query.
- The WHERE clause sets selection conditions using boolean operators.
Advanced Query Operations
- Cartesian product combines all tuples from two tables; can be restricted by join conditions.
- Use table aliases (AS or just a short name) for clarity, especially with self-joins or complex queries.
- The IN operator simplifies multiple OR conditions in WHERE clauses.
- Parentheses ensure correct evaluation order in complex conditions with AND/OR.
String Operations and Sorting
- Pattern matching with LIKE uses underscores (_) for single characters and % for wildcards.
- ORDER BY sorts results by specified attributes; ASC (default) for ascending, DESC for descending.
- Multiple columns in ORDER BY prioritize sorting order.
Set Operations
- UNION merges results from two queries, removing duplicates (use UNION ALL to keep them).
- INTERSECT returns rows common to both queries.
- EXCEPT (or MINUS) returns rows from the first query not in the second.
Aggregation and Grouping
- GROUP BY collects rows by specified attribute(s) for aggregation.
- Aggregation functions: AVG (average), MIN (minimum), MAX (maximum), SUM (total), COUNT (row count).
- HAVING filters grouped results (not shown but implied).
- Aggregation supports queries like average capacity per building or sum of course credits per department.
Key Terms & Definitions
- Relational Algebra — Formal foundation of relational database queries, including selection, projection, join, etc.
- Multiset — A set that allows duplicate elements.
- Alias — An alternate name for a table or column in a query.
- Aggregation — Calculation over a set of rows, e.g., SUM, AVG.
- Set Operations — SQL commands for combining results from multiple queries: UNION, INTERSECT, EXCEPT.
Action Items / Next Steps
- Practice writing and executing SQL queries covering SELECT-FROM-WHERE, set operations, and aggregation.
- Work on provided tutorials and practice problems for better understanding.
- Review the university database schema and sample data as used in examples.