Consider the following TABLES Schema in DATABASE: CREATE TABLE Teams ( teamCode

Question

Consider the following TABLES Schema in DATABASE:

CREATE TABLE Teams (
teamCode char(3) PRIMARY KEY,
teamName varchar(50),
city varchar(50)
);
CREATE TABLE Players (
playerID integer PRIMARY KEY,
name varchar(50),
teamCode char(3) REFERENCES Teams(teamCode),
height integer,
weight integer
);
CREATE TABLE Games (
gameID integer PRIMARY KEY,
hometeam char(3) NOT NULL REFERENCES Teams(teamCode),
awayteam char(3) NOT NULL REFERENCES Teams(teamCode),
homescore integer,
awayscore integer
);
CREATE TABLE GameStats (
playerID integer NOT NULL REFERENCES Players(playerID),
gameID integer NOT NULL REFERENCES Games(gameID),
points integer,
PRIMARY KEY (playerID, gameID)
);

Write QUERY to:

Find the games in which both “Shannon Brown” and “DJ Mbenga” played, and DJ Mbenga scored more points than Shannon. Return gameID for those games.

Find the teams which didn’t play any games. Return the team names and cities of those teams.

Find the shortest player(s) from each team. Return the teamCode of each team and the name of the shortest player in that team.

Explanation / Answer

I have written the nested sql queries from the same:

1)

select gameid from Games where( (playerid=select playerid from players where player = "Shannon Brown" as Sh) OR (playerid=select playerid from players where player = "DJ Mbenga" as Dj ) AND (select points from Gamestats where Dj > Sh))

2)

select teamName.cities from Teams where teamcode = ((select count(*) from Games where (hometeam=teamCode or awayteam=teamcode) < 1))

3)

select name,teamcode from players where teamcode = (select teamCode from players Order by Height)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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