Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Capgemini hiring for SQL roles
Capgemini is a French multinational IT services and consulting group providing technology, engineering, cloud and data services. India is its largest employee base, supporting projects in testing, application development, SAP, data engineering and analytics for global clients. It hires freshers as analysts and software engineers, and experienced professionals in QA automation, data and cloud roles.
For SQL roles, this Capgemini practice set focuses most on Database Design (7), SQL Basics (6), Joins (4), Indexes and Performance (4). It has 14 easy, 16 medium questions, so you can start easy and work up to the harder ones.
Company type
IT services
Headquarters
Paris, France
Main India offices
Mumbai, Bengaluru, Pune, Chennai, Hyderabad
Capgemini SQL interview process
Online assessment (pseudocode + English)
Game-based aptitude test
Technical interview
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for CapgeminiStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
Write SQL to create a students table with a primary key and constraints.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE,
age INT CHECK (age >= 17),
city VARCHAR(50) DEFAULT 'Delhi',
joined_on DATE
);
PRIMARY KEY ensures unique non-null IDs, UNIQUE blocks duplicate emails, and CHECK validates age.
02
What are equi joins, non-equi joins and natural joins?
An equi join uses equality in the condition, such as a.id = b.id, the most common case. A non-equi join uses other operators like BETWEEN or greater than, for example matching salary to a grade range table. A natural join automatically joins on columns with the same names, which is risky because adding a column can silently change results, so explicit ON is preferred.
03
What is the difference between FULL OUTER JOIN and UNION of LEFT and RIGHT joins?
A FULL OUTER JOIN returns matched rows plus unmatched rows from both sides in one operation. MySQL does not support it, so it is emulated with a LEFT JOIN UNION a RIGHT JOIN, or LEFT JOIN UNION ALL a RIGHT JOIN filtered to unmatched rows. Using plain UNION removes duplicates but can also remove genuinely repeated rows, so the UNION ALL pattern is safer.
04
What are referential actions such as ON DELETE CASCADE?
Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.
An index is a separate data structure, usually a B-tree, that stores column values in sorted order with pointers to rows, so the database can find matching rows without scanning the whole table, like a book index. Indexes speed up WHERE, JOIN, ORDER BY and GROUP BY, but use storage and slow down inserts, updates and deletes because the index must be maintained.
06
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills columns from the right with NULL when there is no match. For customers and orders, INNER JOIN shows only customers who ordered, while LEFT JOIN shows all customers, including those with no orders, which is useful for finding inactive customers.
Constraints enforce rules on data: PRIMARY KEY for unique row identity, FOREIGN KEY for valid references, UNIQUE for no duplicates, NOT NULL for required values, CHECK for conditions like age >= 18, and DEFAULT for automatic values. They protect data quality at the database level, so bad data is rejected even if an application has a bug.
08
Get the top 2 highest paid employees in each department.
WITH r AS (
SELECT emp_id, name, department_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT * FROM r WHERE rn <= 2;
Use DENSE_RANK instead of ROW_NUMBER if tied salaries should all be included.
09
A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?
The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.
A composite key is a primary key made of two or more columns that together uniquely identify a row. For example, in an enrolments table, student_id and course_id together form the key because a student can join many courses and a course has many students, but each pair appears once. Column order in a composite key matters for index usage.
11
What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
COUNT(*) counts all rows including those with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-null values. In a table of 100 orders where 10 have no coupon and 30 distinct coupons were used, COUNT(*) is 100, COUNT(coupon) is 90 and COUNT(DISTINCT coupon) is 30.
A Common Table Expression is a named temporary result defined with WITH before the main query. It improves readability by breaking a complex query into named steps, can be referenced multiple times in the same statement, and supports recursion. Example: WITH high_value AS (SELECT * FROM orders WHERE amount > 10000) SELECT customer_id, COUNT(*) FROM high_value GROUP BY customer_id.
13
What is the difference between an index scan and an index seek?
An index seek navigates the B-tree directly to the matching keys, reading only the needed rows, which is efficient for selective filters. An index scan reads the whole index or a large range of it. A table scan reads every row. Scans are not always bad; for queries returning a large share of rows, a scan can be cheaper than many seeks.
14
What are database statistics and why do they matter?
Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.
15
What is the difference between putting a condition in ON versus WHERE in a LEFT JOIN?
In a LEFT JOIN, a condition in ON filters the right table before matching, so all left rows are still kept. A condition on the right table in WHERE runs after the join and removes rows where the right side is NULL, effectively turning it into an inner join. To get all customers with only their 2025 orders, put the date condition in ON.
DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) changes data: INSERT, UPDATE, DELETE, and SELECT is often grouped here or as DQL. DCL (Data Control Language) manages access: GRANT and REVOKE. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK and SAVEPOINT. Knowing the categories helps explain behaviour such as auto-commit of DDL in some databases.
NULL means a missing or unknown value, not zero or an empty string. Any comparison with NULL using = or <> returns unknown, so rows are not returned. Use IS NULL or IS NOT NULL to test it, and COALESCE or IFNULL to replace it. Aggregates like SUM and AVG ignore NULLs, while COUNT(*) counts all rows and COUNT(column) skips NULLs.
18
A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?
Heavy analytical queries can lock rows, use CPU and slow customer transactions, and normalised schemas make reporting complex. Alternatives are a read replica for reporting, a data warehouse loaded by ETL or CDC with a star schema, or materialised summary tables. Grant read-only access with limits if direct access is unavoidable, and schedule heavy jobs off-peak.
19
What is the difference between a correlated and a non-correlated subquery?
A non-correlated subquery runs independently and returns a result once, such as the overall average salary. A correlated subquery references columns from the outer query and logically runs for each outer row, such as comparing each employee's salary with the average of their own department. Correlated subqueries are powerful but may be slow without good indexes, although optimisers often rewrite them as joins.
20
What is the difference between MIN/MAX on numbers and on strings or dates?
MIN and MAX work on any comparable type. On numbers they return smallest and largest values, on dates the earliest and latest, and on strings the first and last in alphabetical order based on collation. MAX(order_date) gives the latest order, and MIN(name) gives the alphabetically first name, which is useful but not a meaningful business metric by itself.
21
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows using a WHERE clause, is logged row by row, fires triggers and can be rolled back inside a transaction. TRUNCATE removes all rows quickly by deallocating pages, usually resets identity values and cannot use WHERE. DROP removes the whole table including structure, indexes and permissions. Use DELETE for selective removal, TRUNCATE to empty staging tables and DROP to remove objects.
A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
23
Write a transaction to transfer money between two accounts.
BEGIN;
UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 1 AND balance >= 5000;
UPDATE accounts
SET balance = balance + 5000
WHERE account_id = 2;
COMMIT;
If the first update affects zero rows or any error occurs, the application issues ROLLBACK so neither change is saved.
24
What is the CASE expression and where can it be used?
CASE adds conditional logic in SQL, returning a value based on conditions: CASE WHEN marks >= 60 THEN 'First' WHEN marks >= 45 THEN 'Second' ELSE 'Fail' END. It can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates, for example SUM(CASE WHEN status = 'Paid' THEN amount ELSE 0 END) to pivot data. Conditions are checked in order and the first true one wins.
A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates such as COUNT or SUM. For example, WHERE city = 'Pune' keeps Pune rows, and HAVING COUNT(*) > 5 keeps only groups with more than five rows. Filtering early with WHERE is also more efficient.
27
What is the difference between OLTP and OLAP databases?
OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.
WITH m AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1
)
SELECT month, revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct
FROM m ORDER BY month;
NULLIF avoids division by zero.
1NF: each column holds atomic values and there are no repeating groups, so no comma-separated phone numbers. 2NF: in 1NF and every non-key column depends on the whole composite key, so product name should not sit in an order_items table keyed by order and product. 3NF: in 2NF with no transitive dependency, so city should not determine state inside the employees table.
30
What is the difference between SUM with CASE and filtering with WHERE?
WHERE removes rows from the entire query, so all aggregates see only filtered data. SUM(CASE WHEN condition THEN amount END) applies the condition to one aggregate only, letting you calculate several conditional totals in one pass, such as paid and refunded amounts per customer. Some databases support the cleaner FILTER clause: SUM(amount) FILTER (WHERE status = 'Paid').
Tips to crack the Capgemini SQL interview
Practise game-based cognitive tests and pseudocode questions, which are commonly part of Capgemini's online assessment.
Prepare a short explanation of your projects, focusing on your role, tools used and what you would improve.
Learn about Capgemini's engineering arm and its work in sectors like automotive and financial services to show interest.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; Capgemini interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Capgemini SQL interview?
Candidates usually go through 4 stages: Online assessment (pseudocode + English); Game-based aptitude test; Technical interview; HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the Capgemini SQL interview?
This practice set leans on Database Design (7), SQL Basics (6), Joins (4), Indexes and Performance (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Capgemini interviewers ask you to walk through real work.
Are these the exact questions asked at Capgemini?
No. These are curated practice questions that match the skills and difficulty usually tested in it services interviews like Capgemini's. They are not a leaked or verified list of past questions.
Where does Capgemini hire SQL professionals in India?
Capgemini has large teams in Mumbai, Bengaluru, Pune, Chennai, Hyderabad. Openings and locations change often, so check the Capgemini careers page and filter by city and experience.