NAME: ABBAS RAZA ZAIDI REG.
NO: 21BCE11503
SLOT: A11+A12+A13 COURSE CODE: CSE3001
EXPERIMENT 1:
Create:
CREATE DATABASE flights;
USE flights;
CREATE TABLE flight(
no INT,
frm VARCHAR(20),
too VARCHAR(20),
distance INT,
departs VARCHAR(20),
arrives VARCHAR(20),
price REAL,
PRIMARY KEY (no)
);
CREATE TABLE aircraft(
aid INT,
aname VARCHAR(20),
cruisingrange INT,
PRIMARY KEY (aid)
);
CREATE TABLE employees(
eid INT,
ename VARCHAR(20),
salary INT,
PRIMARY KEY (eid)
);
CREATE TABLE certified(
eid INT,
aid INT,
PRIMARY KEY (eid,aid),
FOREIGN KEY (eid) REFERENCES employees (eid),
FOREIGN KEY (aid) REFERENCES aircraft (aid)
);
Queries:
1.)
SELECT C.eid
FROM Aircraft A,Certified C
WHERE A.aid=C.aid AND A.aname=’Boeing’
2.)
SELECT E.ename
FROM Aircraft A,Certified C,Employees E
WHERE A.aid=C.aid AND A.aname=’Boeing’ AND E.eid=C.eid
3.)
SELECT a.aid
FROM aircraft a
WHERE a.cruisingrange>
(SELECT MIN(f.distance)
FROM flight f
WHERE f.frm='Bonn'
AND f.too='Madras');
4.)
SELECT E.ename
FROM AIRCRAFT A,Certified C,Employees E,Flight F
WHERE A.aid=C.aid AND E.eid=C.eid AND distance <cruisingrange AND salary>100,000
5.)
SELECT E.ename
FROM Certified C,Employees E,Aircraft A
WHERE A.aid=C.aid AND C.eid=C.eid AND A.cruisingrange>Boeing
AND E.eid NOT IN (SELECT C2.eid
FROM Certified C2,Aircraft A2
WHERE C2.aid=A2.aid AND A2.aname=’Boeing’)
EXPERIMENT 2:
Create:
CREATE TABLE sailors (
sid integer not null,
sname varchar(32),
rating integer,
age real,
CONSTRAINT PK_sailors
PRIMARY KEY (sid)
);
CREATE TABLE reserves (
sid integer not null,
bid integer not null,
day datetime not null,
CONSTRAINT PK_reserves PRIMARY KEY (sid, bid, day),
FOREIGN KEY (sid) REFERENCES sailors(sid),
FOREIGN KEY (bid) REFERENCES boats(bid)
);
Queries:
1.)
SELECT DISTINCT S.sname, S.age
FROM sailors AS S
2.)
SELECT S.sname, S.rating
FROM Sailors S
WHERE S.rating > 7
3.)
SELECT b.name,color*
FROM Boats
4.)
SELECT S.sname
FROM Sailors AS S, Reserves AS R, Boats AS B
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=’red’
5.)
SELECT S.*
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103
6.)
SELECT S.sid
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘pink’
7.)
SELECT color
FROM Sailor S, Reserves R, Boats B
WHERE S.sname=’Rajesh’ AND R.bid=B.bid AND S.sid=R.sid
8.)
SELECT sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
9.)
SELECT sname
FROM Sailors, Boats, Reserves
WHERE Sailors.sid=Reserves.sid AND Reserves.bid=Boats.bid AND Boats.color=’red’
INTERSECT SELECT sname
FROM Sailors, Boats, Reserves
WHERE Sailors.sid=Reserves.sid AND Reserves.bid=Boats.bid AND Boats.color=’green’
10.)
SELECT S.sname
FROM Sailors AS S, Reserves AS R
WHERE S.sid=R.sid AND R.bid=103
11.)
SELECT S.sname
FROM Sailors AS S
WHERE S.sid IN (
SELECT R.sid
FROM Reserve AS R
WHERE R.bid = 103
)
13.)
SELECT S.sname,
FROM Sailors S
WHERE S.rating>=ALL ( SELECT rating FROM Sailors )
12.)
SELECT S.sname, S.rating
FROM Sailors S
WHERE S.rating > ANY ( SELECT S2.rating FROM Sailors S2 WHERE S2.sname = ‘Rajesh’ )
14.)
SELECT COUNT( DISTINCT S.sid)
FROM Sailors S
15.)
SELECT COUNT( DISTINCT B.bid)
FROM Reserves R
16.)
SELECT COUNT( DISTINCT B.bid)
FROM Boats B
17.)
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MAX(S2.age)
FROM Sailors S2 )
18.)
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MIN(S2.age)
FROM Sailors S2 )
19.)
SELECT AVG(S.age) AS avg_age
FROM Sailors S
WHERE S.rating=10
20.)
SELECT COUNT( DISTINCT S.sname )
FROM Sailors S
21.)
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MAX(S2.age) FROM Sailors S2 )
22.)
SELECT COUNT( DISTINCT S.sid)
FROM Sailors S
24.)
SELECT S.sname, S.age
FROM Sailors S
ORDER BY S.age
25.)
SELECT S.sname, S.age
FROM Sailors S
ORDER BY S.sname
