12th D SQL- 2 TWO TABLE PRACTICAL

(2014) OUTSIDE DELHI

Table: SHOPEE

Id	SName	Area
S001	ABC Computronics	CP
S002	All Infotech Media	GK II
S003	Tech Shoppe	CP
S004	Greeks Tecno Soft	Nehru Place
S005	Hitech Tech Store	Nehru Place

create table shoppe
(Id character(10),
 Sname character(30),
 Area character(20)
);


insert into shoppe values('S001','ABC Computeronics','CP');

insert into shoppe values('S002','All Infotech Media','GK II');

insert into shoppe values('S003','Tech Shoppe','CP');

insert into shoppe values('S004','Geeks Techno Soft','Nehru Place');

insert into shoppe values('S005','Hitech Tech Store','Nehru Place');

Table: Accessories:

No	Name	Price	Id
A01	Mother Board	12000	S001
A02	Hard Disk	5000	S001
A03	Keyboard	500	S002
A04	Mouse	300	S001
A05	Mother Board	13000	S002
A06	Keyboard	400	S003
A07	LCD	6000	S004
T08	LCD	5500	S005
T09	Mouse	350	S005
T10	Hard Disk	4500	S003

create table accessories
(No character(5),
 Name character(30),
 Price decimal,
 Id character(5)
);

insert into accessories values('A01','Mother Board',12000.00,'S001');
insert into accessories values('A02','Hard Disk',5000.00,'S001');
insert into accessories values('A03','Key Board',500.00,'S002');
insert into accessories values('A04','Mouse',300.00,'S001');
insert into accessories values('A05','Mother Board',13000.00,'S002');
insert into accessories values('A06','Keyboard',400.00,'S003');
insert into accessories values('A07','LCD',6000.00,'S004');
insert into accessories values('T08','LCD',5500.00,'S005');
insert into accessories values('T09','Mouse',350.00,'S005');
insert into accessories values('T10','Hard Disk',4500.00,'S003');

Q.1  To display Name and Price FROM Accessories in ascending order of their Price.
Ans. SELECT NAME,PRICE FROM ACCESSORIES ORDER BY PRICE;

Q.2  To display Id, SName of  all shopee located in Nehru Place
Ans: SELECT ID,SNAME FROM SHOPPE WHERE AREA='Nehru Place';

Q.3. To display Minimum and Maximum Price of each Name of Accessories.
Ans:  SELECT NAME,MIN(PRICE),MAX(PRICE) FROM ACCESSORIES GROUP BY NAME;

Q.4 To display Name, Price of all Accessories and their respective SName where they are available.
Ans:  SELECT NAME,PRICE,SNAME FROM ACCESSORIES A , SHOPPE S WHERE A.ID=S.ID;