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:

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

ANS:

6)

select firstname,lastname from students lastname IN(select lastname from professor);

7)

select lastname from students where lastname like M% and year_in_school=3;

8)

select grades from enrolled where enrolled.class_offered_id=CS 4660;

9)

(select name from major where major_id IN (select major_id from students group by major_id having count (major_id)>5);

10)

select firstname,lastname from professor where professor_id IN (select professor_id from from schedule where class_id IN (select class_id from major_classes where class_id IN (select id from class where name="computer science")));

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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