- Tao database
- Table
	- Tao table
		- Them 1 column vao table
		- xoa 1 column khoi bang
		- Sua kieu du lieu table
		- constraint cho cac column
		- primary key => ???
	- Xoa table
	- insert & update
	- select
==================================================================
Tạo 1 bảng đặt tên: student_management_c2009i
Tạo 1 bảng Student
	- fullname => nvarchar(50)
	- rollno => nvarchar(20) -> duy nhat -> ko trung -> primary key (unique + not null)
	- phone => nvarchar(20) -> duy nhat
	- address => nvarchar(200)
	- email => nvarchar(150) -> duy nhat
	- gender => nvarchar(10)
	- birthday => date
	- married_status => nvarchar(20)

-- Tao database -> student_management_c2009i
create database student_management_c2009i
go

-- Active database
use student_management_c2009i
go

-- Tao table -> Student
create table Student (
	rollno nvarchar(20),
	fullname nvarchar(50),
	phone nvarchar(20),
	address nvarchar(200),
	email nvarchar(150),
	gender nvarchar(10),
	birthday date
)

select * from Student

insert into Student(rollno, fullname, phone, address, email, gender, birthday)
values
('R001', 'TRAN VAN A', '1234567', 'Ha Noi', 'tranvana@gmail.com', 'Nam', '1980-02-08')


insert into Student(rollno, fullname, phone, address, email, gender, birthday)
values
('R002', 'TRAN VAN B', '234234', 'Ha Noi', 'tranvanb@gmail.com', 'Nam', '1988-02-08')

-- Drop table
drop table Student

-- Thiet ke lai bang
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50),
	phone nvarchar(20) unique,
	address nvarchar(200),
	email nvarchar(150) unique,
	gender nvarchar(10),
	birthday date
)

select * from Student

-- Them 1 column moi vao bang Student => married_status
alter table Student
add married_status nvarchar(20)


insert into Student(rollno, fullname, phone, address, email, gender, birthday, married_status)
values
('R003', 'TRAN VAN C', '56546546', 'Ha Noi', 'tranvanc@gmail.com', 'Nam', '1988-02-08', 'Single'),
('R004', 'TRAN VAN D', '56546346', 'Ha Noi', 'tranvand@gmail.com', 'Nam', '1988-02-08', 'Single'),
('R005', 'TRAN VAN E', '56565565', 'Ha Noi', 'tranvane@gmail.com', 'Nam', '1988-02-08', 'Single')

alter table Student
add note nvarchar(200)

-- Xoa 1 column khoi table
alter table Student
drop column note

-- Add nham datatype cua column
alter table Student
add note int

-- Sua datatype cua column
alter table Student
alter column note nvarchar(20)

select * from Student

-- Sua du lieu -> update du lieu
update Student set married_status = 'Married'

-- Sua lai R003, R004, R005 => married_status = 'Single'
update Student set married_status = 'Single' where rollno = 'R003'
update Student set married_status = 'Single' where rollno in ('R004', 'R005')

-- Xoa du lieu => R004, R005 => Xoa khoi table
delete from Student where rollno in ('R004', 'R005')

-- Select
select * from Student

-- Select -> chi gom 3 column
select rollno, fullname, birthday from Student

-- Select -> chi gom 3 column: rollno => 'MSV', fullname => 'Ho & Ten', birthday => 'Ngay Sinh'
select rollno as 'MSV', fullname 'Ho & Ten', birthday 'Ngay Sinh' from Student

-- Hien thi danh sach sinh vien co ten la TRAN VAN A
select * from Student where fullname = 'TRAN VAN A'

-- Hien thi danh sach sinh vien ten co chua chu TRAN
select * from Student where fullname like '%TRAN%'
select * from Student where fullname like 'TRAN%'
select * from Student where fullname like '%TRAN'

-- Hien thi sinh vien co ngay sinh 1984-01-01 -> 1990-01-01
select * from Student where birthday >= '1984-01-01' and birthday <= '1990-01-01'
-- Doi sang cau lenh khac
select * from Student where birthday between '1984-01-01' and '1990-01-01'
-- Hien thi sinh vien co ngay sinh 1984-01-01 -> 1990-01-01 & married_status = 'Single'
select * from Student
	where birthday >= '1984-01-01' and birthday <= '1990-01-01'
		and married_status = 'Single'