Bright Tutorials Bright Tutorials
CBSE Class 12 Informatics Practices Question 68 of 79

Database Query using SQL — Question 26

Back to all questions
26
Question

Question 26

Consider the following table:

Table: ITEM

ItemnoInamePriceQuantity
101Soap50100
102Powder10050
103Facecream15025
104Pen50200
105Soapbox20100

Write queries based on the table ITEM:

(a) Display the information of all the items.

(b) Display item name and price value.

(c) Display soap information.

(d) Display the item information whose name starts with letter 's'.

(e) Display a report with item number, item name and total price (total price = price * quantity).

(f) SELECT DISTINCT PRICE FROM ITEM;

(g) SELECT COUNT(DISTINCT Price) FROM ITEM;

Answer

(a)

SELECT * FROM ITEM;
Output
+--------+-----------+-------+----------+
| ITEMNO | INAME     | PRICE | QUANTITY |
+--------+-----------+-------+----------+
|    101 | SOAP      |    50 |      100 |
|    102 | POWDER    |   100 |       50 |
|    103 | FACECREAM |   150 |       25 |
|    104 | PEN       |    50 |      200 |
|    105 | SOAPBOX   |    20 |      100 |
+--------+-----------+-------+----------+

(b)

SELECT INAME, PRICE 
FROM ITEM;
Output
+-----------+-------+
| INAME     | PRICE |
+-----------+-------+
| SOAP      |    50 |
| POWDER    |   100 |
| FACECREAM |   150 |
| PEN       |    50 |
| SOAPBOX   |    20 |
+-----------+-------+

(c)

SELECT  * FROM ITEM 
WHERE INAME = 'SOAP';
Output
+--------+-------+-------+----------+
| ITEMNO | INAME | PRICE | QUANTITY |
+--------+-------+-------+----------+
|    101 | SOAP  |    50 |      100 |
+--------+-------+-------+----------+

(d)

SELECT * FROM ITEM 
WHERE INAME LIKE 'S%';
Output
+--------+---------+-------+----------+
| ITEMNO | INAME   | PRICE | QUANTITY |
+--------+---------+-------+----------+
|    101 | SOAP    |    50 |      100 |
|    105 | SOAPBOX |    20 |      100 |
+--------+---------+-------+----------+

(e)

SELECT ITEMNO, INAME, (PRICE * QUANTITY) AS Total_Price 
FROM ITEM;
Output
+--------+-----------+-------------+
| ITEMNO | INAME     | Total_Price |
+--------+-----------+-------------+
|    101 | SOAP      |        5000 |
|    102 | POWDER    |        5000 |
|    103 | FACECREAM |        3750 |
|    104 | PEN       |       10000 |
|    105 | SOAPBOX   |        2000 |
+--------+-----------+-------------+

(f)

Output
+-------+
| PRICE |
+-------+
|    50 |
|   100 |
|   150 |
|    20 |
+-------+

(g)

Output
+-----------------------+
| COUNT(DISTINCT Price) |
+-----------------------+
|                     4 |
+-----------------------+