SQL

8.3 SQL (DML & DDL)

Understanding SQL Data Definition Language (DDL) and Data Manipulation Language (DML) for database management

Learning Objectives

By the end of this lesson, you will be able to:

  • Show understanding that DBMS carries out all creation/modification of database structure using its Data Definition Language
  • Show understanding that DBMS carries out all queries and maintenance of data using Data Manipulation Language
  • Show understanding that industry standard for both DDL and DML is SQL
  • Understand given SQL (DDL) commands and write simple SQL (DDL) commands using a sub-set of commands
  • Create database (CREATE DATABASE) and tables (CREATE TABLE) with various data types
  • Change table definitions using ALTER TABLE commands
  • Add primary keys and foreign keys to tables
  • Write SQL scripts to query or modify data stored in database tables using DML
  • Use SELECT, FROM, WHERE, ORDER BY, GROUP BY, INNER JOIN, SUM, COUNT, AVG for queries
  • Perform data maintenance using INSERT INTO, DELETE FROM, UPDATE commands

Key Terms

SQL (Structured Query Language)

Programming language provided by DBMS to support all operations associated with a relational database

DDL (Data Definition Language)

Part of SQL used by DBMS to create, modify and remove data structures that form relational database

DML (Data Manipulation Language)

Used to select, insert, update, or delete data in objects defined with DDL

CREATE DATABASE

DDL command that creates a new database

CREATE TABLE

DDL command that creates a new table definition with specified columns and data types

ALTER TABLE

DDL command that changes the definition of an existing table

PRIMARY KEY

Constraint that uniquely identifies each record in a table

FOREIGN KEY

Constraint that creates a relationship between two tables

SELECT

DML command that fetches data from a database (queries always begin with SELECT)

INSERT INTO

DML command that adds new row(s) to a table

UPDATE

DML command that edits row(s) in a table

DELETE FROM

DML command that removes row(s) from a table

CHARACTER / CHAR

Fixed length text data type

VARCHAR(n)

Variable length text data type (max length n)

INNER JOIN

Combines rows from different tables if the join condition is true

GROUP BY

Arranges data into groups, often used with aggregate functions

Introduction to SQL

Structured Query Language (SQL) is a programming language provided by DBMS to support all operations associated with a relational database. SQL is used when a database package offers high-level software tools for user interaction. SQL is used for sorting, manipulating and retrieving data stored in relational database.

Features of SQL

SQL Syntax Rules

  • SQL consists of a sequence of commands
  • Each command is terminated by a semicolon (;)
  • A command can occupy more than one line
  • SQL keywords are NOT case sensitive: select is the same as SELECT
  • We usually use upper case for commands and lower case for table names, attribute names and datatypes
  • When a command contains a list of items, these are separated by a comma

SQL Data Types

Data types used for attributes in SQL:

Data Type Description
CHARACTER Fixed length text
VARCHAR(n) Variable length text (max n characters)
BOOLEAN True or False; SQL uses integers 1 and 0
INTEGER Whole number
REAL Number with decimal places
DATE A date usually formatted as YYYY-MM-DD
TIME A time usually formatted as HH:MM:SS
Note:

CHAR stands for fixed-length character. It stores a fixed amount of characters, padding the data with spaces if actual string is shorter than specified length. VARCHAR stands for variable-length character. It stores a variable amount of characters without padding with spaces.

SQL Data Types Visual Guide

CHARACTER
Fixed length text
Example: 'ABC' stored as 'ABC ' (padded)
VARCHAR(50)
Variable length text
Example: 'ABC' stored as 'ABC'
INTEGER
Whole numbers
Example: 42, -15, 0
REAL
Decimal numbers
Example: 3.14, -2.5
BOOLEAN
True/False
Stored as 1 (True) or 0 (False)
DATE
Date values
Format: YYYY-MM-DD
TIME
Time values
Format: HH:MM:SS

Real-Life Example: School Database

Think of a school database that stores student information. Different data types would be used for different kinds of information:

StudentID: INTEGER (whole number)
FirstName: VARCHAR(50) (variable text)
LastName: VARCHAR(50) (variable text)
DateOfBirth: DATE (YYYY-MM-DD)
EnrollmentDate: DATE (YYYY-MM-DD)
Grade: CHAR(2) (fixed text like '7A')
AverageScore: REAL (decimal like 85.5)
IsActive: BOOLEAN (True/False)
RegistrationTime: TIME (HH:MM:SS)
PhoneNumber: VARCHAR(15) (variable text)

Choosing the right data type is important for efficient storage and accurate data representation.

Activity 1: SQL Data Types Practice

For each of the following data items, choose the most appropriate SQL data type:

  1. A person's full name (maximum 100 characters)
  2. A product price (e.g., £19.99)
  3. A student's age in years
  4. A book ISBN (13 characters exactly)
  5. Whether a user account is active (True/False)
  6. A date of birth
  7. The time a class starts
  8. A short status message (max 280 characters)
Solution:
  1. Full name: VARCHAR(100) - variable length text up to 100 characters
  2. Product price: REAL or DECIMAL - numbers with decimal places
  3. Student's age: INTEGER - whole numbers only
  4. Book ISBN: CHAR(13) - fixed length of exactly 13 characters
  5. Account active status: BOOLEAN - True or False values
  6. Date of birth: DATE - stores dates in YYYY-MM-DD format
  7. Class start time: TIME - stores time in HH:MM:SS format
  8. Status message: VARCHAR(280) - variable length text up to 280 characters

Check Your Understanding: SQL Basics

Answer
  • [1 mark] SQL stands for Structured Query Language
  • [1 mark] Its main purpose is to support all operations associated with a relational database, including creating, modifying, querying, and managing data
  • [Additional] SQL is used for sorting, manipulating and retrieving data stored in relational databases
Answer
  • [1 mark] CHAR is fixed-length character data type that pads strings with spaces if shorter than specified length
  • [1 mark] VARCHAR is variable-length character data type that stores strings without padding, using only required space
  • [Additional] Example: 'ABC' stored as CHAR(5) = 'ABC ', stored as VARCHAR(5) = 'ABC'
Answer
  • [1 mark] No, SQL keywords are NOT case sensitive
  • [1 mark] 'select' is the same as 'SELECT' and 'Select'
  • [Additional] However, it is common practice to use uppercase for SQL keywords and lowercase for table/column names for readability
Answer
  • [1 mark] Phone numbers: VARCHAR(15) - variable length text to accommodate different formats
  • [1 mark] Birth dates: DATE - stores dates in YYYY-MM-DD format
  • [1 mark] Exam scores: REAL or DECIMAL - numbers with decimal places
  • [Additional] For exam percentages, REAL allows values like 85.5, 92.0, etc.
Answer
  • [1 mark] Boolean values are typically stored using integers: 1 for True and 0 for False
  • [Additional] Some database systems may use actual Boolean type, but the integer representation (1/0) is common

Data Definition Language (DDL)

Data Definition Language (DDL) is part of SQL. DBMS use DDL to create, modify and remove data structures that form relational database. These commands only create structure. They do not put any data into database. DDL statements are written as script that uses syntax similar to computer program.

DDL Commands

Common DDL Commands

SQL (DDL) Command Description
CREATE DATABASE Creates a database
CREATE TABLE Creates a table definition
ALTER TABLE Changes the definition of a table
PRIMARY KEY Adds a primary key to a table
FOREIGN KEY ... REFERENCES ... Adds a foreign key to a table
DDL enables you to:
  • Create, alter, and drop schema objects
  • Grant and revoke privileges and roles
  • Add comments to the data dictionary

CREATE DATABASE & DROP DATABASE

CREATE DATABASE SchoolDB;

Database name should be relevant; usually it shouldn't have spaces.

DROP DATABASE SchoolDB;
Warning:

DROP DATABASE command simply removes database. Note that it doesn't ask you for confirmation, and once you remove a database, it is gone forever.

CREATE TABLE Command

Example: Creating a Persons Table

CREATE TABLE Persons (
  PersonID int NOT NULL,
  LastName varchar(45) NOT NULL,
  FirstName varchar(45),
  DateBirth Date,
  Address varchar(255),
  City varchar(30),
  PRIMARY KEY (PersonID)
);
Explanation:
  • Creates table named "Persons"
  • PersonID column is of type int (integer)
  • LastName, FirstName, Address, and City columns are of type varchar
  • PersonID and LastName are marked as NOT NULL, which means NULL values are not allowed
  • PersonID is set as the PRIMARY KEY

Example: Band Booking Database

CREATE DATABASE BandBooking;

CREATE TABLE Band (
  BandName varchar(25),
  NumberOfMembers integer
);
ALTER TABLE Band
ADD PRIMARY KEY (BandName);

ALTER TABLE BandBooking
ADD FOREIGN KEY (BandName)
REFERENCES Band(BandName);

BandName varchar(25) allows up to 25 characters for the band name.

Example: Birds Database

Database Birds has following tables:

BIRD_TYPE(BirdID, Name, Size)
BirdID
Name
Size
0123
Blackbird
Medium
0035
Jay
Large
0004
Raven
Large
0085
Robin
Small
-- SQL script to define table Bird_Type
CREATE TABLE BIRD_TYPE (
  BirdID CHAR(4) NOT NULL,
  Name VARCHAR(9),
  Size VARCHAR(6),
  PRIMARY KEY (BirdID)
);
Note:

Bird ID as CHAR or VARCHAR • Name and size as VARCHAR or CHAR

ALTER TABLE Command

Adding a Column

ALTER TABLE Student
ADD Address varchar(25);

Adds a new column called "Address" to the Student table with data type varchar(25).

Modifying a Column

ALTER TABLE Product
MODIFY COLUMN Quantity Integer;

Changes data type of field Quantity to integer.

Dropping a Column

ALTER TABLE Stock
DROP Quantity;

Deletes the Quantity column from the Stock table.

Adding Foreign Key

ALTER TABLE Orders
ADD FOREIGN KEY (Cust_ID)
REFERENCES Customer (Cust_ID);

Adds foreign key to Orders table linking it with customer table.

Example: MARKS Database (9618/11/M/J/22)

A teacher uses a relational database, MARKS, to store data about students and their test marks. Database has following structure:

STUDENT(StudentID, FirstName, LastName)
TEST(TestID, Description, TotalMarks)
STUDENT_TEST(StudentID, TestID, Mark)
StudentID
TestID
Mark
12
A1
50
12
P10
100
13
A1
75
-- SQL script to create table STUDENT_TEST
CREATE TABLE STUDENT_TEST (
  StudentId INTEGER,
  TestID VARCHAR,
  Mark INTEGER,
  PRIMARY KEY(StudentID, TestID),
  FOREIGN KEY(TestID) REFERENCES TEST(TestID),
  FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
);

DDL Command Simulator

Try writing and executing DDL commands in the simulated SQL editor below:

-- Type your DDL commands here -- Example: CREATE DATABASE SchoolDB; -- Remember to end each command with a semicolon (;)
Results will appear here...
Tips:
  • End each SQL command with a semicolon (;)
  • Use uppercase for SQL keywords (CREATE, TABLE, etc.)
  • Table and column names are usually lowercase
  • PRIMARY KEY uniquely identifies each record
  • FOREIGN KEY creates relationships between tables

Activity 2: DDL Commands Practice

Write SQL DDL commands for the following tasks:

  1. Create a database called "Library"
  2. Create a table called "Books" with columns: BookID (integer, primary key), Title (varchar 100), Author (varchar 100), YearPublished (integer), Available (boolean)
  3. Add a new column "Genre" (varchar 50) to the Books table
  4. Create a table "Members" with: MemberID (integer, primary key), Name (varchar 100), JoinDate (date)
  5. Add a foreign key to a "Loans" table that references the BookID in the Books table
  6. Change the data type of the "YearPublished" column from integer to varchar(4)
Solution:
  1. CREATE DATABASE Library;
  2. CREATE TABLE Books (
      BookID INTEGER PRIMARY KEY,
      Title VARCHAR(100),
      Author VARCHAR(100),
      YearPublished INTEGER,
      Available BOOLEAN
    );
  3. ALTER TABLE Books ADD Genre VARCHAR(50);
  4. CREATE TABLE Members (
      MemberID INTEGER PRIMARY KEY,
      Name VARCHAR(100),
      JoinDate DATE
    );
  5. ALTER TABLE Loans ADD FOREIGN KEY (BookID) REFERENCES Books(BookID);
  6. ALTER TABLE Books MODIFY COLUMN YearPublished VARCHAR(4);

Check Your Understanding: DDL

Answer
  • [1 mark] DDL (Data Definition Language) is used by DBMS to create, modify and remove data structures that form relational database
  • [1 mark] These commands only create structure - they do not put any data into database
  • [Additional] Examples include CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE
Answer
  • [1 mark] "NOT NULL" means that the column is not allowed to have NULL values
  • [1 mark] Every row in the table must have a value for that column (it cannot be empty or null)
  • [Additional] This constraint ensures data integrity by requiring mandatory fields to always have values
Answer
  • [1 mark] PRIMARY KEY uniquely identifies each record in a table (e.g., StudentID in Students table)
  • [1 mark] FOREIGN KEY creates a relationship between two tables by referencing the PRIMARY KEY of another table
  • [1 mark] Example: StudentID in Grades table could be a FOREIGN KEY referencing StudentID in Students table
  • [Additional] PRIMARY KEY values must be unique and NOT NULL; FOREIGN KEY values must match existing PRIMARY KEY values or be NULL
Answer
ALTER TABLE Employees
ADD Email VARCHAR(100);

[2 marks] Correct syntax for ALTER TABLE with ADD column command

Answer
  • [1 mark] DROP DATABASE command immediately and permanently removes the entire database
  • [1 mark] It doesn't ask for confirmation, and once executed, all data is gone forever
  • [Additional] There is no "undo" or "recycle bin" for DROP DATABASE - all tables, data, and structure are permanently deleted

Data Manipulation Language (DML)

Data Manipulation Language is used when a database is first created, to populate the tables with data. It can then be used for ongoing maintenance. It is used to select, insert, update, or delete data in objects defined with DDL.

DML vs DDL

DDL is used for working on relational database structure, whereas DML is used to work with data stored in relational database.

DML helps us to:
  • Insert data into tables when database is created
  • Modify or remove data in database
  • Read data stored in database

DML Maintenance Commands

SQL (DML) Command Description
INSERT INTO Adds new row(s) to a table
DELETE FROM Removes row(s) from a table
UPDATE Edits row(s) in a table

DML Command Examples

INSERT INTO Command

INSERT INTO Band_Booking (BandName, BookingID)
VALUES ('Rockz', 65231);
-- If order of fields is known, can skip field names
INSERT INTO Employee
VALUES ('mrkashif42', 'Kashif', '34', 12, 'Islamabad');

DELETE FROM Command

-- Delete specific record
DELETE FROM Employee
WHERE Employee_ID = 'mrkashif42';
Warning:

DELETE FROM Student will delete the whole table! Always use WHERE clause unless you intend to delete all records.

UPDATE Command

UPDATE Cars
SET Colour = 'Red'
WHERE RegNo = 'MH09RCM';

Updates the colour field to 'Red' for the car with registration number MH09RCM.

DML Query Commands (SELECT)

SQL (DML) Query Command Description
SELECT FROM Fetches data from a database. Queries always begin with SELECT.
WHERE Includes only rows in a query that match a given condition
ORDER BY Sorts the results from a query by a given column either alphabetically or numerically
GROUP BY Arranges data into groups
INNER JOIN Combines rows from different tables if the join condition is true
SUM() Returns the sum of all the values in the column
COUNT() Counts the number of rows where the column is not NULL
AVG() Returns the average value for a column with a numeric data type
Boolean Operators:

OR, AND, NOT Boolean operators can be used together with standard comparisons such as =, >, <, >=, <=, <> (not equal) etc. when specifying conditions in WHERE clause.

IS NULL is used to check if it's a null value contained within the variable.

Example: School Database Query

-- Show first and second names of all students in class 7A, sorted alphabetically by second name
SELECT FirstName, SecondName
FROM Student
WHERE ClassID = '7A'
ORDER BY SecondName;
Query Execution Steps:
1
FROM Student - Start with the Student table
2
WHERE ClassID = '7A' - Filter to only students in class 7A
3
SELECT FirstName, SecondName - Select only these two columns
4
ORDER BY SecondName - Sort results by second name alphabetically

Aggregate Functions Examples

COUNT() Function

SCHEDULE Table
ScheduleID
StaffID
WorkDate
Morning
Afternoon
210520-1
BC
21/05/2020
TRUE
TRUE
210520-2
JB
21/05/2020
TRUE
FALSE
220520-1
BC
22/05/2020
FALSE
TRUE
220520-2
LK
22/05/2020
TRUE
FALSE
-- Count number of people working in morning of 26/05/2020
SELECT COUNT(StaffID)
FROM Schedule
WHERE WorkDate = '26/05/2020' AND Morning = TRUE;

SUM() Function

SELECT SUM(CS_Test)
FROM Test
WHERE Age > 16 AND Std_Name = "Haider";
SELECT SUM(ExamMark)
FROM STUDENTSUBJECT;

AVG() Function

SELECT AVG(column_name)
FROM table_name
WHERE condition;
GROUP BY Clause:

GROUP BY clause in SQL is used to organize identical data into groups. It is often used with aggregate functions (such as SUM, COUNT, AVG, etc.). This clause ensures that values in specified field(s) are not repeated in result set, and result is grouped based on unique values in those fields.

Example: EMPLOYEE Table Query

EMPLOYEE Table
EmployeeID
FirstName
LastName
Role
Language
001
Jasmine
Chen
Leader
French
002
Kenton
Archer
Leader
English
003
Michael
Roux
Cook
French
004
Conrad
Slavorski
Leader
Russian
-- Return first name and last name of all employees who are leaders and speak either French or English
SELECT FirstName, LastName
FROM EMPLOYEE
WHERE Role = "Leader"
  AND (Language = "French" OR Language = "English");

Example: INNER JOIN

CUSTOMER Table
CustomerID
CustomerName
1
John Doe
2
Jane Smith
3
Bob Johnson
ORDERS Table
OrderID
CustomerID
Status
OrderDate
101
1
Pending
2023-01-15
102
2
Shipped
2023-02-05
103
1
Pending
2023-02-10
-- Method #1: Using WHERE for join
SELECT OrderID, OrderDate, CustomerName
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustomerID = ORDERS.CustomerID
  AND ORDERS.Status = 'Pending';
-- Method #2: Using INNER JOIN
SELECT OrderID, OrderDate, CustomerName
FROM CUSTOMER INNER JOIN ORDERS
ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.Status = 'Pending';
Expected Output:
OrderID
OrderDate
CustomerName
101
2023-01-15
John Doe
103
2023-02-10
John Doe

Example: Holiday Company Database (9618/01/SP/21)

Holiday company has several members of staff. Database has two additional tables to store data about the staff:

STAFF(StaffID, FirstName, SecondName, DateOfBirth, Role, Salary)
SCHEDULE(ScheduleID, StaffID, WorkDate, Morning, Afternoon)
-- Display first name and second name of all staff members working on 22/05/2020
SELECT STAFF.FirstName, STAFF.SecondName
FROM STAFF, SCHEDULE
WHERE SCHEDULE.WorkDate = '22/05/2020'
  AND SCHEDULE.StaffID = STAFF.StaffID;

DML Query Simulator

Practice writing DML queries with the sample EMPLOYEE table:

-- Try queries on the EMPLOYEE table -- Example: SELECT * FROM EMPLOYEE; -- Example with WHERE: SELECT * FROM EMPLOYEE WHERE Role = 'Leader';
Query results will appear here...
EMPLOYEE Table Data
EmployeeID
FirstName
LastName
Role
Language
001
Jasmine
Chen
Leader
French
002
Kenton
Archer
Leader
English
003
Michael
Roux
Cook
French
004
Conrad
Slavorski
Leader
Russian
Sample Queries to Try:
  • SELECT * FROM EMPLOYEE; - Select all columns
  • SELECT FirstName, Role FROM EMPLOYEE; - Select specific columns
  • SELECT * FROM EMPLOYEE WHERE Role = 'Leader'; - Filter by role
  • SELECT * FROM EMPLOYEE ORDER BY LastName; - Sort by last name
  • SELECT COUNT(*) FROM EMPLOYEE WHERE Role = 'Leader'; - Count leaders

Real-Life Example: E-commerce Database

An e-commerce website uses SQL DML queries to manage products, orders, and customers:

-- Add new product to inventory
INSERT INTO Products
  (ProductID, Name, Price, Stock)
VALUES (1001, 'Wireless Mouse', 25.99, 50);
-- Update price of a product
UPDATE Products
SET Price = 22.99
WHERE ProductID = 1001;
-- Find all orders from a customer
SELECT o.OrderID, o.OrderDate, p.Name
FROM Orders o
INNER JOIN Products p
ON o.ProductID = p.ProductID
WHERE o.CustomerID = 12345
ORDER BY o.OrderDate DESC;
-- Calculate total sales
SELECT SUM(TotalAmount) FROM Orders;

These DML operations allow the e-commerce system to manage inventory, process orders, and generate reports.

Activity 3: DML Queries Practice

Write SQL DML queries for the following tasks using the EMPLOYEE table:

  1. Select all columns for all employees
  2. Select only the first name and role of employees who are Leaders
  3. Count how many employees speak French
  4. Insert a new employee with EmployeeID 005, FirstName "Sarah", LastName "Wilson", Role "Manager", Language "English"
  5. Update the role of employee with ID 003 from "Cook" to "Head Chef"
  6. Delete the employee with ID 004 from the table
  7. Select all employees sorted by last name in alphabetical order
Solution:
  1. SELECT * FROM EMPLOYEE;
  2. SELECT FirstName, Role FROM EMPLOYEE WHERE Role = 'Leader';
  3. SELECT COUNT(*) FROM EMPLOYEE WHERE Language = 'French';
  4. INSERT INTO EMPLOYEE VALUES (005, 'Sarah', 'Wilson', 'Manager', 'English');
  5. UPDATE EMPLOYEE SET Role = 'Head Chef' WHERE EmployeeID = 003;
  6. DELETE FROM EMPLOYEE WHERE EmployeeID = 004;
  7. SELECT * FROM EMPLOYEE ORDER BY LastName;

Check Your Understanding: DML

Answer
  • [1 mark] SELECT - retrieves/reads data from database
  • [1 mark] INSERT INTO - adds new data/rows to tables
  • [1 mark] UPDATE - modifies existing data in tables
  • [1 mark] DELETE FROM - removes data/rows from tables
  • [Additional] DML is used for ongoing maintenance and data manipulation after DDL creates the structure
Answer
  • [1 mark] DELETE FROM is a DML command that removes rows/data from a table but keeps the table structure
  • [1 mark] DROP TABLE is a DDL command that completely removes the entire table structure and all its data
  • [Additional] DELETE FROM can be undone (rolled back) in some databases, while DROP TABLE is permanent
Answer
SELECT *
FROM Students
WHERE Marks > 70
ORDER BY Marks DESC;

[3 marks] Correct SELECT, WHERE with condition, and ORDER BY with DESC

Answer
  • [1 mark] GROUP BY clause organizes identical data into groups based on specified column(s)
  • [1 mark] It is often used with aggregate functions (SUM, COUNT, AVG, etc.)
  • [1 mark] Example: To find total sales for each product category, you would use GROUP BY on "Product Category"
  • [Additional] Ensures values in specified field(s) are not repeated in result set; groups based on unique values
Answer
UPDATE Products
SET Price = Price * 1.10
WHERE Category = 'Electronics';

[3 marks] Correct UPDATE with SET using calculation, and WHERE to filter by category

Key Takeaways

  • SQL (Structured Query Language) is the industry standard language for relational database management
  • DDL (Data Definition Language) is used to create and modify database structure (CREATE, ALTER, DROP)
  • DML (Data Manipulation Language) is used to work with data in databases (SELECT, INSERT, UPDATE, DELETE)
  • CREATE DATABASE creates a new database; DROP DATABASE permanently removes it
  • CREATE TABLE defines a new table with columns, data types, and constraints
  • ALTER TABLE modifies an existing table structure (add, modify, or delete columns)
  • PRIMARY KEY uniquely identifies each record in a table; values must be unique and NOT NULL
  • FOREIGN KEY creates relationships between tables by referencing PRIMARY KEY of another table
  • Common SQL data types include CHARACTER, VARCHAR(n), INTEGER, REAL, BOOLEAN, DATE, TIME
  • SELECT queries retrieve data using clauses: WHERE (filtering), ORDER BY (sorting), GROUP BY (grouping)
  • Aggregate functions like SUM(), COUNT(), AVG() perform calculations on data sets
  • INNER JOIN combines rows from different tables based on related columns
  • INSERT INTO adds new rows; UPDATE modifies existing rows; DELETE FROM removes rows
  • Always use WHERE clause with UPDATE and DELETE unless you intend to modify all rows
  • SQL keywords are not case sensitive, but convention uses uppercase for SQL commands
  • Commands end with a semicolon (;) and can span multiple lines for readability

Question Bank

Marking Scheme & Answer
  • [2 marks] DDL (Data Definition Language): Used to create and modify database structure. Works on schema/structure level, not data.
  • [1 mark] DDL Examples: CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE
  • [2 marks] DML (Data Manipulation Language): Used to work with data within database structures. Performs CRUD operations on data.
  • [1 mark] DML Examples: SELECT, INSERT INTO, UPDATE, DELETE FROM
  • [Additional] DDL creates the "container" (tables, databases), DML works with the "contents" (data in those tables)
Marking Scheme & Answer
-- a) Create database
CREATE DATABASE School;

-- b) Create Students table with columns
CREATE TABLE Students (
  StudentID INTEGER,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  DateOfBirth DATE,
  Grade CHAR(2),
  AverageMark REAL
);

-- c) Add primary key (alternative: include in CREATE TABLE)
ALTER TABLE Students
ADD PRIMARY KEY (StudentID);

-- d) Insert sample records
INSERT INTO Students VALUES
  (1001, 'John', 'Smith', '2005-03-15', '10A', 85.5);
INSERT INTO Students VALUES
  (1002, 'Sarah', 'Jones', '2005-07-22', '10B', 92.0);

[8 marks] 2 marks each for correct syntax of CREATE DATABASE, CREATE TABLE, ALTER TABLE with PRIMARY KEY, and INSERT INTO commands

Marking Scheme & Answer
  • [2 marks] Primary Key: Uniquely identifies each record in its own table. Must be unique and NOT NULL. Each table has one primary key.
  • [2 marks] Foreign Key: Creates relationship between tables by referencing primary key of another table. Can have duplicate values and NULLs. A table can have multiple foreign keys.
  • [1 mark] SQL Example:
    ALTER TABLE Orders
    ADD FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID);
  • [Additional] Foreign keys enforce referential integrity - cannot have value that doesn't exist in referenced table's primary key
Marking Scheme & Answer
-- a) Select products with price > £50
SELECT * FROM Products
WHERE Price > 50;

-- b) Count products in "Electronics" category
SELECT COUNT(*)
FROM Products
WHERE Category = 'Electronics';

-- c) Update price of product ID 101
UPDATE Products
SET Price = 75
WHERE ProductID = 101;

-- d) Delete discontinued products
DELETE FROM Products
WHERE Discontinued = TRUE;

[8 marks] 2 marks each for correct SELECT with WHERE, COUNT with WHERE, UPDATE with SET and WHERE, DELETE with WHERE

Marking Scheme & Answer
  • [1 mark] Selects Department and the average Salary from the Employees table
  • [1 mark] Groups the results by Department (one row per unique department)
  • [1 mark] Calculates the average salary for each department using AVG() function
  • [1 mark] Orders the results by average salary in descending order (highest average salary first)
  • [Additional] This query would show which departments have the highest average salaries, useful for HR analysis
Marking Scheme & Answer
  • [2 marks] INNER JOIN: Combines rows from two or more tables based on a related column between them. Returns only rows that have matching values in both tables.
  • [1 mark] When to use: When you need data from multiple related tables (e.g., customer details with their orders)
  • [2 marks] Example:
    SELECT Customers.Name, Orders.OrderDate, Orders.Total
    FROM Customers
    INNER JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
    WHERE Orders.Total > 100;
  • [Additional] Alternative syntax uses WHERE: SELECT ... FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID
Marking Scheme & Answer
  • [1 mark] UPDATE without WHERE will modify all rows in the table (e.g., setting all prices to the same value)
  • [1 mark] DELETE without WHERE will remove all rows from the table (emptying the table completely)
  • [1 mark] These operations are often irreversible and can cause permanent data loss if not backed up
  • [Additional] Always test UPDATE/DELETE with SELECT first: SELECT * FROM table WHERE condition to see which rows will be affected
Marking Scheme & Answer
CREATE DATABASE Library;

CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  Title VARCHAR(200) NOT NULL,
  Author VARCHAR(100),
  ISBN CHAR(13),
  PublishedYear INTEGER,
  Available BOOLEAN DEFAULT TRUE
);

CREATE TABLE Members (
  MemberID INTEGER PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  JoinDate DATE,
  Email VARCHAR(100)
);

CREATE TABLE Loans (
  LoanID INTEGER PRIMARY KEY,
  BookID INTEGER,
  MemberID INTEGER,
  LoanDate DATE,
  ReturnDate DATE,
  FOREIGN KEY (BookID) REFERENCES Books(BookID),
  FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

[8 marks] 1 mark for CREATE DATABASE, 2 marks each for CREATE TABLE with appropriate columns/data types, 1 mark for primary keys, 2 marks for foreign key relationships

Marking Scheme & Answer
  • [1 mark] Purpose: ORDER BY clause sorts the result set of a query in either ascending or descending order based on one or more columns
  • [1 mark] Ascending order: Default sorting order. Use ORDER BY column_name or ORDER BY column_name ASC
  • [1 mark] Descending order: Use ORDER BY column_name DESC keyword
  • [Additional] Example: ORDER BY Salary DESC sorts employees by highest salary first; ORDER BY LastName sorts alphabetically A-Z
Marking Scheme & Answer
  • [2 marks] CHAR (CHARACTER): Fixed-length text. Always uses specified storage space, padding with spaces if shorter. Faster for fixed-length data.
  • [1 mark] Use CHAR when: Data length is always the same (e.g., country codes like 'UK' = CHAR(2), gender 'M'/'F' = CHAR(1), ISBN numbers = CHAR(13))
  • [2 marks] VARCHAR (Variable Character): Variable-length text. Uses only required storage space. More space-efficient for variable data.
  • [1 mark] Use VARCHAR when: Data length varies (e.g., names, addresses, descriptions, email addresses)
  • [Additional] Example: 'ABC' stored as CHAR(5) = 'ABC ' (2 spaces), as VARCHAR(5) = 'ABC'