2023-12-13 14:14:45

#SQLQuery1.sql

-- Tao CSDL
create database C2307LSQL
go

-- Kich hoat CSDL
use C2307LSQL
go

-- Tao tables
create table category (
	name nvarchar(150)
)
go

insert into category(name)
values
('Danh muc A'),
('Danh muc B'),
('Danh muc C')
go

insert into category(name)
values
(null),
(null),
(null)
go

select * from category
go

-- Xoa du lieu
delete from category
go

-- Xoa table
drop table category
go

-- Tao tables
create table category (
	id int primary key,
	name nvarchar(150) not null
)
go

insert into category (id, name)
values
(1, 'Danh Muc A')
go

insert into category (id, name)
values
(1, 'Danh Muc A'),
(2, 'Danh Muc B'),
(3, 'Danh Muc C')
go

-- Xoa table
drop table category
go

-- Tao tables
create table category (
	id int primary key identity(1,1),
	name nvarchar(150) not null
)
go

insert into category(name)
values
('Danh Muc A'),
('Danh Muc B'),
('Danh Muc C')
go

select * from category
go

-- Xoa table
drop table category
go

-- Tao tables
create table category (
	id int primary key identity(1,1),
	name nvarchar(150) not null unique
)
go

insert into category(name)
values
('Danh Muc A'),
('Danh Muc B'),
('Danh Muc C')
go

select * from category
go

-- Tao bang products
create table products (
	id int primary key identity(1,1),
	title nvarchar(150) not null,
	price int,
	thumbnail nvarchar(500),
	category_id int
)
go

-- Bang products -> Thieu column: updated_at, created_at
alter table products
add created_at datetime
go

alter table products
add updated_at datetime
go

-- Bang products -> price: int -> price: float
alter table products
alter column price float
go

alter table products
add test datetime
go

alter table products
drop column test
go

insert into products(title, thumbnail, price, category_id, created_at, updated_at)
values
('San pham A', null, 1000, 1, '2023-10-23 8:30:00', '2023-10-23 8:30:00'),
('San pham B', null, 2000, 1, '2023-10-23 8:30:00', '2023-10-23 8:30:00'),
('San pham C', null, 5000, 2, '2023-10-23 8:30:00', '2023-10-23 8:30:00')
go

select * from products
go

-- Lay san pham thuoc danh muc 1
select * from products
where category_id = 1
go

-- Lay san pham co gia < 2000
select * from products
where price < 2000
go

-- Lay san pham co gia < 2000 va > 4000
select * from products
where price < 2000 or price > 4000
go

-- Lay san pham co gia tu 1500 toi 4000
select * from products
where price >= 1500 and price <= 4000
go

select * from products
where price between 1500 and 4000
go

-- Lay san pham co gia tu 1500 toi 4000, thuoc danh muc 1
select * from products
where (price between 1500 and 4000) and category_id = 1
go

select * from products
go

-- Xoa du lieu products
delete from products
go

-- Xoa du lieu products -> dieu kien id = 3
delete from products
where id = 3
go

-- Sua du lieu
select * from products

update products set price = 10
go

update products set price = 1000
where id = 4
go

-- Tim hieu primary key nhieu columns
create table students (
	student_id int primary key identity(1,1),
	fullname nvarchar(50),
	email nvarchar(150)
)
go
insert into students (fullname, email)
values
('A', 'a@gmail.com'),
('B', 'b@gmail.com')
go

create table subjects (
	subject_id int primary key identity(1,1),
	subject_name nvarchar(50)
)
go
insert into subjects (subject_name)
values
('SQL Server'),
('Lap Trinh C')
go

select * from students
select * from subjects
select * from marks

create table marks (
	subject_id int,
	student_id int,
	mark float,
	primary key(subject_id, student_id)
)
go

alter table marks
add constraint check_mark check (mark >= 0 and mark <= 10)
go

drop table marks

insert into marks(subject_id, student_id, mark)
values
(1, 1, 6),
(1, 2, 10)
go

insert into marks(subject_id, student_id, mark)
values
(2, 1, 8)
go

insert into marks(subject_id, student_id, mark)
values
(2, 2, -8)
go