This is for Oracle SQL Plus. Please answer the following question. Below is the
ID: 3739143 • Letter: T
Question
This is for Oracle SQL Plus. Please answer the following question. Below is the code related to the tables
Write and run the following queries. Do not show duplicates (50 points)
1. Print the names of players who joined after 2005 and played for the Hornets
2. Print the names of players that belong to team that comes from a city that begins with 'Ch' and were drafted in 2007
3. Print the names of the teams that played a home game
4. Print the points and name of the player with the highest score 5. Print the names of the teams that played in all arenas.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Drop the tables if they are already there.
-- OK to get an Oracle error if the table(s) not found (if running script
for the first time)
drop table TEAM_PLAYER_ARENA;
drop table TEAM;
drop table PLAYER;
drop table ARENA;
-- Create the tables, views, constraints, etc..
-- Create the TEAM table
CREATE TABLE TEAM ( Team_ID INTEGER,
Tname
VARCHAR(15),
Rank
INTEGER,
City
VARCHAR(20),
Championships INTEGER,
PRIMARY KEY
(Team_id)
);
-- Create the PLAYER table
CREATE TABLE PLAYER( Player_ID INTEGER,
Pname
VARCHAR(30),
Position
VARCHAR(20),
Draft_year
INTEGER,
Retire_year INTEGER,
Education
VARCHAR(30),
PRIMARY
KEY(Player_ID)
);
-- Create the ARENA table
CREATE TABLE ARENA( ARENA_ID INTEGER,
Aname
VARCHAR(20),
City
VARCHAR(20),
PRIMARY
KEY(ARENA_ID)
);
-- Create the TEAM_PLAYER_ARENA table
CREATE TABLE TEAM_PLAYER_ARENA ( Team_ID INTEGER,
Player_ID INTEGER,
ARENA_ID INTEGER,
Points INTEGER,
PRIMARY KEY(Team_ID, Player_ID, ARENA_ID),
FOREIGN KEY(Team_ID) REFERENCES TEAM,
FOREIGN KEY(Player_ID) REFERENCES PLAYER,
FOREIGN KEY(ARENA_ID) REFERENCES ARENA
);
-- Ensure all data is removed from the tables
truncate table TEAM_PLAYER_ARENA;
delete from TEAM;
delete from PLAYER;
delete from ARENA;
-- INSERT THE DATA WITH NAMED FIELDS (notice that the fields are not in
the order the table was created)
insert into Team (Tname, Rank , City, Championships, Team_ID)
values( 'Clippers' , 6 , 'LA' , 0 , 1 );
insert into Team (Tname, Rank , City, Championships, Team_ID)
values( 'Bulls' , 5 , 'Chicago' , 6, 2 );
insert into Team (Tname, Rank , City, Championships, Team_ID)
values('Hornets' , 9 , 'Charlotte' , 0, 3 );
insert into Team (Tname, Rank , City, Championships,
Team_ID)
values('Blazers' , 2 , 'Portland' , 0, 4 );
insert into Team (Tname, Rank , City, Championships,
Team_ID)
values('Spurs' , 7 , 'San Antonio' , 5, 5 );
insert into Team (Tname, Rank , City, Championships,
Team_ID)
values('Mavericks' , 4 , 'Dallas' , 1, 6 );
-- INSERT THE DATA WITH ORDER OF FIELDS
insert into PLAYER values( 1 , 'Chris Paul' ,
'Small Forward' , 2005, null, 'Wake Forest' );
insert into PLAYER values( 2 , 'tony Parker' ,
'Point guard' , 2001, null, 'INSEP' );
insert into PLAYER values( 3 , 'Marco Belinelli' ,
'Shooting guard', 2007, null, 'San Giovanni' );
insert into PLAYER values( 4 , 'Gary Neal' ,
'Power Forward' , 2007, null, 'La Salle University');
insert into PLAYER values( 5 , 'Kawhi Leonard' , 'Forward'
, 2011, null, 'San Diego State' );
insert into PLAYER values( 6 , 'Patty Mills' ,
'Point guard' , 2009, null, 'Marist College' );
insert into PLAYER values( 7 , 'Tyson Chandler' ,
'Center' , 2001, null, 'Dominguez' );
insert into PLAYER values( 8 , 'Derek Fisher' ,
'Shooting guard', 1996, 2014, 'Arkansas' );
-- -------------------------------------
insert into ARENA values( 1 , 'American Airlines' ,
'Miami' );
insert into ARENA values( 2 , 'Moda Center' ,
'Portland' );
insert into ARENA values( 3 , 'Staples Center' ,
'LA' );
insert into ARENA values( 4 , 'United Center' ,
'Chicago' );
insert into ARENA values( 5 , 'TD Garden' ,
'Boston' );
insert into ARENA values( 6 , 'ATT Center' ,
'San Antonio');
insert into ARENA values( 7 , 'Philips Arena' ,
'Atlanta' );
-- ---------------------------------------------------------------------
insert into TEAM_PLAYER_ARENA values( 1 , 1 , 1 ,
20 );
insert into TEAM_PLAYER_ARENA values( 1 , 1 , 4 ,
17 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 1 ,
32 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 2 ,
22 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 3 ,
13 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 4 ,
25 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 5 ,
06 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 6 ,
14 );
insert into TEAM_PLAYER_ARENA values( 2 , 3 , 7 ,
18 );
insert into TEAM_PLAYER_ARENA values( 2 , 5 , 2 ,
11 );
insert into TEAM_PLAYER_ARENA values( 3 , 3 , 1 ,
20 );
insert into TEAM_PLAYER_ARENA values( 3 , 4 , 2 ,
05 );
insert into TEAM_PLAYER_ARENA values( 4 , 6 , 3 ,
23 );
insert into TEAM_PLAYER_ARENA values( 4 , 6 , 7 ,
18 );
insert into TEAM_PLAYER_ARENA values( 5 , 2 , 2 ,
24 );
insert into TEAM_PLAYER_ARENA values( 5 , 2 , 4 ,
10 );
insert into TEAM_PLAYER_ARENA values( 5 , 5 , 5 ,
25 );
insert into TEAM_PLAYER_ARENA values( 5 , 5 , 7 ,
16 );
insert into TEAM_PLAYER_ARENA values( 5 , 6 , 2 ,
24 );
insert into TEAM_PLAYER_ARENA values( 5 , 1 , 4 ,
33 );
insert into TEAM_PLAYER_ARENA values( 5 , 3 , 4 ,
31 );
insert into TEAM_PLAYER_ARENA values( 5 , 4 , 4 ,
26 );
insert into TEAM_PLAYER_ARENA values( 5 , 5 , 4 ,
14 );
insert into TEAM_PLAYER_ARENA values( 5 , 6 , 4 ,
05 );
-- COMMIT and save data
COMMIT;
Explanation / Answer
Please find my answer:
Answer) Above queries have bee executed from the question:
1. Print the names of players who joined after 2005 and played for the Hornets
select Pname from PLAYER where Draft_year > 2005 and Player_ID in(
select Player_ID from TEAM_PLAYER_ARENA where Team_ID in(
select Team_ID from TEAM where Tname='Hornets'
)
);
Output:
Marco Belinelli
Gary Neal
2. Print the names of players that belong to team that comes from a city that begins with 'Ch' and were drafted in 2007
select Pname from PLAYER where Draft_year = 2007 and Player_ID in(
select Player_ID from TEAM_PLAYER_ARENA where Team_ID in(
select Team_ID from TEAM where City like 'Ch%'
)
);
Output:
Marco Belinelli
Gary Neal
3. Print the names of the teams that played a home game.
select Tname from TEAM where Team_ID in(
select Team_ID from TEAM_PLAYER_ARENA where ARENA_ID in(
select ARENA_ID from ARENA where ARENA.city = TEAM.city
)
);
Output;
Bulls
4. Print the points and name of the player with the highest score
select max(points) as highest_score, pname from team_player_arena
inner join player on player.player_ID = team_player_arena.player_ID;
5. Print the names of the teams that played in all arenas.
select tname from team
inner join team_player_arena on team_player_arena.team_ID = team.team_ID
innner join arena on team_player_arena.arena_ID = arena.arena_ID;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.