Relational Database and SQL — Question 34
Back to all questionsWrite SQL Commands for (i) to (v) and write the outputs for (vi) to (viii) on the basis of the following table:
Table: FURNITURE
| NO | ITEM | TYPE | DATEOFSTOCK | PRICE | DISCOUNT |
|---|---|---|---|---|---|
| 1 | White Lotus | DoubleBed | 2002-02-23 | 3000 | 25 |
| 2 | Pinkfeathers | BabyCot | 2002-01-29 | 7000 | 20 |
| 3 | Dolphin | BabyCot | 2002-02-19 | 9500 | 20 |
| 4 | Decent | OfficeTable | 2002-02-01 | 25000 | 30 |
| 5 | Comfortzone | DoubleBed | 2002-02-12 | 25000 | 30 |
| 6 | Donald | BabyCot | 2002-02-24 | 6500 | 15 |
(i) To list the details of furniture whose price is more than 10000.
(ii) To list the Item name and Price of furniture whose discount is between 10 and 20.
(iii) To delete the record of all items where discount is 30.
(iv) To display the price of 'Babycot'.
(v) To list item name, type and price of all items whose names start with 'D'.
(vi) SELECT DISTINCT Type FROM Furniture;
(vii) SELECT MAX(Price) FROM Furniture WHERE DateofStock > '2002-02-15' ;
(viii) SELECT COUNT(*) FROM Furniture WHERE Discount < 25 ;
(i)
SELECT * FROM FURNITURE
WHERE PRICE > 10000;+----+-------------+-------------+-------------+-------+----------+
| NO | ITEM | TYPE | DATEOFSTOCK | PRICE | DISCOUNT |
+----+-------------+-------------+-------------+-------+----------+
| 4 | Decent | OfficeTable | 2002-02-01 | 25000 | 30 |
| 5 | Comfortzone | DoubleBed | 2002-02-12 | 25000 | 30 |
+----+-------------+-------------+-------------+-------+----------+
(ii)
SELECT ITEM, PRICE
FROM FURNITURE
WHERE DISCOUNT BETWEEN 10 AND 20;+--------------+-------+
| ITEM | PRICE |
+--------------+-------+
| Pinkfeathers | 7000 |
| Dolphin | 9500 |
| Donald | 6500 |
+--------------+-------+
(iii)
DELETE FROM FURNITURE WHERE DISCOUNT = 30;(iv)
SELECT PRICE
FROM FURNITURE
WHERE TYPE = 'BabyCot';+-------+
| PRICE |
+-------+
| 7000 |
| 9500 |
| 6500 |
+-------+
(v)
SELECT ITEM, TYPE, PRICE
FROM FURNITURE
WHERE ITEM LIKE 'D%';+---------+-------------+-------+
| ITEM | TYPE | PRICE |
+---------+-------------+-------+
| Dolphin | BabyCot | 9500 |
| Decent | OfficeTable | 25000 |
| Donald | BabyCot | 6500 |
+---------+-------------+-------+
(vi) SELECT DISTINCT Type FROM Furniture;
+-------------+
| Type |
+-------------+
| DoubleBed |
| BabyCot |
| OfficeTable |
+-------------+
(vii) SELECT MAX(Price) FROM Furniture WHERE DateofStock > '2002-02-15' ;
+------------+
| MAX(Price) |
+------------+
| 9500 |
+------------+
(viii) SELECT COUNT(*) FROM Furniture WHERE Discount < 25 ;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+