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

Quản lý khách sạn - nâng cao - Lập trình Sql Server

#SQLQuery1.sql

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

-- Query: FullName (Referee), level (Referee), exp (Referee), LeagueName (PenaltyHistory), TeamName 1 (Team), Team Name 2 (Team)
select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, 
	T1.TeamName 'Team Name 1', T2.TeamName 'Team Name 2'
from Referee, PenaltyHistory, Team as T1, Team as T2
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_1 = T1.TeamID
	and PenaltyHistory.TeamID_2 = T2.TeamID
go

-- Query: FullName (Referee), level (Referee), exp (Referee), LeagueName (PenaltyHistory), TeamName 1 (Team)
select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, 
	Team.TeamName
from Referee, PenaltyHistory, Team
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_1 = Team.TeamID
go

select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, 
	Team.TeamName
from Referee, PenaltyHistory, Team
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_2 = Team.TeamID
go

select * from Team
go

-- Query: FullName (Referee), level (Referee), exp (Referee), LeagueName (PenaltyHistory), TeamName 1 (Team), Team Name 2 (Team)
select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, 
	PenaltyHistory.TeamID_1, T1.TeamName 'Team Name 1', 
	PenaltyHistory.TeamID_2, T2.TeamName 'Team Name 2'
from Referee, PenaltyHistory, Team as T1, Team as T2
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_1 = T1.TeamID
	and PenaltyHistory.TeamID_2 = T2.TeamID
go

-- Xem thong tin lich su da cua doi bong 1
select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, 
	PenaltyHistory.TeamID_1, T1.TeamName 'Team Name 1', 
	PenaltyHistory.TeamID_2, T2.TeamName 'Team Name 2'
from Referee, PenaltyHistory, Team as T1, Team as T2
where Referee.RefereeID = PenaltyHistory.RefereeID
	and PenaltyHistory.TeamID_1 = T1.TeamID
	and PenaltyHistory.TeamID_2 = T2.TeamID
	and (PenaltyHistory.TeamID_1 = 1 or PenaltyHistory.TeamID_2 = 1)
go

create proc proc_view_history_team
	@teamId int
as
begin
	select Referee.FullName, Referee.Level, Referee.Exp, PenaltyHistory.LeagueName, 
		PenaltyHistory.TeamID_1, T1.TeamName 'Team Name 1', 
		PenaltyHistory.TeamID_2, T2.TeamName 'Team Name 2'
	from Referee, PenaltyHistory, Team as T1, Team as T2
	where Referee.RefereeID = PenaltyHistory.RefereeID
		and PenaltyHistory.TeamID_1 = T1.TeamID
		and PenaltyHistory.TeamID_2 = T2.TeamID
		and (PenaltyHistory.TeamID_1 = @teamId or PenaltyHistory.TeamID_2 = @teamId)
end

exec proc_view_history_team 1
exec proc_view_history_team 2
exec proc_view_history_team 3

#SQLQuery2.sql

-- Tao database
create database hotel_db

-- Active database
use hotel_db

-- Tao tables
create table hotel (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	address nvarchar(200) not null,
	area float,
	owner_name nvarchar(50)
)

create table room (
	room_no nvarchar(20) primary key,
	hotel_id int references hotel (id),
	area float,
	type nvarchar(20),
	floor int
)

create table book (
	id int primary key identity(1,1),
	room_no nvarchar(20) references room (room_no),
	checkin datetime,
	checkout datetime,
	amount int
)

-- Insert database
insert into hotel (name, address, area, owner_name)
values
('A1', '54 Le Thanh Nghi', '200', 'A'),
('A2', '285 Doi Can', '300', 'A'),
('A3', '12 Tran Duy Hung', '50', 'B')

insert into room(room_no, hotel_id, area, type, floor)
values
('R001', 1, 20, 'NORMAL', 1),
('R002', 1, 15, 'VIP', 2),
('R003', 1, 30, 'VIP', 2),
('K001', 2, 20, 'NORMAL', 1),
('K002', 2, 20, 'VIP', 2)

insert into book (room_no, checkin, checkout, amount)
values
('R001', '2021-01-15 10:00:00', '2021-01-16 18:00:00', 2),
('R001', '2021-03-02 10:00:00', '2021-03-04 18:00:00', 2),
('R002', '2021-02-05 10:00:00', '2021-02-06 18:00:00', 4),
('R002', '2021-01-15 10:00:00', '2021-01-16 18:00:00', 2),
('K001', '2021-02-15 10:00:00', '2021-02-16 18:00:00', 3),
('K002', '2021-02-15 10:00:00', '2021-02-16 18:00:00', 6),
('R003', '2021-02-15 10:00:00', '2021-02-16 18:00:00', 4)

-- Ten KS (hotel), address (hotel), so phong (dem tu room)
---- Ten KS (hotel), address (hotel), room_id (room)
select hotel.name, hotel.address, room.room_no
from hotel, room
where hotel.id = room.hotel_id
go

select hotel.name, hotel.address, room.room_no
from hotel inner join room on hotel.id = room.hotel_id
go

select hotel.name, hotel.address, room.room_no
from hotel join room on hotel.id = room.hotel_id
go

-- left join
select hotel.name, hotel.address, room.room_no
from hotel left join room on hotel.id = room.hotel_id
go

-- Hien thi tat ca
select hotel.name, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
go

-- Hien thi so phong > 2
select hotel.name, hotel.address, count(room.room_no) 'So Phong'
from hotel left join room on hotel.id = room.hotel_id
group by hotel.name, hotel.address
having count(room.room_no) > 2
go

-- Viet proc -> Truyen tham so @roomNum
create proc proc_count_room
	@roomNum int
as
begin
	select hotel.name, hotel.address, count(room.room_no) 'So Phong'
	from hotel left join room on hotel.id = room.hotel_id
	group by hotel.name, hotel.address
	having count(room.room_no) > @roomNum
end

exec proc_count_room 1
exec proc_count_room 2
exec proc_count_room 3