Introduction to Oracle SQL

I was a teaching assistant for a series of Oracle-based database courses at Temple University, so I had to prepare SQL scripts to illustrate the concepts I was explaining. They eventually grew into lab notes, so here they are:

But before we start:
  • Create an account with Oracle Technology Network - http://otn.oracle.com
  • Create a SQL directory on the Z: drive (Z:\SQL). This will be the place to store your sql scripts, etc.
  • Create a shortcut to SQL*Plus on your desktop (an easy way to do this is to hold Control and drag the shortcut from the Start menu).
  • Right-click on the SQL*Plus icon on the desktop, click on Properties, and change the Start In property to "Z:\SQL\"
The database I was using to illustrate the concepts is a very intuitive Students database:

DDL scripts:

students_create.sql
students_populate.sql
students_drop

Snapshot:

Database snapshot

Lab notes:
Introduction to Oracle SQL Create and drop table
Data types
Insert, update, delete
Operators
Precedence of operators
Alter table
Oracle SQL*Plus Common SQL*Plus commands
TAB
first.sql A very simple SQL script (download it and execute by typing @first in your SQL*Plus window).
Select statement and the like SELECT
LIKE ...
... JOIN ...
Set operations
INTERSECT
UNION
MINUS
Aggregate functions ORDER BY, ASC, DESC
Aggregate functions
COUNT
SUM
MIN
MAX
AVG
GROUP BY and HAVING
Nested statements Nested statements, subqueries
Indentation
More joins
All
Views Views
COLUMN ... FORMAT ...
Oracle system views:
USER_TABLES
USER_VIEWS
USER_OBJECTS
Joins and outer joins JOIN, OUTER JOIN (LEFT, RIGHT, FULL)
Oracle's join syntax
Variables and built-in functions SQL*Plus string functions
UPPER()
LOWER()
SUBSTR()
SYSDATE() function
Variables in SQL*Plus
PROMPT ... ACCEPT ...
TTITLE, BTITLE
BREAK
command_line.sql Command line argument passing for SQL scripts
Past errors and future strategies Common errors I discovered while grading homeworks and lab reports for database courses.
Normalization lecture Normalization
1NF
2NF
3NF
BCNF
Indexes Indexes in Oracle
Create, alter, drop
Function-based indexes
USER_INDEXES
Indexes and primary keys
Oracle PL/SQL Language Structure of a PL/SQL program
Exceptions
3-valued logic
Loops (unconditional, while, for)
Cursors
Procedures
Functions
Triggers