Alright fellas, here is a SQL evaluation exercise and solutions on database for online gamers who participate in teams.
We have a list of online gamers who participate in teams. We need to keep track of some basic information about our players as well as which players are on which teams.
There is a list of Players and a list of Teams in the form of existing tables, and there are some stored procedures to access those tables, but there are a lot of problems with the existing design.
Your job is to fix the existing data structure by modifying, adding and/or removing components as necessary in order to create a solid, well designed database that meets the requirements and deliverables defined below.
Please provide your solution in the form of a T-SQL script that can be executed on a SQL Server instance all at once (use GO statement separators as needed) to add/edit/delete the tables, stored procedures, etc. that are needed to meet the requirements.
We are using SQL Server 2016 so please be sure to only use statements that are supported on that version of SQL Server.
We are looking for a simple solution. Please do not add any unnecessary complication to the DDL or stored procedure code or use any components or constructs that are not necessary to complete the evaluation. The ideal solution will be well formatted, uncomplicated and easy to read.
Then we have the following scripts that were deliberately badly written.
create table Player ( ID int, LastName varchar(50), FirstName varchar(50), DOB varchar(10), Team1 varchar(50), Team2 varchar(50), Team3 varchar(50) ) create table Team ( ID int, TeamName varchar(50), TeamLeader varchar(100) ) insert into Player values (100, 'Smith', 'Sally', '04/18/1995', 'Red', null, null), (101, 'Jones', 'James', '05/22/1996', 'Blue', 'Red', null), (102, 'Brown', 'Bobby', '11/18/1995', 'Green', 'Blue', null), (103, 'Jones', 'Janet', '08/22/1998', 'Blue', 'Green', 'Red'), (104, 'Magoo', 'Matthew', '12/20/1994', 'Green', null, null), (105, 'Wayfield', 'Wanda', '05/05/1995', 'Red', null, null) insert into Team values (10, 'Red', 'Sally Smith'), (20, 'Blue', 'Bobby Brown'), (30, 'Green', 'Matthew Magoo')
create procedure GetPlayer @ID int, @LastName varchar(50), @FirstName varchar(50), @Team varchar(50) as begin set nocount on if @ID is not null begin select * from Player where ID = @ID end else if @LastName is not null begin select * from Player where LastName = @LastName end else if @FirstName is not null begin select * from Player where FirstName = @FirstName end else if @Team is not null begin select * from Player where Team1 = @Team or Team2 = @Team or Team3 = @Team end end
Here's part of the script to create the tables including the referential integrity constraints:
create table dbo.Player ( ID int identity(1,1) primary key, LastName varchar(50) not null, FirstName varchar(50) not null, DOB varchar(10) not null ) go create table dbo.Team ( ID int identity(1,1) primary key, TeamName varchar(50) not null, TeamLeaderID int foreign key references Player(ID) null ) go create table dbo.TeamMembers ( PlayerID int foreign key references Player(ID) not null, TeamID int foreign key references Team(ID) not null ) go
The referential integrity constraints are
not null and foreign key. We have
not null declaration on columns
LastName, FirstName, TeamName, PlayerID, and
TeamID because those values are required during insert.
There is a many-to-many relationship between Teams and Players. This means many players can belong to multiple teams. To implement this in our data model, we need a bridge
table, hence why we created the additional table
dbo.TeamMembers. This table has only foreign key columns
PlayerID and TeamID referencing
Team tables respectively on those tables'
Here's part of the script to load the tables with data:
set identity_insert dbo.Player on insert into dbo.Player (ID,LastName,FirstName,DOB) values (100, 'Smith', 'Sally', '04/18/1995'), (101, 'Jones', 'James', '05/22/1996'), (102, 'Brown', 'Bobby', '11/18/1995'), (103, 'Jones', 'Janet', '08/22/1998'), (104, 'Magoo', 'Matthew', '12/20/1994'), (105, 'Wayfield', 'Wanda', '05/05/1995') set identity_insert dbo.Player off go set identity_insert dbo.Team on insert into dbo.Team(ID,TeamName,TeamLeaderID) values (10, 'Red', null), (20, 'Blue', null), (30, 'Green', null) set identity_insert dbo.Team off go insert into dbo.TeamMembers values (100, 10), (101, 20), (101, 10), (102, 30), (102, 20), (103, 20), (103, 30), (103, 10), (104, 30), (105, 10) go update dbo.Team set TeamLeaderID=case ID when 10 then 100 when 20 then 102 when 30 then 104 end go
Notice we set
identity_insert on before inserting into the tables
dbo.Player and dbo.Team. We did this to override the auto-generated identity primary keys we
used during those tables definition above.
Here's the refactored/new
setPlayer stored procedure:
create procedure SetPlayer @ID int=null, @LastName varchar(50)=null, @FirstName varchar(50)=null, @DOB varchar(10)=null, @Teams varchar(4000)=null, @IsDelete bit=null, @RetID int=null out as begin set nocount on if @IsDelete=1 begin delete from dbo.TeamMembers where PlayerID=@ID update dbo.Team set TeamLeaderID=null where TeamLeaderID=@ID delete from dbo.Player where ID=@ID return 0 end if @ID is null begin if @LastName is null or @FirstName is null or @DOB is null return -1 insert into Player (LastName,FirstName,DOB) values ( @LastName, @FirstName, @DOB ) set @ID=SCOPE_IDENTITY() end else begin update Player set LastName = isnull(@LastName,LastName), FirstName = isnull(@FirstName,FirstName), DOB = isnull(@DOB,DOB) where ID = @ID end if @ID is not null and @Teams is not null and @Teams!='' begin delete from dbo.TeamMembers where PlayerID=@ID insert into dbo.TeamMembers select @ID,tt.ID from(select ltrim(rtrim(value)) as Team from STRING_SPLIT(@Teams, ',')) as t inner join dbo.Team as tt on tt.TeamName=t.Team end set @RetID=@ID return 0 end go
Finally here is the new
getPlayer stored procedure
create procedure GetPlayer @ID int =NULL, @LastName varchar(50)=NULL, @FirstName varchar(50)=NULL, @Team varchar(50)=NULL as begin set nocount on select p.ID,p.FirstName, p.LastName, p.DOB, t.TeamName from dbo.Player as p left join dbo.TeamMembers as tm on p.ID=tm.PlayerID inner join dbo.Team as t on t.ID=tm.TeamID WHERE (@ID is null or p.ID=@ID) and (@LastName is null or p.LastName like '%' + @LastName + '%') and (@FirstName is null or p.FirstName like '%' + @FirstName + '%') and (@Team is null or t.TeamName like '%' + @Team + '%') return 0 end go
Note that we set these stored procedure's parameters' initial value to null to make them optional. This allows us to call the SP by passing only one or some of the parameters. This means
we can call the SP like
EXEC PROC dbo.GetPlayer @ID=1. Let me know what you think in the comment. Do you think this could be implemented differently?
More from @sqldibia