PSG COLLEGE OF TECHNOLOGY-COIMBATORE

DEPARTMENT OF MATHEMATICS AND COMPUTER APPLICATIONS

III Sem B.Sc CSD
21X306 - DBMS LABORATORY
Problem Sheet – VII

1. Create the following tables with the given data. Include the necessary referential
integrity constraints.
Customer:
customeri firstna lastna
city state
d me me
10101 John Gray Lynden Washington
10298 Leroy Brown Pinetop Arizona
10299 Elroy Keller Snoqualmie Washington
10315 Lisa Jones Oshkosh Wisconsin
10325 Ginger Schultz Pocatello Idaho
Mendoz
10329 Kelly Kailua Hawaii
a
Cannon
10330 Shawn Dalton Oregon
Beach
10338 Michael Howell Tillamook Oregon
Sanche
10339 Anthony Winslow Arizona
z
10408 Elroy Cleaver Globe Arizona
Mary South
10410 Howell Charleston
Ann Carolina
10413 Donald Davids Gila Bend Arizona
Sakahar
10419 Linda Nogales Arizona
a
North
10429 Sarah Graham Greensboro
Carolina
10438 Kevin Smith Durango Colorado
10439 Conrad Giles Telluride Colorado
10449 Isabela Moore Yuma Arizona

Items_ordered:

customer quanti
order_date item price
id ty
30-Jun-
10330 Pogo stick 1 28.00
1999
30-Jun-
10101 Raft 1 58.00
1999
10298 01-Jul-1999 Skateboard 1 33.00
10101 01-Jul-1999 Life Vest 4 125.00
1250.0
10299 06-Jul-1999 Parachute 1
0
10339 27-Jul-1999 Umbrella 1 4.50
13-Aug-
10449 Unicycle 1 180.79
1999
14-Aug-
10439 Ski Poles 2 25.50
1999
18-Aug-
10101 Rain Coat 1 18.30
1999
01-Sep-
10449 Snow Shoes 1 45.00
1999
 customer quanti
order_date item price
id ty
30-Jun-
10330 Pogo stick 1 28.00
1999
18-Sep-
10439 Tent 1 88.00
1999
19-Sep-
10298 Lantern 2 29.00
1999
28-Oct-
10410 Sleeping Bag 1 89.22
1999
01-Nov-
10438 Umbrella 1 6.75
1999
02-Nov-
10438 Pillow 1 8.50
1999
01-Dec-
10298 Helmet 1 22.00
1999
15-Dec-
10449 Bicycle 1 380.50
1999
22-Dec-
10449 Canoe 1 280.00
1999
30-Dec-
10101 Hoola Hoop 3 14.75
1999
10330 01-Jan-2000 Flashlight 4 28.00
10101 02-Jan-2000 Lantern 1 16.00
Inflatable
10299 18-Jan-2000 1 38.00
Mattress
10438 18-Jan-2000 Tent 1 79.99
10413 19-Jan-2000 Lawnchair 4 32.00
10410 30-Jan-2000 Unicycle 1 192.50
10315 2-Feb-2000 Compass 1 8.00
29-Feb-
10449 Flashlight 1 4.50
2000
08-Mar-
10101 Sleeping Bag 2 88.70
2000
18-Mar-
10298 Pocket Knife 1 22.38
2000
19-Mar-
10449 Canoe paddle 2 40.00
2000
01-Apr-
10298 Ear Muffs 1 12.50
2000
19-Apr-
10330 Shovel 1 16.75
2000

1. From the items_ordered table, select a list of all items purchased for customerid
10449. Display the customerid, item, and price for this customer.

2. Select all columns from the items_ordered table for whoever purchased a Tent.
3. Select the customerid, order_date, and item values from the items_ordered table
for any items in the item column that start with the letter "S".

4. Select the distinct items in the items_ordered table. In other words, display a
listing of each of the unique items from the items_ordered table.

5. Select the maximum price of any item ordered in the items_ordered table.
6. Select the average price of all of the items ordered that were purchased in the
month of Dec.

7. For all of the tents that were ordered in the items_ordered table, what is the price
of the lowest tent? Hint: Your query should return the price only.

8. How many people are in each unique state in the customers table? Select the state
and display the number of people in each. Hint: count is used to count rows in a
column, sum works on numeric data only.
9. From the items_ordered table, select the item, maximum price, and minimum
price for each specific item in the table. Hint: The items will need to be broken up
into separate groups.

10. How many orders did each customer make? Use the items_ordered table. Select
the customerid, number of orders they made, and the sum of their orders.
11. How many people are in each unique state in the customers table? Select the state
and display the number of how many people are in each if it's greater than 1.

12. From the items_ordered table, select the item, maximum price, and minimum
price for each specific item in the table.
13. How many orders did each customer make? Use the items_ordered table. Select
the customerid, number of orders they made, and the sum of their orders if they
purchased more than 1 item.

14. Select the lastname, firstname, and city for all customers in the customers table.
Display the results in Ascending Order based on the lastname.
15. Select the item and price for all of the items in the items_ordered table that the
price is greater than 10.00. Display the results in ascending order based on the
price.
16. Select the customerid, order_date, and item from the items_ordered table for all
items unless they are 'Snow Shoes' or if they are 'Ear Muffs'. Display the rows as
long as they are not either of these two items.

17. Select the item and price of all items that start with the letters 'S', 'P', or 'F'.
18. Select the date, item, and price from the items_ordered table for all of the rows
that have a price value ranging from 10.00 to 80.00.

19. Select the firstname, city, and state from the customers table for all of the rows
where the state value is either: Arizona, Washington, Oklahoma, Colorado, or
Hawaii.

20. Select the item and per unit price for each item in the items_ordered table. Hint:
Divide the price by the quantity.
21. Write a query using a join to determine which items were ordered by each of the
customers in the customers table. Select the customerid, firstname, lastname,
order_date, item, and price for everything each customer purchased in the
items_ordered table.
22. Display the first name, last name and order dates for all orders by a customer id
10101.

23. Display the total sales year wise.

24. Display the details of the customers in the Customer table who have never placed
an order.
25. Select the names of all customers who have orders.--
26. Display the details of all customers, who have ordered products worth more than
Rs.800/- and the related product information.
27. Display the details of a customer who purchased a product and the quantity of
product.
28. Display the month on which more customers purchased the items.
29. Identify the items purchased by the customers from Colorado.
 30. Display the details of items with the item price greater than 350 and the customer
purchased from Colorado, North Carolina, Washington, and South Carolina.

2. Consider the schemas for the tables department and employee. Insert the appropriate
records to answer the queries given below.

Table 1 : DEPT

Deptno (not null , number(2)), Dname (varchar2(14)), Location (varchar2(13)

Table 2 : EMP

Empno (not null , number(4)), Ename (varchar2(10)), Job (varchar2(9)), Mgr

(number(4)), Hiredate (date), Sal (number(7,2)), Ccomm (number(7,2)), Deptno
(number(2))

MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign
key.

QUERIES

1. List all the employees who have at least one person reporting to them.
2. List the employee details if and only if more than 10 employees are present in
department no 10.
3. List the name of the employees with their immediate higher authority.
4. List all the employees who do not manage any one.
5. List the employee details whose salary is greater than the lowest salary of an
employee belonging to deptno 20.
6. List the details of the employee earning more than the highest paid manager.
7. List the highest salary paid for each job.
8. Find the most recently hired employee in each department.
9. In which year did most people join the company? Display the year and the number of
employees.
10. Which department has the highest annual remuneration bill?
11. Write a query to display a ‘*’ against the row of the most recently hired
employee.
12. Write a correlated sub-query to list out the employees who earn more than the
average salary of their department.
13. Find the second maximum salary.
14. Select the duplicate records (Records, which are inserted, that already exist) in the
EMP table.
15. Write a query to list the length of service of the employees (of the form n years and m
months).

CREATE TABLE CUST(

CUSTOMERID NUMBER(5) PRIMARY KEY,
 FIRSTNAME CHAR(20),
LASTNAME CHAR(20),
CITY CHAR(30),
STATE CHAR(20));

CREATE TABLE ITEMS_ORDERED(

CUSTOMERID NUMBER(5),
ORDER_DATE DATE,
ITEM CHAR(20),
QUANTITY NUMBER,
PRICE DECIMAL(6,2));
drop table items_ordered;
INSERT INTO CUST VALUES(10101,'John','Gray','Lynden','Washington');
INSERT INTO CUST VALUES(10298,'Leroy','Brown','Pinetop','Arizona');
INSERT INTO CUST VALUES(10299,'Elroy','Keller','Snoqualmie','Washington');
INSERT INTO CUST VALUES(10315,'Lisa','Jones','Oshkosh','Wisconsin');
INSERT INTO CUST VALUES(10325,'Ginger','Schultz','Pocatello','Idaho');
INSERT INTO CUST VALUES(10329,'Kelly','Mendoza','Kailua','Hawaii');
INSERT INTO CUST VALUES(10330,'Shawn','Dalton','Cannon Beach','Oregon');
INSERT INTO CUST VALUES(10338,'Michael','Howell','Tillamook','Oregon');
INSERT INTO CUST VALUES(10339,'Anthony','Sanchez','Winslow','Arizona');
INSERT INTO CUST VALUES(10408,'Elroy','Cleaver','Globe','Arizona');
INSERT INTO CUST VALUES(10410,'Mary Ann','Howell','Charleston','South
Carolina');
INSERT INTO CUST VALUES(10413,'Donald','Davids','Gila Bend','Arizona');
INSERT INTO CUST VALUES(10419,'Linda','Sakahara','Nogales','Arizona');
INSERT INTO CUST VALUES(10429,'Sarah','Graham','Greensboro','North Carolina');
INSERT INTO CUST VALUES(10438,'Kevin','Smith','Durango','Colorado');
INSERT INTO CUST VALUES(10439,'Conrad','Giles','Telluride','Colorado');
INSERT INTO CUST VALUES(10449,'Isabela','Moore','Yuma','Arizona');
UPDATE CUST SET STATE='Washington' WHERE (CUSTOMERID=10101);
drop table cust;

INSERT INTO ITEMS_ORDERED VALUES(10330, '30-Jun-1999', 'Pogo stick', 1,

28.00);
INSERT INTO ITEMS_ORDERED VALUES(10101, '30-Jun-1999', 'Raft', 1, 58.00);
INSERT INTO ITEMS_ORDERED VALUES(10298, '01-Jul-1999', 'Skateboard', 1,
33.00);
INSERT INTO ITEMS_ORDERED VALUES(10101, '01-Jul-1999', 'Life Vest', 4,
125.00);
INSERT INTO ITEMS_ORDERED VALUES(10299, '06-Jul-1999', 'Parachute', 1,
1250.00);
INSERT INTO ITEMS_ORDERED VALUES(10339, '27-Jul-1999', 'Umbrella', 1, 4.50);
INSERT INTO ITEMS_ORDERED VALUES(10449, '13-Aug-1999', 'Unicycle', 1,
180.79);
INSERT INTO ITEMS_ORDERED VALUES(10439, '14-Aug-1999', 'Ski Poles', 2,
25.50);
INSERT INTO ITEMS_ORDERED VALUES(10101, '18-Aug-1999', 'Rain Coat', 1,
18.30);
INSERT INTO ITEMS_ORDERED VALUES(10449, '01-Sep-1999', 'Snow Shoes', 1,
45.00);
INSERT INTO ITEMS_ORDERED VALUES(10439, '18-Sep-1999', 'Tent', 1, 88.00);
INSERT INTO ITEMS_ORDERED VALUES(10298, '19-Sep-1999', 'Lantern', 2, 29.00);
INSERT INTO ITEMS_ORDERED VALUES(10410, '28-Oct-1999', 'Sleeping Bag', 1,
89.22);
INSERT INTO ITEMS_ORDERED VALUES(10438, '01-Nov-1999', 'Umbrella', 1,
6.75);
INSERT INTO ITEMS_ORDERED VALUES(10438, '02-Nov-1999', 'Pillow', 1, 8.50);
INSERT INTO ITEMS_ORDERED VALUES(10298, '01-Dec-1999', 'Helmet', 1, 22.00);
INSERT INTO ITEMS_ORDERED VALUES(10449, '15-Dec-1999', 'Bicycle', 1,
380.50);
INSERT INTO ITEMS_ORDERED VALUES(10449, '22-Dec-1999', 'Canoe', 1,
280.00);
INSERT INTO ITEMS_ORDERED VALUES(10101, '30-Dec-1999', 'Hoola Hoop', 3,
14.75);
INSERT INTO ITEMS_ORDERED VALUES(10330, '01-Jan-2000', 'Flashlight', 4,
28.00);
INSERT INTO ITEMS_ORDERED VALUES(10330, '30-Jun-1999', 'Pogo stick', 1,
28.00);
INSERT INTO ITEMS_ORDERED VALUES(10101, '02-Jan-2000', 'Lantern', 1, 16.00);
INSERT INTO ITEMS_ORDERED VALUES(10299, '18-Jan-2000', 'Inflatable
Mattress', 1, 38.00);
INSERT INTO ITEMS_ORDERED VALUES(10438, '18-Jan-2000', 'Tent', 1, 79.99);
INSERT INTO ITEMS_ORDERED VALUES(10413, '19-Jan-2000', 'Lawnchair', 4,
32.00);
INSERT INTO ITEMS_ORDERED VALUES(10410, '30-Jan-2000', 'Unicycle', 1,
192.50);
INSERT INTO ITEMS_ORDERED VALUES(10315, '2-Feb-2000', 'Compass', 1, 8.00);
INSERT INTO ITEMS_ORDERED VALUES(10449, '29-Feb-2000', 'Flashlight', 1,
4.50);
INSERT INTO ITEMS_ORDERED VALUES(10101, '08-Mar-2000', 'Sleeping Bag', 2,
88.70);
INSERT INTO ITEMS_ORDERED VALUES(10298, '18-Mar-2000', 'Pocket Knife', 1,
22.38);
INSERT INTO ITEMS_ORDERED VALUES(10449, '19-Mar-2000', 'Canoe paddle', 2,
40.00);
INSERT INTO ITEMS_ORDERED VALUES(10298, '01-Apr-2000', 'Ear Muffs', 1,
12.50);
INSERT INTO ITEMS_ORDERED VALUES(10330, '19-Apr-2000', 'Shovel', 1, 16.75);
--1
SELECT * FROM ITEMS_ORDERED WHERE CUSTOMERID=10449;
--2
SELECT * FROM ITEMS_ORDERED WHERE ITEM='Tent';
--3
SELECT CUSTOMERID,ORDER_DATE,ITEM FROM ITEMS_ORDERED WHERE
ITEM LIKE 'S%';
--4
SELECT DISTINCT ITEM FROM ITEMS_ORDERED GROUP BY ITEM;
--5
SELECT DISTINCT ITEM, MAX(PRICE) FROM ITEMS_ORDERED GROUP BY
ITEM;
--6
SELECT AVG(PRICE) FROM ITEMS_ORDERED WHERE
TO_CHAR(ORDER_DATE,'MM')=12 ;
--7
SELECT MIN(PRICE) FROM ITEMS_ORDERED WHERE ITEM='Tent';
--8
SELECT DISTINCT STATE,COUNT(CUSTOMERID) FROM CUST GROUP BY
STATE;
--9
SELECT ITEM, MAX(PRICE),MIN(PRICE) FROM ITEMS_ORDERED GROUP BY
ITEM;
--10
SELECT CUSTOMERID,COUNT(ITEM) FROM ITEMS_ORDERED GROUP BY
CUSTOMERID;
--11
SELECT DISTINCT STATE,COUNT(CUSTOMERID) FROM CUST GROUP BY
STATE;
--12
SELECT ITEM, MAX(PRICE),MIN(PRICE) FROM ITEMS_ORDERED GROUP BY
ITEM;
--13
SELECT CUSTOMERID,COUNT(ITEM) FROM ITEMS_ORDERED GROUP BY
CUSTOMERID HAVING COUNT(ITEM)>1;
--14
SELECT LASTNAME,FIRSTNAME,CITY FROM CUST ORDER BY LASTNAME
ASC;
--15
SELECT ITEM,PRICE FROM ITEMS_ORDERED WHERE PRICE>10.00 ORDER BY
PRICE ASC;
--16
SELECT CUSTOMERID,ORDER_DATE,ITEM FROM ITEMS_ORDERED WHERE
ITEM!='Snow Shoes' AND ITEM!='Ear Muffs';
--17
SELECT ITEM,PRICE FROM ITEMS_ORDERED WHERE ITEM LIKE 'S%' OR
ITEM LIKE 'P%'OR ITEM LIKE 'F%';
--18
SELECT ORDER_DATE,ITEM,PRICE FROM ITEMS_ORDERED WHERE PRICE
BETWEEN 10.00 AND 80.00;
SELECT *FROM ITEMS_ORDERED;