Ryerson University
CIND110
Data Organization for Data Analysts
Assignment II
Practicing SQL Queries
Starts: Thursday, October 16, 2021, 8:00 PM
Due: Saturday, November 06, 2021, 11:59 PM
This assignment counts for 10% of the final grade
General Instructions:
• The aim of this assignment is to help you practice complex and straightforward SQL
queries construction for retrieving or modifying data using the CompanyDB dataset,
which can be found as an SQL file on the course shell, under the Assignment 2 tab.
• The design of the CompanyDB database schema, including the names of entities, the
data types of the attributes, and the types of the relationships, is shown in the
following entity-relationship diagram.
DEPARTMENT
dept_name VARCHAR(20)
dept_number INT
manager_sin VARCHAR(20)
manager_start_date DATE
Indexes
PRIMARY
DEPENDENT
depend_sin VARCHAR(20)
depend_name VARCHAR(20)
depend_gender CHAR(1)
depend_birth_date DATE
depend_relationship VARCHAR(20)
emp_sin VARCHAR(20)
Indexes
PRIMARY
fk_dependent_enp_sin_idx
DEPTLOCATION
dept_location VARCHAR(20)
dept_number INT
Indexes
fk_deptlocation_dept_number
EMPLOYEE
emp_first_name VARCHAR(20)
emp_last_name VARCHAR(20)
emp_sin VARCHAR(20)
emp_birth_date DATE
emp_address VARCHAR(80)
emp_gender CHAR(1)
emp_salary FLOAT
manager_sin VARCHAR(20)
dept_number INT
Indexes
PRIMARY
fk_emp_dept_number_idx
PROJECT
prj_name VARCHAR(20)
prj_number INT
prj_location VARCHAR(20)
Indexes
PRIMARY
WORKSON
emp_sin VARCHAR(20)
prj_number INT
working_hours FLOAT(3,1)
Indexes
PRIMARY
fk_workson_prj_number_idx
• Download then run the CompanyDB.sql file using the MySQL Workbench tool to
load the database to your machine.
• For each question:
– Write your answer as an SQL query statement.
– Take a screenshot of the output.
– Paste the answer and the respective screenshot into your assignment answer
sheet with question number.
• Submit your answer sheet either in PDF or DOCX file format.
1
Write an SQL statement(s) to find the following: [Total Points: 100]
1. [5 Pts.] For every project located in ’Montréal’, list the project name, the controlling
department number, and the department manager’s last name.
2. [5 Pts.] Retrieve repeated addresses and the names of employees who lives at these
addresses.
3. [5 Pts.] Retrieve the first name, address and salary of each employee whose last name
starts with the letter ’E’ and works for either the ’Marketing’ or the ’Administration’
department.
4. [5 Pts.] List the names of managers who have at least one dependent.
5. [10 Pts.] Find the sum of the salaries of all employees of the ’Marketing’ department.
6. [10 Pts.] For each department that has more than two employees, retrieve the de-
partment number and the number of its employees who are making less than $50,000.
7. [10 Pts.] For each department whose average employee salary is less than $90,000,
retrieve the department name and the number of employees working for that depart-
ment.
8. [10 Pts.] Retrieve the names of all employees who work in the department that has
the employee with the highest salary among all employees.
9. [10 Pts.] Retrieve the names of employees who make at least $20,000 more than the
employee who is paid the least in the company.
10. [10 Pts.] Find the number of employees who are working on more than 2 projects
and show the result in descending order.
11. [10 Pts.] Retrieve the employee names and their dependent names. Return employee
names even if the dependent name is not present for the employee.
12. [10 Pts.] If more than one employee is working on the same project with the same
number of hours, then display the number of these employees along with the project
number.
2