Write SQL queries for this database Create the following queries on the COMPANY

Question

Write SQL queries for this database

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

Below are your SQL queries: -

1. SELECT concat(e.Fname+' '+e.Lname) from Employee e INNER JOIN WORKS_ON w ON e.Ssn = w.essn INNER JOIN PROJECT p ON p.pnumber = w.pno WHERE e.dno = 4 AND p.pname = 'Computerization' AND w.hours > 20.0

2. SELECT concat(e.Fname+' '+e.Lname) ,salary,e1.Fname FROM EMPLOYEE e INNER JOIN EMployee e1 where e.super_ssn = e1.ssn INNER JOIN Deparment d ON e.dno = d.dnumber WHERE d.dname = 'Research'

3. SELECT d.dname,count(e.ssn) FROM DEPARTMENT d INNER JOIN Employee e ON e.dno = d.dnumber WHERE e.salary > 32000 group by e.dno

4. SELECT d.dname,count(e.ssn) FROM DEPARTMENT d INNER JOIN Employee e ON e.dno = d.dnumber group by e.dno having avg(e.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.