5COSC020W DATABASE SYSTEMS

2022-2023 Tutorial 05
Querying Relational Databases – Simple SQL Queries

Case Study

Carefully consider the conceptual ERD shown below for the MegaFirm organisation (figure 1).

Figure 1: MegaFirm Conceptual ERD

Tutorial 04 Task 01: Logical Mapping

Quickly map this conceptual EERD into a full logical ERD (i.e. a relational schema).

• To do this, resolve all the relationships one by one and derive the associated relations (i.e. tables) with all the
attributes, primary keys and foreign keys. Your solution should consist of a complete logical ERD.

• Create your logical ERD using draw.io, export your ERD as a PNG and insert it below.
Tutorial 04 Task 02: Accessing an IDE and MySQL via PHPMyAdmin

2.1. Access an IDE.

• Access AppsAnywhere on https://appsanywhere.westminster.ac.uk and locate your preferred IDE (code

• You can choose any from the following list among others: Notepad++, Atom, Brackets, Visual Studio Code,
Programmer's Notepad or Emacs.

• For more info, see https://support.ecs.westminster.ac.uk/w/index.php/Title:Text_Editors

2.2. Access the MySQL DBMS (via the PHPMyAdmin admin tool).

• Go to https://support.ecs.westminster.ac.uk/mysql/index.php to authenticate yourself and generate your

• Access phpMyAdmin on https://phpmyadmin.ecs.westminster.ac.uk/ and enter the MySQL database account

• Access your default database by clicking on your database name on the left hand-side.

• For more info, see the ‘Database Systems Module Software Guide’ accessible on Blackboard

Tutorial 04 Task 03: Creating and Populating the MegaFirm Tables in MySQL

3.1 Access and run the table creation and population SQL script from Blackboard

• Get the “Tutorial 04 – SQL Tables Script” under ‘Learning Resources and ‘Section 02 – SQL’.

• Open the script in your IDE and copy and paste the code in the SQL area of phpMyAdmin (2nd tab ‘SQL’) and
run it by clicking on “Go”.

• Alternatively, import the script (6th tab ‘Import’) and execute it.

3.2. Check the structure and content of your MegaFirm Database

• You should see your tables as successfully created appearing in the list of tables on the left hand-side.

• Click on the ‘Structure’ tab at the top to verify the structure of the table.

• Click on the ‘Browse’ tab at the top to verify the content of the table.

Tutorial 04 Question 01

1.1. Write a simple SQL query to display the content of the employees table.

1.2. Write a simple SQL query to display the content of the departments table.

1.3. Write a simple SQL query to display the employee number, last name, job code, hire date and
department id for each employee.
1.4. Write a simple SQL query to display the department id and department name for each department.

Tutorial 04 Question 02

2.1. Create a query that displays the last name and salary of employees earning more than £45,000.

2.2. Modify this query to display the last name and salary for all employees whose salary is not in the range
of £47,000 to £57,000.

2.3. Create a query that displays the last name, hire date and department id of employees called Matos.

2.4. Create a query that displays the last name, hire date and salary of employees hired after 1st January
2016.

Tutorial 04 Question 03

3.1. Create a report to display the last name, job id, and start date for the employees with the last names of
Matos and Taylor. Order the query in ascending order by start date.

3.2. Create a query to list the last name, salary and department ids for employees who earn between £37,000
and £57,000 and are in department 20 or 40. Label the columns Employee, Yearly Salary, and DepId
respectively.

3.3. Create a query to list the last name, job id, hire date and salary of employees who work in department
40 and who earn more than 41000 as well as those who work in the same department and were hired before
the 15th February 2016.

Tutorial 04 Question 04

4.1. Create a query to display all employee last names in which the third letter of the name is a.

4.2. Create a query to display the last name of all employees who have both an a and an t in their last name.

4.3. Create a query that displays the last name and hire date for all employees who were hired in 2014.

Tutorial 04 Question 05

5.1 Create a query to display the last name, job id, and salary for all employees whose job id is 902 or 903
and whose salary is not equal to £35000, £41000, or £51000.

5.2 Create a query to display the last name, job id, salary, hire date for all employees for which, either their
job id is 909 or 911 and their salary is less than 45000, or they were hired before 3rd March 2017 and their
last names contains the letter ‘o’ as the one letter before last.