Nội dung:
- Table
	- default
	- check
	- unique
	- foreign key
- update
- delete
	- Mệnh đề điều kiện
- select
	- Mệnh đề điều kiện
	- Nối nhiều bảng
		- where
		- join
			- left join
			- right join
			- inner join
			- full outer join

-- Active
use C2010L

-- Tao bang citizen
create table citizen (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	address nvarchar(200),
	gender nvarchar(20) default 'Other'
)

create table citizen (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	address nvarchar(200),
	gender nvarchar(20)
)

alter table citizen
add constraint default_gender default 'Other' for gender

alter table citizen
drop constraint default_gender

drop table citizen

insert into citizen(fullname, address)
values
('A', 'Ha Noi')

select * from citizen

-- unique
select * from Student

insert into Student(roll_number, student_name, email, address, phone)
values
('R003', 'TRAN VAN B', 'a@gmail.com', 'Nam Dinh', '1231232')

alter table Student
add constraint unique_email unique(email)

create table Student (
	roll_number nvarchar(20) primary key,
	student_name nvarchar(50) not null,
	email nvarchar(150) unique,
	address nvarchar(200),
	phone nvarchar(20)
)

select * from Marks

-- check
create table Marks (
	subject_id int,
	roll_number nvarchar(20),
	marks int,
	primary key (subject_id, roll_number),
	constraint check_marks check (marks between 0 and 10)
)

alter table Marks
add constraint check_marks check (marks between 0 and 10)

insert into Marks (subject_id, roll_number, marks)
values
(3, 'R002', 8)

-- marks >= 0 && marks <= 10

-- delete
delete from Marks where marks < 0

-- update
update Marks set marks = 9 where subject_id = 3 and roll_number = 'R002'

select * from Marks

-- Foreign key
select * from Student
select * from Subject
select * from Marks

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

insert into Marks (subject_id, roll_number, marks)
values
(3, 'R003', 7)

insert into Marks (subject_id, roll_number, marks)
values
(4, 'R002', 7)

delete from Marks where subject_id = 1

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

alter table Marks
add constraint fk_roll_number foreign key (roll_number) references Student (roll_number)

-- foreign key
create table Marks (
	subject_id int references Subject (subject_id),
	roll_number nvarchar(20),
	marks int,
	primary key (subject_id, roll_number),
	constraint check_marks check (marks between 0 and 10),
	constraint fk_roll_number foreign key (roll_number) references Student (roll_number)
)

-- Hien thi du lieu theo format sau: roll_number, student_name, marks
---- Student, Marks
select Student.roll_number, Student.student_name, Marks.marks
from Student, Marks
where Student.roll_number = Marks.roll_number

-- Hien thi du lieu theo format sau: roll_number, student_name, subject_name, marks
---- Student, Marks, Subject
select Student.roll_number, Student.student_name, Subject.subject_name, Marks.marks
from Student, Marks, Subject
where Student.roll_number = Marks.roll_number
	and Subject.subject_id = Marks.subject_id