Nội dung học
- Tạo bảng
	- Kiểu dữ liệu của column
	- not null
	- primary key

	- alter: Khi table da dc tao xong truoc do
		- Them column moi vao trong bang -> add
		- Sua kieu du lieu cua column -> alter column
		- Xoa column -> drop column ten_column
	- identity

- Select
	- Căn bản
	- Điều kiện
		- Căn bản
	- Distinct
	- Top
- Drop table
- Update
- Delete

============================================================
Mini Project:
	- Quản lý sinh viên
	- Quản lý thư viện

1) B1: Tạo tên CSDL -> C2110I
2) B2: Kích hoạt CSDL -> Viết lệnh -> Nhận đc
3) Phan tich CSDL
	Quản lý sinh viên: Student -> Table
		- rollno => nvarchar(16) -> not null
		- fullname -> nvarchar(50)
		- email -> nvarchar(150)
		- phone_number -> nvarchar(16)
		- birthday -> date
		- address -> nvarchar(200)
		...

		- gender: nvarchar(16)
	Quan ly thu vien:
		- Category: Danh muc sach
			- category_id: int primary key
			- name: nvarchar(50)
		- Book: Sach
			- book_id: int primary key
			- title: nvarchar(150)
			- price: float
			- category_id: int

-- Tao CSDL : C2110I
create database C2110I
go

-- Kich hoat su dung CSDL
use C2110I
go

-- Tao table Student
create table Student (
	rollno nvarchar(16) not null,
	fullname nvarchar(50),
	email nvarchar(150),
	phone_number nvarchar(16),
	birthday date,
	address nvarchar(200)
)
go

insert into Student(rollno, fullname, email, phone_number, birthday, address)
values
('R001', 'A', 'a@gmail.com', '123456', '1999-02-22', 'Ha Noi')
go

select * from Student

insert into Student(rollno, fullname)
values
('R002', 'B')
go

-- Test: Not null
insert into Student(fullname) -- rollno: not null -> khong dc de trong khi insert -> error
values
('C')
go

select * from Student

insert into Student(rollno, fullname, email, phone_number, birthday, address)
values
('R001', 'C', 'c@gmail.com', '2123213', '1998-02-22', 'Ha Noi')
go

-- Lenh chay thanh cong -> ko gap van de j ca
-- Nhan xet: rollno -> ton tai 2 R001 cua 2 sinh vien khac nhau
---- Trong thuc te: khong ton tai 2 sinh vien co cung rollno, cmtnd -> ko trung, email -> ko de trung
---- Them du lieu bi sai -> ko chinh xac
-- Co cach de giai quyet van de nay:
---- 1) Ko cho phep insert rollno trung nhau
---- 2) Thiet ke table trong CSDL -> moi 1 table thiet ke -> can co 1 column thoa man dieu kien sau
------- Ko dc de trong
------- Duy nhat trong bang
---- TH thiet ke nhu tren -> column -> primary key
-- Xoa bang di & lam lai
drop table Student
go

create table Student (
	rollno nvarchar(16) primary key,
	fullname nvarchar(50),
	email nvarchar(150),
	phone_number nvarchar(16),
	birthday date,
	address nvarchar(200)
)
go

-- Insert lai
insert into Student(rollno, fullname, email, phone_number, birthday, address)
values
('R001', 'A', 'a@gmail.com', '123456', '1999-02-22', 'Ha Noi')
go

insert into Student(rollno, fullname)
values
('R002', 'B')
go

select * from Student

-- Duplicate R001 -> do thiet ke primary key (rollno)
insert into Student(rollno, fullname, email, phone_number, birthday, address)
values
('R001', 'C', 'c@gmail.com', '2123213', '1998-02-22', 'Ha Noi')
go

drop table Student

create table Student (
	rollno nvarchar(16) not null,
	fullname nvarchar(50),
	email nvarchar(150),
	phone_number nvarchar(16),
	birthday date,
	address nvarchar(200),
	primary key (rollno)
)
go


create table Student (
	rollno nvarchar(16) not null,
	fullname nvarchar(50),
	email nvarchar(150),
	phone_number nvarchar(16),
	birthday date,
	address nvarchar(200),
	constraint PK_RollNo primary key (rollno)
)
go

create table Student (
	rollno nvarchar(16) not null,
	fullname nvarchar(50),
	email nvarchar(150),
	phone_number nvarchar(16),
	birthday date,
	address nvarchar(200)
)
go

alter table Student
add constraint PK_Student primary key (rollno)
go

-- Them column gender vao trong table: Student
alter table Student
add gender int
go

select * from Student

-- Sua lai kieu du lieu gender -> nvarchar(16)
alter table Student
alter column gender nvarchar(16)
go

-- Xoa column khoi table
alter table Student
drop column gender
go

-- Tao bang Category
create table Category (
	category_id int primary key, -- dien gia tri cho column -> tu dong dien vao -> Lam the nao
	name nvarchar(50)
)
go

select * from Category

insert into Category(category_id, name)
values
(1, 'DM A'),
(2, 'DM B')
go

drop table Category

create table Category (
	category_id int primary key identity(1,1),
	name nvarchar(50)
)
go

insert into Category(name)
values
('DM A'),
('DM B')
go

-- Tim hieu Select
select * from Student

insert into Student(rollno, fullname, email, phone_number, birthday, address)
values
('R002', 'A', 'a@gmail.com', '345435', '1988-02-22', 'Nam Dinh'),
('R003', 'B', 'b@gmail.com', '345435', '1996-12-20', 'Ha Noi'),
('R004', 'D', 'd@gmail.com', '345435', '1997-10-22', 'Nam Dinh'),
('R005', 'E', 'e@gmail.com', '345435', '1989-06-12', 'Ninh Binh')
go

-- *: Hien thi tat ca cac column trong bang
-- Yeu cau: Hien thi rollno, fullname, address
select rollno, fullname, address
from Student
go

-- Yeu cau: Hien thi rollno -> MSV, fullname -> Ho & Ten, address -> Dia Chi
select rollno as MSV, fullname as 'Ho & Ten', address 'Dia Chi'
from Student
go

-- Sap xep: address (asc: a-z)
select rollno as MSV, fullname as 'Ho & Ten', address 'Dia Chi'
from Student
order by address asc
go

select rollno as MSV, fullname as 'Ho & Ten', address 'Dia Chi'
from Student
order by address desc
go

-- Top
select top(2) rollno as MSV, fullname as 'Ho & Ten', address 'Dia Chi'
from Student
order by address desc
go

select top(2) rollno as MSV, fullname as 'Ho & Ten', address 'Dia Chi'
from Student
order by address asc
go

-- distinct: Loai cac ban ghi giong -> giu lai 1 thoi
select distinct address from Student

-- where: Hien thi sinh vien que Nam Dinh
select *
from Student
where address = 'Nam Dinh'

-- where: Hien thi sinh vien sinh nam 1997-01-01 toi 1999-12-30
select *
from Student
where birthday >= '1997-01-01' and birthday <= '1999-12-30'
go

-- Viet theo cach khac nhu sau
-- date, datetime su dung nhu int, smallint, bigint, float, money
select *
from Student
where birthday between '1997-01-01' and '1999-12-30'
go

-- where: Hien thi sinh vien address bat dau bang 'Nam'
select *
from Student
where address like 'Nam%'

-- where: Hien thi sinh vien address ket thuc bang Dinh
select *
from Student
where address like '%Dinh'

-- where: Hien thi sinh vien co address chua ky tu n
select *
from Student
where address like '%n%'

-- Xoa du lieu
-- Xoa sinh vien co rollno: R005
delete from Student
where rollno = 'R005'
go

delete from Student

-- Update: Sua du lieu sinh vien -> phone_number: 0967025996, email: gokisoft.com@gmail.com cho sinh vien R002
update Student set phone_number = '0967025996', email = 'gokisoft.com@gmail.com'
where rollno = 'R002'
go

update Student set phone_number = '123456'

-- phone_number: '' -> data that