Nội dung kiến thức học:
	Select
		- inner join
		- left join
		- right join
		- full outer join

-- Tao tables
create table Student (
	id int primary key identity(1,1),
	fullname nvarchar(50)
)
go

create table Subject (
	id int primary key identity(1,1),
	subject_name nvarchar(50)
)
go

create table Marks (
	student_id int not null,
	subject_id int not null,
	mark float,
	primary key (student_id, subject_id)
)
go

alter table Marks
add constraint fk_student_id foreign key (student_id) references Student(id)
go

alter table Marks
add constraint fk_subject_id foreign key (subject_id) references Subject(id)
go

-- insert data
insert into Student (fullname)
values
('A'),
('B')
go

insert into Subject (subject_name)
values
('Lap Trinh C'),
('HTML/CSS/JS')
go

insert into Marks (student_id, subject_id, mark)
values
(1, 1, 8),
(1, 2, 10),
(2, 1, 6),
(2, 2, 7)
go

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

-- where giua 2 bang
select Student.fullname, Marks.subject_id, Marks.mark
from Student, Marks
where Student.id = Marks.student_id
go

-- Chung co the su dung bang cach khac nhu sau
select Student.fullname, Marks.subject_id, Marks.mark
from Student inner join Marks on Student.id = Marks.student_id
go

select Student.fullname, Marks.subject_id, Marks.mark
from Student join Marks on Student.id = Marks.student_id
go

-- left join
select Student.fullname, Marks.subject_id, Marks.mark
from Student left join Marks on Student.id = Marks.student_id
go

-- right join
select Student.fullname, Marks.subject_id, Marks.mark
from Student right join Marks on Student.id = Marks.student_id
go

select Student.fullname, Marks.subject_id, Marks.mark
from Marks right join Student on Student.id = Marks.student_id
go

-- full outer join
select Student.fullname, Marks.subject_id, Marks.mark
from Student full outer join Marks on Student.id = Marks.student_id
go

-- Bo sung du lieu
insert into Student (fullname)
values
('D'),
('E')
go

-- Xem tung sinh vien da co bao nhieu mon co diem thi
select Student.fullname, count(Marks.subject_id) 'So Mon Thi'
from Student, Marks
where Student.id = Marks.student_id
group by Student.fullname
go

select Student.fullname, count(Marks.subject_id) 'So Mon Thi'
from Student left join Marks on Student.id = Marks.student_id
group by Student.fullname
go

select Student.id, Student.fullname, count(Marks.subject_id)
from Student left join Marks on Student.id = Marks.student_id
group by Student.id, Student.fullname
go