Given DDL Script : 1. CREATE TABLE PROFESSOR(FACULTYID NUMBER PRIMARY KEY, FIRST

Question

Given DDL Script :

1. CREATE TABLE PROFESSOR(FACULTYID NUMBER PRIMARY KEY, FIRSTNAME VARCHAR(20),LASTNAME VARCHAR(20));
2. CREATE TABLE MAJOR(MAJORNAME VARCHAR (10),MAJORID NUMBER PRIMARY KEY);
3. CREATE TABLE STUDENT(STUDENTID NUMBER PRIMARY KEY,FIRSTNAME VARCHAR(10),LASTNAME VARCHAR (10),YEAR_IN_SCHOOL NUMBER(5),MAJORID VARCHAR(10) FOREIGN KEY REFERENCES MAJOR(MAJORID));
4. CREATE TABLE CLASS(CLASSID NUMBER PRIMARY KEY,CLASSNAME VARCHAR(20));
5. CREATE TABLE ENROLL(STUDENTID NUMBER FOREIGN KEY REFERENCES STUDENT(STUDENTID),CLASSID NUMBER FOREIGN KEY REFERENCES CLASS(CLASSID),GRADE VARCHAR(2));

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

1) The names of all classes that could be taught

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

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

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

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

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

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
10) The professors that teach classes for the "Computer Science" major

Explanation / Answer

FROM CLASS;

FROM STUDENT

ORDER BY LASTNAME;

FROM STUDENT

WHERE FIRSTNAME LIKE ‘M%’ AND =3;

FROM STUDENT

WHERE STUDENT.LASTNAME=PROFESSOR.LASTNAME;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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