*must be able to use on oracle* \"most points to a good answer* here is the code
ID: 3864586 • Letter: #
Question
*must be able to use on oracle*
"most points to a good answer*
here is the code
1. List the total hours that chen has working on projects
2. List the name of division that has two employees whose salary is below 50000. (if you don't have such division, use INSERT statements to add employee records into Employee table, check the use of INSERT statement in chapter 7, SHOW your INSERT statement).
3. List the total number of projects, total project budget each division has , including division name.
4. List the name and the salary of employee who work on more that 2 projects
5. List the name and salary of employee who works on project 'DB development '.
6. List the name of project that has more than two employees working on it.
7. List the name of employee whose name is unique.
Note the following queries need you to use "subquery" strategy. Please study this subject and related examples in Chapter 2
8. List the name of employee whose salary is above company's average salary
9. List the name of employee whose salary is higher that chen's salary.
10. List the name of employee who don't work on any project.
11. List the name of division that has more project than division 'Accounting' has.
12. List the name of employees who work on more project than chen does.
Explanation / Answer
This is long assignment to do at once.
I am giving you the answer of first seven queries. Please post question again for remaining queries.
Please give thumbs up, If it is helpful for you. Thankyou!!
/* 1 */
select E.EMPID, E.NAME, w.PID, W.HOURS
from employee E join workon w
on E.empid = W.empid
where E.NAME = 'chen';
/* 2 */
select t2.DNAME from
(select DID, count(DID) from employee
where salary < 50000
group by DID having count(DID)=2) t1
join division t2
on t1.DID = t2.DID;
/* 3 */
select P.PID, P.PNAME, D.DNAME, P.BUDGET
from project P join division D
on P.DID = D.DID;
/* 4 */
select E.NAME, E.SALARY
from employee E join
(select EMPID, count(distinct PID) from workon
group by EMPID having count(distinct PID)>2) T
on E.empid = T.empid;
/* 5 */
select E.NAME, E.SALARY
from employee E join workon W
on E.empid = W.empid
join project P
on W.pid = P.pid
where pname = 'DB development';
/* 6 */
select pname
from project P join
(select PID, count(empid) from workon
group by PID having count(empid)>2) T
on P.pid = T.pid;
/* 7 */
select name from
(select name,count(*) from employee
group by NAME
having count(*)=1) T;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.