about

Relational Database Basics

Introduction

Rights, Liability, & Trademarks

Who, What, Why?

How do you use this Course?

What is a Database?

Flat & Indexed Files

DataBase Management Systems

DBMS Models & What is RDBMS?

Tables, Columns, & Rows

Primary Key

Foreign Key

One-to-One Relationships

One-to-Many Relationships

Many-to-Many Relationships

Why is Relational Better?

SQL Basics

SQL is DML, DDL, & DCL

SQL Format

Let’s Try Some SQL!

ANSI SQL & Vendor Implementation

SQL History

Relational Model

Base Table

Views

Set Theory

Projection

Selection

Join

Union

Data Integrity

Data Independence

Codd’s Rules

Collating Sequence

SELECT Statement

SELECT Statement Syntax

SELECT * (ALL Columns)

Using Column Names

AS (Column Aliases)

DISTINCT Values

Using Constants

NULLs

Conditions & Comparisons

Arithmetic Operations

DATE & TIME Expressions

WHERE Clause

WHERE Clause (Row Selection)

Column & Row Selection

AND, OR, and NOT

IS NULL (Unknown Value)

BETWEEN (Matching to a Range)

LIKE (Matching to a Pattern)

SIMILAR TO (Matching to an Expression)

IN (Matching to a List)

ORDER BY (Sorting the Results)

INSERT, UPDATE, DELETE

INSERT Syntax

INSERT One Row

INSERT Multiple Rows

UPDATE Syntax

UPDATE

DELETE Syntax

DELETE

ANSI String Scalar Functions

Concatenate || (attach values)

CONVERT (chg. to new chars.)

LOWER (chg. to lower case)

OVERLAY (replace substring)

SUBSTRING (get part of string)

TRANSLATE (chg. character set)

TRIM (remove lead or trail chars.)

UPPER (chg. to upper case)

Column Functions

Introduction

SUM (Summation)

AVG (Average)

MIN (Minimum)

MAX (Maximum)

COUNT (Rows)

Combinations

Calculations

Grouping & Filtering

GROUP BY

HAVING

JOINs

Introduction

Qualifying Column Names

AS (Table Aliases)

JOIN versus WHERE

INNER JOIN or Equijoin

Multiple Equates

Filter with WHERE

Non-Equal Conditions

Three Tables

Column Functions

Col. Func. and Calculations

Column Functions and HAVING

NATURAL JOIN

Self Join

CROSS JOIN or Cartesian Join

OUTER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

UNIONs

UNION

INTERSECT

EXCEPT

Subselects

How Do Subselects Work?

Simple and Correlated Subselects

Select & Subselect Column Names

Nulls in a Subselect

Simple Subselect

After a Comparison Operator

ALL

ANY or SOME

Correlated Subselects

EXISTS

Multiple Subselects

Equivalent Statements

Database Objects

What Are Database Objects?

Converting Data into Tables

Normalizing the Database

Naming Standards

Table Creation

Introduction

How do Constraints Work?

NOT NULL

DEFAULT

PRIMARY KEY

FOREIGN KEY

UNIQUE

CHECK

Data Definition Language

CREATE TABLE

ALTER TABLE

DROP TABLE

CREATE VIEW

ALTER VIEW

DROP VIEW

CREATE INDEX

DROP INDEX

CREATE ALIAS or SYNONYM

DROP ALIAS or SYNONYM

CREATE SCHEMA

DROP SCHEMA

Data Control Language

User-ID & Authentication

Ownership of Objects

GRANT

SELECT, INSERT, UPDATE, DELETE

TO PUBLIC

WITH GRANT OPTIONS

ALL PRIVILEGES

VIEW security

REVOKE

Cascading Privileges

Host Programs

Cursor Control

DECLARE, OPEN, CLOSE CURSOR

FETCH

INSERT

UPDATE, DELETE

Ada

COBOL

Fortran

MUMPS

Pascal

PL/I

SQLCODE & SQLSTATE

Advanced Topics

Transactions

Concurrency Control

Performance Tuning

Data Types

Introduction to Data Types

ANSI/ISO SQL Standard Data Types

Character String Data

CHAR (length)

VARCHAR (length)

LONG VARCHAR (length)

NATIONAL CHARACTER

Character Large Object CLOB (length)

NCLOB(length)

Boolean Data

Exact Numeric Data

SMALLINT

INTEGER

BIGINT

DECIMAL (total,fraction) or NUMERIC

Approximate Numeric Date

FLOAT (precisionbits)

REAL

DOUBLE PRECISION

Binary large Object (BLOB) Data

GRAPHIC (length)

VARGRAPHIC (length)

LONG VARGRAPHIC (length)

Date/Time/Timestamp Data

DATE

TIME

TIME WITH TIME ZONE

TIMESTAMP

Intervals

Year-Month

Day-Time

Date/Time/Interval Arithmetic

Sample Data Type Values

ANSI Scalar Functions

Introduction

CASE (IF-THEN-ELSE)

CAST (convert data type)

CURRENT_DATE (today’s date)

CURRENT_TIME (today’s time)

CURRENT_TIMESTAMP (date & time)

CURRENT_USER, USER (active user)

SESSION_USER (Authorization ID)

SYSTEM_USER (user of oper. sys.)

ANSI Numeric Scalar Functions

ABSOLUTE or ABS (absolute value)

BIT_LENGTH (no. of bits)

CEILING or CEIL (rounds upward)

CHAR_LENGTH (no. of characters)

EXP (power of e)

EXTRACT (part of date/time)

FLOOR (round downward)

LN (natural logarithm)

MOD (division remainder)

OCTET_LENGTH (bit length / 8)

POSITION (start pos. of a string)

POWER (raise no. to power)

SQRT (square root of no.)

WIDTH_BUCKET (get bucket value)