Question

SALES DB

Write the SQL's for the following statements
a. Find the name of brown items that have been sold by the recreation department
b. Find the items that have never been delivered to the books department
c. Find the departments that have never sold a Geo positioning system
d. Find the items delivered for which there have been no sales
e. Find the name of the highest paid employee
f. Find the names of employees who make less than the average salary
g. List the full details, including the total quantity sold, for every item.
h. Find out how many different sales have taken place for items supplied by supplier number
101.

3. SALES database iColor SaleQty IName IType Delivery# Sale# Sale HA Item I + Delivery DQty Manages Boss of 主主 DName Dept th Works in + Employee Supplier Supplier# DFloor DPhone ESalary ENum SName Ename

Answer 1

Solution:

Create Tables and insert values to tables:

Query:

create table Department(DName varchar(50) primary key,DFloor int,DPhone bigint);
insert into Department values('Recreation department',1,8765346784);
insert into Department values('Books department',2,8765367543);
insert into Department values('Education department',3,9876543456);
select * from Department;
create table Employee(ENum int identity(1,1) primary key,Ename varchar(50),ESalary decimal(9,2),BossId int,DName varchar(50) ,
foreign key(BossId) references Employee(ENum), foreign key(DName) references Department(DName));
insert into Employee values('Ann',25000,1,'Recreation department');   
insert into Employee values('Mary',20000,1,'Recreation department');
insert into Employee values('Tom',22000,3,'Books department');   
insert into Employee values('Jose',15000,3,'Books department');
insert into Employee values('Sunil',30000,5,'Education department');   
insert into Employee values('Anila',25000,5,'Education department');
select * from Employee;
create table Item(IName varchar(50) primary key,Icolor varchar(50),Itype varchar(50));
insert into Item values('Game kits','brown','For kids')
insert into Item values('Gift Items','Green','For Seniors')
insert into Item values('Chair','brown','Long')
insert into Item values('Round-Chair','brown','short')
insert into Item values('Books','brown','Educational')
insert into Item values('Charts','Green','For students')
insert into Item values('Bed','wooden','Long')
insert into Item values('Geo positioning system','brown','')
select * from Item;
create table Supplier(SupplierId int primary key,SName varchar(50));
insert into Supplier values(101,'Sali')
insert into Supplier values(102,'Hajad')
insert into Supplier values(103,'Mani')
select * from Supplier;
create table Delivery(DeliveryId int primary key,SupplierId int,DName varchar(50),
foreign key(SupplierId) references Supplier(SupplierId), foreign key(DName) references Department(DName));
insert into Delivery values(1,101,'Recreation department');
insert into Delivery values(2,102,'Recreation department');
insert into Delivery values(3,101,'Books department');
insert into Delivery values(4,102,'Education department');
select * from Delivery;
create table Delivery_Item(DeliveryId int,IName varchar(50) primary key(DeliveryId,IName),DQty int,
foreign key(DeliveryId) references Delivery(DeliveryId), foreign key(IName) references Item(IName));
  
insert into Delivery_Item values(1,'Game kits',10)
insert into Delivery_Item values(1,'Round-Chair',3)
insert into Delivery_Item values(2,'Books',59)
insert into Delivery_Item values(2,'Bed',10);
insert into Delivery_Item values(3,'Books',10)
insert into Delivery_Item values(3,'Charts',3)
insert into Delivery_Item values(4,'Chair',5)
insert into Delivery_Item values(4,'Geo positioning system',10);
select * from Delivery_Item;
create table Sales(SalesId int primary key,SupplierId int,DName varchar(50) ,foreign key(DName) references Department(DName),
foreign key(SupplierId) references Supplier(SupplierId)) ;
insert into Sales values(1,101,'Recreation department');
insert into Sales values(3,101,'Books department');
insert into Sales values(4,102,'Education department');
select * from Sales;
create table Sales_Item(SalesId int,IName varchar(50) primary key(SalesId,IName),SaleQty int,
foreign key(SalesId) references Sales(SalesId), foreign key(IName) references Item(IName));
insert into Sales_Item values(1,'Game kits',10)
insert into Sales_Item values(1,'Round-Chair',3)
insert into Sales_Item values(3,'Books',10)
insert into Sales_Item values(3,'Charts',3)
insert into Sales_Item values(4,'Chair',5)
insert into Sales_Item values(4,'Geo positioning system',10);   
select * from Sales_Item;   

QUERY IN SQL EDITOR

TABLES IN SQL EDITOR

Question a.

Query:

select si.IName from Sales_Item si join Sales s on si.SalesId=s.SalesId join Department d on s.DName=d.DName join Item i on si.IName=i.IName
where i.Icolor='brown' and s.DName='Recreation department';

Question b.

Query:

select IName from Item where IName not in(select IName from Delivery_Item di join Delivery d on di.DeliveryId=d.DeliveryId where d.DName='books department')

Question c.

Query:

select DName from Department where DName not in(
select s.DName from Sales_Item si join item i on si.IName=i.IName join Sales s on si.SalesId=s.SalesId join Department d on s.DName=d.DName
where i.IName='Geo positioning system')

Question d.

Query:

select 'd' AS ResultOfQuestion
select IName from Delivery_Item where IName not in( select IName from Sales_Item )

Question e.

Query:

select Ename from Employee where ESalary=(select max(ESalary)from Employee)

Question f.

Query:

SELECT Ename FROM Employee e
WHERE ESalary < (select avg(ESalary) from Employee e2 );

Question g.

Query:

select si.IName,sum(si.SaleQty) as TotalQtySold from Sales_Item si group by si.IName

Question h.

Query:

select count (SalesId) As NoOfDifferentSales from Sales where SupplierId=101;

QUERY IN SQL EDITOR

RESULTS IN SQL EDITOR

..