FIT2094 Lecture Notes - Lecture 6: Data Manipulation Language, Data Definition Language, Referential Integrity
3 Aug 2018
School
Department
Course
Professor
SQL General Syntax
- A single statement is ended with SEMICOLON
- Predefined KEYWORDS represent clauses (components) of a statement
- Keywords are NOT case sensitive
- Examples:
CREATE TABLE unit
(
unit_code CHAR(7) NOT NULL,
Unit_name VARCHAR2(50) CONSTRAINT uq_unit_name UNIQUE NOT NULL,
CONSTRAINT pk_unit PRIMARY KEY (unit_code)
);
SELECT * FROM student;
SQL Statements
- Data Definition Language (DDL)
- Create database structure
- CREATE TABLE, ALTER TABLE, DROP TABLE
- Data Manipulation Language (DML)
- Adding and Manipulating database contents (Rows)
- INSERT, UPDATE, DELETE
- Retrieving data from database
- SELECT
- Data Manipulation Language (DML)
- GRANT
Common Oracle Data Types
Text : CHAR(size), VARCHAR2(size)
Eg. CHAR(10), VARCHAR2(10)
CHAR(10) -> ‘apple’ = ‘apple ‘
VARCHAR2(10) -> ‘apple’ != ‘apple ‘
Numbers: NUMBER(precision, scale)
Weight NUMBER(7) or NUMBER(7,0) -> Weight = 7456124
Weight NUMBER(9,2) -> Weight = 7456123.89
Weight NUMBER(8,1) - > Weight = 7456123.9
Data/Time: DATE, TIMESTAMP
DATE can store a date and time (time to seconds), stored as Julian date
TIMESTAMP can store a date and a time (up to fractions of a second)
TIMESTAMP WITH TIME ZONE
Column VS Table Level Constraints
Referential Integrity
To ensure referential integrity, SQL defines three possible actions for FKs in relations
when a deletion of a primary key occurs:
- RESTRICT (Oracle No Action basically equivalent)
- Deletion of tuples is NOT ALLOWED for those tuples in the table referred by
the FK (the table containing PK) if there is corresponding tuple in the table
containing the FK.
- CASCADE
- Deletion of a tuple in the table referred by the FK (the table containing PK) will
result in the deletion of the corresponding tuples in the table containing the FK
- NULLIFY
- Deletion of a tuple in the table referred by the FK( the table containing PK) will
result in the update of the corresponding tuples in the table containing the FK
to NULL
