/* Lab 9
   Philip Bock */

use class_trak;

drop procedure spCheckError;
go

create procedure spCheckError as
if (@@error != 0)
begin
	return 1
end
else
begin
	return 0
end
go

/* Q1 */
drop procedure spQuestion1;
go

create procedure spQuestion1 as
	declare @return int

	select student_id, last_name + ', ' + first_name 'Name' from students
	order by last_name

	exec @return = spCheckError
	return @return
go

exec spQuestion1;

/* Q2 */
drop procedure spQuestion2;
go

create procedure spQuestion2
go

/* Q3 */
drop procedure spQuestion3;
go

create procedure spQuestion3
	@StudentID int
as
	select
		first_name + ' ' + last_name 'Name',
		students.student_id 'Student ID',
		class_desc 'Class Description',
		classes.class_id 'Class ID',
		course_abbrev 'Course',
		course_desc 'Course Description'
	from students
		join class_to_student on class_to_student.student_id = students.student_id
			join classes on classes.class_id = class_to_student.class_id
				join courses on courses.course_id = classes.course_id
	where students.student_id = @StudentID
	group by first_name, last_name, students.student_id, class_desc, classes.class_id, course_abbrev, course_desc
	order by last_name, first_name
go

exec spQuestion3 @StudentID = 20;
