CampusLearn
← CSC 213 Database Systems

Structured Query Language (SQL) and Complex Queries

Notes

Overview of SQL Structured Query Language (SQL) is the standard declarative language for interacting with relational database management systems. Declarative programming allows developers to define the desired data set rather than detailing algorithmic instructions for data retrieval. SQL comprises several sub-languages, primarily Data Definition Language (DDL) for creating and modifying database structures, and Data Manipulation Language (DML) for querying, inserting, updating, and deleting records. Core Data Manipulation Operations The foundational query structure revolves around the SELECT, FROM, and WHERE clauses. Relational selection evaluates conditional criteria to filter rows, while projection isolates specific columns. Boolean operators such as AND, OR, and NOT allow compound filtering. Ordering results relies on the ORDER BY clause, defaulting to ascending sequence unless explicitly instructed. Joins and Aggregate Functions Normalized databases spread distinct entities across separate tables, making relational joins indispensable for analytical reporting. An INNER JOIN evaluates equality predicates between related keys, returning only matching rows across both relations. LEFT OUTER JOINs preserve all records from the left relation while returning matching or null-filled records from the right relation. Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize column values across sets of records. Used in conjunction with GROUP BY, these functions enable multi-level aggregation, while the HAVING clause filters grouped data based on aggregate conditions. Practical example A retail chain like Quickmart Kenya maintains tables for Branches, Products, and DailySales to track revenue across counties. Branches table schema: BranchID, BranchName, County DailySales table schema: SaleID, BranchID, ProductID, UnitsSold, SaleDate, TotalAmount To compute the total sales revenue per branch in Kiambu County where aggregate branch sales exceed 100,000 Kenyan Shillings, the query is written as follows: SELECT b.BranchName, SUM(s.TotalAmount) AS TotalRevenue FROM Branches b INNER JOIN DailySales s ON b.BranchID = s.BranchID WHERE b.County = 'Kiambu' GROUP BY b.BranchName HAVING SUM(s.TotalAmount) > 100000 ORDER BY TotalRevenue DESC; This query executes an inner join across primary and foreign keys, filters rows by county, aggregates total revenue per branch name, rejects aggregated totals below the threshold, and outputs the highest performing branches first.

Quiz

1. In the Quickmart SQL example, why is the WHERE clause evaluated before the HAVING clause?

2. What type of join returns all tuples from the left relation and only matching tuples from the right relation?

3. Which SQL clause is specifically used to filter records after aggregate groupings have already been formed?

Sign in to submit and securely grade your quiz.