SQL PROBLEMS FOR PROBLEMS 121 DEVELOP THE SQL QUERIES Refer

SQL PROBLEMS: FOR PROBLEMS 1-21, DEVELOP THE SQL QUERIES

**Refer to employee column broadly as \"employee\".

1. List lastname, firstname, and department for all employees.

2. Repeat #1, using field aliases to rename lastname to [Last Name] and firstname to [First Name] in the query output.

3. List only distinct lastname, firstname combinations (i.e., no duplicates).

4. Return the names and annual salary of the top (highest) 15% of employees in terms of accrued sick leave.

5. Return the names and annual salary of the 15 lowest performing employees.

6. For each employee, list their full name and \"daily salary\" assuming 248 working days per year.

7. List the max, min, avg, and total sum of annual salary as well as count for employees of the engineering department.

8. List employees and the names of the projects they are working on (one record for each employee/project combination). Only include employees who are assigned to projects.

9. Repeat #8, but this time include all employees even if they are not currently assigned a project. [this is a trick question. Explain why]

10. Repeat #8, aliasing the project table as PT and the employee table as EMP. Modify the query as needed to run.

11. Provide a list of employeeid, lastname firstname, supervisor lastname, supervisor firstname

12. Provide a list of all employee records having salaries at or above $70,000.

13. Provide a list of all employee records where the employees firstname begins with a \"J\"

14. Provide a list of all employee records where the employees lastname begins with a \"B\" or \"P\" AND contains (anywhere) an \"en\",\"on\", or \"ot\"

15. Provide a list of all female employees where the firstname is not blank, they haven\'t taken more than 20% of their accrued sick leave, AND EITHER the employee\'s salary is between $35-60K and their last name is either \"Smith\" or \"Obama\", OR their salary is > $50K but they are not in the accounting department.

16. Repeat #1, sorting by lastname, then firstname

17. Repeat #1, sorting by age in descending order

18. List the average annual salary for each department (ordered by average salary descending)

19. List the average annual salary for each department / job class combination

20. Repeat #18, but only list the departments whose average salary is less than $55K

21. Repeat #20, but only include job class #2 employees in calculating the average salary

Solution

Ans. 1. Select firstname, lastname, department from employee

Ans. 2. Select firstname AS First Name, lastname as Last Name, department from employee

Ans. 3. Select DISTINCT firstname, lastname from employee

Ans. 4.Select firstname, lastname, annualsalary from employee where annualsalary > 0.15