Problem 1 1)Write the SQL for the following tables: Tables:TTeams(intTeamID,strT

Question

Problem 1

1)Write the SQL for the following tables:

Tables:TTeams(intTeamID,strTeam,strMascot)

TTeamPlayers(intTeamID, intPlayerID)

TPlayers(intPlayerID,strFirstName,strLastName)

2)Identify and create the foreign keys.

3)Add sample data (at least two inserts per table).

4)Write the query that will show the ID and name for every team along with a count of how many players are on each team. Order by the team name (you almost never order by IDs). Be sure to use aliases to name all of your aggregate/computed columns. Aliases follow all the normal coding standards (e.g. propercase, prefixes, no abbreviations, descriptive names, watch Star Trek).

5)Write the query that will show all the players ON a specific team (e.g. 1, Curling). You pick the team. Order by last name and first name.

6)Write the query that will show all the players NOT ON a specific team (e.g. 1, Curling). Make sure there’s at least one player not on any team including the one you use for this step. Order by last name and first name.

Problem 2

1)Write the SQL for the following tables:

2)Identify and create the foreign keys.

3)Add sample data (at least two inserts per table).

4)Write the query that will show the ID and name for every user along with a count of how many favorite songs each user has. Sort by user name.

5)Write the query that will show all the users (ID and name ALWAYS) that have at least three favorite songs by <your favorite band>. Make sure you have at least one user that has three or more favorite songs by your favorite band AND another user that has three or more favorite songs by a different band.

EE TTeams ETT FE TPlayers eamPlavers intTeamlD strTeam(50 strMascot(50) intPlayerlD strFirstName(50) strLastName(50)

Explanation / Answer

Problem 1:

2)

DDL SQL:

CREATE TABLE TTeams (

TeamID int NOT NULL,

Team varchar(50),

Mascot varchar (50),

PRIMARY KEY (TeamID));

CREATE TABLE TPlayers (

PlayerID int NOT NULL,

FirstName varchar (50),

LastName varchar (50),

PRIMARY KEY (PlayerID));

CREATE TABLE TTeamPlayers(

TeamID int NOT NULL,

PlayerID int NOT NULL,

PRIMARY KEY (TeamID),

PRIMARY KEY (PlayerID),

FOREIGN KEY (TeamID) REFERENCES TTeams (TeamID),

FOREIGN KEY (PlayerID) REFERENCES TPlayers (PlayerID));

3)

DML SQL:

Insert into TTeams values (1, ‘India’, ‘Peacock’);

Insert into TTeams values (2, ‘Australia’, ‘Kangaroo’);

Insert into TPlayers values (100, ‘Sachin’, ‘Tendulkar’);

Insert into TPlayers values (200, ‘Ricky’, ‘Ponting’);

Insert into TTeamPlayers values (1,100);

Insert into TTeamPlayers values (2,200);

4) select TTeam.TeamID As TEAM ID, Team AS TEAM NAME, count (PlayerID) AS COUNT OF PLAYERS from TTeamPlayers and TTeams where TTeamPlayers.TeamID= TTeams.TeamID order by Team ;

5) Select TeamID, FirstName from TPlayers and TTeamPlayers where TPlayers.PlayerID= TTeamPlayers.PlayerID order by FirstName, LastName

6) select TeamID, FirstName from TPlayers and TTeamPlayers where NOT EXISTS (Select TeamID, FirstName from TPlayers and TTeamPlayers where TPlayers.PlayerID = TTeamPlayers.PlayerID) where TPlayers.PlayerID = TTeamPlayers.PlayerID order by FirstName, LastName

Problem 2:

CREATE TABLE TUsers (

UserID int NOT NULL,

FirstName varchar(50),

LastName varchar (50),

PRIMARY KEY (UserID));

CREATE TABLE TUsersFavouriteSongs (

UserID int NOT NULL,

SongID int NOT NULL,

SortOrder int,

PRIMARY KEY (UserID),

PRIMARY KEY (SongID),

FOREIGN KEY (UserID) REFERENCES TTeams (SongID),

);

CREATE TABLE TSongs (

SongID int NOT NULL,

Song varchar(50),

Artist varchar (50),

PRIMARY KEY (SongID));

Insert into TUsers values (1, ‘John’, ‘Brown’);

Insert into TUsers values (2, ‘Beck’, ‘Logan’);

Insert into TSongs values (100, ‘Mary had a little lamp’, ‘Roman grey’);

Insert into TSongs values (200, ‘long ago’, ‘Liza may’);

Insert into TUserFavouriteSongs values (1, 100, 10);

Insert into TUserFavouriteSongs values (2, 200, 20);

4) select UserID, SongID,count (SongID) from TUserFavouriteSongs, Tusers where TUserFavouriteSongs.UserID= TUsers.UserID order by FirstName

5) select UserId, FirstName from TUsers, TUserFavouriteSongs where count (SongID) > 3

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.