2021-04-22 14:14:21

create database CSDL_FIFA
go

use CSDL_FIFA
go

-- tao bang QuanLy Trong Tai--
create table TrongTai(
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	address nvarchar(200) not null,
	level float,
	experience date
)
go

-- tao bang Lich Su Bat cua Trong Tai--
create table History_TrongTai(
	id int primary key identity(1,1),
	id_trongtai int,
	GiaiDau nvarchar(100) not null,
	Ngay_Bat date,
	rate float,
	id_club_1 int,
	id_club_2 int,
	note nvarchar(500)
)
go
-- tao bang Doi Bong--
create table DoiBong(
	id int primary key identity(1,1),
	TenDoiBong nvarchar(50),
	SanChuNha nvarchar(100),
	HuyenLuyenVien nvarchar(50),
)
go
-- Tao Bang ThongTincauThu--
create table Info_CauThu(
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	salary money,
	NgayBatDauDa date
)
go
-- Tao bang GroupTable--
create table GroupTable(
	id_clubk int,
	id_player int,
	NgayThamGia date
)
go

--foreign key--
alter table History_TrongTai
add constraint FK_HS_TrongTai_ID foreign key (id_trongtai) references TrongTai(id)
go

alter table History_TrongTai
add constraint FK_HS_TrongTai_ID_Club_1 foreign key (id_club_1) references DoiBong(id)
go

alter table History_TrongTai
add constraint FK_HS_TrongTai_ID_Club_2 foreign key (id_club_2) references DoiBong(id)
go

alter table GroupTable
add constraint FK_Group_Table_Id_Club foreign key (id_clubk) references DoiBong(id)
go

alter table GroupTable
add constraint FK_Group_Table_Id_Player foreign key (id_player) references Info_CauThu(id)
go

--2. them 5 du lieu vao moi bang sau--
insert into TrongTai(fullname,address,level,experience)
values 
('Trong Tai A','Ha Noi',6,'2021-3-20'),
('Trong Tai B','Ha Nam',7,'2021-2-15'),
('Trong Tai C','Nam Dinh',9,'2021-1-10'),
('Trong Tai D','Ha Tay',8,'2021-2-25'),
('Trong Tai E','Ha Dong',5,'2021-3-10')
go
select * from TrongTai

insert into Info_CauThu(fullname,birthday,salary,NgayBatDauDa)
values
('Nguyen Van A','2000-5-15',1000,'2021-1-1'),
('Nguyen Nam B','2000-4-5',1100,'2021-1-1'),
('Nguyen Dao C','2000-3-10',1200,'2021-1-1'),
('Nguyen Tran D','2000-2-3',1300,'2021-1-1'),
('Nguyen Hung E','2000-4-10',1400,'2021-1-1')
go

insert into DoiBong(TenDoiBong,SanChuNha,HuyenLuyenVien)
values
('FC_HaNoi','SanBong_HaNoi','Tran Nguyen A'),
('FC_HaNam','SanBong_HaNam','Tran Nguyen B'),
('FC_NamDinh','SanBong_NamDinh','Tran Nguyen C'),
('FC_HaTay','SanBong_HaTay','Tran Nguyen D'),
('FC_HaDong','SanBong_HaDong','Tran Nguyen E')
go

insert into History_TrongTai(id_trongtai,GiaiDau,Ngay_Bat,rate,id_club_1,id_club_2,note)
values
(1,'FIFA_HANOI','2021-4-20',5,'Giai to nhat'),
(3,'FIFA_HANAM','2021-4-20',9,'Giai binh thuong'),
(4,'FIFA_NAMDINH','2021-4-20',8,'Giai kha nho'),
(2,'FIFA_HATAY','2021-4-20',7,'Giai qua be'),
(5,'FIFA_HADONG','2021-4-20',6,'Giai Vua Vua')
go
select * from History_TrongTai
select * from DoiBong
select * from Info_CauThu


insert into GroupTable(id_clubk,id_player,NgayThamGia)
values
(5,1,'2020-5-5'),
(4,5,'2020-5-4'),
(3,4,'2020-5-3'),
(2,3,'2020-5-2'),
(1,2,'2020-5-1')
go

--3. Thong tin lich su bat cua Trong Tai--
create view vw_History_TrongTai 
as
	select TrongTai.fullname 'Ten Trong Tai',TrongTai.level'Level',TrongTai.experience'EXP',History_TrongTai.GiaiDau'GiaiBong',History_TrongTai.id_club_1'Doi 1',History_TrongTai.id_club_2'Doi 2'
	from TrongTai,History_TrongTai
	where TrongTai.id = History_TrongTai.id_trongtai
go

select * from vw_History_TrongTai
go

--4. Xem danh sach cau thu Doi Bong--

create view vw_Info_CauThu_DoiBong
as
select Info_CauThu.fullname ,DoiBong.TenDoiBong
from Info_CauThu,GroupTable,DoiBong
where Info_CauThu.id = GroupTable.id_player
	and GroupTable.id_clubk = DoiBong.id		
go

select * from vw_Info_CauThu_DoiBong
go

--5. tao bang proc --
alter proc proc_Lich_Su_Bat_Trong_Tai
	@TenDoiBong nvarchar(50)
as
begin
	select TrongTai.fullname 'Ten Trong Tai',TrongTai.level'Level',TrongTai.experience'EXP',History_TrongTai.GiaiDau'GiaiBong',DoiBong1.TenDoiBong'Ten Doi1',DoiBong2.TenDoiBong'Ten Doi 2'
	from TrongTai,History_TrongTai,DoiBong DoiBong1, DoiBong DoiBong2
	where TrongTai.id = History_TrongTai.id_trongtai
			and DoiBong1.id = History_TrongTai.id_club_1
			and DoiBong2.id = History_TrongTai.id_club_2
			and (DoiBong1.TenDoiBong = @TenDoiBong or DoiBong2.TenDoiBong = @TenDoiBong)
end
go

exec proc_Lich_Su_Bat_Trong_Tai 'FC_HaNoi'

select * from History_TrongTai
select * from DoiBong


delete from TrongTai where id = 1
--tạo trigger--
create trigger Trigger_delete_TrongTai on TrongTai
instead of delete
as
begin
	delete from History_TrongTai where id_trongtai in (select id from deleted)
	delete from TrongTai where id in (select id from deleted)
end

2021-04-22 13:58:50

Diagram

2021-04-22 13:57:25

-- Create database BT2243
create database BT2243
go

-- Active database
use BT2243
go

-- Create Tables
create table Referee (
	RefereeID int primary key identity(1,1),
	FullName nvarchar(50) not null,
	Address nvarchar(200) not null,
	Level float,
	Exp date
)
go

create table PenaltyHistory (
	PenaltyID int primary key identity(1,1),
	RefereeID int,
	LeagueName nvarchar(100) not null,
	CatchDate date,
	Rate float,
	TeamID_1 int,
	TeamID_2 int,
	Note nvarchar(500)
)
go

create table Team (
	TeamID int primary key identity(1,1),
	TeamName nvarchar(50),
	HomeTeam nvarchar(100),
	CoachName nvarchar(50)
)
go

create table PlayerInfo (
	PlayerID int primary key identity(1,1),
	FullName nvarchar(50),
	Birthday date,
	Salary money,
	LeagueJoiningDate date
)
go

create table Club_Player_Info (
	TeamID int,
	PlayerID int,
	ClubJoiningDate date,
	primary key (TeamID, PlayerID)
)
go

-- Foreign Key
alter table PenaltyHistory
add constraint FK_PenaltyHistory_Referee foreign key (RefereeID) references Referee (RefereeID)
go

alter table PenaltyHistory
add constraint FK_PenaltyHistory_Team_1 foreign key (TeamID_1) references Team (TeamID)
go

alter table PenaltyHistory
add constraint FK_PenaltyHistory_Team_2 foreign key (TeamID_2) references Team (TeamID)
go

alter table Club_Player_Info
add constraint FK_Club_Player_Info_PlayerInfo foreign key (PlayerID) references PlayerInfo (PlayerID)
go

alter table Club_Player_Info
add constraint FK_Club_Player_Info_Team foreign key (TeamID) references Team (TeamID)
go

-- Insert Into
insert into Referee(FullName, Address, Level, Exp)
values 
('Referee A', 'Adress A', 2, '2019-10-10'),
('Referee B', 'Adress B', 1, '2018-12-05'),
('Referee C', 'Adress C', 3, '2017-07-15'),
('Referee D', 'Adress D', 3, '2018-06-20'),
('Referee E', 'Adress E', 2, '2019-04-25')
go

insert into Team (TeamName, HomeTeam, CoachName)
values 
('Team 1', 'Country 1', 'Coach 1'),
('Team 2', 'Country 2', 'Coach 2'),
('Team 3', 'Country 3', 'Coach 3'),
('Team 4', 'Country 4', 'Coach 4'),
('Team 5', 'Country 5', 'Coach 5'),
('Team 6', 'Country 6', 'Coach 6')
go

insert into PlayerInfo(FullName, Birthday, Salary, LeagueJoiningDate)
values
('Player 1A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6F', '1995-10-10', 3000.65, '2018-10-10')
go

insert into Club_Player_Info(TeamID, PlayerID, ClubJoiningDate)
values
(1, 1, '2017-10-10'),
(1, 2, '2017-10-10'),
(1, 3, '2017-10-10'),
(1, 4, '2017-10-10'),
(1, 5, '2017-10-10'),
(1, 6, '2017-10-10'),
(2, 7, '2017-10-10'),
(2, 8, '2017-10-10'),
(2, 9, '2017-10-10'),
(2, 10, '2017-10-10'),
(2, 11, '2017-10-10'),
(2, 12, '2017-10-10'),
(3, 13, '2017-10-10'),
(3, 14, '2017-10-10'),
(3, 15, '2017-10-10'),
(3, 16, '2017-10-10'),
(3, 17, '2017-10-10'),
(3, 18, '2017-10-10'),
(4, 19, '2017-10-10'),
(4, 20, '2017-10-10'),
(4, 21, '2017-10-10'),
(4, 22, '2017-10-10'),
(4, 23, '2017-10-10'),
(4, 24, '2017-10-10'),
(5, 25, '2017-10-10'),
(5, 26, '2017-10-10'),
(5, 27, '2017-10-10'),
(5, 28, '2017-10-10'),
(5, 29, '2017-10-10'),
(5, 30, '2017-10-10'),
(6, 31, '2017-10-10'),
(6, 32, '2017-10-10'),
(6, 33, '2017-10-10'),
(6, 34, '2017-10-10'),
(6, 35, '2017-10-10'),
(6, 36, '2017-10-10')
go

insert into PenaltyHistory(RefereeID, LeagueName, CatchDate, Rate, TeamID_1, TeamID_2)
values
(1, 'League X', '2019-10-10', 7.5, 1, 2),
(1, 'League Y', '2019-10-10', 5.5, 2, 3),
(1, 'League Z', '2019-10-10', 9.0, 3, 4),
(2, 'League X', '2019-10-10', 6.5, 4, 5),
(3, 'League X', '2019-10-10', 5.0, 5, 6),
(4, 'League X', '2019-10-10', 7.5, 6, 1),
(4, 'League Y', '2019-10-10', 4.5, 2, 5),
(5, 'League X', '2019-10-10', 9.5, 6, 4),
(5, 'League Y', '2019-10-10', 8.0, 4, 3)
go

-- TEST
select * from Referee
go
select * from PenaltyHistory
go
select * from Team
go
select * from PlayerInfo
go
select * from Club_Player_Info
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
---- Referee, PenaltyHistory, Team
select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, Team1.TeamName 'Team Name 1', Team2.TeamName 'Team Name 2'
from Referee, Team Team1, Team Team2, PenaltyHistory
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_1 = Team1.TeamID
	and PenaltyHistory.TeamID_2 = Team2.TeamID
go

create view view_referee_history
as
select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, Team1.TeamName 'Team Name 1', Team2.TeamName 'Team Name 2'
from Referee, Team Team1, Team Team2, PenaltyHistory
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_1 = Team1.TeamID
	and PenaltyHistory.TeamID_2 = Team2.TeamID
go

select * from view_referee_history
go

-- Xem danh sách cầu thủ của 1 đội bóng
---- PlayerInfo, Team, Club_Player_Info
select Team.TeamName, PlayerInfo.*
from PlayerInfo, Team, Club_Player_Info
where Team.TeamID = Club_Player_Info.TeamID
	and PlayerInfo.PlayerID = Club_Player_Info.PlayerID
	and Team.TeamName = 'Team 1'
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 -> Tìm theo tên 1 đội bóng
---- proc
create proc proc_find_referee
	@teamId int
as
begin
	select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, Team1.TeamName 'Team Name 1', Team2.TeamName 'Team Name 2'
	from Referee, Team Team1, Team Team2, PenaltyHistory
	where Referee.RefereeID = PenaltyHistory.RefereeID
		and PenaltyHistory.TeamID_1 = Team1.TeamID
		and PenaltyHistory.TeamID_2 = Team2.TeamID
		and (Team1.TeamID = @teamId or Team2.TeamID = @teamId)
end
go

exec proc_find_referee 1
go

-- Tạo instead of delete cho phép delete 1 trọng tài.

delete from Referee where RefereeID = 1
go

create trigger trigger_delete_referee on Referee
instead of delete
as
begin
	delete from PenaltyHistory where RefereeID in (select RefereeID from deleted)
	delete from Referee where RefereeID in (select RefereeID from deleted)
end
go

delete from Referee where RefereeID = 1
go

2021-04-20 14:03:26

create table Referee (
	RefereeID int primary key identity(1,1),
	FullName nvarchar(50) not null,
	Address nvarchar(200) not null,
	Level float,
	Exp date
)
go

create table PenaltyHistory (
	PenaltyID int primary key identity(1,1),
	RefereeID int foreign key references Referee(RefereeID),
	LeagueName nvarchar(100) not null,
	CatchDate date,
	Rate float,
	TeamID_1 int foreign key references Team(TeamID),
	TeamID_2 int foreign key references Team(TeamID)
)
go

create table Team (
	TeamID int primary key identity(1,1),
	TeamName nvarchar(50),
	HomeTeam nvarchar(100),
	CoachName nvarchar(50)
)
go

create table PlayerInfo (
	PlayerID int primary key identity(1,1),
	FullName nvarchar(50),
	Birthday date,
	Salary money,
	LeagueJoiningDate date
)
go

create table Club_Player_Info (
	TeamID int foreign key references Team(TeamID),
	PlayerID int foreign key references PlayerInfo(PlayerID),
	ClubJoiningDate date
)
go

insert into Referee(FullName, Address, Level, Exp)
values 
('Referee A', 'Adress A', 2, '2019-10-10'),
('Referee B', 'Adress B', 1, '2018-12-05'),
('Referee C', 'Adress C', 3, '2017-07-15'),
('Referee D', 'Adress D', 3, '2018-06-20'),
('Referee E', 'Adress E', 2, '2019-04-25')
go

insert into Team (TeamName, HomeTeam, CoachName)
values 
('Team 1', 'Country 1', 'Coach 1'),
('Team 2', 'Country 2', 'Coach 2'),
('Team 3', 'Country 3', 'Coach 3'),
('Team 4', 'Country 4', 'Coach 4'),
('Team 5', 'Country 5', 'Coach 5'),
('Team 6', 'Country 6', 'Coach 6')
go

select * from Referee
go
select * from Team
go
select * from PlayerInfo
go
select * from Club_Player_Info
go
select * from PenaltyHistory
go


insert into PlayerInfo(FullName, Birthday, Salary, LeagueJoiningDate)
values
('Player 1A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 1F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 2F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 3F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 4F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 5F', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6A', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6B', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6C', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6D', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6E', '1995-10-10', 3000.65, '2018-10-10'),
('Player 6F', '1995-10-10', 3000.65, '2018-10-10')
go

insert into Club_Player_Info(TeamID, PlayerID, ClubJoiningDate)
values
(1, 1, '2017-10-10'),
(1, 2, '2017-10-10'),
(1, 3, '2017-10-10'),
(1, 4, '2017-10-10'),
(1, 5, '2017-10-10'),
(1, 6, '2017-10-10'),
(2, 7, '2017-10-10'),
(2, 8, '2017-10-10'),
(2, 9, '2017-10-10'),
(2, 10, '2017-10-10'),
(2, 11, '2017-10-10'),
(2, 12, '2017-10-10'),
(3, 13, '2017-10-10'),
(3, 14, '2017-10-10'),
(3, 15, '2017-10-10'),
(3, 16, '2017-10-10'),
(3, 17, '2017-10-10'),
(3, 18, '2017-10-10'),
(4, 19, '2017-10-10'),
(4, 20, '2017-10-10'),
(4, 21, '2017-10-10'),
(4, 22, '2017-10-10'),
(4, 23, '2017-10-10'),
(4, 24, '2017-10-10'),
(5, 25, '2017-10-10'),
(5, 26, '2017-10-10'),
(5, 27, '2017-10-10'),
(5, 28, '2017-10-10'),
(5, 29, '2017-10-10'),
(5, 30, '2017-10-10'),
(6, 31, '2017-10-10'),
(6, 32, '2017-10-10'),
(6, 33, '2017-10-10'),
(6, 34, '2017-10-10'),
(6, 35, '2017-10-10'),
(6, 36, '2017-10-10')
go

insert into PenaltyHistory(RefereeID, LeagueName, CatchDate, Rate, TeamID_1, TeamID_2)
values
(1, 'League X', '2019-10-10', 7.5, 1, 2),
(1, 'League Y', '2019-10-10', 5.5, 2, 3),
(1, 'League Z', '2019-10-10', 9.0, 3, 4),
(2, 'League X', '2019-10-10', 6.5, 4, 5),
(3, 'League X', '2019-10-10', 5.0, 5, 6),
(4, 'League X', '2019-10-10', 7.5, 6, 1),
(4, 'League Y', '2019-10-10', 4.5, 2, 5),
(5, 'League X', '2019-10-10', 9.5, 6, 4),
(5, 'League Y', '2019-10-10', 8.0, 4, 3)
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 (Viết truy vấn và tạo view)
alter view View_Referee_PenaltyHistory
as
select Referee.FullName 'Ten trong tai', Referee.Level 'Level', PenaltyHistory.LeagueName 'Giai bong', PenaltyHistory.TeamID_1 'Doi 1', PenaltyHistory.TeamID_2 'Doi 2'
from Referee, PenaltyHistory
where PenaltyHistory.RefereeID = Referee.RefereeID
	and Referee.RefereeID = 1
go

select * from View_Referee_PenaltyHistory
go

--- Xem danh sách cầu thủ của 1 đội bóng (Viết truy vấn và tạo view)
alter view View_Team_Players
as
select Team.TeamName, PlayerInfo.*
from Team, PlayerInfo, Club_Player_Info
where Team.TeamID = Club_Player_Info.TeamID
	and Club_Player_Info.PlayerID = PlayerInfo.PlayerID
	and Team.TeamID = 1
go

select * from View_Team_Players
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 -> Tìm theo tên 1 đội bóng. Viết proc có 1 tham số đầu vào là id đội bóng.
alter proc Procedure_Referee_PenaltyHistory
@TeamName nvarchar(50)
as
begin 
	select Referee.FullName 'Ten Trong Tai', Referee.Level 'Level', Referee.Exp 'Exp', PenaltyHistory.LeagueName 'Giai Bong', PenaltyHistory.TeamID_1 'Doi 1'
	from Referee, PenaltyHistory, Team
	where Referee.RefereeID = PenaltyHistory.RefereeID
		and PenaltyHistory.TeamID_1 = Team.TeamID
		--and PenaltyHistory.TeamID_2 = Team.TeamID
		and Team.TeamName like CONCAT('%', @TeamName,'%')
end
go

exec Procedure_Referee_PenaltyHistory 'Team 2';
go

---6) Tạo instead of delete cho phép xong 1 trọng tài

create trigger Trigger_delete_referee on Referee
instead of delete
as
begin
	delete from PenaltyHistory where RefereeID in (select RefereeID from deleted)
	delete from Referee where RefereeID in (select RefereeID from deleted)
end
go

--- cau thu delete de kich hoat trigger
delete from Referee where RefereeID = 1
go

--- check lai trong tai da xoa 
select * from Referee
go
select * from PenaltyHistory
go

2021-04-20 13:54:58

create database FIFA
go
use FIFA
go
create table history (
	id int primary key identity(1,1),
	full_name nvarchar(50) not null,
	address nvarchar(200) not null,
	level float,
	exp date
)
go
create table arrest_history (
	id int primary key identity(1,1),
	id_history int references history(id),
	Tournaments nvarchar(100),
	star_day date,
	rate float,
	id_club_1 int references Club(id),
	id_club_2 int references Club(id),
	note nvarchar(500)
)
go
create table Club (
	id int primary key identity(1,1),
	Club_name nvarchar(50),
	home_field nvarchar(100),
	coach nvarchar(50)
)
go
create table Player (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	salary money,
	star_day date
)
go
create table group_table (
	id_club int references Club(id),
	id_player int references Player(id),
	star_day date
)
go
insert into history (full_name, address, level, exp)
values 
('Nguyen Trung A','Ha Noi',5,'2018-01-01'),
('Nguyen Trung B','Ha Noi',3,'2018-01-01'),
('Nguyen Trung C','Ha Noi',2,'2018-01-01'),
('Nguyen Trung D','Ha Noi',1,'2018-01-01'),
('Nguyen Trung E','Ha Noi',3,'2018-01-01')
go
insert into arrest_history (id_history, Tournaments, star_day, rate, id_club_1, id_club_2, note)
values
(1,'FIFA online 4','2021-04-09',5,1,1,'bal...bal'),
(2,'FIFA online 4','2021-04-09',3,2,2,'bal...bal'),
(3,'FIFA online 4','2021-04-09',1,3,3,'bal...bal'),
(4,'FIFA online 4','2021-04-09',3,4,4,'bal...bal'),
(5,'FIFA online 4','2021-04-09',4,5,5,'bal...bal')
go
insert into Club (Club_name, home_field, coach)
values
('HANOI','HANOI','Tran Van A'),
('HANOI','HANOI','Tran Van B'), 
('HANOI','HANOI','Tran Van C'), 
('HANOI','HANOI','Tran Van D'),
('HANOI','HANOI','Tran Van E')
go
insert into Player (fullname, birthday ,salary, star_day)
values
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01')
go

insert into group_table (id_club, id_player, star_day)
values
(1,2,'2020-05-05'),
(1,3,'2020-05-05'),
(2,2,'2020-05-05'),
(2,4,'2020-05-05'),
(4,1,'2020-05-05')
go


select history.full_name,history.level,history.exp,arrest_history.Tournaments,ClubA.Club_name 'Club A',ClubB.Club_name 'Club B'
from arrest_history,history,Club ClubA, Club ClubB
where history.id=arrest_history.id_history 
	and arrest_history.id_club_1=ClubA.id 
	and arrest_history.id_club_2=ClubB.id

create view fifav 
as
select history.full_name,history.level,history.exp,arrest_history.Tournaments,ClubA.Club_name 'Club A',ClubB.Club_name 'Club B'
from arrest_history,history,Club ClubA, Club ClubB
where history.id=arrest_history.id_history 
	and arrest_history.id_club_1=ClubA.id 
	and arrest_history.id_club_2=ClubB.id


select	* from fifav 

create proc proction
@id int
as
begin

select	* from fifav 

end

2021-04-09 04:23:11

create database FIFA

use FIFA

create table history (
	id int primary key identity(1,1),
	full_name nvarchar(50) not null,
	address nvarchar(200) not null,
	level float,
	exp date
)
create table arrest_history (
	id int primary key identity(1,1),
	id_history int references history(id),
	Tournaments nvarchar(100),
	star_day date,
	rate float,
	id_club_1 int references Club(id),
	id_club_2 int references Club(id),
	note nvarchar(500)
)
create table Club (
	id int primary key identity(1,1),
	Club_name nvarchar(50),
	home_field nvarchar(100),
	coach nvarchar(50)
)
create table Player (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	salary money,
	star_day date
)
create table group_table (
	id_club int references Club(id),
	id_player int references Player(id),
	star_day date
)
insert into history (full_name, address, level, exp)
values 
('Nguyen Trung A','Ha Noi',5,'2018-01-01'),
('Nguyen Trung B','Ha Noi',3,'2018-01-01'),
('Nguyen Trung C','Ha Noi',2,'2018-01-01'),
('Nguyen Trung D','Ha Noi',1,'2018-01-01'),
('Nguyen Trung E','Ha Noi',3,'2018-01-01')

insert into arrest_history (id_history, Tournaments, star_day, rate, id_club_1, id_club_2, note)
values
(1,'FIFA online 4','2021-04-09',5,1,1,'bal...bal'),
(2,'FIFA online 4','2021-04-09',3,2,2,'bal...bal'),
(3,'FIFA online 4','2021-04-09',1,3,3,'bal...bal'),
(4,'FIFA online 4','2021-04-09',3,4,4,'bal...bal'),
(5,'FIFA online 4','2021-04-09',4,5,5,'bal...bal')
insert into Club (Club_name, home_field, coach)
values
('HANOI','HANOI','Tran Van A'),
('HANOI','HANOI','Tran Van B'), 
('HANOI','HANOI','Tran Van C'), 
('HANOI','HANOI','Tran Van D'),
('HANOI','HANOI','Tran Van E')
insert into Player (fullname, birthday ,salary, star_day)
values
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01'),
('Nguyen Van A','2002-05-27',10000000,'2020-01-01')


insert into group_table (id_club, id_player, star_day)
values
(1,2,'2020-05-05'),
(1,3,'2020-05-05'),
(2,2,'2020-05-05'),
(2,4,'2020-05-05'),
(4,1,'2020-05-05')

select history.full_name, history.level, history.exp, arrest_history.Tournaments, arrest_history.id_club_1, arrest_history.id_club_2
from history, arrest_history
where arrest_history.id_history = history.id
----3---
create view View_arrest_history
as
select history.full_name, history.level, history.exp, arrest_history.Tournaments, arrest_history.id_club_1, arrest_history.id_club_2
from history, arrest_history
where arrest_history.id_history = history.id

select * from View_arrest_history
---4--
create view View_Player_Club
as
select group_table.id_player, Player.fullname, Player.birthday, Player.salary, Player.star_day
from group_table , Player
where Player.id = group_table.id_player

select * from View_Player_Club
---5---
create proc proc_arrest_history_player
	@Club nvarchar(50)
as
begin
	select history.full_name, history.level, history.exp, arrest_history.Tournaments, arrest_history.id_club_1, arrest_history.id_club_2, Club.Club_name, Club.home_field
	from history, arrest_history,Club
	where arrest_history.id_history = history.id
	and arrest_history.id_club_1= Club.id
	and arrest_history.id_club_2= Club.id
	and Club.Club_name like CONCAT('%', @Club,'%')

end
drop proc proc_arrest_history_player
exec proc_arrest_history_player 'G'
--6---
alter trigger trigger_delete_history on history
instead of delete
as
begin
	delete from arrest_history where id_history in (select id_history from deleted)
	delete from history where id in (select id from deleted)
end
drop trigger trigger_delete_history
delete from history where id = 1
delete from arrest_history where id_history = 1
select * from history
select * from arrest_history

2021-04-09 03:54:24

create database quan_ly_giai_dau

use quan_ly_giai_dau

drop table Arbitration

drop table Team

drop table Player

drop table Groups

drop table History

create table Arbitration (

ArbitrationID int primary key identity (1,1),

fullname nvarchar(50) not null,

address nvarchar(200) not null,

level float,

exp date

)

create table Team (

TeamID int primary key identity (1,1),

Team_name nvarchar(50),

Home_yard nvarchar(100),

Coach nvarchar(50),

)

create table Player (

PlayerID int primary key identity (1,1),

Fullname nvarchar(50),

birthday date,

salary money,

start_day date

)

create table Groups (

id_club int references Team(TeamID),

id_player int references Player(PlayerID),

join_date date

primary key (id_club, id_player)

)

create table History (

HistoryID int primary key identity (1,1),

Arbitration_ID int references Arbitration (ArbitrationID),

Tournaments nvarchar(100) not null,

arrest_date date,

rate float,

id_club_1 int references Team(TeamID),

id_club_2 int references Team(TeamID),

note nvarchar(200)

)

---

insert into Arbitration (fullname, address, level, exp)

values

('Trong Tai 1','My','1','2020-01-01'),

('Trong Tai 2','Anh','2','2020-01-02'),

('Trong Tai 3','Phap','2','2020-01-03'),

('Trong Tai 4','Duc','1','2020-01-04'),

('Trong Tai 5','Ao','3','2020-01-05')

insert into Team (Team_name, Home_yard, Coach)

values

('Bang A','San 1','HLV 1'),

('Bang B','San 2','HLV 2'),

('Bang C','San 3','HLV 3'),

('Bang D','San 4','HLV 4'),

('Bang E','San 5','HLV 5')

insert into Player (Fullname, birthday, salary, start_day)

values

('Cau Thu 1','1995-01-02','10000','2021-01-01'),

('Cau Thu 2','1995-01-03','10000','2021-01-02'),

('Cau Thu 3','1995-01-04','10000','2021-01-03'),

('Cau Thu 4','1995-01-05','10000','2021-01-04'),

('Cau Thu 5','1995-01-06','10000','2021-01-05')

insert into Groups (id_club, id_player, join_date)

values

('1','1','2010-01-02'),

('2','1','2010-01-03'),

('2','3','2010-01-04'),

('1','2','2010-01-05'),

('3','4','2010-01-06'),

('4','2','2010-01-07'),

('3','1','2010-01-08'),

('5','2','2010-01-09'),

('4','4','2010-01-10'),

('5','3','2010-01-01')

insert into History (Arbitration_ID, Tournaments, arrest_date, rate, id_club_1, id_club_2, note)

values

('1','giai 1','2019-01-01','5','1','2','ABC'),

('1','giai 2','2019-01-02','6','1','1','ABC'),

('2','giai 3','2019-01-03','7','1','3','ABC'),

('2','giai 4','2019-01-04','8','2','5','ABC'),

('3','giai 5','2019-01-05','9','4','3','ABC'),

('3','giai 5','2019-01-06','10','5','2','ABC'),

('4','giai 4','2019-01-07','7','4','5','ABC'),

('4','giai 3','2019-01-08','8','3','2','ABC'),

('5','giai 2','2019-01-09','6','5','4','ABC'),

('5','giai 1','2019-01-10','9','2','4','ABC')

---3.view

create view view_0

as

select Arbitration.fullname, Arbitration.level, Arbitration.exp, History.Tournaments, History.id_club_1, History.id_club_2

from Arbitration left join History on Arbitration.ArbitrationID = History.Arbitration_ID

create view view_1

as

select Arbitration.fullname, Arbitration.level, Arbitration.exp, History.Tournaments, Team.Team_name as 'Team 1', History.id_club_2

from  Arbitration left join History on History.Arbitration_ID = Arbitration.ArbitrationID

left join Team on Team.TeamID = History.id_club_1

create view view_2

as

select view_1.fullname, view_1.level, view_1.exp, view_1.Tournaments, view_1.[Team 1], Team.Team_name as 'Team 2'

from view_1 left join Team on view_1.id_club_2 = Team.TeamID

select * from view_0

select * from view_1

select * from view_2

---- 4.

create view list_player

as

select Player.Fullname, Player.birthday, Player.salary, Player.start_day

from  Player left join Groups on Groups.id_player = Player.PlayerID

left join Team on Team.TeamID = Groups.id_club

select * from list_player

---5.proc

create proc proc_history

@ID_Clus nvarchar(50)

as

begin

select view_2.fullname, view_2.level, view_2.exp, view_2.Tournaments, view_2.[Team 1], view_2.[Team 2]

from view_2

where view_2.[Team 1] like concat ('%', @ID_Clus, '%') or view_2.[Team 2] like concat ('%', @ID_Clus, '%')

end

drop proc proc_history

exec proc_history 'C'

---6.

create trigger Arbitration_delete on Arbitration

instead of delete

as

begin

delete from History where Arbitration_ID in (select Arbitration_ID from deleted)

delete from Arbitration where ArbitrationID in (select ArbitrationID from deleted)

end

delete from Arbitration where ArbitrationID = 1

select * from Arbitration