Creating queries in SQL PLUS

Question

For this database:

CREATE TABLE team (  
  id    CHAR(3),  
  name     VARCHAR2(80) CONSTRAINT nn_team_name     NOT NULL,  
  district VARCHAR2(20) CONSTRAINT nn_team_district NOT NULL,  

CONSTRAINT pk_team  
PRIMARY KEY (id)  
);  

CREATE TABLE game (  
 home,        
 away,  
 round    NUMBER(2) CONSTRAINT nn_game_round NOT NULL,  
 spectators NUMBER(5),  
--  
 CONSTRAINT pk_game  
  PRIMARY KEY (home, away),  
--  
 CONSTRAINT fk_game_home  
    FOREIGN KEY (home)  
    REFERENCES team(id),  
 CONSTRAINT fk_game_away  
    FOREIGN KEY (away)  
    REFERENCES team(id),  
--
CONSTRAINT ck_game_round  
 CHECK (round BETWEEN 1 AND 30),  
CONSTRAINT ck_game_spectators
 CHECK (spectators > 0)
);

CREATE TABLE player (  
 nickname        NUMBER(8),  
 name       VARCHAR2(80) CONSTRAINT nn_player_name      NOT NULL,  
 yearOfBirth NUMBER(4)    CONSTRAINT nn_player_yearOfBirth NOT NULL,   
 team                  CONSTRAINT nn_player_team    NOT NULL,  
--
CONSTRAINT pk_player  
  PRIMARY KEY (nickname),  
--
CONSTRAINT fk_player_team  
  FOREIGN KEY (team)  
  REFERENCES team(id),  
--
CONSTRAINT ck_player_yearOfBirth  
  CHECK (yearOfBirth BETWEEN 1950 AND 2000)  
);

CREATE TABLE plays (  
  player,  
  home,         
  away,    
  goals      NUMBER(2) CONSTRAINT nn_plays_goals NOT NULL,  
--
CONSTRAINT pk_plays  
  PRIMARY KEY (player, home, away),  
--
CONSTRAINT fk_plays_player  
   FOREIGN KEY (player) REFERENCES player(nickname),  
CONSTRAINT fk_plays_game  
   FOREIGN KEY (home, away) REFERENCES game(home, away),  
--
CONSTRAINT ck_plays_goals   
   CHECK (goals >= 0)  
);

I am having trouble making the following queries:

• names of players and their teams that have always lost.

here is my attempt:

SELECT DISTINCT P1.Name, T.Name
FROM Player P1, Plays P2, Team T, Game G
WHERE P1.team = T.id 
  AND P1.nickname = P2.player
  AND P1.team = G.away
  AND P1.team = P2.away;

• The average number of goals per round for players who were born after 1993 and never played at home.

Here is my attempt:

WITH Number_of_Games AS (
  SELECT COUNT(1) AS Number_of_Games, G.Round
  FROM Game G
  GROUP BY G.Round)

SELECT G.Round, SUM(P.goals / N.Number_of_Games) AS Average_goals_per_round
FROM Player P1,Game G INNER JOIN Plays P2 ON (P1.team = P2.away AND G.away = P2.away AND P1.yearofbirth >= '1993')
INNER JOIN Number_of_Games N ON (G.round = n.round)
GROUP BY g.round
ORDER BY g.round;

• If I need the team ID with the most goals in one round, how would I do it?

HERE ARE SOME TEST DATA:

INSERT INTO TEAM (ID, NAME, DISTRICT) VALUES ('MAN','MANCHESTER UNITED','MANCHESTER');
INSERT INTO TEAM (ID,NAME,DISTRICT) VALUES ('CHE','CHELSEA FC','LONDON');
INSERT INTO PLAYER (NICKNAME,NAME,YEAROFBIRTH,TEAM) VALUES('1','VAN PERSIE','1994','MAN');
INSERT INTO PLAYER (NICKNAME,NAME,YEAROFBIRTH,TEAM) VALUES('2','TERRY','1970','CHE');
INSERT INTO GAME(HOME,AWAY,ROUND,SPECTATORS) VALUES ('MAN','CHE','1','15000');
INSERT INTO GAME(HOME,AWAY,ROUND,SPECTATORS) VALUES ('CHE','MAN','2','30000');
INSERT INTO PLAYS(PLAYER,HOME,AWAY,GOALS) VALUES('1','MAN','CHE','2');
INSERT INTO PLAYS(PLAYER,HOME,AWAY,GOALS) VALUES('2','MAN','CHE','1');
INSERT INTO PLAYS(PLAYER,HOME,AWAY,GOALS) VALUES('1','CHE','MAN','1');

why is it wrong? Can anyone help?

Answer 1

Request 1

names of players and their teams who always lost

This will get the names of players who have never played at home:

SELECT p.name AS player_name,
       t.name AS team_name
FROM   player p
       INNER JOIN
       team t
       ON ( p.team = t.id )
WHERE  NOT EXISTS ( SELECT 1
                    FROM   plays l
                    WHERE  p.nickname = l.player
                    AND    p.team     = l.home )

, , :

SELECT p.name AS player_name,
       t.name AS team_name
FROM   player p
       INNER JOIN
       team t
       ON ( p.team = t.id )
WHERE  NOT EXISTS ( SELECT 1
                    FROM   plays l
                    WHERE  p.nickname = l.player
                    AND    p.team     = l.home )
AND    EXISTS     ( SELECT 1
                    FROM   plays l
                    WHERE  p.nickname = l.player
                    AND    p.team     = l.away -- Although you probably don't need this last line.
                  )

2

, 1993 .

, , , 1993 :

SELECT AVG( y.goals ),
       g.round
FROM   player p
       INNER JOIN
       plays y
       ON ( p.nickname = y.player AND p.team = y.home )
       INNER JOIN
       game g
       ON ( y.home = g.home AND y.away = g.away )
WHERE  NOT EXISTS ( SELECT 1
                    FROM   plays l
                    WHERE  p.nickname = l.player
                    AND    p.team     = l.home )
AND    p.yearOfBirth > 1993
GROUP BY g.round
ORDER BY g.round;

3

, ?

, :

WITH team_goals AS (
  SELECT SUM( l.goals ) AS total_goals,
         p.team,
         CASE WHEN p.team = l.home THEN l.away
              WHEN p.team = l.away THEN l.home
              ELSE NULL
              END AS opposing_team
  FROM   player p
         INNER JOIN
         plays l
         ON (p.nickname = l.player)
  GROUP BY
         p.team,
         CASE WHEN p.team = l.home THEN l.away
              WHEN p.team = l.away THEN l.home
              ELSE NULL
              END
),
goals_against AS (
  SELECT p.team,
         SUM( q.total_goals ) AS total_goals_against
  FROM   team_goals p
         INNER JOIN
         team_goals q
         ON (    p.team = q.opposing_team
             AND p.opposing_team = q.team)
  GROUP BY p.team
)
SELECT *
FROM   goals_against
WHERE  total_goals_against = ( SELECT MAX( total_goals_against )
                               FROM goals_against );

Answer 2

:

SELECT DISTINCT P1.Name, T.Name
FROM Player P1 JOIN team T ON p1.team = t.team
WHERE NOT EXISTS
      (SELECT 1
         FROM plays
        WHERE player = p1.name
          AND home = p1.team);

WHERE. ANSI 92 .

, , 1993 :

SELECT P1.Name, T.Name
FROM Player P1 JOIN team T ON p1.team = t.team
WHERE NOT EXISTS
      (SELECT 1
         FROM plays
        WHERE player = p1.name
          AND home = p1.team
          AND year_of_birth <= 1993);

, :

  SELECT P1.Name, T.Name, g.round, AVG(p2.goals) "Average Goals"
    FROM Player P1 JOIN team T ON p1.team = t.team
                   JOIN plays p2 ON p1.nickname = p2.player
                   JOIN game g ON p2.away = g.away
   WHERE NOT EXISTS
         (SELECT 1
            FROM plays
           WHERE player = p1.name
             AND home = p1.team
             AND year_of_birth <= 1993))
  GROUP BY p1.name, t.name, g.round;

, TYPE .

. .