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 BT2243
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 History (
	id int primary key identity(1,1),
	trongtai_id int,
	giaidau nvarchar(50),
	ngaybat date,
	rate float,
	id_club_1 int,
	id_club_2 int
)
go

create table DoiBong (
	id int primary key identity(1,1),
	tendoibong nvarchar(50),
	sanchunha nvarchar(50),
	huanluyenvien 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 ThongTinDoiBong (
	id_club int not null,
	id_player int not null,
	joined_date date,
	primary key (id_club, id_player)
)
go

-- Tao Foreign Key
alter table History
add constraint fk_trongtai_id foreign key (trongtai_id) references TrongTai (id)
go

alter table History
add constraint fk_club_1 foreign key (id_club_1) references DoiBong (id)
go

alter table History
add constraint fk_club_2 foreign key (id_club_2) references DoiBong (id)
go

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

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

-- Them du lieu
---- TrongTai
insert into TrongTai (fullname, level, exp, address)
values
('Trong Tai A', 10, '2012-02-12', 'Ha Noi'),
('Trong Tai B', 6, '2016-03-12', 'Ha Noi'),
('Trong Tai C', 7, '2015-08-12', 'Nam Dinh')
go

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

insert into CauThu (fullname, birthday, salary, created_at)
values
('Cau Thu A', '1999-02-28', 100000, '2000-06-18'),
('Cau Thu B', '1996-07-20', 200000, '2002-06-18'),
('Cau Thu C', '1992-02-22', 800000, '2000-06-18'),
('Cau Thu D', '1997-08-27', 100000, '2003-06-18'),
('Cau Thu E', '1994-02-21', 160000, '2006-06-18'),
('Cau Thu F', '1996-03-26', 100000, '2010-06-18'),
('Cau Thu G', '2002-06-19', 900000, '2012-06-18')
go

insert into ThongTinDoiBong (id_club, id_player, joined_date)
values
(1, 3, '2000-08-15'),
(1, 4, '1999-08-15'),
(2, 5, '2002-08-15'),
(2, 6, '2006-08-15'),
(2, 7, '2012-08-15'),
(3, 8, '2016-08-15'),
(3, 9, '2017-08-15')
go

select * from CauThu
select * from DoiBong

insert into History(trongtai_id, id_club_1, id_club_2, giaidau, ngaybat, rate)
values
(1, 1, 2, 'A', '2020-02-12', 3.6),
(1, 1, 3, 'A', '2020-06-12', 5.6),
(2, 2, 3, 'A', '2020-08-12', 7.6),
(2, 1, 2, 'A', '2020-05-12', 3.6),
(2, 2, 1, 'A', '2022-02-12', 8.6)
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 (giai dau), đội 1, đội 2
select * from DoiBong

select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, History.giaidau, History.id_club_1, History.id_club_2
from TrongTai, History
where TrongTai.id = History.trongtai_id
go

select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, History.giaidau, Team1.tendoibong 'Doi Bong 1', Team2.tendoibong 'Doi Bong 2'
from TrongTai, History, DoiBong Team1, DoiBong Team2
where TrongTai.id = History.trongtai_id
	and History.id_club_1 = Team1.id
	and History.id_club_2 = Team2.id
go

create view view_all_history
as
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, History.giaidau, Team1.tendoibong 'Doi Bong 1', Team2.tendoibong 'Doi Bong 2'
from TrongTai, History, DoiBong Team1, DoiBong Team2
where TrongTai.id = History.trongtai_id
	and History.id_club_1 = Team1.id
	and History.id_club_2 = Team2.id
go

select * from view_all_history

-- Xem danh sách cầu thủ của 1 đội bóng (Id doi bong, Ten Doi Bong, Ten Cau Thu)
select DoiBong.id, DoiBong.tendoibong, CauThu.fullname
from DoiBong, CauThu, ThongTinDoiBong
where ThongTinDoiBong.id_club = DoiBong.id
	and CauThu.id = ThongTinDoiBong.id_player
go

-- Chuyen cau truy van tren su dung inner join
select DoiBong.id, DoiBong.tendoibong, CauThu.fullname
from DoiBong inner join ThongTinDoiBong on DoiBong.id = ThongTinDoiBong.id_club
	inner join CauThu on ThongTinDoiBong.id_player = CauThu.id
go

create view view_thongtindoibong
as
select DoiBong.id, DoiBong.tendoibong, CauThu.fullname
from DoiBong, CauThu, ThongTinDoiBong
where ThongTinDoiBong.id_club = DoiBong.id
	and CauThu.id = ThongTinDoiBong.id_player
go

select * from view_thongtindoibong

-- Viet proc tim thong tin bat cua trong tai
create proc proc_find_history
	@clubId int
as
begin
	select TrongTai.fullname 'Ten Trong Tai', TrongTai.level, TrongTai.exp, History.giaidau, Team1.tendoibong 'Doi Bong 1', Team2.tendoibong 'Doi Bong 2'
	from TrongTai, History, DoiBong Team1, DoiBong Team2
	where TrongTai.id = History.trongtai_id
		and History.id_club_1 = Team1.id
		and History.id_club_2 = Team2.id
		and (Team1.id = @clubId or Team2.id = @clubId)
end

exec proc_find_history 1