FOR FREE CONTENT

CREATE TABLES AND INSERT VALUES

1. CREATE TABLE SAILORS:

(

SID VARCHAR(4) PRIMARY KEY,

SNAME VARCHAR(20) ,

RATING INTEGER(5),

AGE INTEGER(3)

);

1. DESC SAILORS ;

1. INSERT INTO "SAILORS" TABLE :

INSERT INTO SAILORS VALUES ('S001','SURESH',4,36);

INSERT INTO SAILORS VALUES ('S002','RAJESH',5,41);

INSERT INTO SAILORS VALUES ('S003','RAMESH',3,29);

INSERT INTO SAILORS VALUES ('S004','FRANCIS',4,38);

INSERT INTO SAILORS VALUES ('S005','MOHAN',5,42);

INSERT INTO SAILORS VALUES ('S006','KARTIK',4,30);

1. SELECT *FROM SAILORS;

2. CREATE TABLE BOATS

(

BID VARCHAR(4) PRIMARY KEY,

BNAME VARCHAR(20),

COLOR VARCHAR(20)

);

2. DESC BOATS;

2. INSERT INTO "BOATS" TABLE :

INSERT INTO BOATS VALUES ('B001','TITANIC','RED');

INSERT INTO BOATS VALUES ('B002','SERENDIPITY','YELLOW');

INSERT INTO BOATS VALUES ('B003','EPIPHANY','BLUE');

INSERT INTO BOATS VALUES ('B004','INTERLAKE','GREEN');

2. SELECT *FROM BOATS;

3. CREATE TABLE RESERVES:

(

SID VARCHAR(4) REFERENCES SAILORS(SID),

BID VARCHAR(4) REFERENCES BOATS(BID),

DAY DATE

);

3. DESC RESERVES;

3. INSERT INTO "RESEVES" TABLE :

INSERT INTO RESERVES VALUES ('S001','B001','03-05-2002');

INSERT INTO RESERVES VALUES ('S002','B002','14-11-2009');

INSERT INTO RESERVES VALUES ('S003','B004','30-11-2011');

INSERT INTO RESERVES VALUES ('S004','B003','03-09-2007');

INSERT INTO RESERVES VALUES ('S005','B002','13-12-2004');

INSERT INTO RESERVES VALUES ('S006','B001','22-06-2006');

3. SELECT *FROM RESERVES;

QUERIES :

1. FIND NAMES OF SAILORS WHO RESERVED GREEN BOAT

ANS :- SELECT SAILORS.SNAME FROM SAILORS,BOATS,RESERVES WHERE

SAILORS.SID=RESERVES.SID AND BOATS.BID=RESERVES.BID AND BOATS.COLOR = 'GREEN';

2. FIND THE COLORS OF BOAT RESERVED BY RAMESH.

ANS :- SELECT BOATS.COLOR FROM SAILORS,BOATS,RESERVES WHERE

SAILORS.SID=RESERVES.SID AND BOATS.BID=RESERVES.BID AND SAILORS.SNAME = 'RAMESH';

3. FIND NAME OF THE SAILORS WHO HAVE RESERVED A RED OR GREEN BOAT.

ANS :- SELECT DISTINCT SAILORS.SNAME FROM SAILORS,BOATS,RESERVES WHERE

SAILORS.SID=RESERVES.SID AND BOATS.BID=RESERVES.BID AND (BOATS.COLOR = 'RED' OR

BOATS.COLOR = 'GREEN');

4. FIND NAME OF THE SAILORS WHO HAVE RESERVED A RED BOAT.

ANS :- SELECT SAILORS.SNAME FROM SAILORS,BOATS,RESERVES WHERE

SAILORS.SID=RESERVES.SID AND BOATS.BID=RESERVES.BID AND BOATS.COLOR = 'RED';

5. FIND NAME OF THE SAILORS WHO HAVE RESERVED ALL BOATS CALLED "INTERLAKE".

ANS :- SELECT SAILORS.SNAME FROM SAILORS,BOATS,RESERVES WHERE

SAILORS.SID=RESERVES.SID AND BOATS.BID=RESERVES.BID AND BOATS.BNAME = 'INTERLAKE';