Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

SQL Help This question uses the code in Schedule.sql. a) Which table is the foll

ID: 3733780 • Letter: S

Question

SQL Help

This question uses the code in Schedule.sql.

a) Which table is the following query about?

SELECT department + ' ' + number Course, schedule_quarter + ' ' + schedule_year QuarterYear, schedule_section

          FROM course_catalog cc JOIN course_schedule cs ON cc.courseid = cs.courseid

                   WHERE schedule_year='2002'

                   AND ((schedule_quarter='fall'

                   AND scheduleid NOT IN

                             (SELECT scheduleid FROM student_schedule))

                   OR (schedule_quarter = 'summer'))

                   ORDER BY Course, quarteryear, schedule_section

(Here is the Schedule.sql)

-- cis182 final exam database

create database cis182_final

go

use cis182_final

go

CREATE TABLE course_catalog

(

courseid int NOT NULL primary key ,

department char (3) NOT NULL ,

number char (3) NOT NULL ,

credits tinyint NOT NULL DEFAULT 2,

description varchar (30) NULL

)

go

CREATE TABLE course_schedule (

scheduleid int NOT NULL primary key,

courseid int NULL

references course_catalog(courseid),

schedule_section char (1) NULL ,

capacity tinyint NULL ,

building varchar (3) NULL ,

room varchar (3) NULL ,

days varchar (5) NULL ,

times varchar (30) NULL ,

schedule_quarter varchar (6) NULL ,

schedule_year char (4) NULL ,

instructor varchar (20) NULL

)

go

CREATE TABLE students (

studentid int NOT NULL primary key,

first_name varchar (20) NULL ,

last_name varchar (30) NULL ,

address varchar (100) NULL ,

city varchar (25) NULL ,

state char (2) NULL ,

zip char (10) NULL ,

phone char (14) NULL ,

major varchar (25) NULL ,

resident bit NULL

)

go

CREATE TABLE student_schedule (

studentid int NOT NULL

references students(studentid) ,

scheduleid int NOT NULL

references course_schedule(scheduleid),

grade char (1) NULL

check (grade like '[A-DFIV]')

)

go

Insert into course_catalog values (200,'CIS','182',5,'Introduction to

SQL')

Insert into course_catalog values (201,'CIS','201',5,'Systems Analysis')

Insert into course_catalog values (202,'CIS','160',5,'Introduction to

Programming')

Insert into course_catalog values (203,'CIS','166',5,'Intermediate Visual

Basic.NET')

Insert into course_catalog values (204,'BUS','101',5,'Introduction to

Business')

Insert into course_catalog values (205,'BUS','104',5,'Business Math')

Insert into course_catalog values (206,'BUS','160',5,'Principles of

Marketing')

Insert into course_catalog values (207,'ASL','101',5,'American Sign

Language I')

Insert into course_catalog values (208,'ASL','102',5,'American Sign

Language II')

Insert into course_catalog values (209,'ASL','202',5,'American Sign

Language V')

Insert into course_catalog values (210,'SOC','101',5,'Introduction to

Sociology')

Insert into course_catalog values (211,'SOC','125',5,'Marriage and Family

Life')

Insert into course_catalog values (212,'SOC','145',5,'Social Problems')

Insert into course_catalog values (213,'SOC','235',5,'Gender Roles')

Insert into course_catalog values (214,'SOC','265',5,'Criminology')

Insert into course_catalog values (215,'WLD','108',2,'Basic Welding')

Insert into course_catalog values (216,'WLD','111',4,'Oxy-Acetylene

Welding I')

Insert into course_catalog values (217,'WLD','112',4,'Oxy-Acetylene

Welding II')

Insert into course_catalog values (218,'WLD','113',4,'Oxy-Acetylene

Welding III')

go

Insert into course_schedule values (100,207,'N',30,'22','217','MW','5:15P-

7:30P','Winter','2003','Staff')

Insert into course_schedule values

(101,207,'C',30,'22','122','Daily','9:00A-9:50A','Winter','2003','Bateh')

Insert into course_schedule values

(102,208,'D',30,'22','217','Daily','10:00A-

10:50A','Winter','2003','Bateh')

Insert into course_schedule values

(103,208,'E',30,'22','217','Daily','11:00A-

11:50A','Winter','2003','Bateh')

Insert into course_schedule values (104,208,'N',30,'6','1','MW','5:15P-

7:30P','Winter','2003','Bateh')

Insert into course_schedule values (105,208,'N',30,'6','1','TTh','5:15P-

7:30P','Winter','2003','Staff')

Insert into course_schedule values

(106,209,'N',30,'22','217','TTh','5:15P-7:30P','Winter','2003','Staff')

Insert into course_schedule values

(107,204,'B',35,'34','132','Daily','8:00A-8:50A','Winter','2003','Baran')

Insert into course_schedule values

(108,204,'C',35,'34','132','Daily','9:00A-9:50A','Winter','2003','Baran')

Insert into course_schedule values

(109,204,'D',35,'22','126','Daily','10:00A-

10:50A','Winter','2003','Halpern')

Insert into course_schedule values

(110,204,'N',35,'34','131','TTh','5:15P-

7:30P','Winter','2003','Glendenning')

Insert into course_schedule values

(111,205,'I',35,'34','132','Daily','1:15P-

2:05P','Winter','2003','Halpern')

Insert into course_schedule values

(112,206,'P',35,'34','208','TTh','7:45P-

10:00P','Winter','2003','Glendenning')

Insert into course_schedule values

(113,202,'C',30,'34','107','Daily','9:00A-

9:50A','Winter','2003','Nielson')

Insert into course_schedule values

(114,202,'I',30,'34','106','Daily','1:15P-

2:05P','Winter','2003','Richard')

Insert into course_schedule values (115,202,'M',30,'34','106','MW','4:30P-

6:50P','Winter','2003','Richard')

Insert into course_schedule values

(116,203,'F',30,'34','106','MTWF','12:00P-

1:05P','Winter','2003','Nielson')

Insert into course_schedule values (117,203,'P',30,'34','106','MW','7:00P-

9:30P','Winter','2003','Richard')

Insert into course_schedule values (118,200,'M',30,'34','107','MW','4:30P-

6:50P','Winter','2003','Bowe')

Insert into course_schedule values

(119,201,'P',30,'34','106','TTh','7:00P-9:30P','Winter','2003','Staff')

Insert into course_schedule values

(120,210,'C',35,'26','103','Daily','9:00A-9:50A','Winter','2003','Dixon')

Insert into course_schedule values

(121,210,'D',35,'26','102','Daily','10:00A-

10:50A','Winter','2003','Dixon')

Insert into course_schedule values

(122,210,'N',35,'22','207','TTh','5:15P-7:30P','Winter','2003','Chase')

Insert into course_schedule values

(123,211,'I',35,'22','200','TTh','1:15P-3:40P','Winter','2003','Dixon')

Insert into course_schedule values

(124,212,'B',35,'26','103','Daily','8:00A-8:50A','Winter','2003','Hyde')

Insert into course_schedule values

(125,212,'D',35,'31','102','Daily','10:00A-10:50A','Winter','2003','Hyde')

Insert into course_schedule values

(126,212,'F',35,'26','101','MTWF','12:00P-1:05P','Winter','2003','Vosper')

Insert into course_schedule values

(127,213,'E',35,'26','101','Daily','11:00A-

11:50A','Winter','2003','Chase')

Insert into course_schedule values

(128,214,'E',35,'26','102','Daily','11:00A-11:50A','Winter','2003','Hyde')

Insert into course_schedule values (129,215,'N',20,'22','114','T','6:00P-

9:50P','Winter','2003','Hegsted')

Insert into course_schedule values (130,216,'N',20,'22','114','MW','6:00P-

9:00P','Winter','2003','Hegsted')

Insert into course_schedule values (131,217,'N',20,'22','114','MW','6:00P-

9:00P','Winter','2003','Hegsted')

Insert into course_schedule values (132,218,'N',20,'22','114','MW','6:00P-

9:00P','Winter','2003','Hegsted')

Insert into course_schedule values (133,207,'N',30,'22','217','MW','5:15P-

7:30P','Fall','2002','Staff')

Insert into course_schedule values

(134,207,'C',30,'22','122','Daily','9:00A-9:50A','Fall','2002','Bateh')

Insert into course_schedule values

(135,208,'D',30,'22','217','Daily','10:00A-10:50A','Fall','2002','Bateh')

Insert into course_schedule values

(136,208,'E',30,'22','217','Daily','11:00A-11:50A','Fall','2002','Bateh')

Insert into course_schedule values (137,208,'N',30,'6','1','MW','5:15P-

7:30P','Fall','2002','Batch')

Insert into course_schedule values (138,208,'N',30,'6','1','TTh','5:15P-

7:30P','Fall','2002','Staff')

Insert into course_schedule values

(139,209,'N',30,'22','217','TTh','5:15P-7:30P','Fall','2002','Staff')

Insert into course_schedule values

(140,204,'B',35,'34','132','Daily','8:00A-8:50A','Fall','2002','Baran')

Insert into course_schedule values

(141,204,'C',35,'34','132','Daily','9:00A-9:50A','Fall','2002','Baran')

Insert into course_schedule values

(142,204,'D',35,'22','126','Daily','10:00A-

10:50A','Fall','2002','Halpern')

Insert into course_schedule values

(143,204,'N',35,'34','131','TTh','5:15P-

7:30P','Fall','2002','Glendenning')

Insert into course_schedule values

(144,205,'I',35,'34','132','Daily','1:15P-2:05P','Fall','2002','Halpern')

Insert into course_schedule values

(145,206,'P',35,'34','208','TTh','7:45P-

10:00P','Fall','2002','Glendenning')

Insert into course_schedule values

(146,202,'C',30,'34','107','Daily','9:00A-9:50A','Fall','2002','Nielson')

Insert into course_schedule values

(147,202,'I',30,'34','106','Daily','1:15P-2:05P','Fall','2002','Richard')

Insert into course_schedule values

(149,203,'F',30,'34','106','MTWF','12:00P-1:05P','Fall','2002','Nielson')

Insert into course_schedule values (150,203,'P',30,'34','106','MW','7:00P-

9:30P','Fall','2002','Richard')

Insert into course_schedule values

(152,201,'P',30,'34','106','TTh','7:00P-9:30P','Fall','2002','Staff')

Insert into course_schedule values

(153,210,'C',35,'26','103','Daily','9:00A-9:50A','Fall','2002','Dixon')

Insert into course_schedule values

(154,210,'D',35,'26','102','Daily','10:00A-10:50A','Fall','2002','Dixon')

Insert into course_schedule values

(155,210,'N',35,'22','207','TTh','5:15P-7:30P','Fall','2002','Chase')

Insert into course_schedule values

(156,211,'I',35,'22','200','TTh','1:15P-3:40P','Fall','2002','Dixon')

Insert into course_schedule values

(157,212,'B',35,'26','103','Daily','8:00A-8:50A','Fall','2002','Hyde')

Insert into course_schedule values

(158,212,'D',35,'31','102','Daily','10:00A-10:50A','Fall','2002','Hyde')

Insert into course_schedule values

(159,212,'F',35,'26','101','MTWF','12:00P-1:05P','Fall','2002','Vosper')

Insert into course_schedule values

(160,213,'E',35,'26','101','Daily','11:00A-11:50A','Fall','2002','Chase')

Insert into course_schedule values

(161,214,'E',35,'26','102','Daily','11:00A-11:50A','Fall','2002','Hyde')

Insert into course_schedule values (162,215,'N',20,'22','114','T','6:00P-

9:50P','Fall','2002','Hegsted')

Insert into course_schedule values (163,216,'N',20,'22','114','MW','6:00P-

9:00P','Fall','2002','Hegsted')

Insert into course_schedule values (164,217,'N',20,'22','114','MW','6:00P-

9:00P','Fall','2002','Hegsted')

Insert into course_schedule values (165,218,'N',20,'22','114','MW','6:00P-

9:00P','Fall','2002','Hegsted')

Insert into course_schedule values (166,207,'N',30,'22','217','MW','5:15P-

7:30P','Spring','2002','Staff')

Insert into course_schedule values

(167,207,'C',30,'22','122','Daily','9:00A-9:50A','Spring','2002','Bateh')

Insert into course_schedule values

(168,208,'D',30,'22','217','Daily','10:00A-

10:50A','Spring','2002','Bateh')

Insert into course_schedule values

(169,208,'E',30,'22','217','Daily','11:00A-

11:50A','Spring','2002','Bateh')

Insert into course_schedule values (170,208,'N',30,'6','1','MW','5:15P-

7:30P','Spring','2002','Bateh')

Insert into course_schedule values (171,208,'N',30,'6','1','TTh','5:15P-

7:30P','Spring','2002','Staff')

Insert into course_schedule values

(172,209,'N',30,'22','217','TTh','5:15P-7:30P','Spring','2002','Staff')

Insert into course_schedule values

(173,204,'B',35,'34','132','Daily','8:00A-8:50A','Spring','2002','Baran')

Insert into course_schedule values

(174,204,'C',35,'34','132','Daily','9:00A-9:50A','Spring','2002','Baran')

Insert into course_schedule values

(175,204,'D',35,'22','126','Daily','10:00A-

10:50A','Spring','2002','Halpern')

Insert into course_schedule values

(176,204,'N',35,'34','131','TTh','5:15P-

7:30P','Spring','2002','Glendenning')

Insert into course_schedule values

(177,205,'I',35,'34','132','Daily','1:15P-

2:05P','Spring','2002','Halpern')

Insert into course_schedule values

(178,206,'P',35,'34','208','TTh','7:45P-

10:00P','Spring','2002','Glendenning')

Insert into course_schedule values

(179,202,'C',30,'34','107','Daily','9:00A-

9:50A','Spring','2002','Nielson')

Insert into course_schedule values

(180,202,'I',30,'34','106','Daily','1:15P-

2:05P','Spring','2002','Richard')

Insert into course_schedule values

(181,203,'F',30,'34','106','MTWF','12:00P-

1:05P','Spring','2002','Nielson')

Insert into course_schedule values (182,203,'P',30,'34','106','MW','7:00P-

9:30P','Spring','2002','Richard')

Insert into course_schedule values

(183,201,'P',30,'34','106','TTh','7:00P-9:30P','Spring','2002','Staff')

Insert into course_schedule values

(184,210,'C',35,'26','103','Daily','9:00A-9:50A','Spring','2002','Dixon')

Insert into course_schedule values

(186,210,'N',35,'22','207','TTh','5:15P-7:30P','Spring','2002','Chase')

Insert into course_schedule values

(187,211,'I',35,'22','200','TTh','1:15P-3:40P','Spring','2002','Dixon')

Insert into course_schedule values

(189,212,'D',35,'31','102','Daily','10:00A-10:50A','Spring','2002','Hyde')

Insert into course_schedule values

(190,212,'F',35,'26','101','MTWF','12:00P-1:05P','Spring','2002','Vosper')

Insert into course_schedule values (193,215,'N',20,'22','114','T','6:00P-

9:50P','Spring','2002','Hegsted')

Insert into course_schedule values (197,207,'N',30,'22','217','MW','5:15P-

7:30P','Summer','2002','Staff')

Insert into course_schedule values

(199,208,'D',30,'22','217','MTWTh','10:00A-

11:50A','Summer','2002','Bateh')

Insert into course_schedule values

(204,204,'B',35,'34','132','MTWTh','8:00A-9:50A','Summer','2002','Baran')

Insert into course_schedule values

(208,205,'I',35,'34','132','MTWTh','1:15P-

3:05P','Summer','2002','Halpern')

Insert into course_schedule values

(209,206,'P',35,'34','208','TTh','7:45P-

10:00P','Summer','2002','Glendenning')

Insert into course_schedule values

(217,210,'N',35,'22','207','TTh','5:15P-7:30P','Summer','2002','Chase')

Insert into course_schedule values

(218,211,'I',35,'22','200','TTh','1:15P-3:40P','Summer','2002','Dixon')

Insert into course_schedule values

(220,212,'D',35,'31','102','MTWTh','10:00A-11:50A','Summer','2002','Hyde')

Insert into course_schedule values

(221,212,'F',35,'26','101','MTWF','12:00P-1:05P','Summer','2002','Vosper')

Insert into course_schedule values (224,215,'N',20,'22','114','T','6:00P-

10:50P','Summer','2002','Hegsted')

Insert into course_schedule values (225,216,'N',20,'22','114','MW','6:00P-

10:00P','Summer','2002','Hegsted')

Insert into course_schedule values (226,217,'N',20,'22','114','MW','6:00P-

10:00P','Summer','2002','Hegsted')

Insert into course_schedule values (227,218,'N',20,'22','114','MW','6:00P-

10:00P','Summer','2002','Hegsted')

go

Insert into students values (400,'Bob','Johnson','123 Main

Street','Olympia','WA','98501 ','(360) 555-1862','Accounting',1)

Insert into students values (401,'Carrie','Toles','459 Fir Blvd

#25','Tumwater','WA','98502 ','(360) 555-9843','Accounting',1)

Insert into students values (402,'Jim','Bassett','982 Creek

Road','Olympia','WA','98501 ','(360) 555-6428','Computer Science',0)

Insert into students values (403,'Toni','Smith','3589 First

Avenue','Lacey','WA','98503 ','(360) 555-2379','Nursing',1)

Insert into students values (404,'Natasha','Evans','908 Lake

Drive','Lacey','WA','98503 ','(360) 555-1376','Business',1)

Insert into students values (405,'Trevor','MacKenzie','8765 Alder

Road','Tumwater','WA','98502 ','(360) 555-9713','Welding',1)

Insert into students values (406,'Phan','Nguyen','289 Middleton Avenue

#49','Olympia','WA','98501 ','(360) 555-8721','Computer Science',0)

go

Insert into student_schedule values (400,102,null)

Insert into student_schedule values (400,112,null)

Insert into student_schedule values (400,134,'B')

Insert into student_schedule values (400,144,'A')

Insert into student_schedule values (400,173,'B')

Insert into student_schedule values (400,184,'A')

Insert into student_schedule values (401,135,'A')

Insert into student_schedule values (401,144,'B')

Insert into student_schedule values (401,153,'B')

Insert into student_schedule values (401,166,'C')

Insert into student_schedule values (401,204,'B')

Insert into student_schedule values (402,117,null)

Insert into student_schedule values (402,145,'B')

Insert into student_schedule values (404,129,null)

Insert into student_schedule values (404,173,'A')

Insert into student_schedule values (404,208,'A')

Insert into student_schedule values (405,132,null)

Insert into student_schedule values (405,164,'B')

Insert into student_schedule values (405,173,'A')

Insert into student_schedule values (405,225,'B')

Insert into student_schedule values (406,116,null)

Insert into student_schedule values (406,119,null)

Insert into student_schedule values (406,146,'A')

go

Explanation / Answer

a) Which table is the following query about?

Ans)

The query displays data as per the following:

1) All the Course (Department + Number), Quarter Year (Schedule Quarter + Schedule Year) and Schedule Section for "Fall 2002" Quarter year where no schedules are allotted for any students.

Or,

2) All the Course (Department + Number), Quarter Year (Schedule Quarter + Schedule Year) and Schedule Section for "Summer 2002" Quarter Year.

The data will be sorted in the order Course, quarteryear, schedule_section.

The tables involved are course_catalog, course_schedule and student_schedule.

Screenshot:

Related Questions

SQL Assignment The shema of these relations is: .CLASSESclass, type, country, nu
Question #3872910
SQL Assignment Use the W3School\'s sample database (ink here) to answer the ques
Question #3914643
SQL Asslgnment The shema of these relations is: CLASSES(class, type, country, nu
Question #3586447
SQL BRANCH bid B01 B02 EMPLOYEES bname Olympia Seattle eid Ename E1 Bill Gates E
Question #3699678
SQL Background: Premiere Products database team is working on storing the inform
Question #3872180
SQL CANNOT USE “ INNER, LEFT, or RIGHT JOIN ” and “ ON ” syntax to perform the j
Question #3590541
SQL CODE- The Order side of the application center needs to be able to perform t
Question #3843969
SQL CODE-Create a view to show the totals of a single employee in a year. The vi
Question #3843965

Navigate

Previous
SQL Help This question uses the code in Schedule.sql. What question is this quer
Next
SQL Help True/False a) DML commands are used to edit data in a database. (T/F) b

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