Difference between pages "SELECT .. GROUP BY" and "ALTER"

From SQLZOO
(Difference between pages)
Jump to: navigation, search
(fixed name in table - from 'asia' to 'Asia')
 
 
Line 1: Line 1:
 
+
[[File:C:\Users\mn33894\Desktop\Subbu.jpeg]]
<h1>SELECT .. GROUP BY</h1>
+
<h1>ALTER TABLE</h1>
Host cities and continents for the Olympics Games are stored in the table <code>games</code>.
+
<p>The table <code>games</code> shows the year and the city hosting the Olympic Games.</p>
Notice that Europe appears in the table twice:
+
 
<table>
 
<table>
<caption align='center'>'''games'''</caption>
+
<caption align='center'>games</caption>
<tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th><th align='center'>'''continent'''</th></tr>
+
<tr><th align='center'>'''yr'''</th><th align='center'>'''city'''</th></tr>
<tr><td>2000</td><td align='left'>Sydney</td><td align='left'>Australasia</td></tr>
+
<tr><td>2004</td><td align='left'>Athens</td></tr>
<tr><td>2004</td><td align='left'>Athens</td><td align='left'>Europe</td></tr>
+
<tr><td>2006</td><td align='left'>Turin</td></tr>
<tr><td>2008</td><td align='left'>Beijing</td><td align='left'>Asia</td></tr>
+
 
<tr><td>2019</td><td align='left'>London</td><td align='left'>Europe</td></tr>
+
<tr><td>2016</td><td align='left'>india</td><td align='left'>Asia</td></tr>
+
<tr><td>2018</td><td align='left'>india</td><td align='left'>Asia</td></tr>
+
<tr><td>2014</td><td align='left'>england</td><td align='left'>Europe</td></tr>
+
 
</table>
 
</table>
 
 
<div class='ht'>
 
<div class='ht'>
<div class='params'>schema:scott</div>
+
<div class=params>schema:scott</div>
 
<source lang=sql class='tidy'> DROP TABLE games</source>
 
<source lang=sql class='tidy'> DROP TABLE games</source>
 
<source lang=sql class='setup'> CREATE TABLE games(
 
<source lang=sql class='setup'> CREATE TABLE games(
   yr INTEGER,
+
   yr INTEGER PRIMARY KEY,
   city VARCHAR(20),
+
   city VARCHAR(20));
  continent VARCHAR(20));
+
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2000,'Sydney','Australasia');
+
INSERT INTO games VALUES (2006,'Turin');
INSERT INTO games VALUES (2004,'Athens','Europe');
+
INSERT INTO games VALUES (2008,'Beijing','Asia');
+
INSERT INTO games VALUES (2012,'London','Europe');
+
 
</source>
 
</source>
In a GROUP BY statement only <i>distinct</i> values are shown for the column in the GROUP BY.
+
The ALTER statement can be used to add a column.
This example shows the continents hosting the Olympics with the count of the number of games held.  
+
In this example the column season indicates if the games are winter or summer games.  
 
<source lang='sql' class='def e-oracle'>
 
<source lang='sql' class='def e-oracle'>
SELECT continent, COUNT(yr) FROM scott.games
+
SELECT * FROM scott.games;
GROUP BY continent
+
ALTER TABLE games ADD season VARCHAR(6);
</source>
+
UPDATE games SET season = 'summer'
 +
  WHERE yr = 2004;
 +
UPDATE games SET season = 'winter'
 +
  WHERE yr = 2006;
 +
SELECT * FROM scott.games;
 +
</source>  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT continent, COUNT(yr) FROM games
+
SELECT * FROM games;
GROUP BY continent
+
ALTER TABLE games ADD season VARCHAR(6);
 +
UPDATE games SET season = 'summer'
 +
  WHERE yr = 2004;
 +
UPDATE games SET season = 'winter'
 +
  WHERE yr = 2006;
 +
SELECT * FROM games;
 
</source>
 
</source>
 
</div>
 
</div>
Line 41: Line 42:
 
<p>See also</p>
 
<p>See also</p>
 
<ul>
 
<ul>
   <li>[[SUM_and_COUNT |SUM and COUNT]]</li>
+
   <li>[[INSERT_.._VALUES |INSERT]]</li>
   <li>[[SELECT_.._WHERE |SELECT WHERE]]</li>
+
   <li>[[DROP |DROP]]</li>
 
</ul>
 
</ul>
 +
 +
{{Languages}}

Revision as of 07:39, 9 May 2013

File:C:\Users\mn33894\Desktop\Subbu.jpeg

ALTER TABLE

The table games shows the year and the city hosting the Olympic Games.

games
yrcity
2004Athens
2006Turin
schema:scott
 DROP TABLE games
 CREATE TABLE games(
  yr INTEGER PRIMARY KEY,
  city VARCHAR(20));
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2006,'Turin');

The ALTER statement can be used to add a column. In this example the column season indicates if the games are winter or summer games.

SELECT * FROM scott.games;
ALTER TABLE games ADD season VARCHAR(6);
UPDATE games SET season = 'summer'
  WHERE yr = 2004;
UPDATE games SET season = 'winter'
  WHERE yr = 2006;
SELECT * FROM scott.games;
SELECT * FROM games;
ALTER TABLE games ADD season VARCHAR(6);
UPDATE games SET season = 'summer'
  WHERE yr = 2004;
UPDATE games SET season = 'winter'
  WHERE yr = 2006;
SELECT * FROM games;

See also

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense