DBMS Prac7

CREATE DATABASE prac7

CREATE TABLE PROFESSOR (SSN INT PRIMARY KEY, name VARCHAR(20), age INT, research VARCHAR(20), rnk VARCHAR(20))

CREATE TABLE PROJECT (p_no INT PRIMARY KEY, spon_no INT, starting DATE, ending DATE, budget INT, prin_inv INT FOREIGN KEY REFERENCES PROFESSOR)

CREATE TABLE DEPARTMENT (dept_no INT PRIMARY KEY, dname VARCHAR(20), main_office VARCHAR(20), chairman INT FOREIGN KEY REFERENCES PROFESSOR)

CREATE TABLE PROF_WORKS_ON_PROJ (prof INT FOREIGN KEY REFERENCES PROFESSOR, proj INT FOREIGN KEY REFERENCES PROJECT, CONSTRAINT pwppk PRIMARY KEY (prof, proj))

CREATE TABLE prec_time_given (prof INT FOREIGN KEY REFERENCES PROFESSOR, dept_no INT FOREIGN KEY REFERENCES DEPARTMENT, time_per INT, CONSTRAINT ptgpk PRIMARY KEY (prof, dept_no) )

CREATE TABLE STUDENT (SSN INT PRIMARY KEY, name VARCHAR(20), age INT, deg VARCHAR(20), majors_in INT FOREIGN KEY REFERENCES DEPARTMENT)

CREATE TABLE STU_PROJ_PROF (stu INT FOREIGN KEY REFERENCES STUDENT, proj INT FOREIGN KEY REFERENCES PROJECT, prof INT FOREIGN KEY REFERENCES PROFESSOR, CONSTRAINT spppk PRIMARY KEY(stu, proj))


-- Questions. I had some doubts while completing it, there can be few mistakes.
-- 1.    Details of faculty who is specialized in ML
SELECT * FROM PROFESSOR p WHERE research = 'ML'

-- 2.    Details of the project with budget more than 2 crores
SELECT * FROM PROJECT p WHERE budget > 15000

-- 3.    Details of the professors who are managing the project alpha
(SELECT * FROM PROFESSOR p WHERE SSN IN (SELECT prof FROM PROF_WORKS_ON_PROJ pwop WHERE p_no = (SELECT p_no FROM PROJECT p2 WHERE pname = 'Alpha'))


-- 4.    Details of the professors who are working with alpha project.
SELECT * FROM PROFESSOR p WHERE SSN IN (SELECT prof FROM PROF_WORKS_ON_PROJ pwop WHERE p_no = (SELECT p_no FROM PROJECT p2 WHERE pname = 'Alpha'))

-- 5.    Details of the faculty who is managing more than 1 project.
SELECT * FROM PROFESSOR p WHERE SSN IN (SELECT prin_inv FROM PROJECT p2 GROUP BY prin_inv HAVING COUNT(*) > 1 )

-- 6.    Details of the faculty who is not managing more than 1 project.
SELECT * FROM PROFESSOR p WHERE SSN IN (SELECT prin_inv FROM PROJECT p2 GROUP BY prin_inv HAVING COUNT(*) = 1)
UNION
SELECT * FROM PROFESSOR p WHERE SSN NOT IN (SELECT prin_inv FROM PROJECT p2 GROUP BY prin_inv)

-- 7.    Details of the student  who is working on beta project.
SELECT * FROM STUDENT s WHERE SSN = (SELECT stu FROM STU_PROJ_PROF spp WHERE p_no = (SELECT p_no FROM PROJECT p WHERE pname = 'Beta'))

-- 8.    Display projectno ,student name and professor(supervisor) of all the projects.
SELECT s.name, p2.name, spp.p_no FROM STU_PROJ_PROF spp, STUDENT s, PROFESSOR p2 WHERE s.SSN = spp.stu AND p2.SSN = spp.prof

-- 9.    Details of the students  who are working in  atleast one project.
SELECT * FROM STUDENT s WHERE SSN IN (SELECT stu FROM STU_PROJ_PROF spp)

-- 10.    Display professor_no , dept name  and percentage of time allotted to each department.
SELECT ptg.prof, d.dname ,ptg.time_per FROM per_time_given ptg, DEPARTMENT d WHERE ptg.dept_no = d.dept_no

-- 11.    Details of the students  who are not associated with any project.
SELECT * FROM STUDENT s WHERE SSN NOT IN (SELECT stu FROM STU_PROJ_PROF spp)

-- 12.    Display projectno, no of professors working on each project.
SELECT p_no, COUNT(*) FROM PROF_WORKS_ON_PROJ pwop GROUP BY p_no

-- 13.    Display the details of the faculty who is working on maximum number of projects.
SELECT * FROM PROFESSOR p2 WHERE SSN IN (SELECT prof FROM PROF_WORKS_ON_PROJ pwop2 GROUP BY prof
    HAVING COUNT(*) = (SELECT MAX(y.cnt) FROM ( SELECT prof, COUNT(*) 'cnt' FROM PROF_WORKS_ON_PROJ pwop
        GROUP BY prof) AS y))

-- 14.    Display the name of the graduate  student  and  name of his  corresponding senior graduate student.
SELECT s.name 'JUN', s2.name 'SEN' FROM STUDENT s, STUDENT s2 WHERE s.majors_in = s2.majors_in AND s.age < s2.age

-- 15.    Details of the project where minimum no of professors are working on it.
SELECT * FROM PROJECT p WHERE p_no IN
    (SELECT p_no FROM PROF_WORKS_ON_PROJ pwop GROUP BY p_no HAVING COUNT(*) =
        (SELECT MAX(y.cnt) FROM (SELECT COUNT(*) 'cnt' FROM PROF_WORKS_ON_PROJ pwop2 GROUP BY p_no) AS y ))

-- 16.    Details of the project where no student is involved.
SELECT * FROM PROJECT p WHERE p_no NOT IN (SELECT p_no FROM STU_PROJ_PROF spp)

-- 17.     Find the name of the youngest professor.
SELECT name FROM PROFESSOR p WHERE age = (SELECT MIN(age) FROM PROFESSOR p2)

-- 18.    Display projectno , project guide  where budget is  between 10000 and 200000 and has commenced in the year 2016.
SELECT p_no FROM PROJECT p WHERE budget BETWEEN 10000 AND 20000 AND YEAR(starting) = 2020