Introduction to Oracle SQL - Speaker notes

Slide 1:

This is an introduction to Oracle that works with the text introduction available at the Web site for CIS50.

Slide 2:

Note that in the introductory notes, there are only three records in the donor table. In this example, I have added three more records for a total of six records.

Slide 3:

SQL is not case sensitive and the SELECT statement could be written on one line or two.

Coding the SQL words in upper case letters and the names of columns and tables etc. in lower case letters is a common convention.

Additional information is given on the notes Introduction to Oracle - SQL.

Slide 4:

The SELECT statement is written at the SQL prompt. Note that after writing the first line of the SELECT, the user should press ENTERand after writing the second line which in this case includes the semi-colon, they should also press ENTER. The code will then be executed.

When the output is shown it includes the number of rows selected. Since there are 6 records/rows in the current donor table, the information from 6 records/rows is shown followed by the statement 6 rows selected.

The SQL> is the SQL prompt which is waiting for the next command.

Slide 5:

Column headers display in upper case with the hyphenated line beneath by default.

Slide 6:

Note the optional AS:

SELECT idno AS ID, name, stadr AS STREET

FROM donor;

In this case ID will print over the idno column, the default NAME will print over the name column and STREET will print over the stadr column.

Slide 7:

Note that IDNO and DRIVE are character fields, CONTDATE is a date type filed and CONTAMT is numeric.

Slide 8:

Operation Process + Add - Subtract * Multiply / Divide

Note the other arithmetic operators.

Slide 9:

Read more about NULL values in the notes and experiment with them on your own.

The calculation here is to add 50 to the yearly goal and devide it by 4 to come up with the quarterly goal. The column alias is used for the column that contains the calculation.

Slide 10:

Concatenation strings columns or character strings together as a character expression. In Oracle, the concatenation operator is two vertical bars ||.

Slide 11:

DISTINCT allows you to see each contact person only once. You cannot combine DISTINCT with data that appears on individual records because DISTINCT is only showing duplicate data once.

Slide 12:

We are no adding a clause to the SELECT. The SELECT is very powerful because of the number and type of clauses that can be incorporated into the command.

Even though ascending order is the default, you can use ASC after the name your are sorting on:

SELECT idno, name, yrgoal

FROM donor

ORDER BY yrgoal ASC;

Slide 13:

When you order by multiple fields but the field which represents the primary sort first in the ORDER BY list.

Note that each field/column can have its own order and that the fields/columns can also be the results of calculations as opposed to fields that exist on the table.

Check notes for more examples.