CIE 9618 Paper 1 — SQL reference

Cambridge International AS & A Level Computer Science — Chapter 8.3 SQL subset

Open SQL IDE Pseudocode IDE

Overview

This reference covers the SQL commands tested in CIE 9618 Paper 1 theory. Keywords are case-insensitive. String values use single quotes: 'S101'.

SQL IDE: Use the online SQL editor to run scripts. Exam mode follows strict exam syntax. Textbook mode also accepts missing FK parentheses and AND instead of commas in SELECT lists.

Data types

TypeMeaningExample value
CHARACTERText'S101'
VARCHAR(n)Text, max n characters'Room 3'
BOOLEANTrue or FalseTRUE, FALSE
INTEGERWhole number78
REALDecimal number19.99
DATEDate YYYY-MM-DD'2008-05-12'
TIMETime HH:MM:SS'14:30:00'

DDL — CREATE & ALTER

CREATE DATABASE

CREATE DATABASE School;

CREATE TABLE

CREATE TABLE Student(
    StudentID CHARACTER,
    FirstName CHARACTER,
    SecondName CHARACTER,
    DateOfBirth DATE,
    ClassID CHARACTER);

ALTER TABLE — primary key

ALTER TABLE Student ADD PRIMARY KEY (StudentID);
ALTER TABLE STUDENT_SUBJECT ADD PRIMARY KEY (StudentID, SubjectName);

ALTER TABLE — foreign key

ALTER TABLE Student ADD FOREIGN KEY (ClassID) REFERENCES Class(ClassID);

ALTER TABLE — add column

ALTER TABLE STUDENT ADD Email VARCHAR(60);

Typical script order

  1. CREATE DATABASE
  2. CREATE TABLE parent tables
  3. ALTER TABLE … ADD PRIMARY KEY
  4. CREATE TABLE child tables
  5. ALTER TABLE … ADD FOREIGN KEY

SELECT queries

SELECT FirstName, SecondName
FROM Student
WHERE ClassID = '7A'
ORDER BY SecondName;

WHERE operators

=, <>, <, >, <=, >=, combined with AND / OR.

WHERE Returned = FALSE
WHERE ClassID = '7A' AND SecondName = 'Adams'

ORDER BY

ORDER BY SecondName
ORDER BY SecondName ASC
ORDER BY SecondName DESC

INNER JOIN

SELECT STUDENT.FirstName, STUDENT.LastName, TUTOR.TutorName
FROM STUDENT
INNER JOIN CLASS ON STUDENT.ClassCode = CLASS.ClassCode
INNER JOIN TUTOR ON CLASS.TutorID = TUTOR.TutorID
WHERE STUDENT.ClassCode = 'C5A';

GROUP BY & aggregates

SELECT ClassCode, COUNT(StudentID) AS NumberOfPupils
FROM STUDENT
GROUP BY ClassCode;

SELECT SUM(ExamMark) AS TotalMarks
FROM STUDENT_SUBJECT
WHERE StudentID = 'S101';

SELECT AVG(ExamMark) AS AveragePhysics
FROM STUDENT_SUBJECT
WHERE SubjectName = 'Physics';

Allowed aggregate functions: SUM(), COUNT(), AVG().

INSERT, UPDATE, DELETE

INSERT INTO

INSERT INTO Student VALUES('S1301', 'Peter', 'Probert', '2011-06-06', '7A');

INSERT INTO STUDENT(StudentID, FirstName, LastName, ClassCode)
VALUES('S105', 'Fabrice', 'Ah-Sen', 'C5A');

UPDATE

UPDATE STUDENT
SET ClassCode = 'C5A'
WHERE StudentID = 'S104';

DELETE FROM

DELETE FROM STUDENT WHERE StudentID = 'S105';
Warning: DELETE FROM TableName with no WHERE clause deletes all rows.

Not in the syllabus

The following are not required for CIE 9618 Paper 1 and are rejected by the SQL IDE: