• Objective

After completing this lesson,you should be able to do the following:

• List the capabilities of SQL statements.
• Execute a basic SELECT statement

Lesson Agenda

• Basic SELECT statement
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of concatenation operator,literal character strings,alternative quote operator,and the DISTINCT keyword
• DESCRIBE command

Capabilities of SQL SELECT Statements

Basic SELECT Statement

SELECT *|{
   [DISTINCT] column|expression [alias],...} FROM table;

• SELECT identifies the columns to be displayed.
• FROM identifies the table containing those columns.

Selecting All Columns

SQL> SELECT * FROM departments;DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------           10 Administration                        200        1700           20 Marketing                             201        1800           30 Purchasing                            114        1700           40 Human Resources                       203        2400           50 Shipping                              121        1500           60 IT                                    103        1400           70 Public Relations                      204        2700           80 Sales                                 145        2500           90 Executive                             100        1700          100 Finance                               108        1700          110 Accounting                            205        1700DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------          120 Treasury                                         1700          130 Corporate Tax                                    1700          140 Control And Credit                               1700          150 Shareholder Services                             1700          160 Benefits                                         1700          170 Manufacturing                                    1700          180 Construction                                     1700          190 Contracting                                      1700          200 Operations                                       1700          210 IT Support                                       1700          220 NOC                                              1700DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------          230 IT Helpdesk                                      1700          240 Government Sales                                 1700          250 Retail Sales                                     1700          260 Recruiting                                       1700          270 Payroll                                          170027 rows selected.

Selecting Specific Columns

SQL> SELECT department_id,location_id FROM departments;DEPARTMENT_ID LOCATION_ID------------- -----------           10        1700           20        1800           30        1700           40        2400           50        1500           60        1400           70        2700           80        2500           90        1700          100        1700          110        1700DEPARTMENT_ID LOCATION_ID------------- -----------          120        1700          130        1700          140        1700          150        1700          160        1700          170        1700          180        1700          190        1700          200        1700          210        1700          220        1700DEPARTMENT_ID LOCATION_ID------------- -----------          230        1700          240        1700          250        1700          260        1700          270        170027 rows selected.

Writting SQL Statements

• SQL statements are not case-sensitive.(约定俗称的东西，关键字大写，表名称、字段名称小写.)
• SQL statements can be entered on one or more lines.
• Keywords cannot be abbreviated or split across lines.
• Clauses are usually placed on separate lines.
• Idents are used to enhance readbility.
• In SQL Developer,SQL statements can optionally be terminated by a semicolon(;).Semicolons are required when you execute multiple SQL statements.
• In SQL*Plus,you are required to end each SQL statement with a semicolon(;).Or you can do this(end each SQL statement with a /);

Column Heading Defaults

• SQL Developer:
  • -Default heading alignment:Left-aligned
  • -Default heading display:Uppercase
• SQL*Plus:
  • -Character and Date column headings are left-aligned.
  • -Number column headings are right-aligned.
  • -Default heading display:Uppercase 

 Arithmetic Expressions

Create expressions with number and data by using arithmetic operators.

OPerator	Description
+	Add
-	Subtract
*	Multiply
/	Divide

 

 

 

 

 

Using Arithmetic Operators

SQL> SELECT last_name,salary,salary+300 FROM employees;LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------OConnell                        2600       2900Grant                           2600       2900Whalen                          4400       4700Hartstein                      13000      13300Fay                             6000       6300Mavris                          6500       6800Baer                           10000      10300Higgins                        12008      12308Gietz                           8300       8600King                           24000      24300Kochhar                        17000      17300LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------De Haan                        17000      17300Hunold                          9000       9300Ernst                           6000       6300Austin                          4800       5100Pataballa                       4800       5100Lorentz                         4200       4500Greenberg                      12008      12308Faviet                          9000       9300Chen                            8200       8500Sciarra                         7700       8000Urman                           7800       8100LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Popp                            6900       7200Raphaely                       11000      11300Khoo                            3100       3400Baida                           2900       3200Tobias                          2800       3100Himuro                          2600       2900Colmenares                      2500       2800Weiss                           8000       8300Fripp                           8200       8500Kaufling                        7900       8200Vollman                         6500       6800LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Mourgos                         5800       6100Nayer                           3200       3500Mikkilineni                     2700       3000Landry                          2400       2700Markle                          2200       2500Bissot                          3300       3600Atkinson                        2800       3100Marlow                          2500       2800Olson                           2100       2400Mallin                          3300       3600Rogers                          2900       3200LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Gee                             2400       2700Philtanker                      2200       2500Ladwig                          3600       3900Stiles                          3200       3500Seo                             2700       3000Patel                           2500       2800Rajs                            3500       3800Davies                          3100       3400Matos                           2600       2900Vargas                          2500       2800Russell                        14000      14300LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Partners                       13500      13800Errazuriz                      12000      12300Cambrault                      11000      11300Zlotkey                        10500      10800Tucker                         10000      10300Bernstein                       9500       9800Hall                            9000       9300Olsen                           8000       8300Cambrault                       7500       7800Tuvault                         7000       7300King                           10000      10300LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Sully                           9500       9800McEwen                          9000       9300Smith                           8000       8300Doran                           7500       7800Sewall                          7000       7300Vishney                        10500      10800Greene                          9500       9800Marvins                         7200       7500Lee                             6800       7100Ande                            6400       6700Banda                           6200       6500LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Ozer                           11500      11800Bloom                          10000      10300Fox                             9600       9900Smith                           7400       7700Bates                           7300       7600Kumar                           6100       6400Abel                           11000      11300Hutton                          8800       9100Taylor                          8600       8900Livingston                      8400       8700Grant                           7000       7300LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Johnson                         6200       6500Taylor                          3200       3500Fleaur                          3100       3400Sullivan                        2500       2800Geoni                           2800       3100Sarchand                        4200       4500Bull                            4100       4400Dellinger                       3400       3700Cabrio                          3000       3300Chung                           3800       4100Dilly                           3600       3900LAST_NAME                     SALARY SALARY+300------------------------- ---------- ----------Gates                           2900       3200Perkins                         2500       2800Bell                            4000       4300Everett                         3900       4200McCain                          3200       3500Jones                           2800       3100Walsh                           3100       3400Feeney                          3000       3300107 rows selected.

Operator Precedence

SQL> SELECT last_name,salary,12*salary + 100 FROM employees;LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------OConnell                        2600         31300Grant                           2600         31300Whalen                          4400         52900Hartstein                      13000        156100Fay                             6000         72100Mavris                          6500         78100Baer                           10000        120100Higgins                        12008        144196Gietz                           8300         99700King                           24000        288100Kochhar                        17000        204100LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------De Haan                        17000        204100Hunold                          9000        108100Ernst                           6000         72100Austin                          4800         57700Pataballa                       4800         57700Lorentz                         4200         50500Greenberg                      12008        144196Faviet                          9000        108100Chen                            8200         98500Sciarra                         7700         92500Urman                           7800         93700LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Popp                            6900         82900Raphaely                       11000        132100Khoo                            3100         37300Baida                           2900         34900Tobias                          2800         33700Himuro                          2600         31300Colmenares                      2500         30100Weiss                           8000         96100Fripp                           8200         98500Kaufling                        7900         94900Vollman                         6500         78100LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Mourgos                         5800         69700Nayer                           3200         38500Mikkilineni                     2700         32500Landry                          2400         28900Markle                          2200         26500Bissot                          3300         39700Atkinson                        2800         33700Marlow                          2500         30100Olson                           2100         25300Mallin                          3300         39700Rogers                          2900         34900LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Gee                             2400         28900Philtanker                      2200         26500Ladwig                          3600         43300Stiles                          3200         38500Seo                             2700         32500Patel                           2500         30100Rajs                            3500         42100Davies                          3100         37300Matos                           2600         31300Vargas                          2500         30100Russell                        14000        168100LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Partners                       13500        162100Errazuriz                      12000        144100Cambrault                      11000        132100Zlotkey                        10500        126100Tucker                         10000        120100Bernstein                       9500        114100Hall                            9000        108100Olsen                           8000         96100Cambrault                       7500         90100Tuvault                         7000         84100King                           10000        120100LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Sully                           9500        114100McEwen                          9000        108100Smith                           8000         96100Doran                           7500         90100Sewall                          7000         84100Vishney                        10500        126100Greene                          9500        114100Marvins                         7200         86500Lee                             6800         81700Ande                            6400         76900Banda                           6200         74500LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Ozer                           11500        138100Bloom                          10000        120100Fox                             9600        115300Smith                           7400         88900Bates                           7300         87700Kumar                           6100         73300Abel                           11000        132100Hutton                          8800        105700Taylor                          8600        103300Livingston                      8400        100900Grant                           7000         84100LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Johnson                         6200         74500Taylor                          3200         38500Fleaur                          3100         37300Sullivan                        2500         30100Geoni                           2800         33700Sarchand                        4200         50500Bull                            4100         49300Dellinger                       3400         40900Cabrio                          3000         36100Chung                           3800         45700Dilly                           3600         43300LAST_NAME                     SALARY 12*SALARY+100------------------------- ---------- -------------Gates                           2900         34900Perkins                         2500         30100Bell                            4000         48100Everett                         3900         46900McCain                          3200         38500Jones                           2800         33700Walsh                           3100         37300Feeney                          3000         36100107 rows selected.

SQL> SELECT last_name,salary,12*(salary + 100) FROM employees;LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------OConnell                        2600           32400Grant                           2600           32400Whalen                          4400           54000Hartstein                      13000          157200Fay                             6000           73200Mavris                          6500           79200Baer                           10000          121200Higgins                        12008          145296Gietz                           8300          100800King                           24000          289200Kochhar                        17000          205200LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------De Haan                        17000          205200Hunold                          9000          109200Ernst                           6000           73200Austin                          4800           58800Pataballa                       4800           58800Lorentz                         4200           51600Greenberg                      12008          145296Faviet                          9000          109200Chen                            8200           99600Sciarra                         7700           93600Urman                           7800           94800LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Popp                            6900           84000Raphaely                       11000          133200Khoo                            3100           38400Baida                           2900           36000Tobias                          2800           34800Himuro                          2600           32400Colmenares                      2500           31200Weiss                           8000           97200Fripp                           8200           99600Kaufling                        7900           96000Vollman                         6500           79200LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Mourgos                         5800           70800Nayer                           3200           39600Mikkilineni                     2700           33600Landry                          2400           30000Markle                          2200           27600Bissot                          3300           40800Atkinson                        2800           34800Marlow                          2500           31200Olson                           2100           26400Mallin                          3300           40800Rogers                          2900           36000LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Gee                             2400           30000Philtanker                      2200           27600Ladwig                          3600           44400Stiles                          3200           39600Seo                             2700           33600Patel                           2500           31200Rajs                            3500           43200Davies                          3100           38400Matos                           2600           32400Vargas                          2500           31200Russell                        14000          169200LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Partners                       13500          163200Errazuriz                      12000          145200Cambrault                      11000          133200Zlotkey                        10500          127200Tucker                         10000          121200Bernstein                       9500          115200Hall                            9000          109200Olsen                           8000           97200Cambrault                       7500           91200Tuvault                         7000           85200King                           10000          121200LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Sully                           9500          115200McEwen                          9000          109200Smith                           8000           97200Doran                           7500           91200Sewall                          7000           85200Vishney                        10500          127200Greene                          9500          115200Marvins                         7200           87600Lee                             6800           82800Ande                            6400           78000Banda                           6200           75600LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Ozer                           11500          139200Bloom                          10000          121200Fox                             9600          116400Smith                           7400           90000Bates                           7300           88800Kumar                           6100           74400Abel                           11000          133200Hutton                          8800          106800Taylor                          8600          104400Livingston                      8400          102000Grant                           7000           85200LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Johnson                         6200           75600Taylor                          3200           39600Fleaur                          3100           38400Sullivan                        2500           31200Geoni                           2800           34800Sarchand                        4200           51600Bull                            4100           50400Dellinger                       3400           42000Cabrio                          3000           37200Chung                           3800           46800Dilly                           3600           44400LAST_NAME                     SALARY 12*(SALARY+100)------------------------- ---------- ---------------Gates                           2900           36000Perkins                         2500           31200Bell                            4000           49200Everett                         3900           48000McCain                          3200           39600Jones                           2800           34800Walsh                           3100           38400Feeney                          3000           37200107 rows selected.

Defining a Null Value

• Null is a value that is unavailable,unassigned,unknow,or inapplicable.
• Null is not the same as zero or a blank space.

SQL> SELECT last_name,job_id,salary,commission_pct FROM employees;LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------OConnell                  SH_CLERK         2600Grant                     SH_CLERK         2600Whalen                    AD_ASST          4400Hartstein                 MK_MAN          13000Fay                       MK_REP           6000Mavris                    HR_REP           6500Baer                      PR_REP          10000Higgins                   AC_MGR          12008Gietz                     AC_ACCOUNT       8300King                      AD_PRES         24000Kochhar                   AD_VP           17000LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------De Haan                   AD_VP           17000Hunold                    IT_PROG          9000Ernst                     IT_PROG          6000Austin                    IT_PROG          4800Pataballa                 IT_PROG          4800Lorentz                   IT_PROG          4200Greenberg                 FI_MGR          12008Faviet                    FI_ACCOUNT       9000Chen                      FI_ACCOUNT       8200Sciarra                   FI_ACCOUNT       7700Urman                     FI_ACCOUNT       7800LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Popp                      FI_ACCOUNT       6900Raphaely                  PU_MAN          11000Khoo                      PU_CLERK         3100Baida                     PU_CLERK         2900Tobias                    PU_CLERK         2800Himuro                    PU_CLERK         2600Colmenares                PU_CLERK         2500Weiss                     ST_MAN           8000Fripp                     ST_MAN           8200Kaufling                  ST_MAN           7900Vollman                   ST_MAN           6500LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Mourgos                   ST_MAN           5800Nayer                     ST_CLERK         3200Mikkilineni               ST_CLERK         2700Landry                    ST_CLERK         2400Markle                    ST_CLERK         2200Bissot                    ST_CLERK         3300Atkinson                  ST_CLERK         2800Marlow                    ST_CLERK         2500Olson                     ST_CLERK         2100Mallin                    ST_CLERK         3300Rogers                    ST_CLERK         2900LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Gee                       ST_CLERK         2400Philtanker                ST_CLERK         2200Ladwig                    ST_CLERK         3600Stiles                    ST_CLERK         3200Seo                       ST_CLERK         2700Patel                     ST_CLERK         2500Rajs                      ST_CLERK         3500Davies                    ST_CLERK         3100Matos                     ST_CLERK         2600Vargas                    ST_CLERK         2500Russell                   SA_MAN          14000             .4LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Partners                  SA_MAN          13500             .3Errazuriz                 SA_MAN          12000             .3Cambrault                 SA_MAN          11000             .3Zlotkey                   SA_MAN          10500             .2Tucker                    SA_REP          10000             .3Bernstein                 SA_REP           9500            .25Hall                      SA_REP           9000            .25Olsen                     SA_REP           8000             .2Cambrault                 SA_REP           7500             .2Tuvault                   SA_REP           7000            .15King                      SA_REP          10000            .35LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Sully                     SA_REP           9500            .35McEwen                    SA_REP           9000            .35Smith                     SA_REP           8000             .3Doran                     SA_REP           7500             .3Sewall                    SA_REP           7000            .25Vishney                   SA_REP          10500            .25Greene                    SA_REP           9500            .15Marvins                   SA_REP           7200             .1Lee                       SA_REP           6800             .1Ande                      SA_REP           6400             .1Banda                     SA_REP           6200             .1LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Ozer                      SA_REP          11500            .25Bloom                     SA_REP          10000             .2Fox                       SA_REP           9600             .2Smith                     SA_REP           7400            .15Bates                     SA_REP           7300            .15Kumar                     SA_REP           6100             .1Abel                      SA_REP          11000             .3Hutton                    SA_REP           8800            .25Taylor                    SA_REP           8600             .2Livingston                SA_REP           8400             .2Grant                     SA_REP           7000            .15LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Johnson                   SA_REP           6200             .1Taylor                    SH_CLERK         3200Fleaur                    SH_CLERK         3100Sullivan                  SH_CLERK         2500Geoni                     SH_CLERK         2800Sarchand                  SH_CLERK         4200Bull                      SH_CLERK         4100Dellinger                 SH_CLERK         3400Cabrio                    SH_CLERK         3000Chung                     SH_CLERK         3800Dilly                     SH_CLERK         3600LAST_NAME                 JOB_ID         SALARY COMMISSION_PCT------------------------- ---------- ---------- --------------Gates                     SH_CLERK         2900Perkins                   SH_CLERK         2500Bell                      SH_CLERK         4000Everett                   SH_CLERK         3900McCain                    SH_CLERK         3200Jones                     SH_CLERK         2800Walsh                     SH_CLERK         3100Feeney                    SH_CLERK         3000107 rows selected.

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to null.(只要表达式里面的值有NULL存在，整个表达式的值都将为NULL;但是NULL与字符串合并的时候，合并后的值为字符串本身;)

SQL> SELECT last_name,12*salary*commission_pct FROM employees;LAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------OConnellGrantWhalenHartsteinFayMavrisBaerHigginsGietzKingKochharLAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------De HaanHunoldErnstAustinPataballaLorentzGreenbergFavietChenSciarraUrmanLAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------PoppRaphaelyKhooBaidaTobiasHimuroColmenaresWeissFrippKauflingVollmanLAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------MourgosNayerMikkilineniLandryMarkleBissotAtkinsonMarlowOlsonMallinRogersLAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------GeePhiltankerLadwigStilesSeoPatelRajsDaviesMatosVargasRussell                                      67200LAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------Partners                                     48600Errazuriz                                    43200Cambrault                                    39600Zlotkey                                      25200Tucker                                       36000Bernstein                                    28500Hall                                         27000Olsen                                        19200Cambrault                                    18000Tuvault                                      12600King                                         42000LAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------Sully                                        39900McEwen                                       37800Smith                                        28800Doran                                        27000Sewall                                       21000Vishney                                      31500Greene                                       17100Marvins                                       8640Lee                                           8160Ande                                          7680Banda                                         7440LAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------Ozer                                         34500Bloom                                        24000Fox                                          23040Smith                                        13320Bates                                        13140Kumar                                         7320Abel                                         39600Hutton                                       26400Taylor                                       20640Livingston                                   20160Grant                                        12600LAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------Johnson                                       7440TaylorFleaurSullivanGeoniSarchandBullDellingerCabrioChungDillyLAST_NAME                 12*SALARY*COMMISSION_PCT------------------------- ------------------------GatesPerkinsBellEverettMcCainJonesWalshFeeney107 rows selected.

如果在算术表达式里面，存在NULL值，那么整个算术表达式的值将为NULL;

Defining a Column Alias

• A column alias:
• Renamees a column heading.
• Is usefull with alculations.
• Immediately follows the column name(There can also be the options AS keyword between the column name and alias.)
• Requires double quotation marks if it contains spaces or specila characters,or if it is case-sensitive.

Using Column Aliases

SQL> SELECT last_name AS name,commission_pct comm FROM employees;NAME                            COMM------------------------- ----------OConnellGrantWhalenHartsteinFayMavrisBaerHigginsGietzKingKochharNAME                            COMM------------------------- ----------De HaanHunoldErnstAustinPataballaLorentzGreenbergFavietChenSciarraUrmanNAME                            COMM------------------------- ----------PoppRaphaelyKhooBaidaTobiasHimuroColmenaresWeissFrippKauflingVollmanNAME                            COMM------------------------- ----------MourgosNayerMikkilineniLandryMarkleBissotAtkinsonMarlowOlsonMallinRogersNAME                            COMM------------------------- ----------GeePhiltankerLadwigStilesSeoPatelRajsDaviesMatosVargasRussell                           .4NAME                            COMM------------------------- ----------Partners                          .3Errazuriz                         .3Cambrault                         .3Zlotkey                           .2Tucker                            .3Bernstein                        .25Hall                             .25Olsen                             .2Cambrault                         .2Tuvault                          .15King                             .35NAME                            COMM------------------------- ----------Sully                            .35McEwen                           .35Smith                             .3Doran                             .3Sewall                           .25Vishney                          .25Greene                           .15Marvins                           .1Lee                               .1Ande                              .1Banda                             .1NAME                            COMM------------------------- ----------Ozer                             .25Bloom                             .2Fox                               .2Smith                            .15Bates                            .15Kumar                             .1Abel                              .3Hutton                           .25Taylor                            .2Livingston                        .2Grant                            .15NAME                            COMM------------------------- ----------Johnson                           .1TaylorFleaurSullivanGeoniSarchandBullDellingerCabrioChungDillyNAME                            COMM------------------------- ----------GatesPerkinsBellEverettMcCainJonesWalshFeeney107 rows selected.

SQL> SELECT last_name "Name",salary*12 "Annual Salary" FROM employees;Name                      Annual Salary------------------------- -------------OConnell                          31200Grant                             31200Whalen                            52800Hartstein                        156000Fay                               72000Mavris                            78000Baer                             120000Higgins                          144096Gietz                             99600King                             288000Kochhar                          204000Name                      Annual Salary------------------------- -------------De Haan                          204000Hunold                           108000Ernst                             72000Austin                            57600Pataballa                         57600Lorentz                           50400Greenberg                        144096Faviet                           108000Chen                              98400Sciarra                           92400Urman                             93600Name                      Annual Salary------------------------- -------------Popp                              82800Raphaely                         132000Khoo                              37200Baida                             34800Tobias                            33600Himuro                            31200Colmenares                        30000Weiss                             96000Fripp                             98400Kaufling                          94800Vollman                           78000Name                      Annual Salary------------------------- -------------Mourgos                           69600Nayer                             38400Mikkilineni                       32400Landry                            28800Markle                            26400Bissot                            39600Atkinson                          33600Marlow                            30000Olson                             25200Mallin                            39600Rogers                            34800Name                      Annual Salary------------------------- -------------Gee                               28800Philtanker                        26400Ladwig                            43200Stiles                            38400Seo                               32400Patel                             30000Rajs                              42000Davies                            37200Matos                             31200Vargas                            30000Russell                          168000Name                      Annual Salary------------------------- -------------Partners                         162000Errazuriz                        144000Cambrault                        132000Zlotkey                          126000Tucker                           120000Bernstein                        114000Hall                             108000Olsen                             96000Cambrault                         90000Tuvault                           84000King                             120000Name                      Annual Salary------------------------- -------------Sully                            114000McEwen                           108000Smith                             96000Doran                             90000Sewall                            84000Vishney                          126000Greene                           114000Marvins                           86400Lee                               81600Ande                              76800Banda                             74400Name                      Annual Salary------------------------- -------------Ozer                             138000Bloom                            120000Fox                              115200Smith                             88800Bates                             87600Kumar                             73200Abel                             132000Hutton                           105600Taylor                           103200Livingston                       100800Grant                             84000Name                      Annual Salary------------------------- -------------Johnson                           74400Taylor                            38400Fleaur                            37200Sullivan                          30000Geoni                             33600Sarchand                          50400Bull                              49200Dellinger                         40800Cabrio                            36000Chung                             45600Dilly                             43200Name                      Annual Salary------------------------- -------------Gates                             34800Perkins                           30000Bell                              48000Everett                           46800McCain                            38400Jones                             33600Walsh                             37200Feeney                            36000107 rows selected.

Concatenation Operator

A concatenation operator:

• Links coluns or character strings to other columns
• Is represented by two vertical bars(||)
• Creates a resultant column that is a character expression.

SQL> SELECT last_name || job_id AS "Employees" FROM employees;Employees-----------------------------------AbelSA_REPAndeSA_REPAtkinsonST_CLERKAustinIT_PROGBaerPR_REPBaidaPU_CLERKBandaSA_REPBatesSA_REPBellSH_CLERKBernsteinSA_REPBissotST_CLERKEmployees-----------------------------------BloomSA_REPBullSH_CLERKCabrioSH_CLERKCambraultSA_MANCambraultSA_REPChenFI_ACCOUNTChungSH_CLERKColmenaresPU_CLERKDaviesST_CLERKDe HaanAD_VPDellingerSH_CLERKEmployees-----------------------------------DillySH_CLERKDoranSA_REPErnstIT_PROGErrazurizSA_MANEverettSH_CLERKFavietFI_ACCOUNTFayMK_REPFeeneySH_CLERKFleaurSH_CLERKFoxSA_REPFrippST_MANEmployees-----------------------------------GatesSH_CLERKGeeST_CLERKGeoniSH_CLERKGietzAC_ACCOUNTGrantSH_CLERKGrantSA_REPGreenbergFI_MGRGreeneSA_REPHallSA_REPHartsteinMK_MANHigginsAC_MGREmployees-----------------------------------HimuroPU_CLERKHunoldIT_PROGHuttonSA_REPJohnsonSA_REPJonesSH_CLERKKauflingST_MANKhooPU_CLERKKingSA_REPKingAD_PRESKochharAD_VPKumarSA_REPEmployees-----------------------------------LadwigST_CLERKLandryST_CLERKLeeSA_REPLivingstonSA_REPLorentzIT_PROGMallinST_CLERKMarkleST_CLERKMarlowST_CLERKMarvinsSA_REPMatosST_CLERKMavrisHR_REPEmployees-----------------------------------McCainSH_CLERKMcEwenSA_REPMikkilineniST_CLERKMourgosST_MANNayerST_CLERKOConnellSH_CLERKOlsenSA_REPOlsonST_CLERKOzerSA_REPPartnersSA_MANPataballaIT_PROGEmployees-----------------------------------PatelST_CLERKPerkinsSH_CLERKPhiltankerST_CLERKPoppFI_ACCOUNTRajsST_CLERKRaphaelyPU_MANRogersST_CLERKRussellSA_MANSarchandSH_CLERKSciarraFI_ACCOUNTSeoST_CLERKEmployees-----------------------------------SewallSA_REPSmithSA_REPSmithSA_REPStilesST_CLERKSullivanSH_CLERKSullySA_REPTaylorSA_REPTaylorSH_CLERKTobiasPU_CLERKTuckerSA_REPTuvaultSA_REPEmployees-----------------------------------UrmanFI_ACCOUNTVargasST_CLERKVishneySA_REPVollmanST_MANWalshSH_CLERKWeissST_MANWhalenAD_ASSTZlotkeySA_MAN107 rows selected.

Literal Character Strings

• A literal is a character,a number,or a date that is included in the SELECT statement.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output onec for each row returned. 

Using Literal Character Strings

SQL> SELECT last_name || ' is a ' || job_id FROM employees;LAST_NAME||'ISA'||JOB_ID-----------------------------------------Abel is a SA_REPAnde is a SA_REPAtkinson is a ST_CLERKAustin is a IT_PROGBaer is a PR_REPBaida is a PU_CLERKBanda is a SA_REPBates is a SA_REPBell is a SH_CLERKBernstein is a SA_REPBissot is a ST_CLERKLAST_NAME||'ISA'||JOB_ID-----------------------------------------Bloom is a SA_REPBull is a SH_CLERKCabrio is a SH_CLERKCambrault is a SA_MANCambrault is a SA_REPChen is a FI_ACCOUNTChung is a SH_CLERKColmenares is a PU_CLERKDavies is a ST_CLERKDe Haan is a AD_VPDellinger is a SH_CLERKLAST_NAME||'ISA'||JOB_ID-----------------------------------------Dilly is a SH_CLERKDoran is a SA_REPErnst is a IT_PROGErrazuriz is a SA_MANEverett is a SH_CLERKFaviet is a FI_ACCOUNTFay is a MK_REPFeeney is a SH_CLERKFleaur is a SH_CLERKFox is a SA_REPFripp is a ST_MANLAST_NAME||'ISA'||JOB_ID-----------------------------------------Gates is a SH_CLERKGee is a ST_CLERKGeoni is a SH_CLERKGietz is a AC_ACCOUNTGrant is a SH_CLERKGrant is a SA_REPGreenberg is a FI_MGRGreene is a SA_REPHall is a SA_REPHartstein is a MK_MANHiggins is a AC_MGRLAST_NAME||'ISA'||JOB_ID-----------------------------------------Himuro is a PU_CLERKHunold is a IT_PROGHutton is a SA_REPJohnson is a SA_REPJones is a SH_CLERKKaufling is a ST_MANKhoo is a PU_CLERKKing is a SA_REPKing is a AD_PRESKochhar is a AD_VPKumar is a SA_REPLAST_NAME||'ISA'||JOB_ID-----------------------------------------Ladwig is a ST_CLERKLandry is a ST_CLERKLee is a SA_REPLivingston is a SA_REPLorentz is a IT_PROGMallin is a ST_CLERKMarkle is a ST_CLERKMarlow is a ST_CLERKMarvins is a SA_REPMatos is a ST_CLERKMavris is a HR_REPLAST_NAME||'ISA'||JOB_ID-----------------------------------------McCain is a SH_CLERKMcEwen is a SA_REPMikkilineni is a ST_CLERKMourgos is a ST_MANNayer is a ST_CLERKOConnell is a SH_CLERKOlsen is a SA_REPOlson is a ST_CLERKOzer is a SA_REPPartners is a SA_MANPataballa is a IT_PROGLAST_NAME||'ISA'||JOB_ID-----------------------------------------Patel is a ST_CLERKPerkins is a SH_CLERKPhiltanker is a ST_CLERKPopp is a FI_ACCOUNTRajs is a ST_CLERKRaphaely is a PU_MANRogers is a ST_CLERKRussell is a SA_MANSarchand is a SH_CLERKSciarra is a FI_ACCOUNTSeo is a ST_CLERKLAST_NAME||'ISA'||JOB_ID-----------------------------------------Sewall is a SA_REPSmith is a SA_REPSmith is a SA_REPStiles is a ST_CLERKSullivan is a SH_CLERKSully is a SA_REPTaylor is a SA_REPTaylor is a SH_CLERKTobias is a PU_CLERKTucker is a SA_REPTuvault is a SA_REPLAST_NAME||'ISA'||JOB_ID-----------------------------------------Urman is a FI_ACCOUNTVargas is a ST_CLERKVishney is a SA_REPVollman is a ST_MANWalsh is a SH_CLERKWeiss is a ST_MANWhalen is a AD_ASSTZlotkey is a SA_MAN107 rows selected.

Alternative Quote(q) Operator

• Specify your own quotation mark delimiter.
• Select any delimiter.
• Increase readability and useability.

SQL> SELECT department_name || ' Department' || q'[`s Manager Id is ]' ||  manager_id  AS "Department and Manager" FROM departments;Department and Manager--------------------------------------------------------------------------------Administration Department`s Manager Id is 200Marketing Department`s Manager Id is 201Purchasing Department`s Manager Id is 114Human Resources Department`s Manager Id is 203Shipping Department`s Manager Id is 121IT Department`s Manager Id is 103Public Relations Department`s Manager Id is 204Sales Department`s Manager Id is 145Executive Department`s Manager Id is 100Finance Department`s Manager Id is 108Accounting Department`s Manager Id is 205Department and Manager--------------------------------------------------------------------------------Treasury Department`s Manager Id isCorporate Tax Department`s Manager Id isControl And Credit Department`s Manager Id isShareholder Services Department`s Manager Id isBenefits Department`s Manager Id isManufacturing Department`s Manager Id isConstruction Department`s Manager Id isContracting Department`s Manager Id isOperations Department`s Manager Id isIT Support Department`s Manager Id isNOC Department`s Manager Id isDepartment and Manager--------------------------------------------------------------------------------IT Helpdesk Department`s Manager Id isGovernment Sales Department`s Manager Id isRetail Sales Department`s Manager Id isRecruiting Department`s Manager Id isPayroll Department`s Manager Id is27 rows selected.

Duplicate Rows

The default display of queries is all rows,including duplicate rows.

SQL> SELECT department_id FROM employees;DEPARTMENT_ID-------------           50           50           10           20           20           40           70          110          110           90           90DEPARTMENT_ID-------------           90           60           60           60           60           60          100          100          100          100          100DEPARTMENT_ID-------------          100           30           30           30           30           30           30           50           50           50           50DEPARTMENT_ID-------------           50           50           50           50           50           50           50           50           50           50           50DEPARTMENT_ID-------------           50           50           50           50           50           50           50           50           50           50           80DEPARTMENT_ID-------------           80           80           80           80           80           80           80           80           80           80           80DEPARTMENT_ID-------------           80           80           80           80           80           80           80           80           80           80           80DEPARTMENT_ID-------------           80           80           80           80           80           80           80           80           80           80DEPARTMENT_ID-------------           80           50           50           50           50           50           50           50           50           50           50DEPARTMENT_ID-------------           50           50           50           50           50           50           50           50107 rows selected.SQL> SELECT DISTINCT department_id FROM employees;DEPARTMENT_ID-------------          100           30           20           70           90          110           50           40           80           10DEPARTMENT_ID-------------           6012 rows selected.

只要使用了DISTICT keyword，那么DISTINCT后面的所有列都会受此影响，而不是距离DISTINCT keyword最近的那一列.

Displaying the Table Structure

• Use the DESCRIBE command to display the structure of a table
• Or,select the table in the Connections tree and use the Columns to view the table structure.

DESC[RIBE] tablename;

DESC不是SQL的命令，而是SQL*Plus的命令.

DEMO-01:查看表的基本结构信息

SQL> desc employees; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID                               NOT NULL NUMBER(6) FIRST_NAME                                         VARCHAR2(20) LAST_NAME                                 NOT NULL VARCHAR2(25) EMAIL                                     NOT NULL VARCHAR2(25) PHONE_NUMBER                                       VARCHAR2(20) HIRE_DATE                                 NOT NULL DATE JOB_ID                                    NOT NULL VARCHAR2(10) SALARY                                             NUMBER(8,2) COMMISSION_PCT                                     NUMBER(2,2) MANAGER_ID                                         NUMBER(6) DEPARTMENT_ID                                      NUMBER(4)

DEMO-02:查看表的详细结构信息

SQL> SET LONG 9999SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES') FROM DUAL;DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES')--------------------------------------------------------------------------------  CREATE TABLE "HR"."EMPLOYEES"   (    "EMPLOYEE_ID" NUMBER(6,0),        "FIRST_NAME" VARCHAR2(20),        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,        "PHONE_NUMBER" VARCHAR2(20),        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,        "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,        "SALARY" NUMBER(8,2),        "COMMISSION_PCT" NUMBER(2,2),DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES')--------------------------------------------------------------------------------        "MANAGER_ID" NUMBER(6,0),        "DEPARTMENT_ID" NUMBER(4,0),         CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "EXAMPLE"  ENABLE,         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICSDBMS_METADATA.GET_DDL('TABLE','EMPLOYEES')--------------------------------------------------------------------------------  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "EXAMPLE"  ENABLE,         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,         CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE   ) SEGMENT CREATION IMMEDIATEDBMS_METADATA.GET_DDL('TABLE','EMPLOYEES')--------------------------------------------------------------------------------  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "EXAMPLE"

Summary

In this lesson,you should have learned how to:

• Write a SELECT statement that:
  • -Returns all rows and columns from a table.
  • -Returns specified columns form a table.
  • -Uses column aliases to display more descriptive column headings

SELECT *|{
   DISTINCT column|expression [alias],...} FROM table;