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

-- 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),
	address nvarchar(200),
	level float,
	exp date
)
go

create table LichSu (
	id int primary key identity(1,1),
	trongtai_id int,
	giaidau nvarchar(100),
	ngay_thi_dau 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),
	ten nvarchar(50),
	sanbong nvarchar(100),
	huanluyenvien nvarchar(50)
)
go

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

create table ThongTinDoiBong (
	id_club int,
	id_player int,
	joined_date date,
	primary key (id_club, id_player)
)
go

--------- 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 ThongTinDoiBong
add constraint fk_thongtincauthu_club foreign key (id_club) references DoiBong (id)
go

alter table ThongTinDoiBong
add constraint fk_thongtincauthu_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, '2016-02-16'),
('TRONG TAI B', 'Ha Noi', 5, '2016-02-16'),
('TRONG TAI C', 'Ha Noi', 5, '2016-02-16'),
('TRONG TAI D', 'Ha Noi', 5, '2016-02-16'),
('TRONG TAI E', 'Ha Noi', 5, '2016-02-16')
go

insert into CauThu (fullname, birthday, salary, startdate)
values
('CAU THU A', '1996-06-15', 5000, '2010-09-12'),
('CAU THU B', '1996-06-15', 5000, '2010-09-12'),
('CAU THU C', '1996-06-15', 5000, '2010-09-12'),
('CAU THU D', '1996-06-15', 5000, '2010-09-12'),
('CAU THU E', '1996-06-15', 5000, '2010-09-12')
go

insert into DoiBong (ten, sanbong, huanluyenvien)
values
('Doi Bong A', 'San Bong A', 'Huan Luyen Vien A'),
('Doi Bong B', 'San Bong B', 'Huan Luyen Vien B'),
('Doi Bong C', 'San Bong C', 'Huan Luyen Vien C')
go

insert into ThongTinDoiBong (id_club, id_player, joined_date)
values
(1, 1, '2012-02-06'),
(1, 2, '2012-02-06'),
(2, 3, '2012-02-06'),
(2, 4, '2012-02-06'),
(3, 5, '2012-02-06')
go

insert into LichSu (trongtai_id, giaidau, ngay_thi_dau, rate, id_club_1, id_club_2)
values
(1, 'A', '2020-01-16', 4, 1, 2),
(1, 'B', '2020-02-16', 5, 1, 3),
(2, 'C', '2020-03-16', 4, 2, 3),
(2, 'D', '2020-04-16', 5, 2, 1),
(3, 'E', '2020-05-16', 4, 3, 1),
(4, 'F', '2020-06-16', 4, 3, 2)
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 (LichSu), đội 2 (LichSu)
select TrongTai.fullname, TrongTai.level, TrongTai.exp, LichSu.giaidau, LichSu.id_club_1, LichSu.id_club_2
from TrongTai, LichSu
where TrongTai.id = LichSu.trongtai_id

select TrongTai.fullname, TrongTai.level, TrongTai.exp, LichSu.giaidau, LichSu.id_club_1, DB1.ten 'Doi Bong 1', LichSu.id_club_2, DB2.ten 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where TrongTai.id = LichSu.trongtai_id
	and DB1.id = LichSu.id_club_1
	and DB2.id = LichSu.id_club_2

select TrongTai.fullname, TrongTai.level, TrongTai.exp, LichSu.giaidau, DB1.ten 'Doi Bong 1', DB2.ten 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where TrongTai.id = LichSu.trongtai_id
	and DB1.id = LichSu.id_club_1
	and DB2.id = LichSu.id_club_2

create view view_lichsu_trongtai
as
select TrongTai.fullname, TrongTai.level, TrongTai.exp, LichSu.giaidau, DB1.ten 'Doi Bong 1', DB2.ten 'Doi Bong 2'
from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
where TrongTai.id = LichSu.trongtai_id
	and DB1.id = LichSu.id_club_1
	and DB2.id = LichSu.id_club_2

select * from view_lichsu_trongtai

-- Xem danh sách cầu thủ của 1 đội bóng
select DoiBong.ten 'Ten Doi Bong', CauThu.fullname 'Ten Cau Thu', CauThu.salary 'Luong'
from DoiBong, CauThu, ThongTinDoiBong
where DoiBong.id = ThongTinDoiBong.id_club
	and CauThu.id = ThongTinDoiBong.id_player

create view view_thongtincauthu
as
select DoiBong.ten 'Ten Doi Bong', CauThu.fullname 'Ten Cau Thu', CauThu.salary 'Luong'
from DoiBong, CauThu, ThongTinDoiBong
where DoiBong.id = ThongTinDoiBong.id_club
	and CauThu.id = ThongTinDoiBong.id_player

select * from view_thongtincauthu

-- 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
create proc proc_find_lichsubat_by_club
	@clubId int
as
begin
	select TrongTai.fullname, TrongTai.level, TrongTai.exp, LichSu.giaidau, DB1.ten 'Doi Bong 1', DB2.ten 'Doi Bong 2'
	from TrongTai, LichSu, DoiBong DB1, DoiBong DB2
	where TrongTai.id = LichSu.trongtai_id
		and DB1.id = LichSu.id_club_1
		and DB2.id = LichSu.id_club_2
		and (LichSu.id_club_1 = @clubId or LichSu.id_club_2 = @clubId)
end

exec proc_find_lichsubat_by_club 1