Boards83
CBSE · 083Class XII · 2023Section C2 marks

Question 28(1)

30 March 2023 · Computer Science (083)

Write the outputs of the SQL queries (i) to (iv) based on the relations COMPUTER and SALES given below :

Table : COMPUTER

PROD_IDPROD_NAMEPRICECOMPANYTYPE
P001MOUSE200LOGITECHINPUT
P002LASER PRINTER4000CANONOUTPUT
P003KEYBOARD500LOGITECHINPUT
P004JOYSTICK1000IBALLINPUT
P005SPEAKER1200CREATIVEOUTPUT
P006DESKJET PRINTER4300CANONOUTPUT

Table : SALES

PROD_IDQTY_SOLDQUARTER
P00241
P00322
P00132
P00421

(i) SELECT MIN(PRICE), MAX(PRICE) FROM COMPUTER; (ii) SELECT COMPANY, COUNT(*) FROM COMPUTER GROUP BY COMPANY HAVING COUNT(COMPANY) > 1; (iii) SELECT PROD_NAME, QTY_SOLD FROM COMPUTER C, SALES S WHERE C.PROD_ID = S.PROD_ID AND TYPE = 'INPUT'; (iv) SELECT PROD_NAME, COMPANY, QUARTER FROM COMPUTER C, SALES S WHERE C.PROD_ID = S.PROD_ID;

Answer

(i)

MIN(PRICE)MAX(PRICE)
2004300

(ii)

COMPANYCOUNT(*)
LOGITECH2
CANON2

(iii)

PROD_NAMEQTY_SOLD
MOUSE3
KEYBOARD2
JOYSTICK2

(iv)

PROD_NAMECOMPANYQUARTER
MOUSELOGITECH2
LASER PRINTERCANON1
KEYBOARDLOGITECH2
JOYSTICKIBALL1

Explanation

(i) MIN and MAX of PRICE. (ii) Groups by COMPANY and keeps only groups with more than 1 row. (iii) Joins on PROD_ID and filters INPUT-type products. (iv) Full equi-join on PROD_ID.