SQL (OCR A-Level Computer Science): Revision Notes

SQL

Overview

SQL (Structured Query Language) is the standard language for managing and manipulating data in relational databases. It allows users to create, read, update, and delete data through structured commands. SQL is essential in databases because it provides a consistent syntax for interacting with data across different database systems, like MySQL, PostgreSQL, and SQL Server.

Importance of SQL as a Standard Language

SQL's role as a standardised language ensures that:

• Database queries are consistent across different database systems.
• Database developers can work with various SQL-based systems without needing to learn entirely new languages.
• Data is easily accessible and modifiable through SQL commands, enabling flexible data management.

Basic SQL Commands

SQL commands can be divided into four main categories:

1. Data Definition Language (DDL): Used to define or modify database structure.
2. Data Manipulation Language (DML): Used to manipulate data within tables.
3. Data Query Language (DQL): Used to query and retrieve data.
4. Data Control Language (DCL): Used to control access to data.

Data Manipulation and Definition Examples

1. CREATE TABLE - Creating a New Table

Creates a table with specified columns and data types.

CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    City VARCHAR(50)
);

2. INSERT INTO - Adding Data

Adds a new record to a table.

INSERT INTO Students (Student_ID, Name, Age, City)
VALUES (101, 'Alice Smith', 20, 'New York');

3. SELECT - Retrieving Data

Fetches records from a table based on specified criteria.

SELECT Name, Age
FROM Students
WHERE City = 'New York';

4. UPDATE - Modifying Data

Updates existing records in a table.

UPDATE Students
SET Age = 21
WHERE Student_ID = 101;

5. DELETE - Removing Data

Deletes records from a table based on specified conditions.

DELETE FROM Students
WHERE Student_ID = 101;

6. ALTER TABLE - Modifying Table Structure

Used to add, delete, or modify columns in an existing table.

ALTER TABLE Students
ADD Email VARCHAR(100);

Common SQL Queries and Functions

Filtering with WHERE: Retrieves specific rows by setting conditions.

SELECT * FROM Students
WHERE Age > 18;

Sorting with ORDER BY: Orders results by one or more columns.

SELECT * FROM Students
ORDER BY Age DESC;

Using Aggregates: Functions like COUNT, AVG, SUM, MAX, and MIN for calculations.

SELECT AVG(Age) AS AverageAge
FROM Students;

Grouping with GROUP BY: Groups rows sharing a specified column value.

SELECT City, COUNT(Student_ID) AS NumStudents
FROM Students
GROUP BY City;

Joining Tables: Combines rows from two or more tables based on related columns.

Example: INNER JOIN for showing data where there's a match in both tables.

SELECT Students.Name, Courses.Course_Name
FROM Students
INNER JOIN Enrolments ON Students.Student_ID = Enrolments.Student_ID
INNER JOIN Courses ON Enrolments.Course_ID = Courses.Course_ID;

Practical Example of Modifying Data with SQL

Consider a school database with tables for Students, Courses, and Enrollments. Below are some examples of SQL commands to interact with this data.

Add a New Student to the Students Table

INSERT INTO Students (Student_ID, Name, Age, City)
VALUES (102, 'Bob Jones', 19, 'London');

Change a Student's Age

UPDATE Students
SET Age = 20
WHERE Student_ID = 102;

Retrieve Students in a Specific City

SELECT Name
FROM Students
WHERE City = 'London';

Remove a Student from the Database

DELETE FROM Students
WHERE Student_ID = 102;

Note Summary