SQL Help Will replacing the field ss.studentid with * in the count function affe
ID: 3733785 • Letter: S
Question
SQL Help
Will replacing the field ss.studentid with * in the count function affect the result of this query? Should the asterisk be used in place of the field name?
SELECT department, count(ss.studentid) CountOf
FROM course_catalog cc LEFT OUTER JOIN course_schedule cs ON cc.courseid=cs.courseid
LEFT OUTER JOIN student_schedule ss ON cs.scheduleid = ss.scheduleid
WHERE schedule_quarter='summer' and schedule_year='2002'
GROUP BY department
(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
Answer)
I have inserted the following rows in the tables for demo purpose:
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_schedule values
(106,200,'N',30,'22','217','TTh','5:15P-7:30P','summer','2002','Staff');
Insert into course_schedule values
(107,201,'B',35,'34','132','Daily','8:00A-8:50A','summer','2002','Baran');
Insert into course_schedule values
(108,202,'C',35,'34','132','Daily','9:00A-9:50A','summer','2002','Baran');
Insert into course_schedule values
(109,203,'D',35,'22','126','Daily','10:00A-
10:50A','summer','2002','Halpern');
Insert into student_schedule values (400,106,null);
Insert into student_schedule values (400,107,null);
Insert into student_schedule values (400,108,'B');
Insert into student_schedule values (400,109,'A');
After inserting the above rows, lets come to the question.
Will replacing the field ss.studentid with * in the count function affect the result of this query? Should the asterisk be used in place of the field name?
SELECT department, count(ss.studentid) CountOf
FROM course_catalog cc LEFT OUTER JOIN course_schedule cs ON cc.courseid=cs.courseid
LEFT OUTER JOIN student_schedule ss ON cs.scheduleid = ss.scheduleid
WHERE schedule_quarter='summer' and schedule_year='2002'
GROUP BY department;
Output on the above data:
Changing the query :
SELECT department, count(*) CountOf
FROM course_catalog cc LEFT OUTER JOIN course_schedule cs ON cc.courseid=cs.courseid
LEFT OUTER JOIN student_schedule ss ON cs.scheduleid = ss.scheduleid
WHERE schedule_quarter='summer' and schedule_year='2002'
GROUP BY department;
Result is:
4
So the result is the same in case of using count(*) CountOf to count and this does not affect the query or the results.
department CountOf CIS 4Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.