--code by #TASS USE PE_DBI202_Su2019 GO --- Q2 : find all employees : department_id = 9 SELECT * FROM dbo.employees WHERE employees.department_id = 9 GO --Q3 SELECT employees.employee_id , employees.first_name , employees.last_name , employees.job_id , employees.hire_date FROM dbo.employees WHERE employees.job_id IN ( 4, 5, 6, 7, 9 ) AND employees.hire_date BETWEEN '19920101' AND '19991231' ORDER BY employees.job_id DESC , employees.hire_date ASC GO -- Q4 SELECT departments.department_id , departments.department_name , employees.employee_id , employees.first_name , employees.last_name , jobs.job_title FROM dbo.departments INNER JOIN dbo.employees ON employees.department_id = departments.department_id INNER JOIN dbo.jobs ON jobs.job_id = employees.job_id WHERE jobs.job_title IN ( 'Programmer', 'Accountant' ) GO -- Q5 SELECT departments.department_id , departments.department_name , AVG(employees.salary) AS Average_Salary FROM dbo.departments INNER JOIN dbo.employees ON employees.department_id = departments.department_id GROUP BY departments.department_id , departments.department_name ORDER BY Average_Salary DESC GO --- Bonus : employee_id , newSalary = salary + bonus(10%) SELECT employees.employee_id , employees.salary , ( employees.salary * 0.1 ) AS Bonus , ( employees.salary * 1.1 ) AS newSalary FROM dbo.employees ORDER BY newSalary --- Q6: subQuery SELECT employees.employee_id , employees.first_name , employees.last_name , employees.hire_date , employees.department_id FROM dbo.employees WHERE employees.hire_date IN ( SELECT MIN(employees.hire_date) FROM dbo.employees GROUP BY employees.department_id ) ORDER BY employees.department_id --- Q7 SELECT b.employee_id, b.first_name, b.last_name, c.department_name, COUNT(a.employee_id) AS NumberOfSuboridinates FROM dbo.employees AS a RIGHT JOIN dbo.employees AS b ON b.employee_id = a.manager_id INNER JOIN dbo.departments AS c ON c.department_id = b.department_id WHERE c.department_name IN ('Executive', 'IT') GROUP BY b.employee_id, b.first_name, b.last_name, c.department_name ----------------------- Q8 --------------------------------- DROP PROCEDURE proc2 GO --- CREATE PROCEDURE proc2 ( @job_id INT , @numberOfEmployees INT OUTPUT ) AS BEGIN SELECT @numberOfEmployees = COUNT(employees.employee_id) FROM dbo.employees WHERE employees.job_id = @job_id RETURN END GO ---- DECLARE @x INT EXECUTE dbo.proc2 5, @x OUTPUT SELECT @x AS NumberOfEmployees GO --------------------------------- Q9 ----------------------- DROP TRIGGER jobDelete CREATE TRIGGER jobDelete ON dbo.jobs INSTEAD OF DELETE AS SELECT employees.employee_id , employees.first_name , employees.last_name , employees.job_id FROM dbo.employees INNER JOIN Deleted ON Deleted.job_id = employees.job_id GO --- DELETE FROM dbo.jobs WHERE jobs.job_id IN ( 4, 5 ) GO ------------------------ Q10 ----------------- DELETE FROM dbo.countries WHERE countries.country_id IN ( SELECT countries.country_id FROM dbo.countries LEFT OUTER JOIN dbo.locations ON locations.country_id = countries.country_id ) OR countries.region_id IN ( 1, 3 ) GO