Write SQL queries for this database , please type, don\'t use hand writing. Crea

Question

Write SQL queries for this database , please type, don't use hand writing.

Create the following queries on the COMPANY database and show their results.

Here is my database https://www.mediafire.com/file/7g9p5hb1h3mkmm2/COMPANY_1.txt

1.Retrieve the names of all employees in department 4 who work less than 20 hours per week on the 'Computerization' project.

2.Retrieve the employee name, salary, and supervisor name for each employee who works in the ‘Research’ department.

3.List the department name and the number of employees in that department who are making more than $32,000.

4.For each department whose average employee salary is more than $32,000, retrieve the department name and the number of employees in that department.

Explanation / Answer

1.

Select Fname,Lname from Employee inner join Department on Employee.dno = Department.dnumber inner join Project on Department.dnumber = Project.dnum inner join Works_On on Project.pnumber = Works_On.pno where Department.dnumber = 4 and Works_On.hours<20 and Project.pname = 'Computerization';

2.

Select e.Fname,e.Lname,e.salary,s.Fname,s.Lname from Employee e inner join Employee s on e.super_ssn = s.ssn iner join Department on e.dno = Department.dnumber where Depratment.dname = 'Research';

3.

Select dname, sum(Employee.ssn) as 'Number_Of_employees' from Department inner join Employee on Employee.dno = Department.dnumber where Employee.salary > 32000 group by department.dnumber ;

4.

Select dname, sum(Employee.ssn) as 'Number_Of_employees' from Department inner join Employee on Employee.dno = Department.dnumber group by department.dnumber having avg(Employee.salary) > 32000;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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