Nội dung học:
	- Tạo CSDL -> kích hoạt CSDL -> ???
	- Tạo bảng:
		- Kiểu dữ liệu đặc biêt: money, datetime, date, ... => (1)
		- primary key
		- identity
		- not null
		- default
		- unique -> ???
	- Alter table: Thêm/sửa/xóa column trong bảng đã tạo trước đó -> (4)
	- Drop -> (3)
	- Insert -> (2)
	- Delete
	- Update
	- Select -> (5)
		- Top
		- Distinct
		- Where cơ bản
		- Order By (asc, desc)
===================================================================
Mini Project: Quản lý thông tin sinh viên

Phân tích trường dữ liệu sẽ sử dụng trong dự án trên:
- Bảng: Student
	- rollno -> nvarchar(12)
	- fullname -> nvarchar(50)
	- birthday -> date
	- address -> nvarchar(200)
	- salary -> money

-- Tao bang Student
create table Student (
	rollno nvarchar(12),
	fullname nvarchar(50),
	birthday date,
	address nvarchar(200),
	salary money -- giong viec su dung int, bigint, ...
)
go

-- Insert du lieu
-- date: yyyy-mm-dd (1999-02-22) & datetime: yyyy-mm-dd HH:ii:ss (2020-06-16 13:20:19)
insert into Student(rollno, fullname, birthday, address, salary)
values
('R001', 'TRAN VAN A', '1999-02-22', 'Ha Noi', 200000)
go

insert into Student(rollno, fullname, birthday, address, salary)
values
('R002', 'TRAN VAN B', '1999-06-22', 'Ha Noi', 200000),
('R003', 'TRAN VAN C', '1999-08-22', 'Ha Noi', 600000),
('R004', 'TRAN VAN D', '1999-03-20', 'Nam Dinh', 300000)
go

-- Select basic
-- * -> co nghia la hien thi tat ca cac columns trong bang Student
select * from Student
go

-- TH: Hien thi cac column: rollno, fullname, address
select rollno, fullname, address
from Student
go

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

-- PRIMARY KEY
select * from Student
go

insert into Student(rollno, fullname, birthday, address, salary)
values
('R001', 'TRAN VAN E', '1998-10-22', 'Ha Noi', 500000)
go

---- Luu y: trong thuc te -> moi 1 sinh vien -> se co 1 rollno
---- Neu xay ra TH 2 sinh vien co cung msv -> du lieu dang gap error
---- Neu xay ra TH tren -> thiet ke CSDL sai -> ko chinh xac
---- Bai toan dat ra: co cach nao -> giup ko cho phep thiet ke du lieu trung
---- neu column (rollno) -> du lieu duy nhat -> ko bi trung lap + ko dc de trong -> primary key
-- Xoa bang
drop table Student
go

create table Student (
	rollno nvarchar(12) primary key,
	fullname nvarchar(50),
	birthday date,--null
	address nvarchar(200),--null
	salary money -- giong viec su dung int, bigint, ...--null
)
go

insert into Student(rollno, fullname, birthday, address, salary)
values
('R005', 'TRAN VAN E', '1998-10-22', 'Ha Noi', 500000)
go

insert into Student(rollno)
values
('R006')
go

insert into Student(rollno)
values
('R007')
go

-- not null
create table Student (
	rollno nvarchar(12) primary key,
	fullname nvarchar(50) not null,
	birthday date,--null
	address nvarchar(200),--null
	salary money -- giong viec su dung int, bigint, ...--null
)
go

insert into Student(rollno, fullname)
values
('R006', 'TRAN VAN G')
go

-- default
create table Student (
	rollno nvarchar(12) primary key,
	fullname nvarchar(50) not null,
	birthday date,--null
	address nvarchar(200),--null
	salary money default 0
)
go

select * from Student

-- Alter table: Thêm/sửa/xóa column trong bảng đã tạo trước đó
-- Them 1 email -> nvarchar(150)
alter table Student
add email nvarchar(150)
go

alter table Student
add x nvarchar(20)
go

-- Xoa 1 coulumn ko su dung trong bang
alter table Student
drop column x
go

-- Them 1 column phone_number vao bang
alter table Student
add phone_number int
go

-- Trong thuc te datatype -> phone_number: nvarchar(16)

insert into Student(rollno, fullname, birthday, address, salary, email, phone_number)
values
('R007', 'TRAN VAN K', '1999-02-22', 'Ha Noi', 200000, 'tranvank@gmail.com', 0967025996)
go

alter table Student
alter column phone_number nvarchar(16)
go

insert into Student(rollno, fullname, birthday, address, salary, email, phone_number)
values
('R008', 'TRAN VAN H', '1999-02-22', 'Ha Noi', 200000, 'tranvanh@gmail.com', '+84967025996')
go

-- Tim hieu select
select * from Student

-- Hien thi nhung sinh vien co salary = 200000
select * from Student
where salary = 200000
go

-- Hien thi nhung sinh vien co salary = 200000 & birthday = 1999-06-22
select * from Student
where salary = 200000 
	and birthday = '1999-06-22'
go

-- Hien thi sinh vien salary >= 200000 & salary <= 300000
select * from Student
where salary >= 200000 and salary <= 300000
go

-- Viet cach khac
select * from Student
where salary between 200000 and 300000
go

-- Hien thi sinh vien salary >= 200000 & salary <= 300000 -> Hien thi luong giam dan
select * from Student
where salary >= 200000 and salary <= 300000
order by salary desc
go

select * from Student
where salary >= 200000 and salary <= 300000
order by salary asc
go

-- Hien thi sinh vien salary >= 200000 & salary <= 300000 -> Hien thi luong giam dan -> Lay ra 3 phan tu dau tien
select top(3) * from Student
where salary >= 200000 and salary <= 300000
order by salary asc
go

-- Check null & not null
select * from Student

-- Hien thi tat ca cac sinh vien phone_number not null
select * from Student
where phone_number is not null
go

select * from Student
where phone_number is null
go

-- Hien thi tat ca cac tinh ma sinh vien dang tham gia hoc
select address from Student

-- Loai cac du lieu duplicate
select distinct address from Student

-- Update
select * from Student

--Sua thong tin sinh: rollno = R006 -> birthday = 1998-02-12, address = Ninh Binh, email g@gmail.com, phone_number = 123
update Student set phone_number = '234'

update Student set birthday = '1998-02-12', address = 'Ninh Binh', email = 'g@gmail.com', phone_number = '123'
where rollno = 'R006'
go

-- Xoa du lieu
delete from Student

delete from Student
where rollno = 'R007'
go

drop table ten_bang
drop database C2108G3

-- Identity -> int, smallint, ...
create table Product (
	id int primary key,
	title nvarchar(200) not null,
	des text
)
go

select * from Product

insert into Product (id, title, des)
values
(1, 'Vi du 1', 'okok'),
(2, 'Vi du 2', 'okok')
go

drop table Product

create table Product (
	id int primary key identity(1,1),
	title nvarchar(200) not null,
	des text
)
go

-- TH: id -> int -> primary key : Thong thuong trong TH nay id -> du lieu tu dien vao
insert into Product (title, des)
values
('Vi du 1', 'okok'),
('Vi du 2', 'okok')
go

delete from Product