Page 9 - IPP-11

QUESTION 6

(a) What is a primary key? (1)
Ans. A key attribute that uniquely identifies each row of a relation.
(b) Write SQL query to create the following table. (2)
Table: STUDENT

Column name Data type size Constraint
ROLLNO Integer 4 Primary Key
SNAME Varchar 25 Not Null
GENDER Char 1 Not Null
DOB Date Not Null
FEES Integer 4 Not Null
HOBBY Varchar 15 Null
Ans. CREATE TABLE student(
rollno INT(4) PRIMARY KEY,
sname VARCHAR(25) NOT NULL,
gender CHAR(1) NOT NULL,
dob DATE NOT NULL,
fees INT(4) NOT NULL,
hobby VARCHAR(15)
NULL);
(c) Write SQL queries based on the following tables:
PRODUCT:
P_ID ProductName Manufacturer Price Discount
TP01 Talcum Powder LAK 40
FW05 Face Wash ABC 45 5
BS01 Bath Soap ABC 55
SH06 Shampoo XYZ 120 10
FW12 Face Wash XYZ 95

CLIENT:
C_ID ClientName City P_ID
01 Cosmetic Shop Delhi TP01
02 Total Health Mumbai FW05
03 Live Life Delhi BS01
04 Pretty Woman Delhi SH06
05 Dreams Delhi TP01
(i) Write SQL query to display ProductName and Price for all products whose Price is in the range
of 50 to 150. (1)
(ii) Write SQL query to display details of product whose manufacturer is either XYZ or ABC (1)
(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not given
any discount. (1)
(iv) Write SQL query to display ClientName, City, and P_ID for all clients whose city is Delhi. (2)
(v) Which column is used as Foreign Key and name the table where it has been used as Foreign Key. (2)

6