Document preview

← Back to documents

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;