Question

Please write ONE SQL statement for each of the following tasks using the below tables. Note that you can only use conditions specified in the task description and cannot manually look up data and add conditions.

Task 1: return title of textbooks with price over $100.

Task 2: return number of courses sections scheduled for each year and semester. Please return year, semester, and number of courses.

Task 3: Return names of all courses in Information Systems undergraduate program.

Task 4: return titles and prices of textbooks for IS 420 section 1 in spring 2019.

Task 5: return names of courses offered in 2019 spring and have at least 2 sections.

Task 6: for each text book, return the title of the text book and total number of students registered for courses using that text book in spring 2019.

The list of tables are:

Program table:

pid, --- program id

pname, --- program name

ptype --- program type (1: undergraduate, 2: graduate)

Teacher table:

tid, --- teacher id

tname --- teacher name

Author table:

aid, --- author id

aname --- author name

Textbook table:

tbid, --- text book id

title, ---- title of book

publisher, --- publisher name

edition, --- edition, e.g., 3 means 3rd edition

quantity, --- number of copies in store

price --- price of the book

Course table:

cid, --- course id

cname, --- course name

pid --- program id

Schedule table: (scheduled course sections)

sid, --- schedule id, unique for each scheduled class section

cid, --- class id

snumber, --- section number, e.g., 2 means section 2 of the class

semester, --- spring, fall, summer, winter,

year, --- year of the class

tid, --- teach id who teaches this section

num_registered --- number of registered students for this class section

Textbook_author table:

aid, --- author id

tbid --- text book id

Textbook_schedule table:

sid, --- schedule id

tbid --- text book id

Answer 1

Task1:
SELECT title FROM Textbook WHERE price > 1400;

Task2:
SELECT Schedule.year,Schedule.semester,COUNT(Course.cid) AS SemCount FROM Schedule
INNER JOIN Course ON Course.cid = Schedule.cid GROUP BY Schedule.year,Schedule.semester;

Task3:
SELECT cname FROM Course INNER JOIN Program ON Course.pid = Program.pid WHERE Program.pname = "Information Systems undergraduate program";

Task4:
SELECT Textbook.title,Textbook.price FROM Textbook INNER JOIN Textbook_schedule ON Textbook_schedule.tbid = Textbok.tbid
INNER JOIN Schedule ON Schedule.sid = Textbook_schedule.sid
INNER JOIN Course ON Course.cid = Schedule.cid
WHERE Course.cname = "IS",Course.cid = 420,Schedule.snumber = 1,Schedule.semester = "spring",Scedule.year = 2019;

Task5:
SELECT Course.cname FROM Course INNER JOIN Schedule ON Course.cid = Schedule.cid
WHERE Schedule.snumber >= 2,Schedule.semester = "spring",Schedule.year = 2019;

Task6:
SELECT Textbook.title,Schedule.num_registered FROM Textbook INNER JOIN Textbook_schedule ON Textbook.tbid = Textbook_schedule.tbid
INNER JOIN Schedule ON Textbook_schedule.sid = Schedule.sid
WHERE Schedule.semester = "spring",Schedule.year = 2019;