Create the below tables and perform the required queries:
FURNITURE:
+----+-----------------+--------------+-------------+----------+----------+
| no | itemname | type | dateofstock | price | discount |
+----+-----------------+--------------+-------------+----------+----------+
| 1 | dolphin | baby cot | 19-02-2002 | 9500.00 | 20 |
| 2 | decent | office table | 01-01-2002 | 25000.00 | 30 |
| 3 | comfort zone | double bed | 12-01-2002 | 25000.00 | 25 |
| 4 | donald | baby cot | 24-02-2002 | 6500.00 | 15 |
| 5 | royal finish | office table | 20-02-2002 | 18000.00 | 90 |
| 6 | royal tiger | sofa | 22-02-2002 | 31000.00 | 30 |
| 7 | economy sitting | sofa | 13-12-2001 | 9500.00 | 25 |
| 8 | eating paradise | dining table | 19-02-2002 | 11500.00 | 25 |
+----+-----------------+--------------+-------------+----------+----------+
ARRIVALS:
+----+--------------+------------+-------------+----------+----------+
| no | itemname | type | dateofstock | price | discount |
+----+--------------+------------+-------------+----------+----------+
| 1 | wood comfort | double bed | 23-03-2003 | 25000.00 | 25 |
| 2 | old fox | sofa | 20-03-2003 | 17000.00 | 20 |
| 3 | micky | baby cot | 1-4-2003 | 7500.00 | 15 |
+----+--------------+------------+-------------+----------+----------+
CREATE STATEMENTS :
create database lab17;
use lab17;
create table furniture(
no int primary key,
itemname varchar(50) not null,
type varchar(50) not null,
dateofstock date not null,
price decimal(8,2),
discount numeric(2)
);
create table arrivals(
no int primary key ,
itemname varchar(50) not null,
type varchar(50) not null,
dateofstock date not null,
price decimal(8,2) not null,
discount numeric(2)
);
insert into furniture values
(1,'dolphin','baby cot','2002-02-19',9500.00,20),
(2,'decent','office table','2002-01-01',25000.00,30),
(3,'comfort zone','double bed','2002-01-12',25000.00,25),
(4,'donald','baby cot','2002-02-24',6500.00,15),
(5,'royal finish','office table','2002-02-20',18000.00,90),
(6,'royal tiger','sofa','2002-02-22',31000.00,30),
(7,'economy sitting','sofa','2001-12-13',9500.00,25),
(8,'eating paradise','dining table','2002-02-19',11500.00,25);
insert into arrivals values
(1,'wood comfort','double bed','2003-03-23',25000.00,25),
(2,'old fox','sofa','2003-03-20',17000.00,20),
(3,'micky','baby cot','2003-4-1',7500.00,15);
QUERIES
i)List itemnames and types of those items which were stocked in February from the furniture table in descending order of the item name
SELECT itemname, type
FROM furniture
WHERE MONTH(dateofstock) = 2
ORDER BY itemname DESC;
ii)Display item name and date of stock of those items in which the discount percentage is less than 20 or more than 25.
SELECT itemname, dateofstock
FROM furniture
WHERE discount < 20 OR discount > 25;
iii)Display the number of furniture items for each type
SELECT type, COUNT(*) AS item_count
FROM furniture
GROUP BY type;
iv) Find the maximum discount offered on each furniture type
SELECT type, MAX(discount) AS Max_Discount
FROM furniture
GROUP BY type.
v)Display furniture items having a price greater than the average price of all arrivals.
SELECT itemname, price
FROM furniture
WHERE price > (SELECT AVG(price) FROM arrivals);
vi)Display furniture types that are not present in the arrivals table
SELECT DISTINCT type
FROM furniture
WHERE type NOT IN (SELECT DISTINCT type FROM arrivals);
vii) Display item no, item name, mrp and the discounted price for all items
SELECT
no AS item_no,
itemname,
price AS mrp,
price - (price * discount / 100) AS discounted_price
FROM furniture;
viii)Move all the rows from arrivals into the furniture table
UPDATE arrivals SET no=no+(SELECT MAX(no) FROM furniture);
INSERT INTO furniture SELECT * FROM arrivals;
DELETE FROM arrivals;
ix)Increase the discount by 5 for all 'sofa' type items in the furniture table.
UPDATE furniture
SET discount = discount + 5
WHERE type = 'sofa';
x)Remove all furniture items that were stocked before January 1, 2002.
DELETE FROM furniture
WHERE dateofstock < '2002-01-01';
