Chapter 8.3: SQL (DDL & DML)

9618 AS Computer Science

📚 Learning Objectives

Show understanding that DBMS carries out all creation/modification of database structure using its Data Definition Language (DDL)
Show understanding that DBMS carries out all queries and maintenance of data using DML
Show understanding that industry standard for both DDL and DML is SQL
Understand given SQL (DDL) commands and be able to write simple SQL (DDL) commands
Create database (CREATE DATABASE) and create table (CREATE TABLE) with appropriate data types
Write SQL script to query or modify data (DML) stored in database tables
Use queries including SELECT, FROM, WHERE, ORDER BY, GROUP BY, INNER JOIN
Use aggregate functions: SUM, COUNT, AVG
Perform data maintenance: INSERT INTO, DELETE FROM, UPDATE

📋 Prior Knowledge Required

Understanding of relational database concepts
Knowledge of tables, records, fields, and primary keys
Understanding of foreign keys and relationships between tables
Basic understanding of data types
Familiarity with database management systems (DBMS)

🌟 Did You Know?

SQL (Structured Query Language) is the industry standard language for interacting with relational databases. SQLite and MySQL are popular open-source database applications. SQL is designed so that many users can access it simultaneously and has high capacity for storage space!

1. Introduction to SQL

SQL (Structured Query Language) is a programming language used to search and query databases. It is provided by the DBMS to support all operations associated with a relational database.

📖 What is SQL?

A programming language for interacting with relational databases
Used for sorting, manipulating, and retrieving data
Offers high-level software tools for user interaction
Allows users to customize their queries
Enables multiple users to access data simultaneously

1.1 SQL Features

📝 Key Features of 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 = select)
Use UPPER CASE for commands, lower case for table/attribute names
Lists of items are separated by commas

Example: "SELECT these fields FROM this table WHERE this is happening" - The statements are similar to natural language, making SQL easier to remember!

1.2 DDL vs DML Overview

Aspect	DDL (Data Definition Language)	DML (Data Manipulation Language)
Purpose	Define database structure	Manipulate data within database
Operations	Create, modify, remove structures	Insert, update, delete, retrieve data
Commands	CREATE, ALTER, DROP	SELECT, INSERT, UPDATE, DELETE
Data Entry	No data entered into database	Populates and maintains data

2. Data Definition Language (DDL)

DDL is part of SQL used to create, modify, and remove data structures that form a relational database. These commands only create structure - they do not put any data into the database.

2.1 CREATE DATABASE

To create a database, the standard command CREATE DATABASE is used.

CREATE DATABASE ;

-- Example:

CREATE DATABASE SchoolDB;

CREATE DATABASE BandBooking;

💡 Exam Tip

Database names should be relevant and usually should not have spaces. Use descriptive names like "SchoolDB", "CustomerRecords", or "InventorySystem".

2.2 CREATE TABLE

The most common DDL statement is CREATE TABLE. It creates a new table with specified fields and data types.

CREATE TABLE  (

    field1 datatype,

    field2 datatype,

    ...

    PRIMARY KEY (field)

);

📝 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)

);

NOT NULL means the field is not allowed to have NULL values - it must contain data.

3. SQL Data Types

When creating tables, each field must have a data type that defines what kind of data it can store.

Data Type	Description	Example
CHARACTER	Fixed-length text string	Gender CHARACTER(1)
VARCHAR(n)	Variable-length text (max n characters)	Name VARCHAR(50)
BOOLEAN	True or False value	IsActive BOOLEAN
INTEGER	Whole numbers	Age INTEGER
REAL	Decimal/floating-point numbers	Price REAL
DATE	Calendar date	DateOfBirth DATE
TIME	Time of day	StartTime TIME

⚠️ CHAR vs VARCHAR - Important Distinction!

CHAR: Fixed-length - stores exact number of characters, pads with spaces if shorter. Best for consistent lengths (e.g., postcodes, phone numbers)
VARCHAR: Variable-length - stores only the actual characters used. Best for varying lengths (e.g., names, addresses)

📝 Example: BIRD_TYPE Table (Past Paper Question)

CREATE TABLE BIRD_TYPE (

    BirdID CHAR(4) NOT NULL,

    Name VARCHAR(9),

    Size VARCHAR(6),

    PRIMARY KEY (BirdID)

);

BirdID uses CHAR(4) because IDs are always exactly 4 characters (e.g., "0123"). Name and Size use VARCHAR because they vary in length.

4. ALTER TABLE and Keys

The ALTER TABLE command is used to modify the structure of an existing table. This includes adding, modifying, or deleting fields, and adding constraints.

4.1 ALTER TABLE Operations

📝 Common ALTER TABLE Operations

Add a new field:

ALTER TABLE Student ADD Address VARCHAR(25);

Change data type of a field:

ALTER TABLE Product MODIFY COLUMN Quantity INTEGER;

Delete a field:

ALTER TABLE Stock DROP Quantity;

4.2 PRIMARY KEY

A PRIMARY KEY is a field that uniquely identifies each record in a table. It cannot contain NULL values and must be unique.

📖 Adding Primary Keys

During table creation:

CREATE TABLE Students (

    StudentID INTEGER,

    Name VARCHAR(50),

    PRIMARY KEY (StudentID)

);

To existing table:

ALTER TABLE Student ADD PRIMARY KEY (StudentID);

4.3 FOREIGN KEY

A FOREIGN KEY is a field that links to the primary key of another table, creating a relationship between tables.

📝 Adding Foreign Keys

ALTER TABLE Orders

ADD FOREIGN KEY (Cust_ID) REFERENCES Customer(Cust_ID);

This creates a link where Orders.Cust_ID references Customer.Cust_ID

💡 Exam Tip

Remember: PK = Primary Key (unique identifier in its own table), FK = Foreign Key (links to PK of another table). A customer can have many orders (1:N relationship)!

5. Data Manipulation Language (DML)

DML is used to manipulate data within database structures. It deals with adding, updating, deleting, and retrieving data.

📖 DML Purpose

Insertion of data into tables when database is created
Modification or removal of data in database
Reading of data stored in database
DDL structures the database; DML maintains the contents

5.1 SELECT ... FROM

The SELECT statement retrieves data from a database. It is the most commonly used DML command.

📝 SELECT Syntax

-- Select specific columns:

SELECT column1, column2 FROM table_name;

-- Select all columns:

SELECT * FROM table_name;

-- Examples:

SELECT FirstName, LastName FROM Students;

SELECT * FROM Students;

5.2 WHERE Clause

The WHERE clause filters results based on a condition. Only records that meet the condition are returned.

SELECT * FROM Students WHERE Age > 16;

SELECT Title FROM Programmes

WHERE Title = 'The Voice';

SELECT * FROM Products WHERE Price < 50;

⚠️ Operators in WHERE Clause

You can use these operators with WHERE:

= Equal to
<> or != Not equal to
> Greater than, < Less than
>= Greater than or equal, <= Less than or equal
IS NULL Checks for NULL value

6. ORDER BY and GROUP BY

6.1 ORDER BY

The ORDER BY keyword sorts the result set in either ascending (ASC) or descending (DESC) order.

📝 ORDER BY Syntax

-- Sort ascending (default):

SELECT * FROM Students ORDER BY LastName ASC;

-- Sort descending:

SELECT * FROM Students ORDER BY Age DESC;

-- Sort by multiple columns:

SELECT * FROM Students ORDER BY LastName, FirstName;

6.2 Boolean Operators: AND, OR, NOT

Boolean operators can be used with WHERE to combine multiple conditions.

Operator	Meaning	Example
AND	Both conditions must be true	WHERE Age > 16 AND City = 'London'
OR	At least one condition must be true	WHERE Language = 'French' OR Language = 'English'
NOT	Negates the condition	WHERE NOT IsActive = TRUE

-- Find students older than 16 AND living in London:

SELECT * FROM Students WHERE Age > 16 AND City = 'London';

-- Find employees who speak French OR English:

SELECT FirstName, LastName FROM Employee

WHERE Language = 'French' OR Language = 'English';

6.3 GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns. It is often used with aggregate functions.

-- Count students in each class:

SELECT ClassID, COUNT(*) FROM Students GROUP BY ClassID;

-- Count orders per customer:

SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

💡 Exam Tip

ORDER BY sorts results; GROUP BY groups identical values together. GROUP BY removes duplicates from the specified column and is used with aggregate functions like COUNT, SUM, AVG.

7. Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. They are often used with GROUP BY.

Function	Description	Example
SUM()	Returns the sum of numeric column	SELECT SUM(Credits) FROM Courses;
COUNT()	Counts number of rows/non-null values	SELECT COUNT(*) FROM Students;
AVG()	Returns average of numeric column	SELECT AVG(Age) FROM Students;

📝 Aggregate Function Examples

SUM - Total of values:

SELECT SUM(Price) FROM Products;

SELECT SUM(CS_Test) FROM Test WHERE Age > 16;

COUNT - Count records:

SELECT COUNT(*) FROM Enrolments;

SELECT COUNT(StaffID) FROM Schedule

WHERE WorkDate = '26/05/2020' AND Morning = TRUE;

AVG - Average value:

SELECT AVG(Age) FROM Students;

SELECT AVG(Salary) FROM Employee WHERE Department = 'IT';

❌ Common Mistake

Remember: COUNT(*) counts ALL rows including NULLs. COUNT(column) only counts non-NULL values in that column. Use the appropriate one for your needs!

8. INNER JOIN

INNER JOIN combines rows from two or more tables based on a related column between them. It returns only rows that have matching values in BOTH tables.

📖 INNER JOIN Purpose

Combine data from multiple related tables
Use when you need information from different tables in one query
Only returns matching rows (not all rows from either table)
Requires a common field (usually foreign key)

8.1 INNER JOIN Syntax

📝 Two Methods for INNER JOIN

Method 1: Using WHERE clause (older style):

SELECT column(s)

FROM table1, table2

WHERE table1.column = table2.column;

Method 2: Using INNER JOIN keyword (preferred):

SELECT column(s)

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

📝 Complete INNER JOIN Example

-- Find customer names with their order details:

SELECT OrderID, OrderDate, CustomerName

FROM CUSTOMER

INNER JOIN ORDERS

ON CUSTOMER.CustomerID = ORDERS.CustomerID

WHERE ORDERS.Status = 'Pending';

💡 Exam Tip

When writing INNER JOIN queries, always specify which table each column comes from using table.column notation (e.g., CUSTOMER.CustomerID). This prevents ambiguity when both tables have columns with the same name!

9. Data Maintenance Commands

These DML commands are used to maintain data in the database - adding new records, modifying existing ones, or removing records.

9.1 INSERT INTO

The INSERT INTO statement adds new records to a table.

📝 INSERT INTO Syntax

-- With column names specified:

INSERT INTO table_name (col1, col2, col3)

VALUES (val1, val2, val3);

-- Example:

INSERT INTO Students (StudentID, FirstName, LastName, Age)

VALUES (4, 'Amira', 'Patel', 17);

-- If inserting all fields in order:

INSERT INTO Employee

VALUES ('mrkashif42', 'Kashif', '34', 12, 'Islamabad');

⚠️ Important Notes for INSERT

String values must be enclosed in quotes (single or double)
Numeric values do NOT have quotes
Date values should be in quotes: '2025-01-15'
Order of values must match order of columns

9.2 UPDATE

The UPDATE statement modifies existing data in a table.

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

-- Example: Change a student's age:

UPDATE Students SET Age = 18 WHERE StudentID = 4;

-- Example: Update car colour:

UPDATE Cars SET Colour = 'Red'

WHERE RegNo = 'MH09RCM';

9.3 DELETE FROM

The DELETE FROM statement removes records from a table.

DELETE FROM table_name WHERE condition;

-- Example: Delete a specific student:

DELETE FROM Students WHERE StudentID = 4;

-- Example: Delete by name:

DELETE FROM Employee WHERE Employee_ID = 'mrkashif42';

❌ Critical Warning!

ALWAYS include the WHERE clause!

DELETE FROM Students - Deletes ALL records!
DELETE FROM Students WHERE StudentID = 4 - Deletes only one record
Same applies to UPDATE - without WHERE, ALL records are affected!

10. Exam-Style Questions

1. Part of a database table BIRD_TYPE is shown:

BirdID: 0123, Name: Blackbird, Size: Medium
BirdID: 0035, Name: Jay, Size: Large
BirdID: 0004, Name: Raven, Size: Large
BirdID: 0085, Name: Robin, Size: Small

Write an SQL script to define the table BIRD_TYPE. The database supports: character, varchar, boolean, integer, real, date, time. [4 marks]

Answer:

CREATE TABLE BIRD_TYPE (

    BirdID CHAR(4) NOT NULL,

    Name VARCHAR(9),

    Size VARCHAR(6),

    PRIMARY KEY (BirdID)

);

Mark allocation:

CREATE TABLE with brackets [1 mark]
BirdID as CHAR or VARCHAR with appropriate size [1 mark]
Name and Size as VARCHAR or CHAR with appropriate sizes [1 mark]
BirdID as PRIMARY KEY [1 mark]

Additional points for deeper understanding:

CHAR(4) is appropriate because BirdID is always exactly 4 characters
NOT NULL ensures BirdID cannot be empty (good practice for primary keys)
Primary key must be unique and identify each record

2. A teacher uses a relational database MARKS to store data about students and their test marks. Part of the STUDENT_TEST table structure shows:
- StudentID (CHAR(5)) - Primary key
- TestID (CHAR(4)) - Part of composite key
- TestScore (INTEGER)

Write an SQL script to create the table STUDENT_TEST. [5 marks]

Answer:

CREATE TABLE STUDENT_TEST (

    StudentID CHAR(5) NOT NULL,

    TestID CHAR(4) NOT NULL,

    TestScore INTEGER,

    PRIMARY KEY (StudentID, TestID)

);

Mark allocation:

CREATE TABLE with correct syntax and brackets [1 mark]
StudentID correctly defined as CHAR(5) [1 mark]
TestID correctly defined as CHAR(4) [1 mark]
TestScore defined as INTEGER [1 mark]
Composite PRIMARY KEY correctly defined [1 mark]

3. Part of the EMPLOYEE table is shown with columns: FirstName, LastName, Language, IsLeader.

Write a DML statement to return the first name and last name of all employees who are leaders AND speak either French OR English. [4 marks]

Answer:

SELECT FirstName, LastName

FROM Employee

WHERE IsLeader = TRUE

AND (Language = 'French' OR Language = 'English');

Mark allocation:

SELECT FirstName, LastName [1 mark]
FROM Employee [1 mark]
WHERE with IsLeader condition [1 mark]
AND with OR conditions for languages [1 mark]

Additional points for deeper understanding:

Brackets around OR conditions ensure correct logical order
Boolean values can use TRUE/FALSE or 1/0
Alternative: WHERE IsLeader AND Language IN ('French', 'English')

10. Exam-Style Questions (continued)

4. A holiday company database has two tables: STAFF (StaffID, FirstName, SecondName) and SCHEDULE (StaffID, WorkDate, Morning, Afternoon). Write an SQL script to display the first name and second name of all staff members working on 22/05/2020. [4 marks]

Answer:

SELECT STAFF.FirstName, STAFF.SecondName

FROM STAFF, SCHEDULE

WHERE SCHEDULE.WorkDate = '22/05/2020'

AND SCHEDULE.StaffID = STAFF.StaffID;

Alternative using INNER JOIN:

SELECT STAFF.FirstName, STAFF.SecondName

FROM STAFF

INNER JOIN SCHEDULE

ON STAFF.StaffID = SCHEDULE.StaffID

WHERE SCHEDULE.WorkDate = '22/05/2020';

Mark allocation:

SELECT with correct column names and table prefix [1 mark]
FROM clause with both tables [1 mark]
WHERE with date condition [1 mark]
Join condition linking StaffID from both tables [1 mark]

5. The SCHEDULE table stores staff schedules with fields: StaffID, WorkDate, Morning (BOOLEAN), Afternoon (BOOLEAN). Write an SQL script to count the number of people working in the morning of 26/05/2020. [3 marks]

Answer:

SELECT COUNT(StaffID)

FROM SCHEDULE

WHERE WorkDate = '26/05/2020'

AND Morning = TRUE;

Mark allocation:

SELECT COUNT(StaffID) or COUNT(*) [1 mark]
FROM SCHEDULE with WHERE date condition [1 mark]
AND Morning = TRUE (or Morning = 1) [1 mark]

6. A student wants to add a new record to the Students table with the following values: StudentID = 5, FirstName = 'Ahmed', LastName = 'Hassan', Age = 17. Write the SQL statement. [3 marks]

Answer:

INSERT INTO Students (StudentID, FirstName, LastName, Age)

VALUES (5, 'Ahmed', 'Hassan', 17);

Mark allocation:

INSERT INTO with table name and column list [1 mark]
VALUES keyword with brackets [1 mark]
Correct values (strings in quotes, numbers without) [1 mark]

Additional points for deeper understanding:

String values MUST be in quotes: 'Ahmed', 'Hassan'
Numeric values do NOT have quotes: 5, 17
Order of values must match order of columns

10. Exam-Style Questions (continued)

7. A database contains a Products table with columns: ProductID, ProductName, Price, Category. Write an SQL statement to calculate the average price of all products in the 'Electronics' category. [3 marks]

Answer:

SELECT AVG(Price)

FROM Products

WHERE Category = 'Electronics';

Mark allocation:

SELECT AVG(Price) - correct use of aggregate function [1 mark]
FROM Products [1 mark]
WHERE Category = 'Electronics' [1 mark]

Additional points for deeper understanding:

AVG returns a single calculated value
Only numeric columns can use AVG
NULL values are excluded from calculation

8. Write an SQL statement to list all students sorted by their surname in alphabetical order, and then by their first name. [3 marks]

Answer:

SELECT *

FROM Students

ORDER BY LastName ASC, FirstName ASC;

Mark allocation:

SELECT * FROM Students [1 mark]
ORDER BY LastName [1 mark]
Also ordering by FirstName (ASC optional) [1 mark]

Additional points for deeper understanding:

ASC is default (ascending), DESC for descending
First column takes priority in sorting
Second column only used when first column values are identical

9. A school database has tables: STUDENTS(StudentID, Name, ClassID) and CLASSES(ClassID, ClassName, TeacherID). Write an SQL query using INNER JOIN to display student names alongside their class names. [5 marks]

Answer:

SELECT STUDENTS.Name, CLASSES.ClassName

FROM STUDENTS

INNER JOIN CLASSES

ON STUDENTS.ClassID = CLASSES.ClassID;

Mark allocation:

SELECT with both column names [1 mark]
FROM STUDENTS [1 mark]
INNER JOIN CLASSES [1 mark]
ON keyword [1 mark]
Correct join condition with table.column format [1 mark]

Additional points for deeper understanding:

Table prefixes prevent ambiguity when column names are the same
INNER JOIN only returns rows with matching values in both tables
Alternative: WHERE STUDENTS.ClassID = CLASSES.ClassID

10. Write an SQL statement to update the price of product with ProductID 'P101' from $25 to $30 in the Products table. [3 marks]

Answer:

UPDATE Products

SET Price = 30

WHERE ProductID = 'P101';

Mark allocation:

UPDATE Products [1 mark]
SET Price = 30 [1 mark]
WHERE ProductID = 'P101' [1 mark]

Additional points for deeper understanding:

WITHOUT WHERE clause, ALL prices would be changed!
ProductID is a string, so it needs quotes
Price is numeric, so no quotes needed

11. Glossary

Term	Definition
SQL	Structured Query Language - the industry standard language for interacting with relational databases
DDL	Data Definition Language - SQL commands that define database structure (CREATE, ALTER, DROP)
DML	Data Manipulation Language - SQL commands that manipulate data within tables (SELECT, INSERT, UPDATE, DELETE)
Primary Key	A field that uniquely identifies each record in a table; cannot be NULL and must be unique
Foreign Key	A field that links to the primary key of another table, creating a relationship
CHAR	Fixed-length character data type - stores exactly the specified number of characters
VARCHAR	Variable-length character data type - stores up to the specified number of characters
INTEGER	Whole number data type - stores numeric values without decimal places
BOOLEAN	Logical data type - stores TRUE or FALSE values (or 1/0)
INNER JOIN	SQL operation that combines rows from two tables based on matching values in both tables
Aggregate Function	Functions that perform calculations on multiple values and return a single result (SUM, COUNT, AVG)
WHERE Clause	SQL clause that filters records based on specified conditions
ORDER BY	SQL clause that sorts query results in ascending or descending order
GROUP BY	SQL clause that groups rows with identical values, often used with aggregate functions

12. Exam Success Tips

💡 SQL Syntax Essentials

Always end SQL statements with a semicolon (;)
Keywords are NOT case sensitive, but consistency matters
Use UPPERCASE for SQL keywords, lowercase for names
String values MUST be in quotes: 'text'
Numbers do NOT have quotes: 123

💡 DDL vs DML - Know the Difference!

DDL = Data DEFINITION Language → Creates STRUCTURE
DML = Data MANIPULATION Language → Works with DATA
DDL: CREATE, ALTER, DROP (structure commands)
DML: SELECT, INSERT, UPDATE, DELETE (data commands)
DDL doesn't add data - only creates empty structure!

💡 Data Types - Choose Wisely!

CHAR = Fixed length (use for consistent lengths like IDs, postcodes)
VARCHAR = Variable length (use for varying data like names, addresses)
INTEGER = Whole numbers only
REAL = Decimal numbers
BOOLEAN = TRUE/FALSE (or 1/0)

❌ Common Mistakes to Avoid

FORGETTING WHERE clause in UPDATE or DELETE - affects ALL records!
Not using quotes around string values in INSERT/UPDATE
Using quotes around numeric values
Confusing CHAR (fixed) with VARCHAR (variable) length
Forgetting JOIN condition when querying multiple tables
Not including PRIMARY KEY in CREATE TABLE

🧠 Memory Tricks

CRUD = Create, Read, Update, Delete → INSERT, SELECT, UPDATE, DELETE
SELECT FROM WHERE = "What columns?" "Which table?" "Filter condition"
INNER JOIN = Only matching rows from BOTH tables
GROUP BY = GROUP identical values, then aggregate
ORDER BY = Organise results (ASC = A-Z, DESC = Z-A)

13. Key Takeaways

📌 Summary Points

DDL (Data Definition Language)

CREATE DATABASE - Creates a new database
CREATE TABLE - Creates table structure with fields and data types
ALTER TABLE - Modifies table structure (add/modify/delete fields)
DROP - Removes database or table permanently
PRIMARY KEY - Unique identifier for each record
FOREIGN KEY - Links to primary key of another table

DML (Data Manipulation Language)

SELECT ... FROM - Retrieves data from table(s)
WHERE - Filters results based on conditions
ORDER BY - Sorts results (ASC/DESC)
GROUP BY - Groups identical values with aggregates
INNER JOIN - Combines matching rows from two tables
INSERT INTO - Adds new records
UPDATE - Modifies existing records (ALWAYS use WHERE!)
DELETE FROM - Removes records (ALWAYS use WHERE!)

Aggregate Functions

SUM() - Total of numeric values
COUNT() - Number of rows/non-null values
AVG() - Average of numeric values

🌟 Final Reminders

Read questions carefully - identify if it's DDL (structure) or DML (data)
Check mark allocation - it tells you how many points to include
Always specify table.column format for joins
Double-check your WHERE clause in UPDATE and DELETE!
Practice writing SQL statements by hand - exams are handwritten!