#readme.txt

Nội dung học:
	- Select
		- distinct
		- union
		- order by
		- group by .. having
		- join
			- inner join
			- left join
			- right join
			- full outer join
	- Index
		- clustered index
		- non-clustered index
=========================================================

#SQLQuery1.sql

-- Test data
select * from Class
select * from Student
select * from Subject
select * from Result

-- Hien thi du lieu dau ra nhu sau:
---- ClassId (Class), ClassCode (Class), StudentName (Student)
select Class.ClassId, Class.ClassCode, Student.StudentName
from Class, Student
where Class.ClassId = Student.ClassId
go

-- Xem danh sach lop hoc co sinh vien tham gia
select * from Class
go

select top(2) * from Class
go

select distinct Class.ClassId, Class.ClassCode
from Class, Student
where Class.ClassId = Student.ClassId
go

-- Sap xep du lieu theo ClassCode (A-Z)
select distinct Class.ClassId, Class.ClassCode
from Class, Student
where Class.ClassId = Student.ClassId
order by Class.ClassCode asc
go

select distinct Class.ClassId, Class.ClassCode
from Class, Student
where Class.ClassId = Student.ClassId
order by Class.ClassCode desc
go

-- Dem so sinh vien trong lop hoc
---- ClassId, ClassCode, Total
select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) 'Total'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go

-- Dem so sinh vien trong lop hoc -> CHi hien thi lop hoc co >= 2 sinh vien
---- ClassId, ClassCode, Total
(
select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) 'Total'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
having COUNT(Student.StudentId) >= 2 -- Dieu kien > ket qua cua count, sum, min, max, avg -> having
)
union
(
select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) 'Total'
from Class, Student
where Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
having COUNT(Student.StudentId) <= 1
)

-- Noi du lieu tu nhieu bang
select * from Class
select * from Student

select Class.ClassId, Class.ClassCode, Student.StudentName
from Class, Student
where Class.ClassId = Student.ClassId
go

-- Join
select Class.ClassId, Class.ClassCode, Student.StudentName
from Class inner join Student on Class.ClassId = Student.ClassId
go

select Class.ClassId, Class.ClassCode, Student.StudentName
from Class join Student on Class.ClassId = Student.ClassId
go

-- Hien thi tat ca lop hoc
select Class.ClassId, Class.ClassCode, Student.StudentName
from Class left join Student on Class.ClassId = Student.ClassId
go

-- Hien thi tat sinh vien ra -> TH ko co Class -> NULL
select Class.ClassId, Class.ClassCode, Student.StudentName
from Class right join Student on Class.ClassId = Student.ClassId
go

select Class.ClassId, Class.ClassCode, Student.StudentName
from Class full outer join Student on Class.ClassId = Student.ClassId
go

-- Dem so sinh vien trong lop hoc -> Lop khong co sinh vien -> fill 0
---- ClassId, ClassCode, Total
select Class.ClassId, Class.ClassCode, COUNT(Student.StudentId) 'Total'
from Class left join Student on Class.ClassId = Student.ClassId
group by Class.ClassId, Class.ClassCode
go