Company Database SQL Queries and Reports.docx
Text preview. Download the original to see its layout and images.
CSC 1403-DATABASE CONCEPTS OCTOBER 2026
Project 2 (Company Database from Assignment 3)
CSC 1403 Database Concepts · Group project
Question 1: Three useful queries from one table (Employee)
Query 1: Employees who earn more than 70,000
Purpose: To find the highest paid employees. Management can use this when they review salaries.
SQL code:
SELECT emp_id, first_name, last_name, salary
FROM Employee
WHERE salary > 70000
ORDER BY salary DESC;
Sample output:
emp_id
first_name
last_name
salary
100
David
Wallace
250000
101
Jan
Levinson
110000
106
Josh
Porter
78000
102
Michael
Scott
75000
108
Jim
Halpert
71000
Query 2: Number of employees and average salary in each branch
Purpose: To see how many staff each branch has and how much it pays on average. This helps management plan the budget for each branch.
SQL code:
SELECT branch_id, COUNT(*) AS total_employees, ROUND(AVG(salary), 2) AS average_salary
FROM Employee
GROUP BY branch_id;
Sample output:
branch_id
total_employees
average_salary
1
2
180000.00
2
4
65500.00
3
3
71333.33
Query 3: Employees who report to supervisor 102
Purpose: To list the team of one supervisor (here, Michael Scott, emp_id 102). A manager can use it to see who works under them.
SQL code:
SELECT emp_id, first_name, last_name, salary
FROM Employee
WHERE super_id = 102;
Sample output:
emp_id
first_name
last_name
salary
103
Angela
Martin
63000
104
Kelly
Kapoor
55000
105
Stanley
Hudson
69000
Question 2: One useful query from more than one table
Query 4: Sales of each employee for each client
Purpose: To show which employee sold how much to which client. It joins three tables: Employee, Works_With and Client. The Works_With table links employees and clients, so we need it to connect the other two. This helps management see who the top sellers are.
SQL code:
SELECT e.first_name, e.last_name, c.client_name, w.total_sales
FROM Employee e
JOIN Works_With w ON e.emp_id = w.emp_id
JOIN Client c ON w.client_id = c.client_id
ORDER BY w.total_sales DESC;
Sample output:
first_name
last_name
client_name
total_sales
Michael
Scott
Lackawana Country
267000
Stanley
Hudson
FedEx
130000
Stanley
Hudson
Dunmore Highschool
55000
Stanley
Hudson
Scranton Whitepages
33000
Andy
Bernard
Times Newspaper
26000
Jim
Halpert
FedEx
22500
Michael
Scott
FedEx
15000
Jim
Halpert
John Daly Law, LLC
12000
Andy
Bernard
John Daly Law, LLC
5000
Question 3: Three types of reports
Salary Review Report (from Query 1). Lists employees with high salaries, with their ID, name and salary. The HR department can use it when they check pay.
Branch Staff and Salary Summary Report (from Query 2). Shows the number of employees and the average salary for each branch. Top management can use it to compare the branches.
Employee Sales Report (from Query 4). Shows each employee, their clients and the total sales, from highest to lowest. Sales managers can use it to find the best performing employees and the biggest clients.
Question 4: Two types of users and their roles
Database Administrator (DBA). Looks after the whole database. The DBA creates and changes the tables, creates user accounts, gives or removes permissions, makes backups and fixes problems. The DBA has full access.
Branch Manager (end user). Uses the database for daily work. A branch manager can view employee, client and sales information and run queries and reports. They can also add or update client and sales records. They cannot delete tables or change the structure of the database.
Question 5: Two methods to access the database
Using a database tool (SQL client). The user logs in with a username and password using a tool such as MySQL Workbench or the command line. Then they type SQL statements directly. The DBA mostly uses this method.
Using an application with forms. The user opens a company program or website and clicks on buttons and forms to add, search or view data. The program sends the SQL to the database in the background, so the user does not need to know SQL. Branch managers can use this method.
AppendixHere are Screenshots of the Queries in one compiler;