Difference between pages "SQLZOO:SELECT basics" and "+(dates)/de"

From SQLZOO
(Difference between pages)
Jump to: navigation, search
 
 
Line 1: Line 1:
==Introducing the BBC Table of Countries==
+
[[Category:Deutsch]]
This tutorial introduces SQL as a  query language. We will be using the SELECT command on the table bbc:
+
<table align='right' border='1'>
<table class='db_ref'><tr>
+
<caption>Kompatibilität</caption>
<th>name</th>
+
<tr><th colspan='3'>d + i</th></tr>
<th>region</th>
+
<tr><td align='center'>'''DBMS'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
<th>area</th>
+
<tr><td align='left'>Ingres</td><td>Nein</td><td>whn + date('7 day')</td></tr>
<th>population</th>
+
<tr><td align='left'>MySQL</td><td>Ja</td><td></td></tr>
<th>gdp</th>
+
<tr><td align='left'>Oracle</td><td>Ja</td><td></td></tr>
</tr>
+
<tr><td align='left'>PostgreSQL</td><td>Nein</td><td>[[%2B_INTERVAL/de |d+ INTERVAL 'i DAY']]</td></tr>
<tr>
+
<tr><td align='left'>SQL Server</td><td>Ja</td><td></td></tr>
<td>Afghanistan</td>
+
<td>South Asia</td>
+
<td align='right'>652225</td>
+
<td align='right'>26000000</td>
+
<td></td>
+
</tr>
+
<tr>
+
<td>Albania</td>
+
<td>Europe</td>
+
<td align='right'>28728</td>
+
<td align='right'>3200000</td>
+
<td align='right'>6656000000</td>
+
</tr>
+
<tr>
+
<td>Algeria</td>
+
<td>Middle East</td>
+
<td align='right'>2400000</td>
+
<td align='right'>32900000</td>
+
<td align='right'>75012000000</td>
+
</tr>
+
<tr>
+
<td>Andorra</td>
+
<td>Europe</td>
+
<td align='right'>468</td>
+
<td align='right'>64000</td>
+
<td></td>
+
</tr>
+
<tr>
+
<td colspan='5'>...</td>
+
</tr>
+
 
</table>
 
</table>
  
<div class='qu'>
+
<h1>+ (dates)</h1>
The example shows the population of 'France'.
+
<p>d + i liefert ein Datum, das i Tage nach dem Datum d liegt.</p>
Strings should be in 'single quotes';
+
<pre style='width:80ex'>
 
+
DATE '2006-05-20' + 7  -> DATE '2006-05-27'
<p class='imper'>Show the population of Germany</p>
+
</pre>
 
+
<div class=params>schema:gisq</div>
<source lang='sql' class='def'>
+
<div class='ht'>
SELECT population FROM bbc
+
Im Beispiel wird ein Datum 7 Tage nach dem Datumswert in <code>whn</code> ermittelt.
   WHERE name = 'France'
+
<source lang='sql' class='def e-ingres'>
 +
SELECT whn, whn + DATE('7 day')
 +
   FROM eclipse
 
</source>
 
</source>
 
+
<source lang='sql' class='def e-postgres'>
<source lang='sql' class='ans'>
+
SELECT whn, whn+INTERVAL 7 DAY
SELECT population FROM bbc
+
   FROM eclipse
   WHERE name = 'Germany'
+
 
</source>
 
</source>
</div>
+
<source lang='sql' class='def e-oracle'>
 
+
SELECT whn, whn+7
<div class='qu'>
+
   FROM gisq.eclipse
<div>This query shows the population density <code>population/area</code>
+
for each country where the area is over 5,000,000 km<sup>2</sup>.</div>
+
<div class='imper'>Show the per capita gdp: <code>gdp/population</code>
+
for each country where the area is over 5,000,000 km<sup>2</sup></div>
+
 
+
<source lang='sql' class='def'>
+
SELECT name, population/area FROM bbc
+
   WHERE area > 5000000
+
 
</source>
 
</source>
 
<source lang='sql' class='ans'>
 
SELECT name, gdp/population FROM bbc
 
  WHERE area > 5000000
 
</source>
 
</div>
 
 
<div class='qu'>
 
Where to find some very small, very rich countries.<br/>
 
<b>We use <code>AND</code> to ensure that two or more conditions hold
 
true.</b>
 
<div>The example shows the countries where the population is small and the
 
gdp is high.</div>
 
<div class='imper'>Show the <b>name</b> and <b>region</b> where the area is less then 2000 and the gdp is more than 5000000000</div>
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT name , region
+
SELECT whn, whn+7
   FROM bbc
+
   FROM eclipse
  WHERE population < 2000000
+
    AND gdp > 5000000000
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name , region
+
  FROM bbc
+
  WHERE area < 2000
+
    AND gdp > 5000000000
+
 
</source>
 
</source>
 
</div>
 
</div>
  
 +
<p>Siehe auch</p>
 +
<ul>
 +
  <li>[[EXTRACT/de | Funktion EXTRACT]]</li>
 +
  <li>[[%2B_INTERVAL/de |Funktion + INTERVAL]]</li>
 +
</ul>
  
<div class='qu'>
+
{{Languages}}
Checking a list The word <b>IN</b> allows us to check if an item is in a list.
+
The example shows the name and population for the countries 'Ireland', 'Iceland' and 'Denmark'
+
<div class='imper'>Show the '''name''' and the '''population''' for 'Denmark', 'Finland', 'Norway', 'Sweden'
+
</div>
+
<source lang='sql' class='def'>
+
SELECT name, population FROM bbc
+
  WHERE name IN ('Ireland', 'Iceland',
+
                'Denmark')</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name, population FROM bbc
+
  WHERE name IN ('Denmark', 'Finland',
+
                'Norway', 'Sweden')</source>
+
</div>
+
 
+
<div class='qu'>
+
What are the countries beginning with G?
+
'''The word''' <code>LIKE</code> '''permits pattern matching''' - % '''is the wildcard'''.
+
The examples shows countries beginning with D
+
<div class='imper'>Show each country that begins with G</div>
+
<source lang='sql' class='def'>
+
SELECT name FROM bbc
+
  WHERE name LIKE 'D%'
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name FROM bbc
+
  WHERE name LIKE 'G%'
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
Which countries are not too small and not too big?
+
<code>BETWEEN</code> allows range checking - note that it is inclusive.
+
<div class='imper'>'''Show the area in 1000 square km. Show''' ''area''/1000 '''instead of''' ''area''</div>
+
<source lang='sql' class='def'>
+
SELECT name, area FROM bbc
+
  WHERE area BETWEEN 207600 AND 244820
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name, area/1000 FROM bbc
+
  WHERE area BETWEEN 207600 AND 244820
+
</source>
+
</div>
+
<p>[[SELECT Quiz]]</p>
+
<p>You are ready for tutorial one:[[SELECT_from_BBC_Tutorial |SELECT statements with WHERE.]]</p>
+

Latest revision as of 18:18, 21 October 2012

Kompatibilität
d + i
DBMSOKAlternative
IngresNeinwhn + date('7 day')
MySQLJa
OracleJa
PostgreSQLNeind+ INTERVAL 'i DAY'
SQL ServerJa

+ (dates)

d + i liefert ein Datum, das i Tage nach dem Datum d liegt.

 DATE '2006-05-20' + 7  -> DATE '2006-05-27'  
schema:gisq

Im Beispiel wird ein Datum 7 Tage nach dem Datumswert in whn ermittelt.

SELECT whn, whn + DATE('7 day')
  FROM eclipse
SELECT whn, whn+INTERVAL 7 DAY
  FROM eclipse
SELECT whn, whn+7 
  FROM gisq.eclipse
SELECT whn, whn+7 
  FROM eclipse

Siehe auch

Sprache: English  • Deutsch
Personal tools

Variants
Actions
Reference
Toolbox
Google AdSense