SQL-1 PROGRAM

 Two Table General Syntax

 Two Table General Syntax and Example:

SELECT  T2C,  T1C,  FROM  TABLE2  T2,  TABLE1 T1 WHERE T2..PKEY=T1..FKEY  CONDITION

EXAMPLE:
SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E.DCODE=D.DCODE AMD ENO<1003;      (OD. 2015)

SELECT  PNAME, SNAME FROM PRODUCTS P , SUPPLIERS S WHERE P.SUPCODE=S.SUPCODE AND QTY>100;  (OD 2013

       Given the following tables for a database FURNITURE:

Table1: FURNITURE

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

create database F;

use F;

create table FURNITURE

(

 No integer primary key,

 Item_name char(15),

 Type char(15),

 DOS date,

 Price decimal,

 Discount integer

);

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

insert into FURNITURE values( 2,'PINK FEATHER ','BABYCOT ',' 20/01/02', 7000.00, 20);

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

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

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

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

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

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

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

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

Table 2: Arrivals

No	Itemname	Type	Dateofstock	Price	Discount
11	Wood comfort	Double bed	23/03/03	25000	25
12	Old fox	Sofa	20/02/03	17000	20
13	Micky	Baby cot	21/02/03	7500	15

create table ARRIVALS

(No integer,

 itemname character(15),

 type character(15),

 DOS date,

 Price decimal,

 Discount integer);

insert into ARRIVALS values( 11,'WOOD COMFORT','DOUBLE BED', '23/03/03', 25000.00, 25);

 insert into ARRIVALS values(12,'OLD FOX','SOFA','20/02/03', 17000.00, 20);

 insert into ARRIVALS values( 13,'MICKY','BABY COT','21/02/03 ', 25000.00, 25);

I  Write SQL queries for Q1 to Q6:

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

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

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

      Ans: SELECT ITEM_NAME FROM FURNITURE WHERE PRICE>15000;

c    

      Q3. To list the Itemname and Type of those items, in which Dateofstock is before 22/01/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;

e   

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

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

     Q6. To insert a new row in the ARRIVALS table with the following data :

      14, “Velvet touch”, “Double bed”, {25/03/03}, 25000, 30

    Ans:  insert into arrivals values( 14,'Velvet touch', 'Double bed', '25/03/03', 25000.00,30);

g

      Give the output of the following SQL statements :

                                    Q7   Select count(distinct Type) from FURNITURE;

                                Ans: 5:    

                                    Q8 Select max(Discount) from FURNITURE;

                                Ans: 30

                                     Q9 Select avg(Discount) from FURNITURE where type=”BABY COT”;

                              Ans: 18.3333

                                  Q10   Select sum(price) from FURNITURE where DOS< '12-FEB-02';

       Ans: 66500