Introduction to Oracle - SQL

In introducing SQL, I will be using three tables that form a contribution system. The first table is donor, the second is donation and the third is drive:

Donor (idno, name, stadr, city, state, zip, datefst, yrgoal, contact)
Donation (idno, driveno, datecont, amtcont)
Drive (driveno, drname, drchair, goal, ytdtotal, lastyrtot)

SELECT:

The SELECT statement can be used to obtain information from a table. The select can retrieve selected rows, specified columns or you can join information from two or more tables using a link to specified columns. Selection is the name given to selecting rows based on specified criteria. Projection is the name given to specifying certain columns for your query. Join is the name given to linking multiple tables through common columns.

The SELECT statement must include a clause which tells what columns to show and a from clause that designates the table to be used. A where clause can be used to specify a condition that results in only selected rows being displayed. SQL is not case sensitive and the SQL statement can be written on one or many lines. Frequently lines and indenting of clauses are used to enhance readability.

SELECT {columns}
FROM {table};

Example: I want to select all columns from the table called donor. To select all columns you can put an * after the select statement. I can code on one line or two as shown.

SELECT *
FROM donor;

SQL CODE:

SQL> select * from donor;

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       500 John Smith
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       400 Susan Jones
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98       400 Susan Jones

SQL>

After keying the semi-colon the user can press enter to execute. You can also execute a statement by keying a slash at the SQL prompt or issuing the RUN command at the SQL prompt.

To specify certain fields for selection, you list the fields by name separated by a comma. The order that you use in listing determines the order in the results. For example: idno, name means idno lists first while name, idno means name lists first.

SELECT idno, name, city
FROM donor;

SQL CODE:

SQL> select idno, name, city from donor;

IDNO  NAME            CITY
----- --------------- ----------
11111 Stephen Daniels Seekonk
12121 Jennifer Ames   Providence
22222 Carl Hersey     Providence

SQL>

When showing columns, numeric data is justified right and date and character data are justified to the left. Justification applies to both the data and the column headings. The default for column headings is the name of the field displayed in upper case. The developer may specify a column alias which will serve as the column heading. For example, this would show the headings as ID, NAME, STREET instead of IDNO, NAME, STADR. The AS is optional.

SELECT idno AS ID, name, stadr AS STREET
FROM donor;

SELECT idno ID, name, stradr STREET
FROM donor;

SQL CODE:

SQL> select idno AS ID, name, stadr AS STREET
  2  FROM donor;

ID    NAME            STREET
----- --------------- ---------------
11111 Stephen Daniels 123 Elm St
12121 Jennifer Ames   24 Benefit St
22222 Carl Hersey     24 Benefit St

You can also specify a multiword heading or a mixed case heading by enclosing it in double quotes:

SELECT idno "ID #", name "Name", stadr "Street Adr"
FROM donor;

SQL CODE:

SQL> SELECT idno "ID #", name "Name", stadr "Street Adr"
  2  FROM donor;

ID #  Name            Street Adr
----- --------------- ---------------
11111 Stephen Daniels 123 Elm St
12121 Jennifer Ames   24 Benefit St
22222 Carl Hersey     24 Benefit St

SQL>

The SELECT statement can also include Arithmetic expressions:

Operator	Process
+	Add
-	Subtraction
*	Multiplication
/	Division

One of the fields on the donor field is yrgoal. If I want to display a column with monthly goal I would divide by 12. The resulting header would be YRGOAL/12.

SELECT idno, name, yrgoal/12
FROM donor;

SQL CODE:

SQL> SELECT idno, name, yrgoal/12
  2  FROM donor;

IDNO  NAME            YRGOAL/12
----- --------------- ---------
11111 Stephen Daniels 41.666667
12121 Jennifer Ames   33.333333
22222 Carl Hersey     33.333333

SQL>

If I want the header to say Month Goal then I would include the following:

SELECT idno, name, yrgoal/12 "Month Goal"
FROM donor;

Calculations follow the standard order of operation:

Evaluation works from left to right
Multiplication and division first
Addition and subtraction second
Parentheses are evaluated/resolved first (use to change order of operation)

For example: If you want to add 100 to the yrgoal and then divide by 12 you use the parenthesis to do the addition before the division.

SELECT idno, name, (yrgoal + 100)/12 "New Month Goal"
FROM donor;

SQL CODE:

SQL> 1  SELECT idno, name, (yrgoal + 100)/12 "New Month Goal"
  2* FROM donor
SQL> /

IDNO  NAME            New Month Goal
----- --------------- --------------
11111 Stephen Daniels             50
12121 Jennifer Ames        41.666667
22222 Carl Hersey          41.666667

Note that in the example above there is a / at the SQL prompt. This is because I edited the select statement in notepad and then executed it when I returned. The / causes the statement to be executed.

NULL values are values that are unassigned. A null value should not be confused with a zero which is a number or a space which is a character. Null means there is not a data value for a particular field. When a table is created the user can specify that a field cannot be null. Note also that a field defined as a primary key can not be null. When an arithmetic expression contains a null value, the result of the calculation evaluates to null. Looking at the example of dividing yrgoal by 12, any donor with a null value in yrgoal would have a null value as their monthly goal.

SELECT idno, yrgoal, yrgoal/12
FROM donor;

SQL CODE:


SQL> SELECT idno, yrgoal, yrgoal/12
  2  FROM donor;

IDNO     YRGOAL YRGOAL/12
----- --------- ---------
11111       500 41.666667
12121       400 33.333333
22222

CONCATENATION strings columns or character strings together as a character expression. The concatenation operator is two vertical bars ||.

If the city was PROVIDENCE and the state was RI:
SELECT city || state would result in PROVIDENCERI while
SELECT city || ', ' || state would result in PROVIDENCE, RI

SQL CODE:

SQL> SELECT city || state
  2  FROM donor;

CITY||STATE
------------
SeekonkMA
ProvidenceRI
ProvidenceRI

SQL>

SQL> SELECT city || ', ' || state
  2  FROM donor;

CITY||','||STA
--------------
Seekonk, MA
Providence, RI
Providence, RI

Another example:

SELECT name || ' has a yearly goal of ' || yrgoal
FROM donor;

In this case, notice that there is a space after the quote so that there will be a space between name and has and there is a space before the quote which means there will be a space between of and the yearly goal.

For Stephen Daniels with a goal of 500 you would see:
Stephen Daniels has a yearly goal of 500.

SQL CODE:

1  SELECT name || ' has a yearly goal of ' || yrgoal
2* FROM donor
SQL> /

NAME||'HASAYEARLYGOALOF'||YRGOAL
--------------------------------------------
Stephen Daniels has a yearly goal of 500
Jennifer Ames has a yearly goal of 400
Carl Hersey has a yearly goal of 400

If you want to eliminate duplicate rows, you can use the DISTINCT keyword. For example, if you want to see the contacts but you only want to see each contact once, you would use the following:

SELECT DISTINCT contact
FROM donor;

SQL CODE:

SQL> SELECT DISTINCT contact
  2  FROM donor;

CONTACT
------------
John Smith
Susan Jones

SQL>

SORT:

The ORDER BY clause is used to SORT rows in a specified order. Ascending order is the default (it can be indicated by ASC after the column name you are sorting on). If you want to sort in descending order, the DESC clause is placed after the column name you are sorting on. In the example below I have chosen to sort by datefst. Notice that it is in ascending order since that is the default.

SQL CODE:

SQL> SELECT * 
  2  FROM donor
  3  ORDER BY datefst;

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
23456 Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92       100 Amy Costa
33333 Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92        50 John Adams
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       400 Susan Jones
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98           Susan Jones
34567 Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98        50 Amy Costa
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       500 John Smith

6 rows selected.

Specifying DESC after datefst caused the sort to be done in descending order.

SQL CODE:

SQL> SELECT *
  2  FROM donor
  3  ORDER BY datefst DESC;

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       500 John Smith
34567 Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98        50 Amy Costa
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98           Susan Jones
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       400 Susan Jones
23456 Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92       100 Amy Costa
33333 Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92        50 John Adams

6 rows selected.

In the example below the sort is being done on two fields. The field that is listed first is the primary sort field and other fields are secondary. If there are multiple secondary the order is from left to right. The primary sort is by state and since no order was given it is sorted in the default which is ascending order. The secondary sort is on datefst and this sort is done in descending order. Essentially the rows are shown in order by state and datefst within state.

SQL CODE:

SQL> SELECT * 
  2  FROM donor
  3  ORDER by state, datefst DESC;

IDNO  NAME            STADR           CITY       ST ZIP   DATEFST      YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St      Seekonk    MA 02345 03-JUL-98       500 John Smith
34567 Robert Brooks   36 Pine St      Fall River MA 02720 04-APR-98        50 Amy Costa
23456 Susan Ash       21 Main St      Fall River MA 02720 04-MAR-92       100 Amy Costa
33333 Nancy Taylor    26 Oak St       Fall River MA 02720 04-MAR-92        50 John Adams
22222 Carl Hersey     24 Benefit St   Providence RI 02045 03-JAN-98           Susan Jones
12121 Jennifer Ames   24 Benefit St   Providence RI 02045 24-MAY-97       400 Susan Jones

6 rows selected.

SQL>

This sort is done on state and then on yrgoal divided by 12 within state. Since no order is specified for either sort, everything is in ascending order.

SQL CODE:

SQL> SELECT name, city, state, yrgoal/12 "Month Goal"
  2  FROM donor
  3  ORDER BY state, yrgoal/12;

NAME            CITY       ST Month Goal
--------------- ---------- -- ----------
Nancy Taylor    Fall River MA  4.1666667
Robert Brooks   Fall River MA  4.1666667
Susan Ash       Fall River MA  8.3333333
Stephen Daniels Seekonk    MA  41.666667
Jennifer Ames   Providence RI  33.333333
Carl Hersey     Providence RI

6 rows selected.

This sort is very similiar to the one above. However instead of using the formula in the ORDER BY, I used the alias that was assigned and I sorted the monthly goal in descending order.

SQL CODE:

SQL> SELECT name, city, state, yrgoal/12 month
  2  FROM donor
  3  ORDER BY state, month DESC;

NAME            CITY       ST     MONTH
--------------- ---------- -- ---------
Stephen Daniels Seekonk    MA 41.666667
Susan Ash       Fall River MA 8.3333333
Nancy Taylor    Fall River MA 4.1666667
Robert Brooks   Fall River MA 4.1666667
Carl Hersey     Providence RI
Jennifer Ames   Providence RI 33.333333

6 rows selected.