Q1.
(a) Name and salary of employees earning more than 6450€:
SELECT name, salary
FROM employees
WHERE salary > 6450;
(b) Name and salary of employees not earning between 6000€ and 10000€:
SELECT name, salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 10000;
(c) All planes of type A−340 or TRIDENT, sorted by purchase date:
SELECT *
FROM planes
WHERE type IN ('A-340', 'TRIDENT')
ORDER BY purchase_date;
(d) Pilots with a license different from I and II:
SELECT *
FROM pilots
WHERE license NOT IN ('I', 'II');
(e) Names of employees that have an A in the third position of their name:
SELECT name
FROM employees
WHERE SUBSTRING(name, 3, 1) = 'A';
Alternative using SQL LIKE:
SELECT name
FROM employees
WHERE name LIKE '__A%';
(f) Names of employees that have L twice in their name:
SELECT name
FROM employees
WHERE LENGTH(name) - LENGTH(REPLACE(UPPER(name), 'L', '')) = 2;
(g) Name, job, and salary of employees whose job is either Dipl.-Ing. or
steward/-ess, and who earn at least 6000€:
SELECT name, job, salary
FROM employees
WHERE job IN ('Dipl.-Ing.', 'steward/-ess') AND salary >= 6000;
Question 02
(a) Return employee ID, name, salary, and a 15% increased new salary (as
integer):
SELECT
employee_id,
name,
salary,
CAST(salary * 1.15 AS INT) AS new_salary
FROM employees;
(b) Extend (a) to include the difference between original and new salary (as
integer):
SELECT
employee_id,
name,
salary,
CAST(salary * 1.15 AS INT) AS new_salary,
CAST((salary * 1.15 - salary) AS INT) AS difference
FROM employees;
(c) For every plane, list type, serial number, and operating hours (difference
from today to purchase date, as integer), sorted by operating hours:
For databases like PostgreSQL or MySQL:
SELECT
type,
serial_number,
DATEDIFF(CURRENT_DATE, purchase_date) AS operating_hours
FROM planes
ORDER BY operating_hours;
🔁 Alternative for PostgreSQL (uses CURRENT_DATE - purchase_date directly):
SELECT
type,
serial_number,
(CURRENT_DATE - purchase_date) AS operating_hours
FROM planes
ORDER BY operating_hours;
(d) Return a string like: <name> earns <salary> per month, but desires to earn <3*salary>
with column name desired_salary:
SELECT
CONCAT(name, ' earns ', salary, ' per month, but desires to earn ', salary * 3) AS desired_salary
FROM employees;
Works in MySQL. For PostgreSQL use || instead of CONCAT():
SELECT
name || ' earns ' || salary || ' per month, but desires to earn ' || (salary * 3) AS desired_salary
FROM employees;
(e) List all distinct plane types, with:
First letter capitalized
Rest lowercased
Also return length of the name
SELECT
CONCAT(UPPER(LEFT(type, 1)), LOWER(SUBSTRING(type FROM 2))) AS name,
LENGTH(type) AS length
FROM planes;
Question 03
(a) How many planes (not types) are stored in the departure relation:
sql
CopyEdit
SELECT COUNT(*) AS plane_count
FROM departure;
Assumes each row represents one plane instance in a flight/departure.
(b) Number of employees with a doctor’s degree (Dr. or PhD) in their title or
name:
sql
CopyEdit
SELECT COUNT(*) AS doctors_count
FROM employees
WHERE name LIKE '%Dr.%' OR name LIKE '%PhD%';
Adjust column if the title is stored separately (e.g., title or job_title).
(c) Average salary by job:
sql
CopyEdit
SELECT job, AVG(salary) AS average_salary
FROM employees
GROUP BY job;
(d) Total price and number of bookings for all journeys in 1993 (in booking
table):
sql
CopyEdit
SELECT
SUM(price) AS total_price,
COUNT(*) AS total_bookings
FROM booking
WHERE YEAR(booking_date) = 1993;
Replace booking_date with actual column name. If using PostgreSQL, use:
sql
CopyEdit
WHERE EXTRACT(YEAR FROM booking_date) = 1993;
(e) Minimum salary for every job:
sql
CopyEdit
SELECT job, MIN(salary) AS min_salary
FROM employees
GROUP BY job;
(f) Difference between maximum and minimum salary of all employees:
sql
CopyEdit
SELECT MAX(salary) - MIN(salary) AS salary_range
FROM employees;
Question 04.
Original Query:
sql
CopyEdit
SELECT DISTINCT X.exam_ID
FROM exams X
WHERE X.exam_ID IN (
SELECT Y.exam_ID
FROM exams Y
WHERE Y.student_ID <> X.student_ID
);
🔵 (a) What is the result of this query?
This query returns all exam_IDs for which there exists at least one exam entry with a
different student_ID. In other words:
👉 It returns the IDs of exams that have been taken by more than one student.
🔁 (b) Reformulations
(i) Reformulate without a nested query in the WHERE clause:
You can use a self-join:
sql
CopyEdit
SELECT DISTINCT X.exam_ID
FROM exams X
JOIN exams Y ON X.exam_ID = Y.exam_ID
WHERE X.student_ID <> Y.student_ID;
✅ This replaces the correlated subquery with a join and is usually faster due to better
optimization opportunities for joins by the SQL engine.
(ii) Reformulate without using tuple variables (i.e., no aliases), and using
aggregation:
You can use a GROUP BY and HAVING clause:
sql
CopyEdit
SELECT exam_ID
FROM exams
GROUP BY exam_ID
HAVING COUNT(DISTINCT student_ID) > 1;
✅ This finds all exam_IDs that have more than one distinct student, which is the intended
logic.
Question 05
✅ (a) Natural Join
A NATURAL JOIN automatically joins on columns with the same name (Pid here), returning
only matching rows.
SELECT *
FROM employees
NATURAL JOIN salaries;
Result:
Name Pid Salary
Meier 1586 4000
Schmidt 905 1000
✅ (b) Left Outer Join
Returns all rows from the left table (employees), with matching rows from salaries, or NULL if
none.
SELECT *
FROM employees
LEFT OUTER JOIN salaries ON employees.Pid = salaries.Pid;
Result:
Name Pid Salary
Meier 1586 4000
Mueller 1001 NULL
Schmidt 905 1000
✅ (c) Right Outer Join
Returns all rows from the right table (salaries), with matching rows from employees, or NULL if
none.
SELECT *
FROM employees
RIGHT OUTER JOIN salaries ON employees.Pid = salaries.Pid;
Result:
Name Pid Salary
Meier 1586 4000
Schmidt 905 1000
NULL 1235 2500
NULL 512 1575
✅ (d) Full Outer Join
Returns all rows from both tables, with matching rows where available. Use NULL where
there's no match.
SELECT *
FROM employees
FULL OUTER JOIN salaries ON employees.Pid = salaries.Pid;
Result:
Name Pid Salary
Meier 1586 4000
Mueller 1001 NULL
Schmidt 905 1000
NULL 1235 2500
NULL 512 1575
