GRADE 12 : PRACTICAL 16

 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';