Querying with SQL
SQL is the standard language for working with relational databases. A few clear statements can retrieve, combine, and summarise millions of rows.
What SQL is
SQL (Structured Query Language) was developed at IBM in the 1970s and became an international standard. It is declarative: you state what data you want, and the database's query optimiser chooses how to get it. Nearly every relational database uses SQL, with small differences between products.
SELECT basics
SELECT retrieves data. SELECT name, year FROM students returns the name and year columns for every row; SELECT * returns all columns. WHERE filters rows: SELECT name FROM students WHERE year = 2 returns only second-year students. Conditions can combine with AND and OR.
Sorting and limiting
ORDER BY sorts results, such as ORDER BY name for alphabetical order or ORDER BY score DESC for highest first. Many databases support LIMIT to return only the first few rows, such as the top 10 scores. DISTINCT removes duplicate rows from a result.
Aggregates and grouping
Aggregate functions summarise many rows: COUNT, SUM, AVG, MIN, and MAX. GROUP BY splits rows into groups and applies aggregates to each: SELECT year, COUNT(*) FROM students GROUP BY year counts students in each year. HAVING filters groups, as WHERE filters rows, such as keeping only years with more than 100 students.
Joins
A JOIN combines rows from tables using related columns. An inner join returns only rows with matches in both tables, such as students who have enrolments. A left join keeps every row of the first table even when there is no match, filling missing values with NULL, which is useful for finding students with no enrolments at all.
Changing data
INSERT adds rows: INSERT INTO students (id, name, year) VALUES (7, 'Ana', 1). UPDATE changes rows: UPDATE students SET year = 2 WHERE id = 7. DELETE removes rows: DELETE FROM students WHERE id = 7. Forgetting the WHERE clause in an UPDATE or DELETE affects every row, a classic and costly mistake.
Defining structure
CREATE TABLE defines a new table, its columns, their types, and constraints such as PRIMARY KEY, NOT NULL, and UNIQUE. ALTER TABLE changes a table's structure, and DROP TABLE removes it. Constraints let the database itself reject invalid data, such as a missing name or a duplicate ID.
Worked example
To find the average score in each course, highest first, for courses with at least 5 students: SELECT course_id, AVG(score) AS avg_score FROM results GROUP BY course_id HAVING COUNT(*) >= 5 ORDER BY avg_score DESC. The database groups results by course, drops small groups, averages each, and sorts the averages.
Key takeaways
SQL is the declarative standard language for relational databases. SELECT retrieves columns; WHERE filters rows; ORDER BY sorts; LIMIT and DISTINCT trim results. COUNT, SUM, AVG, MIN, and MAX summarise; GROUP BY groups; HAVING filters groups. Inner joins keep matches; left joins keep all rows of the first table. INSERT, UPDATE, and DELETE change data, and missing WHERE clauses are dangerous. CREATE TABLE defines structure and constraints.
Flashcard
Structured Query Language
Check your understanding
Which SQL clause filters groups after aggregation?