--Q1
CREATE DATABASE DE3
CREATE TABLE ITEMS(
ITEMID INT PRIMARY KEY,
NAME NVARCHAR(255),
PRICE FLOAT
)
CREATE TABLE ITEMVARIANTS(
VARIANTID INT PRIMARY KEY,
DETAIL NVARCHAR(200),
SIZE NVARCHAR(30),
COLOR NVARCHAR(50),
ITEMID INT,
FOREIGN KEY (ITEMID) REFERENCES ITEMS(ITEMID)
)
CREATE TABLE CATEGORIESS(
CATID INT PRIMARY KEY,
NAME NVARCHAR(255)
)
CREATE TABLE BELONGTO(
ITEMID INT,
CATID INT,
FOREIGN KEY (ITEMID) REFERENCES ITEMS(ITEMID),
FOREIGN KEY (CATID) REFERENCES CATEGORIESS(CATID),
PRIMARY KEY(ITEMID,CATID)
)
USE [PE_DBI202_Sp2021_B5]
--Q2
select *
from Employees
where Salary > 9000
--Q3
select [Link], [Link],min_salary
from Jobs
where min_salary > 5000 and JobTitle like'%manager%'
order by min_salary desc, [Link]
--Q4
select EmployeeID,FirstName,lastname,Salary,DepartmentName,StateProvince,
CountryID
from Employees
inner join Departments on [Link]=[Link]
inner join locations on [Link]=[Link]
where salary > 3000 and StateProvince = 'Washington' and
CountryID='US'
--Q5
select [Link],StreetAddress,City,StateProvince,
CountryID,count(DepartmentID) as NumberOfDepartments
from Locations
left join Departments on [Link]=[Link]
group by [Link],StreetAddress,City,StateProvince,
CountryID
order by NumberOfDepartments desc,[Link]
--Q6
select [Link],JobTitle,count(EmployeeID) as NumberOfEmployees
from jobs
inner join Employees on [Link]=[Link]
group by [Link],JobTitle
having count(EmployeeID) =(
select max(cnt)
from(
select count(EmployeeID) as cnt
from jobs
inner join Employees on [Link]=[Link]
group by [Link],JobTitle
) subquery
);
select [Link],JobTitle,count(EmployeeID) as NumberOfEmployees
from jobs
inner join Employees on [Link]=[Link]
group by [Link],JobTitle
having count(EmployeeID) >= all
(
select count(EmployeeID)
from jobs
inner join Employees on [Link]=[Link]
group by [Link],JobTitle
)
--Q7
SELECT [Link],
COUNT([Link]) FROM (
SELECT EmployeeID FROM Employees ts
WHERE Salary > 10000 OR EXISTS (
SELECT 1
FROM Employees e3
WHERE [Link] = [Link]
)) as emps1
left join
Employees mngs on [Link] = [Link]
left join
Employees emps2 ON [Link] = [Link]
GROUP BY [Link], [Link]
--Q8
create procedure pr2
@country varchar(10),
@numberOfDepartments int output
as
begin
set @numberOfDepartments =
(select count([Link]) as NumberOfDepartments
from Locations
inner join Departments on [Link]=[Link]
where [Link]=@country)
end
--Q9
CREATE TRIGGER TR1
ON EMPLOYEES
AFTER INSERT
AS
BEGIN
SELECT
[Link],[Link],[Link],[Link],[Link]
FROM inserted AS A
LEFT JOIN Departments ON [Link]=[Link]
END