SQL Online Assessment Questions &amp; Answers

Show answer

FIRST_VALUE returns the first value in the window frame, LAST_VALUE the last, and NTH_VALUE the nth. A common trap is LAST_VALUE with the default frame, which ends at the current row, so it returns the current row's value. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value of the partition.

Assign each customer to a spending quartile.

Show answer

WITH spend AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ) SELECT customer_id, total, NTILE(4) OVER (ORDER BY total DESC) AS quartile FROM spend; Quartile 1 contains the highest spenders. Use this for targeted offers or loyalty tiers.

Write SQL to create a students table with a primary key and constraints.

SQL BasicsEasy

Show answer

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.

Add a foreign key and a check constraint to an existing table.

Show answer

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0); First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.

You need customers who bought both product A and product B. How would you write it?

JoinsMedium

Show answer

Group by customer and count distinct matching products: SELECT customer_id FROM order_items WHERE product_id IN ('A', 'B') GROUP BY customer_id HAVING COUNT(DISTINCT product_id) = 2. Alternatively use INTERSECT of two queries, or two EXISTS conditions. The HAVING approach is concise, scans the table once and is easy to extend to three or more products.

Design tables for students, courses and enrolments.

Database DesignEasy

Show answer

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE courses ( course_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE enrolments ( student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), enrolled_on DATE, PRIMARY KEY (student_id, course_id) ); The junction table resolves the many-to-many relationship.

Find employees earning more than the company average.

Subqueries and CTEsEasy

Show answer

SELECT emp_id, name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) ORDER BY salary DESC; The inner query is a non-correlated scalar subquery that runs once and returns one value used for comparison.

Find employees who earn more than their department average.

Show answer

SELECT e.emp_id, e.name, e.department_id, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id ); This is a correlated subquery. A window version uses AVG(salary) OVER (PARTITION BY department_id) in a CTE and filters in the outer query.

What are ROLLUP, CUBE and GROUPING SETS?

Aggregation and GroupingMedium

Show answer

They produce multiple grouping levels in one query. ROLLUP(region, city) gives city totals, region subtotals and a grand total. CUBE(region, product) gives all combinations of subtotals. GROUPING SETS lets you list exact combinations, such as (region), (product) and (). The GROUPING function identifies subtotal rows, which is useful for labelling them in reports.

What is a composite index and why does column order matter?

Indexes and PerformanceEasy

Show answer

A composite index covers multiple columns, such as (customer_id, order_date). It is sorted by the first column, then the second within it. It helps queries filtering on customer_id alone or on both columns, but not queries filtering only on order_date, because of the leftmost prefix rule. Put the most commonly filtered equality column first, followed by range columns.

What is the difference between a unique index and a normal index?

Indexes and PerformanceEasy

Show answer

A unique index enforces that no two rows have the same value in the indexed columns, and it is created automatically for primary keys and unique constraints. A normal index only speeds up lookups and allows duplicates. Both speed up searches, but a unique index also protects data integrity, for example preventing two users from registering with the same email.

What are database statistics and why do they matter?

Indexes and PerformanceMedium

Show answer

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.

Write a query to pivot order counts by status into columns.

SQL BasicsMedium

Show answer

SELECT customer_id, SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled, SUM(CASE WHEN status = 'Returned' THEN 1 ELSE 0 END) AS returned FROM orders GROUP BY customer_id; Conditional aggregation works in every database, unlike vendor-specific PIVOT syntax.

What are ACID properties?

Transactions and Advanced SQLEasy

Show answer

Atomicity: all statements in a transaction succeed or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not interfere and behave as if run in some order, depending on isolation level. Durability: once committed, changes survive crashes because they are written to logs. Banking and payment systems depend on ACID.

What is the difference between GROUP BY and window functions?

Window FunctionsEasy

Show answer

GROUP BY collapses rows into one row per group, so detail columns are lost. Window functions keep every row and add the computed value alongside, such as each employee with their department average. You can combine both: group first, then apply a window over the grouped result, for example percent of total using SUM(SUM(sales)) OVER ().

Explain 1NF, 2NF and 3NF with simple examples.

Database DesignEasy

Show answer

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.

A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?

Show answer

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.

What is the difference between IN and EXISTS?

Show answer

IN checks whether a value matches any value in a list or subquery result. EXISTS checks whether a correlated subquery returns at least one row and stops at the first match. Modern optimisers often treat them similarly, but EXISTS is clearer for correlated checks and handles NULLs safely. For large subquery results, EXISTS with an index on the join column is usually efficient.

What is a window frame and what is the default frame?

Show answer

A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.

Your NOT IN query returns zero rows even though you expect results. What do you check?

Show answer

Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.

How do databases execute GROUP BY internally?

Aggregation and GroupingHard

Show answer

The optimiser uses either hash aggregation, building a hash table keyed by group values, which is fast for unsorted data with enough memory, or stream (sort) aggregation, which needs input sorted by the group keys, often from an index. Large groups that do not fit memory spill to disk. Indexes on grouping columns and pre-filtering reduce the cost.

Rewrite a non-SARGable date filter to use an index.

Indexes and PerformanceMedium

Show answer

Slow: SELECT * FROM orders WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 3; Fast: SELECT * FROM orders WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'; The second form compares the raw column, so an index on order_date can be used with a range seek.

What are the common SQL data types?

SQL BasicsEasy

Show answer

Common types are INT, BIGINT and SMALLINT for whole numbers, DECIMAL or NUMERIC for exact values like money, FLOAT for approximate values, CHAR for fixed-length and VARCHAR for variable-length text, DATE, TIME, DATETIME or TIMESTAMP for dates, and BOOLEAN where supported. Choosing correct types saves space, improves performance and prevents errors such as storing amounts in FLOAT and getting rounding issues.

Average order value looks too low in a report. What could be wrong?

Aggregation and GroupingMedium

Show answer

Check the grain: if the query averages order items instead of orders, each line counts separately and the average drops. Joins can also duplicate rows. Verify whether cancelled or zero-value orders are included and how NULL amounts are treated. Compute SUM(amount) / COUNT(DISTINCT order_id) at order level and compare with business definitions before changing the report.

Find cities with more than 100 customers.

Aggregation and GroupingEasy

Show answer

SELECT city, COUNT(*) AS customers FROM customers GROUP BY city HAVING COUNT(*) > 100 ORDER BY customers DESC; HAVING is needed because the condition uses an aggregate. A WHERE clause could still filter rows first, for example only active customers.

What is a self join? Give an example.

JoinsEasy

Show answer

A self join joins a table to itself using aliases, used for hierarchical or comparative data in one table. Example: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id. Each employee row is matched with the row of its manager. LEFT JOIN keeps the top manager whose manager_id is NULL.

What is an index in a database?

Indexes and PerformanceEasy

Show answer

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.

What is GROUP BY and what is the rule for selected columns?

Aggregation and GroupingEasy

Show answer

GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.

Write a query to list orders with customer names.

JoinsEasy

Show answer

SELECT o.order_id, o.order_date, c.name, o.amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_date DESC; Aliases o and c keep the query short and clear. INNER JOIN drops orders whose customer is missing, so use LEFT JOIN if such orders must still appear.

What is a covering index?

Indexes and PerformanceMedium

Show answer

A covering index contains all columns a query needs, either as key columns or included columns, so the database answers the query from the index alone without visiting the table. For SELECT order_date, amount FROM orders WHERE customer_id = 5, an index on (customer_id) INCLUDE (order_date, amount) avoids lookups. It speeds reads but increases index size and write cost.

What are LAG and LEAD functions?

Show answer

LAG returns a value from a previous row in the partition and LEAD from a following row, based on the ORDER BY. LAG(revenue, 1, 0) OVER (ORDER BY month) gives last month's revenue with 0 as default for the first row. They are used for month-over-month change, time between events, and detecting status changes without self joins.

What is normalisation and why is it needed?

Database DesignEasy

Show answer

Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.

What are constraints in SQL?

Show answer

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.

A query with a correlated subquery in SELECT runs for every row and is slow. How do you improve it?

Show answer

Rewrite the correlated scalar subquery as a join to a pre-aggregated derived table or CTE, for example aggregate order counts per customer once and LEFT JOIN it. Alternatively use a window function. Ensure the correlation column is indexed if you keep the subquery. Compare execution plans and timing before and after to confirm the improvement.

How do you insert, update and delete a row?

SQL BasicsEasy

Show answer

INSERT INTO students (student_id, name, city) VALUES (101, 'Anita', 'Noida'); UPDATE students SET city = 'Gurugram' WHERE student_id = 101; DELETE FROM students WHERE student_id = 101; Always use WHERE with UPDATE and DELETE; without it every row changes.

Write a query to show each employee with their manager's name.

JoinsMedium

Show answer

SELECT e.emp_id, e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; The same table is used twice with different aliases. LEFT JOIN ensures the CEO, who has no manager, still appears with a NULL manager name.

How does AVG treat NULL values?

Aggregation and GroupingEasy

Show answer

AVG ignores NULLs, dividing the sum of non-null values by the count of non-null values. If three employees have bonuses 1000, 2000 and NULL, AVG(bonus) is 1500, not 1000. If NULL should mean zero, use AVG(COALESCE(bonus, 0)). Interviewers ask this to check whether you understand how missing data changes business metrics.

What is the difference between a correlated and a non-correlated subquery?

Subqueries and CTEsEasy

Show answer

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.

What is a star schema and a snowflake schema?