DROP TABLE IF EXISTS enrolled DROP TABLE IF EXISTS achedule DROP TABLE IF EXISTS
ID: 2246498 • Letter: D
Question
DROP TABLE IF EXISTS enrolled DROP TABLE IF EXISTS achedule DROP TABLE IF EXISTS Class DROP TABLE IF EXISTS atudent: DROP TABLE IF EXISTS professori DROP TABLE IFEXISTS majora DROP TABLE IF EXISTS najor_classes CREATE TABLE class id int (11) NOT NULL AUTO INCRENENT sane varchar (45) NOT NULL CREATE TABLE majo id int (11) NOT NULL AUTO INCREENT nane varchar (45) NOT NULL UNT CCE CREATE TABLE professor id int (31) NOT NULL AUTO INCRENT flest nane varchar (45) NOT NULL last nane varchar (45) NOT ULL PRtMARYKEY 41d) CREATE TABLE student id int (11) NOT NULL AUTO INCREMENT first name varchar (45) NOT NUL laat nane vazchar (45) NOT NULL year in school int (11) major id int (11) PRIMARY KEY (Ld) CREATE TABLE ajoeelasses id int (1) NOT NLL AUTO INCRENENT ajor id Lat (11) class id int (11) EMARY KEY (d). OREIGN KEY najor id) REFERENCEs majorLd) CREATE TABLE Schedule id int (11) NOT NULLAUTO INCREMENT. elass id Lnt (11) NOT NULL professor id inti11) NOT NULL quar ter int (11) NOT LL, PRIMARY KEY (d) OREIGN KEY elass id) REFERENCES class Ld) POREIGN KEY (professor id) REFERENCES proessor (id) CREATE TABLE enEolled d intii) NOT NULL AUTO TINCRENENT. student ld Int (11) class oftered id inta) grade char (2) PRIMARY KEY d) ORE TON KEY (student id) REFERENCES student id) OREG EY (class offered id) REFERENCES schedale d 2Explanation / Answer
I will be using capitals for SQL keywords and lowercase for the variables used in you DDL. SQL is completely case insensitive so there will never be any sort of problem because of the case.
1. SELECT name FROM class;- This is a simple SELECT FROM statement where it selects the specified attribute from a class where a table without any condition
2. SELECT c.name FROM (class AS c JOIN schedule AS s ON c.id=s.class_id) where quarter=51;- This is also a very simple query where we need to do join on schedule and class because scedule contains the quarter and class contains the name so the name is selected from class on where the id matches the class_id where quarter is 51.
3. SELECT DISTINCT last_name FROM student ORDER BY last_name DESC;- This selects the distinct last names of students without any condition and orders them in descing order.
4. SELECT last_name FROM professor AS p JOIN schedule AS s ON p.id=s.professor_id GROUP BY last_name ORDER BY COUNT(*);- This will select the last name of professors where it ordered by the count of the courses that are taught, here popularity is judged by the number of courses they teach.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.