11th CS SQL Practicals 2018-19

1 SQL PROGRAM

Table1: FURNITURE

No	Itemname	Type	Date of stock	Price	Discount
1	White lotus	Double bed	23-FEB-02	30000	25
2	Pink feather	Baby cot	20-JAN-02	7000	20
3	Dolphin	Baby cot	19-FEB-02	9500	20
4	Decent	Office table	01-JAN-02	25000	30
5	Comfort zone	Double bed	12-JAN-/02	25000	25
6	Donald	Baby cot	24-FEB-02	6500	15
7	Royal finish	Office table	20-FEB-02	18000	30
8	Royal tiger	Sofa	02-FEB-02	31000	30
9	Econo sitting	Sofa	13-JAN-01	9500	25
10	Eating paradise	Dining table	19-FEB-02	11500	25

Q1.  To show all information about Baby cots from the FURNITURE table.      

Q2.  To list the Itemname which are priced more than 15000 from the FURNITURE table.

Q3. To list the Itemname and Type of those items, in which Dateofstock is before 22-JAN-02 from the FURNITURE table in descending order of Itemname.      

 Q4. To display the Itemname and Dateofstock of those items, in which Discount percentage is more than 25 from FURNITURE table.      

Q5.  To count the number of items, whose Type is “Sofa” from FURNITURE table.

Ans:

create table FURNITURE

(

 No integer,

 Item_name char(15),

 Type char(15),

 DOS date,

 Price decimal,

 Discount integer

);

insert into FURNITURE values( 1,'WHITE LOTUS','DOUBLE BED','23-FEB-02',30000.00,25);

insert into FURNITURE values( 2,'PINK FEATHER','BABY COT','20-JAN-02',7000.00,20);

insert into FURNITURE values( 3,'DOLPHIN','BABY COT','19-FEB-02',9500.00,20);

insert into FURNITURE values( 4,'DECENT','OFFICE TABLE','01-JAN-02',25000.00,30);

insert into FURNITURE values( 5,'COMFORT ZONE','DOUBLE BED','12-JAN-02',25000.00,25);

insert into FURNITURE values( 6,'DONALD','BABY COT','24-FEB-02',6500.00,15);

insert into FURNITURE values( 7,'ROYAL FINISH','OFFICE TABLE','20-FEB-02',18000.00,30);

insert into FURNITURE values( 8,'ROYAL TIGER','SOFA','22-FEB-02',31000.00,30);

insert into FURNITURE values( 9,'ECONO SITTING','SOFA','13-DEC-01',9500.00,25);

insert into FURNITURE values( 10,'EATING PARADISE','DINING TABLE','19-FEB-02',11500.00,25);

     Q1.  To show all information about Baby cots from the FURNITURE table.

      Ans: SELECT * FROM FURNITURE WHERE TYPE='BABY COT';

      Q2.  To list the Itemname which are priced more than 15000 from the FURNITURE table.

      Ans: SELECT ITEM_NAME FROM FURNITURE WHERE PRICE>15000;

      Q3. To list the Itemname and Type of those items, in which Dateofstock is before 22-JAN-02 from the FURNITURE table in descending order of Itemname.

      Ans: SELECT ITEM_NAME, TYPE FROM FURNITURE WHERE DOS<'22-JAN-02' ORDER BY ITEM_NAME DESC;

    

      Q4. To display the Itemname and Dateofstock of those items, in which Discount percentage is more than 25 from FURNITURE table.

      Ans: SELECT ITEM_NAME, DOS FROM FURNITURE WHERE DISCOUNT>25;

     

     Q5.  To count the number of items, whose Type is “Sofa” from FURNITURE table.

    Ans:    SELECT COUNT (*) FROM FURNITURE WHERE TYPE='SOFA';

     

     2 SQL PROGRAM

Table: Employee

ENO	NAME	DOJ	DOB	GENDER	DCODE
1001	GEORORGE K	01-SEPT-2013	01-SEPT-1991	MALE	D01
1002	RYMA SEN	11-DEC-2012	15-DEC-1990	FEMALE	D03
1003	MOHITESH	03-FEB-2013	04-SEPT1987	MALE	D05
1007	ANILA JHA	17-JAN-2014	19-OCT-1984	MALE	D04
1004	MANILA SAHAI	09-DEC-2012	14-NOV-1986	FEMALE	D01
1005	R SAHAY	18-NOV-2013	31-MAR-1987	MALE	D02
1006	JAYA PRIYA	09-JUNE-2014	23-JUNE-1985	FEMALE	D05

Q01 – To display Eno, Name, Gender from the table EMPLOYEE in ascending of Eno.

Q02 – To display the Name of all the MALE employees from the table EMPLOYEE.

Q03 – To display the Eno and Name of those employees from the table EMPLOYEE 

 who are born between  ‘01-JAN-87’ and ‘01-DEC-91’.

Q04 – To  count and display FEMALE employees who have joined after ‘01-JAN-86’.

Q05 – To display  Name, Gender from the table EMPLOYEE in descending  of Name.

Ans

CREATE TABLE EMPLOYEE
(

 ENO CHAR(4),
 NAME CHAR(15),
 DOJ DATE,
 DOB DATE,
 GENDER CHAR(6),
 DCODE CHAR(3) );

INSERT INTO EMPLOYEE VALUES('1001','GEORORGE','01-SEP-13','01-SEP-91','MALE','D01');
INSERT INTO EMPLOYEE VALUES('1002','RYMA SEN','11-DEC-12','15-DEC-90','FEMALE','D03');
INSERT INTO EMPLOYEE VALUES('1003','MOHITESH','03-FEB-13','04-SEP-87','MALE','D05');
INSERT INTO EMPLOYEE VALUES('1007','ANILA JHA','17-JAN-14','19-OCT-84','MALE','D04');
INSERT INTO EMPLOYEE VALUES('1004','MANILA SAHAI','09-DEC-12','14-NOV-86','FEMALE','D01');
INSERT INTO EMPLOYEE VALUES('1005','R SAHAY','18-NOV-13','13-MAR-87','MALE','D02');
INSERT INTO EMPLOYEE VALUES('1006','JAYA PRIYA','09-JUN-14','23-JUN-85','FEMALE','D05');

 

Q01 - To display Eno, Name, Gender from the table EMPLOYEE in ascending of Eno.
Ans: SELECT ENO,NAME,GENDER FROM EMPLOYEE ORDER BY ENO;

Q02 - To display the Name of all the MALE emloyees from the table EMPLOYEE.
Ans: SELECT NAME FROM EMPLOYEE WHERE GENDER='MALE';

Q03 - To display the Eno and Name of those employees from the table EMPLOYEE   who are born between  '01-JAN-87' and '01-DEC-91'.
Ans: SELECT ENO,NAME FROM EMPLOYEE WHERE DOB BETWEEN '01-JAN-87' AND '01-DEC-91';

Q04 - To  count and display FEMALE employees who have joined after '01-JAN-86'.
Ans: SELECT COUNT(*) FROM EMPLOYEE WHERE GENDER='FEMALE' AND DOJ>'01-JAN-86';

Q05 - To display  Name, Gender from the table EMPLOYEE in descending order of Name. Ans: SELECT  NAME,GENDER FROM EMPLOYEE ORDER BY Name desc;