December 7, 2014 at 10:51 am
1.)Can Somebody help me with codes for these views:
VGameBasicScore, VGameSpareScores, VGameStrikeNoStrikeScore, VGameStrikeStrikeScore, VGameTotalScore
It has to look something like this.
GO
CREATE VIEW VGameBasicScore
AS
SELECT
TPGFB.intGameIndex
,TPGF.intFrameIndex
,TPGF.intPlayerID
,TPGFB.intPinCount
,ISNULL ( SUM (TPGFB.intFrameIndex) , 0 ) AS intBasicScore
FROM
TPlayerGameFrameBalls AS TPGFB
INNER JOIN TPlayerGameFrames AS TPGF
LEFT OUTER JOIN TPlayerGameFrameBalls AS TPGFB
ON( TPGF.intFrameIndex = TPGF.intFrameIndex
AND TPGF.intGameIndex = TPGF.intGameIndex )
INNER JOIN TPGF AS TPlayerGameFrames
ON( TPGF.intFrameIndex = TPGF.intFrameIndex )
INNER JOIN TTeams AS TT
ON(TT.intTeamID = TT.intLeagueID)
ON( TPGF.intGameIndex = TPGF.intGameIndex)
GROUP BY
TPGF.intFrameIndex
,TPGF.intGameIndex
,TPGFB.intPinCount
,TPGFB.intBallIndex
GO
Below are my Tables and what i did so far.
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Step #1.1: Create Tables
-- ---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TYears
(
intYearID INTEGER NOT NULL
,strYear VARCHAR (50) NOT NUll
,CONSTRAINT TYears_PK PRIMARY KEY ( intYearID )
)
CREATE TABLE TLeagues
(
intLeagueID INTEGER NOT NULL
,strLeague VARCHAR(50) NOT NULL
,CONSTRAINT TLeagues_PK PRIMARY KEY ( intLeagueID )
)
CREATE TABLE TTeams
(
intTeamID INTEGER NOT NULL
,strTeam VARCHAR(50) NOT NULL
,CONSTRAINT TTeams_PK PRIMARY KEY ( intTeamID )
)
CREATE TABLE TPlayers
(
intPlayerID INTEGER NOT NULL
,strFirstName VARCHAR(50) NOT NULL
,strLastName VARCHAR(50) NOT NULL
,strAddress VARCHAR(50) NOT NULL
,strPhoneNumber VARCHAR(50) NOT NULL
,CONSTRAINT TPlayers_PK PRIMARY KEY ( intPlayerID )
)
CREATE TABLE TTeamPlayers
(
intTeamID INTEGER NOT NULL
,intPlayerID INTEGER NOT NULL
,CONSTRAINT TTeamPlayers_PK PRIMARY KEY ( intTeamID, intPlayerID )
)
CREATE TABLE TYearLeagues
(
intYearID INTEGER NOT NULL
,intLeagueID INTEGER NOT NULL
,CONSTRAINT TYearLeagues_PK PRIMARY KEY (intYearID, intLeagueID )
)
CREATE TABLE TYearLeagueTeams
(
intYearID INTEGER NOT NULL
,intLeagueID INTEGER NOT NULL
,intTeamID INTEGER NOT NULL
,CONSTRAINT TYearLeagueTeams_PK PRIMARY KEY (intYearID, intLeagueID,intTeamID )
)
CREATE TABLE TGames
(
intGameID INTEGER NOT NULL
,strGames VARCHAR(50) NOT NULL
,strWeeks VARCHAR(50) NOT NULL
,CONSTRAINT TGames_PK PRIMARY KEY (intGameID )
)
CREATE TABLE TPlayerGames
(
intPlayerID INTEGER NOT NULL
,intGameID INTEGER NOT NULL
,CONSTRAINT TPlayerGames_PK PRIMARY KEY (intPlayerID,intGameID )
)
CREATE TABLE TPlayerGameFrames
(
intPlayerID INTEGER NOT NULL
,intGameID INTEGER NOT NULL
,intFrameID INTEGER NOT NULL
,CONSTRAINT TPlayerGameFrames_PK PRIMARY KEY ( intPlayerID ,intGameID,intFrameID)
)
CREATE TABLE TPlayerGameFrameThrows
(
intPlayerID INTEGER NOT NULL
,intGameID INTEGER NOT NULL
,intFrameThrows INTEGER NOT NULL
,intPinCount INTEGER NOT NULL
,CONSTRAINT TPlayerGameFrameThrows_PK PRIMARY KEY (intPlayerID ,intGameID ,intFrameThrows ,intPinCount )
)
CREATE TABLE TPlayerGameFrameBalls
(
intPlayerID INTEGER NOT NULL
,intGameID INTEGER NOT NULL
,intFrameBall INTEGER NOT NULL
,intFrameBallIndex INTEGER NOT NULL
,rvLastUpdated ROWVERSION NOT NULL
,CONSTRAINT TPlayerGameFrameBalls_PK PRIMARY KEY (intPlayerID ,intGameID ,intFrameBall,intFrameBallIndex )
)
CREATE TABLE TScores
(
intPlayerID INTEGER NOT NULL
,intFrameID INTEGER NOT NULL
,intFrameThrows INTEGER NOT NULL
,intFrameBall INTEGER NOT NULL
,intNumberOfPins INTEGER NOT NULL
,CONSTRAINT TScores_PK PRIMARY KEY (intPlayerID,intFrameID ,intFrameThrows,intFrameBall,intNumberOfPins)
)
-- --------------------------------------------------------------------------------
-- Step #1.2: Identify and Create Foreign Keys
-- --------------------------------------------------------------------------------
--
-- # Child Parent Column(s)
-- - ----- ------ ---------
-- 1 TYearLeagues TLeagues intLeagueID
-- 2 TYearLeagues TYears intYearID
-- 3 TYearLeagues TYearLeagueTeams intYearID, intLeagueID, intTeamID
-- 4 TTeamPlayers TTeams intTeamID
-- 5 TTeamPlayers TPlayers intPlayerID
-- 6 TYearLeagueTeams TTeams intTeamID
-- 7 TPlayerGames TGames intGameID
-- 8 TPlayerGames TPlayers intPlayerID
-- 9 TPlayerGames TPlayerGameFrames intPlayerID,intGameID,intFrameID
-- 10 TPlayerGameFrames TPlayerGameFrameBalls intPlayerID,intGameID,intFrameBall
-- 11 TPlayerGameFrameS TPlayerGameFrameThrows intPlayerID,intGameID,intFrameThrows
-- 1
ALTER TABLE TYearLeagues ADD CONSTRAINT TYearLeagues_TLeagues_FK
FOREIGN KEY ( intLeagueID ) REFERENCES TLeagues ( intLeagueID)
---- 2
ALTER TABLE TYearLeagues ADD CONSTRAINT TYearLeagues_TYears_FK
FOREIGN KEY ( intYearID ) REFERENCES TYears ( intYearID )
----3
ALTER TABLE TYearLeagueTeams ADD CONSTRAINT TYearLeagueTeams_TYearLeagues_FK
FOREIGN KEY (intYearID, intLeagueID ) REFERENCES TYearLeagues ( intYearID, intLeagueID)
-- 4
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TTeams_FK
FOREIGN KEY ( intTeamID ) REFERENCES TTeams ( intTeamID )
-- 5
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TPlayers_FK
FOREIGN KEY ( intPlayerID ) REFERENCES TPlayers ( intPlayerID )
-- 6
ALTER TABLE TYearLeagueTeams ADD CONSTRAINT TYearLeagueTeams_TTeams_FK
FOREIGN KEY ( intTeamID) REFERENCES TTeams (intTeamID )
-- 7
ALTER TABLE TPlayerGames ADD CONSTRAINT TPlayerGames_TGames_FK
FOREIGN KEY (intGameID) REFERENCES TGames( intGameID )
-- 8
ALTER TABLE TPlayerGames ADD CONSTRAINT TPlayerGames_TPlayers_FK
FOREIGN KEY ( intPlayerID) REFERENCES TPlayers ( intPlayerID)
-- 9
ALTER TABLE TPlayerGames ADD CONSTRAINT TPlayerGames_TPlayerGameFrames_FK
FOREIGN KEY ( intPlayerID,intGameID,intFrameID ) REFERENCES TPlayerGameFrames ( intPlayerID ,intGameID,intFrameID)
-- 10
ALTER TABLE TPlayerGameFrames ADD CONSTRAINT TPlayerGameFrames_TPlayerGameFrameBalls_FK
FOREIGN KEY ( intPlayerID,intGameID,intFrameBall,intFrameBallIndex) REFERENCES TPlayerGameFrameBalls ( intPlayerID,intGameID,intFrameBall,intFrameBallIndex )
-- 11
ALTER TABLE TPlayerGameFrames ADD CONSTRAINT TPlayerGameFrames_TPlayerGameFramesThrows_FK
FOREIGN KEY ( intPlayerID,intGameID,intFrameThrows,intPinCount) REFERENCES TPlayerGameFramesThrows ( intPlayerID,intGameID,intFrameThrows,intPinCount )
-- --------------------------------------------------------------------------------
-- Step #1.3: Add at least 2 records per table
-- --------------------------------------------------------------------------------
INSERT INTO TLeagues( intLeagueID,strLeague )
VALUES ( 1, 'Singles league1 ' )
,( 2, 'Couples league' )
,( 3, 'Singles league2 ' )
,( 4, 'Singles league3 ' )
INSERT INTO TTeams( intTeamID, strTeam )
VALUES ( 1, 'Hungry-Hungry Hippos')
,( 2, 'Trees Rats')
,( 3, 'Wimpy Wombats')
,( 4, 'Maloderous Moses')
,( 5, 'Dead Mice')
,( 6, 'Bengals')
,( 7, 'Eagles')
,( 8, 'Dyanamo' )
,( 9, 'Platinum')
,( 10, 'Thunder')
,( 11, 'Crew')
,( 12, 'Patriots')
INSERT INTO TPlayers( intPlayerID, strFirstName, strLastName,strAddress,strPhoneNumber )
VALUES ( 1, 'Bill', 'Ding','ABC','5678907' )
,( 2, 'Sue', 'Flay','DEF','3678907' )
,( 3, 'Doug', 'Out','JJJ','1678907' )
,( 4, 'Justin', 'Case','FOOF','5648907' )
,( 5, 'Pete', 'Abred','GHYT','2678907' )
,( 6, 'Adam', 'Baum','OHIO','5478907' )
,( 7, 'Kitty', 'Car','TEXAS','5673907' )
,( 8, 'Bart', 'Ender','NEW','5678957' )
,( 9, 'Cliff', 'Hanger','BOSTON','5678917' )
,( 10, 'Jerry', 'Mander','FREETOWN','5678807' )
,( 11, 'Cybil', 'Ling','EASYTOWN','5675907' )
,( 12, 'Rusty', 'Nail','JOYRIDE','5673907' )
,( 13, 'No-Fun', 'Jason','WHAT','5678507' )
,( 14, 'Jojo', 'May','YIPEYEH','5656907')
,( 15, 'Jason', 'Teen','lOVELAND','2667920' )
,( 16, 'Adam', 'Lazy','MASON','54890907' )
,( 17, 'Navas', 'Tore','TEXAS','5670607' )
,( 18, 'Bart', 'Scott','KENTUKY','5676957' )
,( 19, 'Pete', 'Alfred','SOUTH','16670907' )
,( 20, 'Jane', 'Ball','OHIO','5432907' )
,( 21, 'Jose', 'Car','TEXAS','5600907' )
,( 22, 'Susan', 'Render','KENTUKY','5676557' )
,( 23, 'Joel', 'Smith','CALI','2677707' )
,( 24, 'Tim', 'Lewis','OHIO','54564907' )
INSERT INTO TTeamPlayers( intTeamID, intPlayerID )
VALUES ( 1, 1 )
,( 1, 2 )
,( 1, 3 )
,( 1, 4 )
,( 2, 5 )
,( 2, 6 )
,( 2, 7 )
,( 2, 8 )
,( 3, 9 )
,( 3, 10 )
,( 3, 11 )
,( 3, 12 )
,( 4, 13 )
,( 4, 14 )
,( 4, 15 )
,( 4, 16 )
INSERT INTO TYears( intYearID, strYear )
VALUES ( 1, ' 2004' )
,( 2, ' 2005' )
,( 3, ' 2006')
,( 4, ' 2007')
INSERT INTO TYearLeagues( intYearID, intLeagueID )
VALUES ( 1, 1)
,( 1, 2)
,( 1, 3)
,( 1, 4)
,( 2, 1)
,( 2, 2)
,( 2, 3)
,( 2, 4)
,( 3, 1)
,( 3, 2)
,( 3, 3)
,( 3, 4)
,( 4, 1)
,( 4, 2)
,( 4, 3)
,( 4, 4)
INSERT INTO TYearLeagueTeams( intYearID, intLeagueID, intTeamID )
VALUES
( 1, 1,1)
,( 1, 2,2)
,( 1, 3,3)
,( 1, 4,4)
,( 2, 1,5)
,( 2, 2,6)
,( 2, 3,7)
,( 2, 4,8)
,( 3, 1,9)
,( 3, 2,10)
,( 3, 3,11)
,( 3, 4,12)
,( 4, 1,2)
,( 4, 2,3)
,( 4, 3,7)
,( 4, 4,4)
INSERT INTO TGames( intGameID,strGames,strWeeks )
VALUES ( 1, 'Game1 ','Week1' )
,( 2, 'Game2' ,'Week1' )
,( 3, 'Game3 ' ,'Week1')
,( 1, 'Game1 ','Week2' )
,( 2, 'Game2' ,'Week2' )
,( 3, 'Game3 ' ,'Week2')
,( 1, 'Game1 ','Week3' )
,( 2, 'Game2' ,'Week3' )
,( 3, 'Game3 ' ,'Week3')
,( 1, 'Game1 ','Week4' )
,( 2, 'Game2' ,'Week4' )
,( 3, 'Game3 ' ,'Week4')
,( 1, 'Game1 ','Week5' )
,( 2, 'Game2' ,'Week5' )
,( 3, 'Game3 ' ,'Week5')
,( 1, 'Game1 ','Week6' )
,( 2, 'Game2' ,'Week6' )
,( 3, 'Game3 ' ,'Week6')
INSERT INTO TPlayerGames( intPlayerID ,intGameID)
VALUES ( 1, 1)
,( 1, 2)
,( 1, 3)
,( 2, 1)
,( 2, 2)
,( 2, 3)
,( 3, 1)
,( 3, 2)
,( 3, 3)
,( 4, 1)
,( 4, 2)
,( 4, 3)
,( 5, 1)
,( 5, 2)
,( 5, 3)
,( 6, 1)
,( 6, 2)
,( 6, 3)
,( 7, 1)
,( 7, 2)
,( 7, 3)
,( 8, 1)
,( 8, 2)
,( 8, 3)
,( 9, 1)
,( 9, 2)
,( 9, 3)
,( 10, 1)
,( 10, 2)
,( 10, 3)
,( 11, 1)
,( 11, 2)
,( 11, 3)
,( 12, 1)
,( 12, 2)
,( 12, 3)
,( 13, 1)
,( 13, 2)
,( 13, 3)
,( 14, 1)
,( 14, 2)
,( 14, 3)
,( 15, 1)
,( 15, 2)
,( 15, 3)
,( 16, 1)
,( 16, 2)
,( 16, 3)
,( 17, 1)
,( 17, 2)
,( 17, 3)
INSERT INTO TPlayerGameFrames( intPlayerID ,intGameID,intFrameID )
VALUES ( 1, 1 ,1)
,( 1 , 1 ,2)
,( 1, 1 ,1)
,( 1 , 1 ,2)
,( 1, 1 ,1)
,( 1 , 1 ,1)
,( 1, 1 ,2)
,( 1 , 1 ,1)
,( 1, 1 ,2)
,( 1 , 1 ,1)
,( 1, 2,2)
,( 1 , 2 ,1)
,( 1, 2 ,2)
,( 1 , 2 ,1)
,( 1, 2 ,2)
,( 1 , 2 ,1)
,( 1, 2 ,2)
,( 1 , 2 ,1)
,( 1, 2 ,2)
,( 1 , 2 ,1)
,( 1, 3,1 )
,( 1 , 3 ,2)
,( 1, 3,1)
,( 1 , 3 ,2)
,( 1, 3,1)
,( 1 , 3 ,2)
,( 1, 3 ,1)
,( 1 , 3 ,2)
,( 1, 3 ,1)
,( 1 , 3 ,2)
,( 2, 1 ,1)
,( 2 , 1 ,2)
,( 2, 1,1)
,( 2 , 1 ,2)
,( 2, 1 ,1)
,( 2 , 1 ,2)
,( 2, 1 ,1)
,( 2 , 1 ,2)
,( 2, 1 ,1)
,( 2 , 1 ,2)
,( 2, 2 ,1)
,( 2 , 2 ,2)
,( 2, 2 ,1)
,( 2 , 2 ,1)
,( 2, 2 ,2)
,( 2 , 2 ,1)
,( 2, 2 ,2)
,( 2 , 2 ,1)
,( 2, 2 ,2)
,( 2 , 2 ,1)
,( 2, 3, 2)
,( 2 , 3 ,1)
,( 2, 3,2)
,( 2 , 3 ,1)
,( 2, 3 ,2)
,( 2 , 3 ,1)
,( 2, 3 ,2)
,( 2 , 3 ,1)
,( 2, 3,2)
,( 2 , 3 ,1)
,( 3, 1,2)
,( 3 , 1 ,1)
,( 3, 1,2)
,( 3 , 1 ,1)
,( 3, 1 ,2)
,( 3 , 1 ,1)
,( 3, 1,2)
,( 3 , 1 ,1)
,( 3, 1 ,2)
,( 3 , 1,1)
,( 3, 2,2 )
,( 3 , 2 ,1)
,( 3, 2 ,2)
,( 3 , 2 ,1)
,( 3, 2 ,2)
,( 3 , 2 ,1)
,( 3, 2 ,2)
,( 3 , 2 ,1)
,( 3, 2 ,2)
,( 3 , 2 ,1)
,( 3, 3 ,2)
,( 3 , 3 ,1)
,( 3, 3 ,2)
,( 3 , 3 ,1)
,( 3, 3 ,2)
,( 3 , 3,1)
,( 3, 3,2)
,( 3 , 3 ,1)
,( 3, 3 ,2)
,( 3 , 3 ,1)
,( 4, 1,2)
,( 4 , 1 ,1)
,( 4, 1,2)
,( 4 , 1 ,1)
,( 4, 1 ,2)
,( 4 , 1 ,1)
,( 4, 1 ,2)
,( 4 , 1 ,1)
,( 4, 1 ,2)
,( 4 , 1 ,1)
,( 4, 2,1)
,( 4 , 2 ,2)
,( 4, 2 ,1)
,( 4 , 2 ,2)
,( 4, 2 ,1)
,( 4 , 2 ,2)
,( 4, 2 ,1)
,( 4 , 2 ,2)
,( 4, 2 ,1)
,( 4 , 2 ,2)
,( 4, 3 ,1)
,( 4 , 3 ,2)
,( 4, 3 ,1)
,( 4 , 3 ,2)
,( 4, 3 ,1)
,( 4 , 3 ,2)
,( 4, 3 ,1)
,( 4 , 3 ,2)
,( 4, 3 ,1)
,( 4 , 3 ,2)
,( 5, 1 ,1)
,( 5 , 1 ,2)
,( 5, 1 ,1)
,( 5 , 1 ,2)
,( 5, 1 ,1)
,( 5 , 1 ,2)
,( 5, 1 ,1)
,( 5 , 1 ,2)
,( 5, 1 ,1)
,( 5 , 1,2)
,( 5, 2,1)
,( 5 , 2 ,2)
,( 5, 2 ,1)
,( 5 , 2 ,2)
,( 5, 2 ,1)
,( 5 , 2 ,2)
,( 5, 2 ,1)
,( 5 , 2 ,2)
,( 5, 2 ,1)
,( 5 , 2 ,2)
,( 5, 3 ,1)
,( 5 , 3 ,2)
,( 5, 3 ,1)
,( 5 , 3 ,2)
,( 5, 3 ,1)
,( 5 , 3 ,1)
,( 5, 3 ,2)
,( 5 , 3 ,1)
,( 5, 3 ,2)
,( 5 , 3 ,1)
,( 6, 1 ,2)
,( 6 , 1 ,1)
,( 6, 1 ,1)
,( 6 , 1 ,2 )
,( 6, 1 ,1)
,( 6 , 1 ,1)
,( 6, 1 ,2)
,( 6 , 1 ,1)
,( 6, 1 ,2)
,( 6 , 1 ,1)
,( 6, 2 ,1)
,( 6 , 2 ,2)
,( 6, 2 ,1)
,( 6 , 2 ,2)
,( 6, 2 ,1)
,( 6 , 2 ,2)
,( 6, 2 ,1)
,( 6 , 2 ,2)
,( 6, 2 ,1)
,( 6 , 2 ,2)
,( 6, 3,1 )
,( 6 , 3 ,2)
,( 6, 3 ,1)
,( 6 , 3 ,2)
,( 6, 3 ,1)
,( 6 , 3 ,2)
,( 6, 3 ,1)
,( 6 , 3 ,2)
,( 6, 3 ,1)
,( 6 , 3 ,2)
,( 7, 1 ,1)
,( 7 , 1 ,2)
,( 7, 1 ,1)
,( 7 , 1 ,2 )
,( 7, 1 ,1)
,( 7 , 1 ,2 )
,( 7, 1 ,1)
,( 7 , 1 ,2)
,( 7, 1 ,1)
,( 7 , 1 ,2)
,( 7, 2 ,1)
,( 7 , 2 ,2)
,( 7, 2 ,1)
,( 7 , 2 ,2)
,( 7, 2 ,1)
,( 7 , 2 ,2)
,( 7, 2 ,1)
,( 7 , 2 ,2)
,( 7, 2 ,1)
,( 7 , 2 ,2)
,( 7, 3 ,1)
,( 7 , 3 ,2)
,( 7, 3 ,1)
,( 7 , 3 ,2)
,( 7, 3 ,1)
,( 7 , 3 ,2)
,( 7, 3 ,1)
,( 7 , 3 ,2)
,( 7, 3,1)
,( 7 , 3 ,2)
,( 8, 1 ,1)
,( 8 , 1 ,1)
,( 8, 1 ,2)
,( 8 , 1 ,1)
,( 8, 1 ,2)
,( 8 , 1 ,1)
,( 8, 1 ,2)
,( 8 , 1 ,1)
,( 8, 1 ,2)
,( 8 , 1 ,1)
,( 8, 2 ,2)
,( 8 , 2 ,1)
,( 8, 2 ,2)
,( 8 , 2 ,1)
,( 8, 2 ,2)
,( 8 , 2 ,1)
,( 8, 2 ,2)
,( 8 , 2 ,1)
,( 8, 2 ,2)
,( 8 , 2 ,1)
,( 8, 3 ,2)
,( 8 , 3 ,1)
,( 8, 3 ,2)
,( 8 , 3 ,1)
,( 8, 3 ,2)
,( 8 , 3 ,1)
,( 8, 3 ,2)
,( 8 , 3 ,1)
,( 8, 3 ,2)
,( 8 , 3 ,1)
,( 9, 1 ,2)
,( 9 , 1 ,1)
,( 9, 1 ,2)
,( 9 , 1 ,1)
,( 9, 1 ,2)
,( 9 , 1 ,1)
,( 9, 1 ,2)
,( 9 , 1 ,1)
,( 9, 1 ,2)
,( 9 , 1 ,1)
,( 9, 2 ,2)
,( 9 , 2 ,1)
,( 9, 2 ,2)
,( 9 , 2 ,1)
,( 9, 2 ,2)
,( 9 , 2 ,1)
,( 9, 2 ,2)
,( 9 , 2 ,1)
,( 9, 2 ,2)
,( 9 , 2 ,1)
,( 9, 3 ,2)
,( 9 , 3 ,1)
,( 9, 3 ,2)
,( 9 , 3 ,1)
,( 9, 3 ,2)
,( 9 , 3 ,1)
,( 9, 3 ,2)
,( 9 , 3 ,1)
,( 9, 3,2 )
,( 9 , 3 ,1)
,( 10, 1,2)
,( 10 , 1,1)
,( 10, 1 ,2)
,( 10 , 1 ,1)
,( 10, 1 ,2)
,( 10 , 1 ,1)
,( 10, 1 ,2)
,( 10 , 1 ,1)
,( 10, 1 ,2)
,( 10 , 1 ,1)
,( 10, 2,2)
,( 10 , 2 ,1)
,( 10, 2 ,2)
,( 10 , 2 ,1)
,( 10, 2 ,2)
,( 10 , 2 ,1)
,( 10, 2 ,2)
,( 10 , 2 ,1)
,( 10, 2 ,2)
,( 10 , 2,1)
,( 10, 3 ,2)
,( 10 , 3 ,1)
,( 10, 3 ,2)
,( 10, 3 ,1)
,( 10, 3 ,2)
,( 10 , 3 ,1)
,( 10, 3 ,2)
,( 10 , 3 ,1)
,( 10, 3 ,2)
,( 10 , 3,1)
,( 11, 1 ,2)
,( 11 , 1 ,1)
,( 11, 1 ,2)
,( 11 , 1 ,1)
,( 11, 1 ,2)
,( 11 , 1 ,1)
,( 11, 1 ,2)
,( 11 , 1 ,1)
,( 11, 1 ,2)
,( 11 , 1 ,1)
,( 11, 2 ,2)
,( 11 , 2 ,1)
,( 11, 2 ,2)
,( 11 , 2 ,1)
,( 11, 2 ,2)
,( 11 , 2 ,1)
,( 11, 2 ,2)
,( 11 , 2 ,1)
,( 11, 2 ,2)
,( 11 , 2 ,1)
,( 11, 3 ,2)
,( 11 , 3 ,1)
,( 11, 3 ,2)
,( 11, 3 ,1)
,( 11, 3 ,2)
,( 11 , 3 ,1)
,( 11, 3 ,2)
,( 11 , 3 ,1)
,( 11, 3 ,2)
,( 11 , 3 ,1)
,( 12, 1,2 )
,( 12 , 1 ,1)
,( 12, 1 ,2)
,( 12 , 1 ,1)
,( 12, 1 ,2)
,( 12 , 1 ,1)
,( 12, 1 ,2)
,( 12 , 1 ,1)
,( 12, 1 ,2)
,( 12 , 1 ,1)
,( 12, 2 ,2)
,( 12 , 2 ,1)
,( 12, 2 ,1)
,( 12 , 2 ,2)
,( 12, 2 ,1)
,( 12 , 2 ,2)
,( 12, 2 ,1)
,( 12 , 2 ,2)
,( 12, 2 ,1)
,( 12 , 2 ,2)
,( 12, 3, 1)
,( 12 , 3 ,2)
,( 12, 3 ,1)
,( 12 , 3 ,2)
,( 12, 3 ,1)
,( 12 , 3 ,2)
,( 12, 3 ,1)
,( 12 , 3 ,2)
,( 12, 3 ,1)
,( 12 , 3,2)
INSERT INTO TPlayerGameFrameBalls( intPlayerID ,intGameID,intFrameBall,intBallIndex)
VALUES
( 1, 1 ,1,1 )
,( 2, 2 ,2,2 )
,( 3, 3 ,1 ,1)
,( 4, 4 ,2 ,2)
,( 5, 5 ,1,1 )
,( 6 ,6 ,2,2 )
,( 7, 1 ,1 ,1)
,( 8, 2 ,2 ,2)
,( 9, 3 ,1 ,1)
,( 10, 4 ,2 ,2)
,( 11, 5 ,1,1)
,( 12, 6 ,2 ,2)
,( 1, 1 ,1,1 )
,( 2 , 2 ,2,2)
,( 3, 3, 1,1 )
,( 4, 4,2,2)
,( 5, 5,1,1)
,( 6 ,6 ,2,2)
,( 7, 1 ,1,1)
,( 8, 2 ,2 ,2)
,( 9, 3, 1 ,1)
,( 10 ,4 ,2,2)
,( 11, 5 ,1,1)
,( 12, 6, 2,2)
,( 1, 1 ,1,2)
,( 2 , 2 ,2,1)
,( 3, 3,1,2)
,( 4, 4, 2,1)
,( 5, 5,1,2 )
,( 6 ,6 ,2,1)
,( 7, 1, 1 ,2)
,( 8, 2 , 2,1)
,( 9, 3,1 ,2)
,( 10 ,4 , 2 ,1)
,( 11, 5,1 ,2 )
,( 12, 6, 2 ,1)
,( 1, 1,1 ,2)
,( 2 , 2 ,2,1 )
,( 3, 3,1,2)
,( 4, 4 ,2,1 )
,( 5, 5,1,2)
,( 6 ,6 , 2,1)
,( 7, 1,1 ,2)
,( 8, 2,2 ,1)
,( 9, 3,1 ,2)
,( 10 ,4 ,2,1 )
,( 11, 5,1,2)
,( 12, 6,2,1 )
,( 1, 1,1 ,2)
,( 2 , 2 ,2,1 )
,( 3, 3,1,2)
,( 4, 4,2,1 )
,( 5, 5 ,1,2)
,( 6 ,6 ,2,1)
,( 7, 1 ,1,2 )
,( 8, 2,2 ,1)
,( 9, 3,1 ,2)
,( 10 ,4,2 ,1)
,( 11, 5,1 ,2)
,( 12, 6,2 ,1)
,( 1, 1,1,2)
,( 2 , 2,2 ,1)
,( 3, 3 ,1 ,2)
,( 4, 4 ,2 ,1)
,( 5, 5,1,2)
,( 6 ,6 ,2,1)
,( 7, 1 ,1 ,2)
,( 8, 2, 2,1 )
,( 9, 3 ,1 ,2)
,( 10 ,4 ,2 ,1)
,( 11, 5,1 ,2)
,( 12, 6,2 ,1)
,( 1, 1 ,1,2 )
,( 2 , 2 ,2,1)
,( 3, 3, 1,1)
,( 4, 4 ,2 ,2)
,( 5, 5,1 ,1)
,( 6 ,6 ,2,2)
,( 7, 1 ,1 ,1)
,( 8, 2 ,2,2)
,( 9, 3 ,1 ,1)
,( 10 ,4,2,2)
,( 11, 5,1 ,1)
,( 12, 6,2 ,2)
,( 1, 1 ,1,1)
,( 2 , 2 ,2,2)
,( 3, 3, 1,1 )
,( 4, 4, 2 ,2)
,( 5, 5, 1 ,1)
,( 6 ,6 , 2,2)
,( 7, 1 ,1,1 )
,( 8, 2 ,2,2)
,( 9, 3,1,1)
,( 10 ,4 ,2,2)
,( 11, 5 ,1,1 )
,( 12, 6,2,2)
,( 1, 1,1,1)
,( 2 , 2 , 2,2)
,( 3, 3, 1 ,1)
,( 4, 4, 2 ,2)
,( 5, 5, 1,1)
,( 6 ,6 ,2,2)
,( 7, 1,1 ,1)
,( 8, 2,2,1)
,( 9, 3 ,1,2)
,( 10 ,4 ,2,1)
,( 11, 5,1 ,2)
,( 12,9 ,2,1)
,( 1, 1,1,2)
,( 2 , 2 ,2,1 )
,( 3, 3 ,1,2)
,( 4, 4,2 ,1)
,( 5, 5, 1 ,2)
,( 6 ,6 , 2 ,1)
,( 7, 1, 1,2)
,( 8, 2 ,2,1)
,( 9, 3, 1,2 )
,( 10 ,4, 2,1)
,( 11, 5 ,1,2 )
,( 12, 6,2 ,1)
INSERT INTO TPlayerGameFrameThrows( intPlayerID ,intGameID,intFrameThrows,intPinCount)
VALUES
( 1, 1 ,1,1 )
,( 2, 2 ,2,1 )
,( 3, 3 ,1,1 )
,( 4, 4 ,2,1 )
,( 5, 5 ,1,1 )
,( 6 ,6 ,2,1 )
,( 7, 1 ,1,1 )
,( 8, 2 ,2,1 )
,( 9, 3 ,1,1 )
,( 10, 4 ,2,1 )
,( 11, 5 ,1,1 )
,( 12, 6 ,2,1 )
,( 1, 1 ,1,2 )
,( 2 , 2 ,2,2)
,( 3, 3, 1, 2 )
,( 4, 4,2, 2 )
,( 5, 5,1, 2 )
,( 6 ,6 ,2, 2 )
,( 7, 1 ,1, 2 )
,( 8, 2 ,2,2 )
,( 9, 3, 1, 2 )
,( 10 ,4 ,2, 2)
,( 11, 5 ,1, 2)
,( 12, 6, 2, 2 )
,( 1, 1 ,1, 3 )
,( 2 , 2 , 2, 3)
,( 3, 3,1, 3 )
,( 4, 4, 2, 3 )
,( 5, 5,1, 3 )
,( 6 ,6 , 2, 3)
,( 7, 1, 1, 3 )
,( 8, 2 , 2, 3)
,( 9, 3,1, 3 )
,( 10 ,4 , 2, 3 )
,( 11, 5,1, 3 )
,( 12, 6, 2, 3 )
,( 1, 1,1, 4 )
,( 2 , 2 ,2, 4 )
,( 3, 3,1, 4 )
,( 4, 4 ,2, 4 )
,( 5, 5,1, 4 )
,( 6 ,6 , 2, 4 )
,( 7, 1,1, 4 )
,( 8, 2,2, 4 )
,( 9, 3,1, 4 )
,( 10 ,4 ,2, 4 )
,( 11, 5,1, 4 )
,( 12, 6,2, 4 )
,( 1, 1,1, 5 )
,( 2 , 2 ,2, 5 )
,( 3, 3,1, 5 )
,( 4, 4,2, 5 )
,( 5, 5 ,1, 5 )
,( 6 ,6 ,2, 5 )
,( 7, 1 ,1, 5 )
,( 8, 2,2, 5 )
,( 9, 3,1, 5 )
,( 10 ,4,2, 5 )
,( 11, 5,1, 5 )
,( 12, 6,2, 5 )
,( 1, 1,1, 6 )
,( 2 , 2,2, 6 )
,( 3, 3 ,1, 6 )
,( 4, 4 ,2, 6 )
,( 5, 5,1, 6 )
,( 6 ,6 ,2, 6 )
,( 7, 1 ,1, 6 )
,( 8, 2, 2, 6 )
,( 9, 3 ,1, 6 )
,( 10 ,4 ,2, 6 )
,( 11, 5,1, 6 )
,( 12, 6,2, 6 )
,( 1, 1 ,1, 7 )
,( 2 , 2 ,2, 7 )
,( 3, 3, 1, 7)
,( 4, 4 ,2, 7 )
,( 5, 5,1, 7 )
,( 6 ,6 ,2, 7)
,( 7, 1 ,1, 7 )
,( 8, 2 ,2, 7)
,( 9, 3 ,1, 7 )
,( 10 ,4,2, 7)
,( 11, 5,1, 7 )
,( 12, 6,2, 7 )
,( 1, 1 ,1, 8 )
,( 2 , 2 ,2, 8 )
,( 3, 3, 1, 8 )
,( 4, 4, 2, 8 )
,( 5, 5, 1, 8 )
,( 6 ,6 , 2, 8 )
,( 7, 1 ,1, 8 )
,( 8, 2 ,2, 8 )
,( 9, 3,1, 8 )
,( 10 ,4 ,2, 8 )
,( 11, 5 ,1, 8 )
,( 12, 6,2, 8 )
,( 1, 1,1, 9 )
,( 2 , 2 , 2, 9 )
,( 3, 3, 1, 9 )
,( 4, 4, 2, 9 )
,( 5, 5, 1, 9)
,( 6 ,6 ,2, 9)
,( 7, 1,1, 9 )
,( 8, 2,2, 9 )
,( 9, 3 ,1, 9 )
,( 10 ,4 ,2, 9)
,( 11, 5,1, 9 )
,( 12,9 ,2, 9)
,( 1, 1,1, 10 )
,( 2 , 2 ,2, 10 )
,( 3, 3 ,1, 10 )
,( 4, 4,2, 10 )
,( 5, 5, 1, 10 )
,( 6 ,6 , 2, 10 )
,( 7, 1, 1, 10 )
,( 8, 2 ,2, 10 )
,( 9, 3, 1, 10 )
,( 10 ,4, 2, 10 )
,( 11, 5 ,1, 10 )
,( 12, 6,2, 10 )
INSERT INTO TScores(intPlayerID,intFrameID,intFrameThrows,intFrameBall,intNumberOfPins )
VALUES
( 1, 1,1,1,1)
,( 1, 2,2,2,2)
,( 1, 3,1,1,3)
,( 1, 4,2,2,4)
,( 1, 5,1,1,5)
,( 1, 6,2,2,6)
,( 1, 7,1,1,7)
,( 1, 8,2,2,8)
,( 1, 9,1,1,9)
,( 1, 10,2,2,10)
,( 2, 1,1,1,1)
,( 2, 2,2,2,2)
,( 2, 3,1,1,3)
,( 2, 4,2,2,4)
,( 2, 5,1,1,5)
,( 2, 6,2,2,6)
,( 2, 7,1,1,7)
,( 2, 8,2,2,8)
,( 2, 9,1,1,9)
,( 2, 10,2,2,10)
,( 3, 1,1,1,1)
,( 3, 2,2,2,2)
,( 3, 3,1,1,3)
,( 3, 4,2,2,4)
,( 3, 5,1,1,5)
,( 3, 6,2,2,6)
,( 3, 7,1,1,7)
,( 3, 8,2,2,8)
,( 3, 9,1,1,9)
,( 3, 10,2,2,10)
,( 4, 1,1,1,1)
,( 4, 2,2,2,2)
,( 4, 3,1,1,3)
,( 4, 4,2,2,4)
,( 4, 5,1,1,5)
,( 4, 6,2,2,6)
,( 4, 7,1,1,7)
,( 4, 8,2,2,8)
,( 4, 9,1,1,9)
,( 4, 10,2,2,10)
,( 5, 1,1,1,1)
,( 5, 2,2,2,2)
,( 5, 3,1,1,3)
,( 5, 4,2,2,4)
,( 5, 5,1,1,5)
,( 5, 6,2,2,6)
,( 5, 7,1,1,7)
,( 5, 8,2,2,8)
,( 5, 9,1,1,9)
,( 5, 10,2,2,10)
,( 6, 1,1,1,1)
,( 6, 2,2,2,2)
,( 6, 3,1,1,3)
,( 6, 4,2,2,4)
,( 6, 5,1,1,5)
,( 6, 6,2,2,6)
,( 6, 7,1,1,7)
,( 6, 8,2,2,8)
,( 6, 9,1,1,9)
,( 6, 10,2,2,10)
------------------------------------------------------------------------------------------------------------------------------
--Step # 2.1: uspAddNewPlayer.
-----------------------------------------------------------------------------------------------------------------------------
GO
CREATE PROCEDURE uspAddNewPlayer
@intPlayerID AS INTEGER OUTPUT
,@strFirstName AS VARCHAR( 50 )
,@strLastName AS VARCHAR( 50 )
,@strPhoneNumber AS VARCHAR( 50 )
AS
SET NOCOUNT ON -- Report only errors
SET XACT_ABORT ON -- Terminate and rollback entire transaction on error
BEGIN TRANSACTION
SELECT @intPlayerID = MAX( intPlayerID ) + 1
FROM TPlayers (TABLOCKX) -- Lock table until end of transaction
-- Default to 1 if table is empty
SELECT @intPlayerID = COALESCE( @intPlayerID , 1 )
INSERT INTO TPlayers( intPlayerID, strFirstName, strLastName,strPhoneNumber )
VALUES( @intPlayerID ,@strFirstName, @strLastName, @strPhoneNumber )
COMMIT TRANSACTION
GO
DECLARE @intPlayerID AS INTEGER = 0;
EXECUTE uspAddNewPlayer @intPlayerID OUTPUT, 'Jerry', 'Bengals', '347-1111'
PRINT 'intPlayerID = ' + CONVERT( VARCHAR, @intPlayerID )
------------------------------------------------------------------------------------------------------------------------------
--Step # 2.2: uspAddTeamWithUniquePlayers.
-----------------------------------------------------------------------------------------------------------------------------
GO
CREATE PROCEDURE uspAddTeamWithUniquePlayers
@intTeamID AS INTEGER OUTPUT
,@strTeam AS VARCHAR( 50 )
,@strTeamMascot AS VARCHAR( 50 )
AS
SET NOCOUNT ON -- Report only errors
SET XACT_ABORT ON -- Terminate and rollback entire transaction on error
BEGIN TRANSACTION
SELECT @intTeamID = MAX( intTeamID ) + 1
FROM TTeams (TABLOCKX) -- Lock table until end of transaction
-- Default to 1 if table is empty
SELECT @intTeamID = COALESCE( @intTeamID , 1 )
INSERT INTO TTeams ( intTeamID, strTeam )
VALUES( @intTeamID ,@strTeam)
COMMIT TRANSACTION
GO
uspAddTeamWithUniquePlayers 1,'Jets','Geno'
GO
uspAddTeamWithUniquePlayers 1,'Jets', 'Vick'
GO
uspAddTeamWithUniquePlayers 1,'Jets', 'Percy'
GO
uspAddTeamWithUniquePlayers 1,'Jets', 'Harvin'
------------------------------------------------------------------------------------------------------------------------------
--Step # 2.2: uspAddTeam.
-----------------------------------------------------------------------------------------------------------------------------
GO
CREATE PROCEDURE uspAddTeam
@intTeamID AS INTEGER OUTPUT
,@strTeam AS VARCHAR( 50 )
,@intLeagueID AS INTEGER
AS
SET NOCOUNT ON -- Report only errors
SET XACT_ABORT ON -- Terminate and rollback entire transaction on error
BEGIN TRANSACTION
SELECT @intTeamID = MAX(intTeamID ) + 1
FROM TTeams (TABLOCKX) -- Lock table until end of transaction
-- Default to 1 if table is empty
SELECT @intTeamID = COALESCE( @intTeamID , 1 )
----Add Team
INSERT INTO TTeams( intTeamID,strTeam)
VALUES( @intTeamID ,@strTeam)
-- Return ID to caller
SELECT @intTeamID AS intTeamID
COMMIT TRANSACTION
GO
DECLARE @intTeamID AS INTEGER = 0;
EXECUTE uspAddTeam @intTeamID OUTPUT, 'Shey', 1
PRINT 'intTeamID = ' + CONVERT( VARCHAR, @intTeamID )
SELECT * FROM TTeams
------------------------------------------------------------------------------------------------------------------------------
--Step # 2.3:2: uspAddLeague
-----------------------------------------------------------------------------------------------------------------------------
GO
CREATE PROCEDURE uspAddLeague
@intLeagueID AS INTEGER OUTPUT
,@strLeague AS VARCHAR( 50 )
AS
SET NOCOUNT ON -- Report only errors
SET XACT_ABORT ON -- Terminate and rollback entire transaction on error
BEGIN TRANSACTION
SELECT @intLeagueID = MAX(intLeagueID ) + 1
FROM TLeagues (TABLOCKX) -- Lock table until end of transaction
-- Default to 1 if table is empty
SELECT @intLeagueID = COALESCE( @intLeagueID , 1 )
INSERT INTO TLeagues( intLeagueID,strLeague)
VALUES( @@VERSION ,@strLeague)
COMMIT TRANSACTION
GO
DECLARE @intLeagueID AS INTEGER = 0;
EXECUTE uspAddLeague @intLeagueID OUTPUT, 'Singles'
PRINT 'intLeagueID = ' + CONVERT( VARCHAR, @intLeagueID )
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply