Creating a database & Solving queries using PL/SQL, PL/SQL queries for Displaying, Selecting & Listing
PRACTICAL LIST
1. Create the following Databases.
Orders
ONUM AMOUNT ODATE CNUM SNUM
-------------------------------------------------------
3001 18.69 10/03/97 2008 1007
3003 767.19 10/03/97 2001 1001
3002 1900.10 10/03/97 2007 1004
3005 5160.45 10/03/97 2003 1002
3006 1098.16 10/03/97 2008 1007
3009 1713.23 10/04/97 2002 1003
3007 75.75 10/04/97 2004 1002
3008 4723.00 10/05/97 2006 1001
3010 1309.95 10/06/97 2004 1002
3011 9891.88 10/06/97 2006 1001
Customers
CNUM CNAME CITY RATING SNUM
-------------------------------------------------------
2001 Harsh London
2002 Gita Rome
2003 Lalit Surat
2004 Govind Bombay
2006 Chirag London
2008 Chinmay Surat
2007 Pratik Rome
Salesmen
SNUM SNAME CITY COMMISSION
-------------------------------------------------------
1001 Piyush London
1002 Sejal Surat
1004 Miti London
1007 Rajesh Baroda
1003 Anand New Delhi
SNUM : A unique number assigned to each salesman.
SNAME : The name of salesman.
CITY : The location of salesmen.
COMMISSION: The Salemen's commission on orders.
CNUM : A unique number assigned to each customer.
CNAME : The name of the customer.
CITY : The location of the customer.
RATING : A level of preference indicator given to this customer.
SNUM : The number of salesman assigned to this customer.
ONUM : A unique number assigned to each order.
AMOUNT : The amount of an order.
ODATE : The date of an order.
CNUM : The number of customer making the order.
SNUM : The number of salesman credited with the sale.
Creating Table
create table salesman_96(snum number(4),sname varchar(25),city varchar(20),comm varchar(10));
create table customer_96(cnum number(4),cname varchar(25),city varchar(20),rating number(4),snum number(4));
create table orders_96(onum number(4),amount float,odate date,cnum number(4),snum number(4));
Creating Table for Orders
insert into orders_96 values(3001,'18.69','10-mar-97',2008,1007);
insert into orders_96 values(3003,'767.19','10-mar-97',2001,1001);
insert into orders_96 values(3002,'1900.10','10-mar-97',2007,1004);
insert into orders_96 values(3005,'5160.45','10-mar-97',2003,1002);
insert into orders_96 values(3006,'1098.16','10-mar-97',2008,1007);
insert into orders_96 values(3009,'1713.23','10-apr-97',2002,1003);
insert into orders_96 values(3007,'75.75','10-apr-97',2004,1002);
insert into orders_96 values(3008,'4723.00','10-may-97',2006,1001);
insert into orders_96 values(3010,'1309.95','10-jun-97',2004,1002);
insert into orders_96 values(3011,'9891.88','10-jun-97',2006,1001);
Creating Table for Salesman
Creating Table for Salesman
insert into salesman_96 values(1001,'piyush','london','12%');
insert into salesman_96 values(1002,'sejal','surat','13%');
insert into salesman_96 values(1004,'miti','london','11%');
insert into salesman_96 values(1007,'rajesh','baroda','15%');
insert into salesman_96 values(1003,'anand','new delhi','10%');
Creating Table for Customer
insert into customer_96 values(2001,'harsh','london',100,1001);
insert into customer_96 values(2002,'gita','rome',200,1003);
insert into customer_96 values(2003,'lalit','surat',200,1002);
insert into customer_96 values(2004,'govind','bombay',300,1002);
insert into customer_96 values(2006,'chirag','london',100,1001);
insert into customer_96 values(2008,'chinmay','surat',300,1007);
insert into customer_96 values(2007,'pratik','rome',100,1004);
Solve the following queries using above databases and where clause range searching and pattern matching.
1. Produce the order no, amount and date of all orders.
Ans. select onum,amount,odate from orders_96;
Ans. select onum,amount,odate from orders_96;
2. Give all the information about all the customers with salesman
number 1001.
Ans.select * from customer_96 where (snum='1001');
Ans.select * from customer_96 where (snum='1001');
3. Display the following information in the order of city, sname, snum
and commission.
Ans.select city,sname,snum,comm from salesman_96;
Ans.select city,sname,snum,comm from salesman_96;
4. List of rating followed by the name of each customer in Surat
Ans.select cname,rating from customer_96 where(city='surat');
Ans.select cname,rating from customer_96 where(city='surat');
5. List of snum of all salesmen with orders in order table without any
duplicates.
Ans.select * from orders_96 where(amount>1000);
Ans.select * from orders_96 where(amount>1000);
6. List of all orders for more than Rs. 1000.
Ans.select sname,city from salesman_96 where city='london' and comm>'10%';
Ans.select sname,city from salesman_96 where city='london' and comm>'10%';
7. List of names and cities of all salesmen in London
above 10%.
Ans.select * from customer_96 where(rating>100 or city='rome');
Ans.select * from customer_96 where(rating>100 or city='rome');
8. List all customers excluding those with rating <= 100 unless they
are located in Rome
Ans.select * from orders_96 where(amount>1000 and odate='10-mar-97' and snum>=1006);
Ans.select * from orders_96 where(amount>1000 and odate='10-mar-97' and snum>=1006);
9. List all orders for more than Rs.1000 except the orders of snum<1006
of 10/03/97 .
Ans.select * from orders_96 where odate in('10-mar-97','10-apr-97','6-mar-97');
Ans.select * from orders_96 where odate in('10-mar-97','10-apr-97','6-mar-97');
10. List all orders taken on October 3rd or 4th or 6th, 1997.
Ans.
Ans.
11. List all customers whose names begins with a letter 'C'.
Ans.select * from customer_96 where cname like 'c%';
Ans.select * from customer_96 where cname like 'c%';
12. List all customers whose names begins with letter 'A' to 'G'.
Ans. select * from customer_96 where cname between 'a%' and 'h%';
Ans. select * from customer_96 where cname between 'a%' and 'h%';
13. List all orders with zero or NULL amount.
Ans. select * from orders_96 where (amount='0' or amount='');
Ans. select * from orders_96 where (amount='0' or amount='');
