ADBMS – SQL Queries – BCA-III

Create following Three Tables:-

1. Salesman:

SNUM SNAME CITY COMMISSION

----------------------------------------------------------------------------------
1001 PIYUSH LONDON 12%
1002 NIRAJ SURAT 13%
1003 MITI LONDON 11%
1004 RAJESH BARODA 15%
1005 ANAND NEW DELHI 10%
1006 RAM PATAN 10%
1007 LAXMAN BOMBAY 09%

SNUM : A Unique number assign to each salesman.

SNAME: The name of salesman.
CITY : The location of salesman.
COMMISSION : The salesman commission on order.

2. Customer:

CNUM CNAME CITY RATING SNUM

--------------------------------------------------------
2001 HARDIK LONDON 100 1001
2002 GITA ROME 200 1003
2003 LAXIT SURAT 200 1002
2004 GOVIND BOMBAY 300 1002
2005 CHANDU LONDON 100 1001
2006 CHAMPAK SURAT 300 1007
2007 PRATIK ROME 100 1004

CNUM : A Unique number assign to each customer.

CNAME: The name of customer.
CITY : The location of customer.
RATING: A level of preference indicator given to this customer.
SNUM : A salesman number assign to this customer.

3. Order:-

ONUM AMOUNT ODATE CNUM SNUM

--------------------------------------------------------
3001 18.69 10/03/99 2008 1007
3002 767.19 10/03/99 2001 1001
3003 1900.10 10/03/99 2007 1004
3004 5160.45 10/03/99 2003 1002
3005 1098.25 10/04/99 2008 1007
3006 1713.12 10/04/99 2002 1003
3007 75.75 10/05/99 2004 1002
3008 4723.00 10/05/99 2006 1001
3009 1309.95 10/05/99 2004 1002
3010 9898.87 10/06/99 2006 1001

ONUM : A Unique number assign to each Order.

AMOUNT: Amount of order in Rs.
ODATE : The date of order.
CNUM : The number of customer making the order.
SNUM : The number of salesman credited with the sale.

How to Create Tables:-

how to create table in oracle using sql.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
first of all define the structure of the table.

here we will create the table of salesman, customer and order.

salesman table:

fieldname datatype size

snum number (4)
sname varchar2 (10)
city varchar2 (10)
commission number (2)

query: create table salesman(snum number(4), sname varchar2(10), city varchar2(10), commission
number(2));

customer table:

fieldname datatype size

cnum number (4)
cname varchar2 (10)
city varchar2 (10)
rating number (3)
snum number (4)

query: create table customer(cnum number(4), cname varchar2(10), city varchar2(10), rating number(3),
snum number(4));

orders table:

fieldname datatype size

onum number (4)
amount number (6,2)
odate date
cnum number (4)
snum number (4)

query: create table orders(onum number(4), amount number(6,2), odate date, cnum number(4), snum
number(4));

Note: Please Do not use ‘order’ as table name. You can use Orders or order1 etc..

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
How to Insert Data:

Salesman Table:

Method 1: (If you want to insert data like data entry form then use this method)

insert into salesman values(&snum,'&sname','&city',&commission);

now Enter Values. If you want to execute same query or previous query again the just type / and press
enter key.

Method 2: (If you want add data in all fields then use this method)
insert into salesman values (1001,’PIYUSH’,’LONDON’,12);
insert into salesman values (1002,’NIRAJ’,’SURAT’, 13);
insert into salesman values (1003,’MITI’,’LONDON’,11);
insert into salesman values (1004,’RAJESH’,’BARODA’,15);
insert into salesman values (1005,’ANAND’,’NEW DELHI’,10);
insert into salesman values (1006,’RAM’,’PATAN’,10);
insert into salesman values (1007,’LAXMAN’,’BOMBAY’,09);

Method 3: (If you want to add data in selected fields then use this method)

Insert into salesman(snum,sname,city,commission) values(1001,’PIYUSH’,’LONDON’,12);

Customer Table:

Method 1:
insert into customer values(&cnum,'&cname','&city',&rating,&snum);

Method 2:
insert into customer values(2001,’HARDIK’,’LONDON’,100,1001);
insert into customer values(2002,’GITA’,’ROME’,200,1003);
insert into customer values(2003,’LAXIT’,’SURAT’,200,1002);
insert into customer values(2004,’GOVIND’,’BOMBAY’,300,1002);
insert into customer values(2005,’CHANDU’,’LONDON’,100,1001);
insert into customer values(2006,’CHAMPAK’,’SURAT‘,300,1007);
insert into customer values(2007,’PRATIK’,’ROME’,100,1004);

Method 3:
insert into customer(cnum,cname,city,rating,snum)values(2001,’HARDIK’,’LONDON’,100,1001);

Orders Table:

Method 1:
insert into orders values(&onum,&amount,'&odate',&cnum,&snum);

Method 2:
insert into orders values(3001,18.69,’10/03/99’,2008,1007);
insert into orders values(3002,767.19,’10/03/99’, 2001,1001);
insert into orders values(3003,1900.10,’10/03/99’,2007,1004);
insert into orders values(3004,5160.45,’10/03/99’,2003,1002);
insert into orders values(3005,1098.25,’10/04/99’,2008,1007);
insert into orders values(3006,1713.12,‘10/04/99’,2002,1003);
insert into orders values(3007,75.75,’10/05/99’,2004,1002);
insert into orders values(3008,4723.00,’10/05/99’,2006,1001);
insert into orders values(3009,1309.95,10/05/99,2004,1002);
insert into orders values(3010,9898.87,10/06/99,2006,1001);

Method 3:
insert into orders(onum,amount,odate,cnum,snum)values(3001,18.69,’10/03/99’,2008,1007);

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
SOLVED SQL QUERIES:-
==================================================================

1. Produce the order no, amount and date of all orders.

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

SQL> select onum, amount,odate from orders;

ONUM AMOUNT ODATE

---------- ---------- ---------
3001 18.69 10-MAR-99
3002 767.19 10-MAR-99
3003 1900.1 10-MAR-99
3004 5160.45 10-MAR-99
3005 1098.25 10-APR-99
3006 1713.12 10-APR-99
3007 75.75 10-MAY-99
3008 4723 10-MAY-99
3009 1309.95 10-MAY-99
3010 9898.87 10-JUN-99

10 rows selected.

==================================================================

2. Give all the information about all the customers with salesman number 1001.

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004

SQL> select * from customer where snum=1001;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2005 chandu london 100 1001

==================================================================

3. Display the information in the sequence of city, sname, snum, and Commission.

SNUM SNAME CITY COMMISSION

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 miti london 11
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9

SQL> select city, sname, snum, commission from salesman;

CITY SNAME SNUM COMMISSION

---------- ---------- ---------- ----------
london piyush 1001 12
surat niraj 1002 13
london miti 1003 11
baroda rajesh 1004 15
new delhi anand 1005 10
patan ram 1006 10
bombay laxman 1007 9

7 rows selected.

==================================================================

4. List of rating followed by the name of each customer in Surat.

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004

SQL> select cname, rating from customer where city='surat';

CNAME RATING
---------- ----------
laxit 200
champak 300

==================================================================

5. List of snum of all salesmen with orders in order table without an duplicates.

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

SQL> select distinct(snum) from orders;

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

SNUM
----------
1001
1002
1003
1004
1007

==================================================================

6. List of all orders for more than Rs. 1000.

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

SQL> select * from orders where amount>1000;

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

7 rows selected.

==================================================================

7. List out names and cities of all salesmen in London with commission above 10%

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 miti london 11
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9

SQL> select sname, city, commission from salesman where city='london' and commission>10;

SNAME CITY COMMISSION

---------- ---------- ----------
piyush london 12
miti london 11

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
==================================================================

8. List all customers excluding those with rating <= 100 or they are located in Rome.

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004

SQL> select * from customer where not(rating<=100 or city='rome');

CNUM CNAME CITY RATING SNUM

----- ---------- ---------- ---------- ----------
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2006 champak surat 300 1007

==================================================================

9. List all order for more than Rs. 1000 except the orders of snum,1006 of 10/03/97

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

SQL> select * from orders where amount>1000 and not(snum=1006 and odate='10-mar-99');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001
==================================================================

10. List all orders taken on October 3rd or 4th or 6th 1997.

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

SQL> select * from orders where odate='03-oct-99' or odate='04-oct-99' or odate='06-oct-99';

no rows selected

or

SQL> select * from orders where odate in ('3-oct-99','4-oct-99','6-oct-99');

no rows selected
==================================================================

11. List all customers whose names begins with a letter 'C'.

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004

SQL> select * from customer where cname like 'c%';

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2005 chandu london 100 1001
2006 champak surat 300 1007
==================================================================

12. List all customers whose names begins with letter 'A' to 'G'

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004

SQL> select * from customer where cname like 'a%' or cname like 'b%' or cname like 'c%' or
cname like 'd%' or cname like 'e%' or cname like 'f%' or cname like 'g%';

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2002 gita rome 200 1003
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
or

SQL> select * from customer where substr(cname,1,1) between 'a' and 'g';

CNUM CNAME CITY RATING SNUM

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
---------- ---------- ---------- ---------- ----------
2002 gita rome 200 1003
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
==================================================================

13. List all orders with zero or NULL amount.

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

SQL> select * from orders where amount=0 or amount is NULL;

no rows selected
==================================================================

14. Find out the largest orders of salesman 1002 and 1007.

SQL> select max(amount) LARGEST_ORDER from orders where snum in(1002,1007);

LARGEST_ORDER
-------------
5160.45

OR

SQL> select snum, max(amount) from orders where snum in(1002,1007) group by snum

SNUM MAX(AMOUNT)
---------- -----------
1002 5160.45
1007 1098.25

OR

SQL> select snum, max(amount) max from orders group by snum having snum=1002 or snum=1007;

SNUM MAX
---------- ----------
1002 5160.45
1007 1098.25

OR

SQL> select snum,max(amount) max from orders where snum=1002 or snum=1007 grop by snum;

SNUM MAX
---------- ----------
1002 5160.45
1007 1098.25
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

15. Count all orders of 10-Mar-97.

SQL> select count(odate) as "No. of Orders on 10-MAR-99" from orders where odate='10-mar-99';

No. of Orders on 10-MAR-99

--------------------------
4
==================================================================

16. Calculate the total amount ordered.

SQL> select sum(amount) as "Total Amount of Orders" from orders;

Total Amount of Orders

----------------------
26665.37
==================================================================

17. Calculate the average amount ordered.

SQL> select avg(amount) as "Total Average Amount" from orders;

Total Average Amount

--------------------
2666.537
==================================================================

18. Count the no. of salesmen currently having orders.

SQL> select snum, count(onum) from orders group by snum;

For Each Salesman:

SNUM COUNT(ONUM)
---------- -----------
1001 3
1002 3
1003 1
1004 1
1007 2

For All Salesman:

SQL> select count(distinct snum) as "No. of Salesman having Orders" from orders;

No. of Salesman having Orders

-----------------------------
5
==================================================================

19. Find the largest order taken by each salesman on each date.

SQL> select odate, max(amount) from orders group by odate;

ODATE MAX(AMOUNT)
--------- -----------
10-MAR-99 5160.45
10-APR-99 1713.12
10-MAY-99 4723
10-JUN-99 9898.87

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
==================================================================

20. Find the largest order taken by each salesman on 10/03/1997.

SQL> select odate, snum, max(amount) from orders where odate='10-mar-99' group by odate, snum;

ODATE SNUM MAX(AMOUNT)

--------- ---------- -----------
10-MAR-99 1001 767.19
10-MAR-99 1002 5160.45
10-MAR-99 1004 1900.1
10-MAR-99 1007 18.69
==================================================================
21. Count the no. of different non NULL cities in the Customer table.

SQL> select count(distinct city) as "No. of Non Null Cities" from customer where city is not null;

No. of Non Null Cities

----------------------
4
==================================================================

22. Find out each customer's smallest order.

SQL> select cnum, min(amount) as "Smallest Order" from orders group by cnum;

CNUM Smallest Order

---------- --------------
2001 767.19
2002 1713.12
2003 5160.45
2004 75.75
2006 4723
2007 1900.1
2008 18.69
==================================================================

23. Find out the customer in alphabetical order whose name begins with 'G'

SQL> select * from customer where cname like 'g%' order by cname;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2002 gita rome 200 1003
2004 govind bombay 300 1002
==================================================================

24. Count the no. of salesmen registering orders for each day.

SQL> select odate, count(snum) from orders group by odate;

ODATE COUNT(SNUM)
--------- -----------
10-MAR-99 4
10-APR-99 2
10-MAY-99 3
10-JUN-99 1
==================================================================

25. List all salesmen with their % of commission.

SQL> select snum, sname, city, commission || '%' as commission from salesman;

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12%
1002 niraj surat 13%
1003 miti london 11%
1004 rajesh baroda 15%
1005 anand new delhi 10%
1006 ram patan 10%
1007 laxman bombay 9%
==================================================================
26. Display the no. of order for each day in the following format. dd-mon-yy.

SQL> select 'for ' || odate || ' There are ' || count(*) || ' Orders' from orders group by odate;

'FOR'||ODATE||'THEREARE'||COUNT(amount)||'ORDERS'
-----------------------------------------------------------------------
for 10-MAR-99 There are 4 Orders
for 10-APR-99 There are 2 Orders
for 10-MAY-99 There are 3 Orders
for 10-JUN-99 There are 1 Orders
==================================================================

27. Assume each salesperson has a 12% commission. Write a query on the order table that will produce
the order
number, salesman no and amount of commission for that order.

SQL> select onum, snum, amount*.12 from orders;

ONUM SNUM AMOUNT*.12

---------- ---------- ----------
3001 1007 2.2428
3002 1001 92.0628
3003 1004 228.012
3004 1002 619.254
3005 1007 131.79
3006 1003 205.5744
3007 1002 9.09
3008 1001 566.76
3009 1002 157.194
3010 1001 1187.8644

10 rows selected.
==================================================================

28. Find the highest rating in each city in the following format:

SQL> select 'highest rating' || max(rating) || ' is in the city' || city || 'lowest rating' || min(rating)
|| 'is in the city' || city from customer group by city;

'HIGHESTRATING'||MAX(RATING)||'ISINTHECITY'||CITY||'LOWESTRATING'||MIN(RATING)||

--------------------------------------------------------------------------------

highest rating300 is in the citybombaylowest rating300is in the citybombay

highest rating100 is in the citylondonlowest rating100is in the citylondon
highest rating200 is in the cityromelowest rating100is in the cityrome
highest rating300 is in the citysuratlowest rating200is in the citysurat

==================================================================

29. List all customers in descending order of rating.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

SQL> select * from customer order by rating desc;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2004 govind bombay 300 1002
2006 champak surat 300 1007
2002 gita rome 200 1003
2003 laxit surat 200 1002
2001 hardik london 100 1001
2005 chandu london 100 1001
2007 pratik rome 100 1004
==================================================================

30. Calculate the total of orders for each day.

SQL> select odate, sum(amount) from orders group by odate;

ODATE SUM(AMOUNT)
--------- -----------
10-MAR-99 7846.43
10-APR-99 2811.37
10-MAY-99 6108.7
10-JUN-99 9898.87
==================================================================
31. Show the name of all customers with their salesman's name.

SQL> select cname, sname from customer c, salesman s where s.snum=c.snum;

CNAME SNAME
---------- -------
hardik piyush
chandu piyush
laxit niraj
govind niraj
gita miti
pratik rajesh
champak laxman

7 rows selected.
==================================================================

32. List all customers and salesmen who shared a same city.

SQL> select cname, sname, c.city from customer c, salesman s where c.city=s.city;

CNAME SNAME CITY

---------- ---------- ----------
govind laxman bombay
hardik piyush london
chandu piyush london
hardik miti london
chandu miti london
laxit niraj surat
champak niraj surat
==================================================================

33. List all orders with the names of their customer and salesman.

SQL> select onum, amount, odate, cname, sname from orders o, customer c, salesman s where
o.cnum=c.cnum
and o.snum=s.snum order by o.onum;

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

ONUM AMOUNT ODATE CNAME SNAME

---------- ---------- --------- ---------- ----------
3002 767.19 10-MAR-99 hardik piyush
3003 1900.1 10-MAR-99 pratik rajesh
3004 5160.45 10-MAR-99 laxit niraj
3006 1713.12 10-APR-99 gita miti
3007 75.75 10-MAY-99 govind niraj
3008 4723 10-MAY-99 champak piyush
3009 1309.95 10-MAY-99 govind niraj
3010 9898.87 10-JUN-99 champak piyush

8 rows selected.
==================================================================

34. List all orders by the customers not located in the same city as their salesman.

SQL> select distinct o.* from orders o, customer c, salesman s where c.city<>s.city and
c.snum=s.snum and c.cnum=o.cnum order by o.onum;

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3003 1900.1 10-MAR-99 2007 1004
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001
==================================================================

35. List all customers serviced by salesman with commission above 12%.

SQL> select c.* from customer c, salesman s where c.snum=s.snum and s.commission
>12;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2007 pratik rome 100 1004
==================================================================

36. Calculate the amount of the salesman commission on each order by customer with rating above 100.

SQL> select s.sname, c.cname, o.onum, o.amount*s.commission/100 "commission"

from salesman s, customer c, orders o where s.snum=c.snum and c.rating>100 and c.cnum=o.cnum;

SNAME CNAME ONUM commission

---------- ---------- ---------- ----------
niraj laxit 3004 670.8585
niraj govind 3007 9.8475
niraj govind 3009 170.2935
miti gita 3006 188.4432
laxman champak 3008 425.07
laxman champak 3010 890.8983
==================================================================

37. Find all pairs of customers having the same rating without duplication.

SQL> select a.cname, b.cname from customer a, customer b where a.rating=b.rating and
b.cnum>a.cnum;

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
CNAME CNAME
---------- ----------
hardik chandu
hardik pratik
chandu pratik
gita laxit
govind champak
==================================================================

38. List all customers located in cities where salesman Niraj has customers.

SQL> select * from customer where city in(select distinct city from customer where
snum=(select snum from salesman where sname='niraj'));

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2004 govind bombay 300 1002
2003 laxit surat 200 1002
2006 champak surat 300 1007
==================================================================

39. find all pairs of customers served by a single salesman with the salesman's name and no.

SQL> select c.cname, c1.cname, c.snum, s.sname from salesman s, customer c, customer c1
where c.snum=c1.snum and c.cnum>c1.cnum and c.snum=s.snum;

CNAME CNAME SNUM SNAME

---------- ---------- ---------- ----------
chandu hardik 1001 piyush
govind laxit 1002 niraj
==================================================================

40. List all salesmen who are living in the same city with out duplicate rows.

SQL> select distinct s.* from salesman s, salesman s1 where s.snum<>s1.snum and s.city=s1.city;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1003 miti london 11
1001 piyush london 12
==================================================================

41. Produce the name and city of all the customers with the same rating as Hardik'.

SQL> select cname, city from customer where rating=(select rating from customer where
cname='hardik');

CNAME CITY
---------- ----------
hardik london
chandu london
pratik rome
==================================================================

42. Extract all orders of Miti.

SQL> select * from orders where snum=(select snum from salesman where sname='miti');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3006 1713.12 10-APR-99 2002 1003
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

43. Extract all orders of Baroda's salesmen.

SQL> select * from orders where snum=(select snum from salesman where city='baroda');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3003 1900.1 10-MAR-99 2007 1004
==================================================================

44. Find all orders of the salesman who services 'Hardik'

SQL> select * from orders where snum=(select snum from customer where cname='hardik');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3002 767.19 10-MAR-99 2001 1001
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001

SQL> select o.* from orders o, customer c where c.snum=o.snum and c.cname='hardik';

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3002 767.19 10-MAR-99 2001 1001
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001
==================================================================

45. List all orders that are greater than the average of April 10, 1997

SQL>select * from orders where amount > (select avg(amount) from orders where odate='10-apr-97')

no rows selected.

SQL> select * from orders where amount > (select avg(amount) from orders where odate='10-apr-99');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3006 1713.12 10-APR-99 2002 1003
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001
==================================================================

46. Find all orders attributed to salesmen in 'London'.

SQL> select * from orders where snum in (select snum from salesman where city='london');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3002 767.19 10-MAR-99 2001 1001
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001
3006 1713.12 10-APR-99 2002 1003

SQL>select o.* from orders o, salesman s where o.snum=s.snum and s.city='london';

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3002 767.19 10-MAR-99 2001 1001

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001
3006 1713.12 10-APR-99 2002 1003
==================================================================

47. List the commission of all salesmen serving customers in 'London'.

SQL>select cname, sname, commission from customer c, salesman s where c.snum=s.snum and
c.city='london';

CNAME SNAME COMMISSION

---------- ---------- ----------
hardik piyush 12
chandu piyush 12
==================================================================

48. Find all customers whose cnum is 1000 above than the snum of Niraj.

SQL> select * from customer where cnum > (select snum+1000 from salesman where sname='niraj');

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004
==================================================================

49. Count the no. of customers with the rating above than the average of 'Surat'.

SQL>select count(cnum)"No. of Customers" from customer where

rating > (select avg(rating) from customer where city='surat');

No. of Customers
----------------
2
==================================================================

50. List all orders of the customer 'Chandresh'.

SQL> select * from orders where cnum=(select cnum from customer where cname='chandresh');

no rows selected

SQL>select * from orders where cnum in (select cnum from customer where cname='chandresh');

no rows selected

SQL>select * from orders where cnum in (select cnum from customer where cname='champak');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001
==================================================================

51. Produce the name and rating of all customers who have above average orders.

SQL> select cname, rating from customer where cnum in (select cnum from orders where amount>(select
avg(amount) from orders));

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

CNAME RATING
---------- ----------
laxit 200
champak 300
==================================================================

52. Find all customers with orders on 3rd Oct., 1997 using correlate sub query.

SQL>select * from customer where cnum in (select cnum from orders where odate='3-oct-97');

no rows selected
==================================================================

53. List the name and number of all salesmen who has more than Zero customer.

SQL> select sname, snum from salesman where snum in(select c.snum from customer c, salesman s
where
c.snum=s.snum);

SNAME SNUM
---------- ----------
piyush 1001
niraj 1002
miti 1003
rajesh 1004
laxman 1007

SQL> select sname, snum from salesman where snum in(select distinct snum from customer);

SNAME SNUM
---------- ----------
piyush 1001
niraj 1002
miti 1003
rajesh 1004
laxman 1007

SQL> select distinct s.* from salesman s, customer c where c.snum=s.snum;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 miti london 11
1004 rajesh baroda 15
1007 laxman bombay 9
==================================================================

54. Calculate the total amount ordered on each day eliminating the days where the total amount was not
at least Rs. 2000 above the maximum amount of that day.

SQL>select odate, sum(amount)"Sales Per Day" from orders group by odate having sum(amount)>2000;

ODATE Sales Per Day

--------- -------------
10-MAR-99 7846.43
10-APR-99 2811.37
10-MAY-99 6108.07
10-JUN-99 9898.87
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
55. Using correlated sub query find the name and number of all customer with rating equal to maximum
for their city.

SQL>select cnum, cname, city, rating from customer

where (city,rating) in (select city, max(rating)from customer group by city);

CNUM CNAME CITY RATING

---------- ---------- ---------- ----------
2004 govind bombay 300
2001 hardik london 100
2005 chandu london 100
2002 gita rome 200
2006 champak surat 300

SQL>select cnum,cname,city, rating from customer

where rating in(select max(rating) from customer group by city);

CNUM CNAME CITY RATING

---------- ---------- ---------- ----------
2001 hardik london 100
2005 chandu london 100
2007 pratik rome 100
2002 gita rome 200
2003 laxit surat 200
2004 govind bombay 300
2006 champak surat 300

7 rows selected.
==================================================================

56. Select the name and number of all salesmen who have customers their cities.

SQL>select distinct s.snum, s.sname from salesman s, customer c where s.city=c.city and s.snum=c.snum

SNUM SNAME
---------- ----------
1001 piyush
1002 niraj
==================================================================

57. find all salesmen who have customers with rating > 300

SQL>select * from salesman where snum in(select snum from customer where rating>300);
no rows selected

SQL>select distinct s.* from salesman s, customer c where s.snum=c.snum and c.rating>300;
no rows selected

SQL>select * from salesman where snum in(select snum from customer where rating=300);

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1002 niraj surat 13
1007 laxman bombay 9

SQL>select distinct s.* from salesman s, customer c where s.snum=c.snum and c.rating=300;

SNUM SNAME CITY COMMISSION

----- ---------- ---------- ----------
1002 niraj surat 13
1007 laxman bombay 9
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

58. List all salesmen with customers located in their cities.

SQL> select s.snum, s.sname, s.city,c.city from salesman s, customer c where

s.city=c.city and s.snum=c.snum;

SNUM SNAME CITY CNAME CITY

---------- ---------- ---------- ---------- ----------
1001 piyush london chandu london
1001 piyush london hardik london
1002 niraj surat laxit surat
==================================================================

59. Find all salesmen for whom there are customers that follow them alphabetical order.

SQL> select s.snum,s.sname,c.cname from salesman s, customer c where c.snum=s.snum order by

c.cname;

SNUM SNAME CNAME

---------- ---------- ----------
1007 laxman champak
1001 piyush chandu
1003 miti gita
1002 niraj govind
1001 piyush hardik
1002 niraj laxit
1004 rajesh pratik
==================================================================

60. Find all customers having rating greater than any customer in 'Rome'.

SQL> select * from customer where rating > any(select rating from customer where city='rome');

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2006 champak surat 300 1007
==================================================================

61. List all order that has amount grater than at least one of the orders from 6th October, 1997.

SQL> select * from orders where amount > any(select amount from orders where odate='6-oct-99');

no rows selected
==================================================================

62. Find all orders with amounts smaller than any amount for a customer in 'Rome'.

SQL> select * from orders where amount < any(select amount from orders o, customer c where
o.cnum=c.cnum and c.city='rome');

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3009 1309.95 10-MAY-99 2004 1002

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
6 rows selected.

SQL>select * from orders where amount < any(select amount from orders where cnum in(select cnum
from customer where city='rome'));

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3009 1309.95 10-MAY-99 2004 1002

6 rows selected.
==================================================================

63. Find all the customers who have greater rating than every customer in 'Rome'.

SQL> select * from customer where rating > all(select rating from customer where city='rome');

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2004 govind bombay 300 1002
2006 champak surat 300 1007
==================================================================

64. Select all customers whose rating doesn't match with any rating customer of 'Surat'.

SQL> select * from customer where rating <> all (select rating from customer where city='surat');

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2005 chandu london 100 1001
2007 pratik rome 100 1004

SQL>select * from customer where rating not in (select rating from customer where city='surat');

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2005 chandu london 100 1001
2007 pratik rome 100 1004
==================================================================

65. List all customers whose ratings are equal to or greater than ANY 'Niraj'

SQL> select * from customer where rating>=any(select rating from customer c, salesman s
where sname='niraj' and s.snum=c.snum);

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2006 champak surat 300 1007

SQL>select * from customer where rating>=any(select rating from customer where

snum in(select snum from salesman where sname='niraj'));

CNUM CNAME CITY RATING SNUM

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
---------- ---------- ---------- ---------- ----------
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2006 champak surat 300 1007
==================================================================

66. Find out which salesman produce largest and smallest orders on each date.

SQL> select oa.odate, oa.snum, oa.amount maximun, ob.snum, ob.amount minimum from orders oa,
orders
ob where (oa.amount, ob.amount) in (select max(amount), min(amount) from orders group by odate);

ODATE SNUM MAXIMUN SNUM MINIMUM

--------- ---------- ---------- ---------- ----------
10-APR-99 1003 1713.12 1007 1098.25
10-MAY-99 1001 4723 1002 75.75
10-MAR-99 1002 5160.45 1007 18.69
10-JUN-99 1001 9898.87 1001 9898.87
==================================================================

67. 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'

SQL>select cname, city, rating || ' High Rating' as rating from customer where rating >=200 union select
cname, city, rating||' Low Rating' as rating from customer where rating<200;
CNAME CITY RATING
---------- ---------- ------------------
champak surat 300 High Rating
chandu london 100 Low Rating
gita rome 200 High Rating
govind bombay 300 High Rating
hardik london 100 Low Rating
laxit surat 200 High Rating
pratik rome 100 Low Rating

7 rows selected.
==================================================================

68. Insert a row into salesmen table with the values snum is 100 salesman name is Rakesh,
city is unknown and commission is 14%.

SQL> insert into salesman values(100,'rakesh','',14);

1 row created.

SQL> insert into salesman(snum,sname,commission) values(100,'rakesh',14);

1 row created.

SQL> select * from salesman;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 miti london 11
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
100 rakesh 14

8 rows selected.
==================================================================

69. Insert a row in to customer table with values London, Pratik a 2005 for the columns city, name and
number.

SQL> insert into customer(cnum,cname,city) values(2005,'pratik','london');

1 row created.

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 100 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004
2005 pratik london

8 rows selected.
==================================================================

70. Create another table London staff having same structure as salesman table.

SQL> create table londonstaff as select * from salesman where snum=null;

Table created.

SQL> create table londonstaff as select * from salesman where snum is null;

Table created.

SQL> desc londonstaff;

Name Null? Type

----------------------------------------- -------- --------------------
SNUM NUMBER(4)
SNAME VARCHAR2(10)
CITY VARCHAR2(10)
COMMISSION NUMBER(2)
==================================================================

71. Insert all the rows of salesmen table with city London in the London staff table.

SQL> insert into londonstaff select * from salesman where city='london';

2 rows created.

SQL> select * from londonstaff;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1003 miti london 11
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
72. Create another table Day totals with two attributes date and total and insert rows into this table from
order table.

SQL> create table daytotals as select odate,amount from orders;

Table created.

SQL> select * from daytotals;

ODATE AMOUNT
--------- ----------
10-MAR-99 18.69
10-MAR-99 767.19
10-MAR-99 1900.1
10-MAR-99 5160.45
10-APR-99 1098.25
10-APR-99 1713.12
10-MAY-99 75.75
10-MAY-99 4723
10-MAY-99 1309.95
10-JUN-99 9898.87

10 rows selected.
==================================================================

73. Create a duplicate of the salesmen table with a name Multicust. Now delete all the rows from the
salesmen table.

SQL>create table multicust as select * from salesman;

Table Created.

SQL> create table multicust(snum,sname,city,commission) as select snum,sname, city,commission from

salesman;

Table created.

SQL> select * from multicust;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 miti london 11
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9
100 rakesh 14

8 rows selected.

Delete Rows:

SQL> delete salesman;

8 rows deleted.

SQL> delete from salesman;

8 rows deleted.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
SQL> select * from salesman;

no rows selected
==================================================================

74. Get back all the rows of salesmen table from its duplicate table.

SQL> insert into salesman select * from multicust;

8 rows created.

SQL> select * from salesman;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 miti london 11
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9
100 rakesh 14

8 rows selected.
==================================================================

75. Remove all orders from customer Chandresh from the orders table.

SQL>delete from orders where cnum in (select cnum from customer where cname='chandresh');

0 rows deleted.
==================================================================

76. Set the ratings of all the customers of Piyush to 400.

SQL> update customer set rating=400 where snum in(select snum from salesman where
sname='piyush');

2 rows updated.

SQL> select * from customer;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 400 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 400 1001
2006 champak surat 300 1007
2007 pratik rome 100 1004
2005 pratik london

8 rows selected.

SQL> select * from salesman;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
1003 miti london 11
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9
100 rakesh 14

8 rows selected.
==================================================================

77. Increase the rating of all customers in Rome by 100.

SQL> update customer set rating=(rating+100) where city='rome';

2 rows updated.

SQL> select * from customer;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 400 1001
2002 gita rome 300 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 400 1001
2006 champak surat 300 1007
2007 pratik rome 200 1004
2005 pratik london

8 rows selected.
==================================================================

78. Salesman Miti has resigned. Reassign her number to a new salesman Gopal whose city is Bombay and
commission is 10%.

SQL> update salesman set sname='gopal',city='bombay',commission=10 where sname='miti';

1 row updated.

SQL> select * from salesman;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
1002 niraj surat 13
1003 gopal bombay 10
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9
100 rakesh 14

8 rows selected.
==================================================================

79. Double the commission of all salesmen of London.

SQL> update salesman set commission=(commission*2) where city='london';

1 row updated.

SQL> select * from salesman;

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 24
1002 niraj surat 13
1003 gopal bombay 10
1004 rajesh baroda 15
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9
100 rakesh 14

8 rows selected.
==================================================================

80. Set ratings for all customers in London to NULL.

SQL> update customer set rating=NULL where city='london';

3 rows updated.

SQL> select * from customer;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 1001
2002 gita rome 300 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2005 chandu london 1001
2006 champak surat 300 1007
2007 pratik rome 200 1004
2005 pratik london

8 rows selected.
==================================================================

81. Suppose we have a table called sales Manager with the same definition as Salesmen table.
Company decides to promote salesmen having total order more than 5000 to Sales Manager.
Fill up the Sales Manager table.

SQL> create table salesmanager as select * from salesman where snum is null;

Table created.

SQL> insert into salesmanager select * from salesman where snum in(select snum from orders group by
snum having sum(amount)>5000);

2 rows created.

SQL> select * from salesmanager;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 24
1002 niraj surat 13
==================================================================

82. Assume that we have a table called smcity. Store the information of all salesmen
with the customers in their home cities into smcity.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
SQL> create table smcity as select s.* from salesman s, customer c where s.snum=c.snum and
s.city=c.city;

Table created.

SQL> select * from smcity;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 24
1001 piyush london 24
1002 niraj surat 13

SQL> create table smcity (snum,sname,scity,cnum,cname,ccity) as select s.snum, s.sname, s.city,

c.cnum,
c.cname, c.city from salesman s,customer c where s.city=c.city and s.snum=c.snum;

Table created.

SQL> select * from smcity;

SNUM SNAME SCITY CNUM CNAME CCITY

---------- ---------- ---------- ---------- ---------- ----------
1001 piyush london 2001 hardik london
1001 piyush london 2005 chandu london
1002 niraj surat 2003 laxit surat
==================================================================

83. Create a table Bonus that contains date wise maximum amount of order for all salesmen.

SQL> create table bonus (odate,amount) as select odate,max(amount) from orders group by odate;

Table created.

SQL> select * from bonus;

ODATE AMOUNT
--------- ----------
10-MAR-99 5160.45
10-APR-99 1713.12
10-MAY-99 4723
10-JUN-99 9898.87

SQL> create table bonus as select * from orders where amount in(select max(amount) from orders group
by odate);

Table created.

SQL> select * from bonus;

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3006 1713.12 10-APR-99 2002 1003
3008 4723 10-MAY-99 2006 1001
3004 5160.45 10-MAR-99 2003 1002
3010 9898.87 10-JUN-99 2006 1001
==================================================================

84. Create a table Multicust containing the salesmen with more than one customer.

SQL> create table multicust1 as select * from salesman where snum in (select c.snum from customer c,
customer d where c.snum=d.snum and not c.cnum=d.cnum);

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

Table created.

SQL> select * from multicust1;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 24
1002 niraj surat 13

SQL> create table multicust2 as select * from salesman where snum in (select snum from customer
group by snum having count(*)>1);

Table created.

SQL> select * from multicust2;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 24
1002 niraj surat 13
==================================================================

85. New Delhi office has closed. Remove all customers assigned to salesmen in New Delhi.

SQL> delete from customer where snum in (select snum from salesman where city='new delhi');

0 rows deleted.
==================================================================

86. Delete all salesmen who have at least one customer with a rating of 100 from salesmen table.

SQL> delete from salesman where snum in(select snum from customer where rating=100);

2 rows deleted.

SQL> select * from salesman;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1002 niraj surat 13
1003 gopal bombay 10
1005 anand new delhi 10
1006 ram patan 10
1007 laxman bombay 9
100 rakesh 14

6 rows selected.
==================================================================

87. Delete the salesmen who produce the lowest order for each day.

SQL> delete from salesman where snum in(select snum from orders where amount in(select min(amount)
from orders group by odate));

2 rows deleted.

SQL> select * from salesman;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1003 gopal bombay 10

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
1005 anand new delhi 10
1006 ram patan 10
100 rakesh 14
==================================================================

88. Find the smallest order for each day. Reduce the commission of all salesmen by 2% who produce this
order.

SQL>select snum, sname, commission-(commission*0.02) "Reduce 2%" from salesman where snum In
(select snum from orders where amount In(select min(amount) from orders group by odate));

SNUM SNAME Reduce 2%

---------- ---------- ----------
1001 piyush 11.76
1002 niraj 12.74
1007 laxman 8.82
==================================================================

89. Delete all customers with no current orders.

SQL> delete from customer where cnum NOT IN(select cnum from orders);

2 rows deleted.

SQL> delete customer c where not exists(select * from orders o where c.cnum=o.cnum);

2 rows deleted.

SQL> select * from customer;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2001 hardik london 100 1001
2002 gita rome 200 1003
2003 laxit surat 200 1002
2004 govind bombay 300 1002
2006 champak surat 300 1007
2007 pratik rome 100 1004

6 rows selected.
==================================================================

90. Write a command to find out the orders by date.

SQL> select * from orders order by odate;

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3001 18.69 10-MAR-99 2008 1007
3002 767.19 10-MAR-99 2001 1001
3003 1900.1 10-MAR-99 2007 1004
3004 5160.45 10-MAR-99 2003 1002
3005 1098.25 10-APR-99 2008 1007
3006 1713.12 10-APR-99 2002 1003
3007 75.75 10-MAY-99 2004 1002
3008 4723 10-MAY-99 2006 1001
3009 1309.95 10-MAY-99 2004 1002
3010 9898.87 10-JUN-99 2006 1001

10 rows selected.
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
91. Write a command to add the item-name column to the order table.

SQL>alter table orders add(item_name varchar2(10));

Table altered.

SQL> desc orders;

Name Null? Type
----------------------------------------- -------- ------------
ONUM NUMBER(4)
AMOUNT NUMBER(6,2)
ODATE DATE
CNUM NUMBER(4)
SNUM NUMBER(4)
ITEM_NAME VARCHAR2(10)
==================================================================

92. Create a copy of your order table. Drop the original order table.

SQL> create table orderscp as select * from orders;

Table created.

SQL> drop table orders;

Table dropped.
==================================================================

93. Write a command to create the order table so that all onum values as well as all combinations
of cnum and snum are different from one another and so that NULL values are excluded from the date
field.

SQL>create table orders2 as select onum,amount,odate, cnum,snum from ordersbk;

Table Created.
==================================================================

94. Write a command to create the salesmen table so that the default commission is 10% with no NULL
permitted, snum is the primary key and all names contain alphabets only.

SQL> create table salesman1(snum number(4) primary key,sname varchar2(10),city

varchar2(10),commissi
on number(2) default 10 not null);

Table created.

SQL> insert into salesman1 select * from salesman;

8 rows created.
==================================================================

95. Give the commands to create our sample tables (salesmen, customer, orders) with
all the necessary constraints like PRIMARY KEY, NOT NULL UNIQUE, FOREING KEY.

Salesman:

SQL> create table s_salesman(snum number(4) primary key,sname varchar2(10),city

varchar2(10),commiss
ion number(2) not null);

Table created.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
SQL> desc s_salesman;

Name Null? Type

---------------- -------- -------------
SNUM NOT NULL NUMBER(4)
SNAME VARCHAR2(10)
CITY VARCHAR2(10)
COMMISSION NOT NULL NUMBER(2)

Customer:

SQL> create table c_customer(cnum number(4) primary key,cname varchar2(10) NOT NULL,city
char(10),ra
ting number(4),snum number(4), FOREIGN KEY(snum) REFERENCES s_salesman);

Table created.

SQL> desc c_customer;

Name Null? Type

------------------- --------------
CNUM NOT NULL NUMBER(4)
CNAME NOT NULL VARCHAR2(10)
CITY CHAR(10)
RATING NUMBER(4)
SNUM NUMBER(4)

Order:

SQL> create table o_order(onum number(4) primary key,amount number(7,2),odate date NOT
NULL,cnum num
ber(4) REFERENCES c_customer,snum number(4) REFERENCES s_salesman);

Table created.

SQL> desc o_order;

Name Null? Type

------------------- ------------
ONUM NOT NULL NUMBER(4)
AMOUNT NUMBER(7,2)
ODATE NOT NULL DATE
CNUM NUMBER(4)
SNUM NUMBER(4)
==================================================================

96. Create a view called Big orders which stores all orders larger than Rs.4000.

SQL> create view big_orders as select * from orders where amount>4000;

View created.

SQL> select * from big_orders;

ONUM AMOUNT ODATE CNUM SNUM

---------- ---------- --------- ---------- ----------
3004 5160.45 10-MAR-99 2003 1002
3008 4723 10-MAY-99 2006 1001
3010 9898.87 10-JUN-99 2006 1001
==================================================================

97. Create a view Rate count that gives the count of no. of customers at each rating.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

SQL> create view rate_count(rating,cnum) as select rating,count(cnum) from customer group by rating;

View created.

SQL> select * from rate_count;

RATING CNUM
---------- ----------
100 2
200 2
300 2
==================================================================

98. Create a view that shows all the customers who have the highest ratings.

SQL> create view high_rate as select * from customer where rating IN(select max(rating)
from customer);

View created.

SQL> select * from high_rate;

CNUM CNAME CITY RATING SNUM

---------- ---------- ---------- ---------- ----------
2004 govind bombay 300 1002
2006 champak surat 300 1007
==================================================================

99. Create a view that shows all the number of salesman in each city.

SQL> create view tot_sales as select city, count(snum) "Total Salesman" from salesman group by city;

View created.

SQL> select * from tot_sales;

CITY Total Salesman

---------- --------------
baroda 1
bombay 1
london 2
new delhi 1
patan 1
surat 1
1

7 rows selected.
==================================================================

100. Create a view that shows all the number of salesmen in each city.

SQL> create view tot_sales as select city, count(snum) "Total Salesman" from salesman group by city;

View created.

SQL> select * from tot_sales;

CITY Total Salesman

---------- --------------
baroda 1
bombay 1

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
london 2
new delhi 1
patan 1
surat 1
1

7 rows selected.
==================================================================

101. Create a view that shows the average and total orders for each salesmen after his name and number.

SQL>create view avg_totorders as select sname, orders.snum,avg(amount) average, count(onum) "Total

Orders"
from salesman, orders where salesman.snum=orders.snum group by sname, orders.snum;

View created.

SQL> select * from avg_totorders;

SNAME SNUM AVERAGE Total Orders

---------- ---------- ---------- ------------
piyush 1001 5129.68 3
niraj 1002 2182.05 3
miti 1003 1713.12 1
rajesh 1004 1900.1 1
laxman 1007 558.47 2
==================================================================

102. Create a view that shows all the salesmen with multiple customers.

SQL> create view multi_cust as select s.* from salesman s,customer c1,customer c2 where s.snum=c1.sn
um AND c1.cnum>c2.cnum AND c1.snum=c2.snum;

View created.

SQL> create view multi_cust1 as select * from salesman where snum in(select c1.snum from customer c1,
customer c2 where c1.snum=c2.snum and c1.cnum <> c2.cnum);

View created.

SQL> select * from multi_cust;

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1002 niraj surat 13
==================================================================

103. Create a view to keep track of the total no of customers ordering, no of salesmen taking orders,
the no of orders, the average amount ordered, and the total amount ordered for each day.

SQL> create view alltrack as select odate, count(distinct cnum) "No of Customer", count(distinct snu
m) "No of Salesman", count(onum) "No of Orders", avg(amount) "Average",sum(amount) "Total Amount"
fr
om orders group by odate;

View created.

SQL> select * from alltrack;

ODATE No of Customer No of Salesman No of Orders Average Total Amount

--------- -------------- -------------- ------------ ---------- ------------
10-MAR-99 4 4 4 1961.60 7846.43

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III
10-APR-99 2 2 2 1405.68 2811.37
10-MAY-99 2 2 3 2036.23 6108.7
10-JUN-99 1 1 1 9898.87 9898.87
==================================================================

104. Create a view Show name that shows for each order the order no, amount, salesman name and the
customer name.

SQL> create view orderinfo(onum,amount,sname,cname) as select onum,amount,sname,cname from

orders,sa
lesman,customer where customer.cnum=orders.cnum AND salesman.snum=orders.snum;

View created.

SQL> select * from orderinfo;

ONUM AMOUNT SNAME CNAME

---------- ---------- ---------- ----------
3002 767.19 piyush hardik
3008 4723 piyush champak
3010 9898.87 piyush champak
3004 5160.45 niraj laxit
3007 75.75 niraj govind
3009 1309.95 niraj govind
3006 1713.12 miti gita
3003 1900.1 rajesh pratik

8 rows selected.
==================================================================

105. List all orders of salesman 'Rajesh' using Show name View along with his commission.

SQL>create view shnameview as select o.*, sname,commission from salesman s, orders o where
s.snum=o.snum and s.sname='rajesh';

View created.

SQL> select * from shnameview;

ONUM AMOUNT ODATE CNUM SNUM SNAME COMMISSION

---------- ---------- --------- ---------- ---------- ---------- ----------
3003 1900.1 10-MAR-99 2007 1004 rajesh 15
==================================================================

106. Create a view Max sales to store the name and number of salesman, along with the date,
who have the highest order on any given date.

SQL>create view max_sales as select s.snum,s.sname,o.odate from salesman s, orders o where

o.snum=s.snum and o.amount in(select max(amount) from orders group by odate);

SQL> select * from max_sales;

SNUM SNAME ODATE

---------- ---------- ---------
1003 miti 10-APR-99
1001 piyush 10-MAY-99
1002 niraj 10-MAR-99
1001 piyush 10-JUN-99
==================================================================

107. Using above view, find out the name and number of salesman who have the highest order at
least two times. Store the result in another view.

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

SQL> create view high_orders as select sname, snum from max_sales where
snum in(select snum from orders where amount in(select max(amount) from orders group by odate)
group by snum having count(snum)>=2);

View created.

SQL> select * from high_orders;

SNAME SNUM
---------- ----------
piyush 1001
piyush 1001
==================================================================

108. Create a view Same city that shows the no and name and city of the customers along with the
city of the salesman serving them.

SQL> create view same_city as select c.cnum,c.cname,c.city,s.city Sman_City from customer c, Sal
esman s where s.snum=c.snum;

View created.

SQL> select * from same_city;

CNUM CNAME CITY SMAN_CITY

---------- ---------- ---------- ----------
2001 hardik london london
2003 laxit surat surat
2004 govind bombay surat
2002 gita rome london
2007 pratik rome baroda
2006 champak surat bombay

6 rows selected.
==================================================================

109. Create a view Commission of salesmen table to include only snum and commission field so that
through. this view someone can enter or change the commission but only to values between 10% and
20%.

SQL> create view smancomm as (select snum,commission from salesman where commission between 10
and 20);

View created.

SQL> select * from smancomm;

SNUM COMMISSION
---------- ----------
1001 12
1002 13
1003 11
1004 15
1005 10
1006 10
100 14

7 rows selected.
==================================================================

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]
ADBMS – SQL Queries – BCA-III

110. Assume that the CURDATE is a constant representing current date. Give a command to create orders
table with CURDATE as a default date.

SQL> create table orders_curdt(onum number(4), amount NUMBER(6,2), odate DATE DEFAULT SYSDATE,
cnum NUMBER(4),
snum NUMBER(4));

Table created.
==================================================================

111. List all salesmen in London who had at least one customer located there as well.

SQL> select salesman.snum,sname,cname from salesman,customer where customer.snum IN(select snum

from
salesman where city='london') AND customer.snum=salesman.snum;

SNUM SNAME CNAME

---------- ---------- ----------
1001 piyush hardik
1003 miti gita

SQL> select s.snum, s.sname,s.city,c.cname Customer from salesman s, customer c where

s.snum=c.snum
and c.city=s.city and s.city='london';

SNUM SNAME CITY CUSTOMER

---------- ---------- ---------- ----------
1001 piyush london hardik

SQL> select * from salesman where city='london' and snum in(select snum from customer where city='lo
ndon');

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1001 piyush london 12
==================================================================

112. List all salesmen in London who didn't have any customer there.

SQL>select * from salesman where city='london' and snum not in(select snum from customer where
city='london');

SNUM SNAME CITY COMMISSION

---------- ---------- ---------- ----------
1003 miti london 11

By: Hitesh Patel (P.G.D.C.A, A.D.C.A, M.C.A, M.Phil(CS)

9998531670 – [email protected]