Topic 9: Databases — Part 5

05 Worked SQL queries

The Employee_Data table stores the following records:

Example 1 — find the names of all employees in the Tech department:

SELECT Name
FROM Employee_Data
WHERE Department = 'Tech'

Result: Bob, Charlie

Example 2 — list all details for employees earning more than 32,000, sorted by salary from highest to lowest:

SELECT *
FROM Employee_Data
WHERE Salary > 32000
ORDER BY Salary DESCENDING

Result: [S002, Bob, Tech, 42000, FALSE], [S003, Charlie, Tech, 38000, TRUE], [S001, Alice, Sales, 35000, TRUE]

Example 3 — calculate the total salary budget for the Sales department:

SELECT SUM(Salary)
FROM Employee_Data
WHERE Department = 'Sales'

Result: 66000 (35000 + 31000)

Example 4 — count how many employees work remotely:

SELECT COUNT(StaffID)
FROM Employee_Data
WHERE RemoteWork = TRUE

Result: 2 (Alice and Charlie)

Example 5 — combine conditions with AND and OR:

SELECT Name
FROM Employee_Data
WHERE Department = 'Sales' AND RemoteWork = TRUE

Result: Alice (must be in Sales and work remotely)

SELECT Name
FROM Employee_Data
WHERE Department = 'Tech' OR Salary > 40000

Result: Bob, Charlie (Bob is in Tech; Bob also earns over 40,000 — either condition is enough for OR)