Difference between pages "SELECT from BBC Tutorial" and "HOUR/de"

From SQLZOO
(Difference between pages)
Jump to: navigation, search
 
 
Line 1: Line 1:
 
+
[[Category:Deutsch]]
<div class='ref_section'>
+
<table align='right' border='1'>
<table class='db_ref'>
+
<caption>Kompatibilität</caption>
<caption>bbc</caption>
+
<tr><th colspan='3'>HOUR(d)</th></tr>
<tr>
+
<tr><td align='center'>'''DBMS'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
<th>name</th>
+
<tr><td align='left'>Ingres</td><td>Nein</td><td>[[EXTRACT/de | EXTRACT(HOUR from d)]]</td></tr>
<th>region</th>
+
<tr><td align='left'>MySQL</td><td>Ja</td><td>[[EXTRACT/de | EXTRACT(HOUR from d)]]</td></tr>
<th>area</th>
+
<tr><td align='left'>Oracle</td><td>Nein</td><td>[[TO_CHAR(dates)/de | TO_CHAR(d,'HH24')]]</td></tr>
<th>population</th>
+
<tr><td align='left'>PostgreSQL</td><td>Nein</td><td>[[EXTRACT/de | EXTRACT(HOUR from d)]]</td></tr>
<th>gdp</th>
+
<tr><td align='left'>SQL Server</td><td>Ja</td><td>[[DATEPART/de | DATEPART(HOUR,d)]]</td></tr>
</tr>
+
<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>
 
  
==BBC Country Profiles==
+
<h1>HOUR</h1>
In this tutorial you will use the SELECT command on the table bbc:
+
<p>HOUR ermittelt die Stunde aus einem Datums-/Zeitwert.</p>
 +
<p></p>
 +
<pre style='width:60ex'>
 +
  HOUR(d)
 +
</pre>
  
<div class='extra_space' style='width:1em; height:2.5em;'></div>
+
<div class='ht'>
 
+
In diesem Beispiel wird die Stunde aus dem <code>whn</code>-Datumswert ermittelt.
<div class="progress_panel"><div>
+
<source lang='sql' class='def e-ingres'>
  <div class="summary">Summary</div>
+
SELECT EXTRACT(HOUR from whn) AS v
  <div class="progressbarbg">
+
      ,whn
    <div class="progressbar"></div>
+
      ,wht
  </div>
+
  FROM eclipse
</div></div>
+
 
+
==Warming up==
+
<div class='qu'>
+
[[Read_the_notes_about_this_table. |Read the notes about this table.]] Observe the result of running a simple SQL command.
+
<source lang='sql' class='def'>
+
SELECT name, region, population FROM bbc
+
 
</source>
 
</source>
 
+
<source lang='sql' class='def e-postgres'>
<source lang='sql' class='ans'>
+
SELECT EXTRACT(HOUR from whn) AS v
SELECT name, region, population FROM bbc
+
      ,whn
 +
      ,wht
 +
  FROM eclipse
 
</source>
 
</source>
</div>
+
<source lang='sql' class='def e-oracle'>
 
+
SELECT TO_CHAR(whn,'HOUR') AS v
==Large Countries==
+
      ,whn
[[WHERE_filters |How to use WHERE to filter records.]]
+
      ,wht
<div class='qu'>
+
  FROM gisq.eclipse
Show the name for the countries  that have a population of at least 200 million. (200 million is 200000000, there are eight zeros)
+
<source lang='sql' class='def'>
+
SELECT name FROM bbc
+
WHERE population>250000000
+
 
</source>
 
</source>
 
<source lang='sql' class='ans'>
 
SELECT name FROM bbc
 
WHERE population>200000000
 
</source>
 
</div>
 
 
 
<div class='qu'>
 
Give the name and the per capita GDP for those countries with a population of at least 200 million.
 
<div title="HELP:How to calculate per capita GDP" class='hint'>
 
per capita GDP is the GDP divided by the population GDP/population
 
</div>
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
</source>
+
SELECT HOUR(whn) AS v, whn, wht
 
+
   FROM eclipse
<source lang='sql' class='ans'>
+
SELECT name, gdp/population FROM bbc
+
   WHERE population > 200000000
+
 
</source>
 
</source>
 
</div>
 
</div>
  
<div class='qu'>
+
<p>Siehe auch</p>
Show the <code>name</code> and <code>population</code> in millions for the countries of 'Middle East'
+
<ul>
Divide the population by 1000000 to get population in millions.
+
  <li>[[EXTRACT/de | Funktion EXTRACT]]</li>
<source lang='sql' class='def'>
+
  <li>[[%2B(dates)/de | Funktion +(date)]]</li>
</source>
+
  <li>[[MONTH/de | Funktion MONTH]]</li>
 +
  <li>[[MINUTE/de | Funktion MINUTE]]</li>
 +
  <li>[[DAY/de | Funktion DAY]]</li>
 +
  <li>[[SECOND/de | Funktion SECOND]]</li>
 +
</ul>
  
<source lang='sql' class='ans'>
+
{{Languages}}
SELECT name, population/1000000 FROM bbc
+
  WHERE region='Middle East'
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
Show the <code>name</code> and <code>population</code> for 'France', 'Germany', 'Italy'
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name, population FROM bbc
+
  WHERE name IN ('France','Germany','Italy')
+
</source>
+
</div>
+
 
+
<div class='qu'>
+
Identify the countries which have names including the word 'United'
+
<source lang='sql' class='def'>
+
</source>
+
 
+
<source lang='sql' class='ans'>
+
SELECT name FROM bbc
+
  WHERE name LIKE '%United%'
+
</source>
+
</div>
+
<div>
+
<div class="lsclear">Clear your results</div>
+
<p><div class="quizlink">[[BBC QUIZ]]</div></p>
+
</div>
+
<h2>What Next</h2>
+
<ul>
+
  <li>You can play a game:
+
  [http://sqlzoo.net/~andrew/brain/bt.htm Find the duplicate]
+
  </li>
+
  <li>You can to continue practicing the the same techniques
+
    and gain more experience of the basic skills on the Nobel table.
+
  [[SELECT_from_Nobel_Tutorial |The <code>WHERE</code> statement using the
+
  <code>nobel</code> table.]]
+
  </li>
+
  <li>You can learn about nested statements, these are instructive
+
    and entertaining, but rarely useful.
+
  [[SELECT_within_SELECT_Tutorial |Nested <code>SELECT</code> statements using the
+
  <code>bbc</code> table.]]</li>
+
</ul>
+

Latest revision as of 21:17, 21 October 2012

Kompatibilität
HOUR(d)
DBMSOKAlternative
IngresNein EXTRACT(HOUR from d)
MySQLJa EXTRACT(HOUR from d)
OracleNein TO_CHAR(d,'HH24')
PostgreSQLNein EXTRACT(HOUR from d)
SQL ServerJa DATEPART(HOUR,d)

HOUR

HOUR ermittelt die Stunde aus einem Datums-/Zeitwert.

   HOUR(d)

In diesem Beispiel wird die Stunde aus dem whn-Datumswert ermittelt.

SELECT EXTRACT(HOUR FROM whn) AS v
      ,whn
      ,wht
  FROM eclipse
SELECT EXTRACT(HOUR FROM whn) AS v
      ,whn
      ,wht
  FROM eclipse
SELECT TO_CHAR(whn,'HOUR') AS v
      ,whn
      ,wht
  FROM gisq.eclipse
SELECT HOUR(whn) AS v, whn, wht
  FROM eclipse

Siehe auch

Sprache: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense