Scribd
Upload
3 views1 page

SQL Queries for Employee and Project Data

The document outlines a series of SQL queries to retrieve specific information from a database involving divisions, employees, projects, and work assignments. The queries include filtering based on salary comparisons, counting projects and hours, analyzing budgets, and identifying employees based on their project affiliations and divisions. Each query requires the use of subqueries or specific SQL functions to achieve the desired results.

Uploaded by

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

SQL Queries for Employee and Project Data

The document outlines a series of SQL queries to retrieve specific information from a database involving divisions, employees, projects, and work assignments. The queries include filtering based on salary comparisons, counting projects and hours, analyzing budgets, and identifying employees based on their project affiliations and divisions. Each query requires the use of subqueries or specific SQL functions to achieve the desired results.

Uploaded by

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

Division (DID, dname, managerID)

Employee (empID, name, salary, DID)


Project (PID, pname, budget, DID)
Workon (PID, EmpID, hours)
Formulate the following queries

1. List the name of employee whose salary is higher than ‘Justin ‘ and 'Sam'. (hint; use subquery)

2. List the name of EACH employee in marketing division and the total number of projects the
employee works on, as well as the total hours he/she spent on the project(s). Note some employees
may have same names.
3. List the name and budget of the project if its budget is over average budget and has more than 3
people working on it, together with the average budget in query result.
4. List the name of employee who work on a project sponsored by accounting division but does not
work on a project sponsored by engineering division. 

5. List the name of the project that has most people working in  (hint use a subquery at HAVING
clause).

6. List the total number of employee whose salary is over company's average salary (subquery) for each
division, including division name.

7. List the name of manager (note a manager is an employee whose empid is IN division table as
managerID) who work on project with 'Brian' (use subquery to find PID of project Brian works on)

8. List the name of employee who work on more projects than Larry.
9. List the name of project that 'chen' works on but not from chen's division.  (hint/pseudocode: find ID
of proj. that is IN  (ID of proj chen works on returned by a subquery) AND proj's DID NOT IN (DID
of chen's returned by a subquery)
10. List the name of employee who make highest salary in his/her division (use co-related subquery) .

You might also like