Homework: Advanced SQL (w3)

Homework: Advanced SQL (w3)
Points 4 Submitting a file upload

Introduction
The objective of this assignment is to practice writing queries in SQL.
What you must do
Consider the following relational schema:
Emp (eid: integer, ename: string, age: integer, salary: real)
Works (eid: integer, did: integer, pc_time: integer)
Dept (did: integer, dname: string, budge: real, managerid: integer)
The underlined attributes are keys for their relations. Note that a manager is an employee as well and their manager id and employee id are the
same. An employee can work in more than one department. The pct_time field of the works relation shows the percentage of time that a given
employee works in a given department and is always greater than zero.
A sample database (sample db.sql (https://canvas.oregonstate.edu/courses/1939345/files/98616655?wrap=1)
(https://canvas.oregonstate.edu/courses/1939345/files/98616655/download?download_frd=1) ) is provided with this assignment and the output of the
correct queries over this sample database is given in each part of this question. You may use this sample database to test or debug your queries.
We have created an account for each student on the MySQL server of our department. The access guide to the MySQL server (database access
guide.txt (https://canvas.oregonstate.edu/courses/1939345/files/98734072?wrap=1)
(https://canvas.oregonstate.edu/courses/1939345/files/98734072/download?download_frd=1) ) is also posted with this assignment. You can import the
6/1/23, 1:02 AM Homework: Advanced SQL (w3)
https://canvas.oregonstate.edu/courses/1939345/assignments/9237565?module_item_id=23191644 2/6
sample database to your account on the MySQL server and use it to ensure that your queries are correct. Notice that your SQL queries must return
the correct result over every possible database instance of the aforementioned schema for the questions and not only over the sample database.
Write the following queries in SQL.
(a) Return names of every employee who works in the "Hardware", "Software", and "Research" departments (0.5 point).
The answer on the sample database is:
ename
Shirish Ossenbruggen
(b) Return the names of every department without any employee (0.5 point).
The answer on the sample database is:
dname
Business Development
(c) Print the managerid of managers who manage only departments with budgets greater than $1.5 million. (0.5 point)
The answer on the sample database is:
managerid
110511
(d) Print the name of employees whose salary is less than or equal to the salary of every employee (0.5 point).
The answer on the sample database is:
ename
Antonio Lavante
(e) Print the enames of managers who manage the departments with the largest budget (0.5 point).
6/1/23, 1:02 AM Homework: Advanced SQL (w3)
https://canvas.oregonstate.edu/courses/1939345/assignments/9237565?module_item_id=23191644 3/6
The answer on the sample database is:
ename
Tonny Butterworth
(f) Print the name of every department and the average salary of the employees of that department. The department must have a budget more than
or equal to $50. (0.5 point)
The answer on the sample database is:
dname average employee salary
Software 48291
Human Resources 717092.5
Research 490439.6666666667
Hardware 61842.125
Customer Service 40000
(g) Print the managerids of managers who control the largest amount of total budget. As an example, if a manager manages two departments, the
amount of total budget for him/her will be the sum of the budgets of the two departments. We want to find managers that have max total budget. (1
point)
The answer on the sample database is:
managerid
111692
(h) Print the name of every employee who works only in the ”Hardware” department. (1 point)
The answer on the sample database is:
6/1/23, 1:02 AM Homework: Advanced SQL (w3)
https://canvas.oregonstate.edu/courses/1939345/assignments/9237565?module_item_id=23191644 4/6
Assignment 2 SQL
ename
Alex Dalas
Sergio Ravarez
Antonio Lavante
Tonny Conner
Gladys Cooper
Rodney Ferreri
Arie Staelin
What to turn in
The assignment is to be turned in before Midnight (by 11:59pm) on April 23. You may turn in the solutions to this assignment as a PDF file through
Canvas. The solutions should be produced using editing software programs, such as LaTeX or Word, otherwise they will not be graded. The
assignment may be done in groups of two students. Each group may submit only one file that contains the full name, OSU email, and ONID of every
member of the group.
Grading criteria
Each complete query has 0.5 point. Incomplete answers get partial points depending on how accurate their returned results are.
6/1/23, 1:02 AM Homework: Advanced SQL (w3)
https://canvas.oregonstate.edu/courses/1939345/assignments/9237565?module_item_id=23191644 5/6
Criteria Ratings Pts
0.5 pts
0.5 pts
0.5 pts
0.5 pts
0.5 pts
0.5 pts
0.5 pts
0.5 pts
1a 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1b 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1c 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1d 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1e 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1f 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1g 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
1h 0.5 pts
Full Marks
0.45 pts
Minor Issue
0.35 pts
Major Issue
0 pts
No Marks
6/1/23, 1:02 AM Homework: Advanced SQL (w3)
https://canvas.oregonstate.edu/courses/1939345/assignments/9237565?module_item_id=23191644 6/6
Total Points: 4
Criteria Ratings Pts