DBMS (ASS4)

 

1. CREATE TABLE SAILOR(

       SID VARCHAR2(4) PRIMARY KEY CHECK(SID LIKE 's%'),

       SNAME VARCHAR2(10) CHECK(SNAME= INITCAP(SNAME)),

       MNAME VARCHAR2(10),

       SURNAME VARCHAR2(10) NOT NULL,

       RATING NUMBER(2) DEFAULT 0,

       AGE NUMBER(3,1) NOT NULL

);

2. CREATE TABLE BOAT(

       BID NUMBER(3) PRIMARY KEY ,

       BNAME VARCHAR2(10) CHECK (BNAME=UPPER(BNAME)),

       COLOR VARCHAR2(6) CHECK (COLOR IN ('red','green','blue'))

);

3. CREATE TABLE RESERVED(

        SID VARCHAR2(4),

        BID NUMBER(3),

        FOREIGN KEY(SID) REFERENCES SAILOR(SID),

        FOREIGN KEY(BID) REFERENCES BOAT(BID),

        DAY DATE CHECK(DAY<'1-JAN-2000'),

        PRIMARY KEY(SID,BID)

);

4.

INSERT INTO SAILOR VALUES('s22','Fredrico',NULL,'Roberts',7, 45);

INSERT INTO SAILOR VALUES('s31','Lubber',NULL,'Sheen',8,55.5);

INSERT INTO SAILOR VALUES('s32','Charlotte',NULL,'Gordin',8,25.5);

INSERT INTO SAILOR VALUES('s58','Mary','Beth','Lyon',10,35);

INSERT INTO SAILOR VALUES('s64','Horatio',NULL,'Powell',7,35.4);

INSERT INTO SAILOR VALUES('s71','Zorba',NULL,'Alex',10,16);

INSERT INTO SAILOR VALUES('s29','Brutus',NULL,'Slater',1,33.8);

INSERT INTO SAILOR VALUES('s95','Deep','Graceb','Davis',3,63.5);

INSERT INTO SAILOR VALUES('s74','Horatio',NULL,'Forrest',9,35);

INSERT INTO SAILOR VALUES('s85','Sara','Art','Powell',3,25.5);

INSERT INTO SAILOR VALUES('s80','Deep','Kumar','Kumar',6,17);

INSERT INTO SAILOR VALUES('s87','Deep','Kumar','Jha',NULL,51);

5.

INSERT INTO BOAT VALUES(101,'INTERLAKE','blue');

INSERT INTO BOAT VALUES(102,'INTERLAKE','red');

INSERT INTO BOAT VALUES(103,'CLIPPER','green');

INSERT INTO BOAT VALUES(104,'MARINE','red');

6.

INSERT INTO RESERVED VALUES('s22',101,'10-OCT-98');

INSERT INTO RESERVED VALUES('s22',103,'10-AUG-98');

INSERT INTO RESERVED VALUES('s22',102,'10-OCT-98');

INSERT INTO RESERVED VALUES('s22',104,'10-JUL-98');

INSERT INTO RESERVED VALUES('s31',102,'11-OCT-98');

INSERT INTO RESERVED VALUES('s31',103,'11-JUN-98');

INSERT INTO RESERVED VALUES('s31',104,'11-DEC-98');

INSERT INTO RESERVED VALUES('s64',101,'09-MAY-98');

INSERT INTO RESERVED VALUES('s64',102,'09-AUG-98');

INSERT INTO RESERVED VALUES('s74',103,'09-AUG-98');

INSERT INTO RESERVED VALUES('s80',102,'07-JUL-98');

INSERT INTO RESERVED VALUES('s87',101,'08-JUL-98');

INSERT INTO RESERVED VALUES('s87',102,'12-DEC-98');

1.

SELECT UPPER(SNAME||' '||MNAME||' '||SURNAME) AS NAME , AGE 

FROM SAILOR;

2.

SELECT *

FROM SAILOR

ORDER BY SNAME ASC;

3.

SELECT DISTINCT(SNAME ||' '||MNAME||' '||SURNAME) AS NAME 

FROM SAILOR;

4.

SELECT DISTINCT(SNAME ||' '||MNAME||' '||SURNAME) AS NAME,RATING 

FROM SAILOR 

WHERE RATING BETWEEN 5 AND 10;

5.

SELECT *

FROM sailor

ORDER BY rating ASC, age DESC;

6.

SELECT *

FROM sailor

WHERE SNAME = 'Horatio' AND age = 35.4;

7.

SELECT S.SNAME

FROM SAILOR S

JOIN RESERVED R ON S.SID = R.SID

WHERE R.BID = 104;

8.

SELECT SID FROM RESERVED 

WHERE BID IN(select BID FROM BOAT WHERE COLOR='red');

9.

SELECT SNAME

FROM SAILOR

WHERE rating IS NOT NULL;

10.

SELECT SNAME

FROM SAILOR

WHERE rating IS NULL;

11.

SELECT  B.COLOR FROM BOAT B

JOIN RESERVED R ON R.BID=B.BID

WHERE SID IN(SELECT SID FROM SAILOR WHERE SNAME='Lubber');

12.

SELECT DISTINCT(SNAME ||' '||MNAME||' '||SURNAME) AS NAME 

FROM SAILOR S 

JOIN RESERVED R ON R.SID=S.SID

WHERE S.SID IN (SELECT R.SID FROM RESERVED R);

13.

SELECT SNAME FROM SAILOR WHERE SNAME LIKE 'B%b' AND LENGTH(SNAME)>=3;

14.

SELECT SNAME FROM SAILOR WHERE SNAME LIKE 'B%b' AND LENGTH(SNAME)=3;

15.

SELECT DISTINCT(S.SNAME||' '||S.MNAME||' '||S.SURNAME) AS NAME FROM SAILOR S

JOIN RESERVED R ON R.SID=S.SID

JOIN BOAT B ON B.BID=R.BID

WHERE B.COLOR='red' OR B.COLOR='green';

16.

SELECT S.SNAME, S.MNAME, S.SURNAME 

FROM SAILOR S 

JOIN RESERVED R ON S.SID = R.SID 

JOIN BOAT B ON R.BID = B.BID 

WHERE B.COLOR IN ('red')

AND S.SID NOT IN (

    SELECT R2.SID 

    FROM RESERVED R2 

    JOIN BOAT B2 ON R2.BID = B2.BID 

    WHERE B2.COLOR = 'green'

);

17.

SELECT S.SNAME, S.MNAME, S.SURNAME 

FROM SAILOR S 

JOIN RESERVED R ON S.SID = R.SID 

WHERE R.BID = 103;

18.

SELECT DISTINCT S.SNAME, S.MNAME, S.SURNAME

FROM SAILOR S

JOIN RESERVED R ON S.SID = R.SID

JOIN BOAT B ON R.BID = B.BID

WHERE B.COLOR = 'red';

19.

SELECT DISTINCT S.SNAME, S.MNAME, S.SURNAME

FROM SAILOR S

WHERE S.SID NOT IN (

    SELECT R.SID

    FROM RESERVED R

    JOIN BOAT B ON R.BID = B.BID

    WHERE B.COLOR = 'red'

);

20.

SELECT *

FROM SAILOR

WHERE RATING > (

    SELECT MIN(RATING)

    FROM SAILOR

    WHERE SNAME = 'Horatio'

);

21.

SELECT AVG(SAILOR.AGE) FROM SAILOR WHERE SAILOR.RATING=10;

22.

SELECT SAILOR.SNAME,SAILOR.AGE FROM SAILOR WHERE SAILOR.AGE>(SELECT MAX (SAILOR.AGE) FROM SAILOR WHERE SAILOR.RATING=10);

23.

SELECT MIN(AGE) AS YOUNGEST_AGE FROM SAILOR GROUP BY RATING;

24.

SELECT S.SNAME, S.MNAME, S.SURNAME, S.RATING

FROM SAILOR S

WHERE AGE>= 18;

25.

SELECT rating, AVG(age) AS average_age

FROM (

    SELECT rating, age, COUNT(*) OVER (PARTITION BY rating) AS sailors_count

    FROM sailor

) subquery

WHERE sailors_count >= 2

GROUP BY rating;

26.

SELECT COLOR,COUNT(*) FROM RESERVED R,BOAT B WHERE B.BID=R.BID GROUP BY COLOR HAVING COLOR='red';

27.

SELECT * FROM SAILOR

WHERE RATING = (SELECT MAX(RATING) FROM sailor WHERE rating < (SELECT MAX(rating) FROM SAILOR));

28.

SELECT SNAME,MNAME,SURNAME FROM SAILOR WHERE RATING=(SELECT MIN(RATING) FROM SAILOR WHERE RATING>(SELECT MIN(RATING) FROM SAILOR WHERE RATING>(SELECT MIN(RATING)FROM SAILOR)));

29.

SELECT SID,SNAME,MNAME,SURNAME

FROM SAILOR

WHERE SID IN (

    SELECT SID

    FROM RESERVED

    GROUP BY SID

    HAVING COUNT(DISTINCT BID) = (

        SELECT COUNT(*) FROM BOAT

    )

);

30.

 SELECT SID,SNAME

FROM SAILOR

WHERE SID IN(

SELECT SID

FROM RESERVED

GROUP BY SID

HAVING COUNT(*)>2

);