please help me solve these queries DROP TABLE IF EXISTS enrolled; DROP TABLE IF

Question

please help me solve these queries

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)
);

bb.csue astbay.edu 011 Apple Disney ESPN Yahoo! For each of the following, figure out the SQL needed to complete the task. Please mention just ONE SQL statement as your answer. Multiple answers will be given a zero for that question The questions are not necessarily in order of difficulty, although the ones towards the end tend to be more difficult. Questions: For the sample DDL provided, 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)

Explanation / Answer

hidDROP TABL IF EXISTS enrolled;
DROP TABL IF EXISTS schedule;
DROP TABL IF EXISTS class;
DROP TABL IF EXISTS student;
DROP TABL IF EXISTS professor;
DROP TABL IF EXISTS major;
DROP TABL IF EXISTS major_classes;
CREATE TABL class (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL UNIQUE,
PRIMARY KEY (id)
);
CREATE TABL major (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL UNIQUE,
PRIMARY KEY (id)
);
CREATE TABL professor (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABL 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 TABL 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 TABL 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 TABL 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)
);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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