create Database SelfExercis;
use SelfExercis;
drop database SelfExercis;
create table Salesman
(Salesmanid varchar(20) primary key,
Name varchar (30) not null,
City varchar (30) not null,
Commission char(12));
create table Customer
(Cust_id varchar(20) primary key,
Salesmanid varchar (20),
Cust_name varchar (30) not null,
City varchar (30) not null,
Grade char(15),
FOREIGN KEY(Salesmanid) REFERENCES Salesman(Salesmanid));
create table OrderS
(Order_no varchar(20) primary key,
Purch_amt int not null,
Order_date varchar (30) not null,
Cust_id varchar(20),
Salesmanid varchar (20),
FOREIGN KEY(Salesmanid) REFERENCES Salesman(Salesmanid),
FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id));
insert into Customer(Cust_id,Cust_name,City,Grade,Salesmanid)
values('101','Azanaw','Bahir Dar','Level I','001'),
('102','Mamo','AA','Level I','002'),
('103','Hirut','Mekele','Level
II','005'),
('104','Zufan','Dire
Dawar','Level II','003'),
('201','Chala','Harar','Level
III','004'),
('202','Kedir','Bahir
Dar','Level II','005'),
('203','Umer','Jijiga ','Level
III','006');
insert into Salesman(Salesmanid,Name,City,Commission)
values('001','Bekalu','Gondar','1345'),
('002','Dereje','Hawasa','1023'),
('003','Almaz','AA','970'),
('004','Fatuma','Semera','547'),
('005','Kumsa','Ambo','1678'),
('006','Dawit','Bahir
dar','598');
insert into OrderS
values('301', '32098','12-Nov-2011', '101', '001'),
('302', '55500','19-Dec-2012', '101', '004'),
('303', '72000','24-Jan-2012', '201', '003'),
('304', '98000','18-Aug-2011', '203', '002'),
('305', '125000','27-Jun-2011', '203', '005'),
('306', '9896', '10-Sep-2012', '202', '001');
select * from Customer;
select * from Salesman;
select * from OrderS;
-- 1. Find the names of all customers whose city is either Gondar or Bahir
Dar
select * from Customer where City='Bahir Dar' or City='Gondar';
--2. Write a query to find the name and numbers of all salesman who had more
than one customer
select Name, count (*) as tt from Salesman, Customer where
Salesman.Salesmanid=Customer.Salesmanid group by Name having count(Cust_id)>1;
---3. Write a query to find all orders with order amounts which are above average
amounts for their customers
select *from OrderS where Purch_amt>(select avg(Purch_amt) from OrderS);
--or
select avg(Purch_amt) from OrderS;
select *from OrderS where Purch_amt> 65415;
--4. Find the name of the customer who has the smallest purch-amt?
select min(Purch_amt) from OrderS;
-- 5. Write a syntax that displays all salesman name containing a letter “D”.
select *from Salesman where Name like '%d%';
--6. Write a syntax that displays all Salesman ID, name and City having commision >1000
and came from addis Ababa in descending order by their name
select Salesmanid,Name,City from Salesman where Commission <1000 and City='AA';
-- 7. Find the names of all customers except their city in AA and Bahir Dar
select * from Customer where City not in ('Bahir Dar', 'AA');
---8. Write a syntax to delete customer “Umer” from custtomer table
delete from Customer where Cust_name='Umer' and Salesmanid in(select *from Salesman
where Salesmanid='006');
-- delete from Customer where Salesmanid= Salesmanid (select *from Salesman
Cust_name='Umer' AND Salesmanid='006');
--9. Write a syntax that displays Salesmanall customers whose Salesman city address
is AA.
select *from Customer where Salesmanid in(select *from Salesman where City='AA');
--or
select *from Customer, Salesman where Customer.Salesmanid= Salesman.Salesmanid
and Salesman.City='AA';
--10. Write a syntax that displays all customers whose Purch_amt is between 5000 and
9000).
select *from OrderS;
select *from Customer, OrderS where Customer.Cust_id= OrderS.Cust_id and
OrderS.Purch_amt between 50000 and 90000;
--11. Write a query to display all the orders which values are greater than the
average order value for 19th December 2012
select *from OrderS where Order_date>'19-Dec-2012';
--12. Write a query to find the name and numbers of all salesman who had more
than one customer
select Salesman.Name, count (*) from Salesman, Customer where
Salesman.Salesmanid=Customer.Salesmanid group by Name having count(Cust_id)>1;
