On the following database schema, compose sQL SELECT statements to answer 12 que

Question

On the following database schema, compose sQL SELECT statements to answer 12 questions. Employee (empNo fName, IName, address, DOB, sex, position, dantNo Department (depto, deptName marinnNa) Project (proiNo proiName, dentNal WorksOn (emnNo.nroiNe 1. (2pts) What are the fill details of works-on records, in the ascending order of project numbers and then the descending order of hours worked? 2. (2pts) What are the full details of employees who are 'Male'? 3. (3pts) What are the names and addresses of employees who are 'staff'? 4. (3pts) What are the names and addresses of employees who work at the 'Integration" department? 5. (3pts) How many unique 'Staff employees, who worked more than 10 hours on a project? 6. (3pts) What are all project names run by the 'Video department? (4pts) What are the department names and project names run by the departments in which employee name 'Franklin Wong' is working? Each row in the resulting table should be unique and be ordered with department names alphabetically. 7.

Explanation / Answer

Hi,

Ans 1 - select empno,projno,hoursWorked from WorksOn
order by projnn asc, hoursWorked desc;

Ans 2 - select * from Employee where sex='Male';

Ans 3 - select fname,lname,address from Employee where position='Staff';

Ans 4 - select fname,lname,address from Employee a join Department d on a.deptNo=d.deptNo and d.deptName='Integration';

Ans 5 - select count(e.empno) from Employee e join WorksOn w
on e.empno=w.empno and e.position='Staff' and w.hoursWorked>10;

Ans 6 - select projname from Project p join Department d
on p.deptno=d.deptno
and deptName='Video';

Ans 7 - select deptname,projname from employee e
join department d
on e.deptno=d.deptno
and e.fname||' '||e.lname='Franklin Wong'
join project p
on d.deptno=p.deptno;

Ans 8 - select count(e.empno) from Employee e
join WorksOn w
on e.empno=w.empno
join Project p
on w.projno=p.projno
and p.projname in('Transformer','Lord of the Rings');

Ans 9 - select fname||' '||lname from Employee e
join WorksOn w
on e.empno=w.empno
join Project p
on w.projno=p.projno
and p.projname='Harry Potter';

Ans 10 - select avg(hoursWorked) from employee e
join department d
on e.deptno=d.deptno
and d.deptname='Integration';

Ans 11 - select projname, fname, lname from Employee e
join Project p
on e.deptno=p.deptno
order by projname, fname, lname;

Ans 12 - select fname,lname from Employee e
join WorksOn w
on e.empno=w.empno
join Project p
on w.projno=p.projno
and p.projname ='Harry Potter'
and p.projname!='Computerization';

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.