Page 291 - PYTHON-12

(d) To display the price, product name and quantity (i.e., qty) of those products which have quantity more
than 100.
Ans. SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE QTY>100;
(e) To display the names of those suppliers who are either from DELHI or from CHENNAI.
Ans. SELECT SNAME FROM SUPPLIERS WHERE CITY=”DELHI” or CITY=”CHENNAI”;
(f) To display the names of the companies and the names of the products in descending order of company
names.
Ans. SELECT COMPANY, PNAME FROM PRODUCTS ORDER BY COMPANY DESC;
(g) Obtain the outputs of the following SQL queries based on the data given in tables PRODUCTS and
SUPPLIERS above.
(g1) SELECT DISTINCT SUPCODE FROM PRODUCTS;
(g2) SELECT MAX(PRICE), MIN(PRICE) FROM PRODUCTS;
(g3) SELECT PRICE*QTY AMOUNT FROM PRODUCTS WHERE PID=104;
Ans. (g1)
DISTINCT SUPCODE
S01
S02
S03
(g2)

MAX (PRICE) MIN (PRICE)
28000 1100
(g3)
PRICE*QTY
550000

18. (a) Give a suitable example of a table with sample data and illustrate Primary and Alternate Keys in it.
Ans. Primary Key: Primary key is a set of one or more fields/columns of a table that uniquely identifies a record
in database table. It cannot accept null, duplicate values. Only one Candidate Key can be
Primary Key.
Alternate key: Alternate key is a key that can work as a primary key. Basically, it is a candidate key that
currently is not a primary key.
Example: In the table given below, AdmissionNo becomes the Alternate Key when we define
Registration No as the Primary Key.

Student Registration Table:
RegistrationNo AdmissionNo Name Phone Gender DOB
CBSE4554 215647 Mihir Ranjan 9568452325 Male 1992-04-15
CBSE6985 265894 Amita Guha 8456985445 Female 1993-03-24
CBSE5668 458961 Rajesh Singh 9654212440 Male 1992-12-04
CBSE3654 469799 Mohit Patel 7421589652 Male 1992-05-16
Relational Database and SQL
Primary Key – RegistrationNo
Alternate Key – AdmissionNo

12.61