- SQL (Structured Query Language): A declarative, non-procedural language used to interact with relational databases. It allows users to perform operations like fetching, inserting, updating, and deleting data using single statements or blocks. It lacks control structures like loops or conditional branches.
- PL/SQL (Procedural Language extension to SQL): Oracle's proprietary procedural extension. It combines the data manipulation power of SQL with processing structures like loops (
FOR,WHILE), conditional statements (IF-THEN-ELSE), exception handling, and modular constructs like functions, procedures, and packages.
- DELETE: A Data Manipulation Language (DML) command. It removes rows line-by-line based on a
WHEREcondition. It fires delete triggers, generates undo/redo logs, and can be rolled back. It does not release the storage space allocated to the table. - TRUNCATE: A Data Definition Language (DDL) command. It removes all rows from a table instantly by deallocating the data pages. It cannot be rolled back (implicitly commits), does not fire triggers, and resets the table's High Water Mark (HWM) to reclaim space.
- DROP: A Data Definition Language (DDL) command. It removes the entire table structure, data, indexes, constraints, and privileges from the database. It cannot be rolled back unless flashback is enabled.
- VARCHAR: A standard ANSI SQL data type designed for variable-length character strings. In Oracle, it is currently synonymous with
VARCHAR2, but Oracle recommends avoiding it because its behavior might change in future releases to comply with evolving ANSI standards. - VARCHAR2: Oracle’s proprietary data type used to store variable-length alphanumeric text. It optimizes storage by saving only the exact bytes of data entered (up to 4000 bytes in standard SQL, or 32767 bytes if extended data types are enabled).
- CHAR: A fixed-length character data type. If you declare
CHAR(10)and insert the string'Basha', Oracle pads the remaining 5 characters with blank spaces. It takes up the maximum specified space regardless of input length. - VARCHAR2: A variable-length character data type. If you declare
VARCHAR2(10)and insert'Basha', it consumes exactly 5 bytes of storage. No padding is added.
Pseudo-columns behave like table columns but are not actually stored in the table. You can select data from them, but you cannot insert, update, or delete their values.
- Examples:
ROWID,ROWNUM,LEVEL,NEXTVAL,CURRVAL.
- ROWNUM: A temporary sequential dynamic number assigned to the rows returned by a query result set. It starts at 1 and changes depending on how the rows are filtered or ordered. It is determined before the
ORDER BYclause is executed. - ROWID: A permanent, globally unique physical identifier representing the exact storage address of a row within a data file, block, and slot. It never changes for a row unless the row is moved or the table is rebuilt.
DUAL is a special, single-column table automatically created by Oracle in the SYS schema. It contains exactly one row with a value of 'X'. It is used to evaluate scalar expressions, execute built-in functions, or select pseudo-columns when a physical database table isn't required.
SELECT SYSDATE, USER, 5 * 10 FROM DUAL;- DDL (Data Definition Language): Defines or modifies database structures (
CREATE,ALTER,DROP,TRUNCATE,RENAME). - DML (Data Manipulation Language): Manages data within objects (
SELECT,INSERT,UPDATE,DELETE,MERGE). - DCL (Data Control Language): Manages permissions (
GRANT,REVOKE). - TCL (Transaction Control Language): Manages transaction flows (
COMMIT,ROLLBACK,SAVEPOINT).
Integrity constraints enforce business rules and prevent invalid data entry into the database. The six standard types are:
- NOT NULL: Restricts a column from accepting null values.
- UNIQUE: Ensures all values in a column or combination of columns are unique (allows multiple NULLs).
- PRIMARY KEY: Uniquely identifies each row; enforces unique and not null properties automatically.
- FOREIGN KEY: Enforces a referential link between columns across tables.
- CHECK: Validates that data satisfies a specific boolean condition.
- REF: Defines reference types representing logical pointers to object rows.
- A table can have only one Primary Key.
- A table can have multiple Unique Keys.
- Additionally, Primary Keys do not allow
NULLvalues, whereas Unique Keys permit multipleNULLentries in Oracle.
Oracle executes an implicit COMMIT immediately before and immediately after any DDL statement. This means any active, pending DML transactions in your current session will be permanently written to the database and cannot be rolled back.
- WHERE: Filters individual rows before any groupings are formed. It cannot evaluate or contain aggregate functions like
SUM(),AVG(), orCOUNT(). - HAVING: Filters summarized groups after the
GROUP BYclause is evaluated. It is explicitly designed to filter on aggregated values.
A subquery is a nested query block enclosed in parentheses inside an outer SQL statement.
- Single-Row Subquery: Returns exactly one row and one column; uses scalar operators like
=,>,<. - Multi-Row Subquery: Returns multiple rows; uses set operators like
IN,ANY,ALL. - Multiple-Column Subquery: Returns more than one column to the outer query.
- Correlated Subquery: References columns from the outer query, executing once for every row processed by the outer expression.
- Regular Subquery: Evaluates independently once, passing its fixed result back to the outer query block.
- Correlated Subquery: References parent columns from the outer query context. The inner query re-evaluates for every single candidate row considered by the outer query, matching the correlation condition dynamically.
Analytical functions compute an aggregate value based on a group of rows (called a window) without collapsing the result set into a single row. Unlike standard aggregations with GROUP BY, every row in the output maintains its individual identity.
- Syntax pattern:
FUNCTION() OVER (PARTITION BY col1 ORDER BY col2) - Examples:
ROW_NUMBER(),RANK(),DENSE_RANK(),LEAD(),LAG().
Suppose we have duplicate values for scores: [95, 95, 90, 85].
- ROW_NUMBER(): Assigns a unique, consecutive integer sequence regardless of ties. Result:
1, 2, 3, 4. - RANK(): Assigns the same rank to identical values, but skips subsequent rank numbers to match total positions. Result:
1, 1, 3, 4. - DENSE_RANK(): Assigns the same rank to identical values without skipping any numbers. Result:
1, 1, 2, 3.
- LAG(): Provides access to data from a previous row in the same result set at a designated physical offset without performing a self-join.
- LEAD(): Provides access to data from a subsequent row ahead in the same result set at a designated offset.
A DB Link is a schema object that enables a session in one Oracle database instance to securely access objects (like tables or views) in a completely separate, remote Oracle database instance using a Net Service network connection string.
SELECT * FROM employees@remote_db_link;- UNION: Combines the distinct outputs of two or more queries. It sorts the combined result set to eliminate all duplicate rows. This incurs a performance overhead.
- UNION ALL: Merges outputs directly without sorting or filtering. It preserves all duplicate rows, making it significantly faster than
UNION.
Joins combine columns from two or more tables based on logical relationships.
- Inner Join: Returns rows only when the join condition is satisfied in both tables.
- Left Outer Join: Returns all rows from the left table and matching rows from the right table. If no match is found, NULLs are returned.
- Right Outer Join: Returns all rows from the right table and matching rows from the left table.
- Full Outer Join: Returns all rows from both tables, filling with NULLs where matches don't align.
- Cross Join (Cartesian Product): Joins every single row of the first table to every row of the second table.
- View: A virtual table based on a saved SQL query stored in the data dictionary. It does not contain physical data of its own.
- Inline View: A subquery enclosed in parentheses within the
FROMclause of an outer query, acting dynamically as a temporary table for the duration of that single execution.
- Standard View: A stored query definition. Every time it is queried, Oracle must rerun the underlying SQL statements against base tables.
- Materialized View: Physically stores the query result set in disk blocks. It speeds up intensive aggregations and joins on large datasets. It can be refreshed on demand, at scheduled intervals, or on changes (
FAST,COMPLETE,FORCE).
An index is a database performance structure that speeds up rows retrieval by providing a quick path to data blocks instead of performing a costly full table scan.
- B-Tree Index: The default index type. Ideal for high-cardinality columns (unique or highly diverse values like primary keys).
- Bitmap Index: Ideal for low-cardinality columns (few distinct values like Gender, Status). It uses bit arrays to store mappings.
- Function-Based Index: Built on expressions or functions applied to columns (e.g.,
UPPER(last_name)).
A composite index (or concatenated index) is an index built across multiple columns of a single table. It is beneficial when those columns are frequently combined within the WHERE clause of complex search operations using AND logic.
- NVL(exp1, exp2): Returns
exp2ifexp1evaluated to aNULLvalue. - NVL2(exp1, exp2, exp3): Evaluates
exp1. If it is NOTNULL, it returnsexp2. If it isNULL, it returnsexp3. - COALESCE(v1, v2, ..., vn): Evaluates arguments sequentially and returns the first non-null expression in the sequence.
- NULLIF(exp1, exp2): Compares two arguments. Returns
NULLif they are equal; otherwise, returnsexp1.
- Unique Constraint: A declarative database model requirement enforcing data uniqueness.
- Unique Index: A physical storage structure built to enforce that uniqueness. Creating a unique constraint automatically triggers Oracle to build an underlying unique B-Tree index if one does not already exist.
A self-join is an inner or outer join where a table is joined to itself. This requires defining distinct alias names for the table within the query context.
- Use Case: Querying an organizational employee table where each row contains a
manager_idreferencing theemployee_idwithin the same table.
Aggregate functions perform a calculation on a set of values and return a single summarizing metric. They ignore NULL values (except COUNT(*)).
- Examples:
SUM(),AVG(),COUNT(),MAX(),MIN(),STDDEV(),VARIANCE().
The MERGE statement (also called an "upsert") conditionally updates or inserts rows into a target table using a source dataset. It checks a matching condition: if a match exists, it updates the target row; if no match exists, it inserts a new row.
A Sequence is a schema object that generates a sequential list of unique integers based on configuration rules.
- Accessing values uses pseudo-columns:
sequence_name.NEXTVAL: Increments and returns the next value.sequence_name.CURRVAL: Returns the current session's last fetched value.
A Synonym is an alias or alternative pointer name assigned to a database object like a table, view, sequence, or program unit.
- Private Synonym: Accessible only to the schema owner and explicitly authorized database users.
- Public Synonym: Created by a DBA and accessible to all valid users within the database instance.
A transaction is a logical unit of work consisting of one or more SQL statements. It must satisfy the ACID properties:
- Atomicity: All statements succeed completely, or the entire transaction is rolled back.
- Consistency: Keeps database states aligned with structural constraints.
- Isolation: Uncommitted modifications made in one session are hidden from concurrent sessions.
- Durability: Committed transactions are permanently saved to disk, even through system failures.
A SAVEPOINT creates a marker within an active transaction. It allows you to roll back a specific portion of your uncommitted changes without abandoning the entire transaction sequence.
SAVEPOINT step_one;
-- some statements
ROLLBACK TO step_one;- Implicit Cursor: Created and managed automatically by Oracle whenever a single DML statement or a
SELECT INTOquery executes. - Explicit Cursor: Created, named, and managed manually by a developer in PL/SQL blocks for multi-row queries using
CURSOR,OPEN,FETCH, andCLOSE.
The LIKE operator performs pattern matching on text strings:
- Percent Sign (
%): Matches zero or more characters. - Underscore (
_): Matches exactly one character position.
DECODE is an Oracle-proprietary conditional function that acts similarly to an inline IF-THEN-ELSE statement. It evaluates an expression against a list of search values and returns the corresponding result.
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM dual;- DECODE: An Oracle-proprietary function that only performs equality comparisons. It cannot handle complex logical conditions or range evaluations (like
<or>). - CASE: An ANSI-compliant, versatile standard expression. It handles complex conditional logic, range comparisons, and can combine multiple criteria using
AND/OR. It is supported across both SQL and PL/SQL blocks.
A Flashback query allows developers to view data exactly as it existed at a specific point in time or a known System Change Number (SCN) in the past, utilizing undo tablespace segments.
SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2026-07-03 10:00:00', 'YYYY-MM-DD HH24:MI:SS');A Cartesian product (Cross Join) occurs when rows from one table are combined with all rows of another table without any matching filter criteria. This returns a result set size equal to (Rows in Table A) × (Rows in Table B). It typically happens when a join condition is omitted or malformed.
The High Water Mark is a boundary marker within an Oracle segment that defines the highest limit of formatted disk blocks allocated to a table that have ever contained data. Full table scans read all blocks up to the HWM, even if those blocks were completely emptied by a large DELETE operation.
- Database: The collection of physical operating system files located on disk storage (Data files, Control files, Redo Log files).
- Instance: The combination of background processes and memory structures (SGA - System Global Area) allocated in RAM when an Oracle database engine starts up.
- ANY: Compares a value against any single result in a list. It evaluates to true if at least one match satisfies the condition (e.g.,
> ANY (10, 20)means greater than 10). - ALL: Compares a value against every single result in a list. It evaluates to true only if all values satisfy the condition (e.g.,
> ALL (10, 20)means greater than 20).
- IN: The inner subquery executes first, collecting its entire list of values into memory before filtering the outer query. It is efficient for small lookup datasets but struggles if the subquery returns a massive result set.
- EXISTS: The outer query processes row by row. It checks the subquery, which stops processing as soon as it finds the first matching row. It is generally more efficient for large datasets when the subquery filters heavily.
INSTR searches a string for a specified substring and returns the 1-based integer index position of its first occurrence. If the substring is not found, it returns 0.
SELECT INSTR('Shaik Mahaboob Basha', 'Basha') FROM dual; -- Returns 16SUBSTR extracts a specific portion of a character string based on a starting position and a defined length argument.
SELECT SUBSTR('OracleSQL', 7, 3) FROM dual; -- Returns 'SQL'Foreign Key, Unique, and Check constraints can be declared as DEFERRABLE. This allows you to delay validation checking until the end of a transaction when a COMMIT is issued, rather than validating row-by-row during active execution.
- GRANT: A DCL statement that assigns system privileges or specific object permissions to a database user or role.
- REVOKE: A DCL statement that removes previously assigned privileges or permissions.
- TO_DATE: Converts a character string representation into an Oracle internal
DATEdata type using a specific format mask. - TO_CHAR: Converts a numeric or date value into a formatted character string for display purposes.
A mutating table error (ORA-04091) occurs in row-level triggers when the trigger tries to query or modify the same physical table that is currently undergoing DML changes from the statement that fired the trigger.
An Execution Plan is the sequential set of steps generated by the Oracle Cost-Based Optimizer (CBO) to execute a SQL statement. It outlines details like join orders, index utilization, and table scanning methods, helping developers identify performance bottlenecks.
The following queries assume standard organizational tables: employees (emp_id, name, salary, dept_id, hire_date, manager_id) and departments (dept_id, dept_name, location).
SELECT SYSDATE FROM dual;
-- Or for high precision with timezone:
SELECT SYSTIMESTAMP FROM dual;SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
) WHERE rank = 3;53. Write a query to find the Nth highest salary from an employees table using a correlated subquery.
SELECT DISTINCT e1.salary
FROM employees e1
WHERE n = (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary >= e1.salary
);SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;55. Write a query to delete duplicate rows from a table while keeping only the original row (lowest ROWID).
DELETE FROM employees
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM employees
GROUP BY email
);-- In Oracle 12c and above:
SELECT * FROM employees
ORDER BY emp_id
FETCH FIRST 5 ROWS ONLY;
-- In older versions:
SELECT * FROM (
SELECT * FROM employees ORDER BY emp_id
) WHERE rownum <= 5;SELECT * FROM (
SELECT * FROM employees ORDER BY emp_id DESC
) WHERE rownum <= 5
ORDER BY emp_id ASC;SELECT * FROM employees
WHERE name LIKE 'S%';SELECT UPPER(RTRIM(name)) AS formatted_name FROM employees;SELECT COUNT(*) FROM employees WHERE dept_id = 10;SELECT dept_id FROM (
SELECT dept_id, COUNT(*) as emp_count
FROM employees
GROUP BY dept_id
ORDER BY emp_count DESC
) WHERE rownum = 1;SELECT * FROM employees
WHERE hire_date >= TRUNC(SYSDATE, 'YYYY')
AND hire_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12);SELECT e.name AS Employee, m.name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);65. Write a query to find employees who earn more than the average salary of their respective departments.
SELECT * FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);
-- Alternative analytical approach:
SELECT * FROM (
SELECT e.*, AVG(salary) OVER (PARTITION BY dept_id) as dept_avg
FROM employees e
) WHERE salary > dept_avg;-- Using NOT EXISTS:
SELECT d.* FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);SELECT MIN(salary) FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees);SELECT * FROM (
SELECT e.*, ROWNUM rnum FROM employees e
) WHERE MOD(rnum, 2) <> 0;SELECT * FROM (
SELECT e.*, ROWNUM rnum FROM employees e
) WHERE MOD(rnum, 2) = 0;SELECT emp_id, name, NVL(commission, 0) AS commission FROM employees;SELECT LENGTH('Shaik Mahaboob Basha') FROM dual;SELECT SUBSTR(name, 1, 3) FROM employees;SELECT INSTR('Shaik Mahaboob Basha', 'M') FROM dual;SELECT first_name || ' ' || last_name AS full_name FROM employees;SELECT dept_id, SUM(salary) AS total_expense
FROM employees
GROUP BY dept_id;76. Write a query to display employee details ordered by department ID ascending and salary descending.
SELECT * FROM employees
ORDER BY dept_id ASC, salary DESC;SELECT emp_id, name, TRUNC(SYSDATE - hire_date) AS days_completed
FROM employees;SELECT name, ADD_MONTHS(hire_date, 6) AS review_date FROM employees;SELECT LAST_DAY(SYSDATE) FROM dual;SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;81. Write a query to check if a table EMP_TEMP exists before creating it (Conditional Drop workaround).
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'EMP_TEMP';
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE EMP_TEMP';
END IF;
END;
/UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = 20;83. Write a query using MERGE to synchronize an emp_source staging table into the target employees table.
MERGE INTO employees t
USING emp_source s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN
UPDATE SET t.salary = s.salary, t.dept_id = s.dept_id
WHEN NOT MATCHED THEN
INSERT (emp_id, name, salary, dept_id)
VALUES (s.emp_id, s.name, s.salary, s.dept_id);SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 70000;SELECT * FROM employees
WHERE commission IS NOT NULL;SELECT MIN(salary) as min_sal, MAX(salary) as max_sal, AVG(salary) as avg_sal
FROM employees;87. Write a query to find the top 3 earning employees within each department using analytic functions.
SELECT * FROM (
SELECT emp_id, name, salary, dept_id,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank
FROM employees
) WHERE rank <= 3;88. How do you find the total number of characters in a table column without counting space paddings?
SELECT SUM(LENGTH(TRIM(name))) FROM employees;89. Write a query to display a mask pattern for employee phone numbers (e.g., converting '1234567890' to '******7890').
SELECT emp_id, '******' || SUBSTR(phone_number, -4) AS masked_phone
FROM employees;SELECT * FROM employees
WHERE LENGTH(name) = 5;
-- Alternative using wildcards:
SELECT * FROM employees
WHERE name LIKE '_____';SELECT name FROM employees
WHERE name LIKE '_a%';SELECT table_name FROM user_tables;SELECT column_name, data_type, data_length, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';SELECT name, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date)) AS total_months_worked
FROM employees;95. Write a query to display employee data with a flag indicating if they earn above or below 50,000 using a CASE expression.
SELECT name, salary,
CASE
WHEN salary >= 50000 THEN 'High Earner'
ELSE 'Standard Earner'
END AS salary_bracket
FROM employees;96. Write a query to display records from the employees table where the row values are identical across all columns (Full row duplicate check).
SELECT emp_id, name, salary, dept_id, COUNT(*)
FROM employees
GROUP BY emp_id, name, salary, dept_id
HAVING COUNT(*) > 1;SELECT SUBSTR(name, 1, INSTR(name || ' ', ' ') - 1) AS first_word
FROM employees;98. How do you force a query to skip the default cost-based optimizer choices and execute a full table scan?
SELECT /*+ FULL(e) */ *
FROM employees e;99. Write a query to convert numerical IDs to words/characters by padding zeros (e.g., ID 458 to '00458').
SELECT TO_CHAR(emp_id, 'FM00000') AS padded_id FROM employees;100. Write a query to determine which day of the week an employee was hired (e.g., Monday, Tuesday).
SELECT name, TO_CHAR(hire_date, 'Day') AS hire_day_name FROM employees;