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 BT2234
go

-- Kich hoat CSDL
use BT2234
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 date
)
go

create table LichSu (
	id int primary key identity(1,1),
	trongtai_id int,
	giaidau nvarchar(100) not null,
	start_date date,
	rate float,
	id_club_1 int, -- DoiBong A: LichSu.id_club_1 = A.id
	id_club_2 int, -- DoiBong B: LichSu.id_club_2 = B.id
	note nvarchar(500)
)
go

create table DoiBong (
	id int primary key identity(1,1),
	tendoibong nvarchar(50),
	sanchunha nvarchar(100),
	huanluyenvien nvarchar(50)
)
go

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

create table ThongTinDoiBong (
	id_club int not null,
	id_player int not null,
	start_date date,
	primary key (id_club, id_player)
)

-- Tao foreign key
alter table LichSu
add constraint fk_trongtai foreign key (trongtai_id) references TrongTai(id)
go

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

alter table LichSu
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

-- Insert Data
insert into DoiBong(tendoibong, sanchunha, huanluyenvien)
values
('BD A', 'A', 'AA'),
('BD B', 'B', 'BB'),
('BD C', 'C', 'CC')
go

insert into CauThu(fullname, birthday, salary, start_date)
values
('Cau Thu A', '1999-10-12', 5000, '2010-01-05'),
('Cau Thu B', '1998-03-22', 6000, '2010-02-05'),
('Cau Thu C', '1989-09-18', 7000, '2000-06-05'),
('Cau Thu D', '1992-06-15', 9000, '2000-07-05'),
('Cau Thu E', '1991-11-12', 2000, '2009-01-05')
go

insert into TrongTai(fullname, address, exp_date, level)
values
('TT A', 'TA', '1999-02-06', 23),
('TT B', 'TB', '2002-02-06', 20)
go

insert into ThongTinDoiBong (id_club, id_player, start_date)
values
(1, 1, '2010-12-06'),
(1, 3, '2012-12-06'),
(2, 2, '2015-12-06'),
(2, 5, '2019-12-06'),
(1, 4, '2018-12-06')
go

insert into LichSu(giaidau, id_club_1, id_club_2, trongtai_id, start_date, rate, note)
values
('GDA', 1, 2, 1, '2018-02-16', 8, 'OK'),
('GDB', 1, 3, 1, '2019-02-16', 7, 'OK'),
('GDC', 2, 3, 2, '2016-02-16', 8, 'OK'),
('GDD', 1, 2, 1, '2015-02-16', 9, 'OK'),
('GDE', 3, 2, 2, '2012-02-16', 6, 'OK')
go

-- Lich Su: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.level 'Cap Do', TrongTai.exp_date 'Ngay Bat',
	LichSu.giaidau 'Giai Dau', Club1.tendoibong 'Doi Bong 1', Club2.tendoibong 'Doi Bong 2'
from TrongTai, LichSu, DoiBong Club1, DoiBong Club2
where TrongTai.id = LichSu.trongtai_id
	and LichSu.id_club_1 = Club1.id
	and LichSu.id_club_2 = Club2.id
go

create view view_lichsu
as
select TrongTai.fullname 'Ten Trong Tai', TrongTai.level 'Cap Do', TrongTai.exp_date 'Ngay Bat',
	LichSu.giaidau 'Giai Dau', Club1.tendoibong 'Doi Bong 1', Club2.tendoibong 'Doi Bong 2'
from TrongTai, LichSu, DoiBong Club1, DoiBong Club2
where TrongTai.id = LichSu.trongtai_id
	and LichSu.id_club_1 = Club1.id
	and LichSu.id_club_2 = Club2.id
go

select * from view_lichsu

-- Xem thong tin doi bong: ten doi bong, ten cau thu, ngay tham gia
select DoiBong.tendoibong, CauThu.fullname, ThongTinDoiBong.start_date
from DoiBong left join ThongTinDoiBong on DoiBong.id = ThongTinDoiBong.id_club
	left join CauThu on CauThu.id = ThongTinDoiBong.id_player
where DoiBong.id = 1

create view view_doibong --Su dung view la khong hop ly
as
select DoiBong.tendoibong, CauThu.fullname, ThongTinDoiBong.start_date
from DoiBong left join ThongTinDoiBong on DoiBong.id = ThongTinDoiBong.id_club
	left join CauThu on CauThu.id = ThongTinDoiBong.id_player
where DoiBong.id = 1

select * from view_doibong

create proc proc_doibong
	@doibong_id int
as
begin
	select DoiBong.tendoibong, CauThu.fullname, ThongTinDoiBong.start_date
	from DoiBong left join ThongTinDoiBong on DoiBong.id = ThongTinDoiBong.id_club
		left join CauThu on CauThu.id = ThongTinDoiBong.id_player
	where DoiBong.id = @doibong_id
end

exec proc_doibong 1
exec proc_doibong 2
exec proc_doibong 3
exec proc_doibong 4

-- Tim thong tin bat cua trong tai
create proc proc_find_trongtai_by_doibong
	@doibong_id int
as
begin
	select TrongTai.fullname 'Ten Trong Tai', TrongTai.level 'Cap Do', TrongTai.exp_date 'Ngay Bat',
		LichSu.giaidau 'Giai Dau', Club1.tendoibong 'Doi Bong 1', Club2.tendoibong 'Doi Bong 2'
	from TrongTai, LichSu, DoiBong Club1, DoiBong Club2
	where TrongTai.id = LichSu.trongtai_id
		and LichSu.id_club_1 = Club1.id
		and LichSu.id_club_2 = Club2.id
		and (Club1.id = @doibong_id or Club2.id = @doibong_id)
end

exec proc_find_trongtai_by_doibong 1
exec proc_find_trongtai_by_doibong 2
exec proc_find_trongtai_by_doibong 3