Oracle SQL Course Week 4 - Lesson 1 DDL & DCL Final Lesson

Oracle SQL Final Lesson: DDL, DCL, and Closing the Course

This final lesson introduces Data Definition Language (DDL) and Data Control Language (DCL), explains how to create and manage tables, apply constraints, control user access, and finish the Oracle SQL course with practical real-world examples.

Lesson Overview

Up to this point, the course focused on retrieving and manipulating data using SELECT, functions, joins, subqueries, set operators, and DML. In this final lesson, students move to a new level: designing table structures and controlling database access.

CREATE TABLE ALTER TABLE DROP TABLE PRIMARY KEY UNIQUE NOT NULL CHECK FOREIGN KEY CREATE USER GRANT REVOKE
Trainer
Mohamed Al-Swaify
Mobile: 0564842804
Group: 28
Lesson: Week 4 Lesson 1
Part 1: DDL - Data Definition Language

What is DDL?

DDL is used to define and modify the structure of database objects such as tables. With DDL, we can create a table, change its columns, rename it, or remove it completely.

Main DDL Commands
  • CREATE - create a new object
  • ALTER - modify an existing object
  • DROP - remove an object permanently
Common Datatypes
  • CHAR(25)
  • VARCHAR2(25)
  • NUMBER(5,2)
  • DATE
  • TIMESTAMP

Naming Rules

  • Object names should not be more than 30 characters.
  • Names may contain letters, numbers, underscore _, dollar sign $, and hash #.
  • Names must start with a letter.
  • Names should not be Oracle reserved keywords.

Constraint Types

Primary Key

Uniquely identifies each row in a table and cannot be null.

Unique

Prevents duplicate values in a column or group of columns.

Not Null

Forces the column to always contain a value.

Check

Validates a condition such as salary greater than 3000.

Foreign Key

Links data between tables and enforces referential integrity.

DDL Examples

Example 1 - Simple CREATE TABLE

Create a departments table with a primary key and a required department name.

CREATE TABLE new_dept ( dept_id NUMBER(5) CONSTRAINT new_dept_dept_id_pk PRIMARY KEY, dept_name VARCHAR2(30) NOT NULL );
Example 2 - View Table Data
SELECT * FROM new_dept;
Example 3 - Insert Test Row
INSERT INTO new_dept VALUES (2, 'App Web');
Example 4 - Create Employee Table with Constraints

Create an employees table with PK, CHECK, DEFAULT, and FK constraints.

CREATE TABLE new_emp ( emp_id NUMBER(6) CONSTRAINT new_emp_emp_id_pk PRIMARY KEY, emp_name VARCHAR2(30) NOT NULL, salary NUMBER(8,2) CONSTRAINT new_emp_salary_ck CHECK (salary > 3000), hire_date DATE DEFAULT SYSDATE, dept_id NUMBER(4) CONSTRAINT new_emp_dept_id_fk REFERENCES new_dept(dept_id) );
Example 5 - Insert into Employee Table
INSERT INTO new_emp (emp_id, emp_name, salary, dept_id) VALUES (1, 'Mohamed', 5000, 2);
Example 6 - Add Column
ALTER TABLE new_emp ADD ( city VARCHAR2(30) DEFAULT 'Riyadh' );
Example 7 - Modify Column
ALTER TABLE new_emp MODIFY ( city VARCHAR2(50) DEFAULT 'Riyadh' );
Example 8 - Rename Column
ALTER TABLE new_emp RENAME COLUMN city TO country;
Example 9 - Drop Column
ALTER TABLE new_emp DROP (country);
Example 10 - More Advanced Constraints
CREATE TABLE new_emp ( emp_id NUMBER(6) CONSTRAINT new_emp_emp_id_pk PRIMARY KEY, emp_name VARCHAR2(30) NOT NULL, overTime NUMBER(6), salary NUMBER(8,2) CONSTRAINT new_emp_salary_ck CHECK (salary > 3000), hire_date DATE DEFAULT SYSDATE, dept_id NUMBER(4), CONSTRAINT new_emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES new_dept(dept_id), CONSTRAINT new_emp_overTime_ck CHECK (overTime < salary) );
Example 11 - Rename Table
ALTER TABLE new_emp RENAME TO new_employees;
Example 12 - Read Only / Read Write
ALTER TABLE new_dept READ ONLY; ALTER TABLE new_dept READ WRITE;
Example 13 - Drop Table
DROP TABLE new_emp;

Additional DDL Practice

Extra Example 1 - Unique Constraint
CREATE TABLE students ( student_id NUMBER(6) PRIMARY KEY, email VARCHAR2(100) UNIQUE, full_name VARCHAR2(50) NOT NULL );
Extra Example 2 - Check Constraint
CREATE TABLE products ( product_id NUMBER(6) PRIMARY KEY, product_name VARCHAR2(50) NOT NULL, price NUMBER(8,2) CHECK (price > 0) );
Extra Example 3 - Composite Constraint Style
CREATE TABLE course_enrollments ( student_id NUMBER(6), course_id NUMBER(6), enroll_date DATE DEFAULT SYSDATE, CONSTRAINT course_enrollments_pk PRIMARY KEY (student_id, course_id) );
Part 2: DCL - Data Control Language

What is DCL?

DCL is used to control access to database objects and users. It answers questions like: who can log in, who can read a table, and who can insert, update, or delete data.

System Privileges

These privileges control what a user can do in the database system itself, such as creating a session or creating tables.

Object Privileges

These privileges control what a user can do on a specific object such as a table: SELECT, INSERT, UPDATE, DELETE, ALTER, and more.

DCL Syntax

Create User
CREATE USER username IDENTIFIED BY password;
Grant System Privileges
GRANT privilege1, privilege2 TO username;
Revoke System Privileges
REVOKE privilege1, privilege2 FROM username;
Grant Object Privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO username;
Revoke Object Privileges
REVOKE SELECT, INSERT, UPDATE, DELETE ON hr.employees FROM username;

DCL Examples

Example 1 - Create User
CREATE USER abdallh IDENTIFIED BY 123;
Example 2 - Grant Create Session
GRANT CREATE SESSION TO abdallh;
Example 3 - Revoke Create Session
REVOKE CREATE SESSION FROM abdallh;
Example 4 - Grant Object Privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO abdallh;
Example 5 - Revoke Object Privileges
REVOKE SELECT, INSERT, UPDATE, DELETE ON hr.employees FROM abdallh;
Example 6 - Grant with UPDATE Columns
GRANT UPDATE(salary, commission_pct) ON hr.employees TO abdallh;
Example 7 - Grant to PUBLIC
GRANT SELECT ON hr.departments TO PUBLIC;
Example 8 - Grant Option
GRANT SELECT ON hr.employees TO abdallh WITH GRANT OPTION;

DDL vs DCL

DDL
  • Used to define or modify database structure
  • Examples: CREATE, ALTER, DROP
  • Focuses on tables and schema objects
DCL
  • Used to control user access and permissions
  • Examples: GRANT, REVOKE
  • Focuses on security and privileges

Questions

Write a CREATE TABLE statement for a table called new_course containing: course_id, course_name, price, and start_date with suitable datatypes and constraints.
Write an ALTER TABLE statement to add a column called city with default value 'Riyadh'.
Write a statement to rename the column city to country.
Create a user called student1 with a password, then grant create session privilege.
Grant SELECT privilege on hr.employees to the created user, then revoke it.

Tasks

Task 1 - Build Two Related Tables

Create two tables:

  • departments_test with department ID and department name
  • employees_test with employee ID, employee name, salary, hire date, and department ID

Apply the correct constraints including primary key, not null, check, and foreign key.

Task 2 - Modify Structure

Add a new column, modify its datatype, rename it, then drop it.

Task 3 - Security Task

Create a user, grant create session, grant select and insert on one table, then revoke insert only.

Task 4 - Data Validation

Create a table with a salary check constraint and test inserting both valid and invalid salaries.

Challenge Task - Final Practice

Design a mini HR schema from scratch that includes:

  • Departments table
  • Employees table
  • Constraints between them
  • A separate user with limited privileges
Goal: This task combines the full course journey: structure design, data rules, and access control.

Course Closing

Congratulations. By reaching this final lesson, students have completed a strong Oracle SQL foundation. Throughout the course, they learned:

  • Basic SELECT statements and filtering
  • Functions for characters, numbers, and dates
  • Joins and relationships between tables
  • Subqueries and set operators
  • DML operations for changing data
  • DDL for creating and managing table structures
  • DCL for controlling user access and privileges
Final Message:
Oracle SQL is not only about writing queries. It is about understanding data, designing structure, enforcing rules, and managing access professionally. This course has prepared students for the next level: PL/SQL, Oracle APEX, and real database projects.