Hướng dẫn tìm hiểu view + proc trong SQL - Lập trình SQL Server

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

-- Query
---- Hien thi thong tin diem thi sinh vien: roll_number, student_name, subject_name, marks
select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id

---- Bai toan: Luu lai truy van tren thi lam cach nao -> Cach thong thuong -> save file sql -> sau mo lai va test
---- Bai toan 2: Neu phat trien du an theo team (nhieu thanh vien) -> cung su dung chung 1 database
--------- Lam sao de chia se cac query -> thanh vien trong du an co the su dung lai query ban da viet thi lam cach nao???
---- Bai toan 3: Phat trien chuong trinh phan mem:
--------- Cach 1: Nhung sql trong code ngon ngu lap trinh
--------- Cach 2: Luu toan sql trong database -> su dung thong qua 1 view & proc -> su dung dc query
---- View & Proc => giup chung ta => quan ly dc cau query trong chinh database
-- I) View: Luu 1 truy van select -> view se dc hieu nhu 1 table tam thoi.
create view view_student_marks
as
select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id

---- TEST
select * from view_student_marks
order by marks asc

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

---- Sua view
alter view view_student_marks
as
select top(10) Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id

---- Xoa view
drop view view_student_marks

-- II) proc: Duoc hieu nhu 1 function (ham & method & phuong thuc)
------ Điều này có nghĩa là gì: proc -> có thể lưu nhiều câu truy vấn khác nhau: Có thể là nhiều lệnh select, insert, update, delete
create proc proc_test
as
begin
	select * from Student
	select * from Subject
	select * from Marks
end

---- TEST
exec proc_test

create proc proc_view_student_marks_and_test
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id

	select * from Student
	select * from Subject
	select * from Marks
end

exec proc_view_student_marks_and_test

-- Proc: Hiểu nó như là 1 function trong ngôn ngữ lập trình
---- Do vậy: Tham số đầu vào, dữ liệu trả về (output)
---- Proc -> xem thông tin điểm thi của sinh viên -> điều kiện tham số đầu vào là minMarks
create proc view_student_marks_by_min
	@minMarks float
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks
end

exec view_student_marks_by_min 8

---- Bài toán proc gồm nhiều tham số đầu vào thì làm thế nào.
------ Xem thông tin điểm thi sinh viên: @minMark & @roll_number
create proc view_student_marks_by_min_rollno
	@minMarks float,
	@rollno nvarchar(20)
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks
		and Student.roll_number = @rollno
end

exec view_student_marks_by_min_rollno 7, 'R001'

---- Đếm xem có bao nhiêu sinh viên có có điểm thi >= minMarks - Hiển thị dữ liệu đầu ra.
create proc view_count_student_marks_by_min
	@minMarks float,
	@count int output -- float, int, nvarchar, ...
as
begin
	select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks

	-- count(*): Có nghĩa là đếm tổng số bản ghi trong câu truy vấn: Với cách viết này không cần dùng thêm group by.
	select @count = count(*)
	from Student, Subject, Marks
	where Student.roll_number = Marks.roll_number
		and Subject.subject_id = Marks.subject_id
		and Marks.marks >= @minMarks
end

---- Khi khai báo 1 biến trong lệnh sql -> cần đặt từ khóa declare trước tên biến.
declare @count2 int
exec view_count_student_marks_by_min 6, @count = @count2 output
---- Không thể cộng chuỗi với số nguyên
---- Do vậy: Để in được kết quả đầu ra -> chuyển số nguyên về chuỗi (int -> nvarchar)
------- Hàm chuyển int -> nvarhhar: CONVERT(nvarchar(20), @count2)
print N'So ban ghi tim duoc: ' + CONVERT(nvarchar(20), @count2)

-- Sua stored (proc) -> alter
-- Xoa stored (proc) -> drop
drop proc view_count_student_marks_by_min

---- Overview: group by
-- group by: Khi nhom dữ liêu -> thông qua các hàm: count, sum, min, max, avg.
select Student.roll_number, Student.student_name, count(Marks.marks) 'So Mon Co Diem Thi'
from Student, Subject, Marks
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id
group by Student.roll_number, Student.student_name