1. Consider the following relations:
student (snum: integer, sname: string, major: string, level: string, age: integer)
class (name: string, meets at: string, room: string, d: integer)
enrolled (snum: integer, cname: string)
faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Level is a two character code with 4 different values (example: Junior: JR etc) Write the following queries in SQL. No duplicates should be printed in any of the answers.
i. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
ii. Find the names of all classes that either meet in room R128 or have five or more Students enrolled. iii. Find the names of all students who are enrolled in two classes that meet at the same time.
iv. Find the names of faculty members who teach in every room in which some class is taught.
v. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
Solution :
create table student(snum integer,sname varchar(20),major varchar(10),levels varchar(5),age integer,primary key(snum));
create table faculty(fid integer,fname varchar(20),deptid integer,primary key(fid));
create table class(cname varchar(20),meetsat varchar(20),room varchar(5),fid integer,primary key(cname),foreign key(fid) references faculty(fid));
create table enrolled(snum integer,cname varchar(20),primary key(snum,cname),foreign key(snum)references student(snum),foreign key(cname) references class(cname));
INSERT VALUES TO ALL TABLES
insert into student values('&snum','&sname','&major','&levels','&age');
insert into faculty values('&fid','&fname','&deptid');
insert into class values('&cname','&meetsat','&room','&fid');
insert into enrolled values('&snum','&cname');
QUERY
i. select distinct sname from student s,faculty f,class c,enrolled e where e.snum=s.snum and f.fid=c.fid and e.cname=c.cname and levels='jr' and fname='harshith';
OUTPUT:
abhi
ahana
anoop
ii. select cname from class where room='r128'
union
select cname from enrolled group by cname having count(*)>4;
OUTPUT:
iot
java
python
iii. select distinct sname from student s,class c,enrolled e where s.snum=e.snum and c.cname=e.cname and (select count(*) from student x,class y,enrolled z where x.snum=z.snum and y.cname=z.cname and s.snum=x.snum and c.meetsat=y.meetsat)>=2;
OUTPUT:
abhi
ahana
bhavya
anoop
iv. select fname from faculty where fid in(select fid from class group by fid having count(distinct room)=(select count(distinct room) from class));
OUTPUT:
harshith
v. select fname from faculty f,class c,enrolled e where f.fid=c.fid and c.cname=e.cname and group by fname having count(*)<5;
OUTPUT:
aarav
akshatha
jeevan
archana
--------------------------------------------------------------------------------------------------------------------------
2. The following relations keep track of airline flight information:
Flights (no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time, price: real)
Aircraft (aid: integer, aname: string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well;
Every pilot is certified for some aircraft, and only pilots are certified to fly.
Write each of the following queries in SQL.
i. Find the names of aircraft such that all pilots certified to operate them have salaries more than Rs.80, 000.
ii. For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruising range of the aircraft for which she or he is certified.
iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt.
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.
v. Find the names of pilots certified for some Boeing aircraft. vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
SOLUTION :
Create table first
1. create table flight(fno integer,fcity varchar(20),tcity varchar(20),dist integer,dept timestamp,arrive timestamp,price integer,primary key(fno));
2. create table aircraft(aid integer,aname varchar(20),crange integer, primary key(aid));
3. create table employee(eid integer,ename varchar(20),salary integer,primary key(eid));
4. create table certified(eid integer,aid integer,primary key(eid,aid),foreign key(eid)references employee(eid),foreign key(aid)references aircraft(aid));
INSERT VALUES
1. insert into flight values('&fno','&fcity','&tcity','&distance','&dept','&arrive','&price');
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
1 bangalore delhi 3000
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
3500
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
2 bangalore delhi 1500
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
3000
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
3 bangalore delhi 900
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
2000
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
4 bangalore frankfort 500
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
4000
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
5 bangalore frankfort 800
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
3000
2. insert into aircraft values('&aid','&aname','&arange');
3. insert into employee values('&eid','&ename','&salary');
4. insert into certified values('&eid','&aid');
QUERY :
1. select aname from aircraft a,certified c,employee e where a.aid=c.aid and e.eid=c.eid group by aname having min(salary)>80000;
OUTPUT:
boeing10
boeing15
boeing20
2. select aname,avg(salary) from aircraft a,certified c,employee e where a.aid=c.aid and c.eid=e.eid group by aname having min(crange)>1000;
OUTPUT:
ANAME AVG(SALARY)
-------------------- -----------
boeing20 87500
boeing15 87500
boeing10 87500
3. select e.eid,max(crange) from aircraft a,certified c,employee e where a.aid=c.aid and c.eid=e.eid group by e.eid having count(*)>3;
OUTPUT
EID MAX(CRANGE)
---------- -----------
501 4000
4. select distinct ename from aircraft a,certified c,employee e where a.aid=c.aid and c.eid=e.eid and aname like 'boeing%';
OUTPUT:
ENAME
--------------------
abhi
ahana
5.select ename from employee where salary<(select min(price) from flight where fcity='bangalore' and tcity='frankfort');
OUTPUT:
ENAME
--------------------
bhavana
bhavya
6.select aid from aircraft where crange>(select min(dist) from flight where fcity='bangalore' and tcity='delhi');
OUTPUT:
AID
----------
101
102
103
104
student (snum: integer, sname: string, major: string, level: string, age: integer)
class (name: string, meets at: string, room: string, d: integer)
enrolled (snum: integer, cname: string)
faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such that the student is enrolled in the class. Level is a two character code with 4 different values (example: Junior: JR etc) Write the following queries in SQL. No duplicates should be printed in any of the answers.
i. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
ii. Find the names of all classes that either meet in room R128 or have five or more Students enrolled. iii. Find the names of all students who are enrolled in two classes that meet at the same time.
iv. Find the names of faculty members who teach in every room in which some class is taught.
v. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five.
Solution :
create table student(snum integer,sname varchar(20),major varchar(10),levels varchar(5),age integer,primary key(snum));
create table faculty(fid integer,fname varchar(20),deptid integer,primary key(fid));
create table class(cname varchar(20),meetsat varchar(20),room varchar(5),fid integer,primary key(cname),foreign key(fid) references faculty(fid));
create table enrolled(snum integer,cname varchar(20),primary key(snum,cname),foreign key(snum)references student(snum),foreign key(cname) references class(cname));
INSERT VALUES TO ALL TABLES
insert into student values('&snum','&sname','&major','&levels','&age');
insert into faculty values('&fid','&fname','&deptid');
insert into class values('&cname','&meetsat','&room','&fid');
insert into enrolled values('&snum','&cname');
QUERY
i. select distinct sname from student s,faculty f,class c,enrolled e where e.snum=s.snum and f.fid=c.fid and e.cname=c.cname and levels='jr' and fname='harshith';
OUTPUT:
abhi
ahana
anoop
ii. select cname from class where room='r128'
union
select cname from enrolled group by cname having count(*)>4;
OUTPUT:
iot
java
python
iii. select distinct sname from student s,class c,enrolled e where s.snum=e.snum and c.cname=e.cname and (select count(*) from student x,class y,enrolled z where x.snum=z.snum and y.cname=z.cname and s.snum=x.snum and c.meetsat=y.meetsat)>=2;
OUTPUT:
abhi
ahana
bhavya
anoop
iv. select fname from faculty where fid in(select fid from class group by fid having count(distinct room)=(select count(distinct room) from class));
OUTPUT:
harshith
v. select fname from faculty f,class c,enrolled e where f.fid=c.fid and c.cname=e.cname and group by fname having count(*)<5;
OUTPUT:
aarav
akshatha
jeevan
archana
--------------------------------------------------------------------------------------------------------------------------
2. The following relations keep track of airline flight information:
Flights (no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time, price: real)
Aircraft (aid: integer, aname: string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well;
Every pilot is certified for some aircraft, and only pilots are certified to fly.
Write each of the following queries in SQL.
i. Find the names of aircraft such that all pilots certified to operate them have salaries more than Rs.80, 000.
ii. For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruising range of the aircraft for which she or he is certified.
iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to Frankfurt.
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.
v. Find the names of pilots certified for some Boeing aircraft. vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
SOLUTION :
Create table first
1. create table flight(fno integer,fcity varchar(20),tcity varchar(20),dist integer,dept timestamp,arrive timestamp,price integer,primary key(fno));
2. create table aircraft(aid integer,aname varchar(20),crange integer, primary key(aid));
3. create table employee(eid integer,ename varchar(20),salary integer,primary key(eid));
4. create table certified(eid integer,aid integer,primary key(eid,aid),foreign key(eid)references employee(eid),foreign key(aid)references aircraft(aid));
INSERT VALUES
1. insert into flight values('&fno','&fcity','&tcity','&distance','&dept','&arrive','&price');
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
1 bangalore delhi 3000
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
3500
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
2 bangalore delhi 1500
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
3000
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
3 bangalore delhi 900
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
2000
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
4 bangalore frankfort 500
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
4000
FNO FCITY TCITY DIST
---------- -------------------- -------------------- ----------
DEPT
---------------------------------------------------------------------------
ARRIVE
---------------------------------------------------------------------------
PRICE
----------
5 bangalore frankfort 800
08-DEC-16 09.00.00.000000 AM
08-DEC-16 12.00.00.000000 PM
3000
2. insert into aircraft values('&aid','&aname','&arange');
3. insert into employee values('&eid','&ename','&salary');
4. insert into certified values('&eid','&aid');
QUERY :
1. select aname from aircraft a,certified c,employee e where a.aid=c.aid and e.eid=c.eid group by aname having min(salary)>80000;
OUTPUT:
boeing10
boeing15
boeing20
2. select aname,avg(salary) from aircraft a,certified c,employee e where a.aid=c.aid and c.eid=e.eid group by aname having min(crange)>1000;
OUTPUT:
ANAME AVG(SALARY)
-------------------- -----------
boeing20 87500
boeing15 87500
boeing10 87500
3. select e.eid,max(crange) from aircraft a,certified c,employee e where a.aid=c.aid and c.eid=e.eid group by e.eid having count(*)>3;
OUTPUT
EID MAX(CRANGE)
---------- -----------
501 4000
4. select distinct ename from aircraft a,certified c,employee e where a.aid=c.aid and c.eid=e.eid and aname like 'boeing%';
OUTPUT:
ENAME
--------------------
abhi
ahana
5.select ename from employee where salary<(select min(price) from flight where fcity='bangalore' and tcity='frankfort');
OUTPUT:
ENAME
--------------------
bhavana
bhavya
6.select aid from aircraft where crange>(select min(dist) from flight where fcity='bangalore' and tcity='delhi');
OUTPUT:
AID
----------
101
102
103
104
