2. Given the following tables for a database FURNITURE:
Table1: FURNITURE
No
|
Item_name
|
Type
|
Date of stock
|
Price
|
Discount
|
1
|
White lotus
|
Double bed
|
23-FEB-02
|
30000
|
25
|
2
|
Pink feather
|
Baby cot
|
20-JAN-02
|
7000
|
20
|
3
|
Dolphin
|
Baby cot
|
19-FEB-02
|
9500
|
20
|
4
|
Decent
|
Office table
|
01/01/02
|
25000
|
30
|
5
|
Comfort zone
|
Double bed
|
12/01/02
|
25000
|
25
|
6
|
Donald
|
Baby cot
|
24/02/02
|
6500
|
15
|
7
|
Royal finish
|
Office table
|
20/02/02
|
18000
|
30
|
8
|
Royal tiger
|
Sofa
|
22/02/02
|
31000
|
30
|
9
|
Econo sitting
|
Sofa
|
13/12/01
|
9500
|
25
|
10
|
Eating paradise
|
Dining table
|
19/02/02
|
11500
|
25
|
create table FURNITURE
(
No integer,
Item_name char(15),
Type char(15),
DOS date,
Price decimal,
Discount integer
);
insert into FURNITURE values( 1,'WHITE LOTUS','DOUBLE BED','23-FEB-02',30000.00,25);
insert into FURNITURE values( 2,'PINK FEATHER','BABY COT','20-JAN-02',7000.00,20);
insert into FURNITURE values( 3,'DOLPHIN','BABY COT','19-FEB-02',9500.00,20);
insert into FURNITURE values( 4,'DECENT','OFFICE TABLE','01-JAN-02',25000.00,30);
insert into FURNITURE values( 5,'COMFORT ZONE','DOUBLE BED','12-JAN-02',25000.00,25);
insert into FURNITURE values( 6,'DONALD','BABY COT','24-FEB-02',6500.00,15);
insert into FURNITURE values( 7,'ROYAL FINISH','OFFICE TABLE','20-FEB-02',18000.00,30);
insert into FURNITURE values( 8,'ROYAL TIGER','SOFA','22-FEB-02',31000.00,30);
insert into FURNITURE values( 9,'ECONO SITTING','SOFA','13-DEC-01',9500.00,25);
insert into FURNITURE values( 10,'EATING PARADISE','DINING TABLE','19-FEB-02',11500.00,25);
Table 2: Arrivals
No
|
Itemname
|
Type
|
Dateofstock
|
Price
|
Discount
|
11
|
Wood comfort
|
Double bed
|
23/03/03
|
25000
|
25
|
12
|
Old fox
|
Sofa
|
20/02/03
|
17000
|
20
|
13
|
Micky
|
Baby cot
|
21/02/03
|
7500
|
15
|
create table ARRIVALS
(No integer,
itemname character(15),
type character(15),
DOS date,
Price decimal,
Discount integer);
insert into ARRIVALS values( 11,'WOOD COMFORT','DOUBLE BED','23-MAR- 03',25000.00,25);
insert into ARRIVALS values(12,'OLD FOX','SOFA','20-FEB-03',17000.00,20);
insert into ARRIVALS values( 13,'MICKY','BABY COT','21-FEB-03',25000.00,25);
I Write SQL queries for Q1 to Q6:
a Q1. To show all information about Baby cots from the FORNITURE table.
Ans: SELECT * FROM FURNITURE WHERE TYPE='BABY COT';
b Q2. To list the Itemname which are priced more than 15000 from the FURNITURE table.
Ans: SELECT ITEM_NAME FROM FURNITURE WHERE PRICE>15000;
c
Q3. To list the Itemname and Type of those items, in which Dateofstock is before 22/01/02 from the FURNITURE table in descending order of Itemname.
Ans: SELECT ITEM_NAME,TYPE FROM FURNITURE WHERE DOS<'22-JAN-02' ORDER BY ITEM_NAME DESC;
d
Q4. To display the Itemname and Dateofstock of those items, in which Discount percentage is more than 25 from FURNITURE table.
Ans: SELECT ITEM_NAME,DOS FROM FURNITURE WHERE DISCOUNT>25;
e
Q5. To count the number of items, whose Type is “Sofa” from FURNITURE table.
A Ans: SELECT COUNT(*) FROM FURNITURE WHERE TYPE='SOFA';
Q6. To insert a new row in the ARRIVALS table with the following data :
14, “Velvet touch”, “Double bed”, {25/03/03}, 25000, 30
Ans: insert into arrivals values( 14,'Velvet touch', 'Double bed', '25-MAR-03', 25000.00,30);
g
II Give the output of the following SQL statements :
i. Select count(distinct Type) from FURNITURE;
Ans: 5: A
ii. Select max(Discount) from FURNITURE;
Ans: 30
iii. Select avg(Discount) from FURNITURE where type=”BABY COT”;
Ans: 18.3333
iv. Select sum(price) from FURNITURE where DOS< '12-FEB-02';
Ans: 66500
No comments:
Post a Comment