SQL statements have different purposes: some define the database's structure, while others work with the records held in that structure.

Structure versus contents

Data definition language (DDL) is the part of SQL used to create or change database structures. Data manipulation language (DML) is the part used to retrieve, insert, change or remove stored data.

Reading the distinction in SQL

This DDL statement creates a table. It specifies column names and data types, but does not add any room records.

1  CREATE TABLE Room (
2      roomId INTEGER PRIMARY KEY,
3      roomName VARCHAR(30)
4  );

This DML statement retrieves the names of rooms already stored in that table. It does not change the table's structure.

1  SELECT roomName
2  FROM Room;

The key distinction is the target of the operation, not whether the statement changes something: a SELECT query belongs to DML even though it leaves the stored records unchanged.

Terminology: data definition language (DDL), data manipulation language (DML), SQL statements, data structures.

To construct a query across two tables, identify how their records match, select the required columns, apply conditions and specify how the result should be arranged.

Two related tables

A community centre stores its rooms and bookings separately. In Room, roomId is the primary key. In Booking, bookingId is the primary key and roomId is a foreign key referencing Room.

Room

Booking

Join, filter and order

A join combines records from tables according to a matching condition. Here, the condition matches each booking's roomId to the corresponding room's roomId.

The following query finds bookings charged between 20 and 80 whose organiser starts with Art or Code, excluding Birch. It returns higher charges first, using room names in ascending order to resolve equal charges.

1  SELECT r.roomName, b.organiser, b.charge
2  FROM Room r
3  JOIN Booking b ON r.roomId = b.roomId
4  WHERE b.charge BETWEEN 20 AND 80
5      AND (b.organiser LIKE 'Art%' OR b.organiser LIKE 'Code%')
6      AND NOT (r.roomName = 'Birch')
7  ORDER BY b.charge DESC, r.roomName ASC;

The short names r and b are table aliases. Prefixing columns with their aliases makes it clear which table supplies each value.

Work through the query in this conceptual order: FROM and JOIN identify matching records; WHERE keeps qualifying rows; SELECT identifies the output columns; ORDER BY arranges the result. This is a reasoning model, not a claim about the database's physical execution order.

Conditions and pattern matching

Relational operators compare values. WHERE retains rows for which its condition is true.

With LIKE, the % wildcard represents any sequence of characters, including an empty sequence. A pattern of 'Art%' matches text beginning with Art; '%Club' matches text ending with Club; '%Circle%' matches text containing Circle. Case sensitivity can depend on the database system.

Parentheses make combinations of AND, OR and NOT explicit. In the worked query, either organiser pattern is acceptable, but the charge condition and room exclusion must also hold.

Removing duplicate results

SELECT normally retains duplicate output rows. DISTINCT removes duplicates across the complete set of selected columns.

1  SELECT DISTINCT r.roomName
2  FROM Room r
3  JOIN Booking b ON r.roomId = b.roomId
4  WHERE b.hours >= 2
5  ORDER BY r.roomName ASC;

The result is Birch, Cedar and Maple, each appearing once. Without DISTINCT, Maple would appear twice because two of its bookings meet the condition. ASC means ascending order; DESC means descending order.

Grouping rows and filtering groups

GROUP BY places rows with matching grouping values into the same group. HAVING filters those groups, whereas WHERE filters individual rows before grouping.

This query first keeps bookings lasting at least two hours, then groups them by room name and retains only room-name groups beginning with M.

1  SELECT r.roomName
2  FROM Room r
3  JOIN Booking b ON r.roomId = b.roomId
4  WHERE b.hours >= 2
5  GROUP BY r.roomName
6  HAVING r.roomName LIKE 'M%'
7  ORDER BY r.roomName ASC;

Its result is a single row: Maple. Both Maple bookings enter the same group, so the group produces one output row. Grouping does not itself guarantee any particular output order; use ORDER BY when order matters.

SQL syntax can vary between database systems, but the roles of selecting, joining, filtering, grouping and ordering remain distinct.

Terminology: SELECT, DISTINCT, FROM, WHERE, BETWEEN, ORDER BY, GROUP BY, HAVING, ASC, DESC, JOIN, LIKE, % wildcard, AND, OR, NOT, relational operators.

SQL changes stored data by inserting records, modifying values or deleting records. These operations may also require the database to maintain indexes, so the work involved can extend beyond changing the table itself.

Insert a new record

INSERT INTO adds a record to a table. Listing the column names explicitly makes the correspondence between columns and values clear.

Using the Booking table from the preceding section, this statement adds a two-hour booking for Music Circle in room 2.

1  INSERT INTO Booking (bookingId, roomId, organiser, hours, charge)
2  VALUES (6, 2, 'Music Circle', 2, 50);

The supplied values must satisfy the table's constraints. For example, bookingId must not duplicate an existing primary key, and roomId must reference an existing room.

Modify existing data

UPDATE changes values in existing records; SET identifies the columns and their new values. WHERE identifies the records to change.

1  UPDATE Booking
2  SET charge = 60
3  WHERE bookingId = 6;

The new booking's charge changes from 50 to 60. Omitting WHERE would change the charge in every Booking record, not just booking 6.

Remove a record

DELETE removes records selected by its condition. It leaves the table and its column definitions in place.

1  DELETE FROM Booking
2  WHERE bookingId = 6;

Omitting WHERE would remove all records from Booking. It would not remove the table itself.

Why indexes affect update performance

An index is an additional structure that helps the database locate records efficiently. Faster retrieval comes with maintenance work when indexed data changes.

• An insertion may require new entries in each relevant index.
• A deletion requires the corresponding index entries to be removed or marked for removal.
• Updating an indexed column can require its old index entry to be replaced or repositioned.

Maintaining more indexes generally adds work to data modifications. An index can nevertheless speed up locating the records targeted by an UPDATE or DELETE condition, so the overall effect depends on the operation and database design.

Reorganizing or rebuilding indexes

Significant modifications can leave an index fragmented or inefficiently arranged. Reorganizing an index tidies its existing organization; rebuilding creates a fresh index structure from the current data.

These operations can improve later access performance, but they also consume processing time and storage resources and may affect availability. They are maintenance responses to substantial changes or degraded organization, not steps that must follow every individual update.

Terminology: INSERT INTO, UPDATE, SET, DELETE, indexed columns, indexes, rebuilt, reorganized.

To construct an aggregate query, choose the records to include, choose any grouping columns, and apply functions that reduce each group to calculated results.

An aggregate function calculates a result from a collection of values. With GROUP BY, SQL calculates a separate result for each group rather than one result for the entire query.

The required functions

The guide calls the mean operation AVERAGE; the SQL function name used in the examples is AVG. COUNT(*) counts rows, while COUNT(column) counts non-null values in that column. The other listed functions ignore null values in their input column.

Worked grouped report

Use the original Room and Booking data from A3.3.2, without booking 6. The charge values entering each room's group are:

The following query joins the room names to their bookings and calculates all five aggregates for each room.

1  SELECT r.roomName,
2      COUNT(*) AS bookingCount,
3      SUM(b.charge) AS totalCharge,
4      AVG(b.charge) AS averageCharge,
5      MIN(b.charge) AS lowestCharge,
6      MAX(b.charge) AS highestCharge
7  FROM Room r
8  JOIN Booking b ON r.roomId = b.roomId
9  GROUP BY r.roomId, r.roomName
10  ORDER BY r.roomId ASC;

Both selected room identifiers are included in the grouping: roomId distinguishes rooms, while roomName supplies the readable label. AS gives each calculated output column a meaningful name.

For Cedar, COUNT is 2, SUM is 40 + 20 = 60, and AVG is 60 / 2 = 30. Comparing the two values gives MIN 20 and MAX 40. For Maple, 90 + 60 = 150 and 150 / 2 = 75. Birch has one booking, so its sum, average, minimum and maximum are all 80.

Filtering a calculated report

WHERE filters individual input records. HAVING can filter groups using their calculated results.

1  SELECT r.roomName, SUM(b.charge) AS totalCharge
2  FROM Room r
3  JOIN Booking b ON r.roomId = b.roomId
4  GROUP BY r.roomId, r.roomName
5  HAVING SUM(b.charge) >= 100
6  ORDER BY totalCharge DESC;

Only Maple appears, with totalCharge 150. A condition on an individual charge would answer a different question: it would remove bookings before their room totals were calculated.

Such reports support decisions by summarizing activity. The centre can compare total charges between rooms, while the counts and averages distinguish many lower-charge bookings from fewer higher-charge bookings.

Terminology: aggregate functions, grouped data, AVERAGE, AVG, COUNT, MAX, MIN, SUM, GROUP BY, HAVING.

Database views let users work with a particular presentation of data rather than directly with the underlying tables. The key difference is what the database stores: just the query definition, or its results as well.

Two kinds of view

A virtual view is a named query that retrieves results from the underlying data whenever someone queries it. It keeps no separate stored copy of the result rows.

A materialized view, also called a snapshot view, stores a query's results. Those results must be refreshed to reflect later changes in the underlying data.

Hiding complexity and simplifying queries

Suppose a RoomSchedule view combines Room and Booking but exposes only roomName and hours. Users can request these fields from RoomSchedule without writing the join each time. They don't need to know the underlying roomId relationship either.

Both types of view can hide joins and other query details, making repeated queries simpler while giving users or applications a stable interface.

Independence and consistency

Views offer a degree of data independence. Sometimes the underlying table arrangement can change while the view continues to present the same external columns. The view definition must be adapted correctly, though. A view does not automatically make every structural change harmless.

Sharing a view definition also keeps selection rules consistent. Users who query the same view don't have to recreate its filtering or calculation rules independently.

Because a virtual view has no separate stored result, it avoids having a stored copy that could become stale. A materialized view, by contrast, may temporarily disagree with changed base data until it is refreshed. Its performance benefit has to be weighed against the need for up-to-date information.

Security and update access

RoomSchedule leaves out organiser and charge. Users who have permission to query this view, but not the underlying tables, can see scheduling information without gaining access to those other columns. Views can restrict visible rows too.

A view may provide read-only data or updatable data. With some simple virtual views, a change made through the view can be mapped unambiguously to a base-table record. Complex joins or calculations may prevent a view from supporting such updates directly; the rules depend on the database system.

Materialized views are commonly used to read prepared results. Changes are made to the base data, then incorporated through refreshes. Access permissions still need to be configured. Creating a view alone does not stop users who already have base-table access from reading the underlying data.

Terminology: virtual views, materialized (snapshot) views, data consistency, independence, performance, query simplification, read-only data, updatable data, security.

Transactions protect data integrity by treating related database operations as a controlled unit. The database must not leave behind a partial operation that breaks the rules of the application.

A transaction is a sequence of database operations treated as one unit of work. It either completes successfully as a whole or has its changes undone.

A transfer that requires two updates

A centre stores customer credit in an Account table. It needs to transfer 10 units from account 1 to account 2.

The combined balance must remain 100. If only the deduction succeeded, the balances would be 70 and 20: 10 units would have disappeared. The two updates therefore belong in one transaction.

ACID properties

These properties work together. Atomicity prevents partial completion; consistency concerns the validity of the result; isolation controls interaction with concurrent work; durability protects a completed result.

Consistency still depends on correct transaction logic and appropriate constraints. Wrapping an incorrect calculation in a transaction does not make that calculation correct.

Transaction control language

Transaction control language (TCL) controls transaction boundaries and outcomes.

• BEGIN TRANSACTION starts the transaction.
• COMMIT completes it successfully and makes its changes permanent.
• ROLLBACK cancels the uncommitted changes made by the transaction.

A successful transfer can be expressed as follows:

1  BEGIN TRANSACTION;
2  UPDATE Account
3  SET balance = balance - 10
4  WHERE accountId = 1;
5  UPDATE Account
6  SET balance = balance + 10
7  WHERE accountId = 2;
8  COMMIT;

Before committing, the application must establish that the required operations succeeded and the transfer is valid. For example, an UPDATE that matches no account may not itself raise an error, so checking success matters.

If a problem is detected before COMMIT, the transaction should instead execute:

1  ROLLBACK;

This restores the original balances of 80 and 20 rather than retaining only part of the transfer. ROLLBACK is an alternative to committing the pending transaction; it does not undo a transaction that has already been committed.

Terminology: transaction, data integrity, atomicity, consistency, isolation, durability, ACID, transaction control language (TCL), BEGIN TRANSACTION, COMMIT, ROLLBACK.