Question

I am having a hard time writing these SQL queries. Please specify the following queries in SQL on the database schema shown in the figure below.

STUDENT

Name	StudentNumber	Class	Major
Smith	17	1	CS
Brown	8	2	CS
Kathy	15	1	EE

COURSE

CourseName	CourseNumber	CreditHours	Department
Intro to Computer Science	CSE110	4	CS
Data Structures	CSE205	4	CS
Discrete Mathematics	MAT240	3	MATH
Databases	CSE380	3	CS
Analog Circuits	EE260	3	EE

SECTION

SectionIdentifier	CourseNumber	Semester	Year	Instructor
85	MAT240	Fall	2010	King
92	CSE110	Fall	2010	Chang
102	CSE205	Spring	2011	King
112	MAT240	Fall	2011	Chang
119	CSE110	Fall	2011	Anderson
135	CSE380	Fall	2011	Stone
146	EE260	Fall	2011	James

GRADE REPORT

StudentNumber	SectionIdentifier	Grade
17	112	B
17	119	C
8	85	A
8	92	A
8	102	B
8	135	A
15	146	A

PREREQUISITE

CourseNumber	PreRequisiteNumber
CSE380	CSE205
CSE380	MAT240
CSE205	CSE110
EE260	MAT240

1) Retrieve the course names of all courses with the number of credit hours as 4

2) Retrieve the names of all courses and their respective departments taught by professor Chang in 2010 and 2011

3) For each section taught by Professor King, retrieve the course number, semester, year, and the number of students who took the section

4) Retrieve the name and transcript of each freshman student (Class=1) majoring in EE. Transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student

Answer 1

Answer:

1) Retrieve the course names of all courses with the number of credit hours as 4

Select CourseName,CreditHours from COURSE where CreditHours = 4

Output:

CourseName	CreditHours
Intro to Computer Science	4
Data Structures	4

2) Retrieve the names of all courses and their respective departments taught by professor Chang in 2010 and 2011

Select co.CourseName,co.Department,se.Instructor,se.Year from Course co
join Section se on se.CourseNumber = co.CourseNumber
where se.Year in ('2010','2011') and se.Instructor = "Chang" order by co.CourseName;

Output:

CourseName	Department	Instructor	Year
Intro to Computer Science	CS	Chang	2010
Discrete Mathematics	MATH	Chang	2011

3) For each section taught by Professor King, retrieve the course number, semester, year, and the number of students who took the section

Select se.CourseNumber,se.Semester,se.Year,count(gr.StudentNumber)as " Total Students" from SECTION se
join GradeReport gr on gr.SectionIdentifier = se.SectionIdentifier
where se.Instructor = "King" group by se.SectionIdentifier ;

Output:

CourseNumber	Semester	Year	Total Students
MAT240	Fall	2010	1
CSE205	Spring	2011	1

4) Retrieve the name and transcript of each freshman student (Class=1) majoring in EE. Transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student

Select st.Name,c.CourseName,c.CourseNumber,c.CreditHours,s.Semester,s.Year,g.Grade from Course c
join Section s on s.CourseNumber = c.CourseNumber
join GradeReport g on g.SectionIdentifier = s.SectionIdentifier
join Student st on st.StudentNumber = g.StudentNumber
where st.class = 1 and st.Major = "EE" order by Name;

Output:

Name	CourseName	CourseNumber	CreditHours	Semester	Year	Grade
Kathy	Analog Circuits	EE260	3	Fall	2011	A

Thanks and all the best. Let me know in case if you have any doubts.