Informatics Practices
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 ;
Answer
(i)
Output
+-----------+
| City |
+-----------+
| Delhi |
| Mumbai |
| Bangalore |
+-----------+
(ii)
Output
+--------------+------------+------------+----------+
| Manufacturer | MAX(Price) | Min(Price) | Count(*) |
+--------------+------------+------------+----------+
| ABC | 55 | 45 | 2 |
| XYZ | 120 | 95 | 2 |
| LAK | 40 | 40 | 1 |
+--------------+------------+------------+----------+
(iii)
Output
+---------------+---------------+
| ClientName | ProductName |
+---------------+---------------+
| Cosmetic Shop | Face Wash |
| Total Health | Bath Soap |
| Live Life | Shampoo |
| Pretty Woman | Face Wash |
| Dreams | Talcum Powder |
+---------------+---------------+
(iv)
Output
+---------------+-----------+
| ProductName | Price * 4 |
+---------------+-----------+
| Bath Soap | 220 |
| Face Wash | 180 |
| Face Wash | 380 |
| Shampoo | 480 |
| Talcum Powder | 160 |
+---------------+-----------+
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 : 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' ;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' ;