Informatics Practices
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 ;
Answer
(i)
Output
+------------+
| SUM(PRICE) |
+------------+
| 2600 |
+------------+
(ii)
Output
+----------------+----------+
| DESCRIPTION | TYPE |
+----------------+----------+
| INFORMAL PANT | COTTON |
| INFORMAL SHIRT | COTTON |
| FORMAL PANT | TERELENE |
+----------------+----------+
(iii)
Output
+------------+
| MAX(FCODE) |
+------------+
| F04 |
+------------+
(iv)
Output
+-----------------------+
| COUNT(DISTINCT PRICE) |
+-----------------------+
| 7 |
+-----------------------+
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 department names and the number of their employees.
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 : 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 : 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' ;