Oracle SQL Basic day 13
Oracle SQL provides robust Data Definition Language (DDL) commands for defining and managing database schema structures. DDL operations include creating, altering, and dropping database objects such as tables, views, indexes, and sequences.
In this comprehensive guide, we will explore the primary DDL commands—CREATE, ALTER, DROP, TRUNCATE, RENAME, CREATE INDEX, CREATE SEQUENCE—and provide two real-world examples with three-line explanations for each using Oracle SQL syntax.
1. CREATE Statement
The CREATE statement is used to define new database objects such as tables, views, or indexes. This operation allocates space and defines structure. The object is available for immediate use.
Example 1:
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50) NOT NULL,
manager_id NUMBER,
location_id NUMBER
);
- This command creates the departments table with four columns.
- department_id is the primary key.
- The table includes data types and a NOT NULL constraint.
Example 2:
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 60;
- This view shows employee data for department 60.
- Views are virtual tables that simplify access.
- They also enhance data security and control.
2. ALTER Statement
The ALTER statement modifies the structure of existing objects like tables and views. It allows adding, dropping, or modifying columns and constraints.
Example 1:
ALTER TABLE departments
ADD (budget NUMBER(10,2));
- Adds a new column budget to the departments table.
- The new column supports values with two decimal places.
- Existing data is unaffected.
Example 2:
ALTER TABLE employees
MODIFY (email VARCHAR2(100));
- Changes the data type of the email column to allow up to 100 characters.
- Useful when existing field size is insufficient.
- No data loss occurs.
We can use alter statement to define constrains and checks as well for a table
3. DROP Statement
The DROP command deletes a database object completely. Dropped objects and their data are permanently lost.
Example 1:
DROP TABLE old_employees;
- Deletes the entire old_employees table and its data.
- This is irreversible.
- Use with caution, especially in production environments.
Example 2:
DROP VIEW emp_view;
- Removes the emp_view view from the database.
- Source tables remain intact.
- Views can be recreated if needed.
4. TRUNCATE Statement
The TRUNCATE command deletes all rows in a table quickly. It’s a DDL operation and cannot be rolled back.
Example 1:
TRUNCATE TABLE temp_employees;
- Instantly removes all data from temp_employees.
- No WHERE clause is allowed.
- Ideal for cleaning up temporary or staging data.
Example 2:
TRUNCATE TABLE logs;
- Efficiently deletes all rows from the logs table.
- Faster than DELETE and doesn’t generate rollback info.
- No triggers are fired.
5. RENAME Statement
The RENAME command changes the name of a database object. It doesn’t affect the structure or data.
Example 1:
RENAME departments TO departments_backup;
- Renames the departments table to departments_backup.
- The contents remain unchanged.
- Handy for making backup copies.
Example 2:
RENAME emp_view TO emp_view_backup;
- Renames the view for archival or testing purposes.
- Useful when doing development iterations.
- Does not modify the query logic.
6. CREATE INDEX Statement
Indexes speed up access to rows by providing fast lookup on columns. Oracle automatically uses indexes in query plans.
Example 1:
CREATE INDEX idx_emp_last_name ON employees(last_name);
- Creates an index on last_name to improve search performance.
- Helps queries that filter or sort by last name.
- Reduces full table scans.
Example 2:
CREATE UNIQUE INDEX idx_dept_name ON departments(department_name);
- Ensures uniqueness of department names.
- Speeds up equality searches on this column.
- Acts like a constraint in some ways.
7. CREATE SEQUENCE Statement
Sequences are database objects that generate numeric values, commonly for primary keys. They ensure uniqueness.
Example 1:
CREATE SEQUENCE emp_seq
START WITH 1000
INCREMENT BY 1;
- Generates employee IDs starting from 1000.
- Each new value is unique and auto-incremented.
- Useful for automated insert operations.
Example 2:
CREATE SEQUENCE dept_seq
START WITH 500
INCREMENT BY 10
MAXVALUE 10000;
- Defines a department ID generator with custom increment and limit.
- Sequences help avoid race conditions.
- Common in distributed applications.
Conclusion
Understanding and mastering Oracle SQL DDL commands is crucial for database developers and administrators. These commands allow you to:
- Create and structure your database objects
- Modify them as requirements evolve
- Delete or archive when objects are no longer needed
Working with Oracle's sample HR schema provides realistic scenarios for learning. Be especially careful with DROP and TRUNCATE operations, as they are irreversible. Practice these operations in a test environment before applying them to production.
DDL is foundational in Oracle SQL. Once you are confident with DDL, pairing it with DML and TCL will make you a well-rounded SQL expert.
0 Comments