Oracle - Introduction

Introduction

The purpose of this module is to teach you about databases, in particular databases based on the relational model. The relational model is widely used in industry and many vendors provide relational products. Oracle, Microsoft Access and Visual FoxPro are three such products. SQL (Structure Query Language) is regarded as being the de facto query language for relational databases and this module uses Oracle as a vehicle for teaching SQL.

SQL is subject to a number of standards: ANSI X3.135 - 1989; SQL2 (or SQL92) and SQL3 (SQL99) and recently SQL4 (2003). The current version of Oracle (version 12c) supports most of the SQL92 standard. It does also offer some support for objects, which is in the SQL99

As you get more familiar with the product, you will come to realise that a lot of the commands detailed in this booklet could have been more easily entered/executed using the power tools. The purpose of the module is to teach you SQL which is standard across many relational products, e.g., Sysbase, Ingres, DB2. The Oracle Developer tools however are proprietary and every product has different tools. The commands in this booklet however can be executed on most relational platforms with little amendment; this would not be true if the toolset were used.

The Sample Relational Database

A relational database is a named collection of tables and other associated database objects (e.g., indexes, screens, menus, etc.). Typically a database management system (DBMS) can support more than one database simultaneously (or in ANSI-1989 terms, more than one environment). This allows the user to organise their tables into different databases if required. For example, you could have a different database for each module.

The following tables are used for illustrative purposes throughout the handout:

EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT(DEPTNO, DNAME, LOC)

SALGRADE(GRADE, LOSAL, HISAL)

CUSTOMER(CNO, ORDERDATE, NAME, CITY, COUNTRY, PNO, DESCRIPTION, QTY)

Where:

• EMP contains information about the employees of a sample company
• DEPT contains information about the departments in the company
• SALGRADE groups salary ranges into grades
• CUSTOMER details information about the company's customers