SQL and Client-Server Databases

SELECT retrieves data from one or more tables.

SELECT column1, column2
FROM TableName
WHERE condition
ORDER BY column ASC|DESC;

Examples:

-- All students with grade > 70
SELECT Name, Grade
FROM Student
WHERE Grade > 70
ORDER BY Grade DESC;

-- Count rows matching a condition
SELECT COUNT(*)
FROM Order
WHERE CustomerID = 5;

-- Select all columns
SELECT *
FROM Product
WHERE Price < 10.00;

Filtering operators: =, <> (not equal), <, >, <=, >=, LIKE (pattern matching with % wildcard), IN, BETWEEN … AND …, AND, OR, NOT.

JOIN combines rows from two or more tables based on a related column (usually a foreign key).

INNER JOIN

Returns rows where there is a match in both tables.

SELECT Student.Name, Course.Title
FROM Student
INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID
INNER JOIN Course ON Enrolment.CourseCode = Course.CourseCode
WHERE Course.Title = 'Computer Science';

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matched rows from the right; unmatched right-side values are NULL.

SELECT Customer.Name, Order.OrderID
FROM Customer
LEFT JOIN Order ON Customer.CustomerID = Order.CustomerID;
-- Returns all customers, even those with no orders (OrderID = NULL)

When to use each:

• INNER JOIN: only records that have matching data in both tables
• LEFT JOIN: all records from the left table, whether or not they have matches

INSERT INTO

Adds a new row to a table.

INSERT INTO Student (StudentID, Name, Email, Grade)
VALUES (42, 'Alice', 'alice@school.ac.uk', 85);

UPDATE … SET

Modifies existing rows. Without a WHERE clause, all rows are updated.

UPDATE Product
SET Price = Price * 1.1
WHERE Category = 'Electronics';

DELETE FROM

Removes rows. Without a WHERE clause, all rows are deleted.

DELETE FROM Order
WHERE OrderDate < '2024-01-01';

Caution: UPDATE and DELETE without WHERE affect every row in the table.

DDL (Data Definition Language) defines the structure of a database.

CREATE TABLE Student (
    StudentID   INTEGER     PRIMARY KEY,
    Name        VARCHAR(50) NOT NULL,
    Email       VARCHAR(100) UNIQUE,
    Grade       INTEGER,
    CourseCode  CHAR(6)     REFERENCES Course(CourseCode)
);

Key constraints:

Constraint	Meaning
PRIMARY KEY	Uniquely identifies each row; cannot be NULL
NOT NULL	The column must have a value
UNIQUE	All values in the column must be distinct
REFERENCES Table(Column)	Foreign key — enforces referential integrity
CHECK (condition)	Validates column values against a condition

A client-server database allows multiple clients to access and modify the database simultaneously. This introduces a fundamental problem: concurrent access.

The lost update problem

Two clients read the same value and both update it — the second update overwrites the first, losing a change.

Client A reads StockLevel = 10
Client B reads StockLevel = 10
Client A writes StockLevel = 9  (deducts 1)
Client B writes StockLevel = 8  (deducts 2 from the original 10 — Client A's update is lost)
Result: StockLevel = 8, but should be 7

Concurrency control methods

Record locking: a client locks a record when reading it for update; other clients must wait until the lock is released.

Serialisation: transactions are scheduled so they execute as if they ran sequentially, even if they run concurrently. Guarantees consistent results.

Timestamp ordering: each transaction is assigned a timestamp when it starts. Conflicting operations are resolved in timestamp order — older transactions take priority.

Commitment ordering: transactions are committed in an order that respects their dependencies, preventing cycles.

1. Forgetting the WHERE clause in UPDATE and DELETE

UPDATE Product SET Price = 0; sets every product's price to 0. DELETE FROM Student; deletes every student. These are valid SQL that run without error — the missing WHERE clause is a logic error, not a syntax error.

2. Confusing INNER JOIN and LEFT JOIN

INNER JOIN returns only matched rows — customers with no orders are excluded. LEFT JOIN returns all customers, with NULL for the order columns when there is no match. The choice depends on whether you want unmatched rows.

3. Confusing DDL and DML

DDL (Data Definition Language): CREATE TABLE, ALTER TABLE, DROP TABLE — define structure. DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE — manipulate data. Exam questions sometimes ask which category a statement belongs to.

4. Describing record locking without mentioning deadlock risk

Record locking solves the lost update problem but creates a deadlock risk: Client A holds lock on Record 1 and waits for Record 2; Client B holds lock on Record 2 and waits for Record 1. Neither can proceed. Database systems use timeout detection or deadlock detection algorithms to resolve this.