DBMS SEM3 22012011024-INDU JEPH

PRACTICAL :- 5
AIM:-
Study the concepts of different operators, aggregate functions and various
joins.

ANS:-
create table salesmen(snum varchar2(6) primary key, sname varchar2(20)
not null, city varchar2(15), comm number(5,2), check (snum like 's%'));
insert into salesmen values('s1001','Piyush','London',0.12);
insert into salesmen values('s1002','Niraj','san jose',0.13);
insert into salesmen values('s1003','Miti','London',0.11);
insert into salesmen values('s1004','Rajesh','Barcelona',0.15);
insert into salesmen values('s1005','Haresh','new York',0.10);
insert into salesmen values('s1006','Ram','Bombay',0.10);
insert into salesmen values('s1007','Nehal','Delhi',0.09);
select * from salesmen;
OUTPUT:-

BATCH-3B2 1|Page
 DBMS SEM3 22012011024-INDU JEPH

ANS:-
create table customer(cnum varchar2(6) primary key, cname varchar2(20) not null,
city varchar2(15), rating number(5), snum varchar2(6), check (cnum like 'c%'),
foreign key(snum) references salesmen(snum));
insert into customer values('c2001','Hardik','london',100,'s1001');

insert into customer values('c2002','geeta','rome',200,'s1003');

insert into customer values('c2003','kavish','san jose',200,'s1002');

insert into customer values('c2004','dhruv','berlin',300,'s1002');

BATCH-3B2 2|Page
 DBMS SEM3 22012011024-INDU JEPH

insert into customer values('c2005','pratham','london',100,'s1001');

insert into customer values('c2006','vyomesh','san jose',300,'s1007');

insert into customer values('c2007','kirit','rome',100,'s1004');

insert into customer values('c2008','agam',null,200,'s1003');

insert into customer values('c2009','falgun','san jose',null,'s1001');

select * from customer;

OUTPUT:-

ANS:-

create table order_info(onum varchar2(6) primary key, amt number(10,2) not null, odate date, cnum
varchar2(6), snum varchar2(6), check (onum like 'o%'), foreign key(cnum) references
customer(cnum), foreign key(snum) references salesmen(snum)); insert into order_info
values('o3001',18.69,'10-mar-20','c2008','s1007');

insert into order_info values('o3003',767.19,'10-mar-20','c2001','s1001');

insert into order_info values('o3002',1900.10,'03-oct-21','c2007','s1004');

BATCH-3B2 3|Page
 DBMS SEM3 22012011024-INDU JEPH

insert into order_info values('o3005',5160.45,'04-oct-21','c2003','s1002');

insert into order_info values('o3006',1098.16,'10-mar-20','c2008','s1007');

insert into order_info values('o3009',1713.23,'10-april-20','c2002','s1003');

insert into order_info values('o3007',75.75,'10-april-20','c2004','s1002');

insert into order_info values('o3008',4723.00,'10-may-21','c2006','s1001');

insert into order_info values('o3010',1309.95,'10-may-21','c2004','s1002');

insert into order_info values('o3011',9891.88,'10-june-21','c2006','s1001');

select * from order_info;

Write SQL Queries for following questions:-

SELECT:-

1. Display commission values of without any repetition from Salesman

Table.
select distinct comm from salesmen;

BATCH-3B2 4|Page
 DBMS SEM3 22012011024-INDU JEPH

SPECIAL OPERATORS:-
2. Display all salesmen that were located in either BARCELONA or
LONDON (use IN keyword).
select sname from salesmen where city in ('Barcelona','London');

3. Display all salesmen with commission between 0.10 and 0.12.

select sname from salesmen where comm between 0.10 and 0.12;

BATCH-3B2 5|Page
 DBMS SEM3 22012011024-INDU JEPH

LIKE OPERATOR:-
4. List all the customers whose name’s third latter is ‘R’.
select cname from customer where cname like '__r%';

5. List all salesmen whose name start with letter ‘P’ and end letter is ‘h’.
select sname from salesmen where sname like 'P%h';

BATCH-3B2 6|Page
 DBMS SEM3 22012011024-INDU JEPH

NULL OPERATOR:
6. Find all records in customer table with NULL values in the city column.
select * from customer where city is null;

7. Write two queries that will produce all orders taken on October 3rd or
4th , 2020. a) Use BETWEEN b) Use OR.
select * from order_info where odate between '03-oct-21' and '04-
oct-21';
select * from order_info where odate='03-oct-21' or odate='04-
oct-21';

8. Write a query that selects all orders without ZEROS or NULLS in amount
field.

BATCH-3B2 7|Page
 DBMS SEM3 22012011024-INDU JEPH

select * from order_info where amt is not null or amt!FUNCTIONS:-

9. Count the numbers of salesmen without duplication from the

order_info table.
select count(distinct snum) from order_info;

10.Count the rating of customers (with NULL and without NULL)

select count(*) from customer;
select count(rating) from customer;

11 Find the largest order taken by each salesperson.

select snum,max(amt) from order_info group by snum;

BATCH-3B2 8|Page
 DBMS SEM3 22012011024-INDU JEPH

12. Find the largest order taken by each salesperson on each date.
select snum,odate,max(amt) from order_info group by snum,odate;

13. Find total amount ordered by each customer, only display records whose
total amount is greater than 800.
select cnum,sum(amt) from order_info group by cnum having sum(amt)>800;

BATCH-3B2 9|Page
 DBMS SEM3 22012011024-INDU JEPH

14. Write a query that counts the number of different non-NULL city in the
customer table.
select count(distinct city) from customer;

15. Display sname and commission from salesmen in descending order of

salesman number.
select sname,comm from salesmen order by snum desc;

16. Assume each salesperson has a 0.12 commission. Write a query on the
order_info table that will produce the order number, the salesperson number
and the amount of the salesperson’s commission for that order.
select onum,snum,amt*0.12 from order_info;

BATCH-3B2 10 | P a g e
 DBMS SEM3 22012011024-INDU JEPH

17. Write a query on the customers table that will find the highest rating in
each city. Put the output in this form. For the city (city column), the highest
rating is: (rating column).
select 'For the city ', the highest rating is : ' || max(rating) || '' from customer
group by city;

18. Write a query that totals the orders for each day and places the results in
descending order.
select odate, sum(amt) from order_info group by odate order by sum(amt)
desc;

BATCH-3B2 11 | P a g e
 DBMS SEM3 22012011024-INDU JEPH

JOIN OPERATION:-
19. Show the names of all customers matched with the salesmen serving
them.
select cname, sname from customer c,salesmen s where c.snum = s.snum;

20. Write a query that lists each order number followed by customer name
who made the order.
select onum, cname from order_info o, customer c where o.cnum = c.cnum;

21. Write a query that gives the names of both the salesperson and the
customer for each order after the order number.
select onum, sname, cname from order_info o, customer c, salesmen s where
o.cnum = c.cnum and o.snum = s.snum;

BATCH-3B2 12 | P a g e
 DBMS SEM3 22012011024-INDU JEPH

22. Write a query that produces all customers serviced by salesmen with a
commission above 0.12. Output shows the customer’s name, the
salesperson’s name and the salesperson’s rate of commission.
select cname,sname,comm from customer c,salesmen s where c.snum=s.snum
and comm > 0.12;

23. Write a query that calculates the amount of the salesperson’s commission
on each order by a customer with a rating above 100.
select sname, amt*comm from order_info o, customer c, salesmen s where
rating>100 and s.snum=c.snum and s.snum=o.snum and c.cnum = o.cnum;

OTHERS:-
24. Create a union of two queries that shows the names, cities and ratings of
all customers. Those with rating of >=200 should display ‘HIGH RATING’ and
those with <200 should display ‘LOW RATING’.

BATCH-3B2 13 | P a g e
 DBMS SEM3 22012011024-INDU JEPH

select cname, city, 'HIGH RATING' as rating from customer where rating >= 200
union select cname, city, 'LOW RATING' as rating from customer where rating <
200;

25. Find all customers who made order on 10th March 2020 using sub-query.

select cname from customer c where c.cnum in (select o.cnum from order_info
o where o.odate = '10-mar-20');

26. Find all customers having rating greater than any customer in ‘ROME’.

select c.cname from customer c where c.rating > any(select rating from
customer where city = 'rome');

27. Create another table London_staff having same structure as salesmen

table.
create table London_staff as select * from salesmen;

BATCH-3B2 14 | P a g e
 DBMS SEM3 22012011024-INDU JEPH

28. Delete all salesmen who have at least one customer with a rating of 100
from salesmen table.
delete from salesmen where snum in (select s.snum from salesmen s join
customer c on s.snum = c.snum where c.rating = 100);

BATCH-3B2 15 | P a g e