SQL Developer interview questions and answers

A self join joins a table to itself, using two aliases so each copy can play a different role. In an employee table where manager_id holds the emp_id of the person's manager: SELECT e.name AS employee, m.name AS manager FROM employee e LEFT JOIN employee m ON e.manager_id = m.emp_id.

The LEFT JOIN matters: the head of the company has no manager, and an inner join would drop that row. Self joins also compare rows within one table, such as finding employees who earn more than their manager, with an inner join and WHERE e.salary > m.salary. For a full reporting chain of any depth, you'd use a recursive CTE instead.

NOT IN (1, 2, NULL) means value <> 1 AND value <> 2 AND value <> NULL. Any comparison with NULL gives UNKNOWN, not TRUE, so the whole condition can never be TRUE and every row is filtered out. For example, finding employees who manage nobody with WHERE emp_id NOT IN (SELECT manager_id FROM employee) returns nothing, because the head of the company has a NULL manager_id.

Fix it with NOT EXISTS, which checks for matching rows and isn't affected by NULLs, or add WHERE manager_id IS NOT NULL inside the subquery. The same three-valued logic is why you must write IS NULL rather than = NULL.

A correlated subquery reads naturally: SELECT e.name, e.salary FROM employee e WHERE e.salary > (SELECT AVG(d.salary) FROM employee d WHERE d.dept_id = e.dept_id). Logically it runs once per outer row, though most optimisers rewrite it.

An alternative is to calculate each department's average once in a derived table, SELECT dept_id, AVG(salary) AS avg_sal FROM employee GROUP BY dept_id, and join it to employee on dept_id with salary > avg_sal. A window function, AVG(salary) OVER (PARTITION BY dept_id), inside a CTE also works and reads the table once. On a large table, compare the execution plans rather than assuming one form is always faster.

Use MERGE, which Oracle and SQL Server both support: MERGE INTO customer t USING stg_customer s ON (t.customer_id = s.customer_id) WHEN MATCHED THEN UPDATE SET t.name = s.name, t.city = s.city WHEN NOT MATCHED THEN INSERT (customer_id, name, city) VALUES (s.customer_id, s.name, s.city). It does both in one statement instead of a row-by-row loop.

The common failure is duplicates in the source: if staging has two rows for one customer, Oracle raises ORA-30926 and SQL Server complains that a target row was updated more than once. De-duplicate staging first, for example keeping the latest row with ROW_NUMBER. In SQL Server, end MERGE with a semicolon; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE instead.

Yes, a stored function can be used in SQL like a built-in one, for example SELECT invoice_no, calc_gst(amount) FROM invoices, as long as it follows some rules: only IN parameters, a return type that SQL can use, and no changes to data. A function that runs an INSERT or UPDATE fails with ORA-14551 when called from a query, and it can't commit either.

A procedure can never be called inside a SELECT. It's run as a statement, from a PL/SQL block or with EXEC, returns results through OUT parameters, and suits operations such as posting a payment. So use functions for reusable calculations and lookups, and procedures for business operations that change data.

A cursor is a pointer to the result set of a query, which lets PL/SQL process it row by row. Oracle opens an implicit cursor for every SQL statement you run directly, such as an UPDATE or a SELECT INTO, and you can check SQL%ROWCOUNT afterwards to see how many rows it affected. An explicit cursor is one you declare for a multi-row query, then OPEN, FETCH in a loop and CLOSE.

The attributes are %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN. A cursor FOR loop opens, fetches and closes for you, so it's the simplest and safest form. Mention that row-by-row processing is slow for large volumes, where a single SQL statement or BULK COLLECT with FORALL is much faster.

A package groups related procedures, functions, types, variables and cursors under one name. It has two parts: the specification, which declares what other code may call, and the body, which holds the implementation and any private helpers.

The benefits: related code stays together, such as a loan_pkg with procedures for disbursement, EMI calculation and closure; private routines stay hidden; you can overload a procedure name with different parameters; package variables keep their values for the whole session; and the package loads into memory as one unit on first use. Most usefully, if you change only the body, code that calls the package isn't invalidated, because it depends only on the specification.

Catch the specific errors you can deal with, such as NO_DATA_FOUND when a SELECT INTO finds no row, TOO_MANY_ROWS or DUP_VAL_ON_INDEX, and handle each meaningfully. For business rule failures, raise a clear error the application can show, using RAISE_APPLICATION_ERROR with a code between -20000 and -20999, for example -20001 with the message 'Insufficient balance'.

The biggest mistake is WHEN OTHERS THEN NULL, which hides the error and lets the caller think everything worked. If you use WHEN OTHERS, log SQLCODE, SQLERRM and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, which gives the failing line, then RAISE again. Also decide who commits: usually the calling application, not code deep inside procedures.

A row-level trigger can't query or change the table that fired it, because that table is in the middle of being changed and the result would depend on the order in which rows are processed. A typical case is an AFTER UPDATE FOR EACH ROW trigger on orders that runs SELECT SUM(amount) FROM orders to check a customer's credit limit.

Fixes: use a compound trigger (Oracle 11g and later), collecting the affected keys in the row-level section and running the query in the AFTER STATEMENT section; use a statement-level trigger; or, often best, move the rule into the procedure that does the update. Mention that heavy logic in triggers is hard to debug and slows bulk loads.

A heap is a table with no clustered index: rows are stored in no particular order, and non-clustered indexes point to them by a row ID (file, page and slot). A clustered index stores the table's rows in the order of its key, so the leaf level of the index is the data itself, and a table can have only one. SQL Server creates a clustered index on the primary key by default unless one already exists.

Most tables benefit from one because range queries on the key are efficient, and heaps suffer from forwarded records when updated rows no longer fit on their page. A good clustered key is narrow, unique, unchanging and ever-increasing, such as an identity column, because every non-clustered index also stores it.

A B-tree index is a balanced tree of pages. The root and branch pages hold key ranges that point down to the next level, and the leaf pages hold the sorted keys with pointers to the rows. To find order_id 50,000, the database reads the root, follows one branch at each level and reaches the right leaf, usually in three or four page reads even for millions of rows, instead of scanning the whole table.

Because the leaves are kept in sorted order and linked to each other, a range such as order_date BETWEEN '2026-01-01' AND '2026-01-31' finds the first matching leaf and then reads forward until the range ends. The same sorted order can also avoid a separate sort for ORDER BY.

A composite index is sorted by the first column, then by the second within each value of the first, like a phone book sorted by surname and then first name. So it supports WHERE customer_id = 101, and WHERE customer_id = 101 AND order_date >= '2026-01-01', very well, and can even return that customer's orders already sorted by date.

A filter on order_date alone can't seek into it, because the dates are scattered across all customers, so it usually scans instead. Oracle can sometimes use an index skip scan when the first column has few distinct values. The rule of thumb: put the columns filtered by equality first and the range column last, and design indexes around your most important queries.

A covering index contains every column a query needs, so the database answers it from the index alone without going back to the table. A key lookup in a SQL Server plan means a non-clustered index found the rows, but for each one the engine went to the clustered index to fetch columns that weren't in the index. For a few rows that's fine; for thousands it's expensive.

The fix is to add the missing columns, usually as included columns: CREATE INDEX ix_orders_cust ON orders (customer_id, order_date) INCLUDE (amount, status). Included columns sit only at the leaf level, so they don't widen the key. Don't include half the table, though: a wide index costs storage and slows every write.

Wrapping the column in a function makes the condition non-sargable: the index is sorted by order_date, not by YEAR(order_date), so the database has to calculate the year for every row. Rewrite it as a range: order_date >= '2025-01-01' AND order_date < '2026-01-01'.

Other common causes: a leading wildcard such as LIKE '%kumar'; an implicit conversion, such as comparing a number column with a string, which can force a scan; arithmetic on the column, such as amount * 1.18 > 1000; and a filter that matches a large share of the table, where a scan really is cheaper. Stale statistics can also make the optimiser choose a scan, so check the plan rather than guessing.

All three number rows in the order given in OVER (ORDER BY ...), restarting for each PARTITION BY group if you add one. They differ only on ties. For salaries 90,000, 90,000 and 80,000 in descending order: ROW_NUMBER gives 1, 2, 3, choosing an arbitrary order for the tie; RANK gives 1, 1, 3, leaving a gap after the tie; DENSE_RANK gives 1, 1, 2, with no gap.

Use ROW_NUMBER when you need exactly one row per group, such as the latest order per customer, and DENSE_RANK for "top three salaries" where ties should share a place. Add a tie-breaker column to ROW_NUMBER's ORDER BY so the result is the same every time.

Number the rows within each email and delete everything after the first. In SQL Server you can delete through a CTE: WITH d AS (SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn FROM customer) DELETE FROM d WHERE rn > 1. This keeps the earliest row for each email.

In Oracle, a common way is DELETE FROM customer WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM customer GROUP BY email). Before deleting, run the same logic as a SELECT to see what will go, check whether other tables reference the rows you're removing, and afterwards add a unique constraint on email so the duplicates can't come back.

SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) adds each transaction to everything before it in the same account. To show the previous transaction alongside, LAG(amount) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id) returns the earlier row's value.

If you write only ORDER BY txn_date with no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same txn_date as peers, so two transactions on the same day both show the day's closing balance instead of stepping up one at a time. So specify ROWS explicitly and add a unique tie-breaker such as txn_id. In SQL Server, ROWS is usually faster too.

1NF: each column holds one value and there are no repeating groups, so no column like phone_numbers holding two numbers separated by a comma; put phones in their own table. 2NF: in a table with a composite key, every non-key column depends on the whole key. In order_items(order_id, product_id, quantity, product_name), product_name depends only on product_id, so it belongs in products. 3NF: no non-key column depends on another non-key column. In employee(emp_id, dept_id, dept_name), dept_name depends on dept_id, so move it to a department table.

The goal is to store each fact once, so an update can't leave two copies disagreeing. Reporting tables are sometimes denormalised on purpose for speed.

A primary key identifies each row: it must be unique and NOT NULL, and a table has only one, though it can span several columns. A unique key also prevents duplicates, such as on email or PAN, and a table can have several. How it treats NULLs depends on the database: SQL Server allows only one NULL in a unique column, while Oracle and PostgreSQL allow many.

A foreign key makes a column refer to a primary or unique key in another table, so an order can't point to a customer who doesn't exist, and by default the parent row can't be deleted while child rows refer to it. Mention that foreign key columns should usually be indexed, for joins and for deletes on the parent.

Money: DECIMAL or NUMERIC with a fixed scale, such as DECIMAL(18,2), never FLOAT, because floating point can't store values like 0.10 exactly and totals drift by a paisa. Dates: a DATE, DATETIME2 or TIMESTAMP type, not a string, so sorting, ranges and date arithmetic work and invalid dates like 31 February are rejected; if users are in several time zones, store UTC or the offset. Phone and account numbers: VARCHAR, not a number, since nobody does arithmetic on them and they can have leading zeros or a + sign.

Keep sizes sensible, since oversized columns waste memory in sorts, and use the same type on both sides of a join to avoid implicit conversions.

Partitioning splits one table into pieces by a key while queries still see a single table. Here, range partitioning on txn_date, one partition per month, is the natural choice. Queries that filter on txn_date then read only the relevant partitions (partition pruning), and old data can be archived by switching out or dropping a whole partition instead of running a huge DELETE.

But it isn't a cure for slow queries on its own: a query that doesn't filter on the partition key may have to touch every partition, so good indexes still matter. Choose the key from how the data is queried and purged. Also check licensing: in Oracle, partitioning is a separately licensed Enterprise Edition option.

Keep it to about a minute and make it about data and SQL. For example: "I'm a [degree] graduate from [college]. For the last [months] I've focused on databases: I write joins, window functions and stored procedures in [Oracle or SQL Server]. In my project, [project name], I designed [number] tables for a [sales or library] system, wrote procedures for [task] and brought a slow report down from [x] seconds to [y] by adding the right index. I enjoy making queries correct and fast, and I'd like to do that full time as a SQL developer here."

Have the project's tables and one query ready to explain, because the next question is often about it.

Show that it's a real choice, not a fallback, and that you know what the job involves: "I like problems where the answer has to be exactly right, and in my projects I enjoyed the database part most: designing tables, writing reports and tuning queries. Every application depends on its data, so SQL skills stay useful whatever the front end is. I also know some [Python or Java], which helps me understand how applications call the database."

Avoid saying you chose SQL because programming is hard: SQL development is programming, and the technical round will test it.

Show that you prioritise by impact and communicate, rather than just working faster: "I'd quickly ask each team what the report is for and when they really need it, because 'urgent' means different things. A report needed for a regulatory submission or a client meeting today comes before an internal review. If I can't decide between them, I'd ask my lead to set the order. Then I'd tell each team when they'll get theirs, and update them if anything slips."

As a fresher, add that you'd ask for help early rather than quietly missing all three deadlines.

Interviewers want honesty, quick action and what you changed afterwards. Use STAR: "In my last role I ran an UPDATE on a customer table in production with a condition wider than I meant, and about [number] rows changed (situation). I told my lead within minutes instead of trying to fix it quietly (task and action). We restored the affected rows from the audit table and the previous night's backup, and checked them with the business (result). Since then I always run the same condition as a SELECT first, work inside a transaction and get a second person to review production scripts."

Never claim you've never made a mistake; it doesn't sound believable.

Stay calm, don't argue, and treat it as a question to answer with data: "First I'd ask exactly which figure looks wrong, what they expected, and where their number comes from. Then I'd run my query for a small slice, such as one branch for one day, and compare it line by line with their source. Usually the difference is a definition, such as whether cancelled orders or returns are included. If my report is wrong, I fix it, tell them what changed and check other reports that use the same logic. If it's right, I explain the difference simply and agree the definition in writing."

This shows you care about accuracy, not about being right.