Scribd
Upload
4 views3 pages

SQL Report Assignment for Week 4

The assignment requires the creation of a SQL report addressing 14 specific questions related to employee data and a new Student table. Questions include calculating average salaries, counting employees by department and state, and performing data manipulation tasks such as adding and removing rows from the Student table. The assignment is due on October 6, 2024, and must be submitted in PDF or Word format with a specific naming convention.

Uploaded by

Maggie Eng
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views3 pages

SQL Report Assignment for Week 4

The assignment requires the creation of a SQL report addressing 14 specific questions related to employee data and a new Student table. Questions include calculating average salaries, counting employees by department and state, and performing data manipulation tasks such as adding and removing rows from the Student table. The assignment is due on October 6, 2024, and must be submitted in PDF or Word format with a specific naming convention.

Uploaded by

Maggie Eng
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Assignment 3

SQL (Week 4)

Using the sample database, you need to create a report for your manager.
The following questions should be answered using SQL commands.
There are 14 questions in total.

Question 1
What is the average salary in Marketing department?

Question 2
Who are the employees with a base salary greater or equal to the average of all base salaries?

Question 3
Suppose that you want to generate a listing of the number of employees by each department.
However, you want to limit the listing to number of employees whose base salary less than
$3500. You can use both a GROUP BY clause and a WHERE clause in your query.

Question 4
What is the minimum base salary of employees working in IT department?

Question 5
Display the number of employees by each state.

Question 6
How many employees in each state have a base salary greater than $3150? You should write
your query in a way that displays the name of each state, the number of employees for each state
and the number of employees in each state who have a base salary greater than $3150.

Question 7
Which department has the highest average rate of commission?

Question 8
How many unique job titles do we have in each department? You should write your query in a
way that displays the department ids and the number of unique job titles for each department.

Question 9
You have decided to create a table called Student and it has 8 columns.
The columns are: Student_id, Stud_fname, Stud_lname, Age, Phone, City, Province, Country

1
Question 10
Add 1 row to the table you just created in Question 10. The row should contain the following
information:

Student_id : 2020987
Stud_fname: Jennifer
Stud_lname: Davidson
Age: 23
Phone: 5194387224
City: London
Province: Ontario
Country: Canada

Question 11
Add another row to the table you just created. The row should contain the following information:

Student_id : 2020992
Stud_fname: Mike
Stud_lname: Trudeau
Age: 21
Phone: 5194387223
City: Montreal
Province: Quebec
Country: Canada

Question 12
Change the phone number to 5194387223 for student who has the id# 2020987.

Question 13
Suppose, we don’t need the data about the student with the id# 2020992 anymore. Write a query
that removes the data of this particular student from our database.

Question 14
Save all the changes permanently.

PLEASE SEE THE NEXT PAGE

2
IMPORTANT
Your assignment document should have the following structure:
Question 1
 SQL Query
 Screenshot that shows how you executed the query on your computer and the answer
displayed on your screen.

Question 2
 SQL Query
 Screenshot that shows how you executed the query on your computer.

EXAMPLE:
ANSWER
SELECT * FROM employees;

Due: Sunday, October 6, 2024 at 10:00 PM


You should submit your file to OWL Brightspace(Assignment tab)
Your file should be renamed in the following order:
Lastname_Firstname_Week4

Acceptable format: PDF or Word

You might also like