Using the tables above, write and execute SQL for the following queries. Save ea

Question

Using the tables above, write and execute SQL for the following queries. Save each successful command in the spool file.

1.The name (in ascending order), starting salary and department id of each employee within a descending order of department ids.

2.Give the number of employees for each of the pension schemes offered by the company. The result listing should include the name of each scheme and the corresponding number of employees who join the scheme.

3.List the id and name of each employee along with his/her manager’s name.

4.Give the total number of employees who are not a manager but currently receive an annual salary of over £30,000.

5.List employees whose surnames begin with ‘S’ and whose employee ids begin with either ‘E1’ or ‘E3’ along with the departments in which they work. The result listing should include the ids, names of the employees and the names of their departments.

Explanation / Answer

Solution:

1)

SELECT name, dep_id, salary FROM Employee ORDER BY name ASC, salary ASC, dept_id, DESC

2)

SELECT Pension_Scheme.name, emp_id, COUNT(emp_id) FROM Eemployee INNER JOIN

Pension_Scheme ON Pension_Scheme.salary_code= Employee.salary GROUP BY scheme_id.

3)

SELECT emp_id, name FROM Employee GROUP BY manager.

4)

SELECT emp_id, name FROM Employee WHERE manager IS NULL AND finish_salary>3000

5)

SELECT employee_id, department_code, Employee.name, Department.name FROM Employee WHERE name LIKE %S AND employee_id LIKE %E!1 OR LIKE %E3

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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