IT230 Phase 3 Data Requirements

Professor
ProfID	Department	Rank	Last Name	First Name	Initial	Email
SJ001	MSIS	Professor	Jones	Samuel	L	SJones@trinity.edu
NS001	MSIS	Professor	Smith	Nancy	P	NSmith@trinity.edu
RA001	BIO	Adjunct	Adams	Robert	R	RAdams@trinity.edu
BB001	AER	Professor	Brooks	Bradford	C	BBrooks@trinity.edu
SM001	AM	Adjunct	McDonald	Susan	W	SMcDonald@trinity.edu
SW001	DAN	Professor	Winsor	Stephen	A	SWinsor@trinity.edu
TA001	ECO	Professor	Anthony	Todd	T	TAnthony@trinity.edu
JW001	SE	Professor	Walker	Joanne	L	JWalker@trinity.edu
TH001	AER	Professor	Hastings	Timothy		THastings@trinity.edu
MY001	PHY	Professor	Young	Margaret	J	MYoung@trinity.edu
CM001	CS	Professor	MacNamara	Christine	F	CMacNamara@trinity.edu
DW001	MUS	Professor	Winsor	Deborah		DWinsor@trinity.edu
HB001	EE	Professor	Brown	Harold	R	HBrown@trinity.edu
CN001	BIO	Professor	Noble	Charles	E	CNoble@trinity.edu
FL001	PHY	Professor	Lawton	Francis	X	FLawton@trinity.edu
PH001	CE	Adjunct	Hohl	Patrick		PHohl@trinity.edu
KY001	AER	Professor	Yang	Kim		KYang@trinity.edu
JS001	PHY	Adjunct	Santiago	Jose	M	JSantiago@trinity.edu

Shool
SchoolID	School Name
Mgt	Management
Eng	Engineering
Edu	Education
FA	Fine Arts
Math	Mathematics
Sci	Science
SchoolDean
SchoolID	ProfID
Mgt	NS001
Eng	KY001
Edu	HB001
FA	DW001
Math	CM001
Sci	JS001
DepartmentChair
DeptID	ProfID
MSIS	SJ001
BIO	CN001
PHY	JS001
MKT	NS001
ECO	TA001
CHE	MY001
DAN	SW001
MUS	DW001
AER	TH001
CE	PH001
CS	CM001
AM	SM001
EE	JW001
SE	JW001

Department
DeptID	Name	SchoolID
MSIS	MSIS	Mgt
BIO	Biology	Sci
PHY	Physics	Sci
MKT	Marketing	Mgt
ECO	Economics	Mgt
CHE	Chemistry	Sci
DAN	Dance	FA
MUS	Music	FA
AER	Aerospace	Eng
CE	Civil Engineering	Eng
CS	Computer Science	Math
AM	Applied Mathematics	Math
EE	Elementary Education	Edu
SE	Special Education	Edu

Student
StudID	DeptID	Last Name	First Name	MI	Email	Advisor
PP001	MSIS	Pierce	Paul	A	ppierce@trinity.edu	NS001
AA001	BIO	Adams	Abigail	W	aadams@trinity.edu	CN001
WS001	ECO	Smithson	William	P	wsmithson@trinity.edu	TA001
AB001	CS	Bonner	Allison	W	abonner@trinity.edu	CM001
MB001	SE	Brady	Marcia		mbrady@trinity.edu	JW001
KN001	MSIS	Nelson	Kenneth	A	knelson@trinity.edu	SJ001
CA001	MSIS	Alexander	Constance	C	calexander@trinity.edu	NS001
TN001	CS	Nguyen	Trin		tnguyen@trinity.edu	CM001
SB001	AER	Brandt	Stephen	R	sbrandt@trinity.edu	TH001
JE001	PHY	Espanet	Jesse
JE002	DA	Espanet	Jordan			SW001
EL001	PHY	Lawton	Eleanor		elawton@trinity.edu	MY001
RK001	PHY	King	Robert	L	rking@trinity.edu
RK002	CS	Kershaw	Rowena	C	rkershaw@trinity.edu	CM001
RM001	EE	McNamara	Richard		rmcnamara@trinity.edu	HB001
LC001	MUS	Chin	Lori		lchin@trinity.edu
JM001	EE	Martinez	Jose		jmartinez@trinity.edu	HB001
MB002	MKT	Brown	Marcus	L

Course
CourseCode	DeptID	Title	Description	Credits
MGT105	MSIS	Intro to Information Systems	Lecture	3
MGT475	MKT	Strategic Marketing	Lecture	4
ENG210	AER	Engineering Mecahnics I	Lab	3
ENG320	CE	Electric Circuits	Lab	4
EDU117	EE	Educational Equity and the Law	Lecture	3
EDU256	EE	Education and Religion	Lecture	3
MATH300	AM	Statistics	Lecture	4
MATH325	AM	Calculus II	Lecture	4
SCI110	PHY	Physics I	Lab	3
SCI415	PHY	Thermodynamics	Lab	4

Class
ClassCode	Class Section	Class Time	CourseCode	ProfID	RoomCode
CL001	1	MWF 8:00 - 9:00	MGT105	SJ001	MCLH001
CL002	2	TTh 1:30 - 3:00	MGT105	NS001	KEC001
CL003	1	MWF 1:00 - 2:00	MGT475	SJ001	SLH001
CL004	1	MWF 11:00 - 12:00	ENG210	KY001	PAC001
CL005	2	TTh 11:30 - 1:00	ENG210	KY001	MCL001
CL006	3	W 6:00 - 9:00	ENG210	BB001	PAL001
CL007	1	TTh 8:30 - 11:00	MATH300	SM001	MCC001
CL008	1	MWF 10:00 - 11:00	MATH325	MY001	SLL001
CL009	1	M 6:00 - 9:00	SCI110	FL001	SAL001
CL010	1	Th 6:00 - 9:00	SCI415	MY001	SAL001

ENROLL
ClassCode	StudID	Enrollment Date	Grade
CL001	PP001	9/1/2017
CL002	PP001	9/1/2017
CL003	PP001	9/1/2017
CL009	AA001	9/1/2017
CL010	AA001	9/1/2017
CL004	WS001	9/1/2017
CL005	AB001	9/1/2017
CL006	AB001	9/1/2017
CL008	AB001	9/1/2017
CL009	JE001	9/1/2017
CL010	JE002	9/1/2017
CL001	JE002	9/1/2017
CL004	LC001	9/1/2017
CL008	JM001	9/1/2017
CL009	MB002	9/1/2017
CL006	MG001	9/1/2017
CL005	AA002	9/1/2017
CL006	AA002	9/1/2017
CL003	KN001	9/1/2017
CL010	CA001	9/1/2017

Building				Room
BldgID	Name	Location		RoomCode	Type	BldgID
MC	McCormack	Boston		MCL001	Lab	MC
QU	Quinn	Boston		MCC001	Classroom	MC
KE	Kennedy	Worcester		MCLH001	Lecture Hall	MC
SL	Sloan	Worcester		QUL001	Lab	QU
SA	Saltonstall	Boston		QUC001	Classroom	QU
PA	Parks	Amherst		QULH001	Lecture Hall	QU
				KEL001	Lab	KE
				KEC001	Classroom	KE
				KELH001	Lecture Hall	KE
				SLL001	Lab	SL
				SLC001	Classroom	SL
				SLLH001	Lecture Hall	SL
				SAL001	Lab	SA
				SAC001	Classroom	SA
				SALH001	Lecture Hall	SA
				PAL001	Lab	PA
				PAC001	Classroom	PA
				PALH001	Lecture Hall	PA

IT230 - Phase 3 SQL Statements

Single table queries:

Create a query that returns student last name, first name, and email address. Sort by last name.

Create a query that return student last name, first name, advisor (ProfID) and email address for any student who doesnt have an advisor or an email address. Sort by last name.

Create a query that returns professor rank, last name, first name and email address. Sort by rank then last name.

Create a query that returns professor rank, last name, first name and email address whose rank is Professor.

Create a query that returns building name and location. Sort by location.

Multiple table queries:

Create a query that returns Advisor Last Name, Advisor First Name, Student Last Name, and Student First Name. Sort by Advisor Last Name then Student Last Name.

Create a query that returns School Name, School Dean Last Name, School Dean First Name, Department Name, Department Chair Last Name, and Department Chair First Name. Sort by School Name and Department Name.

Create a query that returns Department Name, Course Title, Course Description, and Course Credits. Sort by Department Name then Course Title.

Create a query that returns Student Last Name, Student First Name, Course Title, Class Section, Class Time, Professor Last Name, Professor First Name, Room Code, Room Type, Building Name and Building Location. Sort by Student Last Name then Course Title

Grades:

Create update queries that will change the grades for the following students and classes:

StudID ClassCode Grade

PP001 CL001 A

PP001 CL002 B

AA001 CL009 B+

JE001 CL009 C

KN001 CL003 F

You only need to include the update queries in the Word document.

Using Management Studio, enter grades for all the other rows in the Enroll table.

After all the grades have been entered, create a query that returns Student Last Name, Student First Name, Course Title, Grade, and Number of Credits. Sort by