SQL Tutorial for Interviews- Types of SQL /Spark SQL commands- DDL, DML, TCL, CRUD in SQL

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?

FeatureDELETETRUNCATEDROP
RollbackYes❌ No❌ No
Table staysYesYes (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)

  1. What is the difference between TRUNCATE and DELETE?
  2. Write a DDL to create a table from another table’s structure and data.
  3. How do you rename a column in Oracle?
  4. When would you use SET UNUSED instead of DROP COLUMN?
  5. How do you make a table READ-ONLY?

✅ Summary Table

DDL CommandPurposeKey Use CaseInterview Tip
CREATEDefine structureNew tables, CTASWatch out for constraint exclusions
ALTERModify structureAdd/modify/drop columnsRename columns carefully
DROPRemove objectsDeleting obsolete tablesUse with caution
TRUNCATERemove data onlyFast full deletionCan’t be rolled back
COMMENTDocumentationAdd info for developersCheck in data dictionary
RENAMERename objectsRefactor tablesCannot rename columns via RENAME
SET UNUSEDHide columnsSafe drop laterGood for phased removals
READ ONLYLock dataFreeze archived dataOverrides 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?

FeatureLOCK TABLESELECT ... FOR UPDATE
Lock ScopeEntire tableOnly selected rows
Use CaseFull-table DML isolationRow-level locking (e.g., web apps)
Common InETL, batch opsOLTP, real-time updates

🧠 INTERVIEW QUESTIONS & ANSWERS – DML

  1. Q: What’s the difference between TRUNCATE and DELETE?
    • A: TRUNCATE is DDL, faster, non-transactional. DELETE is DML, slower, transactional.
  2. Q: Can you use MERGE without WHEN MATCHED?
    • A: Yes, just omit the WHEN MATCHED part; acts as INSERT ONLY.
  3. Q: What happens if an INSERT violates a unique constraint?
    • A: Oracle throws ORA-00001: unique constraint violated.
  4. 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' );
  5. Q: Difference between DELETE and DROP?
    • DELETE: removes data, table structure remains.
    • DROP: removes data + structure + dependent objects.

✅ Summary Table – DML Quick Reference

CommandPurposeCan Rollback?Locks Rows?Common Use Case
INSERTAdd new rows✅ YesAdding records
UPDATEModify existing rows✅ Yes✅ (if used with WHERE)Salary, status updates
DELETERemove specific rows✅ Yes✅ (row-level)Soft delete pattern
MERGEConditional insert/update✅ YesUpsert in ETL/data sync
LOCK TABLELock table explicitlyN/AAvoid update conflicts
SELECT ... FOR UPDATERow-level lock on selectN/APrevent 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:

  1. COMMIT
  2. ROLLBACK
  3. SAVEPOINT
  4. ROLLBACK TO SAVEPOINT
  5. 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

CommandDescriptionCan Rollback?Notes
COMMITSave changes permanently❌ NoAuto-commit in DDL
ROLLBACKUndo all uncommitted changes✅ YesUndoes the whole transaction
SAVEPOINTCreate a logical point in a transactionN/AUsed for partial rollback
ROLLBACK TOUndo changes after a SAVEPOINT✅ PartialEarlier changes stay intact
SET TRANSACTIONSet isolation level or access modeN/AUse 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:

  1. GRANT
  2. REVOKE
  3. (Oracle also uses ROLE and AUDIT, 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 on EMPLOYEES)

✅ 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

CommandUseObject-Level?System-Level?Can Cascade?Common Use Case
GRANTGives privileges✅ Yes✅ Yes✅ With GRANT OPTIONGiving access to tables, sessions
REVOKERemoves privileges✅ Yes✅ Yes✅ Yes (cascading)Revoking outdated or misused access

🔐 Privilege Types Summary

Privilege TypeExamplesApplies To
System PrivilegesCREATE SESSION, CREATE TABLE, DROP USERWhole database
Object PrivilegesSELECT, INSERT, UPDATE, EXECUTETables, Views, Procedures
Role-BasedGRANT 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

  1. Grant SELECT & UPDATE on ORDERS to userX.
  2. Revoke INSERT on CUSTOMERS from userY.
  3. Grant EXECUTE on procedure calc_bonus to hr_analyst.
  4. Create a role finance_readonly that grants SELECT on 3 tables and assign it to 2 users.
  5. Explain WITH GRANT OPTION behavior when revoked.

Pages: 1 2 3 4 5

Leave a Reply

Your email address will not be published. Required fields are marked *