-- TEST
select * from Student
select * from Subject
select * from Marks

-- Query -> Hien thi du lieu theo format
---- rollno, fullname, email, subject_name, marks
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id

-- Query -> Hien thi du lieu theo format
---- rollno, fullname, email, subject_name, marks
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno
	left join Subject on Subject.subject_id = Marks.subject_id

-- View -> table tam thoi duoc sinh ra boi cau lenh select
create view view_student_marks
as
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id

select * from view_student_marks
-- Dac diem view
---- 1. Chua duy nhat 1 cau lenh select
---- 2. Moi mot view -> duoc hieu nhu la 1 bang tam thoi.

select * from view_student_marks
where marks >= 8
order by marks desc

alter view view_student_marks
as
select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno
	left join Subject on Subject.subject_id = Marks.subject_id

drop view view_student_marks

-- Note: Khi tao view muon su dung order by => ket hop vs lenh top() -> neu khong se bi error
create view view_student_marks
as
select top(10) Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id
order by Marks.marks desc

-- View Test -> error
create view view_test
as
select * from Student
select * from Subject
select * from Marks

-- Quan ly dc danh sach lenh -> giong nhu tao function trong js, c, ...
---- Stored, proc, function
create proc proc_test
as
begin
	select * from Student
	select * from Subject
	select * from Marks
end

exec proc_test
---- Mot so dac tinh cua function
------ Function khong tham so dau vao
------ Function co tham so dau vao
------ Function tra du lieu ve & khong tra du lieu ve
create proc proc_view_student_mark
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
	order by Marks.marks desc
end

---- Goi toi stored/proc -> hieu nhu la dang goi toi 1 ham -> thuc hien cac cau lenh trong ham do.
exec proc_view_student_mark

select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id
	and Marks >= 7
order by Marks.marks desc

select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.rollno = Marks.rollno
	and Subject.subject_id = Marks.subject_id
	and Marks >= 9
order by Marks.marks desc

---- Dung giai phap -> stored -> de giai quyet bai toan tren
create proc proc_view_student_marks_by_marks
	@marks float
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @marks
	order by Marks.marks desc
end

exec proc_view_student_marks_by_marks 7
exec proc_view_student_marks_by_marks 9
exec proc_view_student_marks_by_marks 5

create proc proc_view_student_marks_by_marks2
	@marks float,
	@rollno nvarchar(12)
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @marks
		and Student.rollno = @rollno
	order by Marks.marks desc
end

exec proc_view_student_marks_by_marks2 5, 'R001'
exec proc_view_student_marks_by_marks2 5, 'R002'

drop proc proc_view_student_marks_by_marks2

alter proc proc_view_student_marks_by_marks2
	@marks float,
	@rollno nvarchar(12),
	@subjectName nvarchar(30)
as
begin
	select Student.rollno, Student.fullname, Student.email, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.rollno = Marks.rollno
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @marks
		and Student.rollno = @rollno
		and Subject.subject_name like CONCAT('%', @subjectName, '%')
	order by Marks.marks desc
end

exec proc_view_student_marks_by_marks2 5, 'R001', 'P'

---- Stored co du lieu tra ve.
select Student.rollno, Student.fullname, count(Student.rollno) 'Count'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

create proc proc_count_marks_by_student
	@rollno nvarchar(12),
	@count int output
as
begin
	select Student.rollno, Student.fullname, count(Student.rollno) 'Count'
	from Student left join Marks on Student.rollno = Marks.rollno
	where Student.rollno = @rollno
	group by Student.rollno, Student.fullname
	
	select @count = count(Student.rollno)
	from Student left join Marks on Student.rollno = Marks.rollno
	where Student.rollno = @rollno
end

declare @count1 int
exec proc_count_marks_by_student 'R001', @count = @count1 output
print @count1