-- Overview
select * from Student
select * from Subject
select * from Marks

-- Xem mon hoc da co diem thi
select distinct subject_code from Marks

select top(2) * from Student

select * from Student
order by birthday desc

select * from Student
order by birthday asc

select * from Student where birthday = '1980-02-08'
union
select * from Student where birthday = '1985-02-08'

--- View: Luu dc 1 lenh select -> hieu nhu la 1 table tam thoi
create view view_union_student
as
select * from Student where birthday = '1980-02-08'
union
select * from Student where birthday = '1985-02-08'

-- Test view
select * from view_union_student
select * from view_union_student where married_status = 'Single'

-- Sua view
alter view view_union_student
as
select * from Student where birthday = '1980-02-08' or birthday = '1985-02-08'

drop view view_union_student

-- Stored, Proc, Function
create proc proc_test
as
begin
	select * from Student
	select * from Subject
	select * from Marks
end

-- test proc
exec proc_test

select * from student
where birthday >= '1985-02-08'

select * from student
where birthday >= '1988-02-08'

-- Proc co tham so dau vao
create proc proc_student_by_birthday
	@bithday date
as
begin
	select * from student
		where birthday >= @bithday
end

exec proc_student_by_birthday '1985-02-08'

exec proc_student_by_birthday '1988-02-08'

-- proc nhieu tham so dau vao
create proc proc_student_by_birthday_two_param
	@startdate date,
	@enddate date
as
begin
	select * from student
		where birthday between @startdate and @enddate
end

exec proc_student_by_birthday_two_param '1988-01-01','1990-01-01'

select count(*) as 'Total' from student
	where birthday between '1988-01-01' and '1990-01-01'

create proc proc_count_student_by_birthday
	@startdate date,
	@enddate date,
	@count int output
as
begin
	select @count = count(*) from student
		where birthday between @startdate and @enddate
end

declare @count2 int
exec proc_count_student_by_birthday '1988-01-01','1990-01-01',@count = @count2 output
print N'So sinh vien can tim: ' + convert(varchar(20), @count2)