Question
CREATE TABLE students( sid char(10), name varchar, age int, gpa float, PRIMARY KEY(sid)); CREATE TABLE courses( cid varchar(7), deptid varchar(10), name varchar(20), PRIMARY KEY(cid)); CREATE
CREATE TABLE students(
sid char(10),
name varchar,
age int,
gpa float,
PRIMARY KEY(sid));
CREATE TABLE courses(
cid varchar(7),
deptid varchar(10),
name varchar(20),
PRIMARY KEY(cid));
CREATE TABLE professors(
ssn int,
name varchar(20),
address varchar(30),
phone varchar(10),
deptid varchar(10),
PRIMARY KEY(ssn));
CREATE TABLE enrollment(
cid varchar(7),
sid char(10),
section int,
grade varchar(3),
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) references students,
FOREIGN KEY (cid) references courses,
FOREIGN KEY (cid,section) references teaches);
CREATE TABLE teaches(
cid varchar(7),
section int,
ssn int,
PRIMARY KEY(cid,section),
FOREIGN KEY (cid) references courses,
FOREIGN KEY (ssn) references professors);
In SQL, write queries for:
9. List of professors that work for departments with more than 20 faculty members and that offer more large sections than small and medium sections combined.
10. Assume grades are A, B, C, D, F where D and F are failing grades. For each course (section) find the percentage of students that failed the course.
11. Find the name of the professor with the maximum percentage of students that failed his course.
12. On average what percentage of students fail a course? (total number of students that failed a course / total number of enrolled students).
13. Find a list of courses (sections) where the percentage of students with D or F is greater than average.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started