L.J. INSTITUTE OF ENGINEERING AND TECHNOLOGY
BE. SEM – III CE & IT
DATABASE MANAGEMENT SYSTEM (DBMS)
PRACTICAL LIST
1. Create the following Databases.
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.
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
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.
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
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.
Practical List - 1
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.
2. Give all the information about all the customers with salesman
number 1001.
3. Display the following information in the order of city, sname, snum
and commission.
4. List of rating followed by the name of each customer in Surat
5. List of snum of all salesmen with orders in order table without any
duplicates.
6. List of all orders for more than Rs. 1000.
7. List of names and cities of all salesmen in London
above 10%.
8. List all customers excluding those with rating <= 100 unless they
are located in Rome
9. List all orders for more than Rs.1000 except the orders of snum<1006
of 10/03/97 .
10. List all orders taken on October 3rd or 4th or 6th, 1997.
11. List all customers whose names begins with a letter 'C'.
12. List all customers whose names begins with letter 'A' to 'G'.
13. List all orders with zero or NULL amount.
Practical List - 2
Solve the following queries using above databases and group by clause.
1. Find out the largest orders of salesman 1002 and 1007.
2. Count all orders of October 3, 1997 .
3. Calculate the total amount ordered.
4. Calculate the average amount ordered.
5. Count the no. of salesmen currently having orders.
6. Find the largest order taken by each salesman on each date.
7. Find the largest order taken by each salesman on 10/03/1997 .
8. Count the no. of different non NULL cities in the Customer table.
9. Find out each customer's smallest order.
10. Find out the first customer in alphabetical order whose name begins
with 'G'.
11. Count the no. of salesmen registering orders for each day.
Practical List - 3
Solve the following queries using above databases and formatted output and order by clause.
1. List all salesmen with their % of commission.
2. Display the no. of orders for each day in the descending order of the
no. of orders in the following format.
FOR dd-mon-yy, there are __ Orders.
3. Assume each salesperson has a 12% commission. Write a query on the
order table that will produce the order number, salesman no and the
amount of commission for that order.
4. Find the highest rating in each city in the form :
For the city (city), the highest rating is : (rating)
5. List all in descending order of rating.
6. Calculate the total of orders for each day and place the result in
descending order.
Practical List - 4
Solve the following queries using above databases and join.
1. Show the name of all customers with their salesman's name.
2. List all customers and salesmen who shared a same city.
3. List all orders with the names of their customer and salesman.
4. List all orders by the customers not located in the same city as
their salesman.
5. List all customers serviced by salespeople with commission above
12%.
6. Calculate the amount of the salesman commission on each order by a
customer with rating above 100.
7. Find all pairs of customers having the same rating with out
duplication.
8. Company policy is to assign each salesperson three customers,one at
each of the three ratings. Display all possible combination of such
three customers.
9. List all customers located in cities where salesman Sejal has
customers.
10. Find all pairs of customers served by a single salesman with the
salesman's name and no.
11. List all salesmen who are living in the same city with out duplicate
rows.
12. List all pairs of orders by a given customer with customer name.
13. Produce the name and city of all the customers with the same rating
as Harsh.
14. Extract all orders of Miti.
15. Extract all orders of Baroda
16. Find all orders of the salesman who services 'Harsh'.
17. List all orders that are greater than the average of October 4,1997 .
18. Find the average commission of salesmen in London
Practical List - 5
Solve the following queries using above databases and join and subquery.
1. Find all orders attributed to salesmen in 'London
subquery and join methods.
2. List the commission of all salesmen serving customers in 'London
3. Find all customers whose cnum is 1000 above than the snum of Sejal.
4. Count the no. of customers with the rating above than the average of
'Surat
5. List all orders of the customer 'Chirag'.
6. Produce the name and rating of all customers who have above average
orders.
7. Select the total amount in orders for each salesman for whom this
total is greater than the amount of the largest order in the table.
8. Find all customers with orders on 3rd Oct., 1997 using correlated
subquery.
9. List the name and number of all salesmen who has more than one
customer.
10. Find all orders with amount atleast equal to the average amounts for
their customers.
11. Calculate the total amount ordered on each day eliminating those
days where the total amount was not atleast Rs. 2000 above the
maximum amount of that day.
12. Using correlated subquery, find the name and number of all customers
with rating equal to maximum for their city.
13. Select the name and number of all salesmen who have customers in
their cities who they do not service.
14. Find the number of all the salesmen having multiple customers
using EXIST.
15. Find the name,number and city of all the salesmen having multiple
customers using EXIST.
16. Find the name and number of all the salesmen who serve only one
customer.
17. Find all salesmen with more than one current order.
18. Display the customer information if and only if one or more of the
customers in are located in 'Surat
19. Find all salesmen who have customers with more than one current
order.
20. Find all salesmen who have customers with rating > 300 using EXIST
and using join.
21. List all salesmen with coustomers located in their cities who are
not assigned to them.
22. write a query to extract from the customers table every customer
assigned to a salesman who currently has atleast one other customer
with orders in the order table.
23. Find all salesmen with customers located in their cities using ANY
and IN.
24. Find all salesmen for whom there are customers that follow them in
alphabetical order.
25. Find all customers having rating greater than any customer in
'Rome
26. List all orders that has amount grater than atleat one of the orders
from 6th October, 1997 .
27. Find all orders with amounts smaller than any amount for a customer
in 'London
28. Find all the customers who have greater rating than every customer
in 'Rome
29. Select all customers whose rating doesn't match with any rating of
customer of 'Surat
30. List all customers whose ratings are equal to or greater than ANY of
'Sejal'.
31. Find all salesmen who have no customers located in their city using
ANY and ALL.
32. List all orders for amount greater than any for the customers in
33. Find all salesmen and customers located in London
34. Find out which salesman produce largest and smallest orders on each
date.
35. Find out which salesman produce largest and smallest orders on each
date in the order of order number.
36. List salesman no, salesman name,cusotmer name & comission of
salesman who have customers in their cities as well as those who
don't have in which case the customer name should indicate 'NO
MATCH'.
Practical List - 6
Solve the following queries using above databases and table from another table and set operation.
1. 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'.
2. Produce the name and number of each salesman and each customer with
more than one current order in the alphabetical order of names.
3. Create union of three queries. First select snum of all salesman in
onum of all orders of 3rd Oct. Retain duplicates between the last
two queries but remove the duplicates between either of them and
the first.
4. Insert a row into salesmen table with the values snum is 1005,
salesman name is Rakesh, city is unknown and commission is 14%.
5. Insert a row in to customer table with values London
2005 for the columns city, name and number.
6. Create another table Londonstaff having same structure as salesmen
table.
7. Insert all the rows of salesmen table with city London
Londonstaff table.
8. Create another table Daytotals with two attributes date and total
and insert rows into this table from order table.
9. Create a duplicate of the salesmen table with a name Multicust. Now
delete all the rows from the salesmen table.
10. Get back all the rows of salesmen table from its duplicate table.
Practical List - 7
Solve the following queries using above databases and delete and update.
1. Remove all orders from customer Chirag from the orders table.
2. set the ratings of all the customers of Piyush to 400.
3. Increase the rating of all customers in Rome
4. Salesman Sejal has left the company. Assign her customers to Miti.
5. Salesman Miti has resigned. Reassign her number to a new salesman
Gopal whose city is Bombay
6. Double the commission of all salesmen of London
7. Set ratings for all customers in London
8. Suppose we have a table called SalesManager with the same
definition as Salesmen table. Company decides to promot salesmen
having total order more than 5000 to SalesManager.Fill up the
SalesManager table.
9. Assume that we have a table called smcity. Store the information of
all salesmen with the customers in their home cities into smcity.
10. Create a table Bonous that contains datewise maximum amount of
order for all salesmen.
11. Create a table Mltcust containing the salesmen with more than one
customer.
12. New Delhi
salesmen in New Delhi
13. Delete all salesmen who have at least one customer with a rating of
100 from salesmen table.
14. Delete all salesmen who don't have any customer with a rating of
100 from salesmen table.
15. Delete the salesmen who produce the lowest order for each day.
16. Delete the salesmen who produce the lowest order for each day
unless he has a commission > 12%.
17. Increase the commission of all salesmen by 2% who have been
assigned at least two customers.
18. Find the smallest order for each day. Reduce the commission of all
salesmen by 2% who produce this order.
19. Delete all customers with no current orders.
20. Double the rating of all customers having more than one current
order.
21. Write a command to find out the orders by date.
Practical List - 8
Solve the following queries using above databases and alter table and table constraints..
1. How the onum field is forced to be an unquie?
2. Create an index to permit each salesman to find out his orders by
date quickly.
3. Write a command to enforce that each salesman is to have only one
customer of a given rating.
4. Write a command to add the item-name column to the order table.
5. Create a copy of your order table. Drop the original order table.
6. 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.
7. Write a command to create the salesmen table so that the default
commission is 10% with no NULLs permitted, snum is the primary key
and all names contain alphabeticals only.
8. Write a command to create the order table making sure that the onum
is greater than cnum and cnum is greater than the snum. Allow NULL
in any of these fields.
9. Give the commands to create our sample tables ( salesmen, customer,
orders) with all the necessary constraints like PRIMARY KEY, NOT
NULL , UNIQUE, FOREIGN KEY.
Practical List - 9
Solve the following queries using above databases and view.
1. Create a view called Bigorders which stores all orders larger than
Rs. 4000.
2. Create a view Ratecount that gives the count of no. of customers at
each rating.
3. Create a view that shows all the customers who have the highest
ratings.
4. Create a view that shows all the number of salesmen in each city.
5. Create a view that shows the average and total orders for each
salesmen after his name and number.
6. Create a view that shows all the salesmen with multiple customers.
7. Create a view that shows all the salesmen with multiple customers
with rating > 200.
8. 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.
9. Create a view Showname that shows for each order the order no,
amount, salesman name and the customer name.
10. List all orders of salesman 'Rajesh' using Showname View along with
his commission.
11. Create a view Maxsales to store the name and number of salesman,
along with the date, who have the highest order on any given date.
12. Using above view, find out the name and number of salesman who have
the highest order atleast two times. Store the result in another
view.
13. 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.
14. 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%.
15. Assume that the CURDATE is a constant representing current date.
Give a command to create orders table with CURDATE as a default
odate.
16. List all salesmen in London
there as well.
17. List all salesmen in London
18. Develop an input form in SQL Forms to enter the students marks in
order to prepare marksheet of student result.
19. Develop an application to maintain payroll of employees in an
organization. Use SQL Forms for data entry
Practical List – 10
Solve the following using PL/SQL Block , cursors , Procedure , Function ,Trigger
- Write a PL/SQL block to display whether the given number is odd or even.
- Write a PL/SQL block to display LJIET 10 times using for loop
- Write a PL/SQL block using cursor to update salary of a given programmer by 25%.
- Write a PL/SQL block to display addition of all the numbers in the given range
- Write a PL/SQL block to display the detail about given employee from EMP table
- Write a PL/SQL block to find the salary of a given employee and raise his salary by 20%.
- Write a PL/SQL procedure to print the following output.
* * *
* *
*
- Create a cursor emp_cur,fetch record from emp table and check whether sal>10000 then update Grade = ‘A’ else if sal = > 5000 and sal <= 10000 then update Grade = ‘B’
- Write a PL/SQL procedure to find the table structure of a given number
- Write a function on programmer table to calculate number of female programmer
- Write a function on programmer table to return total number of programmers having knowledge of specified language
- Write a function on programmer table to return age of specified person.
- Write a function on software table to calculate selling cost of all software of a specified person
- Write a function on studies table to return course fee of a specified course.
- Write a function on studies and programmer table to return number of months required to overcome the course fee he/she has studied
- Write a PL/SQL block with cursor ,showing the use of SQL%FOUND attribute
- Write a PL/SQL block with cursor ,showing the use of SQL% ROWCOUNT attribute
- Write a PL/SQL block with cursor ,showing the use of SQL% ISOPEN attribute
- Create trigger on Supplier Detail on update or insert of Sname to convert Sname into capital letter
- Create trigger on Supplier Detail on update or insert of Scity to convert first letter of scity into capital letter.
create table salesman_21(snum number(4),sname varchar(25),city varchar(20),comm varchar(10));
create table customer_21(cnum number(4),cname varchar(25),city varchar(20),rating number(4),snum number(4));
create table orders_21(onum number(4),amount float,odate date,cnum number(4),snum number(4));
insert into salesman_21 values(1001,'piyush','london','12%');
insert into salesman_21 values(1002,'sejal','surat','13%');
insert into salesman_21 values(1004,'miti','london','11%');
insert into salesman_21 values(1007,'rajesh','baroda','15%');
insert into salesman_21 values(1003,'anand','new delhi','10%');
insert into customer_21 values(2001,'harsh','london',100,1001);
insert into customer_21 values(2002,'gita','rome',200,1003);
insert into customer_21 values(2003,'lalit','surat',200,1002);
insert into customer_21 values(2004,'govind','bombay',300,1002);
insert into customer_21 values(2006,'chirag','london',100,1001);
insert into customer_21 values(2008,'chinmay','surat',300,1007);
insert into customer_21 values(2007,'pratik','rome',100,1004);
insert into orders_21 values(3001,'18.69','10-mar-97',2008,1007);
insert into orders_21 values(3003,'767.19','10-mar-97',2001,1001);
insert into orders_21 values(3002,'1900.10','10-mar-97',2007,1004);
insert into orders_21 values(3005,'5160.45','10-mar-97',2003,1002);
insert into orders_21 values(3006,'1098.16','10-mar-97',2008,1007);
insert into orders_21 values(3009,'1713.23','10-apr-97',2002,1003);
insert into orders_21 values(3007,'75.75','10-apr-97',2004,1002);
insert into orders_21 values(3008,'4723.00','10-may-97',2006,1001);
insert into orders_21 values(3010,'1309.95','10-jun-97',2004,1002);
insert into orders_21 values(3011,'9891.88','10-jun-97',2006,1001);
Practical 1:
************
select onum,amount,odate from orders_21;
select * from customer_21 where (snum='1001');
select city,sname,snum,comm from salesman_21;
select cname,rating from customer_21 where(city='surat');
select * from orders_21 where(amount>1000);
select sname,city from salesman_21 where city='london' and comm>'10%';
select * from customer_21 where(rating>100 or city='rome');
select * from orders_21 where(amount>1000 and odate='10-mar-97' and snum>=1006);
select * from orders_21 where odate in('10-mar-97','10-apr-97','6-mar-97');
select * from customer_21 where cname like 'c%';
select * from customer_21 where cname between 'a%' and 'h%';
select * from orders_21 where (amount='0' or amount='');
***********************************************************************
Practical 2:
***********
select max(amount) from orders_21 where (snum=1002 or snum=1007);
select count(*) from orders_21 where odate='10-mar-97';
select sum(amount) from orders_21;
select avg(amount) from orders_21;
alter table salesman_21 add primary key(snum);
alter table customer_21 add primary key(cnum);
insert into salesman_21(snum) values(1001);
alter table orders_21 add primary key(onum);
alter table orders_21 add foreign key(snum) references salesman_21(snum);
alter table orders_21 add foreign key(cnum) references customer_21(cnum);
alter table customer_21 add foreign key(snum) references salesman_21(snum);
select count(*),city from customer_21 group by city;
select max(amount),snum,odate from orders_21 group by snum,odate;
select max(amount),snum,odate from orders_21 where odate='10-mar-97' group by snum,odate;
select count(*),city from customer_21 where city is not null group by city;
select min(amount),cnum from orders_21 group by cnum;
select cname from customer_21 where cname='g%' group by cname;
***********************************************************************
Practical 3:
***********
***********
select comm,sname from salesman_21;
select 'for ' ||odate|| ' there are ' ||count(onum)|| ' orders' from orders_21 group by odate;
select onum,snum,(amount*(12/100)) from orders_21;
select 'for the city ' ||city|| ' the highest rating is ' ||max(rating) from customer_21 group by city;
select * from customer_21 order by rating desc;
select sum(amount),odate from orders_21 group by odate order by sum(amount) desc;
wherer is the output of other pactical
ReplyDeleteWhere is the others practical query output
ReplyDeleteWhere are the solution of all questions
ReplyDelete