making 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'm having trouble making the following queries:

names of the players and their teams, who always played away.

here's my attempt at this:

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;

Average number of goals by round for the players who were born after 1993 and never played at home.

Here's 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 want the id's of the teams with the biggest number of goals against per round , how would i do that ?

HERE'S SOME TESTING 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 this wrong ? Can anybody help ?

making queries in SQL PLUS

2 Answers