Nội dung:
	- Thiết kế table
		- foreign key
		- constraint
	- Select
		- where: join nhieu tables
		- inner join
		- left join
		- right join
		- full outer join
		- order by

-- Create database
create database C2010G

-- Active database
use C2010G

-- Create tables
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	email nvarchar(200),
	gender nvarchar(16)
)

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

create table Marks (
	rollno nvarchar(20),
	subject_id int,
	marks float
)

--- Insert data
insert into Student (rollno, fullname, email, gender)
values
('R001', 'TRAN VAN A', 'a@gmail.com', 'Nam'),
('R002', 'TRAN VAN B', 'b@gmail.com', 'Nam')

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

insert into Marks(rollno, subject_id, marks)
values
('R001', 1, 7),
('R001', 5, 8),
('R002', 1, 7)

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

-- Bai toan: Thiet ke rollno trong Marks -> khong dc phep insert du lieu rollno khong chua trong bang Student vao Marks
insert into Marks(rollno, subject_id, marks)
values
('R003', 1, 7)

insert into Marks(rollno, subject_id, marks)
values
('R002', 10, 7)

insert into Marks(rollno, subject_id, marks)
values
('R020', 16, 7)

-- Cach thiet ke foreign key
-- Co 3 cach
---- Cach 1:
drop table Marks

create table Marks (
	rollno nvarchar(20) references Student (rollno),
	subject_id int,
	marks float
)

insert into Marks(rollno, subject_id, marks)
values
('R003', 2, 7)

insert into Marks(rollno, subject_id, marks)
values
('R001', 1, 7)

---- Cach 2:
drop table Marks

create table Marks (
	rollno nvarchar(20) references Student (rollno),
	subject_id int,
	marks float,
	constraint fk_subject_id foreign key (subject_id) references subject (id)
)

insert into Marks(rollno, subject_id, marks)
values
('R002', 1, 8)

insert into Marks(rollno, subject_id, marks)
values
('R002', 10, 8)

---- Cach 3:
drop table Marks

create table Marks (
	rollno nvarchar(20),
	subject_id int,
	marks float
)

alter table Marks
add constraint fk_rollno foreign key (rollno) references Student (rollno)

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

insert into Marks(rollno, subject_id, marks)
values
('R001', 1, 7),
('R002', 1, 7)

insert into Marks(rollno, subject_id, marks)
values
('R001', 2, 2)

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

delete from Subject where id = 1
delete from Subject where id = 2

-- Phan 2:
--- Hien thi du lieu theo yeu cau: rollno (Student), fullname (Student), marks (Marks)

select Student.rollno, Student.fullname, Marks.marks
from Student, Marks
where Student.rollno = Marks.rollno

--- Hien thi du lieu theo yeu cau: rollno (Student), fullname (Student), marks (Marks), subject_name (Subject)

select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id

-- Them dieu kien hien -> marks xep theo thu tu giam dan
select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id
order by Marks.marks desc

select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id
order by Marks.marks asc

select Student.rollno, Student.fullname, Marks.marks, Subject.subject_name
from Student, Marks, Subject
where Student.rollno = Marks.rollno
	and Subject.id = Marks.subject_id
order by Marks.marks

insert into Student (rollno, fullname, email, gender)
values
('R003', 'TRAN VAN C', 'c@gmail.com', 'Nam')

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

-- inner join
select Student.rollno, Student.fullname, Marks.marks
from Student, Marks
where Student.rollno = Marks.rollno
order by Student.rollno asc

-- Viet bang cach khac
select Student.rollno, Student.fullname, Marks.marks
from Student inner join Marks on Student.rollno = Marks.rollno
-- Student: table left, Marks -> table right

-- left join
select Student.rollno, Student.fullname, Marks.marks
from Student left join Marks on Student.rollno = Marks.rollno

-- right join
select Student.rollno, Student.fullname, Marks.marks
from Marks right join Student on Student.rollno = Marks.rollno

-- full outer join
select Student.rollno, Student.fullname, Marks.marks
from Student full outer join Marks on Student.rollno = Marks.rollno

-- group by
select * from Student
select * from Subject
select * from Marks

-- Hien thi du lieu dau ra: dem so mon hoc da co diem cua tung sinh vien
select Student.rollno, Student.fullname, count(Marks.subject_id) as 'Count'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, avg(Marks.marks) as 'Marks_AVG'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, min(Marks.marks) as 'Marks MIN'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, max(Marks.marks) as 'Marks MAX'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, sum(Marks.marks) as 'Marks SUM'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, 
	count(Marks.subject_id) as 'Count',  
	avg(Marks.marks) as 'Marks_AVG', 
	min(Marks.marks) as 'Marks MIN',
	max(Marks.marks) as 'Marks MAX',
	sum(Marks.marks) as 'Marks SUM'
from Student, Marks
where Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, 
	count(Marks.subject_id) as 'Count',  
	avg(Marks.marks) as 'Marks_AVG', 
	min(Marks.marks) as 'Marks MIN',
	max(Marks.marks) as 'Marks MAX',
	sum(Marks.marks) as 'Marks SUM'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname

select Student.rollno, Student.fullname, count(Marks.subject_id) as 'Count'
from Student left join Marks on Student.rollno = Marks.rollno
group by Student.rollno, Student.fullname
having count(Marks.subject_id) = 0