PART I
=============================================================
Q1. SELECT name
    FROM player
    WHERE position='Guard' AND name LIKE '%Jo%'
    ORDER BY name

Q2. SELECT city
    FROM team
    GROUP BY city
    HAVING COUNT(*)>1
    ORDER BY city

Q3. SELECT p.playerID, p.name, p.team, st.total as total_score
    FROM player p,
         (SELECT playerid, SUM(points) AS total
          FROM gamestats
          GROUP BY playerid) st
    WHERE st.playerid=p.playerid AND 
          st.total=(SELECT MAX(c.total)
                    FROM (SELECT playerid, SUM(points) AS total
                          FROM gamestats
                          GROUP BY playerid) c)
    ORDER BY p.playerid

Q4. SELECT s.playerid, AVG(s.points) AS average_points
    FROM (SELECT st.playerid, st.points
          FROM player p, game g, gamestats st
          WHERE st.gameid=g.gameid AND p.team=g.hometeam AND 
                p.playerid=st.playerid) s
    GROUP BY s.playerid
    ORDER BY s.playerid

Q5. SELECT team, position, AVG(height) AS avg_height, AVG(weight) AS avg_weight
    FROM player
    GROUP BY team, position
    ORDER BY team, position DESC

Q6. SELECT p.playerid, p.name, p.team, MAX(u.td) AS num_of_triple_doubles
    FROM 
     ((SELECT playerid, 0 AS td FROM player)
      UNION 
      SELECT playerid, COUNT(*) 
      FROM gamestats 
      WHERE points>9 AND assists>9 and rebounds>9
      GROUP BY playerid) u, player p
    WHERE p.playerid=u.playerid
    GROUP BY p.playerid, p.name, p.team
    ORDER BY p.playerid

Q7. SELECT COUNT(*) AS num_of_games
    FROM player p1, player p2, gamestats g1, gamestats g2
    WHERE p1.name='Hairy Harry' AND p2.name='Joey Loo' AND g1.playerid=p1.playerid AND 
          g2.playerid=p2.playerid AND g1.gameid=g2.gameid AND g1.points<g2.points

Q8. SELECT p.playerid, p.name, g.games as num_of_games
    FROM player p, (SELECT playerid, COUNT(*) AS games
                    FROM gamestats
                    GROUP BY playerid
                    HAVING COUNT(*)>1) g
    WHERE p.playerid=g.playerid
    ORDER BY p.playerid

Q9. SELECT w.winner as name, COUNT(*) AS num_of_wins
    FROM
      ((SELECT hometeam AS winner FROM game WHERE homescore>awayscore)
       UNION ALL
       (SELECT awayteam AS winner FROM game WHERE homescore<awayscore)) w
    GROUP BY w.winner
    HAVING COUNT(*)=(SELECT MAX(s.games_won)
                     FROM (SELECT w.*, COUNT(*) AS games_won
                           FROM
                             ((SELECT hometeam AS winner 
                               FROM game 
                               WHERE homescore>awayscore)
                               UNION ALL
                              (SELECT awayteam AS winner 
                               FROM game 
                               WHERE homescore<awayscore)) w
                           GROUP BY w.winner) s)

Q10. SELECT p.playerid,p.name,p.team
     FROM player p
     WHERE EXISTS (SELECT g.gameid FROM game g WHERE g.hometeam=p.team) AND
           NOT EXISTS ((SELECT g.gameid
                        FROM game g
                        WHERE g.hometeam=p.team)
                        EXCEPT
                       (SELECT st.gameid
                        FROM gamestats st
                        WHERE st.playerid=p.playerid))
     ORDER BY p.playerid

PART II
=================================================================

-----------------------start of explain1.txt-----------------------------

select * from player p1 where p1.height <= all (select height from player);

NOTICE:  QUERY PLAN:

Seq Scan on player p1  (cost=0.00..1484.87 rows=174 width=55)
  SubPlan
    ->  Seq Scan on player  (cost=0.00..8.48 rows=348 width=4)

EXPLAIN

select * from player p1 where p1.height in (select min(height) from player);

NOTICE:  QUERY PLAN:

Seq Scan on player p1  (cost=0.00..3263.15 rows=174 width=55)
  SubPlan
    ->  Materialize  (cost=9.35..9.35 rows=1 width=4)
          ->  Aggregate  (cost=9.35..9.35 rows=1 width=4)
                ->  Seq Scan on player  (cost=0.00..8.48 rows=348 width=4)

EXPLAIN

select * from player p1 where not exists (select * from player p2 where p2.height < p1.height);

NOTICE:  QUERY PLAN:

Seq Scan on player p1  (cost=0.00..36.53 rows=174 width=55)
  SubPlan
    ->  Seq Scan on player p2  (cost=0.00..9.35 rows=116 width=55)

EXPLAIN

(select * from player)
EXCEPT
(select p1.*  from player p1, player p2
where p1.height>p2.height AND p1.playerid<>p2.playerid);

NOTICE:  QUERY PLAN:

SetOp Except  (cost=8849.78..9458.78 rows=4060 width=63)
  ->  Sort  (cost=8849.78..8849.78 rows=40600 width=63)
        ->  Append  (cost=0.00..4784.56 rows=40600 width=63)
              ->  Subquery Scan *SELECT* 1  (cost=0.00..8.48 rows=348 width=55)
                    ->  Seq Scan on player  (cost=0.00..8.48 rows=348 width=55)
              ->  Subquery Scan *SELECT* 2  (cost=0.00..4776.08 rows=40252 width=63)
                    ->  Nested Loop  (cost=0.00..4776.08 rows=40252 width=63)
                          ->  Seq Scan on player p1  (cost=0.00..8.48 rows=348 width=55)
                          ->  Seq Scan on player p2  (cost=0.00..8.48 rows=348 width=8)

EXPLAIN

select * from player p1 where not exists (select * from player p2 where p2.height < p1.height);

Explanation:
The "not exists" returns false when there is at least one tuple in the result of the subquery. 
So, although the subquery has to be evaluated everytime because it is not independent from the 
outer query, the evaluation of the subquery can stop whenever a tuple is found that satisfies 
the condition of the subquery, since the "not exists" is going to return false anyway.

Note: We wanted you to analyze the output of the 'explain' command, not the 'explain analyze'
command. The point was to have you analyze the plan generated by the system and the cost  
(in terms of CPU cost and I/Os).

---------------start of explain2.txt----------------------------------------

select gameId, homeScore + awayScore as totalScore from game;

NOTICE:  QUERY PLAN:

Seq Scan on game  (cost=0.00..3.00 rows=100 width=12)

EXPLAIN

select gameId, sum(points) as totalScore from gameStats group by gameid;

NOTICE:  QUERY PLAN:

Aggregate  (cost=158.54..169.43 rows=218 width=8)
  ->  Group  (cost=158.54..163.98 rows=2178 width=8)
        ->  Sort  (cost=158.54..158.54 rows=2178 width=8)
              ->  Seq Scan on gamestats  (cost=0.00..37.78 rows=2178 width=8)

EXPLAIN



select gameId, homeScore + awayScore as totalScore from game; (1 point)

Explanation:
The query just requires one scan over a table and there are no aggregates or grouping required 
as in the second query. Moreover table game is much smaller than table gamestats.