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 Wikipedia: Data definition language.
2. Read Wikipedia: Check constraint.
3. Read Wikipedia: 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)

Activities

Schema Diagrams

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

HR DDL Statements

1. Display database objects.
   1. 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;
   2. 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;
   3. Display user tables in the schema using the following query:
      SELECT TABLE_NAME FROM USER_TABLES;
   4. Display information for all user table columns using the the following query:
      SELECT * FROM USER_TAB_COLUMNS;
2. Display table structure and column data types.
   1. 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;
   2. Display column types for user tables in the HR schema using the following query:
      SELECT DISTINCT DATA_TYPE FROM USER_TAB_COLUMNS;
   3. 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;
3. Create a simple table and add constraints with table creation.
   1. 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)
);
   2. 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
);
   3. 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);
   4. Remove the weight column from the equipment table using the following query:
      ALTER TABLE EQUIPMENT
DROP COLUMN WEIGHT;
   5. 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);
   6. Remove the Equipment table using the following query:
      DROP TABLE EQUIPMENT;

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 <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

References

← Transactions

Oracle SQL Fundamentals

Other Schema Objects →