Table creation:
create database mydb7;
use mydb7;
CREATE TABLE Student (
snum INTEGER PRIMARY KEY,
sname VARCHAR(50),
major VARCHAR(50),
level VARCHAR(10),
age INTEGER
);
CREATE TABLE Class (
name VARCHAR(50),
meets_at TIME,
room VARCHAR(10),
fid INTEGER
);
CREATE TABLE Enrolled (
snum INTEGER,
cname VARCHAR(50),
PRIMARY KEY (snum, cname)
-- FOREIGN KEY (snum) REFERENCES Student(snum),
-- FOREIGN KEY (cname) REFERENCES Class(name)
);
CREATE TABLE Faculty (
fid INTEGER PRIMARY KEY,
fname VARCHAR(50),
deptid INTEGER
);
INSERT INTO Student (snum, sname, major, level, age)
VALUES
(1001, 'Alice Smith', 'Computer Science', 'Senior', 22),
(1002, 'Bob Johnson', 'Mathematics', 'Junior', 20),
(1003, 'Charlie Brown', 'Computer Science', 'Sophomore', 19),
(1004, 'Diana Ross', 'Physics', 'Senior', 23);
INSERT INTO Class (name, meets_at, room, fid)
VALUES
('CS101', '[Link]', 'A101', 1),
('MATH201', '[Link]', 'B202', 2),
('PHYS301', '[Link]', 'C303', 3);
INSERT INTO Enrolled (snum, cname)
VALUES
(1001, 'CS101'),
(1002, 'MATH201'),
(1003, 'CS101'),
(1004, 'PHYS301');
INSERT INTO Faculty (fid, fname, deptid)
VALUES
(1, 'Dr. John Doe', 1),
(2, 'Dr. Jane Smith', 2),
(3, 'Dr. Emily Davis', 3);
1.
Code:
SELECT DISTINCT [Link]
FROM Student s
JOIN Enrolled e ON [Link] = [Link]
JOIN Class c ON [Link] = [Link]
JOIN Faculty f ON [Link] = [Link]
WHERE [Link] = 'Junior' AND [Link] = 'Prof. Z';
Output:
2.
Code:
SELECT MAX([Link])
FROM Student s
LEFT JOIN Enrolled e ON [Link] = [Link]
LEFT JOIN Class c ON [Link] = [Link]
LEFT JOIN Faculty f ON [Link] = [Link]
WHERE [Link] = 'History' OR [Link] = 'Prof. Z';
Output:
3.
Code:
SELECT MAX([Link])
FROM Student s
LEFT JOIN Enrolled e ON [Link] = [Link]
LEFT JOIN Class c ON [Link] = [Link]
LEFT JOIN Faculty f ON [Link] = [Link]
WHERE [Link] = 'History' OR [Link] = 'Prof. Z';
Output:
4.
Code:
SELECT DISTINCT [Link]
FROM Student s
JOIN Enrolled e1 ON [Link] = [Link]
JOIN Class c1 ON [Link] = [Link]
JOIN Enrolled e2 ON [Link] = [Link]
JOIN Class c2 ON [Link] = [Link]
WHERE c1.meets_at = c2.meets_at AND [Link] != [Link];
Output:
5.
Code:
SELECT [Link]
FROM Faculty f
JOIN Class c ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT(DISTINCT [Link]) = (SELECT COUNT(DISTINCT room) FROM Class);
Output:
