SQL1

--Sailors Schema
--Sailors Table

CREATE TABLE SAILORS(
SID NUMBER(4),
SNAME VARCHAR2(20),
RATING VARCHAR2(20),
AGE REAL
);

--Boats Table

CREATE TABLE BOATS(
BID NUMBER(4),
BNAME VARCHAR2(20),
COLOR VARCHAR2(20)
);

--Reserves Table

CREATE TABLE RESERVES(
SID NUMBER(4),
BID NUMBER(4)
);

-- insert Data
INSERT INTO SAILORS VALUES(22,'RAJ',7,45);
INSERT INTO SAILORS VALUES(29, 'NIL',1,33);
INSERT INTO SAILORS VALUES(31, 'VIJAY',8,55.5);
INSERT INTO SAILORS VALUES(32, 'ANAND',8,25.5 );
INSERT INTO SAILORS VALUES(58, 'AKASH',10,35);
INSERT INTO SAILORS VALUES(64, 'UDAY',7,35);
INSERT INTO SAILORS VALUES(71, 'RAM',10,16 );
INSERT INTO SAILORS VALUES(74, 'VIKAS',9,35);
INSERT INTO SAILORS VALUES(85, 'SARVADNYA',3,25.5 );
INSERT INTO SAILORS VALUES(95, 'MIKE',3,63.5);
INSERT INTO SAILORS VALUES(67, 'RAKESH',3,63.5);


INSERT INTO BOATS VALUES(101, 'BOAT1','RED');
INSERT INTO BOATS VALUES(102, 'BOAT2','GREEN');
INSERT INTO BOATS VALUES(103, 'BOAT3','BLUE');
INSERT INTO BOATS VALUES(104, 'BOAT4','YELLOW');

INSERT INTO RESERVES VALUES(22,101);
INSERT INTO RESERVES VALUES(22,102);
INSERT INTO RESERVES VALUES(22,103);
INSERT INTO RESERVES VALUES(22,104);
INSERT INTO RESERVES VALUES(31,102);
INSERT INTO RESERVES VALUES(31,103);
INSERT INTO RESERVES VALUES(31,104);
INSERT INTO RESERVES VALUES(64,101);
INSERT INTO RESERVES VALUES(64,102);
INSERT INTO RESERVES VALUES(67,103);
INSERT INTO RESERVES VALUES(67,101);

SELECT * FROM SAILORS;
SELECT * FROM BOATS;
SELECT * FROM RESERVES;

-- a)Find the names and ages of all sailors
SELECT SNAME, AGE FROM SAILORS;

-- b)Find all sailors with a rating above 7
SELECT * FROM SAILORS WHERE RATING > 7;

-- c)Find the names of sailors who have reserved boat number 103
SELECT S.SNAME FROM SAILORS S WHERE SID IN ( SELECT R.SID FROM RESERVES R WHERE R.BID = 103);



-- e)Find the colors of boats reserved by Raj
SELECT B.COLOR
FROM BOATS B
WHERE BID IN (SELECT R.BID FROM RESERVES R
              WHERE SID IN (SELECT S.SID FROM SAILORS S
                            WHERE S.SNAME = 'RAJ'));            
               -- OTHERWISE --
SELECT B.COLOR FROM BOATS B,SAILORS S,RESERVES R
WHERE S.SID = R.SID AND B.BID = R.BID AND S.SNAME = 'RAJ';

--f)Find the names of sailors who have reserved at least one boat

SELECT S.SNAME FROM SAILORS S
WHERE S.SID IN (SELECT R.SID FROM RESERVES R)


--g)Find the names of sailors who have reserved a red or green boat

SELECT S.SNAME
FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID = R.SID AND B.BID=R.BID AND B.COLOR='RED'
UNION
SELECT S.SNAME
FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID = R.SID AND B.BID = R.BID AND B.COLOR='GREEN';


--h)Find the names of sailors who have reserved both a red and a green boat

SELECT S.SNAME
FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID = R.SID AND B.BID=R.BID AND B.COLOR='RED'
INTERSECT
SELECT S.SNAME
FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID = R.SID AND B.BID = R.BID AND B.COLOR='GREEN';

-- i)Find sid's of all sailors who have reserved red boats but not green boats

SELECT S.SID
FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID = R.SID AND B.BID=R.BID AND B.COLOR='RED'
MINUS
SELECT S.SID
FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID = R.SID AND B.BID = R.BID AND B.COLOR ='GREEN';


-- j)Find all sids of sailors who have a rating of 10 or reserved boat 104
SELECT S.SID FROM SAILORS S WHERE SID IN ( SELECT R.SID FROM RESERVES R WHERE R.BID = 104) OR S.RATING = 10;


-- k. Find the names of sailors who have not reserved a red boat
SELECT DISTINCT S.SNAME FROM SAILORS S,BOATS B,RESERVES R
WHERE S.SID=R.SID AND B.BID=R.BID AND B.COLOR<>'RED' MINUS SELECT
DISTINCT S.SNAME FROM SAILORS S,BOATS B,RESERVES R WHERE
S.SID=R.SID AND B.BID=R.BID AND B.COLOR='RED'

-- l. Find sailors whose rating is better than some sailor called Raj.
SELECT S.SNAME FROM SAILORS S WHERE S.RATING IN (SELECT S.RATING
FROM SAILORS S WHERE S.RATING>(SELECT S.RATING FROM SAILORS S
WHERE S.SNAME='RAJ'))

-- m. Find the names of sailors who have reserved all boats
SORRY

-- n. Find the average age of all sailors
SELECT AVG(AGE) FROM SAILORS

-- o. Find the average age of sailors with a rating of 10
SELECT AVG(AGE) FROM SAILORS S WHERE S.RATING=7

-- p. Find the name and age of the oldest sailor
SELECT S.SNAME,S.AGE FROM SAILORS S WHERE S.AGE IN(SELECT MAX
(S.AGE) FROM SAILORS S )

-- q. Count the number of sailors
SELECT COUNT(S.SID) FROM SAILORS S

-- r. Count the number of different sailor names
SELECT DISTINCT COUNT(S.SNAME) FROM SAILORS S

-- s. Find the names of sailors who are older than the oldest sailor with rating of 10
SELECT S.SNAME FROM SAILORS S WHERE S.AGE IN (SELECT MAX(S.AGE)
FROM SAILORS S WHERE S.AGE <>(SELECT MAX(S.AGE) FROM SAILORS S
)AND S.RATING=8)

-- t. Find the age of the youngest sailor for each rating level
SELECT S.RATING, MIN(S.AGE) FROM SAILORS S GROUP BY S.RATING

-- u. Find the age of youngest sailor who is at least 18 years old
SELECT MIN(S.AGE) FROM SAILORS S WHERE S.AGE>=18

-- v. For each red boat, find the number of reservations for this boat
SORRY

-- w. Find average age of sailors for each rating level.
SELECT S.RATING, AVG(S.AGE) FROM SAILORS S GROUP BY S.RATING

-- x. Find those ratings for which the average age of sailors is 18
SORRY











Post a Comment

0 Comments