Oracle SQL Fundamentals/Data Definition Language

This lesson introduces data definition language.

Objectives and Skills

edit

Objectives and skills for the data definition language portion of Oracle SQL Fundamentals I certification include:[1]

  • 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

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

Multimedia

edit
  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

edit

Schema Diagrams

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

HR DDL Statements

edit
  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

edit
  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

edit
  • A data definition language (DDL) is a syntax for defining data structures, especially database schemas.[2]
  • Oracle data types include CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP, RAW, and block data types.[3]
  • DDL statements include CREATE, ALTER, DROP, and RENAME.[4]
  • 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.[5]
  • CREATE TABLE syntax: CREATE TABLE <table1> (<column> <type> [PRIMARY KEY] [not null] [, ...]);[6]
  • CREATE TABLE syntax: CREATE TABLE <table1> AS SELECT <column(s)> FROM <table(s) [WHERE <condition>];[7]
  • ALTER TABLE syntax: ALTER TABLE <table1> ADD | MODIFY | DROP <column> <type>;[8]
  • DROP TABLE syntax: DROP TABLE <table1>;[9]
  • RENAME TABLE syntax: RENAME TABLE <table1> TO <table2>;[10]
  • CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> CHECK (<predicate>) ...;[11]
  • CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> PRIMARY KEY ...;[12]
  • CONSTRAINT syntax: CREATE TABLE ... CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table(column)> ...;[13]
  • CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> CHECK (<predicate>) ...;[14]
  • CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> PRIMARY KEY (<column(s)>);[15]
  • CONSTRAINT syntax: ALTER TABLE <table1> ADD CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table(column)> ...;[16]

Assessments

edit

See Also

edit

References

edit