Here’s a detailed Data Definition Language (DDL) guide for Oracle SQL, covering all listed topics, with classic definitions, challenging examples, and interview-style Q&A included inline:
1. Naming Rules & Naming Conventions in Oracle
✅ Concept:
- Object names (tables, columns, indexes) must start with a letter.
- Can include letters, numbers, underscores.
- Max length: 30 characters (in Oracle).
- Avoid Oracle reserved keywords (
SELECT
,TABLE
, etc.).
✅ Naming Conventions:
- Use UPPERCASE_SNAKE_CASE for tables:
EMPLOYEE_DETAILS
- Use camelCase or snake_case for columns:
employee_id
,first_name
- Prefixing is common in legacy systems:
TBL_EMP
,IDX_EMP_NAME
⚡️Example:
CREATE TABLE employee_details (
emp_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
hire_date DATE
);
🎯 Interview Question:
Q: Why should you avoid using reserved keywords as table names?
A: They cause ambiguity or syntax errors unless quoted. For example:
CREATE TABLE "SELECT" (id NUMBER); -- works but bad practice
2. CREATE TABLE Statement
✅ Concept:
Used to create a new table in the database.
✅ Syntax:
CREATE TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
...
);
⚡️Example:
CREATE TABLE department (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100) NOT NULL,
created_at DATE DEFAULT SYSDATE
);
🎯 Interview Challenge:
Q: How would you create a table with a check constraint ensuring salary > 1000?
A:
CREATE TABLE employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER CHECK (salary > 1000)
);
3. CREATE TABLE AS SELECT (CTAS)
✅ Concept:
Creates a new table with data copied from an existing query result.
✅ Syntax:
CREATE TABLE new_table AS
SELECT * FROM existing_table WHERE ...;
⚡️Example:
CREATE TABLE emp_backup AS
SELECT * FROM employee WHERE hire_date < '01-JAN-2020';
✅ The new table won’t have constraints or indexes from the source.
🎯 Interview Trick:
Q: What does CTAS copy from the source table?
A: It copies only the data and column structure, not:
- Primary/Foreign keys
- Check constraints
- Triggers or Indexes
4. ALTER TABLE Statement
✅ Concept:
Used to modify the structure of an existing table.
✅ Use Cases:
- Add, drop, or modify columns
- Rename columns
- Add/drop constraints
⚡️Example:
ALTER TABLE employee ADD (email VARCHAR2(100));
ALTER TABLE employee MODIFY (email NOT NULL);
ALTER TABLE employee DROP COLUMN email;
🎯 Interview Challenge:
Q: How would you rename a column in Oracle?
A:
ALTER TABLE employee RENAME COLUMN emp_name TO full_name;
5. SET UNUSED Columns
✅ Concept:
Marks a column as unused (invisible to queries) without physically dropping it.
🔒 Safe in production systems where immediate drop is risky.
⚡️Example:
ALTER TABLE employee SET UNUSED (old_column);
➡️ To drop unused columns later:
ALTER TABLE employee DROP UNUSED COLUMNS;
🎯 Interview Trick:
Q: Why use SET UNUSED
instead of DROP COLUMN
?
A: SET UNUSED
is faster and reversible in audit/compliance-heavy systems.
6. READ-ONLY Tables
✅ Concept:
Used to prevent modifications (INSERT/UPDATE/DELETE) on a table.
⚡️Example:
ALTER TABLE employee READ ONLY;
-- To revert:
ALTER TABLE employee READ WRITE;
✅ Useful for archived or reference data.
🎯 Interview Twist:
Q: How does READ ONLY
differ from GRANT SELECT ONLY
?
A: READ ONLY
applies at the table level (irrespective of user privileges); GRANT
is user-level access control.
7. DROP TABLE Statement
✅ Concept:
Removes a table and its data irreversibly (unless in a recycle bin-enabled setup).
⚡️Example:
DROP TABLE employee;
✅ If enabled, Oracle will place the dropped table in the recycle bin.
🎯 Interview Pitfall:
Q: What happens to dependent objects when a table is dropped?
A: All dependent indexes, constraints, triggers, synonyms are also dropped.
8. TRUNCATE TABLE Statement
✅ Concept:
Removes all rows from a table, resetting high water mark, faster than DELETE.
⚡️Example:
TRUNCATE TABLE employee;
✅ Cannot be rolled back (DDL), unlike DELETE
.
🎯 Interview Trick:
Q: Difference between DELETE
, TRUNCATE
, and DROP
?
Feature | DELETE | TRUNCATE | DROP |
---|---|---|---|
Rollback | Yes | ❌ No | ❌ No |
Table stays | Yes | Yes (empty) | ❌ No |
Reset HWM | ❌ No | ✅ Yes | ✅ N/A |
9. COMMENT Statement
✅ Concept:
Used to add descriptions to tables or columns for documentation.
⚡️Example:
COMMENT ON TABLE employee IS 'Stores employee data';
COMMENT ON COLUMN employee.salary IS 'Monthly salary in INR';
✅ Comments are stored in Oracle’s data dictionary views.
🎯 Interview Tip:
Q: Where can you view table/column comments?
A: Use:
SELECT * FROM user_tab_comments;
SELECT * FROM user_col_comments WHERE table_name = 'EMPLOYEE';
10. RENAME Statement
✅ Concept:
Used to rename a table or view (not individual columns in this syntax).
⚡️Example:
RENAME employee TO emp_master;
✅ Column renaming uses ALTER TABLE
instead.
🎯 Interview Trick:
Q: Can you rename a table with active constraints?
A: Yes, Oracle automatically updates constraint references.
✅ BONUS: Quiz/Practice Questions (5 Sample Qs)
- What is the difference between
TRUNCATE
andDELETE
? - Write a DDL to create a table from another table’s structure and data.
- How do you rename a column in Oracle?
- When would you use
SET UNUSED
instead ofDROP COLUMN
? - How do you make a table READ-ONLY?
✅ Summary Table
DDL Command | Purpose | Key Use Case | Interview Tip |
---|---|---|---|
CREATE | Define structure | New tables, CTAS | Watch out for constraint exclusions |
ALTER | Modify structure | Add/modify/drop columns | Rename columns carefully |
DROP | Remove objects | Deleting obsolete tables | Use with caution |
TRUNCATE | Remove data only | Fast full deletion | Can’t be rolled back |
COMMENT | Documentation | Add info for developers | Check in data dictionary |
RENAME | Rename objects | Refactor tables | Cannot rename columns via RENAME |
SET UNUSED | Hide columns | Safe drop later | Good for phased removals |
READ ONLY | Lock data | Freeze archived data | Overrides user DML rights |
Absolutely! Here’s a complete guide to DML (Data Manipulation Language) in Oracle SQL, covering each command with:
- Concept + Syntax
- Classic + Challenging Examples
- Interview Questions + Answers
🔷 DATA MANIPULATION LANGUAGE (DML) IN ORACLE
DML Commands:
INSERT
UPDATE
DELETE
MERGE
LOCK TABLE
SELECT ... FOR UPDATE
1. INSERT Statement
✅ Concept:
Used to add new rows to a table.
✅ Syntax:
INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...);
⚡ Classic Example:
INSERT INTO employee (emp_id, emp_name, salary)
VALUES (101, 'John Doe', 50000);
⚡ Challenging Example:
Insert data from another table:
INSERT INTO emp_backup
SELECT * FROM employee WHERE dept_id = 10;
🎯 Interview Q&A:
Q: What happens if you omit a NOT NULL column in INSERT
?
A: Oracle will raise an error unless a DEFAULT value exists for that column.
2. UPDATE Statement
✅ Concept:
Modifies existing data in a table.
✅ Syntax:
UPDATE table_name
SET col1 = val1, col2 = val2
WHERE condition;
⚡ Classic Example:
UPDATE employee
SET salary = salary * 1.10
WHERE dept_id = 20;
⚡ Challenging Example:
Update multiple rows with different values using CASE
:
UPDATE employee
SET bonus =
CASE
WHEN dept_id = 10 THEN 1000
WHEN dept_id = 20 THEN 2000
ELSE 500
END;
🎯 Interview Q&A:
Q: What happens if you omit the WHERE clause?
A: All rows are updated — a major risk in production.
3. DELETE Statement
✅ Concept:
Removes rows from a table.
✅ Syntax:
DELETE FROM table_name WHERE condition;
⚡ Classic Example:
DELETE FROM employee WHERE emp_id = 101;
⚡ Challenging Example:
Delete rows based on a subquery:
DELETE FROM employee
WHERE dept_id IN (SELECT dept_id FROM department WHERE location = 'Chicago');
🎯 Interview Q&A:
Q: Can DELETE
be rolled back?
A: Yes, unlike TRUNCATE
, DELETE
is transactional and can be undone with ROLLBACK
.
4. MERGE Statement (a.k.a UPSERT)
✅ Concept:
Combines INSERT
and UPDATE
. Common in ETL and data sync scenarios.
✅ Syntax:
MERGE INTO target_table tgt
USING source_table src
ON (tgt.id = src.id)
WHEN MATCHED THEN
UPDATE SET tgt.col = src.col
WHEN NOT MATCHED THEN
INSERT (id, col) VALUES (src.id, src.col);
⚡ Classic Example:
MERGE INTO employee tgt
USING new_employees src
ON (tgt.emp_id = src.emp_id)
WHEN MATCHED THEN
UPDATE SET tgt.salary = src.salary
WHEN NOT MATCHED THEN
INSERT (emp_id, emp_name, salary)
VALUES (src.emp_id, src.emp_name, src.salary);
🎯 Interview Q&A:
Q: What’s a real-world use case for MERGE
?
A: In a data warehouse, when syncing dimensions or fact tables from staging → production.
5. LOCK TABLE Statement
✅ Concept:
Explicitly locks a table to prevent others from modifying it during a transaction.
✅ Syntax:
LOCK TABLE table_name IN {EXCLUSIVE|SHARE} MODE;
⚡ Example:
LOCK TABLE employee IN EXCLUSIVE MODE;
Prevents others from DML operations until the transaction is committed or rolled back.
🎯 Interview Q&A:
Q: Why use LOCK TABLE
explicitly?
A: To ensure consistency in critical multi-step transactions and avoid lost updates.
6. SELECT … FOR UPDATE
✅ Concept:
Locks selected rows for update in concurrent environments.
✅ Syntax:
SELECT * FROM employee
WHERE dept_id = 10
FOR UPDATE;
Locks only the rows returned, preventing other sessions from updating/deleting them.
🎯 Interview Q&A:
Q: Difference between LOCK TABLE
and SELECT ... FOR UPDATE
?
Feature | LOCK TABLE | SELECT ... FOR UPDATE |
---|---|---|
Lock Scope | Entire table | Only selected rows |
Use Case | Full-table DML isolation | Row-level locking (e.g., web apps) |
Common In | ETL, batch ops | OLTP, real-time updates |
🧠 INTERVIEW QUESTIONS & ANSWERS – DML
- Q: What’s the difference between
TRUNCATE
andDELETE
?- A:
TRUNCATE
is DDL, faster, non-transactional.DELETE
is DML, slower, transactional.
- A:
- Q: Can you use
MERGE
withoutWHEN MATCHED
?- A: Yes, just omit the
WHEN MATCHED
part; acts asINSERT ONLY
.
- A: Yes, just omit the
- Q: What happens if an
INSERT
violates a unique constraint?- A: Oracle throws
ORA-00001: unique constraint violated
.
- A: Oracle throws
- Q: How do you update one column based on a condition from another table?
UPDATE emp e SET salary = salary + 5000 WHERE EXISTS ( SELECT 1 FROM dept d WHERE d.dept_id = e.dept_id AND d.location = 'Bangalore' );
- Q: Difference between
DELETE
andDROP
?DELETE
: removes data, table structure remains.DROP
: removes data + structure + dependent objects.
✅ Summary Table – DML Quick Reference
Command | Purpose | Can Rollback? | Locks Rows? | Common Use Case |
---|---|---|---|---|
INSERT | Add new rows | ✅ Yes | ❌ | Adding records |
UPDATE | Modify existing rows | ✅ Yes | ✅ (if used with WHERE) | Salary, status updates |
DELETE | Remove specific rows | ✅ Yes | ✅ (row-level) | Soft delete pattern |
MERGE | Conditional insert/update | ✅ Yes | ✅ | Upsert in ETL/data sync |
LOCK TABLE | Lock table explicitly | N/A | ✅ | Avoid update conflicts |
SELECT ... FOR UPDATE | Row-level lock on select | N/A | ✅ | Prevent concurrent updates |
Absolutely! Here’s a complete guide to TCL (Transaction Control Language) in Oracle SQL with:
- ✅ Concept + Syntax
- ⚡ Classic + Challenging Examples
- 🎯 Interview Questions + Answers
- 🧠 Summary Table at the end
🔷 TRANSACTION CONTROL LANGUAGE (TCL) — ORACLE SQL
⚙️ What is TCL?
TCL commands are used to manage changes made by DML statements (INSERT, UPDATE, DELETE). They ensure data consistency and allow grouping DML operations into atomic transactions.
TCL Commands:
COMMIT
ROLLBACK
SAVEPOINT
ROLLBACK TO SAVEPOINT
SET TRANSACTION
✅ 1. COMMIT
🔹 Concept:
Saves all changes made by the transaction permanently to the database.
🔹 Syntax:
COMMIT;
⚡ Classic Example:
UPDATE employee SET salary = salary + 1000 WHERE dept_id = 10;
COMMIT;
➡ Changes are now permanent and visible to other sessions.
⚡ Challenging Example:
BEGIN
UPDATE accounts SET balance = balance - 500 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE acc_id = 2;
COMMIT;
END;
✅ Used to transfer funds with atomicity.
✅ 2. ROLLBACK
🔹 Concept:
Undo changes made by the current transaction since the last COMMIT or SAVEPOINT.
🔹 Syntax:
ROLLBACK;
⚡ Classic Example:
DELETE FROM orders WHERE order_date < '01-JAN-2020';
ROLLBACK;
➡ All deleted rows are restored.
⚡ Challenging Example:
BEGIN
UPDATE emp SET salary = salary + 2000 WHERE emp_id = 101;
RAISE_APPLICATION_ERROR(-20001, 'Error occurred');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
✅ Ensures automatic rollback on failure.
✅ 3. SAVEPOINT
🔹 Concept:
Creates a named point in a transaction to which you can ROLLBACK partially.
🔹 Syntax:
SAVEPOINT savepoint_name;
⚡ Classic Example:
UPDATE employee SET salary = salary + 1000 WHERE dept_id = 10;
SAVEPOINT after_salary_update;
DELETE FROM employee WHERE status = 'INACTIVE';
SAVEPOINT after_delete;
-- Later
ROLLBACK TO after_salary_update;
⚡ Challenging Example:
BEGIN
INSERT INTO temp_log VALUES ('START');
SAVEPOINT sp1;
INSERT INTO temp_log VALUES ('MID');
SAVEPOINT sp2;
INSERT INTO temp_log VALUES ('END');
ROLLBACK TO sp2;
COMMIT;
END;
✅ Only the 'END'
insert is undone.
✅ 4. ROLLBACK TO SAVEPOINT
🔹 Concept:
Rolls back part of a transaction to a previous SAVEPOINT without affecting earlier work.
🔹 Syntax:
ROLLBACK TO savepoint_name;
⚡ Example:
UPDATE employee SET salary = salary * 1.10;
SAVEPOINT sp1;
DELETE FROM employee WHERE hire_date < '01-JAN-2000';
SAVEPOINT sp2;
ROLLBACK TO sp1; -- Undo delete, keep update
✅ 5. SET TRANSACTION
🔹 Concept:
Used to set the properties of a transaction, like read consistency or isolation level.
🔹 Syntax:
SET TRANSACTION [READ ONLY | READ WRITE];
⚡ Classic Example:
SET TRANSACTION READ ONLY;
SELECT * FROM employee WHERE salary > 50000;
COMMIT;
✅ Ensures no DML occurs inside the transaction.
🎯 INTERVIEW QUESTIONS — TCL
1. Q: What’s the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?
A:
ROLLBACK
→ undo the entire transaction.ROLLBACK TO SAVEPOINT
→ undo changes after the savepoint only.
2. Q: What happens if you issue COMMIT
after a ROLLBACK
?
A: The ROLLBACK
discards the transaction. The following COMMIT
has no effect (nothing to commit).
3. Q: Is TRUNCATE
rollback-able?
A: ❌ No. It is a DDL command, auto-committed, and cannot be rolled back.
4. Q: Can a SAVEPOINT be reused?
A: ✅ Yes. Issuing SAVEPOINT sp1;
again overwrites the old one.
5. Q: How do you partially rollback a transaction?
A:
SAVEPOINT sp1;
-- do something
ROLLBACK TO sp1;
🧠 TCL SUMMARY TABLE
Command | Description | Can Rollback? | Notes |
---|---|---|---|
COMMIT | Save changes permanently | ❌ No | Auto-commit in DDL |
ROLLBACK | Undo all uncommitted changes | ✅ Yes | Undoes the whole transaction |
SAVEPOINT | Create a logical point in a transaction | N/A | Used for partial rollback |
ROLLBACK TO | Undo changes after a SAVEPOINT | ✅ Partial | Earlier changes stay intact |
SET TRANSACTION | Set isolation level or access mode | N/A | Use READ ONLY for reporting txns |
💡 Real-World Scenario:
Banking Transaction:
BEGIN
SAVEPOINT before_deduction;
UPDATE accounts SET balance = balance - 10000 WHERE acc_id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 10000 WHERE acc_id = 2;
-- If acc_id = 2 doesn't exist, rollback to before_deduction
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO before_deduction;
RAISE;
END;
Here’s a complete and interview-ready Oracle SQL DCL (Data Control Language) guide, with:
- ✅ Concept + Syntax
- ⚡ Classic + Challenging Examples
- 🎯 Interview Questions + Answers
- 🧠 Summary Table
🔷 DATA CONTROL LANGUAGE (DCL) — ORACLE SQL
DCL is used to control access to data in a database through permissions and privileges.
📌 DCL Commands:
GRANT
REVOKE
- (Oracle also uses
ROLE
andAUDIT
, but they’re part of security, not pure DCL)
✅ 1. GRANT Statement
🔹 Concept:
Grants privileges (rights) to users or roles on database objects.
🔹 Syntax:
GRANT privilege[, privilege2, ...]
ON object_name
TO user_or_role[, user2]
[WITH GRANT OPTION];
✅ System Privileges Examples:
GRANT CREATE SESSION TO hr;
GRANT CREATE TABLE, CREATE VIEW TO hr;
➡ Allows hr
to connect and create tables/views.
✅ Object Privileges Example:
GRANT SELECT, INSERT ON employees TO analyst;
➡ Allows analyst
to query and insert data into employees
.
✅ WITH GRANT OPTION:
GRANT SELECT ON employees TO analyst WITH GRANT OPTION;
➡ Allows analyst
to further grant SELECT on employees
to others.
⚡ Challenging Example:
GRANT SELECT, INSERT, UPDATE
ON orders
TO sales_user, sales_manager;
➡ Grants access to multiple users in one command.
🎯 Interview Q&A:
Q: What’s the difference between system and object privileges?
A:
- System Privileges: Access to operations (e.g.,
CREATE TABLE
,DROP USER
) - Object Privileges: Access to specific tables, views (e.g.,
SELECT
,INSERT
onEMPLOYEES
)
✅ 2. REVOKE Statement
🔹 Concept:
Removes privileges granted earlier using GRANT
.
🔹 Syntax:
REVOKE privilege[, privilege2]
ON object_name
FROM user_or_role[, user2];
✅ Example:
REVOKE SELECT, INSERT ON employees FROM analyst;
➡ Removes SELECT
and INSERT
access from analyst
.
⚡ Challenging Example:
REVOKE CREATE TABLE FROM hr;
➡ User hr
can no longer create tables.
⚠️ Important:
If user A has WITH GRANT OPTION
and gave privileges to B and C, then revoking A’s privilege also revokes B and C’s access (called cascading revoke).
🎯 Interview Q&A:
Q1: Can GRANT
be used to give access to procedures?
A: Yes:
GRANT EXECUTE ON my_procedure TO app_user;
Q2: What happens if a privilege is granted twice?
A: Oracle doesn’t duplicate privileges; the grant is just maintained once.
Q3: Can a user revoke a privilege they didn’t give?
A: ❌ No. Only admin or the user who granted with GRANT OPTION
can revoke.
Q4: What’s the purpose of roles in Oracle?
A: A role is a named group of privileges that can be granted to users to simplify management.
CREATE ROLE reporting_role;
GRANT SELECT ON employees TO reporting_role;
GRANT reporting_role TO analyst;
🧠 DCL Summary Table
Command | Use | Object-Level? | System-Level? | Can Cascade? | Common Use Case |
---|---|---|---|---|---|
GRANT | Gives privileges | ✅ Yes | ✅ Yes | ✅ With GRANT OPTION | Giving access to tables, sessions |
REVOKE | Removes privileges | ✅ Yes | ✅ Yes | ✅ Yes (cascading) | Revoking outdated or misused access |
🔐 Privilege Types Summary
Privilege Type | Examples | Applies To |
---|---|---|
System Privileges | CREATE SESSION , CREATE TABLE , DROP USER | Whole database |
Object Privileges | SELECT , INSERT , UPDATE , EXECUTE | Tables, Views, Procedures |
Role-Based | GRANT analyst_role TO userX; | Aggregated access |
💡 Real-World Scenario:
Requirement: Let a reporting user only view employee data, and nothing else.
-- Step 1: Create the user
CREATE USER report_user IDENTIFIED BY strongpass;
GRANT CREATE SESSION TO report_user;
-- Step 2: Grant access only to EMPLOYEES table
GRANT SELECT ON employees TO report_user;
📝 Bonus Practice Questions
- Grant SELECT & UPDATE on ORDERS to userX.
- Revoke INSERT on CUSTOMERS from userY.
- Grant EXECUTE on procedure
calc_bonus
tohr_analyst
. - Create a role
finance_readonly
that grants SELECT on 3 tables and assign it to 2 users. - Explain
WITH GRANT OPTION
behavior when revoked.
Leave a Reply