# 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

* Read Wikipedia: Data definition language.
* Read Wikipedia: Check constraint.
* Read Wikipedia: Unique key.
* Read Oracle: Data Types.

## Multimedia

* YouTube: Oracle SQL Tutorial Creating a table (Theory)
* YouTube: Oracle SQL Tutorial Creating a Table (Hands On CREATE TABLE command)
* YouTube: Oracle SQL Tutorial Creating a Table (SQL Developer)
* YouTube: Oracle SQL Tutorial Creating a table and its constraints
* YouTube: Oracle SQL Tutorial Creating constraints after Table Creation
* YouTube: Oracle SQL Tutorial Adding a column to a table
* YouTube: Oracle SQL Tutorial Altering a Table using SQL commands (Theory)
* YouTube: Oracle SQL Tutorial Altering a table using SQL commands (Hands On)

## Activities
### Schema Diagrams

* Review HR and OE schema diagrams.
** Review the Oracle: HR and OE schema diagrams.

### HR DDL Statements

* Display database objects.
** Display all user object types in the schema using the following query: SELECT OBJECT_TYPE, COUNT(OBJECT_TYPE) AS "COUNT" FROM USER_OBJECTS GROUP BY OBJECT_TYPE;
** Display all object types in the schema using the following query: SELECT OBJECT_TYPE, COUNT(OBJECT_TYPE) AS "COUNT" FROM ALL_OBJECTS GROUP BY OBJECT_TYPE;
** Display user tables in the schema using the following query: SELECT TABLE_NAME FROM USER_TABLES;
** Display information for all user table columns using the the following query: SELECT * FROM USER_TAB_COLUMNS;
* Display table structure and column data types.
** Display table structure and column data types using the following queries: DESCRIBE REGIONS; DESCRIBE COUNTRIES; DESCRIBE LOCATIONS; DESCRIBE DEPARTMENTS; DESCRIBE EMPLOYEES; DESCRIBE JOBS; DESCRIBE JOB_HISTORY;
** Display column types for user tables in the HR schema using the following query: SELECT DISTINCT DATA_TYPE FROM USER_TAB_COLUMNS;
** Display table names, column names, data types, length, precision, scale, and nullable for user tables in the HR schema using the following query: SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE FROM USER_TAB_COLUMNS;
* Create a simple table and add constraints with table creation.
** Create a table to track employee equipment. Include an ID, description, and employee ID using the following query: CREATE TABLE EQUIPMENT( EQUIPMENT_ID NUMBER PRIMARY KEY, DESCRIPTION VARCHAR2(50) NOT NULL, EMPLOYEE_ID NUMBER(6) NOT NULL, CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES (EMPLOYEE_ID) );
** Add fields to the equipment table to track purchase date and purchase price using the following query: ALTER TABLE EQUIPMENT ADD( PURCHASE_DATE DATE, PURCHASE_PRICE NUMBER, WEIGHT NUMBER );
** Modify the purchase date field using the following queries: ALTER TABLE EQUIPMENT MODIFY PURCHASE_PRICE NUMBER(7,2); ALTER TABLE EQUIPMENT ADD CONSTRAINT PRICE_CK CHECK (PURCHASE_PRICE > 0);
** Remove the weight column from the equipment table using the following query: ALTER TABLE EQUIPMENT DROP COLUMN WEIGHT;
** Insert equipment data using the following query: INSERT INTO EQUIPMENT (EQUIPMENT_ID, DESCRIPTION, EMPLOYEE_ID, PURCHASE_DATE, PURCHASE_PRICE) VALUES(1, 'Laptop', 100, '1-JUL-87', 2413.89);
** Remove the Equipment table using the following query: DROP TABLE EQUIPMENT;

### OE DDL Statements

* Display database objects
* Display table structure and column data types.
* 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 <table1> (<column> <type> [PRIMARY KEY] [not null] [, ...]);
* CREATE TABLE syntax: CREATE TABLE <table1> AS SELECT <column(s)> FROM <table(s) [WHERE <condition>];
* ALTER TABLE syntax: ALTER TABLE <table1> ADD | MODIFY | DROP <column> <type>;
* DROP TABLE syntax: DROP TABLE <table1>;
* RENAME TABLE syntax: RENAME TABLE <table1> TO <table2>;
* CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> CHECK (<predicate>) ...;
* CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> PRIMARY KEY ...;
* CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table(column)> ...;
* CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> CHECK (<predicate>) ...;
* CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> PRIMARY KEY (<column(s)>);
* CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table(column)> ...;

## Assessments

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

## See Also

* Oracle: Database 2 Day Developers Guide - Creating and Managing Schema Objects