Applied Class 5: Basic SQL syntax, DDL and DML

exercises
tutorial
databases
sql
ddl
dml

Practice for 2026-03-30-basic-sql-ddl-and-dml.

Schema — BestTechLtd

BestTechLtd have built a simplistic authorisation management system for secure access control within their organisational network. When a new employee joins, their personal details are stored in Employee. Administrative employees (a specific type of employee, recorded additionally in AdministrativeEmployee) can grant roles to employees through a role-granting process. Each Role comes with specific Permissions that determine which company websites/resources an employee holding that role can access.

Employee             [EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt]
AdministrativeEmployee [EmployeeID, Level, Type]
Role                  [RoleID, Name, Description]
RoleGranting          [EmployeeID, RoleID, AdministrationID, Timestamp]
Permission            [WebsiteURI, RoleID, GrantType, Description]

AdministrativeEmployee.EmployeeID references Employee.EmployeeID
RoleGranting.EmployeeID references Employee.EmployeeID
RoleGranting.RoleID references Role.RoleID
RoleGranting.AdministrationID references AdministrativeEmployee.EmployeeID
Permission.RoleID references Role.RoleID

Section 1 — Basic SQL

Question 1

Return all information (all columns) relating to the Roles recorded in the database.

SELECT * FROM Role;

Question 2

Return the distinct first name and last name of all employees, in descending alphabetical order of their last name.

SELECT DISTINCT FirstName, LastName
FROM   Employee
ORDER BY LastName DESC;

Question 3

Return all the Permission websiteURIs which grant edit permissions to commercial websites (websites ending in .com).

SELECT WebsiteURI
FROM   Permission
WHERE  GrantType = 'Edit'
  AND  WebsiteURI LIKE '%.com';

Question 4 (Challenge)

Create an Employee code, which is the combination of the employee’s first name, last name, and DOB year with syntax [FirstName]-[Lastname]-[yyyy]. Create the code only if a first name, last name, and Date of Birth is present.

SELECT CONCAT(FirstName, '-', LastName, '-', YEAR(DOB))
FROM   Employee
WHERE  1 = 1
  AND  FirstName IS NOT NULL
  AND  LastName IS NOT NULL
  AND  DOB IS NOT NULL;

Section 2 — Data Definition Language (DDL)

Question 5

Before BestTechLtd created their authorisation system, employees were assigned only a single role, plus a unique access token granting access for that role. Create a new relation LegacyEmployee, a specialised type of employee (stored in a separate relation), with:

  • LegacyEmployeeID: a unique reference to the EmployeeID stored in the authorisation database.
  • GrantAccessToken: a string of exactly 64 characters granting access to old websites.
  • RoleID: a reference to the legacy employee’s original role. This reference cannot be null.
CREATE TABLE LegacyEmployee (
    LegacyEmployeeID VARCHAR(8) PRIMARY KEY,
    GrantAccessToken CHAR(64) NOT NULL,
    RoleID           INT NOT NULL,
    FOREIGN KEY (LegacyEmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (RoleID) REFERENCES Role(RoleID)
);

LegacyEmployee is a specialisation of Employee (recall relational-mapping-notation’s Rule 8b: a 1:1 relation with the subclass’s primary key also being a foreign key referencing the superclass).

Question 6 (Challenge)

BestTechLtd want to migrate password credentials from Employee into a separate Credential relation. Create the new table (fields in any order), assuming there is no existing data, and remove the migrated fields from Employee.

CREATE TABLE Credential (
    CredentialID VARCHAR(9) PRIMARY KEY,
    EmployeeID   VARCHAR(8) NOT NULL,
    PasswordHash VARCHAR(128) NOT NULL,
    PasswordSalt VARCHAR(64) NOT NULL,
    Timestamp    DATETIME,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

ALTER TABLE Employee
    DROP COLUMN PasswordHash,
    DROP COLUMN PasswordSalt;

Section 3 — Data Manipulation Language (DML)

Question 7

Revoke all roles granted to EmployeeID E0008.

DELETE FROM RoleGranting
WHERE  EmployeeID = 'E0008';

Question 8

Revoke the role(s) granted to John Stevens at any time during 22 July 2024. Assume such a role exists and only one John Stevens exists.

DELETE FROM RoleGranting
WHERE  EmployeeID = (
    SELECT EmployeeID FROM Employee
    WHERE  FirstName = 'John' AND LastName = 'Stevens'
)
AND    Timestamp BETWEEN '2024-07-22 00:00:00' AND '2024-07-22 23:59:59';

Question 9 (Challenge)

A new employee E0024, James Moran, born 21 November 2001, with a given password hash and salt, is granted all the roles that employee Sofia Gonzalez has, granted by admin E0001 today. Insert the new records (assume only one Sofia Gonzalez exists).

INSERT INTO Employee (EmployeeID, FirstName, LastName, DOB, PasswordHash, PasswordSalt)
VALUES ('E0024', 'James', 'Moran', '2001-11-21',
        'e7cf3ef8d8aac2c1c93963e7a58b7b62ade24d0d0ba2c8ae0f7fb6c8b0aa0332',
        'gmAlpLW8cUj');

INSERT INTO RoleGranting
SELECT 'E0024', RoleID, 'E0001', Timestamp
FROM   RoleGranting
WHERE  EmployeeID = (
    SELECT EmployeeID
    FROM   Employee
    WHERE  FirstName = 'Sofia' AND LastName = 'Gonzalez'
);