Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
Cognizant · SQL & Databases
Practice SQL & Databases questions specifically asked in Cognizant interviews – ideal for online test preparation, technical rounds and final HR discussions.
Questions
60
Tagged for this company + subject
Company
Cognizant
View company-wise questions
Subject
SQL & Databases
Explore topic-wise practice
Go through each question and its explanation. Use this page for targeted revision just before your Cognizant SQL & Databases round.
Most engines allow ORDER BY on a SELECT alias. It keeps the query clean and avoids repetition. If your engine forbids it, repeat the expression or use the ordinal position carefully.
SELECT customer_id, SUM(total) AS spend FROM sales GROUP BY customer_id ORDER BY spend DESC;
For complete preparation, combine this company + subject page with full company-wise practice and subject-wise practice. You can also explore other companies and topics from the links below.
CROSS JOIN pairs every row from the first input with every row from the second. The result has rows equal to rows(A) times rows(B). Use it to generate combinations such as dates by products, but be careful with size explosion.
SELECT d.dt, p.product_id FROM dates d CROSS JOIN products p;
The anchor member seeds the first set of rows. The recursive member references the CTE and expands the set until no new rows appear. Always add a termination guard such as a depth limit to avoid infinite recursion.
WITH RECURSIVE org AS ( SELECT id, manager_id, 1 AS lvl FROM emp WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, o.lvl+1 FROM emp e JOIN org o ON e.manager_id=o.id ) SELECT * FROM org;
A running total accumulates from the start of the partition up to the current row. The ROWS frame is precise and counts physical rows, which avoids surprises with duplicate sort values. RANGE frames can expand to peers that share the same ORDER BY value, which can change results. For financial and inventory totals, prefer ROWS for predictable behavior.
SELECT order_id, sale_date, amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY sale_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales;The default frame for ordered windows is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in many engines. With peers, that frame can include more rows than expected, and LAST_VALUE may not reflect the true last row in the partition. Set ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the absolute first or last within the partition, or use CURRENT ROW for stepwise behavior.
LAST_VALUE(amount) OVER ( PARTITION BY product ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_amt
Use LAG to fetch the previous day’s value in the same series. Subtract to get the absolute change, and divide by the lag value to get the percentage growth. Provide a safe default in LAG or guard against division by zero to keep results stable. Partition the series by entity, like product or region, and order by the date. This produces clean, independent time series with clear comparisons across consecutive points.
SELECT day, val,
LAG(val,1) OVER (PARTITION BY series_id ORDER BY day) AS prev,
val - LAG(val,1,0) OVER (PARTITION BY series_id ORDER BY day) AS delta,
CASE WHEN LAG(val,1) OVER (PARTITION BY series_id ORDER BY day) > 0
THEN (val - LAG(val,1) OVER (PARTITION BY series_id ORDER BY day))
/ LAG(val,1) OVER (PARTITION BY series_id ORDER BY day)
END AS growth
FROM series;Window functions read neighboring rows without multiplying the dataset. They are ideal for running totals, period-over-period comparisons, and rank-based filters. Plans are usually simpler, and the intent is clearer in code reviews. Self-joins can express the same logic but tend to inflate rows and require DISTINCT or GROUP BY to recover granularity. That increases memory and sort work. Window functions preserve detail and perform well with proper indexing and ordering.
SELECT order_id, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amt
FROM orders;First, ensure foreign keys from the fact to each dimension are indexed. Add composite indexes that start with the join key and include common filters for selective access. Next, verify that the dimensions have primary or unique indexes on their keys for quick lookups. Run EXPLAIN to see join order and row estimates. Push selective filters into the earliest steps, and consider pre-aggregating the fact in a CTE or temp table if the report only needs rolled-up data. Validate statistics, and test alternative join orders by adding selective predicates or hints only as a last resort.
CREATE INDEX idx_fact_date ON fact_sales(date_id); CREATE INDEX idx_fact_customer ON fact_sales(customer_id); -- Verify unique indexes on dim_date(date_id), dim_customer(customer_id)
2PC coordinates multiple resource managers to vote on success, then either commits all or aborts all. It preserves atomicity across systems. It adds latency and failure modes like in-doubt transactions. Many modern designs prefer idempotent events with eventual consistency unless strict atomicity is required.
-- conceptual coordinator messages: prepare -> commit
ROWS defines frames by row count relative to the current row. RANGE uses logical offsets on the ORDER BY value and often includes peer rows with the same sort key. For precise moving windows like last 3 rows, pick ROWS. For value ranges like past 7 days by timestamp, RANGE can be appropriate when supported.
SUM(amount) OVER ( ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_sum
When all needed columns are in the index (keys + included), the plan can avoid extra lookups. This cuts I/O and latency for hot paths. Use includes for non-key columns used in SELECT or WHERE to create index-only scans where supported.
-- SQL Server style CREATE INDEX ix ON orders(customer_id) INCLUDE (status,total); -- Postgres: just place columns in index and rely on index-only scan when visible
Create a composite index keyed by (user_id, created_at DESC, id DESC). The engine can seek on user_id and stream rows already in the required order. Adding id as a tiebreaker stabilizes order when timestamps collide and preserves index order across peers. Returning the first 50 rows becomes a cheap ordered index scan with an early stop.
CREATE INDEX idx_posts_feed ON posts(user_id, created_at DESC, id DESC);
Delete in small, time-bounded chunks that follow an index, such as primary key or date. Use a WHERE with a range or a keyset cursor to grab, say, 5–20k rows per batch. Commit between batches to release locks and let checkpoints advance. Add supporting indexes, disable unnecessary triggers, and run during low traffic. Monitor fragmentation; if your engine supports it, use partitioning and drop or detach whole partitions for instant archival cleanup.
WHILE 1=1 LOOP DELETE FROM events WHERE ts < :cutoff ORDER BY ts LIMIT 10000; EXIT WHEN ROW_COUNT()=0; COMMIT; END LOOP;
Optimistic control assumes conflicts are rare. It reads without locks and, at update, verifies the row version or timestamp has not changed. If changed, it retries or errors. This reduces lock contention but needs careful retry logic in the application.
UPDATE t SET val=:v, version=version+1 WHERE id=:id AND version=:expected;
INSERT adds new rows. List columns in a stable order to avoid surprises when schema evolves. Bulk loads and multi-row inserts are faster than many single-row inserts for large data.
INSERT INTO employees (id, name, email) VALUES (101, 'Asha', 'asha@example.com');
Percent matches any sequence of characters, including empty. Underscore matches exactly one character. Anchoring at the end, like 'abc%', can use indexes; leading wildcards like '%abc' usually force scans. Use LIKE for simple patterns. For heavy text search or complex rules, prefer full text indexes or regex features to keep queries fast and precise.
SELECT * FROM students WHERE first_name LIKE 'K%'; SELECT * FROM students WHERE first_name LIKE '__K%';
Read Committed shows only committed data, so you avoid reading a rolled-back change. But the same row can be updated between two reads, producing a non-repeatable read. Repeatable Read also blocks non-repeatable reads. Serializable adds protection against phantoms, at higher cost.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- now reads see only committed rows
Every non-aggregated column in the SELECT list must be functionally dependent on the GROUP BY columns, and practically must be listed there. If a column is not grouped, aggregate it or remove it from the projection.
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
Row-level predicates belong in WHERE. That reduces data before grouping and often yields simpler plans. Reserve HAVING for predicates that depend on aggregates.
SELECT store_id, SUM(total) FROM sales WHERE region='APAC' GROUP BY store_id;
NOT IN compares each value to the list. If the list contains NULL, comparisons become unknown and no row qualifies. Use NOT EXISTS for anti-joins, or ensure the subquery column is declared NOT NULL or filtered to exclude NULL.
SELECT a.id FROM A a WHERE a.id NOT IN ( SELECT b.a_id FROM B b WHERE b.a_id IS NOT NULL );
DISTINCT removes duplicate rows from the projection exactly as listed. It is concise when you just need unique combinations of columns without any aggregates. GROUP BY collapses rows by keys and invites aggregates in the same step. Prefer GROUP BY when you also need counts or sums per unique combination. Prefer DISTINCT when you only need uniqueness and no metrics.
SELECT DISTINCT user_id FROM logins; -- vs SELECT user_id, COUNT(*) FROM logins GROUP BY user_id;
EXISTS returns true on the first found match and stops. It avoids row multiplication and usually produces an efficient semi-join plan. Distinct can work but may add extra sort or hash steps and is less direct.
SELECT a.id FROM A a WHERE EXISTS ( SELECT 1 FROM B b WHERE b.a_id = a.id );
Union a LEFT JOIN and a RIGHT JOIN, and remove the overlap by filtering the right side of one branch to NULL. This yields all matches and non-matches from both inputs. The trade-off is extra scan work and potential duplication risk if the filters are not precise. Indexes on join keys help both branches. Test the plan size on large tables to ensure it remains acceptable.
SELECT a.*, b.* FROM A a LEFT JOIN B b ON b.k = a.k UNION ALL SELECT a.*, b.* FROM A a RIGHT JOIN B b ON b.k = a.k WHERE a.k IS NULL;
Temp tables live for the session or transaction and can be indexed and referenced many times. They are good for reuse and complex pipelines. CTEs are single statement helpers. Many engines inline them; some may materialize. Pick based on reuse and performance needs.
CREATE TEMP TABLE t AS SELECT * FROM big WHERE flag=1; CREATE INDEX ON t(id); SELECT * FROM t JOIN dim USING(id);
Write a correlated subquery in NOT EXISTS that checks for the absence of a matching row. The database evaluates it per outer row and returns true only when the inner query returns no rows. This approach does not compare values directly, so NULL does not break the logic. It avoids the three valued logic pitfall that can affect NOT IN when the right side has NULLs.
SELECT p.product_id FROM products p WHERE NOT EXISTS ( SELECT 1 FROM sales s WHERE s.product_id = p.product_id );
High-selectivity columns filter out many rows per lookup. Unique keys are the most selective and typically index well. Low-cardinality columns like boolean flags rarely help alone, though they can help as the leading column in a composite index for special cases.
CREATE UNIQUE INDEX idx_users_email ON users(email);
SARGable predicates let the engine search the index without transforming the column. Range predicates on the raw column preserve index use. Wrapping the column in a function forces a scan in many engines because the index on the base value cannot be used directly.
SELECT * FROM events WHERE created_at >= '2025-10-01' AND created_at < '2025-10-02';
Statistics inform the optimizer about value distributions. If they are stale, row estimates drift and the plan can degrade. Refreshing stats helps the optimizer choose the right indexes and join strategies for current data.
-- engine specific -- PostgreSQL autovacuum/analyze or: ANALYZE orders; -- SQL Server: UPDATE STATISTICS orders;
Create a filtered index when queries target a well-defined subset, like active=true, recent dates, or non-NULL values. The smaller index is cheaper to maintain and can be highly selective for those queries. The trade-off is coverage. Queries that fall outside the predicate cannot use the index. Be sure the predicate matches how the application filters in practice so the benefit is realized.
-- PostgreSQL CREATE INDEX idx_invoices_paid_recent ON invoices(paid_at) WHERE paid = true AND paid_at >= CURRENT_DATE - INTERVAL '90 days';
The cost model relies on estimated row counts. If estimates are wrong, the optimizer may choose scans, wrong join algorithms, or expensive rehashes. Refreshing statistics, using SARGable predicates, and avoiding functions on columns help the engine estimate more accurately.
-- PostgreSQL ANALYZE sales; -- SQL Server UPDATE STATISTICS sales;
On first execution, the engine compiles a plan using the sniffed parameter. If that value is atypical, the cached plan can be bad for other values. Mitigations include recompiling, using option hints, parameterizing carefully, or separating hot and cold paths with targeted queries.
-- SQL Server example EXEC sp_recompile N'dbo.SearchOrders';
A materialized view trades storage and refresh work for faster reads, which helps heavy, repeated analytics. A plain view is only a saved query definition. Choose refresh timing based on freshness needs. Some engines support fast refresh with logs; others only full refresh.
-- Postgres
CREATE MATERIALIZED VIEW mv_sales AS
SELECT date_trunc('day', created_at) d, SUM(total) s
FROM orders GROUP BY 1;Hints can force join orders or algorithms, but they lock the plan to today’s data shape. Over time, they may hide underlying issues like missing indexes or bad statistics and can age poorly. Use hints as a last resort for stable, critical queries after fixing schema and statistics. Document why the hint exists and review it when data distributions change.
-- Example (syntax varies) SELECT /*+ USE_HASH(o c) LEADING(o c) */ ...
As isolation increases, more anomalies are blocked. Read Committed avoids dirty reads. Repeatable Read also stabilizes previously read rows. Serializable enforces the same outcome as some serial order of transactions and eliminates phantoms. Engines vary: some provide Snapshot Isolation variants that behave between Repeatable Read and Serializable for common workloads.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
A deadlock is a cycle of waiting. Engines detect the cycle and abort one victim to break it. Your code should handle the error and retry safely. Prevent deadlocks by acquiring resources in a consistent order, keeping transactions short, and using selective indexes to lock fewer rows.
BEGIN; -- T1 locks row A then tries B -- T2 locks row B then tries A -> cycle
Treat deadlocks as expected under contended workloads. Make the unit of work idempotent, catch the specific error code, and retry the whole transaction a small number of times with backoff. Keep transactions short and ordered to lower the chance of a second collision. Log the query shapes involved so you can re-order updates or add selective indexes to shrink lock footprints and reduce contention hot spots.
-- pseudocode
for attempt in 1..3:
begin tx
try { do_work(); commit; break; }
catch deadlock { rollback; sleep(jitter); continue; }The anti-join pattern uses a left join and then filters for rows where the right side is missing. This expresses “in A but not in B”. The same logic can be written with NOT EXISTS. Choose the form that is clearest and performs well in your engine.
SELECT A.* FROM A LEFT JOIN B ON B.k = A.k WHERE B.k IS NULL;
The anchor produces the seed rows. The recursive member references the CTE and expands the result, usually via UNION ALL. A safety condition limits depth. This pattern models hierarchies like org charts and folder trees without procedural code.
WITH RECURSIVE org AS ( SELECT id, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, o.lvl+1 FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org;
A cross join returns every combination of rows from both inputs. It is useful for generating grids or filling a date series crossed with categories. Use with care. Without a subsequent filter, the row count is rows(A) × rows(B), which can explode quickly.
SELECT p.product_id, d.dt FROM products p CROSS JOIN dates d;
NULL represents unknown. Comparisons with equals return unknown, not true. Use IS NULL or IS NOT NULL. When aggregating, functions like COUNT(col) skip NULLs, while COUNT(*) counts all rows.
SELECT COUNT(*) total, COUNT(email) with_email FROM users WHERE deleted_at IS NULL;
Use a CTE when naming the step improves clarity, you reuse the step, or the engine supports readable recursion. A derived table is fine for small, local transformations and keeps the scope tight. Performance often depends on the optimizer. Many engines inline both forms. When reuse, indexing, or materialization control is needed, consider a temporary table with indexes instead.
SELECT * FROM ( SELECT customer_id, SUM(total) AS s FROM orders GROUP BY 1 ) t JOIN customers c ON c.id = t.customer_id;
LEFT JOIN preserves every row from the left input and fills right-side columns with NULL when a match is missing. Use it to find presence plus absence cases, such as customers without orders.
SELECT c.id, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
Parameters keep code and data separate. The engine treats the parameter as a literal, not executable SQL. Combine with allow-list validation and least-privilege accounts to layer defenses.
SELECT * FROM users WHERE email = $1; -- bind at driver
NTILE splits the ordered rows into the requested number of buckets and labels each row with its bucket number. With 4, you get quartiles by row count. It is handy for top-x percent tagging and A B testing splits.
NTILE(4) OVER (PARTITION BY country ORDER BY revenue DESC) AS quartile
When the scan order matches an index, less sorting is needed and the window can stream. ROWS frames keep work bounded to exact row counts instead of peers. Materialization and DISTINCT can add unnecessary sorts and memory usage unless required by logic.
CREATE INDEX idx_sales_pid_day ON sales(product_id, day); -- then order by (product_id, day)
Partition the window by account and the month key so the cumulative sum restarts for each month. Order by transaction time and add a stable id as a tie breaker. This produces a per-month running total without extra joins or procedural loops.
SUM(amount) OVER (
PARTITION BY account_id, date_trunc('month', txn_time)
ORDER BY txn_time, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS month_running_balanceSET NULL preserves the child record but clears the foreign key, which is valid only if the column allows NULL. CASCADE deletes the children, RESTRICT/NO ACTION prevents the parent delete when children exist.
customer_id BIGINT REFERENCES customers(id) ON DELETE SET NULL
Composite keys model identities like (order_id, line_no) or (country_code, phone). They encode business meaning and enforce pair uniqueness. They do add key width to child tables. Consider a surrogate key if many foreign keys must reference the row.
PRIMARY KEY (order_id, line_no)
A CHECK with an IN list validates values at the database boundary and works across vendors. Vendor-specific enums can be ergonomic, but CHECK is simple and portable.
status TEXT NOT NULL CHECK (status IN ('NEW','PAID','CANCELLED'))Sequences are independent objects you can reuse across tables, set start and increment, and alter without touching table DDL. They work well when you need gaps handled explicitly or cross-table ordering. Identity columns are convenient table-local generators. They reduce boilerplate but are less flexible to share or reset. Choose sequences for cross-table coordination or when your DBMS lacks uniform identity semantics; use identity for simple, per-table keys.
CREATE SEQUENCE seq_id START WITH 1;
CREATE TABLE t (id BIGINT PRIMARY KEY DEFAULT nextval('seq_id'));CASCADE can remove deep trees. Wrap deletes in a transaction, check impact with a SELECT first, and delete in small batches if needed. Keep foreign keys explicit and ensure only intended users can perform destructive actions.
BEGIN; -- preview SELECT COUNT(*) FROM orders WHERE customer_id = :id; -- then delete parent DELETE FROM customers WHERE id = :id; COMMIT;
A partial index stores only rows that satisfy a predicate. It stays small and hot for common filters, reducing I/O and maintenance cost.
-- PostgreSQL CREATE INDEX idx_active ON users(email) WHERE active = true;
OFFSET N means the engine must find and skip N rows in order, which grows linearly with page depth. That adds sorts and reads, and results drift when new rows arrive. Keyset uses a stable cursor, like the last seen id and timestamp, so the query can seek to a point and fetch the next chunk. It is faster and avoids misses or duplicates under concurrency.
SELECT * FROM posts WHERE user_id=:u AND (created_at,id) < (:t,:id) ORDER BY created_at DESC, id DESC LIMIT 50;
Wrapping the column in a function blocks index seeks. Range boundaries allow the engine to use the index on created_at efficiently. This pattern also avoids time zone surprises and keeps the predicate precise and fast.
created_at >= :d AND created_at < :d + INTERVAL '1 day'
De-duplicating early on the side that causes multiplicity prevents row explosion and shrinks the join inputs. That reduces memory, sorts, and downstream aggregation cost. This pattern is especially useful when joining many-to-many relationships or dimension tables with duplicated keys.
WITH d AS ( SELECT DISTINCT customer_id FROM clicks WHERE dt=:d ) SELECT ... FROM d JOIN customers c ON c.id=d.customer_id;
Reads either take shared locks (blocking writers on those rows) or read MVCC versions without locking them. Writes need exclusive intent so concurrent writers do not conflict. Lock names and hierarchy vary by engine, but the concept of shared versus exclusive is common.
UPDATE inventory SET qty=qty-1 WHERE sku='X'; -- exclusive row lock
They hold locks and versions for a long time, increasing contention and bloat. In MVCC, old versions cannot be reclaimed, which inflates storage and slows scans. Checkpoint and log retention also grow. Mitigate by committing in smaller units, breaking work into batches, and using snapshot reads for reporting. Keep interactive transactions short and avoid idle-in-transaction sessions.
-- Batch pattern DELETE FROM events WHERE created_at < :cutoff LIMIT 10000; COMMIT; -- loop
Authn answers “who are you.” Authz answers “what can you access.” Use centralized roles and scoped object permissions to enforce authorization consistently.
CREATE ROLE reporting; GRANT SELECT ON SCHEMA analytics TO reporting;
RPO is tolerated data loss. It drives log backup cadence and replication mode. A near-zero RPO needs synchronous replication or continuous log shipping. RTO is tolerated downtime. It drives automation, hot standbys, and failover drills. Short RTO favors pre-provisioned replicas, scripted role swaps, and frequent restore testing.
Policy: RPO 5 min (logs every 5 min); RTO 15 min (warm standby + automated failover)
WHERE applies to raw rows, before grouping. It reduces input early and improves performance when selective. Use HAVING after GROUP BY when you need to filter on aggregate results, like COUNT or SUM.
SELECT department_id, COUNT(*) FROM employees WHERE active = 1 GROUP BY department_id;