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

-- Thiet ke bang
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 LichSu (
	id int primary key identity(1,1),
	id_trongtai int,
	match nvarchar(50),
	start_date datetime,
	rate float,
	id_club_1 int,
	id_club_2 int,
	note nvarchar(200)
)
go

create table DoiBong (
	id int primary key identity(1,1),
	team_name nvarchar(50),
	studium_name nvarchar(50),
	coacher nvarchar(50)
)
go

create table CauThu (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	salary float,
	join_date date
)
go

create table ThongTinDoiBong (
	id_club int not null,
	id_player int not null,
	join_date datetime,
	primary key (id_club, id_player)
)
go

-- Thiet ke foreign key
alter table LichSu
add constraint fk_lichsu_trongtai foreign key (id_trongtai) references TrongTai(id)
go

alter table LichSu
add constraint fk_lichsu_club_1 foreign key (id_club_1) references DoiBong (id)
go

alter table LichSu
add constraint fk_lichsu_club_2 foreign key (id_club_2) references DoiBong (id)
go

alter table ThongTinDoiBong
add constraint fk_thongtindoibong_club foreign key (id_club) references DoiBong (id)
go

alter table ThongTinDoiBong
add constraint fk_thongtindoibong_player foreign key (id_player) references CauThu (id)
go

-- insert data
insert into TrongTai (fullname, address, level, exp)
values
('Trong Tai A', 'Ha Noi', 5, '2017-02-12'),
('Trong Tai B', 'Ha Noi', 2, '2020-08-12'),
('Trong Tai C', 'Ha Noi', 3, '2019-03-12')
go

insert into CauThu (fullname, join_date, salary)
values
('Cau Thu 1', '2016-02-18', 30000),
('Cau Thu 2', '2017-06-18', 20000),
('Cau Thu 3', '2018-01-18', 50000),
('Cau Thu 4', '2015-09-18', 80000),
('Cau Thu 5', '2019-10-18', 10000)
go

insert into DoiBong (team_name, studium_name, coacher)
values
('Doi Bong A', 'AAA', 'HLV A'),
('Doi Bong B', 'BBB', 'HLV B'),
('Doi Bong C', 'CCC', 'HLV C')
go

insert into ThongTinDoiBong (id_club, id_player, join_date)
values
(1, 6, '2019-01-12'),
(1, 7, '2019-01-12'),
(2, 8, '2019-01-12'),
(2, 9, '2019-01-12'),
(2, 10, '2019-01-12')
go

insert into LichSu (id_trongtai, id_club_1, id_club_2, match, rate, start_date, note)
values
(1, 1, 2, 'AAA', 4, '2022-02-16', 'ABC'),
(1, 2, 1, 'BBB', 5, '2021-02-16', 'ABC'),
(2, 1, 3, 'CCC', 5, '2022-06-16', 'ABC'),
(2, 2, 3, 'DDD', 4, '2022-08-16', 'ABC'),
(3, 1, 2, 'EEE', 2, '2022-03-16', 'ABC')
go

select * from TrongTai
select * from CauThu
select * from DoiBong

-- Query
---- 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)
select TrongTai.fullname 'Ten Trong Tai', TrongTai.exp, LichSu.match, DB1.team_name 'Doi Bong 1', DB2.team_name 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where TrongTai.id = LichSu.id_trongtai
	and LichSu.id_club_1 = DB1.id
	and LichSu.id_club_2 = DB2.id
go

create view vw_history
as
select TrongTai.fullname 'Ten Trong Tai', TrongTai.exp, LichSu.match, DB1.team_name 'Doi Bong 1', DB2.team_name 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where TrongTai.id = LichSu.id_trongtai
	and LichSu.id_club_1 = DB1.id
	and LichSu.id_club_2 = DB2.id
go

select * from vw_history
go

-- Xem thong tin cau thu cua doi bong
---- ID (DoiBong), ten doi bong (DoiBong), ten cau thu (CauThu), luong (CauThu), ngay tham gia (ThongTinDoiBong)
select DoiBong.id, DoiBong.team_name, CauThu.fullname, CauThu.salary, ThongTinDoiBong.join_date
from DoiBong, CauThu, ThongTinDoiBong
where DoiBong.id = ThongTinDoiBong.id_club
	and CauThu.id = ThongTinDoiBong.id_player
go

create proc pr_view_thongtindoibong
	@id_club int
as
begin
	select DoiBong.id, DoiBong.team_name, CauThu.fullname, CauThu.salary, ThongTinDoiBong.join_date
	from DoiBong, CauThu, ThongTinDoiBong
	where DoiBong.id = ThongTinDoiBong.id_club
		and CauThu.id = ThongTinDoiBong.id_player
		and DoiBong.id = @id_club
end

exec pr_view_thongtindoibong 1
exec pr_view_thongtindoibong 2
exec pr_view_thongtindoibong 3

select * from LichSu
go

-- record: 100,000 -> 1 trieu ban ghi --> truy van cuc cham.
---- query: insert, update, delete -> ko anh huong & it khi dc su
----        select -> lay du lieu ->  duoc su nhieu nhat trong he thong
---- index, partition
---- index: clustered index (primary key) & none-clustered index (nhieu fields)
create index idx_lichsu_match on LichSu (match)

create clustered index idx_lichsu_match_2 on LichSu (match)

create nonclustered index idx_lichsu_match_3 on LichSu (match)

drop index idx_lichsu_match_3 on LichSu

-- Trigger
select * from CauThu
select * from DoiBong
select * from ThongTinDoiBong

delete from CauThu where id = 6
delete from CauThu where id = 11
delete from CauThu where id = 14

-- trigger -> cai dat vao 1 hanh dong nao do trong SQL -> insert, update, delete
---- Cai trigger delete -> CauThu
alter trigger trigger_no_delete_id_11 on CauThu
for delete
as
begin
	if(select count(*) from deleted where id = 11) > 0
	begin
		print N'Khong dc xoa cau thu co ID = 11'
		rollback transaction
	end
end

alter trigger trigger_no_delete_id_11 on CauThu
for delete
as
begin
	print N'Khong dc xoa cau thu'

	rollback transaction
end

insert into CauThu (fullname, join_date, salary)
values
('Cau Thu 1', '2016-02-18', 60000)
go

alter table CauThu
add constraint check_salary check (salary >= 0)
go

-- trigger insert -> CauThu -> Khi chay cau lenh insert CauThu
alter trigger trigger_create_check_salary on CauThu
for insert
as
begin
	if(select count(*) from inserted where salary < 0) > 0
	begin
		print N'Khong dc chen du lieu salary < 0'
		rollback transaction
	end
end

alter trigger trigger_create_check_salary on CauThu
for insert
as
begin
	print N'Khong dc chen du lieu salary < 0'
	rollback transaction
end

select * from CauThu

update CauThu set salary = -60000 where id = 17

alter trigger trigger_update_check_salary on CauThu
for update
as
begin
	if(select count(*) from inserted where salary < 0) > 0
	begin
		print N'Khong dc cap nhat du lieu salary < 0'
		rollback transaction
	end
end

update CauThu set salary = -60000 where id = 17
--------------------------------------------------------------------------
select * from CauThu
select * from DoiBong
select * from ThongTinDoiBong

delete from CauThu where id = 6
delete from ThongTinDoiBong where id_player = 6

delete from ThongTinDoiBong where id_player = 7
delete from CauThu where id = 7

create trigger trigger_instead_of_delete on CauThu
instead of delete
as
begin
	delete from ThongTinDoiBong where id_player in (select id from deleted)
	delete from CauThu where id in (select id from deleted)
end

delete from CauThu where id = 7
delete from CauThu where id = 8
----------------------------------------------------------------------------