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.sql file 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 USING is 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 USING clause 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;