Scribd Logo
Upload

Welcome to Scribd!

  • 50 views

    Assignment3 Solution

    Uploaded by

    Seenu Cnu
    The document describes a relational schema with Employees, Works, and Departments relations and provides 8 queries against this schema: 1) Find names and ages of employees who work in both Hardware and Software departments 2) Find departments with over 20 full-time employees and their employee counts 3) Find names of employees whose salary exceeds all departments they work in 4) Find manager IDs of managers with departments over $1M budget 5) Find names of managers of departments with largest budgets 6) Find manager IDs controlling over $5M total budgets 7) Find manager IDs controlling largest total budgets 8) Find names of managers with departments over $1M but one under $5M

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Assignment3 Solution

    Uploaded by

    Seenu Cnu
    50 views2 pages
    The document describes a relational schema with Employees, Works, and Departments relations and provides 8 queries against this schema: 1) Find names and ages of employees who work in both Hardware and Software departments 2) Find departments with over 20 full-time employees and their employee counts 3) Find names of employees whose salary exceeds all departments they work in 4) Find manager IDs of managers with departments over $1M budget 5) Find names of managers of departments with largest budgets 6) Find manager IDs controlling over $5M total budgets 7) Find manager IDs controlling largest total budgets 8) Find names of managers with departments over $1M but one under $5M

    Original Title

    assignment3_solution (1)

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes a relational schema with Employees, Works, and Departments relations and provides 8 queries against this schema: 1) Find names and ages of employees who work in both Hardware and Software departments 2) Find departments with over 20 full-time employees and their employee counts 3) Find names of employees whose salary exceeds all departments they work in 4) Find manager IDs of managers with departments over $1M budget 5) Find names of managers of departments with largest budgets 6) Find manager IDs controlling over $5M total budgets 7) Find manager IDs controlling largest total budgets 8) Find names of managers with departments over $1M but one under $5M

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    50 views2 pages

    Assignment3 Solution

    Uploaded by

    Seenu Cnu
    The document describes a relational schema with Employees, Works, and Departments relations and provides 8 queries against this schema: 1) Find names and ages of employees who work in both Hardware and Software departments 2) Find departments with over 20 full-time employees and their employee counts 3) Find names of employees whose salary exceeds all departments they work in 4) Find manager IDs of managers with departments over $1M budget 5) Find names of managers of departments with largest budgets 6) Find manager IDs controlling over $5M total budgets 7) Find manager IDs controlling largest total budgets 8) Find names of managers with departments over $1M but one under $5M

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    2. Consider the following relational schema.

    An employee can work in more than


    one department; the pct_time filed 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, dname: string, budget: real, managerid: integer)

    Write the following queries in SQL:

    a. Print the names and ages of each employee who works in both the Hardware
    department and the Software department.
    b. For each department with more than 20 full-time-equivalent employees (i.e.,
    where the part-time and full-time employees add up to at least that many full-
    time employees), print the did together with the number of employees that
    work in that department.
    c. Print the name of each employee whose salary exceeds the budget of all of the
    departments that he or she works in.
    d. Find the managerids of managers who manage only departments with budgets
    greater than $1 million.
    e. Find the enames of managers who manage the departments with the largest
    budgets.
    f. If a manager manages more than one department, he or she controls the sum
    of all the budgets for those departments. Find the managerids of managers
    who control more than $5 million.
    g. Find the managerids of managers who control the largest amounts.
    h. Find the enames of managers who manage only departments with budgets
    larger than $1 million, but at least one department with budget less than $5
    million.

    Ans:
    a. SELECT E.ename, E.age
    FROM Emp E, Works W1, Works W2, Dept D1, Dept D2
    WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = ‘Hardware’ AND
    E.eid = W2.eid AND W2.did = D2.did AND D2.dname = ‘Software’

    b. SELECT W.did, COUNT (W.eid)


    FROM Works W
    GROUP BY W.did
    HAVING 2000 < ( SELECT SUM (W1.pct time)
    FROM Works W1
    WHERE W1.did = W.did )
    c. SELECT E.ename
    FROM Emp E
    WHERE E.salary > ALL (SELECT D.budget
    FROM Dept D, Works W
    WHERE E.eid = W.eid AND D.did = W.did)

    d. SELECT DISTINCT D.managerid


    FROM Dept D
    WHERE 1000000 < ALL (SELECT D2.budget
    FROM Dept D2
    WHERE D2.managerid = D.managerid )

    e. SELECT E.ename
    FROM Emp E
    WHERE E.eid IN (SELECT D.managerid
    FROM Dept D
    WHERE D.budget = (SELECT MAX (D2.budget)
    FROM Dept D2))

    f. SELECT D.managerid
    FROM Dept D
    WHERE 5000000 < (SELECT SUM (D2.budget)
    FROM Dept D2
    WHERE D2.managerid = D.managerid )

    g. SELECT DISTINCT tempD.managerid


    FROM (SELECT DISTINCT D.managerid, SUM (D.budget) AS tempBudget
    FROM Dept D
    GROUP BY D.managerid ) AS tempD
    WHERE tempD.tempBudget = (SELECT MAX (tempD.tempBudget)
    FROM tempD)

    h. SELECT E.ename
    FROM Emp E, Dept D
    WHERE E.eid = D.managerid GROUP BY E.Eid, E.ename
    HAVING EVERY (D.budget > 1000000) AND ANY (D.budget < 5000000)

    You might also like