Sunday, 27 July 2025

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

USE lab16;


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


ii)Display item name and date of stock of those items in which the discount percentage is less than 20 or more than 25.


iii)Display the number of furniture items for each type

iv) Find the maximum discount offered on each furniture type


v)Display furniture items having a price greater than the average price of all arrivals.


vi)Display furniture types that are not present in the arrivals table

vii) Display item no, item name, mrp and the discounted price for all items


viii)Move all the rows from arrivals into the furniture table


ix)Increase the discount by 5 for all 'sofa' type items in the furniture table.

x)Remove all furniture items that were stocked before January 1, 2002.



No comments:

Post a Comment