SQL Interview

Master Guide
Complete Prep: Basics → Advanced → Oracle-Specific

Designed for Data Engineers | Barclays BUK Context | Fidelity-Ready

SECTION 1 SECTION 2 SECTION 3

Basics Joins & Query Logic SQL Problems

SECTION 4 SECTION 5 BONUS

Window Functions Oracle-Specific Optimization & CTEs

Each answer is crafted to demonstrate deep conceptual thinking, real-world analogies, and comparisons with Ab
Initio / ETL patterns you already know — so your answers sound experienced, not textbook.

SQL Interview Master Guide | Page 1

 ■ SECTION 1: BASICS — Must Know

Q What is SQL and why is it used?

SQL (Structured Query Language) is the universal language for communicating with relational databases —
asking questions, inserting records, changing data, and controlling who can do what.

Real-world analogy:

Think of a database as a massive filing cabinet in a bank — like Barclays' customer records vault. SQL is the
librarian's request form: you write a precise request ("Fetch all customers who made a transaction above £10,000
in the last 30 days") and the database executes it. Without SQL, you'd have to open every drawer manually.

Why it matters in your context:

In the Barclays BUK platform, every Hive and Impala query you run against the Hadoop cluster is SQL. When the
Athena team queries Parquet files on S3, that's also SQL. The Snowflake consumption layer at the top of the
pipeline is pure SQL. SQL is the thread connecting every layer of a modern data platform.

■ Interview tip: Mention that SQL is declarative — you say WHAT you want, not HOW to get it. The database engine
figures out the execution plan. This shows you understand the abstraction, not just the syntax.

Q Difference between DBMS and RDBMS

A DBMS (Database Management System) stores and manages data, but has no enforced relationship between
tables. A RDBMS (Relational DBMS) organises data into related tables and enforces relationships through keys
and constraints.

DBMS Example: A file system or MongoDB (pre-relational NoSQL) — data lives in documents with no enforced links.
RDBMS Example: Oracle, PostgreSQL, SQL Server — data lives in tables that reference each other through
primary/foreign keys.

The critical difference — referential integrity:

In a RDBMS, if a Transactions table references a Customers table via customer_id, the database refuses to let
you insert a transaction for a customer who doesn't exist. A plain DBMS has no such guardrail. This is like Ab
Initio's Lookup component — it enforces a match relationship between datasets at runtime. RDBMS enforces it at
the database level, permanently.

■ Key phrase to use: 'RDBMS enforces referential integrity through constraints, ensuring data consistency across the
entire system — not just in individual queries.'

Q What are Primary Key and Foreign Key?

Primary Key:

A column (or combination) that uniquely identifies every row in a table. It cannot be NULL and must be unique.
Think of it as an Aadhaar number for each row — one person, one ID, no exceptions.

Foreign Key:

A column in one table that references the Primary Key of another table. It creates the 'relationship' in relational
databases.

SQL Interview Master Guide | Page 2

 -- Example: Barclays customer & account model CREATE TABLE Customers ( customer_id NUMBER
PRIMARY KEY, -- PK: unique per customer customer_name VARCHAR2(100) ); CREATE TABLE
Accounts ( account_id NUMBER PRIMARY KEY, customer_id NUMBER REFERENCES
Customers(customer_id), -- FK balance NUMBER(15,2) );

The FK ensures you cannot open an Account for a customer_id that doesn't exist in Customers. This is your data
integrity guardrail.

ETL comparison:

In Ab Initio, when you do a Lookup join between transaction records and a reference dataset (e.g., account
master), you are manually enforcing what a Foreign Key does automatically in a database. RDBMS does it
passively, 24x7, on every INSERT/UPDATE.

■ Composite PK: In a junction table (e.g., CustomerProducts), the PK can be (customer_id, product_id) together —
neither column alone is unique, but the combination is.

Q What is Normalisation?
Normalisation is the process of organising a database to reduce data redundancy and improve data integrity
by splitting large tables into smaller, related ones following a set of rules called Normal Forms (NF).

Real-world analogy:

Imagine one giant Excel sheet with customer name, address, account type, and transaction details all in one row
— customer name repeated 500 times across 500 transactions. If the customer moves cities, you update 500
rows. Normalisation splits this into a Customers table, an Accounts table, and a Transactions table — update city
in one row, everywhere reflects it.

The Normal Forms (simplified):

1NF — No repeating groups; each cell holds one atomic value (no comma-separated lists in a column). 2NF — 1NF +
no partial dependency (all non-key columns depend on the FULL primary key, not just part of it). 3NF — 2NF + no
transitive dependency (non-key columns depend ONLY on the primary key, not on other non-key columns).

ETL angle:

Source systems at Barclays (like legacy Teradata or Oracle) are typically normalised OLTP databases —
designed for write-heavy transactional workloads. Your ETL pipeline (Ab Initio ingestion layer) reads this
normalised data and transforms it, often denormalising it into flat structures for the consumption layer (Snowflake,
Databricks) which is optimised for reads.

Q What is Denormalisation?
Denormalisation is the intentional process of adding redundancy back into a normalised schema to speed up
read queries by reducing the number of JOINs needed at query time.

When is it used?

In OLAP systems (data warehouses, analytics), queries need to read millions of rows fast. Every JOIN adds
overhead. So instead of keeping CustomerName only in the Customers table, you pre-join and store it directly in
the Transactions table — even though it's 'redundant'. This is the philosophy behind flat fact tables in dimensional
modelling.

Your Barclays context:

SQL Interview Master Guide | Page 3

 The FDP (Financial Data Product) and CDP layers in the Barclays BUK 5-layer architecture are essentially
denormalised, consumption-ready views of the underlying raw and base data. The Snowflake/Iceberg
consumption layer stores wide, pre-joined tables so analysts can query without complex JOINs. That is
denormalisation by design.

■ The trade-off to mention: Normalisation = write efficiency + data integrity. Denormalisation = read efficiency +
query speed. A mature data platform uses BOTH — normalised at the source, denormalised at the consumption
layer.

Q Difference between DELETE, TRUNCATE, DROP

These three commands all remove data, but at very different levels — and with very different consequences.

Aspect DELETE TRUNCATE DROP

What it removes Specific rows (with WHERE) ALL rows in the table The entire table structure + data
or all rows (without WHERE)

WHERE clause ■ Yes ■ No ■ No

Rollback possible? ■ Yes (DML — logged) ■■ No in most DBs ■ No (DDL — auto-commit)

(Oracle: yes via segments)

Speed Slow (row-by-row logged) Very fast (deallocates pages) Instant

Table still exists? ■ Yes ■ Yes (empty) ■ Table is gone

Triggers fired? ■ Yes ■ No ■ No

■ Analogy: DELETE is erasing specific entries from a notebook. TRUNCATE is tearing out all the pages but keeping
the notebook cover. DROP is throwing the entire notebook in a shredder.

Q What is a VIEW?
A VIEW is a virtual table — a stored SQL SELECT query that behaves like a table when you query it. It doesn't
physically store data (in most cases); it re-runs the underlying query each time it's accessed.

-- Create a view of active Barclays accounts CREATE VIEW active_accounts AS SELECT

a.account_id, c.customer_name, [Link] FROM Accounts a JOIN Customers c ON a.customer_id
= c.customer_id WHERE [Link] = 'ACTIVE';

Now any analyst can query SELECT * FROM active_accounts without knowing the underlying JOIN logic. The
view hides complexity and enforces access control — you can grant SELECT on the view without exposing the
base tables.

Materialised View vs Regular View:

A regular view re-executes SQL every time. A Materialised View physically stores the result and refreshes
periodically — like a cached snapshot. This is similar to how Snowflake's dynamic tables or Databricks' Delta
tables cache aggregated results for performance. In Ab Initio terms, think of a regular view as a graph that runs
every time vs. a materialised view as a pre-computed output file stored on disk.

■ Interview angle: Views promote the principle of 'abstraction' — the same pattern used in your DTX framework
where the transformation logic is encapsulated in AWS Glue jobs, hidden from downstream consumers.

Q What is an INDEX and why is it used?

SQL Interview Master Guide | Page 4

An INDEX is a database performance structure — a separate data structure built on one or more columns that
allows the database to find rows without scanning the entire table, just like a book's index lets you jump to the
right page instead of reading cover to cover.

How it works internally:

Most indexes use a B-Tree structure — a sorted, balanced tree where each leaf points to the actual row location.
When you query WHERE customer_id = 12345, the database traverses the B-Tree (O(log n) operations) instead
of scanning all rows (O(n)).

-- Without index: Full table scan (slow for millions of rows) SELECT * FROM transactions
WHERE account_id = 987654; -- Create index on frequently queried column CREATE INDEX
idx_txn_account ON transactions(account_id); -- Now the query uses the index — much faster

The trade-off (critical to mention):

Indexes speed up SELECT but slow down INSERT/UPDATE/DELETE because the index structure must also be
updated on every write. A table with 20 indexes on a write-heavy OLTP system will be slow. This is why data
warehouses (Barclays' Snowflake layer) use clustering keys and micro-partitioning instead of traditional indexes
— same idea, different implementation for analytical workloads.

■ Types to know: B-Tree (default, range queries), Bitmap (Oracle, low-cardinality columns like status codes), Unique
index (enforces uniqueness like a PK), Composite index (multiple columns — column order matters!).

Q What are Constraints in SQL?

Constraints are rules enforced at the database level to maintain data accuracy and integrity. They act as the
database's quality gate — no bad data gets in, regardless of which application or user is inserting it.

The six main constraints:

PRIMARY KEY — Unique + Not Null. One per table. FOREIGN KEY — References PK of another table. Enforces
referential integrity. UNIQUE — Column values must be unique (allows one NULL, unlike PK). NOT NULL — Column
cannot hold NULL values. CHECK — Custom logic that values must satisfy (e.g., CHECK(salary > 0)). DEFAULT —
Provides a default value when none is supplied.

Your ETL context:

In Ab Initio, you manually build these checks — filter records where customer_id IS NULL, use Dedup to remove
duplicates, use Lookup to validate foreign keys against reference data. Database constraints automate this at the
storage layer. A robust data pipeline enforces quality at BOTH levels: database constraints at the source, ETL
logic in the transformation layer.

■ In a data warehouse context (Snowflake, Redshift), constraints are often NOT enforced (they exist as metadata
only) because data is pre-validated by the ETL pipeline. Mention this nuance — it shows warehouse awareness.

Q What is ACID Property?

ACID is the set of four properties that guarantee reliable database transactions — ensuring that even if the
system crashes mid-operation, the database remains in a consistent, correct state.

A — Atomicity:

A transaction is ALL or NOTHING. If a bank transfer involves debiting Account A and crediting Account B, both
operations happen together or neither does. No half-completed transfers. This maps directly to Ab Initio's
checkpoint-and-restart logic — if a graph fails mid-run, you roll back to the last checkpoint.

C — Consistency:

SQL Interview Master Guide | Page 5

A transaction must take the database from one valid state to another. All constraints, rules, and triggers must still
hold after the transaction. If a rule says balance cannot be negative, no transaction can leave an account in
negative balance.

I — Isolation:

Concurrent transactions execute as if they are running sequentially. One transaction's intermediate state is
invisible to others. This prevents 'dirty reads' where Transaction A reads a value that Transaction B hasn't
committed yet.

D — Durability:

Once a transaction is committed, it is permanent — even if the server crashes a millisecond later. This is
achieved through write-ahead logs (WAL). In Barclays' context, this is why financial transaction records are
recoverable even after failures.

■ Modern data lakes (S3, HDFS) were originally NOT ACID-compliant. Delta Lake (used in Databricks) added ACID
properties to data lakes — that's a key reason Barclays adopted it. Connect ACID to Delta Lake in interviews.

SQL Interview Master Guide | Page 6

 ■ SECTION 2: JOINS & QUERY LOGIC

Q Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN

JOINs combine rows from two tables based on a matching condition. The type of JOIN determines what happens
to rows with NO match.

JOIN Type Returns Non-matching rows?

INNER JOIN Only rows where the condition matches in BOTH

Excluded
tables
from result

LEFT JOIN ALL rows from left table + matched rows from
Left
right.
table
Right
rowsside
kept;
is right
NULLside
if no= match.
NULL
(LEFT OUTER)

RIGHT JOIN ALL rows from right table + matched rows from
Right
left.
table
Leftrows
side kept;
is NULL
left ifside
no =
match.
NULL
(RIGHT OUTER)

Q
-- Employees and Departments example SELECT [Link], d.dept_name FROM employees e INNER
JOIN departments d ON e.dept_id = d.dept_id; -- Only employees WITH a department
assignment SELECT [Link], d.dept_name FROM employees e LEFT JOIN departments d ON
e.dept_id = d.dept_id; -- ALL employees; dept_name = NULL if unassigned

■ Real-world use: In Ab Initio, INNER JOIN maps to a standard Join component with 'inner join' mode. LEFT JOIN
maps to 'left outer join' mode. When you see NULL on the right side, those are your unmatched left records —
equivalent to Ab Initio outputting records from the left input that had no match in the right input.

Q What is FULL OUTER JOIN?

FULL OUTER JOIN returns ALL rows from BOTH tables. Where there's no match, the missing side is filled with
NULL. It's the union of LEFT JOIN and RIGHT JOIN.

SELECT [Link], d.dept_name FROM employees e FULL OUTER JOIN departments d ON e.dept_id =
d.dept_id; -- Employees with no dept (NULL on right) -- Departments with no employees
(NULL on left) -- Both show up

Use case: Reconciliation reports — find records that exist in System A but not System B, AND records in System
B but not System A. Very common in financial data reconciliation (exactly the kind of work done in Barclays BUK
audits).

■■ MySQL does not natively support FULL OUTER JOIN — simulate it with LEFT JOIN UNION RIGHT JOIN.

Q What is SELF JOIN? Give example.

A SELF JOIN joins a table to itself — treating the same table as if it were two different tables using aliases. Used
when data has a self-referential relationship.

Classic example: Employee-Manager hierarchy

SELECT [Link] AS employee, [Link] AS manager FROM employees e JOIN employees m ON

e.manager_id = m.employee_id; -- Same table (employees) joined to itself -- 'e' =
employee, 'm' = their manager

Another use: Find pairs of employees in the same department, or transactions that reference each other
(parent-child transaction records). In Ab Initio, this would require two Scan or Read components reading the
same file and then a Join component — you can't self-join within one component.

SQL Interview Master Guide | Page 7

Q What is CROSS JOIN?

A CROSS JOIN produces the Cartesian product — every row from Table A paired with every row from Table B.
No join condition. If Table A has 5 rows and Table B has 4 rows, the result has 5×4 = 20 rows.

SELECT product, region FROM products CROSS JOIN regions; -- Every product × every region
combination

Practical use: Generate all possible combinations — e.g., all (product, date) slots for a reporting template, even
those with no sales data. In Ab Initio, a Cross Join is represented by a Join component with no key — it produces
the full Cartesian product, which can explode in size. Always use cautiously.

■■ Cross joins on large tables are dangerous — 1M × 1M = 1 trillion rows. Always have a business reason.

Q Difference between WHERE and HAVING

Both filter rows, but they operate at different stages of query execution.

WHERE: Filters BEFORE aggregation (GROUP BY). Works on individual row values. HAVING: Filters AFTER
aggregation. Works on aggregate results (SUM, COUNT, AVG, etc.).

-- WHERE: filter rows before grouping SELECT dept_id, AVG(salary) AS avg_sal FROM
employees WHERE status = 'ACTIVE' -- filter rows first GROUP BY dept_id HAVING AVG(salary)
> 60000; -- then filter groups

Analogy: In Ab Initio, WHERE is like placing a Filter component BEFORE a Rollup. HAVING is like placing a Filter
component AFTER the Rollup — you're filtering on aggregated output values. You cannot reference aggregate
functions in a WHERE clause.

Q Difference between UNION and UNION ALL

UNION: Combines results of two queries and REMOVES duplicates (like DISTINCT). Slower due to deduplication
step. UNION ALL: Combines results and KEEPS all rows including duplicates. Faster.

-- UNION removes duplicates SELECT customer_id FROM uk_customers UNION SELECT customer_id
FROM eu_customers; -- UNION ALL keeps everything SELECT customer_id FROM uk_customers
UNION ALL SELECT customer_id FROM eu_customers;

In Ab Initio, this maps to the Concatenate component — which is effectively UNION ALL (all records from all
inputs, no dedup). To get UNION behaviour, you'd add a Dedup Sorted component after the Concatenate.

■ Rule of thumb: Always prefer UNION ALL unless you specifically need deduplication — it avoids an expensive
sort/hash operation. In large-scale pipelines, this performance difference is significant.

Q What is a Subquery?

A subquery (or inner query) is a query nested inside another query. The inner query runs first and its result is
used by the outer query.

-- Find employees earning above the company average SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees); -- Inner query runs first → returns
one avg value -- Outer query filters against that value

Subqueries can appear in SELECT (scalar subquery), FROM (derived table / inline view), WHERE (filter
subquery), or HAVING clauses. In the FROM clause, they function like temporary tables — similar to a CTE
(WITH clause).

Q Correlated vs Non-Correlated Subquery

SQL Interview Master Guide | Page 8

Non-Correlated Subquery:

The inner query is independent — it runs ONCE and returns a fixed value used by the outer query. Like the AVG
salary example above.

Correlated Subquery:

The inner query REFERENCES a column from the outer query — it runs once PER ROW of the outer query.
Slower but powerful.

-- Correlated: find employees earning > their own department avg SELECT [Link],
[Link], e1.dept_id FROM employees e1 WHERE [Link] > ( SELECT AVG([Link]) FROM
employees e2 WHERE e2.dept_id = e1.dept_id -- ← references outer query );

This is like running a Rollup per department inside an Ab Initio graph and then filtering records by comparing
each record's salary against its own department's rollup output. Correlated subqueries can be slow; window
functions (see Section 4) are usually a better alternative.

Q How to find and delete duplicate rows

Step 1: Identify duplicates

-- Find duplicate emails SELECT email, COUNT(*) AS cnt FROM customers GROUP BY email
HAVING COUNT(*) > 1;

Step 2: Delete duplicates, keep one (Oracle/general approach using ROWID)

-- Keep the row with the smallest ROWID (Oracle) DELETE FROM customers WHERE ROWID NOT IN
( SELECT MIN(ROWID) FROM customers GROUP BY email ); -- Modern approach using CTE +
ROW_NUMBER (standard SQL) WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email
ORDER BY created_date) AS rn FROM customers ) DELETE FROM ranked WHERE rn > 1;

In Ab Initio, duplicate removal is done with the Dedup Sorted component (requires sorted input) or Dedup
(unsorted, memory-intensive). The GROUP BY + HAVING approach maps conceptually to the Rollup component
counting occurrences per key.

SQL Interview Master Guide | Page 9

 ■■ SECTION 3: IMPORTANT SQL PROBLEMS

Q Find second highest salary (and Nth highest salary)

Method 1: Using OFFSET (Standard SQL)

SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 ROW FETCH NEXT 1 ROW
ONLY; -- 2nd highest

Method 2: Using subquery (works everywhere)

SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary)
FROM employees);

Method 3: DENSE_RANK (best for Nth — handles ties correctly)

-- Nth highest salary (replace 2 with N) SELECT salary FROM ( SELECT salary, DENSE_RANK()
OVER (ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 2;

■ Always use DENSE_RANK for 'Nth' problems — if two people share rank 1, RANK skips rank 2, but
DENSE_RANK doesn't. The interviewer is testing whether you know this difference.

Q Find employees earning more than their manager

SELECT [Link] AS employee, [Link] AS emp_salary, [Link] AS manager, [Link] AS
mgr_salary FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE
[Link] > [Link];

This is a SELF JOIN on the employees table — treating it as both an employee table and a manager table
simultaneously. The JOIN condition links each employee to their manager, and the WHERE clause filters for the
salary comparison.

Q Find department-wise highest salary

-- Method 1: GROUP BY + MAX SELECT dept_id, MAX(salary) AS max_salary FROM employees GROUP
BY dept_id; -- Method 2: With employee name (requires window function or subquery) SELECT
name, dept_id, salary FROM ( SELECT name, dept_id, salary, RANK() OVER (PARTITION BY
dept_id ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk = 1;

Method 2 is preferred because it also gives you the employee name. GROUP BY + MAX only gives the max
value — getting the employee name requires joining back. Window functions solve this elegantly in one pass.

Q Find top 3 employees per department

SELECT name, dept_id, salary FROM ( SELECT name, dept_id, salary, DENSE_RANK() OVER (
PARTITION BY dept_id ORDER BY salary DESC ) AS rnk FROM employees ) t WHERE rnk <= 3;

The PARTITION BY groups by department (like Ab Initio's Sort Within Group defines partition boundaries). Within
each partition, DENSE_RANK assigns ranks by salary. The outer WHERE filters to top 3. If two employees tie at
rank 2, both are included — which is usually the business requirement.

Q Running Total (Cumulative Sum)

SELECT transaction_date, amount, SUM(amount) OVER ( ORDER BY transaction_date ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM transactions;

SQL Interview Master Guide | Page 10

The window frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means: 'accumulate
from the very first row up to and including the current row.' This is exactly how a daily balance is computed — add
today's transactions to yesterday's closing balance.

Barclays context:

Account balance calculations, cumulative PnL (Profit & Loss), and regulatory capital adequacy calculations all
use running totals. This exact pattern would appear in the Snowflake consumption layer.

Q Moving Average
-- 7-day moving average of transaction amounts SELECT transaction_date, amount,
AVG(amount) OVER ( ORDER BY transaction_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS
moving_avg_7d FROM daily_transactions;

The window frame '6 PRECEDING AND CURRENT ROW' captures the current row + 6 rows before it = a rolling
7-day window. As the window slides forward, the oldest row drops off. This is the SQL equivalent of a signal
smoothing filter — used in fraud detection (unusual spend vs. rolling average) and risk dashboards.

Q Find consecutive days / gaps problem

The classic approach: subtract a row number from the date. If dates are truly consecutive, the difference is
constant.

-- Find groups of consecutive login dates per user SELECT user_id, MIN(login_date) AS
streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_length FROM ( SELECT
user_id, login_date, login_date - ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY
login_date ) AS grp FROM user_logins ) t GROUP BY user_id, grp ORDER BY streak_length
DESC;

The trick: if dates are consecutive, date - row_number stays constant within the streak. When there's a gap,
row_number increments but the date jumps more — so the difference changes, creating a new group. This is
elegant, concise, and a fan favourite in interviews.

■■ In Oracle, date arithmetic works natively. In Hive/Impala, use DATEDIFF or date_sub functions.

Q Find missing values / gaps in a sequence

-- Find missing IDs between min and max SELECT level AS missing_id FROM ( SELECT MIN(id)
AS min_id, MAX(id) AS max_id FROM orders ) t CONNECT BY LEVEL <= max_id - min_id + 1 --
Oracle hierarchical START WITH 1 MINUS SELECT id FROM orders; -- Standard SQL approach
using a numbers/dates CTE WITH all_ids AS ( SELECT generate_series(MIN(id), MAX(id)) AS
expected_id FROM orders ) SELECT a.expected_id AS missing_id FROM all_ids a LEFT JOIN
orders o ON a.expected_id = [Link] WHERE [Link] IS NULL;

■ In Hive/Spark SQL, use sequence() or explode() to generate number ranges. Oracle CONNECT BY is the most
elegant approach and will come up in Fidelity's Oracle interviews.

SQL Interview Master Guide | Page 11

 ■■■ SECTION 4: WINDOW FUNCTIONS

Q What are Window Functions? Difference from GROUP BY.

Window functions perform calculations across a set of rows related to the current row, without collapsing
those rows into one. This is the key distinction from GROUP BY.

GROUP BY: Collapses all rows in a group into ONE summary row. You lose individual row detail. Window Function:
Computes a value FOR EACH ROW while still preserving all rows. You keep individual row detail AND get the
aggregated value.

-- GROUP BY: collapses to one row per dept SELECT dept_id, AVG(salary) AS avg_sal FROM
employees GROUP BY dept_id; -- Result: 3 rows (one per dept) -- Window Function: preserves
all rows + adds avg column SELECT name, dept_id, salary, AVG(salary) OVER (PARTITION BY
dept_id) AS dept_avg FROM employees; -- Result: all 100 rows with dept_avg added to each

Ab Initio analogy:

GROUP BY maps to the Rollup component — output is one record per group. Window functions map to Scan +
Rollup + Lookup back to original — you compute group-level aggregates but re-join them back to every original
record. SQL window functions do this in one elegant expression.

Q Explain ROW_NUMBER, RANK, DENSE_RANK

Setup: Employees with salaries — 50K, 60K, 60K, 70K

Function Logic Result for: 50K, 60K, 60K, 70K

ROW_NUMBER() Sequential number — no ties. Always unique.

1, 2, 3, 4

RANK() Tied rows get the same rank. SKIPS next rank(s).
1, 2, 2, 4 (3 is skipped)

DENSE_RANK() Tied rows get the same rank. Does NOT skip
1, next
2, 2,rank.
3 (no gap)

Q
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, RANK() OVER
(ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk FROM
employees;

■ Use ROW_NUMBER when you need exactly one row (deduplication, pagination). Use DENSE_RANK when you
need 'Nth highest' without gaps. Use RANK when gaps represent the actual number of people ahead of you.

Q What is LAG and LEAD?

LAG accesses a value from a previous row. LEAD accesses a value from a future row — all within the current
result set, without a self-join.

SELECT transaction_date, amount, LAG(amount, 1) OVER (ORDER BY transaction_date) AS

prev_day_amount, LEAD(amount, 1) OVER (ORDER BY transaction_date) AS next_day_amount,
amount - LAG(amount, 1) OVER (ORDER BY transaction_date) AS day_over_day_change FROM
daily_transactions;

Real-world use: Calculate day-over-day change in account balance, detect spikes (fraud detection), compare
current month sales to previous month. In Ab Initio, you'd sort the data and use a Scan component with a
'previous record' variable — LAG/LEAD in SQL eliminates the need for that.

SQL Interview Master Guide | Page 12

 ■ LAG/LEAD syntax: LAG(column, offset, default_if_null). The third argument handles NULLs for the first/last row —
e.g., LAG(amount, 1, 0) returns 0 instead of NULL for the first row.

Q What is PARTITION BY and ORDER BY in OVER clause?

PARTITION BY:

Divides rows into groups (partitions) within which the window function operates independently. Like GROUP BY
— but without collapsing rows. Each partition is a separate 'window'.

ORDER BY (inside OVER):

Defines the order of rows within each partition for functions that care about sequence — like running totals, ranks,
LAG/LEAD.

SELECT name, dept_id, salary, -- Rank within each department (PARTITION BY creates
dept-level windows) RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
-- Running total per department per salary order SUM(salary) OVER (PARTITION BY dept_id
ORDER BY salary) AS running_dept_sal FROM employees;

Ab Initio mapping:

PARTITION BY maps to Sort Within Group's group key — it defines the boundary of each processing group.
ORDER BY inside OVER maps to the sort key within that group. This is exactly how Ab Initio's Sort Within Group
component works: partition key defines groups, sort key defines order within groups.

Q What is a Window Frame?

A window frame defines which specific rows within the partition the function uses for its calculation relative to
the current row. It's the fine-grained control of the window.

-- Running total: current row + all preceding rows SUM(amount) OVER (ORDER BY date ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- 7-day moving average: 6 rows before +
current AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) --
Centred 3-row average: 1 before, current, 1 after AVG(amount) OVER (ORDER BY date ROWS
BETWEEN 1 PRECEDING AND 1 FOLLOWING) -- Entire partition (no ORDER BY needed for simple
aggregates) SUM(salary) OVER (PARTITION BY dept_id)

ROWS: counts physical rows (precise). RANGE: counts rows with the same ORDER BY value (treats ties as one
unit). GROUPS (SQL 2011): groups of equal-valued rows.

■ Default frame when ORDER BY is present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW. Default when no ORDER BY: entire partition. Knowing this prevents subtle bugs in running total calculations.

SQL Interview Master Guide | Page 13

 ■ SECTION 5: ORACLE-SPECIFIC — Your Edge

Q Difference between ROWNUM and ROW_NUMBER()

Aspect ROWNUM ROW_NUMBER()

Type Pseudo-column (Oracle proprietary) Standard SQL window function

When assigned BEFORE ORDER BY is applied AFTER ORDER BY is applied

Filter pitfall WHERE ROWNUM = 2 → returns nothing! Safe to filter in outer query
(Row gets ROWNUM=1 first, then filtered)

Pagination Clunky (needs subquery) Clean with OFFSET/FETCH or in subquery

Q
-- WRONG: This returns NO rows! SELECT * FROM employees WHERE ROWNUM = 2; -- CORRECT with
ROWNUM (subquery required) SELECT * FROM ( SELECT e.*, ROWNUM AS rn FROM employees e ORDER
BY salary DESC ) WHERE rn = 2; -- CLEAN with ROW_NUMBER (preferred) SELECT * FROM ( SELECT
e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees e ) WHERE rn = 2;

■ This is one of Oracle's most common interview traps. ROWNUM is assigned before the result is sorted, so filtering
ROWNUM = 2 after an ORDER BY gives unexpected results. Always use ROW_NUMBER() in subqueries for
reliable pagination.

Q What is CONNECT BY PRIOR (Hierarchical Query)?

Oracle's CONNECT BY PRIOR is a recursive query mechanism for navigating hierarchical (tree-structured)
data — like organisational charts, account category trees, or bill-of-materials structures.

-- Employee hierarchy: show all levels SELECT employee_id, name, manager_id, LEVEL, --
depth level (1=root) SYS_CONNECT_BY_PATH(name, '/') AS org_path -- full path FROM
employees START WITH manager_id IS NULL -- start from root (CEO) CONNECT BY PRIOR
employee_id = manager_id -- traverse downward ORDER SIBLINGS BY name; -- sort within each
level

In Barclays' context, CONNECT BY would model the legal entity hierarchy (parent company → subsidiary →
branch), product category trees, or geographic region hierarchies. Standard SQL uses recursive CTEs (WITH
RECURSIVE) for the same purpose — CONNECT BY is Oracle's proprietary, often more readable alternative.

■ LEVEL is a special Oracle pseudo-column in hierarchical queries — it equals 1 for root, 2 for first level children,
etc. SYS_CONNECT_BY_PATH builds the full path string.

Q What is the MERGE statement (UPSERT)?

MERGE combines INSERT and UPDATE into a single atomic operation — 'if the record exists, update it; if it
doesn't, insert it.' This is the UPSERT pattern.

MERGE INTO target_accounts t USING source_updates s ON (t.account_id = s.account_id) --

match condition WHEN MATCHED THEN UPDATE SET [Link] = [Link], t.updated_date =
SYSDATE WHEN NOT MATCHED THEN INSERT (account_id, customer_id, balance, created_date)
VALUES (s.account_id, s.customer_id, [Link], SYSDATE);

Your ETL context:

SQL Interview Master Guide | Page 14

This is exactly what CDC (Change Data Capture) processing does in the Barclays BUK DTX transformation layer.
When a new change record arrives, you need to either update an existing row (MATCHED) or create a new one
(NOT MATCHED). In Ab Initio, this pattern is implemented using a Lookup join + conditional routing to separate
Insert vs Update streams. MERGE does it in one SQL statement.

Snowflake, Databricks Delta Lake, and Oracle all support MERGE. It's a core pattern in SCD Type 1
implementations.

Q What is the DUAL table? What is SYSDATE?

DUAL:

A special one-row, one-column dummy table that Oracle provides. It's used when you need to run a SELECT but
don't actually need data from a real table — just to evaluate a function or expression.

SELECT SYSDATE FROM DUAL; -- current date/time SELECT 2 + 2 FROM DUAL; -- arithmetic
SELECT UPPER('hello') FROM DUAL; -- string function SELECT SYS_GUID() FROM DUAL; --
generate UUID

SYSDATE:

Returns the current date AND time from the database server clock. It's an Oracle function — not a keyword. Use
TRUNC(SYSDATE) to get just the date without time component.

SELECT SYSDATE, -- 17-APR-2026 14:32:07 TRUNC(SYSDATE), -- 17-APR-2026 00:00:00 SYSDATE -

30, -- 30 days ago ADD_MONTHS(SYSDATE,3) -- 3 months from now FROM DUAL;

■ PostgreSQL equivalent: SELECT NOW() or CURRENT_TIMESTAMP. MySQL: NOW(). SQL Server: GETDATE().
In standard SQL: CURRENT_TIMESTAMP. Always mention the portability context.

Q What are Sequences in Oracle?

A Sequence is a database object that generates a unique, incrementing number on demand — used for primary
key generation without needing application logic to manage IDs.

-- Create a sequence CREATE SEQUENCE account_seq START WITH 1000 INCREMENT BY 1 NOCACHE --
don't pre-generate values in memory NOCYCLE; -- don't restart after reaching max -- Use it
INSERT INTO accounts (account_id, customer_id, balance) VALUES (account_seq.NEXTVAL,
12345, 0.00); -- Check current value (doesn't advance) SELECT account_seq.CURRVAL FROM
DUAL;

ETL context:

In your Barclays BUK pipeline, Surrogate Key generation in the DTX transformation layer does exactly this —
generating unique integer keys for the data warehouse tables. In AWS Glue/PySpark, you'd use
monotonically_increasing_id() or a UUID function. Sequences are Oracle's way; Lambda-based key generators
or Snowflake AUTOINCREMENT are cloud equivalents.

Q NVL vs COALESCE vs NVL2

Function Syntax Behaviour Standard?

NVL NVL(expr, replacement) If expr IS NULL, return replacement.

Oracle
2 args
onlyonly.

NVL2 NVL2(expr, not_null_val, null_val)If expr NOT NULL → val1. If NULL → val2.
Oracle only3 args.

COALESCE COALESCE(a, b, c, ...) Returns first NON-NULL value from

ANSI
a list
Standard
of N args.

ISNULL ISNULL(expr, replacement) Like NVL. 2 args only. SQL Server only

SQL Interview Master Guide | Page 15

Q
-- Equivalent null handling: NVL(commission, 0) -- Oracle COALESCE(commission, 0) --
Standard SQL -- COALESCE's power: multiple fallbacks COALESCE(pref_phone, office_phone,
mobile, 'No Contact') -- returns first non-null

■ Always prefer COALESCE in new code — it's portable across Oracle, Hive, Snowflake, PostgreSQL, and Spark
SQL. Use NVL only when you need to match legacy Oracle code style.

Q TRUNC(date) and FETCH FIRST / ROWNUM for limiting rows

TRUNC(date):

TRUNC(SYSDATE) -- strips time → midnight of today TRUNC(SYSDATE, 'MM') -- first day of

current month TRUNC(SYSDATE, 'YYYY') -- first day of current year TRUNC(SYSDATE, 'IW') --
Monday of current week

Limiting rows — old vs new Oracle:

-- Old Oracle (pre-12c): ROWNUM workaround SELECT * FROM ( SELECT * FROM employees ORDER
BY salary DESC ) WHERE ROWNUM <= 5; -- Modern Oracle 12c+: FETCH FIRST (SQL standard)
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY; -- With ties (return
all rows that tie at the boundary) FETCH FIRST 5 ROWS WITH TIES; -- Pagination OFFSET 10
ROWS FETCH NEXT 5 ROWS ONLY; -- rows 11-15

■ Fidelity likely uses Oracle 19c+. Demonstrate knowledge of both approaches — legacy ROWNUM for code you'll
encounter in existing codebase, FETCH FIRST for new code you'd write.

SQL Interview Master Guide | Page 16

 ■ BONUS: High-Impact Concepts

Q CTE — WITH clause

A CTE (Common Table Expression) is a temporary, named result set defined at the top of a query using the
WITH keyword. It improves readability and allows you to break complex queries into logical named steps.

WITH dept_avg AS ( -- Step 1: Compute average salary per department SELECT dept_id,
AVG(salary) AS avg_sal FROM employees GROUP BY dept_id ), high_earners AS ( -- Step 2:
Find employees earning 20% above their dept avg SELECT [Link], e.dept_id, [Link],
d.avg_sal FROM employees e JOIN dept_avg d ON e.dept_id = d.dept_id WHERE [Link] >
d.avg_sal * 1.2 ) -- Step 3: Final query SELECT * FROM high_earners ORDER BY salary DESC;

CTEs can also be recursive (standard SQL equivalent of Oracle's CONNECT BY), and are much more readable
than deeply nested subqueries.

Ab Initio mapping:

A CTE is conceptually identical to an Ab Initio subgraph — you define a reusable, named transformation block
and reference it in the main graph. The logic is encapsulated, testable, and readable. Good CTE usage in SQL
demonstrates the same architectural thinking as good subgraph design in Ab Initio.

Q Query Optimisation & Execution Plans

Why queries are slow — the main culprits:

1. Full Table Scan — no usable index, or function applied to indexed column. 2. Cartesian Join — missing or wrong
join condition. 3. Too many NULLs in indexed columns — bitmap indexes handle this better. 4. Implicit type
conversion — comparing NUMBER column to VARCHAR literal. 5. Non-SARGable predicates — WHERE
UPPER(name) = 'PRIYA' prevents index use.

Reading an Execution Plan (Oracle):

EXPLAIN PLAN FOR SELECT * FROM transactions WHERE account_id = 12345; SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY); -- Key things to look for in the plan: -- TABLE ACCESS FULL →
bad for large tables, needs an index -- INDEX RANGE SCAN → good, using index efficiently
-- HASH JOIN vs NESTED LOOPS → hash better for large sets; NL better for small lookups --
COST and CARDINALITY columns → optimizer's estimate of work

Make queries SARGable (Search ARGument ABLE):

-- BAD: function on indexed column defeats index WHERE UPPER(customer_name) = 'SMITH' --

GOOD: index on UPPER(customer_name) OR avoid function WHERE customer_name = 'Smith' --
BAD: implicit conversion WHERE account_id = '12345' -- account_id is NUMBER -- GOOD: match
data types WHERE account_id = 12345

■ In your Barclays context: When Impala/Hive queries were slow on the Hadoop cluster, the same principles applied
— partition pruning (PARTITION BY date) is the Hive equivalent of an index range scan. Mention this mapping.

Q Stored Procedures & Cursors (Oracle PL/SQL)

A Stored Procedure is a named block of PL/SQL code stored in the database and executed by name. It
encapsulates business logic, accepts parameters, and can contain conditional logic, loops, and exception
handling.

SQL Interview Master Guide | Page 17

 CREATE OR REPLACE PROCEDURE update_account_status ( p_account_id IN
accounts.account_id%TYPE, p_status IN VARCHAR2, p_result OUT VARCHAR2 ) AS BEGIN UPDATE
accounts SET status = p_status, updated_date = SYSDATE WHERE account_id = p_account_id; IF
SQL%ROWCOUNT = 0 THEN p_result := 'ACCOUNT_NOT_FOUND'; ELSE COMMIT; p_result := 'SUCCESS';
END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; p_result := 'ERROR: ' || SQLERRM; END;

Cursors:

A cursor is a pointer to a result set, allowing row-by-row processing. Implicit cursors are auto-created for every
DML statement. Explicit cursors are declared for complex row-by-row logic.

DECLARE CURSOR c_accounts IS SELECT account_id, balance FROM accounts WHERE balance < 0;
v_acc c_accounts%ROWTYPE; BEGIN OPEN c_accounts; LOOP FETCH c_accounts INTO v_acc; EXIT
WHEN c_accounts%NOTFOUND; -- Process each negative-balance account
DBMS_OUTPUT.PUT_LINE('Account: ' || v_acc.account_id); END LOOP; CLOSE c_accounts; END;

Ab Initio comparison:

A stored procedure is like an Ab Initio graph encapsulated as a reusable component with input/output ports. A
cursor is like Ab Initio's record-at-a-time processing in a Scan component — it processes one record per iteration.
The critical difference: SQL set-based operations (without cursors) process ALL rows at once (like Ab Initio
parallel execution), which is almost always faster. Cursors = row-by-row = use sparingly.

■ Interview nuance: 'Cursors should be a last resort in PL/SQL — set-based SQL operations are 10-100x faster for
bulk data. I use them only when row-by-row logic is unavoidable, like complex conditional processing that can't be
expressed in set-based SQL.'

Q Index Usage & Types

When does an index NOT get used? (Critical for tuning)

1. Function applied on column: WHERE TRUNC(created_date) = TRUNC(SYSDATE) — defeats index. 2. Leading

wildcard: WHERE name LIKE '%Smith' — can't use B-Tree from the right. 3. OR conditions — often cause index skip.
Use UNION ALL instead. 4. Low-cardinality B-Tree index — e.g., status column with only 3 values. Use Bitmap
instead. 5. NOT IN / NOT EXISTS — sometimes causes full scan; EXISTS is often better.

Index Type Best For Avoid When

B-Tree (default) High cardinality columns (IDs, names), range

Low
queries
cardinality (< 10 distinct values)

Bitmap (Oracle) Low cardinality (status, gender, region codes),

OLTPdatawrite-heavy
warehouses tables (locks on update)

Composite (multi-col) Queries filtering on multiple columns together

Column order wrong — must match query order

Function-based Queries using functions: UPPER(name), TRUNC(date)

High update frequency on that column

Unique Index Enforce uniqueness (supplements or replaces

Columns
PK) that legitimately have duplicates

■ You now have the full picture — from basics to Oracle-specific depth. Connect each concept to your Barclays
BUK pipeline experience and your answers will stand out as those of an engineer who truly understands data
systems, not just SQL syntax.

SQL Interview Master Guide | Page 18