Menu Bài Viết:

Nguyen Trung Kien [community,C2010G]

Ngày viết: 11:23 09/04/2021

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

Phạm Ngọc Đại [community,C2010G]

Ngày viết: 10:54 09/04/2021

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