Database programming

A3.3.1 Data language types

CREATE TABLE Employees (
    EmployeeID   INT PRIMARY KEY,
    LastName     VARCHAR(20),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);

ALTER TABLE Employees ADD Email VARCHAR(25);
ALTER TABLE Employees DROP COLUMN Email;
CREATE INDEX idx_last_name ON Employees (LastName);
DROP TABLE Employees;

SELECT field1, field2
FROM table_name
WHERE condition
ORDER BY field1 DESC;

Next: queries →

A3.3.2 Queries between two tables

A JOIN combines rows from two tables, usually by matching a foreign key to a primary key.

SELECT Department.DepartmentName, Employees.LastName
FROM Employees
JOIN Department ON Employees.DepartmentID = Department.DepartmentID;

WHERE filters rows before any grouping. HAVING filters groups after grouping, so it goes with GROUP BY.

Next: updates →

A3.3.3 Updating data

INSERT INTO Employees (EmployeeID, LastName) VALUES (8, 'Lim');
UPDATE Employees SET Salary = 40000 WHERE EmployeeID = 8;
DELETE FROM Employees WHERE EmployeeID = 8;

Indexes and updates

Indexes speed up searches, but when an indexed column changes, the index must change too. This can slow the update and block other transactions. To manage this, batch updates, rebuild indexes regularly, or use partial indexes that cover only the most relevant records.

Next: aggregate functions →

Next: SQL lab →

HL A3.3.4 Aggregate functions

With GROUP BY, these work per group, which is useful for reports:

SELECT Department.DepartmentName, COUNT(Employees.EmployeeID) AS ECount
FROM Employees
JOIN Department ON Department.DepartmentID = Employees.DepartmentID
GROUP BY Department.DepartmentName;

Next: views →

HL A3.3.5 Database views

A view is a virtual table based on a SELECT. Different users can see different subsets of the same data.

Advantages

• Hides complexity and simplifies queries.
• Security: limits which fields and records a user sees.
• Data independence: tables can change while the view stays the same for users.
• Consistency: everyone gets data presented in the same way.

A view is updatable only if it is a subset of one table (or updatable view), excluded fields allow NULL, and it has no joins, aggregates, DISTINCT or HAVING. Otherwise it is read-only.

Next: transactions →

HL A3.3.6 Transactions

A transaction is a sequence of operations executed as one unit of work. Four properties define it, known as ACID:

BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 100 WHERE EmployeeID = 1;
UPDATE Employees SET Salary = Salary - 100 WHERE EmployeeID = 5;
COMMIT;      -- or ROLLBACK; to undo both

BEGIN TRANSACTION, COMMIT and ROLLBACK are transaction control language (TCL) commands.

Next: SQL lab →

SQL lab

The tables are Employees(EmployeeID, FirstName, LastName, Salary, ManagerID, DepartmentID) and Department(DepartmentID, DepartmentName). Choose an example, or write your own. All names and figures are made up.

Next: review →

Quick quiz

Exam-style questions

These are written for this site in the style of Paper 1. Answer on paper first, then open the mark points.

1. Outline the difference between DDL and DML. [2]

   Mark points

   • DDL creates, changes and removes structures, such as with CREATE [1].
   • DML adds, changes, deletes and retrieves data, such as with SELECT [1].

2. Tables are Book(BookID, Title, AuthorID) and Author(AuthorID, LastName). Construct a query listing titles by the author “Rao”. [3]

   Mark points

   • SELECT Book.Title FROM Book [1]
   • JOIN Author ON Book.AuthorID = Author.AuthorID [1]
   • WHERE Author.LastName = 'Rao'; [1]

3. Construct an SQL statement to raise the salary of engineer D894 by 300. [2]

   Mark points

   • UPDATE Engineer SET Salary = Salary + 300 [1]
   • WHERE EngineerID = 'D894'; [1]

4. Construct SQL to add an integer field Experience to the Engineer table. [2]

   Mark points

   • ALTER TABLE Engineer [1]
   • ADD Experience INT; [1]

5. Construct SQL to remove all students in year 13 from Student(StudentID, Year). [2]

   Mark points

   • DELETE FROM Student [1]
   • WHERE Year = 13; [1]