WRITE Queries Questions: For the sample DDL provided BELOW , provide the SQL tha

Question

WRITE Queries

Questions: For the sample DDL provided BELOW , provide the SQL that will return:

1) The names of all classes that could be taught (1 pt)

2) The names of classes taught in quarter 51 (1 pt)
3) The unique set of last names of all students, in ascending order. (1 pt)

4) The most common professor last names, ordered in terms of popularity (1 pt)

5) Classes for the "Computer Science" major that are offered in quarter 75 (1 pt)

6) The first and last names of all students that have the same last name as a professor (2 pts)

7) The last names (duplicates allowed) of all students with a first name that starts with an "M" and is 3rd year in school (2 pts)

8) The grades for all students that took a class named "CS 4660", taught by Greg Barish. (2 pts) 9) The set of majors that have been chosen by at least 5 students. (2 pts)
10) The professors that teach classes for the "Computer Science" major (2 pts)

- -- -- - - - -- - - - - - - -- - - -- - - - -- - - -- -- - - - -- - - -- - -- - - -- - - - - - - - -- - -

DROP TABLE IF EXISTS enrolled;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS class;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS professor;
DROP TABLE IF EXISTS major;
DROP TABLE IF EXISTS major_classes;

CREATE TABLE class (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL UNIQUE,
PRIMARY KEY (id)
);

CREATE TABLE major (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL UNIQUE,
PRIMARY KEY (id)
);

CREATE TABLE professor (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
year_in_school int(11),
major_id int(11),
PRIMARY KEY (id),
FOREIGN KEY (major_id) REFERENCES major (id)
);

CREATE TABLE major_classes (
id int(11) NOT NULL AUTO_INCREMENT,
major_id int(11),
class_id int(11),
PRIMARY KEY (id),
FOREIGN KEY (major_id) REFERENCES major (id),
FOREIGN KEY (class_id) REFERENCES class (id)
);

CREATE TABLE schedule (
id int(11) NOT NULL AUTO_INCREMENT,
class_id int(11) NOT NULL,
professor_id int(11) NOT NULL,
quarter int(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (class_id) REFERENCES class (id),
FOREIGN KEY (professor_id) REFERENCES professor (id)
);

CREATE TABLE enrolled (
id int(11) NOT NULL AUTO_INCREMENT,
student_id int(11),
class_offered_id int(11),
grade char(2),
PRIMARY KEY (id),
FOREIGN KEY (student_id) REFERENCES student (id),
FOREIGN KEY (class_offered_id) REFERENCES schedule (id)
);

Explanation / Answer

Answering your first 5 queries,

1) The names of all classes that could be taught (1 pt)

select name from class;

2) The names of classes taught in quarter 51 (1 pt)

select class.name

from class, schedule

where class.id = schedule.class_id

and schedule.quarter = 51;

3) The unique set of last names of all students, in ascending order. (1 pt)

select distinct last_name from student order by last_name desc;

4) The most common professor last names, ordered in terms of popularity (1 pt)

select first_name, last_name

from professor

where id = (

select professor_id

from schedule

group by professor_id

order by COUNT(*) DESC

limit 1;

);

5) Classes for the "Computer Science" major that are offered in quarter 75 (1 pt)

select class.name

from class, schedule

where class.id = schedule.class_id

and schedule.quarter = 75

and class.id in (

select major_classes.class_id

from major_classes, major

where major.name = "Computer Science"

and major.id = major_classes.major_id

);

As it is a quite long question, i request you to please the questions in a subparts of 4 questions in a thread. That way we are able to explain the answer more clearly and that also fits in the time limit given to us.

Thanks!

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.