Oracle SQL Background info to get you started.
This Oracle SQL background section is not going to bore you with history or who invented it, if you need that information, look at the last link on this page.
SQL is short for Structured Query Language.
It is the language you will use to access any data structure or its data in the Oracle database.
You will use this to issue commands to send instructions to the database.
It is a standard across all compliant databases.
SQL commands work on sets of data and allow you to :
- Query the data (select)
- Change the data (insert, update, delete)
- Work with data structures (create or replace, alter, drop)
- Determine access rules to data and structures (grant)
- Lock rows or tables to enforce consistency. (lock)
Tables and indexes
A table is a structure which contains horizontal rows and vertical columns.
The intersection of a row and a column is a cell.
When we search for data in a table, we use the SQL 'select' statement to extract the data in the table cells by specifying which 'intersection' of column and row we want to extract results for. (More details later..)
| Column A | Column B | |
| Row 1 | Cell: column A, row 1 | Cell: column B, row 1 |
| Row 2 | Cell: column A, row 2 | Cell: column B, row 2 |
Here's a real-world example of a table:
Table: Employees, contains 3 columns : EmployeeID, Surname and Firstname
and 4 rows of data.
Each table has a hidden column called rowid which uniquely identifies
each row in the table.
| Rowid | EmployeeID | Surname | Firstname |
| AAAUM2AAMAACepMAAA | J001 | Jones | Abe |
| AAAUM2AAMAACepMAAB | S001 | Smith | Brian |
| AAAUM2AAMAACepMAAB | B001 | Beckett | Chris |
| AAAUM2AAMAACepMAAD | B002 | Brown | David |
Index: a technique implemented by the database engine to conduct data access as fast as possible.
An index on the 'Surname' column of the Employees table would create another 'table' (but now called an index) containing only the sorted Surname data and the rowid of that row.
When you query data in the Surname column of the Employees table,
the database engine's optimizer determines whether or not to use the index,
based on the expected number of rows it has to return.
If it detects and uses the relevant index, it does a search on the index rather than the table.
Once if finds the correct row, it uses the stored rowid to access the Employees table directly (no searching) to retrieve the whole row.
Once an index is created, you do not need to maintain it, nor do you need to use its name to access
the data in the table. The Oracle engine does that for you in the background.
Normalization:
A methodology to design a database system in such a way so that there is no or limited data duplication.
No table should contain data already stored in another table unless it is a link to a row
in the other table (foreign key relationships).
There are many other rules and degrees of normalization , if you would
like to read more about it , see the link below in 'Additional resources'.
Recommended reading:
For more info on relational database theory, here's an excellent well-priced resource:
If you have time to scan through Oracle's manual about SQL, follow the link to the online documentation :
