Example 1: Using the SELECT statement
The following SELECT statement returns all rows from the
EMPLOYEES table in no specific order:
SQL> SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL FROM EMPLOYEES;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
Toliver Alvin A
Smith Terry D
Dietrich Rick NULL
Kilpatrick Janet NULL
.
.
.
100 rows selected
Example 2: Adding an ORDER BY clause to sort rows selected
An ORDER BY clause added to the same SELECT statement causes SQL
to sort the rows according to the LAST_NAME column.
SQL> SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL FROM
cont> EMPLOYEES ORDER BY LAST_NAME;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
Ames Louie A
Andriola Leslie Q
Babbin Joseph Y
Bartlett Dean G
Bartlett Wes NULL
.
.
.
100 rows selected
Example 3: Adding a LIMIT TO clause to return a certain number of
rows
The same SELECT statement with both an ORDER BY clause and a
LIMIT TO clause causes SQL to:
1. Sort all the rows of the EMPLOYEES table according to the
LAST_NAME column
2. Return the first five rows in the ordered set
SQL> SELECT LAST_NAME, FIRST_NAME, MIDDLE_INITIAL FROM
cont> EMPLOYEES ORDER BY LAST_NAME LIMIT TO 5 ROWS;
LAST_NAME FIRST_NAME MIDDLE_INITIAL
Ames Louie A
Andriola Leslie Q
Babbin Joseph Y
Bartlett Dean G
Bartlett Wes NULL
5 rows selected
Example 4: Using the optimize clause to specify an outline and a
query name
The following select query uses a previously defined outline
called WOMENS_DEGREES and also names the query. The RDMS$DEBUG_
FLAGS logical has been set to "Ss":
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE,
cont> D.DEGREE_FIELD, D.YEAR_GIVEN
cont> FROM EMPLOYEES E, DEGREES D
cont> WHERE E.SEX = 'F'
cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID
cont> ORDER BY LAST_NAME
cont> OPTIMIZE USING WOMENS_DEGREES
cont> AS WOMENS_DEGREES;
~Query Name : WOMENS_DEGREES
~S: Outline WOMENS_DEGREES used
Sort
Cross block of 2 entries
Cross block entry 1
Conjunct Get Retrieval by index of relation EMPLOYEES
Index name EMP_EMPLOYEE_ID [0:0]
Cross block entry 2
Leaf#01 BgrOnly DEGREES Card=165
BgrNdx1 DEG_EMP_ID [1:1] Fan=17
-- Rdb Generated Outline : 16-JUN-1994 11:01
create outline WOMENS_DEGREES
id 'D3A5BC351F507FED820EB704FC3F61E8'
mode 0
as (
query (
subquery (
EMPLOYEES 0 access path index EMP_EMPLOYEE_ID
join by cross to
DEGREES 1 access path index DEG_EMP_ID
)
)
)
compliance optional ;
E.LAST_NAME E.EMPLOYEE_ID D.DEGREE D.DEGREE_FIELD D.YEAR_GIVEN
Boyd 00244 MA Elect. Engrg. 1982
Boyd 00244 PhD Applied Math 1979
Brown 00287 BA Arts 1982
Brown 00287 MA Applied Math 1979
Clarke 00188 BA Arts 1983
Clarke 00188 MA Applied Math 1976
Clarke 00196 BA Arts 1978
.
.
.
61 rows selected
Example 5: Associating an Edit String with a Value Expression
SQL> CREATE DOMAIN MONEY INTEGER(2)
cont> EDIT STRING '$$$,$$$,$$9.99';
SQL> --Calculate the average salary for all current jobs.
SQL> SELECT EMPLOYEE_ID,
cont> AVG(SALARY_AMOUNT) AS AVERAGE EDIT USING MONEY,
cont> MAX(SALARY_AMOUNT) AS MAXIMUM EDIT USING MONEY,
cont> MAX(SALARY_START) AS START_DATE EDIT USING 'YYYBDDBMMMBWWW'
cont> FROM SALARY_HISTORY
cont> WHERE SALARY_END IS NULL
cont> GROUP BY EMPLOYEE_ID;
EMPLOYEE_ID AVERAGE MAXIMUM START_DATE
00164 $51,712.00 $51,712.00 983 14 Jan Fri
00165 $11,676.00 $11,676.00 982 1 Jul Thu
00166 $18,497.00 $18,497.00 982 7 Aug Sat
00167 $17,510.00 $17,510.00 982 21 Aug Sat
.
.
.
00435 $84,147.00 $84,147.00 982 12 Mar Fri
00471 $52,000.00 $52,000.00 982 15 Aug Sun
100 rows selected
Example 6: Using the ORDER BY Clause with a Value Expression
SQL> SELECT * FROM EMPLOYEES
cont> ORDER BY EXTRACT (YEAR FROM BIRTHDAY),
cont> TRIM(FIRST_NAME) || TRIM(LAST_NAME);
00190 O'Sullivan Rick G.
78 Mason Rd. NULL Fremont
NH 03044 M 12-Jan-1923 1 None
00231 Clairmont Rick NULL
92 Madiso7 Drive NULL Chocorua
NH 03817 M 23-Dec-1924 2 None
00183 Nash Walter V.
197 Lantern Lane NULL Fremont
NH 03044 M 19-Jan-1925 1 None
00177 Kinmonth Louis NULL
76 Maple St. NULL Etna
NH 03750 M 7-Apr-1926 1 None
00240 Johnson Bill R.
20 South St NULL Milford
NH 03055 M 13-Apr-1927 2 None
.
.
.
Example 7: Using the GROUP BY Clause with a Value Expression
SQL> SELECT COUNT (*), EXTRACT (YEAR FROM BIRTHDAY)
cont> FROM EMPLOYEES
cont> GROUP BY EXTRACT (YEAR FROM BIRTHDAY);
1 1923
1 1924
1 1925
1 1926
4 1927
2 1928
1 1930
2 1931
.
.
.
Example 8: Performing an Outer Join with Oracle Server Style
Syntax
SQL> SELECT EMPLOYEES.EMPLOYEE_ID, JOB_CODE
cont> FROM EMPLOYEES, CURRENT_JOB
cont> WHERE EMPLOYEES.EMPLOYEE_ID= CURRENT_JOB.EMPLOYEE_ID(+);
EMPLOYEES.EMPLOYEE_ID CURRENT_JOB.JOB_CODE
00164 DMGR
00165 ASCK
00166 DMGR
00167 APGM
00168 DMGR
00169 SPGM
00170 SCTR
00171 PRGM
.
.
.