Yam Code

New Paste Browse

--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

Paste Settings

Paste Title : [Optional]

Paste Folder : [Optional]

Syntax Highlighting : [Optional]

Paste Expiration : [Optional]

Paste Status : [Optional]

Password : [Optional]

Description: [Optional]

Tags: [Optional]

Encrypt Paste (?)

You are currently not logged in, this means you can not edit or delete anything you paste. Sign Up or Login

Paste Settings

Site Languages