Oracle SQL Fundamentals/Data Definition Language

This lesson introduces data definition language.

Objectives and Skills
Objectives and skills for the data definition language portion of Oracle SQL Fundamentals I certification include:
 * Using DDL Statements to Create and Manage Tables
 * Categorize the main database objects
 * Review the table structure
 * List the data types that are available for columns
 * Create a simple table
 * Explain how constraints are created at the time of table creation
 * Describe how schema objects work

Readings

 * 1) Read  Data definition language.
 * 2) Read  Check constraint.
 * 3) Read  Unique key.
 * 4) Read Oracle: Data Types.

Multimedia

 * 1) YouTube: Oracle SQL Tutorial Creating a table (Theory)
 * 2) YouTube: Oracle SQL Tutorial Creating a Table (Hands On CREATE TABLE command)
 * 3) YouTube: Oracle SQL Tutorial Creating a Table (SQL Developer)
 * 4) YouTube: Oracle SQL Tutorial Creating a table and its constraints
 * 5) YouTube: Oracle SQL Tutorial Creating constraints after Table Creation
 * 6) YouTube: Oracle SQL Tutorial Adding a column to a table
 * 7) YouTube: Oracle SQL Tutorial Altering a Table using SQL commands (Theory)
 * 8) YouTube: Oracle SQL Tutorial Altering a table using SQL commands (Hands On)

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

HR DDL Statements

 * 1) Display database objects.
 * 2) Display all user object types in the schema using the following query:
 * 3) Display all object types in the schema using the following query:
 * 4) Display user tables in the schema using the following query:
 * 5) Display information for all user table columns using the the following query:
 * 6) Display table structure and column data types.
 * 7) Display table structure and column data types using the following queries:
 * 8) Display column types for user tables in the HR schema using the following query:
 * 9) Display table names, column names, data types, length, precision, scale, and nullable for user tables in the HR schema using the following query:
 * 10) Create a simple table and add constraints with table creation.
 * 11) Create a table to track employee equipment. Include an ID, description, and employee ID using the following query:
 * 12) Add fields to the equipment table to track purchase date and purchase price using the following query:
 * 13) Modify the purchase date field using the following queries:
 * 14) Remove the weight column from the equipment table using the following query:
 * 15) Insert equipment data using the following query:
 * 16) Remove the Equipment table using the following query:

OE DDL Statements

 * 1) Display database objects
 * 2) Display table structure and column data types.
 * 3) Create a simple table and add constraints with table creation

Lesson Summary

 * A data definition language (DDL) is a syntax for defining data structures, especially database schemas.
 * Oracle data types include CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP, RAW, and block data types.
 * DDL statements include CREATE, ALTER, DROP, and RENAME.
 * A check constraint is a type of integrity constraint in SQL which specifies a requirement that must be met by each row in a database table.
 * CREATE TABLE syntax: CREATE TABLE ( [PRIMARY KEY] [not null] [, ...]);
 * CREATE TABLE syntax: CREATE TABLE AS SELECT  FROM  ...;
 * CONSTRAINT syntax: ALTER TABLE ADD CONSTRAINT CHECK ...;
 * CONSTRAINT syntax: ALTER TABLE ADD CONSTRAINT PRIMARY KEY ();
 * CONSTRAINT syntax: ALTER TABLE ADD CONSTRAINT FOREIGN KEY REFERENCES  ...;

Assessments

 * Flashcards: Quizlet: Oracle 1Z0-051 Exam - Table Objects
 * Flashcards: Quizlet: Oracle 1Z0-051 Exam - Privileges
 * Flashcards: Quizlet: Oracle 1Z0-051 Exam - Cluster Objects