Consider the following relational schema. An employee can work in more than one
ID: 3735730 • Letter: C
Question
Consider the following relational schema. An employee can work in more than one department; thepct_time field of the Works relation shows the percentage of time that a given employee works in a given department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct_time: integer)
Dept(did: integer, budget: real, managerid: integer)
2. Write the following queries in SQL and run them with MySQL. Print the sql statements and query results.
a) Find the names of managers who manage the departments with the largest budget.
b) Find the name, age and salary of the youngest employee in each department.
c) Find the employees (i.e., include mangers) with a salary greater than some manager.
Explanation / Answer
Solution:
a)
SELECT managerid FROM Dept WHERE budget= MAX(budget)
one more way of writing this
SELECT managerid FROM Dept WHERE MAX(budget)
b)
SELECT name, MIN(age), salary FROM Emp INNER JOIN Works ON Emp.eid= Works.eid INNER JOIN Dept ON Dept.did= Works.did GROUP BY deptid
c)
SELECT empid (age), salary FROM Emp INNER JOIN Works ON Emp.eid= Works.eid INNER JOIN Dept ON Dept.did= Works.did WHERE MAX(salary).
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)