CREATE TABLE Professor (
SSN VARCHAR(11) PRIMARY KEY, -- Unique identifier for a professor
Name VARCHAR(50) NOT NULL,
Age INTEGER,
Rank VARCHAR(10),
Research_Specialty VARCHAR(50)
);
CREATE TABLE Project (
Project_Number INTEGER PRIMARY KEY,
Sponsor_Name VARCHAR(50) NOT NULL,
Start_Date DATE,
End_Date DATE,
Budget DECIMAL(10,2),
PI_SSN VARCHAR(11) REFERENCES Professor(SSN) -- Foreign key to Professor
);
CREATE TABLE Graduate_Student (
SSN VARCHAR(11) PRIMARY KEY, -- Unique identifier for a student
Name VARCHAR(50) NOT NULL,
Age INTEGER,
Degree_Program VARCHAR(10)
);
CREATE TABLE Department (
Department_Number INTEGER PRIMARY KEY,
Dept_Name VARCHAR(50) NOT NULL,
Main_Office VARCHAR(50),
Chairman_SSN VARCHAR(11) REFERENCES Professor(SSN) -- Foreign key to
Professor
);
CREATE TABLE Works_On (
SSN VARCHAR(11) REFERENCES Professor(SSN),
Project_Number INTEGER REFERENCES Project(Project_Number),
PRIMARY KEY (SSN, Project_Number) -- Composite primary key
);
CREATE TABLE Works_On (
SSN VARCHAR(11) REFERENCES Graduate_Student(SSN),
Project_Number INTEGER REFERENCES Project(Project_Number),
PRIMARY KEY (SSN, Project_Number) -- Composite primary key
);
CREATE TABLE Supervised_By (
Student_SSN VARCHAR(11) REFERENCES Graduate_Student(SSN),
Supervisor_SSN VARCHAR(11) REFERENCES Professor(SSN),
PRIMARY KEY (Student_SSN, Supervisor_SSN) -- Composite primary key
);
CREATE TABLE Works_In (
SSN VARCHAR(11) REFERENCES Professor(SSN),
Department_Number INTEGER REFERENCES Department(Department_Number),
Time_Percentage DECIMAL(3,2), -- Percentage of time spent in the
department
PRIMARY KEY (SSN, Department_Number) -- Composite primary key
);
CREATE TABLE Major_Department (
SSN VARCHAR(11) REFERENCES Graduate_Student(SSN),
Department_Number INTEGER REFERENCES Department(Department_Number),
PRIMARY KEY (SSN) -- Foreign key is also the primary key
);
CREATE TABLE Student_Advisor (
Student_SSN VARCHAR(11) REFERENCES Graduate_Student(SSN),
Advisor_SSN VARCHAR(11) REFERENCES Graduate_Student(SSN),
PRIMARY KEY (Student_SSN), -- Foreign key is also the primary key
FOREIGN KEY (Advisor_SSN) REFERENCES Graduate_Student(SSN) -- Ensure
advisor exists
);
1. Display the list of Professors in the University:
SQL
SELECT * FROM Professor;
2. Display the details of the projects undertaken by the professors:
SQL
SELECT P.Project_Number, P.Sponsor_Name, P.Start_Date, P.End_Date,
[Link], [Link] AS Professor_Name
FROM Project P
INNER JOIN Works_On W ON P.Project_Number = W.Project_Number
INNER JOIN Professor Prof ON [Link] = [Link];
3. Display the number of projects being undertaken by each Professor:
SQL
SELECT [Link], [Link], COUNT(*) AS Number_Of_Projects
FROM Professor Prof
INNER JOIN Works_On W ON [Link] = [Link]
GROUP BY [Link], [Link];
4. Display the students working on each project:
SQL
SELECT P.Project_Number, P.Sponsor_Name, [Link] AS Student_Name
FROM Project P
INNER JOIN Works_On W ON P.Project_Number = W.Project_Number
INNER JOIN Graduate_Student GS ON [Link] = [Link];
5. Display the list of students and their project supervisors:
SQL
SELECT [Link] AS Student_Name, [Link] AS Supervisor_Name
FROM Graduate_Student GS
INNER JOIN Works_On W ON [Link] = [Link]
INNER JOIN Professor Prof ON W.Project_Number =
Prof.Manages_Project_Number;
6. Display details of students working on more than one project and their
supervisors:
SQL
SELECT [Link] AS Student_Name, [Link] AS Supervisor_Name,
COUNT(DISTINCT P.Project_Number) AS Projects_Worked_On
FROM Graduate_Student GS
INNER JOIN Works_On W ON [Link] = [Link]
INNER JOIN Professor Prof ON W.Project_Number =
Prof.Manages_Project_Number
GROUP BY [Link], [Link]
HAVING COUNT(DISTINCT P.Project_Number) > 1;
7. Display the list of Professors who are chairman of some department:
SQL
SELECT [Link], Dept.Dept_Name
FROM Professor Prof
INNER JOIN Department Dept ON [Link] = Dept.Chairman_SSN;
8. Display the students and their major department:
SQL
SELECT [Link] AS Student_Name, Dept.Dept_Name
FROM Graduate_Student GS
INNER JOIN Department Dept ON [Link] = Dept.Major_Department_SSN;
9. Display the list of students and their senior advisors:
SQL
SELECT [Link] AS Student_Name, [Link] AS Senior_Advisor_Name
FROM Graduate_Student GS1
INNER JOIN Graduate_Student GS2 ON GS1.Senior_Advisor_SSN = [Link];
10. Display the list of students who have the same senior advisor:
SQL
SELECT [Link] AS Student_Name, COUNT(*) AS Number_Of_Mentees
FROM Graduate_Student GS1
INNER JOIN Graduate_Student GS2 ON GS1.Senior_Advisor_SSN = [Link]
GROUP BY GS1.Senior_Advisor_SSN, [Link]
HAVING COUNT(*) > 1;
11. Display the number of student mentees with each project showing the one
with maximum mentees on top:
SQL
SELECT P.Project_Number, P.Sponsor_Name, COUNT(DISTINCT [Link]) AS
Number_Of_Mentees
FROM Project P
INNER JOIN Works_On W ON P.Project_Number = W.Project_Number
INNER JOIN Graduate_Student GS ON [Link] = [Link]
GROUP BY P.Project_Number, P.Sponsor_Name
ORDER BY Number_Of_Mentees DESC;
12. Display the list of students working on the same projects:
SQL
WITH StudentProjects AS (
SELECT SSN, Project_Number
FROM Works_On
)
SELECT [Link], [Link] AS Student_Name1, [Link] AS Student_Name2
FROM StudentProjects SP1
INNER JOIN StudentProjects SP2 ON SP1.Project_Number =
SP2.Project_Number
WHERE [Link] <> [Link];
13. Display the list of students and professors in Marketing and Finance
departments:
SQL
SELECT [Link] AS Student_Name, [Link] AS Professor
Here are the SQL queries for the additional questions you requested:
14. Display the projects that will be completed in 2021:
SQL
SELECT P.Project_Number, P.Sponsor_Name, P.Start_Date, P.End_Date,
[Link]
FROM Project P
WHERE YEAR(P.End_Date) = 2021;
This query selects all projects where the End_Date year is 2021.
15. Display the most profitable project along with their sponsor and the
associated professor(s) and students:
SQL
WITH MostProfitableProject AS (
SELECT Project_Number, MAX(Budget) AS MaxBudget
FROM Project
)
SELECT P.Project_Number, P.Sponsor_Name, [Link] AS PI_Name, [Link]
AS Student_Name
FROM Project P
INNER JOIN MostProfitableProject MP ON P.Project_Number =
MP.Project_Number
INNER JOIN Professor Prof ON P.PI_SSN = [Link]
INNER JOIN Works_On WO ON P.Project_Number = WO.Project_Number
INNER JOIN Graduate_Student GS ON [Link] = [Link]
GROUP BY P.Project_Number, P.Sponsor_Name, [Link], [Link];
16. Display the number of projects currently running, the most profitable and
the least profitable projects:
SQL
WITH CurrentProjects AS (
SELECT Project_Number
FROM Project
WHERE CURDATE() >= Start_Date AND CURDATE() <= End_Date
)
SELECT (SELECT COUNT(*) FROM CurrentProjects) AS
Current_Projects_Count,
P1.Project_Number AS Most_Profitable_Number, P1.Sponsor_Name AS
Most_Profitable_Sponsor,
P2.Project_Number AS Least_Profitable_Number, P2.Sponsor_Name AS
Least_Profitable_Sponsor
FROM Project P1
INNER JOIN (
SELECT Project_Number, MAX(Budget) AS MaxBudget
FROM Project
GROUP BY Project_Number
) AS MostProfitable ON P1.Project_Number =
MostProfitable.Project_Number
INNER JOIN (
SELECT Project_Number, MIN(Budget) AS MinBudget
FROM Project
GROUP BY Project_Number
) AS LeastProfitable ON P2.Project_Number =
LeastProfitable.Project_Number
INNER JOIN Project P2 ON P2.Project_Number =
LeastProfitable.Project_Number;
17. List the projects whose budget is more than the average project budget:
SQL
WITH AvgBudget AS (
SELECT AVG(Budget) AS AverageBudget
FROM Project
)
SELECT P.Project_Number, P.Sponsor_Name, [Link]
FROM Project P
INNER JOIN AvgBudget AB ON [Link] > [Link];
18. Display the details of department whose professors have the maximum
projects:
SQL
WITH ProfessorProjects AS (
SELECT [Link], Dept.Dept_Name, COUNT(*) AS Project_Count
FROM Professor Prof
INNER JOIN Works_On WO ON [Link] = [Link]
INNER JOIN Department Dept ON [Link] = Dept.Chairman_SSN
GROUP BY [Link], Dept.Dept_Name
)
SELECT *
FROM ProfessorProjects
WHERE Project_Count = (
SELECT MAX(Project_Count)
FROM ProfessorProjects
);
19. Display the details of Marketing and Finance Projects (Assuming no
subject area field):
SQL
SELECT P.Project_Number, P.Sponsor_Name, P.Start_Date, P.End_Date,
[Link]
FROM Project P
WHERE P.Project_Number IN (
SELECT Project_Number
FROM Project
WHERE [Link] LIKE '%Marketing%' OR [Link] LIKE '%Marketing%'
UNION ALL
SELECT Project_Number
FROM Project
WHERE [Link] LIKE '%Finance%' OR [Link] LIKE '%Finance%'
);
20. Display the list of students who are not assigned any projects or working
on 2 projects:
SQL
WITH StudentProjects AS (
SELECT SSN, COUNT(*) AS Project_Count
FROM Works_On
GROUP BY SSN
)
SELECT [Link], [Link]
FROM Graduate_Student GS
LEFT JOIN StudentProjects SP ON [Link] = [Link]
WHERE SP.Project_Count IS NULL OR SP.Project_Count = 2;