SQL Help

This question uses the code in Schedule.sql.

The query below is intended to calculate how much each enrolled student will owe for each Winter 2003 course he's taking, assuming that each credit costs $54.50.

SELECT last_name + ', ' + first_name Student, sum(credits) TotalCredits, sum(credits)*54.5 TotalDue

FROM course_catalog cc JOIN course_schedule cs ON cc.courseid=cs.courseid JOIN student_schedule ss ON cs.scheduleid=ss.scheduleid JOIN students s ON ss.studentid = s.studentid

WHERE schedule_quarter='Winter' and schedule_year = '2003'

ORDER BY student

a)How would you modify this query to get a total due for all courses each student is enrolled in in Winter 2003?

(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