表データの選択
ノート:この項のチュートリアルおよび例を実行するには、ユーザーHRとしてSQL DeveloperからOracle Databaseに接続する必要があります。手順については、「SQL DeveloperからのユーザーHRとしてのOracle Databaseへの接続」を参照してください。
問合せについて
問合せ、つまりSQLのSELECT文は、1つ以上の表またはビューのデータを選択します。
最も単純な形式の問合せは、次のような構文を持ちます。
SELECT select_list FROM source_listselect_list値ではデータの選択元となる列を指定し、source_list値ではこれらの列を含む表またはビューを指定します。
別のSQL文内にネストされている問合せは、副問合せと呼ばれます。
SQL*Plus環境では、SQL>プロンプトで問合せ(またはその他の任意のSQL文)を入力できます。
SQL Developer環境では、ワークシートで問合せ(またはその他の任意のSQL文)を入力できます。
ノート:問合せの結果を表示する場合に、ORDER BY句で順序を指定しないと、レコードは任意の順番になります。詳細は、「選択したデータのソート」を参照してください。
関連情報:
-
問合せおよび副問合せの詳細は、Oracle Database SQL言語リファレンスを参照してください。
-
SELECT文の詳細は、Oracle Database SQL言語リファレンスを参照してください。
-
SQL*Plusのコマンドライン・インタフェースの詳細は、SQL*Plusユーザーズ・ガイドおよびリファレンスを参照
-
Oracle SQL Developer User’s Guide for information about using the Worksheet in SQL Developer
SQL Developerでの問合せの実行
この項では、ワークシートを使用してSQL Developerで問合せを実行する方法を説明します。
ノート:ワークシートは問合せに限定されておらず、任意のSQL文の実行に使用できます。
SQL Developerで問合せを実行するステップ:
-
SQL Developerの右側のフレームに、hr_connペインが表示されます。
-
「ワークシート」サブペインが表示されない場合は、「ワークシート」タブを選択します。
-
ステップ4に進みます。
-
-
「SQLワークシート」アイコンを選択します。
-
接続の選択ウィンドウが開いた場合は、次の手順を実行します。
-
「接続」フィールドの値が
hr_connではない場合は、メニューから値を選択します。 -
「OK」を選択します。
ペインに、hr_connというラベルのタブと、「ワークシート」および「クエリー・ビルダー」という2つのサブペインが表示されています。ワークシートで、SQL文を入力できます。
-
-
ワークシートで、問合せ(SELECT文)を入力します。
-
アイコン「文の実行」をクリックします。
問合せが実行されます。ワークシートに、問合せの結果を示す「問合せ結果」ペインが表示されます。
-
「hr_conn」タブで、「クリア」アイコンをクリックします。
問合せが非表示になり、ワークシートに別のSQL文を入力できます。別のSQL文を実行すると、その結果が「問合せ結果」ペインに表示され、前の実行SQL文の結果が置き換えられます。
See Also: Oracle SQL Developer User’s Guide for information about using the Worksheet in SQL Developer
チュートリアル: 表のすべての列の選択
このチュートリアルでは、EMPLOYEES表のすべての列を選択する方法について説明します。
EMPLOYEES表のすべての列を選択するステップ:
-
hr_connというタブを含むペインが表示される場合は、それを選択します。そうでない場合、"「SQL Developerでの問合せの実行」の記載に従い、「SQLワークシート」をクリックします。
-
ワークシートで、次の問合せを入力します。
SELECT * FROM EMPLOYEES; -
「文の実行」アイコンをクリックします。
問合せが実行されます。ワークシートに、EMPLOYEES表のすべての列が示された「問合せ結果」ペインが表示されます。
注意:パスワードやクレジット・カード情報などの機密データを格納している列を含む表で、SELECT *を使用する場合は注意してください。
See Also: “Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer” for information about another way to view table data with SQL Developer
チュートリアル: 表の特定の列の選択
このチュートリアルでは、EMPLOYEES表の列FIRST_NAME、LAST_NAMEおよびDEPARTMENT_IDのみを選択する方法について説明します。
FIRST_NAME、LAST_NAMEおよびDEPARTMENT_IDのみを選択するステップ:
-
hr_connというタブを含むペインが表示される場合は、それを選択します。そうでない場合、"「SQL Developerでの問合せの実行」の記載に従い、SQLワークシート・アイコンをクリックします。
-
ワークシート・ペインに問合せが含まれている場合は、「クリア」アイコンを選択して問合せをクリアします。
-
ワークシートで、次の問合せを入力します。
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES; -
「文の実行」アイコンをクリックします。
問合せが実行されます。ワークシートの下に「問合せ結果」ペインが表示され、次のような問合せの結果が示されます。
FIRST_NAME LAST_NAME DEPARTMENT_ID
-------------------- ------------------------- -------------
Donald OConnell 50
Douglas Grant 50
Jennifer Whalen 10
Michael Hartstein 20
Pat Fay 20
Susan Mavris 40
Hermann Baer 70
Shelley Higgins 110
William Gietz 110
Steven King 90
Neena Kochhar 90
FIRST_NAME LAST_NAME DEPARTMENT_ID
-------------------- ------------------------- -------------
Lex De Haan 90
...
Kevin Feeney 50
107 rows selected.新しい見出しの下での選択した列の表示
問合せ結果を表示する際のデフォルトの列ヘッダーは、列の名前です。新規の見出しの下に列が表示されるためには、列名直後に新規ヘッダー(別名)を指定します。別名によって、問合せの間のみ列の名前が変更されますが、データベース内の名前は変更されません。
例2-5問合せは、「チュートリアル: 表の特定の列の選択」問合せと同じ行を選択しますが、その列の別名も指定します。別名は二重引用符で囲まれていないため、大文字で表示されます。
列の別名を二重引用符で囲むと、例2-6のように、大/小文字の区別が保持され、別名に空白を使用することも可能です。
関連項目:列の別名(c_alias)を含むSELECT文の詳細は、Oracle Database SQL言語リファレンスを参照してください。
例2-5 新規のヘッダーの下での選択された列の表示
SELECT FIRST_NAME First, LAST_NAME last, DEPARTMENT_ID DepT
FROM EMPLOYEES;結果は、次のようなテキストになります。
FIRST LAST DEPT
-------------------- ------------------------- ----------
Donald OConnell 50
Douglas Grant 50
Jennifer Whalen 10
Michael Hartstein 20
Pat Fay 20
Susan Mavris 40
Hermann Baer 70
Shelley Higgins 110
William Gietz 110
Steven King 90
Neena Kochhar 90
FIRST LAST DEPT
-------------------- ------------------------- ----------
Lex De Haan 90
...
Kevin Feeney 50
107 rows selected.例2-6 列の別名における大/小文字の区別の保持および空白の使用
SELECT FIRST_NAME "Given Name", LAST_NAME "Family Name"
FROM EMPLOYEES;結果は、次のようなテキストになります。
Given Name Family Name
-------------------- -------------------------
Donald OConnell
Douglas Grant
Jennifer Whalen
Michael Hartstein
Pat Fay
Susan Mavris
Hermann Baer
Shelley Higgins
William Gietz
Steven King
Neena Kochhar
Given Name Family Name
-------------------- -------------------------
Lex De Haan
...
Kevin Feeney
107 rows selected.指定した条件を満たすデータの選択
指定した条件に一致するデータのみを選択するには、SELECT文にWHERE句を含めます。
WHERE句の条件には、任意のSQL条件を使用できます(SQL条件の詳細は、『Oracle Database SQL言語リファレンス』を参照してください)。
例2-7の問合せは、部門90に所属する従業員のデータのみを選択します。
部門100、110および120の従業員のデータのみを選択する場合は、このWHERE句を使用します。
WHERE DEPARTMENT_ID IN (100, 110, 120);例2-8の問合せは、姓が「Ma」で始まる従業員のデータのみを選択します。
姓にmaが含まれる従業員のデータのみを選択する場合は、このWHERE句を使用します。
WHERE LAST_NAME LIKE '%ma%';例2-9の問合せは、給与が少なくとも11000であり、かつ歩合率がNULLではないという2つの条件をテストします。
関連情報:
-
SELECT文(WHERE句を含む)の詳細は、Oracle Database SQL言語リファレンスを参照
-
SQL条件の詳細は、Oracle Database SQL言語リファレンスを参照
例2-7 1つの部門からのデータの選択
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;結果は、次のようなテキストになります。
FIRST_NAME LAST_NAME DEPARTMENT_ID
-------------------- ------------------------- -------------
Steven King 90
Neena Kochhar 90
Lex De Haan 90
3 rows selected.例2-8 同じ部分文字列で始まる姓を持つデータの選択
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'Ma%';結果は、次のようなテキストになります。
FIRST_NAME LAST_NAME
-------------------- -------------------------
Jason Mallin
Steven Markle
James Marlow
Mattea Marvins
Randall Matos
Susan Mavris
6 rows selected.例2-9 2つの条件を満たすデータの選択
SELECT FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT "%"
FROM EMPLOYEES
WHERE (SALARY >= 11000) AND (COMMISSION_PCT IS NOT NULL);結果は、次のようなテキストになります。
FIRST_NAME LAST_NAME SALARY %
-------------------- ------------------------- ---------- ----------
John Russell 14000 .4
Karen Partners 13500 .3
Alberto Errazuriz 12000 .3
Gerald Cambrault 11000 .3
Lisa Ozer 11500 .25
Ellen Abel 11000 .3
6 rows selected.選択したデータのソート
問合せの結果を表示する場合、ORDER BY句で順序を指定しないと、レコードは任意の順番になります。
例2-10の問合せの結果は、LAST_NAMEを基準に昇順(デフォルト)でソートされます。
SQL Developerでは、ORDER BY句を使用せずに、列名をダブルクリックしてソートすることもできます。
例2-11に示すように、ソート基準を選択リストに含める必要はありません。
関連項目: ORDER BY句を含むSELECT文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例2-10 LAST_NAMEによる選択されたデータのソート
SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY LAST_NAME;結果:
FIRST_NAME LAST_NAME HIRE_DATE
-------------------- ------------------------- ---------
Ellen Abel 11-MAY-04
Sundar Ande 24-MAR-08
Mozhe Atkinson 30-OCT-05
David Austin 25-JUN-05
Hermann Baer 07-JUN-02
Shelli Baida 24-DEC-05
Amit Banda 21-APR-08
Elizabeth Bates 24-MAR-07
...
FIRST_NAME LAST_NAME HIRE_DATE
-------------------- ------------------------- ---------
Jose Manuel Urman 07-MAR-06
Peter Vargas 09-JUL-06
Clara Vishney 11-NOV-05
Shanta Vollman 10-OCT-05
Alana Walsh 24-APR-06
Matthew Weiss 18-JUL-04
Jennifer Whalen 17-SEP-03
Eleni Zlotkey 29-JAN-08
107 rows selected例2-11 選択対象ではない列による選択されたデータのソート
SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY LAST_NAME;結果:
FIRST_NAME HIRE_DATE
-------------------- ---------
Ellen 11-MAY-04
Sundar 24-MAR-08
Mozhe 30-OCT-05
David 25-JUN-05
Hermann 07-JUN-02
Shelli 24-DEC-05
Amit 21-APR-08
Elizabeth 24-MAR-07
...
FIRST_NAME HIRE_DATE
-------------------- ---------
Jose Manuel 07-MAR-06
Peter 09-JUL-06
Clara 11-NOV-05
Shanta 10-OCT-05
Alana 24-APR-06
Matthew 18-JUL-04
Jennifer 17-SEP-03
Eleni 29-JAN-08
107 rows selected.複数の表からのデータの選択
複数の表からデータを選択するには、結合と呼ばれる問合せを使用します。結合の表は少なくとも1つの列名を共有している必要があります。
全従業員のFIRST_NAME、LAST_NAME、DEPARTMENT_NAMEを選択したいとします。FIRST_NAMEおよびLAST_NAMEはEMPLOYEES表内にあり、DEPARTMENT_NAMEはDEPARTMENTS表内にあります。どちらの表にもDEPARTMENT_IDがあります。この場合、例2-12の問合せを使用できます。
表名の修飾子は、結合の一方の表にしかない列名ではオプションですが、両方の表にある列名では必須です。次の問合せは、例2-12の問合せと同等です。
SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY DEPARTMENT_NAME, LAST_NAME;修飾子付き列名を使用する問合せをより読みやすくするには、次の例では表の別名を使用します:
SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY d.DEPARTMENT_NAME, e.LAST_NAME;FROM句で別名を作成するのではなく、次の例では、問合せの中で先に作成できます。
SELECT e.FIRST_NAME "First",
e.LAST_NAME "Last",
d.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY d.DEPARTMENT_NAME, e.LAST_NAME;関連項目: 『Oracle Database SQL言語リファレンス』の結合の詳細は、
例2-12 2つの表からのデータの選択(2つの表の結合)
SELECT EMPLOYEES.FIRST_NAME "First",
EMPLOYEES.LAST_NAME "Last",
DEPARTMENTS.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY DEPARTMENTS.DEPARTMENT_NAME, EMPLOYEES.LAST_NAME;結果:
First Last Dept. Name
-------------------- ------------------------- ------------------------------
William Gietz Accounting
Shelley Higgins Accounting
Jennifer Whalen Administration
Lex De Haan Executive
Steven King Executive
Neena Kochhar Executive
John Chen Finance
...
Jose Manuel Urman Finance
Susan Mavris Human Resources
David Austin IT
...
Valli Pataballa IT
Pat Fay Marketing
Michael Hartstein Marketing
Hermann Baer Public Relations
Shelli Baida Purchasing
...
Sigal Tobias Purchasing
Ellen Abel Sales
...
Eleni Zlotkey Sales
Mozhe Atkinson Shipping
...
Matthew Weiss Shipping
106 rows selected.問合せでの演算子とファンクションの使用
問合せのselect_listにはSQL式を含めることができ、SQL式にはSQL演算子とSQLファンクションを含めることができます。これらの演算子とファンクションは、表データをオペランドおよび引数として持つことができます。SQL式が評価され、その値が問合せ結果に表示されます。
関連情報:
-
SQL演算子の詳細は、Oracle Database SQL言語リファレンスを参照
-
SQLファンクションの詳細は、Oracle Database SQL言語リファレンスを参照
問合せでの算術演算子の使用
基本的な算術演算子(+(加算)、-(減算)、\*(乗算)および/(除算)を列値で操作する。
例2-13の問合せは、部門90に所属している各従業員のLAST_NAME、SALARY(月給)および年間賃金をSALARYの降順で表示します、
例2-13 問合せにおける演算式の使用
SELECT LAST_NAME,
SALARY "Monthly Pay",
SALARY * 12 "Annual Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
ORDER BY SALARY DESC;結果:
LAST_NAME Monthly Pay Annual Pay
------------------------- ----------- ----------
King 24000 288000
De Haan 17000 204000
Kochhar 17000 204000問合せでの数値ファンクションの使用
数値ファンクションは、数値の入力を受け取って数値を返します。各数値ファンクションは、評価対象の行ごとに1つの値を返します。
SQLでサポートされる数値ファンクションのリストと説明は、Oracle Database SQL言語リファレンスに記載しています。
例2-14の問合せは、数値ファンクションROUNDを使用して、部門100の各従業員の日給 を1セント未満は四捨五入して表示します。
例2-15の問合せは、数値ファンクションTRUNCを使用して、部門100の各従業員の日給を1ドル未満は切り捨てられて表示します。
関連項目: SQL数値ファンクションの詳細は『Oracle Database SQL言語リファレンス』を参照してください。
例2-14 数値データの四捨五入
SELECT LAST_NAME,
ROUND (((SALARY * 12)/365), 2) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;結果:
LAST_NAME Daily Pay
------------------------- ----------
Chen 269.59
Faviet 295.89
Greenberg 394.52
Popp 226.85
Sciarra 253.15
Urman 256.44
6 rows selected.例2-15 数値データの切捨て
SELECT LAST_NAME,
TRUNC ((SALARY * 12)/365) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;結果:
LAST_NAME Daily Pay
------------------------- ----------
Chen 269
Faviet 295
Greenberg 394
Popp 226
Sciarra 253
Urman 256
6 rows selected.問合せでの連結演算子の使用
連結演算子(||)は、2つの文字列を1つの文字列に結合し、2つ目の文字列を1つ目の文字列に追加します。たとえば、'a'||'b'='ab'です。この演算子を使用すると、2つの列または式の情報を問合せ結果の同じ列の中に結合できます。
例2-16の問合せでは、選択した各従業員の名、スペースおよび姓を連結します。
関連項目:連結演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例2-16 文字データの連結
SELECT FIRST_NAME || ' ' || LAST_NAME "Name"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;結果:
Name
----------------------------------------------
John Chen
Daniel Faviet
Nancy Greenberg
Luis Popp
Ismael Sciarra
Jose Manuel Urman
6 rows selected.問合せでの文字ファンクションの使用
文字ファンクションは、文字の入力を受け取ります。大半の場合、文字値を返しますが、数値を返す場合もあります。各文字ファンクションは、評価対象の行ごとに1つの値を返します。
SQLでサポートされる文字ファンクションのリストと説明は、Oracle Database SQL言語リファレンスに記載しています。
ファンクションUPPER、INITCAPおよびLOWERは、文字の引数をそれぞれ大文字、先頭だけ大文字、小文字で表示します。
例2-17の問合せは、LAST_NAMEを大文字で、FIRST_NAMEは頭文字を大文字で他は小文字で、EMAILを小文字で表示します。
関連項目: SQL文字ファンクションの詳細は『Oracle Database SQL言語リファレンス』を参照してください。
例2-17 文字データの大/小文字の変更
SELECT UPPER(LAST_NAME) "Last",
INITCAP(FIRST_NAME) "First",
LOWER(EMAIL) "E-Mail"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY EMAIL;結果:
Last First E-Mail
------------------------- -------------------- -------------------------
FAVIET Daniel dfaviet
SCIARRA Ismael isciarra
CHEN John jchen
URMAN Jose Manuel jmurman
POPP Luis lpopp
GREENBERG Nancy ngreenbe
6 rows selected.問合せでの日付ファンクションの使用
日時ファンクションは、DATE、タイムスタンプ、間隔の値を操作します。各日付ファンクションは、評価対象の行ごとに1つの値を返します。
SQLでサポートされる日付ファンクションのリストと説明は、Oracle Database SQL言語リファレンスに記載しています。
各DATEおよびタイムスタンプの値に、Oracle Databaseには、次の情報が格納されています。
-
年
-
月
-
日付
-
時間
-
分
-
第2
タイムスタンプの値として、Oracle Databaseには、指定した精度の秒の小数部分も格納されます。また、タイムゾーンを格納するには、データ型TIMESTAMP WITH TIME ZONEまたはTIMESTAMP WITH LOCAL TIME ZONEを使用します。
DATEデータ型の詳細は、『Oracle Database SQL言語リファレンス』に関する項を参照してください。
TIMESTAMPデータ型の詳細は、『Oracle Database SQL言語リファレンス』に関する項を参照してください。
その他のタイム・スタンプ・データ型および間隔データ型の詳細は、『Oracle Database SQL言語リファレンス』に関する項を参照してください。
例2-18の問合せは、EXTRACTファンクションおよびSYSDATEファンクションを使用して、部門100の各従業員が何年間雇用されているかを表示します。SYSDATEファンクションは、システム・クロックの現在日をDATE値として返します。SYSDATEファンクションの詳細は、Oracle Database SQL言語リファレンスに関する項を参照してください。EXTRACTファンクションの詳細は、Oracle Database SQL言語リファレンスに関する項を参照してください。
例2-19の問合せは、SYSTIMESTAMPファンクションを使用して、現在のシステム日付と時間を表示します。SYSTIMESTAMPファンクションは、TIMESTAMP値を返します。SYSTIMESTAMPファンクションの詳細は、Oracle Database SQL言語リファレンスに関する項を参照してください。
問合せのFROM句内の表DUALは、Oracle Databaseによってデータ・ディクショナリとともに自動的に作成される1行の表です。SELECT文で定数式を計算する場合は、DUALから選択します。DUALには行が1つしか存在しないため、定数は1回しか返されません。DUALからの選択の詳細は、Oracle Database SQL言語リファレンスに関する項を参照してください。
関連項目: SQL日時関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
例2-18 日付間の年数の表示
SELECT LAST_NAME,
(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) "Years Employed"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY "Years Employed";結果:
LAST_NAME Years Employed
------------------------- --------------
Popp 5
Urman 6
Chen 7
Sciarra 7
Greenberg 10
Faviet 10
6 rows selected.例2-19 システム日付および時間の表示
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;結果は、現在のSYSTIMESTAMPの値によって異なりますが、次の形式で表示されます。
System Time and Date
-------------------------------------------------------------------
18:17:53, 12/27/2012問合せでの変換ファンクションの使用
変換ファンクションは、あるデータ型を別のデータ型に変換します。
SQLでサポートされる変換ファンクションのリストと説明は、Oracle Database SQL言語リファレンスに記載しています。
例2-20の問合せは、TO_CHARファンクションを使用して、HIRE_DATE値(2DATE2型)をFMMonth DD YYYY形式の文字値に変換します。FMは月名から先頭および末尾のブランクを削除します。FMMonth DD YYYYは、日付時間書式モデルの一例である。日付時間書式モデルの詳細は、Oracle Database SQL言語リファレンスに関する項を参照してください。
例2-21の問合せは、TO_NUMBERファンクションを使用して、POSTAL_CODE値(VARCHAR2型)を、計算で使用するNUMBER型の値に変換します。
関連情報:
-
SQL変換ファンクションの詳細は、Oracle Database SQL言語リファレンスを参照
例2-20 日付から文字列への書式テンプレートを使用した変換
SELECT LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'FMMonth DD YYYY') "Date Started"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;結果:
LAST_NAME HIRE_DATE Date Started
------------------------- --------- -----------------
Chen 28-SEP-05 September 28 2005
Faviet 16-AUG-02 August 16 2002
Greenberg 17-AUG-02 August 17 2002
Popp 07-DEC-07 December 7 2007
Sciarra 30-SEP-05 September 30 2005
Urman 07-MAR-06 March 7 2006
6 rows selected.例2-21文字列から数値への変換
SELECT CITY,
POSTAL_CODE "Old Code",
TO_NUMBER(POSTAL_CODE) + 1 "New Code"
FROM LOCATIONS
WHERE COUNTRY_ID = 'US'
ORDER BY POSTAL_CODE;結果:
CITY Old Code New Code
------------------------------ ------------ ----------
Southlake 26192 26193
South Brunswick 50090 50091
Seattle 98199 98200
South San Francisco 99236 99237
4 rows selected.問合せでの集計ファンクションの使用
集計ファンクションは、行のグループを取得し、単一の結果行を返します。行のグループは、表全体またはビュー全体の場合もあります。
SQLでサポートされる集計ファンクションのリストと説明は、Oracle Database SQL言語リファレンスに記載しています。
集計ファンクションは、GROUP BY句とともに使用すると特に効果的です。これにより、1つ以上の列によって問合せ結果をグループ化し、グループごとに結果を表示できます。
例2-22の問合せは、COUNTファンクションおよびGROUP BY句を使用して、各マネージャに対するレポートする人数を表示します。ワイルドカード文字*はレコード全体を表します。例2-22は、従業員の1人がマネージャにレポートしないことを示しています。次の問合せでは、その従業員の名、姓、役職を選択します。
COLUMN FIRST_NAME FORMAT A10;
COLUMN LAST_NAME FORMAT A10;
COLUMN JOB_TITLE FORMAT A10;
SELECT e.FIRST_NAME,
e.LAST_NAME,
j.JOB_TITLE
FROM EMPLOYEES e, JOBS j
WHERE e.JOB_ID = j.JOB_ID
AND MANAGER_ID IS NULL;結果:
FIRST_NAME LAST_NAME JOB_TITLE
---------- ---------- ----------
Steven King President問合せで、集計値が指定された条件を満たす行のみを返すには、問合せのHAVING句で集計ファンクションを使用します。
例2-23の問合せは、毎年給与に$1,000,000を超える額を費やしている部門の部門と給与額を表示します。
例2-24の問合せは、複数の集計ファンクションを使用して、各JOB_IDの給与の統計を表示します。
関連項目: SQL集計ファンクションの詳細は『Oracle Database SQL言語リファレンス』を参照してください。
例2-22 各グループの行数のカウント
SELECT MANAGER_ID "Manager",
COUNT(*) "Number of Reports"
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY MANAGER_ID;結果:
Manager Number of Reports
---------- -----------------
100 14
101 5
102 1
103 4
108 5
114 5
120 8
121 8
122 8
123 8
124 8
145 6
146 6
147 6
148 6
149 6
201 1
205 1
1
19 rows selected.例2-23 条件を満たす行への集計ファンクションの制限
SELECT DEPARTMENT_ID "Department",
SUM(SALARY*12) "All Salaries"
FROM EMPLOYEES
HAVING SUM(SALARY * 12) >= 1000000
GROUP BY DEPARTMENT_ID;結果:
Department All Salaries
---------- ------------
50 1876800
80 3654000例2-24 統計情報を得るための集計ファンクションの使用
SELECT JOB_ID,
COUNT(*) "#",
MIN(SALARY) "Minimum",
ROUND(AVG(SALARY), 0) "Average",
MEDIAN(SALARY) "Median",
MAX(SALARY) "Maximum",
ROUND(STDDEV(SALARY)) "Std Dev"
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;結果:
JOB_ID # Minimum Average Median Maximum Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
AC_ACCOUNT 1 8300 8300 8300 8300 0
AC_MGR 1 12008 12008 12008 12008 0
AD_ASST 1 4400 4400 4400 4400 0
AD_PRES 1 24000 24000 24000 24000 0
AD_VP 2 17000 17000 17000 17000 0
FI_ACCOUNT 5 6900 7920 7800 9000 766
FI_MGR 1 12008 12008 12008 12008 0
HR_REP 1 6500 6500 6500 6500 0
IT_PROG 5 4200 5760 4800 9000 1926
MK_MAN 1 13000 13000 13000 13000 0
MK_REP 1 6000 6000 6000 6000 0
PR_REP 1 10000 10000 10000 10000 0
PU_CLERK 5 2500 2780 2800 3100 239
PU_MAN 1 11000 11000 11000 11000 0
SA_MAN 5 10500 12200 12000 14000 1525
SA_REP 30 6100 8350 8200 11500 1524
SH_CLERK 20 2500 3215 3100 4200 548
ST_CLERK 20 2100 2785 2700 3600 453
ST_MAN 5 5800 7280 7900 8200 1066
19 rows selected.問合せでのNULL関連ファンクションの使用
NULL関連ファンクションは、NULL値の処理を容易にします。
SQLがサポートするNULL関連ファンクションのリストと説明は、『Oracle Database SQL言語リファレンス』に記載されます。
例2-25の問合せは、姓がBで始まる従業員の姓と歩合を返します。従業員が歩合を受け取っていない場合(つまり、COMMISSION_PCTがNULLの場合)、NVLファンクションはNULLを「Not Applicable」に置換する。
例2-26の問合せは、NVL2ファンクションを使用して、姓がBで始まる従業員の氏名、給与および収入を返します(COMMISSION_PCTが2NULL2でない場合、収入は給与と歩合の両方であり、COMMISSION_PCTがNULLの場合、収入は給与のみです)。
関連情報:
-
NVLファンクションの詳細は、Oracle Database SQL言語リファレンスを参照してください。
-
0ファンクションの詳細は、Oracle Database SQL言語リファレンスを参照してください。
例2-25 文字列によるNULL値の置換
SELECT LAST_NAME,
NVL(TO_CHAR(COMMISSION_PCT), 'Not Applicable') "COMMISSION"
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'B%'
ORDER BY LAST_NAME;結果:
LAST_NAME COMMISSION
------------------------- ----------------------------------------
Baer Not Applicable
Baida Not Applicable
Banda .1
Bates .15
Bell Not Applicable
Bernstein .25
Bissot Not Applicable
Bloom .2
Bull Not Applicable
9 rows selected.例2-26 NULL値と非NULL値に対する異なる式の指定
SELECT LAST_NAME, SALARY,
NVL2(COMMISSION_PCT, SALARY + (SALARY * COMMISSION_PCT), SALARY) INCOME
FROM EMPLOYEES WHERE LAST_NAME LIKE 'B%'
ORDER BY LAST_NAME;結果:
LAST_NAME SALARY INCOME
------------------------- ---------- ----------
Baer 10000 10000
Baida 2900 2900
Banda 6200 6820
Bates 7300 8395
Bell 4000 4000
Bernstein 9500 11875
Bissot 3300 3300
Bloom 10000 12000
Bull 4100 4100
9 rows selected.問合せでのCASE式の使用
CASE式を使用すると、サブプログラムを呼び出すことなく、SQL文でIF…THEN…ELSEロジックを使用できます。単純と検索の2種類のCASE式があります。
例2-27の問合せは、単純CASE式を使用し、各国コードの国名を表示します。
例2-28の問合せは、検索CASE式を使用して、勤続期間に関連付けられた日付範囲に基づいて、提示された昇給(15%、10%、5%または0%)を表示します。
関連情報:
-
CASE式の詳細は、Oracle Database SQL言語リファレンスを参照してください。
-
CASE式の詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。
-
CASE文の使用に関する項
例2-27 問合せでの単純CASE式の使用
SELECT UNIQUE COUNTRY_ID ID,
CASE COUNTRY_ID
WHEN 'AU' THEN 'Australia'
WHEN 'BR' THEN 'Brazil'
WHEN 'CA' THEN 'Canada'
WHEN 'CH' THEN 'Switzerland'
WHEN 'CN' THEN 'China'
WHEN 'DE' THEN 'Germany'
WHEN 'IN' THEN 'India'
WHEN 'IT' THEN 'Italy'
WHEN 'JP' THEN 'Japan'
WHEN 'MX' THEN 'Mexico'
WHEN 'NL' THEN 'Netherlands'
WHEN 'SG' THEN 'Singapore'
WHEN 'UK' THEN 'United Kingdom'
WHEN 'US' THEN 'United States'
ELSE 'Unknown'
END COUNTRY
FROM LOCATIONS
ORDER BY COUNTRY_ID;結果:
ID COUNTRY
-- --------------
AU Australia
BR Brazil
CA Canada
CH Switzerland
CN China
DE Germany
IN India
IT Italy
JP Japan
MX Mexico
NL Netherlands
SG Singapore
UK United Kingdom
US United States
14 rows selected.例2-28 問合せでの検索CASE式の使用
SELECT LAST_NAME "Name",
HIRE_DATE "Started",
SALARY "Salary",
CASE
WHEN HIRE_DATE < TO_DATE('01-Jan-03', 'dd-mon-yy')
THEN TRUNC(SALARY*1.15, 0)
WHEN HIRE_DATE >= TO_DATE('01-Jan-03', 'dd-mon-yy') AND
HIRE_DATE < TO_DATE('01-Jan-06', 'dd-mon-yy')
THEN TRUNC(SALARY*1.10, 0)
WHEN HIRE_DATE >= TO_DATE('01-Jan-06', 'dd-mon-yy') AND
HIRE_DATE < TO_DATE('01-Jan-07', 'dd-mon-yy')
THEN TRUNC(SALARY*1.05, 0)
ELSE SALARY
END "Proposed Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;結果:
Name Started Salary Proposed Salary
------------------------- --------- ---------- ---------------
Faviet 16-AUG-02 9000 10350
Greenberg 17-AUG-02 12008 13809
Chen 28-SEP-05 8200 9020
Sciarra 30-SEP-05 7700 8470
Urman 07-MAR-06 7800 8190
Popp 07-DEC-07 6900 6900
6 rows selected.問合せでのDECODEファンクションの使用
DECODEファンクションは、式といくつかの検索値と比較します。式の値が検索値に一致すると、DECODEは、その検索値に関連付けられている結果を返します。DECODEが一致しない場合、デフォルト値(指定されている場合)またはNULL(デフォルト値が指定されていない場合)が返されます。
例2-29の問合せは、DECODEファンクションを使用して、3つの異なる職務に対して提案された給与の値アップを表示します。式はJOB_ID、検索値は'PU_CLERK'、'SH_CLERK'および'ST_CLERK'で、デフォルトはSALARYです。
ノート: DECODEファンクションの引数は、任意のSQL数値または文字型です。式および各検索値は、比較の前に最初の検索値のデータ型に自動的に変換されます。戻り値は、最初の結果と同じデータ型に自動的に変換されます。最初の結果のデータ型がCHARの場合、または最初の結果がNULLの場合、Oracleは戻り値をデータ型VARCHAR2に変換します。
関連情報:
-
DECODEファンクションの詳細は、Oracle Database SQL言語リファレンスを参照
例2-29 問合せにおけるDECODEファンクションの使用
SELECT LAST_NAME, JOB_ID, SALARY,
DECODE(JOB_ID,
'PU_CLERK', SALARY * 1.10,
'SH_CLERK', SALARY * 1.15,
'ST_CLERK', SALARY * 1.20,
SALARY) "Proposed Salary"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%_CLERK'
AND LAST_NAME < 'E'
ORDER BY LAST_NAME;結果:
LAST_NAME JOB_ID SALARY Proposed Salary
------------------------- ---------- ---------- ---------------
Atkinson ST_CLERK 2800 3360
Baida PU_CLERK 2900 3190
Bell SH_CLERK 4000 4600
Bissot ST_CLERK 3300 3960
Bull SH_CLERK 4100 4715
Cabrio SH_CLERK 3000 3450
Chung SH_CLERK 3800 4370
Colmenares PU_CLERK 2500 2750
Davies ST_CLERK 3100 3720
Dellinger SH_CLERK 3400 3910
Dilly SH_CLERK 3600 4140
11 rows selected.