SQL: interpret and modify

SQL – Interpret and modify. See appendix 5d.

1.3.2 Databases

Databases — Note 4 of 6

Candidates should have experience of using SQL to edit and modify data in a database. They should understand the need for SQL as a standard language. Candidates should be able write and follow scripts using the SQL commands listed in appendix 5d.

What you need to know

Structured Query Language (SQL) is the standard language for querying and changing relational databases. You need to be able to read, write and amend SQL using the commands in appendix 5d.

What to know

SELECT chooses the fields to return; FROM names the table(s); WHERE filters records using conditions.
AND / OR combine conditions. LIKE matches patterns, using % as a wildcard for any number of characters. * after SELECT returns all fields.
JOIN combines records from two tables where a field matches (equivalent to INNER JOIN; outer joins are not required).
Nested SELECT: a query can be used inside another query's WHERE clause.
INSERT INTO ... VALUES adds a record; DELETE FROM ... WHERE removes records; DROP TABLE deletes a whole table and its data.
SQL is a standard language, so the same queries work across many different database systems.

Key terms

SQL: A declarative language used to define, query and manipulate data in relational databases.

Exam technique

Tip:Use the exact table and field names given in the question, and put text values in quotation marks.
Mistake:Writing DELETE FROM Students without a WHERE clause, which deletes EVERY record.
Tip:Other commands (e.g. UPDATE, ORDER BY) may appear, but will be explained in the question if needed.

Code examples

SQL commands from appendix 5d

sql

-- Fields from one table with conditions
SELECT FirstName, Surname
FROM Students
WHERE Year = 12 AND Surname LIKE 'S%'

-- Join two tables on a shared key
SELECT Students.Surname, Classes.Room
FROM Students
JOIN Classes ON Students.ClassID = Classes.ClassID

-- Nested SELECT
SELECT Surname FROM Students
WHERE ClassID IN (SELECT ClassID FROM Classes WHERE Room = 'C4')

-- Add, remove and drop
INSERT INTO Students (StudentID, FirstName, Surname, Year)
VALUES (1042, 'Sam', 'Patel', 12)
DELETE FROM Students WHERE StudentID = 1042
DROP TABLE OldResults

← Previous noteNormalisation to 3NF1.3.2 Databases Next note →Referential integrity1.3.2 Databases

Back to Databases