Structured Query Language (SQL)

Welcome to Relational Databases and Structured Query Language (SQL)!

Hello future computer scientists! This chapter is all about Relational Databases and SQL (Structured Query Language). Don't worry if the names sound complex—SQL is simply the standard language used to interact with and query relational databases.

Relational Databases and Keys

A relational database stores data in multiple related tables (also known as relations) to eliminate data redundancy (duplicate data) and maintain data consistency.

• A Table holds data about a single entity (e.g. Students, Courses).
• A Record (or Row) is one complete entry containing all attributes for that entity.
• A Field (or Column) is a specific attribute of the entity (e.g. StudentName, DateOfBirth).
• A Primary Key is a field (or combination of fields) that uniquely identifies each record in a table.
• A Foreign Key is a field in one table that links to the primary key of another table, establishing a relationship between them.

Database Schema Notation

In database design, tables are represented using the standard notation with the primary key underlined:

Student(StudentID, StudentName, DateOfBirth, CourseID)
Course(CourseID, CourseTitle, TeacherName)

Here, StudentID is the primary key in Student, CourseID is the primary key in Course, and CourseID inside Student serves as the foreign key linking each student to their course.

Section 1: What is SQL and Its Purpose?

SQL (Structured Query Language) is a declarative language used to create, query, and manipulate data stored in a Relational Database Management System (RDBMS).

SQL commands are divided into two main categories:

1. Data Definition Language (DDL)

DDL commands define or remove database structures, such as creating or dropping tables.

2. Data Manipulation Language (DML)

DML commands manipulate the data stored inside those structures—including querying, inserting, updating, and deleting records.

Section 2: Data Definition Language (DDL) Commands

1. Creating a Table: CREATE TABLE

This command sets up a new table along with its field names and data types (such as INTEGER or TEXT).

CREATE TABLE Books (
    BookID INTEGER,
    Title TEXT,
    Author TEXT,
    PublicationYear INTEGER
);

2. Deleting a Table: DROP TABLE

This command permanently removes an entire table and all its stored data.

DROP TABLE Books;

Section 3: Querying Data (DML)

1. Core Query Structure: SELECT and FROM

• SELECT specifies the fields (columns) to retrieve.
• FROM specifies the table(s) from which to retrieve data.
• An asterisk (*) serves as a wildcard to select all fields.

SELECT StudentName, Email
FROM Students;

2. Filtering Records: WHERE

The WHERE clause filters query results using comparison operators: =, >, <, >=, <=, and <> (or !=).

Text literals in conditions must be enclosed in single quotes (e.g. 'London').

SELECT *
FROM Students
WHERE Grade = 'A';

3. Logical Operators: AND, OR, NOT

Multiple search criteria can be combined in the WHERE clause:

SELECT StudentName
FROM Students
WHERE Grade = 'A' AND City = 'London';

4. Sorting Records: ORDER BY

The ORDER BY clause sorts the output in ascending (ASC, default) or descending (DESC) order.

SELECT StudentName, Grade
FROM Students
ORDER BY StudentName ASC;

5. Multi-Table Queries (Joining Two Tables)

In exam questions, queries may require extracting data from up to two tables. To join tables, list both in the FROM clause and match the primary key of one table to the foreign key of the other in the WHERE clause.

SELECT Student.StudentName, Course.CourseTitle
FROM Student, Course
WHERE Student.CourseID = Course.CourseID;

Section 4: Modifying Data (DML)

1. Adding Records: INSERT INTO

Inserts a new record into an existing table.

INSERT INTO Books (BookID, Title, Author)
VALUES (101, 'The Great Adventure', 'A. N. Author');

2. Modifying Records: UPDATE

Modifies existing data. Use SET to define new values and WHERE to specify which records to update.

UPDATE Students
SET Grade = 'A*'
WHERE StudentID = 42;

Warning: Omitting the WHERE clause will update every record in the table!

3. Removing Records: DELETE FROM

Deletes specific records from a table based on a condition.

DELETE FROM Students
WHERE City = 'London';

Warning: Omitting the WHERE clause removes all records while leaving the table structure intact.