Use mysq to slove the problem: Given the following tables: o students(sid,name,a
ID: 3601711 • Letter: U
Question
Use mysq to slove the problem:
Given the following tables: o students(sid,name,age,gpa) e courses(cid,deptid, name) . professors(ssn,name,address,phone,deptid) * enrollment (sid.cid,section,grade, foreign key (sid) references students, foreign key (cid) references courses foreign key (cid,section) references teaches) . teaches(cid,section,ssn, foreign key (cid) references courses foreign key (ssn) references professors) Domain . cid is in {'198:11',640:151','198:112'....] . deptid is in 'cs','math'music,.. . grade is in ['A','B','('... » section, age, ssn are an integers . address, phone, name are strings . gpa is floatExplanation / Answer
1)
# Creating a database named college.
CREATE DATABASE College;
# Using the college database
USE College;
# Creating table students with sid as a primary key.
CREATE TABLE students
(
sid VARCHAR(10),
name VARCHAR(20),
age INTEGER,
gpa FLOAT,
PRIMARY KEY (sid)
);
# Creating table courses with cid as primary key.
CREATE TABLE courses
(
cid VARCHAR(10),
deptid VARCHAR(15),
name VARCHAR(20),
PRIMARY KEY (cid)
);
# Creating table professors with ssn as primary key.
CREATE TABLE professors
(
ssn INTEGER,
name VARCHAR(20),
address VARCHAR(30),
phone VARCHAR(10),
deptid VARCHAR(15),
PRIMARY KEY (ssn)
);
# Creating table enrollment with sid, cid as primary key. (sid), (cid), (cid,section) as foreign key.
CREATE TABLE enrollment
(
sid VARCHAR(10),
cid VARCHAR(10),
section INTEGER,
grade CHAR(2),
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES students,
FOREIGN KEY (cid) REFERENCES courses,
FOREIGN KEY (cid,section) REFERENCES teaches
);
# Creating table teaches with (cid,section) as primary key. (cid), (ssn) as foreign key referring to courses and professors table respectively.
CREATE TABLE teaches
(
cid VARCHAR(10),
section INTEGER,
ssn INTEGER,
PRIMARY KEY (cid, section),
FOREIGN KEY (cid) REFERENCES courses,
FOREIGN KEY (ssn) REFERENCES professors
);
2)
# Selecting name from professor table where departmant id is 'cs'.
SELECT name
FROM professors
WHERE deptid = ‘cs’ ;
3)
# Selecting student id (sid) who are enrolled in 'cs' department. Join between students, courses and enrollment table is done.
SELECT s.sid
FROM students AS s INNER JOIN enrollment AS e
ON s.sid = e.sid
INNER JOIN courses AS c
ON e.cid = c.cid
WHERE c.deptid = ‘cs’ ;
4)
# Selecting ssn, professor name who belongs to 'cs' department but do not teach any 'cs' courses.
SELECT p.ssn, p.name
FROM professors AS p
INNER JOIN teaches AS t
ON p.ssn = t.ssn
INNER JOIN courses AS c
ON c.cid = t.cid
WHERE p.deptid = ‘cs’ AND c.deptid <> ‘cs’ ;
5)
# Selecting department and number of courses running in those department. Group by clause is used for aggregation count.
SELECT deptid, COUNT(cid)
FROM courses
GROUP BY deptid;
6)
# Selecting department who run more than 10 courses. Group by and having clause is used for selecting course count greater than 10.
SELECT deptid, COUNT(cid)
FROM courses
GROUP BY deptid
HAVING COUNT(cid) > 10 ;
7)
# Selecting distinct student name who are taught by professors whose name start with 'M'. In where clause like keyword is used to get the professors name that starts with 'M%'.
SELECT DISTINCT s.name
FROM students AS s
INNER JOIN enrollment AS e
ON e.sid = s.sid
INNER JOIN teaches AS t
t.cid = e.cid
INNER JOIN professors AS p
ON p.ssn = t.ssn
WHERE p.name LIKE ‘M%’ ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.