Page 289 - PYTHON-12

Ans. (i) Select * from Stock
order by StockDate;
(ii) Select * from Stock
where Dcode = 102 or Qty > 100;
(iii) Insert into Stock
values (5010, ‘Pencil HB’, 102, 500, 10, ‘2010-01-26’);
(iv) Select Dealer.Dcode, Dname, Item, UnitPrice from Dealer left join Stock on
Dealer.Dcode = Stock.Dcode;
(v) 3
(vi) 4400
16. (a) Explain the concept of Cartesian product between two tables with the help of an example.
Note: Answer the questions (b) and (c) on the basis of the following tables SHOP and ACCESSORIES.
Table: SHOP
Id SName Area
S01 ABC Computronics CP
S02 All Infotech Media GK II
S03 Tech Shoppe CP
S04 Geek Tenco Soft Nehru Place
S05 Hitech Tech Store Nehru Place

Table: ACCESSORIES
No Name Price Id
A01 Motherboard 12000 S01
A02 Hard Disk 5000 S01
A03 Keyboard 500 S02
A04 Mouse 300 S01
A05 Motherboard 13000 S02
A06 Keyboard 400 S03
A07 LCD 6000 S04
T08 LCD 5500 S05
T09 Mouse 350 S05
T010 Hard Disk 450 S03
Ans. When you join two or more tables without any condition, it is called Cartesian product or Cross Join.
Example: SELECT * FROM SHOP, ACCESSORIES;
(b) Write the SQL queries:
(i) To display Name and Price of all the Accessories in ascending order of their Price.
(ii) To display Id and SName of all Shops located in Nehru Place.
(iii) To display Minimum and Maximum Price of all the accessories.
Ans. (i) SELECT Name, Price FROM ACCESSORIES ORDER BY Price;
(ii) SELECT Id, SName FROM SHOP WHERE Area=’Nehru Place’;
(iii) SELECT Name, MAX(Price), MIN(Price) FROM ACCESSORIES;
(c) Write the output of the following SQL commands:
(i) SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>=5000;
(ii) SELECT AREA, COUNT(*) FROM SHOP GROUP BY AREA; Relational Database and SQL
(iii) SELECT COUNT(DISTINCT AREA) FROM SHOP;

12.59