SQL Fundamentals

1Introduction

This lecture explains the fundamentals of SQLStructured Query Language, which declaratively expresses queries and updates over relational databases. It connects tables, rows, columns, primary keys, and foreign keys from the preceding lecture to SQL syntax.

SQL describes conditions on a required result rather than a procedure for reaching the data. A database management system normally chooses the execution plan, including index use and join order. This declarative approach separates a logical query from its physical execution method.

2SELECT, FROM, and WHERE

A basic query consists of three elements: SELECT, FROM, and WHERE.

• FROM specifies the tables that form the query's input.
• WHERE tests each row against a condition. This operation is called selectionSelection, or filtering.
• SELECT specifies the columns or expressions included in the result and broadly corresponds to projectionProjection. SQL retains duplicate rows by default; SELECT DISTINCT removes duplicates. This differs from projection under the set semantics of relational algebra.

Conceptually, FROM establishes the input, WHERE selects rows, and SELECT projects columns. These are logical roles; a database management system need not perform the physical operations in this order.

SELECT student_id, name
FROM students
WHERE department = 'Computer Science';

This query selects rows whose department is Computer Science from students and specifies student_id and name as result columns. Duplicate rows remain unless DISTINCT is specified.

3Result Ordering

A SQL query result has no guaranteed order unless the query specifies ORDER BY. The displayed order of the same query can change with the execution plan or physical data layout.

SELECT student_id, name
FROM students
ORDER BY student_id ASC;

When reproducible ordering is required, ORDER BY must include enough columns to resolve ties.

4NULL and Three-Valued Logic

NULL is a marker for a value that is absent or unknown; it is neither an empty string nor the number zero. An ordinary comparison involving NULL yields UNKNOWN rather than TRUE or FALSE. SQL conditions therefore follow three-valued logicThree-valued logic.

WHERE retains only rows for which its condition is TRUE; it excludes rows for which the condition is FALSE or UNKNOWN. A query must consequently test nullity with IS NULL or IS NOT NULL, not with = NULL.

SELECT student_id, name
FROM students
WHERE email IS NULL;

5JOIN and Keys

JOIN combines related data stored in separate tables. Suppose that students.student_id is a primary key and enrollments.student_id is a foreign key that references it.

SELECT s.student_id, s.name, e.course_id
FROM students AS s
JOIN enrollments AS e
  ON e.student_id = s.student_id
WHERE e.status = 'enrolled';

ON specifies the join condition that matches rows from the two tables. The uniqueness of the primary key and the referential integrity of the foreign key allow the database to validate this relationship. A JOIN can nevertheless execute even when no foreign-key constraint has been declared.

6Updates, Constraints, and Transactions

SQL uses INSERT to add rows, UPDATE to modify existing rows, and DELETE to remove rows.

INSERT INTO enrollments (student_id, course_id, status)
VALUES (1001, 'CS101', 'enrolled');

UPDATE enrollments
SET status = 'completed'
WHERE student_id = 1001 AND course_id = 'CS101';

DELETE FROM enrollments
WHERE student_id = 1001 AND course_id = 'CS101';

ConstraintsConstraint such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK record conditions on admissible data in the schema and reject invalid updates. Specific constraint features and syntax vary among database products.

A transactionTransaction treats multiple statements as one unit of work. COMMIT makes a transaction's changes permanent, whereas ROLLBACK cancels uncommitted changes. Transactions can thereby prevent the database from retaining only an intermediate portion of an operation. Isolation levels and default autocommit behavior vary among implementations.

7Parameterization and Security

Directly concatenating user input into a SQL string can cause SQL injection, in which input is interpreted as SQL syntax. Values must instead be supplied separately through the parameterized-query facility of the relevant database driver.

SELECT student_id, name
FROM students
WHERE email = ?;

Here, ? is a conceptual parameter marker; actual placeholder syntax varies among drivers and database products. Parameterization separates values from SQL syntax, but it does not automatically make arbitrary identifiers such as table or column names safe. Dynamically selected identifiers require validation such as an allowlist. Authorization, least privilege, and protection of confidential data remain separate requirements.

8Key Points

• SQL declares a required result and delegates its physical execution method to the database management system.
• WHERE selects rows, and SELECT specifies result columns or expressions. SQL retains duplicate rows by default. Three-valued logic applies to conditions involving NULL.
• JOIN relates multiple tables, while keys and constraints support data integrity.
• Transactions can manage updates as one unit, and external values must be parameterized to separate them from SQL syntax.
• A result has no guaranteed order without ORDER BY.

9Next Lecture