Practice 3
DATABASE SYSTEMS 2025/2026 Semester II Practice 3 Solutions
Task 1: The ORACLE Demo Database
Initial Setup:
Run the script located in the
Practice_export_demo.sqlfile to set up the demo database.Update the session date format for the database to Hungarian:
Command:
sql ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MM.DD';
a. Employee Details for Alexander Khoo's Department
Objective: List names, entry dates, and salaries of all employees who work in the same department as Alexander Khoo, in descending order of salary.
SQL Query:
SELECT first_name, last_name, hire_date, salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE first_name = 'Alexander' AND last_name = 'Khoo'
)
ORDER BY salary DESC;
b. Employees with Last Names Starting with 'K'
Objective: Create a list of employees whose last name starts with 'K'. Include last name, position, and commission information (include text if no commission is set).
SQL Query:
SELECT last_name AS lname,
job_title AS position,
CASE
WHEN commission_pct IS NULL THEN 'No more money'
ELSE TO_CHAR(commission_pct * 100)
END AS commission
FROM employees
INNER JOIN jobs ON jobs.job_id = employees.job_id
WHERE last_name LIKE 'K%';
Note: The NVL function is not used here as it can only write the same type as
commission_pct.
c. Formatting Employee Salaries
Objective: List all employees' last names and their salaries formatted together in one field, showing last names occupy 10 characters, followed by a colon and a number of '#' characters equal to the employee's salary in $1000 increments.
SQL Query:
SELECT RPAD(last_name, 10) ||
RPAD(': ', ROUND((salary / 1000), 0) + 1, '#') AS salaries
FROM employees
ORDER BY salary DESC;
d. Departments with High Average Salaries
Objective: List departments that pay their employees an average salary greater than $2,000, with department name and rounded average salary, ordered by average salary.
SQL Query:
SELECT department_name AS dep,
ROUND(AVG(salary)) AS average_salary
FROM employees
INNER JOIN departments USING(department_id)
GROUP BY department_name
HAVING ROUND(AVG(salary)) > 2000
ORDER BY average_salary;
Note: The keyword
USINGis utilized for linking fields with the same name across both tables. Note that aliases cannot be used in the HAVING clause.
e. Average Salary with Commission
Objective: Modify the previous query to calculate the average salary including commissions, using the NVL function to replace NULL values with 0.
SQL Query:
SELECT department_name AS dep,
ROUND(AVG(salary * (1 + NVL(commission_pct, 0)))) AS average_salary
FROM employees
INNER JOIN departments USING(department_id)
GROUP BY department_name
HAVING ROUND(AVG(salary)) > 2000
ORDER BY average_salary;
f. Locations of Countries
Objective: List the country name, street address, and city name of the locations in the locations table, including countries with no associated locations. Sort results by country name, then city.
SQL Query:
SELECT country_name, street_address, city
FROM countries
LEFT JOIN locations USING(country_id)
ORDER BY 1, 3;
Note: The
USINGclause is also applicable for left joins. Sorting can be specified according to the order of fields in the SELECT statement.
g. List of Employees with Boss Information
Objective: List the name, department ID, and name of the boss for employees in departments with IDs less than 50, sorted by the boss's name and employee's name.
SQL Query:
SELECT worker.last_name AS "Worker name",
worker.department_id AS "Department ID",
boss.last_name AS "Boss name"
FROM employees boss, employees worker
WHERE boss.employee_id = worker.manager_id
AND worker.department_id < 50
ORDER BY boss.last_name, worker.last_name;