SQL problem

h66umi6q

03.docx

Home >Computer Science homework help >SQL problem

1. For today’s lab, you will be scripting a database that will store information about users and teams.

2. Download the 3 text files from Canvas and place them in your c:\temp folder

3. Write a script that will do the following:

a. Drops the database called TeamsAndUsers and recreates it.

b. Creates the 3 tables that match the text files

c. Import the data into these tables from the files on c:\temp (do not import from anywhere else)

d. Create a view called “vwTeamsAndUsers” that brings all three tables together. The view should return all fields from each table EXCEPT the teamId and userId from the teamUsers table. These are not needed. Also, show all records – meaning, don’t exclude teams that are not active.

e. Create the following storedProcedures

i. spGetTeamUsersByTeamId

· universityTeamName

· fullName (first and last)

· userId

ii. spGetUserTeamsByUserId

· fullName (first and last)

· universityTeamName

· teamId

iii. spGetTeamsWithNoUsers

· teamId

· universityTeamName

· ownerUserId

· ownerFullName (first and last)

iv. spGetUsersNotOnATeam

· userId

· fullName (first and last)

v. spNeverHonoredUsers

· userId

· fullName (first and last)

vi. spGetTeamOwnersByTeamCount (returns a list of owners and team names with >= @teamCount)

· Only active teams should be counted

· userId

· fullName (first and last)

vii. spGetTeamsWithMoreThanAssistantCoaches (returns a list of teams with >= @assistantCount)

· Only active teams should be counted

· teamId

· userId

· fullName (first and last)

viii. spGetTeamsWithNoAssistantCoaches

· teamId

· universityTeamName

ix. spGetHigestHonoredUsers (returns all users that have been honored on ALL teams they are on)

· userId

· fullName (first and last)

x. spGetUsersThatAreOnlyAssistantCoaches (a list of assistant coaches that are ONLY assistant coaches)

· userId

· fullName (first and last)

· universityTeamName