#Problem statement
Professors have a PROFID, a name, an age, a rank, and a research
specialty. Projects have a project number, a sponsor name (e.g. UGC/AICTE/...),
a starting date, an ending date, and a budget. Graduate students have an USN, a
name, an age, and a degree program (e.g. MCA/ MPhil/BE/ME ..). Each project is
managed exactly by one professor (known as the project's principal
investigator). Each project is worked on by one or more professors (known as
the project's co-investigators). Professors can manage/work on multiple
projects. Each project is worked on by one or more graduate students (known as
the project's research assistants). Graduate students can work on multiple
projects. Each professor can supervise many students. A student who is working
on a project can be supervised by only one professor.
Queries
a) Retrieve the names of all professors who do not have an
ongoing project of more than 1 lakh.
b) Retrieve the names of all graduate students along with their
professors under whom they work and project sponsor.
c) List the professors and sum of the budget of their
projects started after 2005 but ended in 2010.
d) List the names of professors who has a total worth of project
greater than the average budget of projects sanctioned
e) List the professors who work on all the projects.
...................................CREATE STATEMENTS...................................
create table projects
(
pno
number,
sponsor
varchar(15),
st_date
number,
end_date
number,
budget
number,
primary key(pno)
);
create table professor
(
prof_id
number
primary key,
name
varchar(15),
age
number(5),
rank
varchar(10),
speciality
varchar(15),
pno
number
references projects
);
create table students
(
usn varchar(5),
name varchar(15),
program
varchar(15),
primary key(usn)
);
create table stud_workson_proj
(
prof_id
number
references professor,
pno
number
references projects,
usn
varchar(20)
references students
);
create table prof_workson_proj
(
prof_id
number
references professor,
pno
number
references projects
);
...................................INSERT INTO
TABLE..............................................
insert into projects values('101','UGC','2006','2028','70000');
insert into projects values('102','UG','1992','2026','60000');
insert into projects values('103','UG','2003','2024','50000');
insert into projects values('104','COLL','2008','2022','000');
insert into projects values('105','UFC','2010','2020','30000');
insert into projects values('106','ULC','2001','2018','20000');
insert into projects values('107','UML','2000','2016','65000');
insert into projects values('108','FLV','1999','2014','75000');
insert into projects values('109','DOC','2006','2012','78000');
insert into projects values('110','UGC','2004','2010','85000');
insert into professor
values('201','Abhishek','20','1200','database mgt','101');
insert into professor
values('202','Hrishikesh','21','1201','database','102');
insert into professor
values('203','Tejraj','22','1202','D.S','101');
insert into professor
values('204','Kokitkar','23','1203','Unix','103');
insert into professor
values('205','Kotrappa','24','1204','mysql','104');
insert into professor values('206','Govind','25','1205','Clang','105');
insert into professor
values('207','Swaroopa','26','1206','cpp','106');
insert into professor
values('208','Venogopal','27','1207','java','102');
insert into professor
values('209','Mithari','28','1208','O.S','105');
insert into professor values('210','Manoj','29','1209','system
s/w','103');
insert into professor values('211','Poonam','30','1300','visual
basic','101');
insert into professor
values('212','Amol','31','1301','fco','106');
insert into professor
values('213','Raghvendra','32','1302','DMS','108');
insert into professor
values('214','Narendra','33','1303','WEB','108');
insert into professor values('215','Sonia','34','1304','computer
n/w','105');
insert into students values('s01','Abhijeet','MCA');
insert into students values('s02','Abid','BCA');
insert into students values('s03','Akshata','BE');
insert into students values('s04','Ankita','MCA');
insert into students values('s05','Apurva','MCA');
insert into students values('s06','Anoop','MSC');
insert into students values('s07','Aparna','MS');
insert into students values('s08','Adavesh','MCA');
insert into students values('s10','Rajnikanth','BBA');
insert into students values('s11','Amit','MBA');
insert into students values('s12','Soumya','MCA');
insert into students values('s13','Amita','MCA');
insert into students values('s14','Apeksha','MCA');
insert into students values('s15','Akshay','BE');
insert into students values('s16','Chetana','BE');
insert into students values('s17','Maya','MCA');
insert into students values('s18','dhanraj','ARCH');
insert into students values('s19','Anant','MCA');
insert into students values('s20','kiran','BE');
insert into stud_workson_proj values('201','101','s01');
insert into stud_workson_proj values('201','102','s02');
insert into stud_workson_proj values('202','103','s03');
insert into stud_workson_proj values('203','101','s01');
insert into stud_workson_proj values('204','104','s01');
insert into stud_workson_proj values('205','103','s03');
insert into stud_workson_proj values('205','105','s04');
insert into stud_workson_proj values('206','104','s05');
insert into stud_workson_proj values('207','101','s06');
insert into stud_workson_proj values('209','102','s04');
insert into stud_workson_proj values('201','105','s03');
insert into stud_workson_proj values('201','106','s05');
insert into stud_workson_proj values('202','107','s07');
insert into stud_workson_proj values('203','103','s05');
insert into stud_workson_proj values('204','103','s05');
insert into stud_workson_proj values('206','105','s04');
insert into stud_workson_proj values('205','105','s06');
insert into stud_workson_proj values('207','101','s08');
insert into stud_workson_proj values('208','107','s14');
insert into stud_workson_proj values('208','103','s11');
insert into stud_workson_proj values('201','107','s11');
insert into stud_workson_proj values('201','103','s11');
insert into stud_workson_proj values('201','104','s11');
insert into stud_workson_proj values('201','108','s11');
insert into stud_workson_proj values('201','110','s11');
insert into prof_workson_proj values('201','101');
insert into prof_workson_proj values('202','101');
insert into prof_workson_proj values('203','101');
insert into prof_workson_proj values('204','102');
insert into prof_workson_proj values('205','103');
insert into prof_workson_proj values('206','103');
insert into prof_workson_proj values('207','102');
insert into prof_workson_proj values('208','108');
insert into prof_workson_proj values('209','108');
insert into prof_workson_proj values('210','102');
insert into prof_workson_proj values('211','104');
insert into prof_workson_proj values('212','101');
insert into prof_workson_proj values('215','106');
insert into prof_workson_proj values('214','106');
insert into prof_workson_proj values('215','108');
insert into prof_workson_proj values('204','108');
insert into prof_workson_proj values('205','104');
insert into prof_workson_proj values('206','108');
insert into prof_workson_proj values('203','109');
insert into prof_workson_proj values('206','110');
insert into prof_workson_proj values('207','101');
insert into prof_workson_proj values('201','109');
insert into prof_workson_proj values('201','102');
insert into prof_workson_proj values('201','103');
insert into prof_workson_proj values('201','104');
insert into prof_workson_proj values('201','105');
insert into prof_workson_proj values('201','106');
insert into prof_workson_proj values('201','107');
insert into prof_workson_proj values('201','108');
insert into prof_workson_proj values('201','110');
insert into prof_workson_proj values('202','102');
insert into prof_workson_proj values('202','103');
insert into prof_workson_proj values('202','104');
insert into prof_workson_proj values('202','105');
insert into prof_workson_proj values('202','106');
insert into prof_workson_proj values('202','107');
insert into prof_workson_proj values('202','108');
insert into prof_workson_proj values('202','109');
insert into prof_workson_proj values('202','110');
QUERIES:
1. select p.name
from professor p, projects pr
where p.pno=pr.pno AND budget<100000;
NAME
--------------------
Abhishek
Hrishikesh
Tejraj
Kokitkar
Kotrappa
Govind
Swaroopa
Venogopal
Mithare
Manoj
Poonam
NAME
--------------------
Amol
Raghvendra
Narendra
Sonia
15 rows selected.
2.
select s.name,p.name,a.sponsor
from students s, professor p, projects a, stud_workson_proj w
where s.usn=w.usn AND p.prof_id=w.prof_id AND a.pno=w.pno;
NAME
NAME
SPONSOR
-------------------- -------------------- --------------------
Akhilesh
Abhishek
UGC
Abid
Abhishek
UG
Akshata
Hrishikesh UG
Akhilesh
Tejraj
UGC
Akhilesh
Kokitkar
COLL
Akshata
Kotrappa
UG
Ankita
Kotrappa
UFC
Apurva
Govind
COLL
Anoop
Swaroopa
UGC
Ankita
Mithare
UG
Akshata
Abhishek
UFC
Apurva
Abhishek
ULC
Aparna
Hrishikesh UML
Apurva
Tejraj
UG
Apurva
Kokitkar
UG
Ankita
Govind
UFC
Anoop
Kotrappa
UFC
Adavesh
Swaroopa
UGC
Amit
Venogopal UG
Apeksha
Venogopal UML
20 rows selected.
3.
select p.name, sum(budget)
from professor p, projects a
where p.pno=a.pno AND a.st_date>2000 AND a.end_date=2018
group by p.name;
NAME
SUM(BUDGET)
-------------------- -----------
Amol
20000
Swaroopa
20000
4.
select p.name
from professor p, projects a
where p.pno=a.pno AND
BUDGET>(select avg(budget)
from projects);
NAME
--------------------
Abhishek
Hrishikesh
Tejraj
Venogopal
Poonam
Raghvendra
Narendra
7 rows selected.select *
5.
select p.name
from professor p
where p.prof_id in(select prof_id
from prof_workson_proj
group by prof_id
having count(*)=(select count(*)
from projects));
NAME
--------------------
Hrishikesh
No comments:
Post a Comment