Oracle SQL Fundamentals/Data Definition Language
This lesson introduces data definition language.
Objectives and Skills
editObjectives 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- Read Wikipedia: Data definition language.
- Read Wikipedia: Check constraint.
- Read Wikipedia: Unique key.
- Read Oracle: Data Types.
Multimedia
edit- 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
editSchema Diagrams
edit- Review HR and OE schema diagrams.
- Review the Oracle: HR and OE schema diagrams.
HR DDL Statements
edit- 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 all user object types in the schema using the following query:
- 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;
- Display table structure and column data types using the following queries:
- 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;
- Create a table to track employee equipment. Include an ID, description, and employee ID using the following query:
OE DDL Statements
edit- Display database objects
- Display table structure and column data types.
- 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- 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
editReferences
edit- ↑ Oracle: Database 11g: SQL Fundamentals I Exam Topics
- ↑ Wikipedia: Data definition language
- ↑ Oracle: Data Types
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Check constraint
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Check constraint
- ↑ Oracle: constraint
- ↑ Oracle: constraint
- ↑ Wikipedia: Check constraint
- ↑ Wikipedia: Unique key
- ↑ Oracle: constraint