Difficulty: Beginner | Prerequisites: Basic understanding of relational database concepts (Ch. 1–4)
This chapter introduces SQL (Structured Query Language), the standard language used to create, manage and query relational databases. It covers the split between DDL (defining structures) and DML (manipulating data), the anatomy of a SELECT statement, and the clauses that filter, group and sort results. If you understand what a relational database is and can identify tables, rows and columns, you are ready for this material.
SQL is the universal language for talking to relational databases. DDL commands (CREATE SCHEMA, CREATE TABLE) build the structure; DML commands (SELECT, INSERT, UPDATE, DELETE) work with the data inside it. The SELECT statement is the workhorse: its clauses (WHERE, GROUP BY, HAVING, ORDER BY) let you filter rows, group results, apply conditions to groups, and sort the output.
SQL (Structured Query Language)
The standard programming language for managing and querying relational databases. Think of it as the common tongue every relational database system understands.
DDL (Data Definition Language)
The subset of SQL commands used to define and modify the structure of a database: creating schemas, tables and views. In simple terms, DDL builds the containers your data lives in.
DML (Data Manipulation Language)
The subset of SQL commands used to insert, update, delete and retrieve data within existing structures. This is the part of SQL you use to work with the actual rows and values.
Schema
A named collection of database objects (tables, views, constraints) that belongs to a single owner. Created with the CREATE SCHEMA command. Think of it as a folder that groups related tables together.
Catalog
A system-level structure that stores descriptions of all objects (schemas, tables, columns, users) in a database. It is not created by a user; it is maintained by the DBMS itself.
Set theory
The branch of mathematics that relational databases are built on. Tables are treated as sets of rows, and operations like SELECT, JOIN and UNION mirror set operations.
Primary key
A column (or combination of columns) that uniquely identifies each row in a table. No two rows can share the same primary key value.
Foreign key
A column in one table whose values must match the primary key values in another table, enforcing a link between the two. In simple terms, it is how tables reference each other.
DISTINCT
A qualifier used in the SELECT statement to eliminate duplicate rows from the result set.
Wildcard operator ( * )
In SQL, the asterisk (*) stands for "all columns" in a SELECT statement. It is also used in pattern matching with LIKE.
Aggregate function
A function that takes a set of values and returns a single summary value: COUNT, SUM, AVG, MIN, MAX.
Scalar aggregate
A single value returned by an aggregate function applied to the entire result set (e.g. COUNT(*) over all rows).
Vector aggregate
Multiple values returned when an aggregate function is applied per group (via GROUP BY), producing one result per group rather than one result overall.
Expression
A mathematical manipulation of data in a table that can be included as part of the SELECT statement. For example, price * quantity computes a value from two columns.
Boolean operators (AND, OR, NOT)
Logical operators used to combine conditions in a WHERE or HAVING clause. Evaluation order when multiple operators appear: NOT first, then AND, then OR.
SQL is a standardised language, meaning it works (with minor dialect differences) across database systems from different vendors.
A major benefit of this standardisation is reduced training costs: learn SQL once, apply it on Oracle, MySQL, SQL Server, PostgreSQL and others.
Additional benefits include cross-system communication and application portability.
Relational databases are built on the mathematical concept of set theory. Each table is a set of rows, and SQL operations manipulate those sets.
CREATE SCHEMA creates a new database (schema). This is the command tested for "creating a database."
CREATE TABLE creates a table within a schema.
CREATE VIEW creates a virtual table based on a query.
CREATE AUTHORIZATION sets permissions.
DROP removes an entire database object (a table, a schema, a view). It does not delete individual rows.
To delete rows, you use DELETE (a DML command).
When creating a table, you must consider foreign key / primary key relationships. These links enforce referential integrity between tables.
DML stands for Data Manipulation Language, not "data mapping language."
Core DML commands: SELECT, INSERT, UPDATE, DELETE.
A database is maintained and queried using DML.
SELECT specifies which columns to return.
Use * (the wildcard) to select all columns.
Use DISTINCT after SELECT to eliminate duplicate rows.
Expressions (mathematical calculations like price * quantity) can appear in the SELECT clause.
FROM specifies the table(s) to query.
WHERE states the conditions for row selection (filters individual rows before any grouping).
Boolean operators combine conditions: NOT is evaluated first, then AND, then OR.
Use parentheses to override default precedence.
GROUP BY groups rows that share values in specified columns so that aggregate functions can be applied per group.
GROUP BY can produce both scalar aggregates (one value for the whole set) and vector aggregates (one value per group).
HAVING filters groups after GROUP BY has been applied. It is not the same as WHERE.
WHERE filters individual rows before grouping.
HAVING filters groups after grouping.
ORDER BY sorts the final result set in ascending (ASC, the default) or descending (DESC) order.
A schema is a named collection of database objects owned by a user.
A catalog is a system-maintained structure holding descriptions of all database objects. It is not something a user creates.
The statement "a catalog contains object descriptions created by a user" is false; the system creates and maintains the catalog.
Students often think DROP deletes rows from a table. It does not. DROP removes an entire object (table, schema, view). To delete rows, use DELETE.
Students confuse HAVING and WHERE, treating them as interchangeable. WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY.
"Data mapping language" sounds plausible, but DML stands for Data Manipulation Language. This is a common trick question.
Students assume a catalog is something a user creates. The catalog is maintained by the DBMS itself; it stores metadata about the database's objects automatically.
⚠️ Know the difference between DROP (removes an object) and DELETE (removes rows). This is tested directly.
⚠️ WHERE vs HAVING is a classic exam question. WHERE = rows, HAVING = groups. If the question involves GROUP BY, the filter on groups is HAVING.
⚠️ Boolean operator precedence: NOT → AND → OR. Expect a question that tests whether you know OR is evaluated last.
⚠️ The wildcard operator in SQL is *, not & or <> or =.
⚠️ CREATE SCHEMA is the command for creating a database. CREATE TABLE creates a table within a schema.
⚠️ DISTINCT eliminates duplicate rows. Not SPECIFIC, not CHECK, not ALTER.
⚠️ Scalar aggregate = one value for the whole set. Vector aggregate = one value per group. GROUP BY can produce both.
SQL is the foundation of virtually every data-driven application. When a website displays your order history, a SELECT query with WHERE filters pulled your rows. When a business dashboard shows "total sales by region," that is GROUP BY with SUM. Understanding these clauses is not just exam material; it is the core skill for anyone working with data.
True or false: The DROP command deletes rows from a table individually or in groups. (False, DROP removes entire objects.)
Fill in the blank: Relational databases are based on the mathematical concept of ______ theory. (Set theory.)
True or false: The HAVING clause and the WHERE clause perform the same operation. (False. WHERE filters rows; HAVING filters groups.)
Fill in the blank: To eliminate duplicate rows, use the ______ qualifier. (DISTINCT.)
True or false: When multiple Boolean operators appear, NOT is evaluated first, then AND, then OR. (True.)
Q: What command is used to create a database in SQL?
A: CREATE SCHEMA. (Not CREATE TABLE, which creates a table within a schema.)
Q: What does the wildcard operator * do in a SELECT statement?
A: It selects all columns from the specified table(s).
Q: What is the difference between a scalar aggregate and a vector aggregate?
A: A scalar aggregate returns a single value for the entire result set (e.g. the total count of all rows). A vector aggregate returns one value per group when GROUP BY is used.
Q: Which SQL clause states the conditions for row selection?
A: The WHERE clause. (GROUP BY groups rows, SELECT picks columns, FROM names the tables.)
Q: A query reads: SELECT Item_No, description FROM item WHERE weight > 100 AND weight < 200; What does it return?
A: The Item_No and description for all items weighing between 101 and 199. The boundary values 100 and 200 are excluded because the operators are strictly greater-than and strictly less-than.
Q: True or false: A database is maintained and queried using the data mapping language (DML).
A: False. DML stands for Data Manipulation Language, not data mapping language. The statement is a deliberate misstatement of the acronym.
Q: Which clause finds all groups meeting stated conditions?
A: HAVING. (WHERE filters individual rows, not groups.)
Q: The following query totals sales in state = 'MA' for each salesperson. True or false?
SELECT salesperson_id, sum(sales) FROM salesperson GROUP BY salesperson_id HAVING state = 'MA';
A: False. The HAVING clause filters groups based on aggregate conditions, but state = 'MA' is a row-level filter that belongs in a WHERE clause. Without WHERE, this query does not correctly filter to Massachusetts before grouping.
This material connects directly to entity-relationship modelling (earlier chapters), where you designed the tables and relationships that SQL now lets you build and query. Understanding WHERE vs HAVING here will pay off when you reach subqueries and correlated subqueries in later chapters, since nested queries rely on the same filtering logic. Aggregate functions (SUM, COUNT, AVG) reappear in reporting and data warehousing topics.
SQL, Structured Query Language, DDL, Data Definition Language, DML, Data Manipulation Language, CREATE SCHEMA, CREATE TABLE, DROP, DELETE, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT, wildcard, asterisk, aggregate function, scalar aggregate, vector aggregate, COUNT, SUM, AVG, MIN, MAX, Boolean operators, AND, OR, NOT, set theory, relational database, primary key, foreign key, referential integrity, catalog, schema, expression, SQL standard, cross-system communication, Ch. 5 SQL quiz, introduction to SQL