1 / 7

Worksheet2 Analysis

Worksheet2 Analysis. create table Teams ( TeamID integer primary key, Name char (30) not null, State char (2) not null ); create table Games ( GameID integer primary key, DatePlayed date, WID integer, LID integer, WScore integer, LScore integer,

lesley-dyer
Download Presentation

Worksheet2 Analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Worksheet2 Analysis create table Teams ( TeamID integer primary key, Name char (30) not null, State char (2) not null ); create table Games ( GameID integer primary key, DatePlayed date, WID integer, LID integer, WScore integer, LScore integer, foreign key (WID) references Teams (TeamID), foreign key (WID) references Teams (TeamID) ); create table Players ( PlayerID integer primary key, name varchar (30) not null, state char (2), position varchar (20) ); create table PlaysFor ( PlayerID integer, TeamID integer, foreign key (TeamID) references Teams (TeamID), foreign key (PlayerID) references Players (PlayerID) ); create index index4 on PlaysFor (PlayerID);

  2. -- 1. How many players are the state of TX? select count(*) from Players where Players.state = 'TX'; Execution Plan | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | TABLE ACCESS FULL| PLAYERS | 2 | 8 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ create index index5 on Players(State); | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | INDEX RANGE SCAN| INDEX5 | 2 | 8 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------

  3. -- 2. How many games have resulted in a tie score? select count(*) 2 from Games 3 where WScore = LScore; Execution Plan | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | TABLE ACCESS FULL| GAMES | 1 | 26 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------

  4. -- 3. List the players who play for more than one team. select name from players join playsfor on players.playerid = playsfor.playerid group by players.playerid, players.name having count(*) > 1; Execution Plan | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 9 | 387 | 4 (25)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 9 | 387 | 4 (25)| 00:00:01 | | 3 | NESTED LOOPS | | 9 | 387 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| PLAYERS | 7 | 210 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | INDEX4 | 1 | 13 | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------

  5. -- 4. How many players are on the largest team create view findteamsize as select teamid, count(teamid) as teamsize from Playsfor group by teamid; select teamid, teamsize from findteamsize where teamsize >= (select max(teamsize) from findteamsize); Execution Plan | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 117 | 4 (25)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | HASH GROUP BY | | 9 | 117 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL | PLAYSFOR | 9 | 117 | 3 (0)| 00:00:01 | | 4 | SORT AGGREGATE | | 1 | 13 | | | | 5 | VIEW | FINDTEAMSIZE | 9 | 117 | 4 (25)| 00:00:01 | | 6 | SORT GROUP BY | | 9 | 117 | 4 (25)| 00:00:01 | | 7 | TABLE ACCESS FULL| PLAYSFOR | 9 | 117 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------

  6. -- 5. List the teams that have not played a game create view involved_teams as select wid as teamid from games union select lid as teamid from games; select TeamID, Name from Teams where TeamID not in ( select teamid from involved_teams ); Execution Plan | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 174 | 12 (25)| 00:00:01 | |* 1 | HASH JOIN ANTI NA | | 3 | 174 | 12 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL | TEAMS | 4 | 180 | 3 (0)| 00:00:01 | | 3 | VIEW | INVOLVED_TEAMS | 10 | 130 | 8 (25)| 00:00:01 | | 4 | SORT UNIQUE | | 10 | 130 | 8 (63)| 00:00:01 | | 5 | UNION-ALL | | | | | | | 6 | TABLE ACCESS FULL| GAMES | 5 | 65 | 3 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL| GAMES | 5 | 65 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------

  7. -- 5. alternate solution using Joins, looking for null WID and LID select TeamID, Name from (( Games G1 right outer join Teams on Teams.Teamid = G1.WID ) left outer join Games G2 on Teams.Teamid = G2.LID ) where G1.WID is null and G2.LID is null; Execution Plan | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 284 | 10 (10)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 284 | 10 (10)| 00:00:01 | |* 2 | HASH JOIN ANTI | | 4 | 232 | 7 (15)| 00:00:01 | | 3 | TABLE ACCESS FULL| TEAMS | 4 | 180 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| GAMES | 5 | 65 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | GAMES | 5 | 65 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------

More Related