EssayproSolutions

(Solved) : Write Select Statement Calculates Total Number Employees Average Salary Department Display Q37299350 . . .

1. Write a SELECT statement that calculates the total number ofemployees and average salary in each department and displays ittogether with the name of the department. Order the departments bythe total number of employees in descending order.
2. Write the SELECT statement to retrieve the first name, lastname and address of employees who work for the ‘Research’department
3. Write a SELECT statement that returns the same result as thefollowing SELECT statement but use a different query. Explain whatthis query returns.
4. Write a SELECT statement to list the names of employees whowork on all the projects controlled by department
5. Write a SELECT statement that returns the project name, and thenumber of employees who work on the project for each project onwhich no more than two employees work.
6. Write a SELECT statement to retrieve the names of employees whohave no dependents.
7. Create a view called WORKS_ON1 that lists employee’s firstname, last name, the project name she/he is working on andhours.
8. Write a SQL query to retrieve the last name and first name ofall employees who work on the ‘ProductX’ project using theWORKS_ON1 view.
9. Write the actual SQL query that the DBMS executes when runningthe query from (h).
10. List the possible orders to join the EMPLOYEE, DEPARTMENT,DEPT_LOCATION and DEPENDENT tables. SELECT * FROM EMPLOYEEe, DEPARTMENT dept, DEPT_LOCATION l, DEPENDENT d
    WHERE Lname LIKE ‘Ro%’ and Bdate Like ‘%-FEB-%’    
    AND e.Dno = dept.Dnumber    
    AND dept.Dnumber = l.Dnumber    
    AND e.ssn = d.essn;

EMPLOYEE Fname Minit Lname SsnBdateAddressS Salary Super_ssnDno DEPARTMENT Dname Dnumber rssn Mgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT PnamePnumber PlocationDnum WORKS ON Pno Hours Essn DEPENDENT Essn Dependent nameSex Bdate Relationship Show transcribed image text EMPLOYEE Fname Minit Lname SsnBdateAddressS Salary Super_ssnDno DEPARTMENT Dname Dnumber rssn Mgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT PnamePnumber PlocationDnum WORKS ON Pno Hours Essn DEPENDENT Essn Dependent nameSex Bdate Relationship

Expert Answer

Answer to Write a SELECT statement that calculates the total number of employees and average salary in each department and display…