Difference between pages "More JOIN operations" and "SELECT .. GROUP BY"

From SQLZOO
(Difference between pages)
Jump to: navigation, search
 
(fixed name in table - from 'asia' to 'Asia')
 
Line 1: Line 1:
<h1>Movie Database</h1>
+
 
<p>This tutorial introduces the notion of a join. The database
+
<h1>SELECT .. GROUP BY</h1>
  consists of three tables
+
Host cities and continents for the Olympics Games are stored in the table <code>games</code>.
  <code>movie</code>
+
Notice that Europe appears in the table twice:
  ,
+
<table>
  <code>actor</code>
+
<caption align='center'>'''games'''</caption>
  and
+
<tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th><th align='center'>'''continent'''</th></tr>
  <code>casting</code>
+
<tr><td>2000</td><td align='left'>Sydney</td><td align='left'>Australasia</td></tr>
  .
+
<tr><td>2004</td><td align='left'>Athens</td><td align='left'>Europe</td></tr>
</p>
+
<tr><td>2008</td><td align='left'>Beijing</td><td align='left'>Asia</td></tr>
<div class='sc'></div>
+
<tr><td>2019</td><td align='left'>London</td><td align='left'>Europe</td></tr>
<div class = 'ref_section'>
+
<tr><td>2016</td><td align='left'>india</td><td align='left'>Asia</td></tr>
<table class = 'db_ref'>
+
<tr><td>2018</td><td align='left'>india</td><td align='left'>Asia</td></tr>
<tr><th>'''movie'''</th><th>'''actor'''</th><th>'''casting'''</th></tr>
+
<tr><td>2014</td><td align='left'>england</td><td align='left'>Europe</td></tr>
<tr><td>id</td><td>id</td><td>movieid</td></tr>
+
<tr><td>title</td><td>name</td><td>actorid</td></tr>
+
<tr><td>yr</td><td></td><td>ord</td></tr>
+
<tr><td>director</td><td></td><td></td></tr>
+
<tr><td>budget</td><td></td><td></td></tr>
+
<tr><td>gross</td><td></td><td></td></tr>
+
<tr><td></td><td></td><td></td></tr>
+
 
</table>
 
</table>
</div>
+
  <p>
+
<div class='ht'>
    [[More details about the database.]]
+
<div class='params'>schema:scott</div>
  </p>
+
<source lang=sql class='tidy'> DROP TABLE games</source>
 
+
<source lang=sql class='setup'> CREATE TABLE games(
 
+
  yr INTEGER,
<div class="progress_panel"><div>
+
  city VARCHAR(20),
  <div class="summary">Summary</div>
+
  continent VARCHAR(20));
  <div class="progressbarbg">
+
INSERT INTO games VALUES (2000,'Sydney','Australasia');
    <div class="progressbar"></div>
+
INSERT INTO games VALUES (2004,'Athens','Europe');
  </div>
+
INSERT INTO games VALUES (2008,'Beijing','Asia');
</div></div>
+
INSERT INTO games VALUES (2012,'London','Europe');
 
+
<h2>Let's go to work.</h2>
+
<p>Limbering up</p>
+
<div class='qu'>
+
List the films where the '''yr''' is 1962 [Show '''id''', '''title'''] 
+
<source lang='sql' class='def'>
+
SELECT id, title
+
FROM movie
+
WHERE yr=1962
+
 
</source>
 
</source>
 
+
In a GROUP BY statement only <i>distinct</i> values are shown for the column in the GROUP BY.
<source lang='sql' class='ans'>
+
This example shows the continents hosting the Olympics with the count of the number of games held.
SELECT id, title
+
<source lang='sql' class='def e-oracle'>
FROM movie
+
SELECT continent, COUNT(yr) FROM scott.games
  WHERE yr=1962
+
  GROUP BY continent
 
</source>
 
</source>
</div>
 
 
<div class='qu'>
 
Give year of 'Citizen Kane'.   
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
</source>
+
SELECT continent, COUNT(yr) FROM games
 
+
GROUP BY continent
<source lang='sql' class='ans'>
+
SELECT yr  
+
FROM movie
+
WHERE title='Citizen Kane'
+
 
</source>
 
</source>
 
</div>
 
</div>
  
<div class='qu'>
+
<p>See also</p>
List all of the Star Trek movies, include the '''id''', '''title''' and '''yr''' (all of these movies include the words Star Trek in the title). Order results by year.
+
<ul>
<source lang='sql' class='def'>
+
   <li>[[SUM_and_COUNT |SUM and COUNT]]</li>
</source>
+
   <li>[[SELECT_.._WHERE |SELECT WHERE]]</li>
 
+
</ul>
<source lang='sql' class='ans'>
+
SELECT id,title, yr FROM movie
+
WHERE title LIKE 'Star Trek%'
+
ORDER BY yr
+
</source>
+
</div>
+
 
+
<h2>Looking at the '''id''' field.</h2>
+
<div class='qu'>
+
What are the titles of the films with '''id''' 11768, 11955, 21191    
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT title
+
FROM movie
+
WHERE id IN (11768, 11955, 21191)
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
What '''id''' number does the actor 'Glenn Close' have? 
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT id FROM actor
+
  WHERE name= 'Glenn Close'
+
</source>
+
</div>
+
 
+
 
+
<div class='qu'>
+
What is the '''id''' of the film 'Casablanca'   
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT id
+
FROM movie
+
WHERE title='Casablanca'
+
</source>
+
</div>
+
 
+
<h2>[[Get to the point]]</h2>
+
<div class='qu'>
+
Obtain the cast list for 'Casablanca'.
+
Use the '''id''' value that you obtained in the previous question. 
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name
+
   FROM casting, actor
+
  WHERE movieid=(SELECT id
+
            FROM movie
+
            WHERE title='Casablanca')
+
    AND actorid=actor.id
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
Obtain the cast list for the film 'Alien' 
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name
+
  FROM movie, casting, actor
+
  WHERE title='Alien'
+
    AND movieid=movie.id
+
    AND actorid=actor.id
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
List the films in which 'Harrison Ford' has appeared   
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT title
+
  FROM movie, casting, actor
+
WHERE name='Harrison Ford'
+
    AND movieid=movie.id
+
    AND actorid=actor.id
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
List the films where 'Harrison Ford' has appeared - but not in the star role.
+
[Note: the '''ord''' field of casting gives the position of the actor.  
+
If ord=1 then this actor is in the starring role]   
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT title
+
  FROM movie, casting, actor
+
WHERE name='Harrison Ford'
+
    AND movieid=movie.id
+
    AND actorid=actor.id
+
  AND ord<>1
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
List the films together with the leading star for all 1962 films.     
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT title, name
+
  FROM movie, casting, actor
+
WHERE yr=1962
+
    AND movieid=movie.id
+
    AND actorid=actor.id
+
    AND ord=1
+
</source>
+
</div>
+
 
+
<h2>Harder Questions</h2>
+
<div class='qu'>
+
Which were the busiest years for 'John Travolta', show the year and the number of movies he made each year for any year in which he made at least 2 movies.     
+
<source lang='sql' class='def'>
+
SELECT yr,COUNT(title) FROM
+
  movie JOIN casting ON movie.id=movieid
+
        JOIN actor  ON actorid=actor.id
+
where name='John Travolta'
+
GROUP BY yr
+
HAVING COUNT(title)=(SELECT MAX(c) FROM
+
(SELECT yr,COUNT(title) AS c FROM
+
  movie JOIN casting ON movie.id=movieid
+
        JOIN actor  ON actorid=actor.id
+
where name='John Travolta'
+
GROUP BY yr) AS t
+
)
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT yr,COUNT(title) FROM
+
  movie JOIN casting ON movie.id=movieid
+
        JOIN actor  ON actorid=actor.id
+
where name='John Travolta'
+
GROUP BY yr
+
HAVING COUNT(title)=(SELECT MAX(c) FROM
+
(SELECT yr,COUNT(title) AS c FROM
+
movie JOIN casting ON movie.id=movieid
+
JOIN actor  ON actorid=actor.id
+
where name='John Travolta'
+
GROUP BY yr) AS t
+
)
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
List the film title and the leading actor for all of the films 'Julie Andrews' played in. 
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT title, name
+
  FROM movie, casting, actor
+
  WHERE movieid=movie.id
+
    AND actorid=actor.id
+
    AND ord=1
+
    AND movieid IN
+
    (SELECT movieid FROM casting, actor
+
    WHERE actorid=actor.id
+
    AND name='Julie Andrews')
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
Obtain a list in alphabetical order of actors who've had at least 30 starring roles.       
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name
+
    FROM casting JOIN actor
+
      ON  actorid = actor.id
+
    WHERE ord=1
+
    GROUP BY name
+
    HAVING COUNT(movieid)>=30
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
List the 1978 films by order of cast list size.       
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
  SELECT title, COUNT(actorid)
+
  FROM casting,movie               
+
  WHERE yr=1978
+
        AND movieid=movie.id
+
  GROUP BY title
+
  ORDER BY 2 DESC
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
List all the people who have worked with 'Art Garfunkel'.       
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT DISTINCT d.name
+
FROM actor d JOIN casting a ON (a.actorid=d.id)
+
  JOIN casting b on (a.movieid=b.movieid)
+
  JOIN actor c on (b.actorid=c.id
+
                and c.name='Art Garfunkel')
+
  WHERE d.id!=c.id
+
</source>
+
</div>
+
<div>
+
<div class="lsclear">Clear your results</div>
+
<p><div class="quizlink">[[JOIN Quiz 2]]</div></p>
+
</div>
+
[http://sqlzoo.net/w/index.php/Using_Null That is definitely enough. Students should, under no circumstances look at the next tutorial, concerning outer joins.]
+

Revision as of 11:51, 7 May 2013

SELECT .. GROUP BY

Host cities and continents for the Olympics Games are stored in the table games. Notice that Europe appears in the table twice:

games
yrcitycontinent
2000SydneyAustralasia
2004AthensEurope
2008BeijingAsia
2019LondonEurope
2016indiaAsia
2018indiaAsia
2014englandEurope
schema:scott
 DROP TABLE games
 CREATE TABLE games(
  yr INTEGER,
  city VARCHAR(20),
  continent VARCHAR(20));
INSERT INTO games VALUES (2000,'Sydney','Australasia');
INSERT INTO games VALUES (2004,'Athens','Europe');
INSERT INTO games VALUES (2008,'Beijing','Asia');
INSERT INTO games VALUES (2012,'London','Europe');

In a GROUP BY statement only distinct values are shown for the column in the GROUP BY. This example shows the continents hosting the Olympics with the count of the number of games held.

SELECT continent, COUNT(yr) FROM scott.games
 GROUP BY continent
SELECT continent, COUNT(yr) FROM games
 GROUP BY continent

See also

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense