DBMS :: 1 AND 2 LAB PROGRAMS

                                  1. VTU 5th SEM DBMS :: STUDENT 

CREATE TABLE STUDENT(
SNUM INT PRIMARY KEY,
NAME VARCHAR(10),
MAJOR  VARCHAR(10),
LEVELS  VARCHAR(5),
AGE INT
);

CREATE TABLE FACULTY
(
FID  VARCHAR(4) PRIMARY KEY,
FNAME VARCHAR(10),
DEPT_ID  INT
);

CREATE TABLE CLASS (
CNAME VARCHAR(10) PRIMARY KEY,
MEETS_AT VARCHAR(10),
ROOM VARCHAR(10),
FID VARCHAR(4),
FOREIGN KEY(FID) REFERENCES FACULTY(FID)
);

CREATE TABLE ENROLLED (
SNUM INT,
CNAME  VARCHAR(10) REFERENCES CLASS(CNAME),
FOREIGN KEY(SNUM) REFERENCES STUDENT(SNUM)
);

INSERT IN TO STUDENT VALUES :::
INSERT INTO STUDENT VALUES (1, 'BLAKE', 'CS', 'JR', 20);
INSERT INTO STUDENT VALUES (2, 'JIM', 'EC', 'SR', 19);
INSERT INTO STUDENT VALUES (3, 'JOHN', 'EC', 'SR', 21);
INSERT INTO STUDENT VALUES (4, 'CHRIS', 'EE', 'JR', 20);
INSERT INTO STUDENT VALUES (5, 'JAKE', 'ME', 'SR', 20);


INSERT IN TO FACULTY VALUES :
INSERT INTO FACULTY VALUES (1, 'HARSHITH', 10);
INSERT INTO FACULTY VALUES (2, 'KAMBER',   20);
INSERT INTO FACULTY VALUES (3, 'NAVATHE',  30);

 INSERT IN TO CLASS VALUES :
INSERT INTO CLASS VALUES ('2SEM', '10AM', '401', 1);
INSERT INTO CLASS VALUES ('3SEM', '12PM', '128', 2);
INSERT INTO CLASS VALUES ('4SEM', '12PM', '601', 3);
INSERT INTO CLASS VALUES ('6SEM', '2PM', '128', 1);
INSERT INTO CLASS VALUES ('5SEM', '8AM', '401', 1);
INSERT INTO CLASS VALUES ('7SEM', '9AM', '601', 1);

 INSERT IN TO ENROLLED VALUES :
INSERT INTO ENROLLED VALUES (1, '4SEM');
INSERT INTO ENROLLED VALUES (2, '6SEM');
INSERT INTO ENROLLED VALUES (3, '3SEM');
INSERT INTO ENROLLED VALUES (4, '2SEM');
INSERT INTO ENROLLED VALUES (5, '5SEM');
INSERT INTO ENROLLED VALUES (2, '4SEM');
INSERT INTO ENROLLED VALUES (3, '4SEM');
INSERT INTO ENROLLED VALUES (1, '3SEM');
INSERT INTO ENROLLED VALUES (1, '2SEM');
INSERT INTO ENROLLED VALUES (4, '6SEM');
INSERT INTO ENROLLED VALUES (5, '3SEM');
INSERT INTO ENROLLED VALUES (1, '5SEM');
INSERT INTO ENROLLED VALUES (5, '4SEM');
INSERT INTO ENROLLED VALUES (4, '4SEM');

Queries:

1. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. HARSHITH

SELECT DISTINCT S.NAME
FROM STUDENT S, CLASS C, ENROLLED E, FACULTY F
WHERE S.SNUM = E.SNUM AND E.CNAME = C.CNAME AND C.FID = F.FID AND
F.FNAME = 'HARSHITH' AND S.LEVELS = 'JR';

NAME
--------
CHRIS
BLAKE

2. Find the names of all classes that either meet in room R128 or have five or more Students enrolled.

SELECT C.CNAME
FROM CLASS C
WHERE C.ROOM = 128
OR C.CNAME IN (SELECT E.CNAME
FROM ENROLLED E
GROUP BY E.CNAME
HAVING COUNT (*) > 4);

CNAME
-------
3SEM
4SEM
6SEM

3. Find the names of all students who are enrolled in two classes that meet at the same time.

SELECT DISTINCT S.NAME
FROM STUDENT S
WHERE S.SNUM IN (SELECT E1.SNUM
FROM ENROLLED E1, ENROLLED E2, CLASS C1, CLASS C2
WHERE E1.SNUM = E2.SNUM AND E1.CNAME !=  E2.CNAME
AND E1.CNAME = C1.CNAME
AND E2.CNAME = C2.CNAME AND C1.MEETS_AT = C2.MEETS_AT);

NAME
------
JAKE
JOHN
BLAKE

4. Find the names of faculty members who teach in every room in which some class is taught.

SELECT DISTINCT F.FNAME , FID
FROM FACULTY F
WHERE NOT EXISTS (
( SELECT DISTINCT C.ROOM FROM CLASS C )
MINUS
( SELECT DISTINCT C1.ROOM  FROM CLASS C1 WHERE C1.FID = F.FID ));

FNAME     FID
---------------
HARSHITH   1

5. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.

SELECT DISTINCT F.FNAME
FROM FACULTY F
WHERE  5 > (SELECT COUNT (E.SNUM)
FROM CLASS C, ENROLLED E
WHERE C.CNAME = E.CNAME
AND C.FID = F.FID);

FNAME
--------
KAMBER


            2.VTU 5th SEM DBMS ::FLIGHT INFORMATION

create table aircraft
(
aid integer primary key,
aname varchar(15),
crange integer
);

create table flights
(
flno integer primary key references aircraft(aid),
fromplace varchar(15),
toplace varchar(15),
distance integer,
departs timestamp,
arrives timestamp,
price integer
);

create table employees
(
eid integer primary key,
ename varchar(15),
salary integer
);

create table certified
(
eid references employees(eid),
aid references aircraft(aid),
primary key(eid,aid)
);


INSERT IN TO AIRCRAFT VALUES::
insert into aircraft values(101,'747',3000);
insert into aircraft values(102,'Boeing',900);
insert into aircraft values(103,'647',800);
insert into aircraft values(104,'Dreamliner',10000);
insert into aircraft values(105,'Boeing',3500);
insert into aircraft values(106,'707',1500);
insert into aircraft values(107,'Dreamliner2',12000);


INSERT IN TO EMPLOYEES VALUES::
insert into employees values(701,'A',50000);
insert into employees values(702,'B',100000);
insert into employees values(703,'C',150000);
insert into employees values(704,'D',90000);
insert into employees values(705,'E',40000);
insert into employees values(706,'F',60000);
insert into employees values(707,'G',70000);


INSERT INTO CERTIFIED VALUES::
insert into certified values(701,101);
insert into certified values(701,102);
insert into certified values(701,106);
insert into certified values(701,105);
insert into certified values(702,104);
insert into certified values(703,104);
insert into certified values(704,104);
insert into certified values(702,107);
insert into certified values(703,107);
insert into certified values(704,107);
insert into certified values(702,101);
insert into certified values(703,105);
insert into certified values(704,105);
insert into certified values(705,103);


insert into flights values(101,'Bangalore','Dehi',2500,TIMESTAMP '2005-05-13 07:15:31',TIMESTAMP '2005-05-13 07:15:31',5000);
insert into flights values(102,'Bangalore','Lucknow',3000,TIMESTAMP '2005-05-13 07:15:31',TIMESTAMP '2005-05-13 07:15:31',6000);
insert into flights values(103,'Lucknow','Dehi',500,TIMESTAMP '2005-05-13 07:15:31',TIMESTAMP '2005-05-13 07:15:31',3000);
insert into flights values(107,'Bangalore','Frankfurt',8000,TIMESTAMP '2005-05-13  07:15:31',TIMESTAMP '2005-05-13 07:15:31',60000);
insert into flights values(104,'Bangalore','Frankfurt',8500,TIMESTAMP '2005-05-13 07:15:31',TIMESTAMP '2005-05-13 07:15:31',75000);
insert into flights values(105,'Kolkata','Dehi',3400,TIMESTAMP '2005-05-13 07:15:31',TIMESTAMP '2005-05-13 07:15:31',7000);


i. Find the names of aircraft such that all pilots certified to operate them have salaries more
 than Rs.80, 000.

SELECT DISTINCT A.ANAME
FROM AIRCRAFT A WHERE A.AID NOT IN (SELECT C.AID
FROM CERTIFIED C, EMPLOYEES E
WHERE C.EID = E.EID AND E.SALARY<80000);
or

SELECT DISTINCT A.ANAME
FROM AIRCRAFT a,CERTIFIED C, EMPLOYEES E
WHERE a.aid=c.aid and c.eid=e.eid AND E.SALARY<80000;

ANAME
---------------
Dreamliner
Dreamliner2


 ii. For each pilot who is certified for more than three aircrafts, find the eid and the maximum
 cruisingrange of the aircraft for which she or he is certified.

SELECT C.EID, MAX (A.CRANGE)
FROM CERTIFIED C, AIRCRAFT A
WHERE C.AID = A.AID
GROUP BY C.EID
HAVING COUNT (*) >= 3;


EID   MAX(A.CRANGE)
----- -------------
701          3500
703         12000
704         12000
702         12000


iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt.

select distinct ename
FROM EMPLOYEES E
WHERE E.SALARY < ( SELECT MIN (F.PRICE)
FROM FLIGHTS F
WHERE F.fromplace ='Bangalore' AND F.toplace = 'Frankfurt');

ENAME
----------
A
E

 iv. For all aircraft with cruisingrange over 1000 Kms,. Find the name of the aircraft and the average salary of all pilots certified for this aircraft.

select name,avgsalary
from (select a.aid,a.aname as name,avg(e.salary) as avgsalary
from aircraft a,certified c,employees e where a.aid=c.aid and e.eid=c.eid and a.crange>1000 group by a.aid,a.aname);

NAME             AVGSALARY
--------------- ----------
747                  75000
Dreamliner      113333.333
Dreamliner2     113333.333
707                  50000
Boeing          96666.6667

5. Find the names of pilots certified for some Boeing aircraft.

SELECT DISTINCT E.ENAME
FROM EMPLOYEES E, CERTIFIED C, AIRCRAFT A
WHERE E.EID = C.EID AND
C.AID = A.AID AND
A.ANAME = 'Boeing';

ENAME
----------
D
A
C

 vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.

SELECT A.AID
FROM AIRCRAFT A
WHERE A.CRANGE >= (SELECT MIN (F.DISTANCE)
FROM FLIGHTS F
WHERE F.FROMplace = 'Bangalore' AND F.TOplace = 'Dehi');

       AID
----------
       101
       104
       105
        107