Informatics Practices
Give output for following SQL queries as per given table(s) :
Table : ITEM
| I_ID | ItemName | Manufacturer | Price |
|---|---|---|---|
| PC01 | Personal Computer | ABC | 35000 |
| LC05 | Laptop | ABC | 55000 |
| PC03 | Personal Computer | XYZ | 32000 |
| PC06 | Personal Computer | COMP | 37000 |
| LC03 | Laptop | PQR | 57000 |
Table : CUSTOMER
| C_ID | CustomerName | City | I_ID |
|---|---|---|---|
| 01 | N Roy | Delhi | LC03 |
| 06 | H Singh | Mumbai | PC03 |
| 12 | R Pandey | Delhi | PC06 |
| 15 | C Sharma | Delhi | LC03 |
| 16 | K Agarwal | Bangalore | PC01 |
(i) SELECT DISTINCT City FROM Customer ;
(ii) SELECT ItemName, MAX(Price), Count(*)
FROM Item GROUP BY ItemName ;
(iii) SELECT CustomerName, Manufacturer
FROM Item, Customer
WHERE Item.I_ID = Customer.I_ID ;
(iv) SELECT ItemName, Price * 100
FROM Item WHERE Manufacturer = 'ABC' ;
Answer
(i)
Output
+-----------+
| City |
+-----------+
| DELHI |
| MUMBAI |
| BANGALORE |
+-----------+
(ii)
Output
+-------------------+------------+----------+
| ItemName | MAX(Price) | Count(*) |
+-------------------+------------+----------+
| LAPTOP | 57000 | 2 |
| PERSONAL COMPUTER | 37000 | 3 |
+-------------------+------------+----------+
(iii)
Output
+--------------+--------------+
| CustomerName | Manufacturer |
+--------------+--------------+
| N ROY | PQR |
| H SINGH | XYZ |
| R PANDEY | COMP |
| C SHARMA | PQR |
| K AGARWAL | ABC |
+--------------+--------------+
(iv)
Output
+-------------------+-------------+
| ItemName | Price * 100 |
+-------------------+-------------+
| LAPTOP | 5500000 |
| PERSONAL COMPUTER | 3500000 |
+-------------------+-------------+
Related Questions
Schemas of tables EMPL, Dept, SalaryGrade are being shown below :
EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SALARYGRADE (Lowsal, Highsal, Grade) DEPT (Deptno, DeptName, Location)List the employee names and the name of their departments.
Give output for following SQL queries as per given table(s) :
Table : GARMENT
GCODE Description Price FCODE READYDATE 10023 PENCIL SKIRT 1150 F03 19-DEC-08 10001 FORMAL SHIRT 1250 F01 12-JAN-08 10012 INFORMAL SHIRT 1550 F02 06-JUN-08 10024 BABY TOP 750 F03 07-APR-07 10090 TULIP SKIRT 850 F02 31-MAR-07 10019 EVENING GOWN 850 F03 06-JUN-08 10009 INFORMAL PANT 1500 F02 20-OCT-08 10017 FORMAL PANT 1350 F01 09-MAR-08 10020 FROCK 850 F04 09-SEP-07 10089 SLACKS 750 F03 31-OCT-08 Table : FABRIC
FCODE TYPE F04 POLYSTER F02 COTTON F03 SILK F01 TERELENE (i) SELECT SUM(PRICE) FROM GARMENT WHERE FCODE = 'F01' ;
(ii) SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC
WHERE GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >= 1260 ;(iii) SELECT MAX(FCODE) FROM FABRIC ;
(iv) SELECT COUNT(DISTINCT PRICE) FROM GARMENT ;
Give output for following SQL queries as per given table(s) :
Table : PRODUCT
P_ID ProductName Manufacturer Price TPO1 Talcom Powder LAK 40 FW05 Face Wash ABC 45 BS01 Bath Soap ABC 55 SHO6 Shampoo XYZ 120 FW12 Face Wash XYZ 95 Table : CLIENT
C_ID ClientName City P_ID 01 Cosmetic Shop Delhi FW05 06 Total Health Mumbai BS01 12 Live Life Delhi SHO6 15 Pretty Woman Delhi FW12 16 Dreams Bangalore TP01 (i) SELECT DISTINCT City FROM Client ;
(ii) SELECT Manufacturer, MAX(Price), Min(Price), Count(*)
FROM Product GROUP BY Manufacturer ;(iii) SELECT ClientName, ProductName
FROM Product, Client
WHERE Client.P_Id = Product.P_Id ;(iv) SELECT ProductName, Price * 4 FROM Product ;
Give output for following SQL queries as per given table(s) :
Table : SENDER
SenderID SenderName SenderAddress SenderCity ND01 R Jain 2, ABC Appts New Delhi MU02 H Sinha 12, Newtown Mumbai MU15 S Jha 27/A, Park Street Mumbai ND50 T Prasad 122-K, SDA New Delhi Table : RECIPIENT
RecID SenderID RecName RecAddress RecCity KO05 ND01 R Bajpayee 5, Central Avenue Kolkata ND08 MU02 S Mahajan 116, A Vihar New Delhi MU19 ND01 H Singh 2A, Andheri East Mumbai MU32 MU15 P K Swamy B5, C S Terminus Mumbai ND48 ND50 S Tripathi 13, B1 D, Mayur Vihar New Delhi (i) SELECT DISTINCT SenderCity FROM Sender ;
(ii) SELECT A.SenderName, B.RecName
FROM Sender A, Recipient B
WHERE A.SenderID = B.SenderID AND B.RecCity = 'Mumbai' ;(iii) SELECT RecName, RecAddress FROM Recipient
WHERE RecCity NOT IN('Mumbai', 'Kolkata') ;(iv) SELECT RecID, RecName FROM Recipient
WHERE SenderID = 'MU02' OR SenderID = 'ND50' ;