SQL Questions and Answers

Question 1: What is the sequence of execution of SQL codes (e.g., SELECT, FROM,
WHERE, HAVING, ORDER BY)?
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Question 2: Write a query to retrieve all customer names that start with the letter
’A’.
SELECT customer_name FROM customers WHERE customer_name LIKE 'A%';
Question 3: Write code to describe the database and columns from a particular table.
Use DESC table_name; or SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
table_name = 'table_name';
Question 4: What is the difference between the HAVING and WHERE clauses?
WHERE filters individual rows before grouping; HAVING filters groups after GROUP
BY.
Question 5: What is SQL, what are the types of joins, and what is the main differ-
ence between UNION and UNION ALL?
SQL is a language for managing relational databases. Joins: INNER, LEFT,
RIGHT, FULL OUTER. UNION removes duplicates; UNION ALL includes them.
Question 6: What are the different types of joins in SQL, and what are the outputs
when joining two tables with nulls?
Joins: INNER (matching rows only), LEFT (all left table rows, NULL for non-
matching right), RIGHT (all right table rows, NULL for non-matching left), FULL
OUTER (all rows, NULL where no match). Nulls are excluded in INNER JOIN,
included as NULL in others.
Question 7: Given a table with match details (countries in first two columns, winner
in the third), write a query to get the number of matches played by each
team and the number of matches won by each team.
SELECT team, COUNT(*) AS matches_played, SUM(CASE WHEN winner = team THEN
1 ELSE 0 END) AS matches_won FROM (SELECT team1 AS team FROM matches UNION
ALL SELECT team2 FROM matches) t GROUP BY team;
Question 8: What is row number, rank, dense rank? Explain partition by, indexing
joins, query optimization techniques.
ROW_NUMBER assigns unique sequential numbers; RANK assigns same rank for
ties with gaps; DENSE_RANK assigns same rank without gaps. PARTITION BY
divides data into partitions for window functions. Indexing improves join perfor-
mance. Optimization: use specific columns, avoid SELECT *, use indexes, optimize
joins.
Question 9: Write an SQL query to find out the different routes for an airline with
two columns: destination and source.
SELECT DISTINCT source, destination FROM airline_routes ORDER BY source,
destination;
Question 10: Can aggregate functions be used with HAVING clause? With WHERE
clause? Can HAVING be used without GROUP BY?

1
 Aggregate functions work with HAVING to filter groups, not with WHERE (which
filters rows). HAVING requires GROUP BY.
Question 11: Write a query to extract the details of all employees whose salary is more
than PHONE_NUMBER.
Assuming PHONE_NUMBER is a placeholder (e.g., 5000000): SELECT * FROM
employees WHERE salary > 5000000;
Question 12: Write a SELECT query to find the highest unique number in a column.
SELECT column_name FROM table_name WHERE column_name IN (SELECT column_name
FROM table_name GROUP BY column_name HAVING COUNT(*) = 1) ORDER BY column_name
DESC LIMIT 1;
Question 13: How would you separate strings with the same first two letters from the
remaining characters using SQL?
SELECT LEFT(name, 2) AS prefix, SUBSTRING(name, 3, LEN(name)) AS remainder
FROM table_name;
Question 14: How do you select consecutive data from a column?
SELECT column_name FROM (SELECT column_name, ROW_NUMBER() OVER (ORDER
BY column_name) AS rn FROM table_name) t WHERE rn = (SELECT rn + 1 FROM
table_name WHERE column_name = t.column_name);
Question 15: Can you provide a real-life example of how you have used window func-
tions, specifically LAG and LEAD?
LAG to compare current sales with previous day’s sales: SELECT sale_date,
sales, LAG(sales) OVER (ORDER BY sale_date) AS prev_sales FROM sales;.
LEAD to forecast next day’s sales: SELECT sale_date, sales, LEAD(sales)
OVER (ORDER BY sale_date) AS next_sales FROM sales;.
Question 16: Can you union tables with different data types?
Yes, but columns must have compatible data types or require conversion. Resulting
columns adopt a common data type (e.g., INT and FLOAT result in FLOAT).
Question 17: What is a trigger in SQL?
A trigger is a stored procedure that automatically executes on events like INSERT,
UPDATE, or DELETE to enforce rules or automate tasks.
Question 18: How can you remove duplicates using SQL?
Use SELECT DISTINCT column_name FROM table_name; or SELECT column_name
FROM table_name GROUP BY column_name;.
Question 19: How would you SELECT data from table A that’s not in table B without
using NOT EXISTS or LEFT JOIN?
SELECT * FROM tableA WHERE columnA NOT IN (SELECT columnB FROM tableB);
Question 20: How do you perform an EXISTS query in SQL?
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column
= table1.column);
Question 21: Write a query to retrieve all employee names who have joined in the last
30 days.
SELECT employee_name FROM employees WHERE join_date >= DATEADD(day, -30,

2
 GETDATE());
Question 22: What is the difference between GROUP BY and DISTINCT when per-
formed on a single column?
GROUP BY groups rows for aggregation; DISTINCT removes duplicate rows with-
out aggregation.
Question 23: What is the difference between TRUNCATE, DELETE, and DROP?
TRUNCATE removes all rows (DDL, non-rollbackable); DELETE removes specific
rows (DML, rollbackable); DROP deletes the table structure.
Question 24: How do you add months to the current date using SQL functions?
SELECT DATEADD(month, 3, GETDATE());
Question 25: Write a query to connect two tables in SQL.
SELECT * FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_co
Question 26: What is an index in SQL?
An index is a database object that speeds up data retrieval by storing a copy of
selected columns.
Question 27: What is a trigger function in SQL?
A trigger function is a stored procedure that auto-executes on specific table events
(e.g., INSERT, UPDATE) to enforce rules or audit changes.
Question 28: What are the limitations of SQL?
SQL struggles with unstructured data, scalability, real-time processing, and is vul-
nerable to SQL injection if not secured.
Question 29: Find the product IDs that were returned more than 10 times in a month
and more than 3 times in a year.
SELECT product_id FROM returns WHERE return_date BETWEEN 'YYYY-MM-01'
AND 'YYYY-MM-31' GROUP BY product_id HAVING COUNT(*) > 10 AND product_id
IN (SELECT product_id FROM returns WHERE return_date BETWEEN 'YYYY-01-01'
AND 'YYYY-12-31' GROUP BY product_id HAVING COUNT(*) > 3);
Question 30: What is the difference between VLOOKUP in Excel and a similar func-
tion in SQL?
VLOOKUP searches a single table in Excel; SQL JOIN/WHERE retrieves data
from multiple tables without sorting requirements.
Question 31: Differentiate between WITH and HAVING clause in SQL.
WITH creates a temporary result set (CTE); HAVING filters groups after GROUP
BY.
Question 32: How do you perform a cross join in SQL?
SELECT * FROM table1 CROSS JOIN table2;
Question 33: Write a query to find the top three salaried employees of each depart-
ment.
SELECT department, employee_name, salary FROM (SELECT department, employee_name,
salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
AS rn FROM employees) t WHERE rn <= 3;

3
Question 34: How do you calculate the cumulative sum for a given table?
SELECT column_name, SUM(value) OVER (ORDER BY column_name) AS cumulative_sum
FROM table_name;
Question 35: Write a query to extract the data and apply filters 2 and 3.
Assuming ’2 3’ is a condition: SELECT * FROM table_name WHERE column_name
= '2 3';
Question 36: Write a SQL query to retrieve the highest salary from the employee
table.
SELECT MAX(salary) FROM employees;
Question 37: What is the difference between char and varchar?
CHAR is fixed-length, VARCHAR is variable-length, saving space for shorter strings.
Question 38: Find the customer_id with the maximum number of consecutive pur-
chases.
WITH purchases AS (SELECT customer_id, purchase_date, ROW_NUMBER() OVER
(PARTITION BY customer_id ORDER BY purchase_date) - ROW_NUMBER() OVER
(PARTITION BY customer_id, DATEDIFF(day, '1970-01-01', purchase_date)
ORDER BY purchase_date) AS group_id FROM orders) SELECT customer_id, MAX(group_co
AS max_consecutive FROM (SELECT customer_id, group_id, COUNT(*) AS group_count
FROM purchases GROUP BY customer_id, group_id) t GROUP BY customer_id
ORDER BY max_consecutive DESC LIMIT 1;
Question 39: Write a SQL query to find customers who have not placed any orders.
SELECT c.customer_id, c.customer_name FROM customers c LEFT JOIN orders
o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
Question 40: What is the difference between SQL and MySQL?
SQL is a standard query language; MySQL is an open-source database management
system using SQL.
Question 41: Write an SQL query using a CASE WHEN THEN statement.
SELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS age_group
FROM customers;
Question 42: How do you select columns?
SELECT column1, column2 FROM table_name;
Question 43: How do you extract the day of the week from a column?
SELECT DAYOFWEEK(date_column) AS day_of_week FROM table_name;
Question 44: What is the SQL Server architecture?
Comprises Database Engine (data storage/processing), SQL Server Agent (automa-
tion/scheduling), and SQL Server Browser (connection management).
Question 45: How do you optimize an SQL query?
Use indexes, select specific columns, avoid SELECT *, optimize joins, use WHERE
before joins, prefer UNION ALL over UNION, limit results.
Question 46: What is the difference between import and direct query?
Import loads data into Power BI (faster, needs refresh); Direct Query connects to
the source in real-time (slower, up-to-date).

4
Question 47: How do you extract numbers from strings in SQL?
SELECT REGEXP_REPLACE(column_name, '[0 −9]′ ,′′ )ASextracted_numbersF ROM table_name;
Question 48: Write a query to calculate the running sum of salaries.
SELECT employee_id, salary, SUM(salary) OVER (ORDER BY employee_id) AS
running_sum FROM employees;
Question 49: Write an SQL query to find the highest earners in each department.
SELECT department, employee_name, salary FROM (SELECT department, employee_name,
salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
AS rn FROM employees) t WHERE rn = 1;
Question 50: What are the different types of SQL commands?
DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE),
DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK).
Question 51: What are SQL functions used in projects?
COUNT, SUM, AVG, MAX, MIN, GROUP BY for aggregation and summarization.
Question 52: How do you create flat tables using SQL?
SELECT t1.column1, t1.column2, t2.column1 INTO flat_table FROM table1
t1 JOIN table2 t2 ON t1.key = t2.key;
Question 53: How do you pull datasets from SQL to Power BI and perform visualiza-
tion?
Connect Power BI to SQL database, use SQL queries to import data, create visu-
alizations (e.g., charts, dashboards) in Power BI.
Question 54: What is the difference between GROUP BY and DISTINCT?
GROUP BY aggregates data with functions (e.g., SUM); DISTINCT removes du-
plicate rows without aggregation.
Question 55: What is SQL, what is RDBMS, what are non-SQL tools, what is schema?
SQL: Language for relational databases. RDBMS: Software managing databases
(e.g., MySQL). Non-SQL: NoSQL databases (e.g., MongoDB). Schema: Database
structure blueprint.
Question 56: What is a database, joins, trigger, view, aggregate function, ETL, data
warehouse?
Database: Organized data collection. Joins: Combine tables. Trigger: Auto-
executed procedure on events. View: Virtual table from query. Aggregate: Func-
tions like SUM, COUNT. ETL: Extract, Transform, Load process. Data Warehouse:
Centralized data storage for analytics.
Question 57: What are data types in SQL?
INT, FLOAT, VARCHAR, DATE, BOOLEAN, etc., defining column data proper-
ties.
Question 58: How do you apply filters to rows?
SELECT * FROM table_name WHERE condition;
Question 59: How do you perform an UPDATE query in SQL?
UPDATE table_name SET column_name = value WHERE condition;

5
Question 60: Write MySQL codes using window functions.
SELECT employee_id, salary, ROW_NUMBER() OVER (PARTITION BY department
ORDER BY salary DESC) AS rank FROM employees;
Question 61: How does applying certain join operations affect the number of rows?
INNER JOIN: matching rows only. LEFT JOIN: all left table rows. RIGHT JOIN:
all right table rows. FULL OUTER JOIN: all rows from both tables.
Question 62: Implement LAG() and LEAD() functions without using the built-in
LAG() or LEAD().
-- LAG equivalent SELECT t1.value, (SELECT t2.value FROM table_name t2
WHERE t2.id = t1.id - 1) AS lag_value FROM table_name t1; -- LEAD equivalent
SELECT t1.value, (SELECT t2.value FROM table_name t2 WHERE t2.id = t1.id
+ 1) AS lead_value FROM table_name t1;
Question 63: What is DML in SQL?
Data Manipulation Language for inserting, updating, deleting, and retrieving data
(e.g., SELECT, INSERT, UPDATE, DELETE).
Question 64: What is DCL in SQL?
Data Control Language for managing access (e.g., GRANT, REVOKE).
Question 65: How do you handle columns with special characters?
SELECT REGEXP_REPLACE(column_name, '[a −zA−Z0−9]′ ,′′ )ASextracted_numbersF ROM table
Question 66: What is a CTE in SQL, and what are subqueries?
CTE: Temporary result set using WITH clause. Subquery: Nested query within
another query.
Question 67: What is the difference between RIGHT JOIN and RIGHT OUTER
JOIN?
No difference; both return all rows from the right table and matching rows from
the left table.
Question 68: What are aggregate functions?
Functions like SUM, AVG, COUNT, MIN, MAX that compute a single value from
a set of rows.
Question 69: What is a pivot table?
A data summarization tool (common in Excel) that groups and aggregates data for
analysis.
Question 70: How do you calculate TDI and MDI of a customer in SQL?
SELECT customer_id, SUM(income) AS TDI, AVG(income) AS MDI FROM transactions
WHERE customer_id = '123' GROUP BY date;