Guide to Oracle SQL (University at Buffalo Version)

Introduction to Databases

Wikipedia defines a database as "an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies."

A schema is "its structure described in a formal language supported by the database management system (DBMS). The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).

A table is a collection of related data held in a structured format within a database. It consists of fields (columns), and rows. In relational databases and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows (a record), the cell (field) being the unit where a row and column intersect.

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SID                                       NOT NULL VARCHAR2(9)
S_LNAME                                   NOT NULL VARCHAR2(10)
S_FNAME                                   NOT NULL VARCHAR2(10)
GENDER                                             VARCHAR2(1)
MAJOR                                              VARCHAR2(3)
GPA                                                NUMBER(3,2)
SAT                                                NUMBER(5)
DOB                                                DATE

Figure 1-1: Database Table Structure

Data Types

Each record contains fields that hold the data for that record. A field has a data type, such as Integer (NUMBER), string (VARCHAR/LONG), date (DATE), large objects (BLOB/CLOB)).

CHAR: Used to store fixed length character data. For example, a state field would be CHAR(2).
VARCHAR2: Used to store variable length character data. For example, a firstName field would be VARCHAR2(25). This allows the field to be any number of characters between 0 and 25.
NUMBER: Used to store integer data. For example, the age field would be NUMBER(2).; NUMBER can also be used to store floating point (decimal) data. For example, a currency field would be NUMBER(9,2) which allows 7 digits to the left of the decimal point and 2 digits to the right of the decdimal point.
DATE: Used to store the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
LOB (Large OBject): BLOB - Used to store unstructured binary data.; CLOB - Used to store database character set data.; NCLOB - Used to store Unicode national character set data.; BFILE - used to store a pointer to a binary file in the operating system outside the database.

Normalization

Normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity..

Using `SQL Developer`

SQL Developer is a graphical interface to an Oracle database. You can download SQL Developer from the Oracle download site. Installation instructions are also available on that site. You also need to download and install an Oracle database.

Setting Up a Connection

SQL Developer New Connection — Figure 1-3: The `SQL Developer` New Connection Window

SQL Developer Interface — Figure 1-4: The `SQL Developer` New Connection Settings