The SQL Tasks In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question. Subqueries and nesting are allowed within a single SQL statement - however, you may be penalized for writing overly complicated SQL statements. DO NOT USE VIEWS (or 'WITH' statements/CTEs) to answer questions. 1. List the first name and last name of all players who have ever played in the league and their current club if applicable. List the players in ascending alphabetical order, primarily on last name and secondarily on first name. A game is still considered to have been 'played' even if it was cancelled or a walkover. (1 mark) 2. List the full name (presented as a single string) of female players who have played for more than one team. (1 mark) 3. List the full name (presented as a single string) of players who have never played in a 'mixed' competition. (1 mark) The Data Model game GameID INT Round INT competition CompetitionID INT Competition Name VARCHAR(35)" Competition Type VARCHAR(10) played in - - - - consists of --------IC - - - season SeasonID INT H SeasonYear YEAR CompetitionID INT SeasonID INT Team1 INT Team2 INT T1Score INT T2Score INT MatchDate DATE MatchTime TIME plays in I playerteam plays in PlayerID INT player PlayerID INT FirstName VARCHAR(45) LastName VARCHAR(45) Sex ENUM('M', 'F') TeamID INT played by GameID INT plays in team # TeamID INT plays for --- TeamName VARCHAR(45) TeamType VARCHAR(25) ClubID INT clubplayer ClubID INT hosts plays for club ClubID INT ClubName VARCHAR(45) Address1 VARCHAR(45) Address2 VARCHAR(45) Suburb VARCHAR(45) PlayerID INT # - - - - FromDate DATE ToDate DATE Postcode CHAR(4) The SQL Tasks In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question. Subqueries and nesting are allowed within a single SQL statement - however, you may be penalized for writing overly complicated SQL statements. DO NOT USE VIEWS (or 'WITH' statements/CTEs) to answer questions. 1. List the first name and last name of all players who have ever played in the league and their current club if applicable. List the players in ascending alphabetical order, primarily on last name and secondarily on first name. A game is still considered to have been 'played' even if it was cancelled or a walkover. (1 mark) 2. List the full name (presented as a single string) of female players who have played for more than one team. (1 mark) 3. List the full name (presented as a single string) of players who have never played in a 'mixed' competition. (1 mark) The Data Model game GameID INT Round INT competition CompetitionID INT Competition Name VARCHAR(35)" Competition Type VARCHAR(10) played in - - - - consists of --------IC - - - season SeasonID INT H SeasonYear YEAR CompetitionID INT SeasonID INT Team1 INT Team2 INT T1Score INT T2Score INT MatchDate DATE MatchTime TIME plays in I playerteam plays in PlayerID INT player PlayerID INT FirstName VARCHAR(45) LastName VARCHAR(45) Sex ENUM('M', 'F') TeamID INT played by GameID INT plays in team # TeamID INT plays for --- TeamName VARCHAR(45) TeamType VARCHAR(25) ClubID INT clubplayer ClubID INT hosts plays for club ClubID INT ClubName VARCHAR(45) Address1 VARCHAR(45) Address2 VARCHAR(45) Suburb VARCHAR(45) PlayerID INT # - - - - FromDate DATE ToDate DATE Postcode CHAR(4)
