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

--BT2243
-- Tao database
create database BT2243
go

-- Kich hoat database
use BT2243
go

-- Tao bang trong tai
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),
	trongtai_id int,
	match_cup nvarchar(100),
	created_at date,
	rate float,
	id_club_1 int,
	id_club_2 int,
	note nvarchar(500)
)
go

create table DoiBong (
	id int primary key identity(1,1),
	team_name nvarchar(50),
	stadium_name nvarchar(100),
	trainer_name nvarchar(50)
)
go

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

create table ChiTietDoiBong (
	id_club int not null,
	id_player int not null,
	created_at date
)
go

-- Tao foreign key
alter table LichSu
add constraint fk_lichsu_trongtai foreign key (trongtai_id) 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 ChiTietDoiBong
add constraint fk_chitietdoibong_club foreign key (id_player) references CauThu(id)
go

alter table ChiTietDoiBong
add constraint fk_chitietdoibong_player foreign key (id_club) references DoiBong(id)
go

alter table ChiTietDoiBong
add constraint pk_club_player primary key (id_player, id_club)
go

-- Fake data
---- Trong Tai
insert into TrongTai(fullname, address, level, exp)
values
('Trong Tai A', 'HN', 1, '2006-02-16'),
('Trong Tai B', 'HN', 1, '2012-06-20')
go

insert into CauThu(fullname, birthday, salary, created_at)
values
('CT A', '1999-02-06', 3000, '2010-02-10'),
('CT B', '1998-08-16', 30000, '2010-12-11'),
('CT C', '1996-12-26', 7000, '2010-06-12'),
('CT D', '1997-06-12', 8000, '2010-11-20'),
('CT E', '1992-02-10', 9000, '2010-09-19')
go

insert into DoiBong (team_name, stadium_name, trainer_name)
values
('MU', 'A', 'A'),
('Liverpool', 'B', 'B'),
('Man City', 'C', 'C'),
('Arsenal', 'D', 'D'),
('E', 'E', 'E')
go

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

insert into ChiTietDoiBong(id_club, id_player, created_at)
values
(1, 1, '2008-10-10'),
(1, 2, '2008-10-10'),
(1, 3, '2008-10-10'),
(2, 4, '2008-10-10'),
(2, 5, '2008-10-10')
go

insert into LichSu (id_club_1, id_club_2, trongtai_id, match_cup, created_at, rate, note)
values
(1, 2, 1, 'AA', '2016-01-19', 5, ''),
(1, 3, 1, 'BA', '2016-02-16', 5, ''),
(2, 4, 2, 'CA', '2016-06-09', 5, ''),
(2, 3, 1, 'AD', '2016-08-29', 5, ''),
(3, 4, 2, 'EE', '2016-11-11', 5, '')
go

-- Xem thông tin lịch sử bắt của trọng tài - tên trọng tài, level, exp, giải bóng, đội 1, đội 2
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.match_cup, DB1.team_name 'Doi Bong 1', DB2.team_name 'Doi Bong 2'
from TrongTai left join LichSu on TrongTai.id = LichSu.trongtai_id
	left join DoiBong DB1 on DB1.id = LichSu.id_club_1
	left join DoiBong DB2 on DB2.id = LichSu.id_club_2
go

create view view_lichsu_dabong
as
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.match_cup, DB1.team_name 'Doi Bong 1', DB2.team_name 'Doi Bong 2'
from TrongTai left join LichSu on TrongTai.id = LichSu.trongtai_id
	left join DoiBong DB1 on DB1.id = LichSu.id_club_1
	left join DoiBong DB2 on DB2.id = LichSu.id_club_2
go

select * from view_lichsu_dabong
go

-- Xem danh sách cầu thủ của 1 đội bóng: ten doi bong, ten cau thu, ngay tham gia
select DoiBong.team_name, CauThu.fullname 'Ten Cau Thu', ChiTietDoiBong.created_at 'Ngay Tham Gia'
from DoiBong left join ChiTietDoiBong on DoiBong.id = ChiTietDoiBong.id_club
	left join CauThu on CauThu.id = ChiTietDoiBong.id_player
order by DoiBong.team_name asc
go

select DoiBong.team_name, CauThu.fullname 'Ten Cau Thu', ChiTietDoiBong.created_at 'Ngay Tham Gia'
from DoiBong join ChiTietDoiBong on DoiBong.id = ChiTietDoiBong.id_club
	join CauThu on CauThu.id = ChiTietDoiBong.id_player
order by DoiBong.team_name asc
go

create view view_thongtin_cauthu
as
select DoiBong.team_name, CauThu.fullname 'Ten Cau Thu', ChiTietDoiBong.created_at 'Ngay Tham Gia'
from DoiBong join ChiTietDoiBong on DoiBong.id = ChiTietDoiBong.id_club
	join CauThu on CauThu.id = ChiTietDoiBong.id_player
go

select * from view_thongtin_cauthu
order by team_name asc

--  Xem thông tin lịch sử bắt của trọng tài - tên trọng tài, level, exp, giải bóng, đội 1, đội 2 -> Tìm theo tên 1 đội bóng
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.match_cup, DB1.team_name 'Doi Bong 1', DB2.team_name 'Doi Bong 2'
from TrongTai left join LichSu on TrongTai.id = LichSu.trongtai_id
	left join DoiBong DB1 on DB1.id = LichSu.id_club_1
	left join DoiBong DB2 on DB2.id = LichSu.id_club_2
go

create proc proc_find_lichsu
	@club_id int
as
begin
	select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, LichSu.match_cup, DB1.team_name 'Doi Bong 1', DB2.team_name 'Doi Bong 2'
	from TrongTai left join LichSu on TrongTai.id = LichSu.trongtai_id
		left join DoiBong DB1 on DB1.id = LichSu.id_club_1
		left join DoiBong DB2 on DB2.id = LichSu.id_club_2
	where DB1.id = @club_id or DB2.id = @club_id
end

exec proc_find_lichsu 1
exec proc_find_lichsu 3