Bài tập - Thiết kế CSDL - sử dụng trong giải đấu bóng đá FIFA - champions league - world cup - Lập trình SQL Server

Nôi dung kiến thức:	
	- Chữa bài tập
	- Nói qua lý thuyết -> giới thiệu -> Azure SQL -> SEM3
	- Tìm hiểu index trong thiết kế CSDL
		-> clustered index <-> primary key -> duy 1 ->
		-> non-clustered index -> tao nhieu dc
	- Xây dựng & thiết kế CSDL
==============================================================

-- Tao CSDL
create database BT2243
go

-- Kich hoat CSDL
use BT2243
go

-- Tao tables
create table TrongTai (
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	address nvarchar(200) not null,
	level float,
	exp date
)
go

create table DoiBong (
	id int primary key identity(1,1),
	name nvarchar(50),
	studium_name nvarchar(100),
	trainer nvarchar(50)
)
go

create table LichSu (
	id int primary key identity(1,1),
	id_trongtai int references TrongTai(id),
	giaidau nvarchar(100) not null,
	ngaybat datetime,
	rate float,
	id_club_1 int references DoiBong (id),
	id_club_2 int references DoiBong (id),
	note nvarchar(500)
)
go

create clustered index index_giaidau on LichSu (giaidau)
go

create nonclustered index index_giaidau on LichSu (giaidau)
go

drop index index_giaidau on LichSu

create table CauThu (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	salary money,
	joined_date date
)
go

create table FootballMembers (
	id_club int references DoiBong(id),
	id_player int references CauThu(id),
	joined_date date,
	primary key (id_club, id_player)
)
go

-- Insert data
insert into TrongTai (fullname, address, level, exp)
values
('TRONG TAI 1', 'HA NOI', 20, '2001-02-18'),
('TRONG TAI 2', 'HA NOI', 12, '2006-06-18'),
('TRONG TAI 3', 'HA NOI', 7, '2012-02-18'),
('TRONG TAI 4', 'HA NOI', 9, '2010-03-18'),
('TRONG TAI 5', 'HA NOI', 30, '2000-02-18')
go

insert into CauThu (fullname, birthday, joined_date, salary)
values
('CAU THU 1', '2001-02-16', '2007-06-08', 30000),
('CAU THU 2', '2002-02-16', '2006-06-08', 30000),
('CAU THU 3', '2003-02-16', '2008-06-08', 30000),
('CAU THU 4', '2004-02-16', '2015-06-08', 30000),
('CAU THU 5', '2005-02-16', '2015-06-08', 30000),
('CAU THU 6', '2002-02-16', '2007-06-08', 30000),
('CAU THU 7', '2002-02-16', '2007-06-08', 30000)
go

insert into DoiBong (name, studium_name, trainer)
values
('DOI BONG 1', 'SAN BONG 1', 'TRAINER 01'),
('DOI BONG 2', 'SAN BONG 2', 'TRAINER 02'),
('DOI BONG 3', 'SAN BONG 3', 'TRAINER 03')
go

insert into FootballMembers (id_club, id_player, joined_date)
values
(1, 1, '2018-06-12'),
(1, 2, '2019-06-12'),
(1, 3, '2016-06-12'),
(2, 4, '2017-06-12'),
(2, 5, '2019-06-12'),
(3, 6, '2018-06-12'),
(3, 7, '2018-06-12')
go

insert into LichSu (id_club_1, id_club_2, id_trongtai, giaidau, ngaybat, rate)
values
(1, 2, 1, 'World Cup 1', '1996-02-12',4),
(2, 1, 1, 'World Cup 1', '1996-02-12',4),
(1, 3, 1, 'World Cup 2', '2000-02-12',5),
(2, 3, 1, 'World Cup 2', '2000-02-12',5),
(1, 2, 1, 'World Cup 3', '2004-02-12',4),
(2, 3, 1, 'World Cup 4', '2004-02-12',4)
go

-- test ket qua
select * from TrongTai
select * from DoiBong
select * from CauThu
select * from FootballMembers
select * from LichSu
go

--  Xem thông tin lịch sử bắt của trọng tài - tên trọng tài (TrongTai), level (TrongTai), exp (TrongTai), giải bóng (LichSu), đội 1 (DoiBong), đội 2 (DoiBong)
---- where, join (inner join), left join, right join
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau, 
	LichSu.id_club_1, LichSu.id_club_2
from TrongTai, LichSu
where LichSu.id_trongtai = TrongTai.id
go

select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau, 
	LichSu.id_club_1, DoiBong.name 'Doi Bong 1',
	LichSu.id_club_2, DoiBong.name 'DoiBong 2'
from TrongTai, LichSu, DoiBong
where LichSu.id_trongtai = TrongTai.id
	and DoiBong.id = LichSu.id_club_1
go

select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau, 
	LichSu.id_club_1, DoiBong.name 'Doi Bong 1',
	LichSu.id_club_2, DoiBong.name 'DoiBong 2'
from TrongTai, LichSu, DoiBong
where LichSu.id_trongtai = TrongTai.id
	and DoiBong.id = LichSu.id_club_2
go

select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.giaidau, 
	LichSu.id_club_1, DB1.name 'Doi Bong 1',
	LichSu.id_club_2, DB2.name 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where LichSu.id_trongtai = TrongTai.id
	and DB2.id = LichSu.id_club_2
	and DB1.id = LichSu.id_club_1
go

-- Xem danh sách cầu thủ của 1 đội bóng (Ten Doi Bong - DoiBong, Ten Cau Thu - CauThu, Ngay Tham Gia (FootballMembers))
select DoiBong.name 'Ten Doi Bong', CauThu.fullname, FootballMembers.joined_date
from DoiBong, CauThu, FootballMembers
where DoiBong.id = FootballMembers.id_club
	and CauThu.id = FootballMembers.id_player
go

select DoiBong.name 'Ten Doi Bong', CauThu.fullname, FootballMembers.joined_date
from DoiBong left join FootballMembers on DoiBong.id = FootballMembers.id_club
	left join CauThu on CauThu.id = FootballMembers.id_player
go

-- where, join, left join, right join, full outer join, ...
-- 10K, 100K, 1M, 2M, 100M -> ???
select * from LichSu

select * from LichSu
where giaidau = 'World Cup 1'
go