Structured Query Language (SQL) is the backbone of modern database management, widely used in various domains including business, healthcare, education, and technology. SQL provides a robust and standardized way to store, retrieve, and manipulate data in relational databases, making it an essential skill for developers, data analysts, and database administrators. Whether you’re preparing for an interview or brushing up on your SQL knowledge, mastering SQL concepts is crucial for managing data effectively and optimizing database performance.
This article compiles the top 50 SQL practice questions and answers, designed to cater to beginners, intermediate learners, and advanced practitioners. It encompasses fundamental concepts like primary keys and joins, intermediate topics like indexes and subqueries, and advanced subjects like window functions and recursive queries. Additionally, practical problem-solving scenarios are included to help you tackle real-world challenges with confidence.
Whether you’re just starting your SQL journey or seeking to deepen your expertise, these questions and answers serve as a comprehensive guide to understanding and applying SQL. Dive into this resource to enhance your database skills and ace your next technical challenge.
Table of Contents
Top 50 SQL Questions and Answers.
Practical Scenarios and Problem-Solving.
American Tourister Valex 28 Ltrs Large Laptop Backpack
Limited time deal
Priced at -44% discount for ₹1,399
Beginner Level
- What is SQL? SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases. It is used for tasks like querying, updating, and managing data.
- What are the different types of SQL commands?
- DDL (Data Definition Language): CREATE, ALTER, DROP
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language): GRANT, REVOKE
- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
- What is a primary key? A primary key is a column or set of columns in a table that uniquely identifies each row. It cannot contain NULL values.
- What is a foreign key? A foreign key is a column in a table that establishes a relationship with a primary key in another table.
- What is the difference between WHERE and HAVING clauses?
- WHERE: Filters rows before grouping.
- HAVING: Filters groups after grouping.
- What is the use of the DISTINCT keyword? DISTINCT removes duplicate records from the results.
Example:
SELECT DISTINCT column_name FROM table_name;
- What is a join in SQL? Name its types. A join is used to combine rows from two or more tables based on a related column.
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- CROSS JOIN
- SELF JOIN
- What is the difference between INNER JOIN and LEFT JOIN?
- INNER JOIN: Returns only matching rows.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
- How do you retrieve the current date in SQL?
SELECT CURRENT_DATE;
- What is the purpose of the GROUP BY clause? GROUP BY is used to arrange identical data into groups for aggregation.
Example:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
Intermediate Level
- What is a subquery? A subquery is a query nested within another query. It can return individual values or entire result sets.
- What is a view? How is it different from a table? A view is a virtual table based on the result of a SELECT query. Unlike tables, views do not store data physically.
- Explain normalization. Normalization is the process of organizing data to reduce redundancy and improve integrity. Normal forms include 1NF, 2NF, 3NF, and BCNF.
- What is the difference between DELETE and TRUNCATE?
- DELETE: Removes specific rows and can include a WHERE clause.
- TRUNCATE: Removes all rows without logging individual row deletions.
- What are indexes in SQL? Indexes improve the speed of data retrieval operations. Types include:
- Unique Index
- Composite Index
- Clustered Index
- Non-clustered Index
- What is a stored procedure? A stored procedure is a precompiled collection of SQL statements that can be executed as a program.
Example:
CREATE PROCEDURE ProcedureName
AS
BEGIN
SELECT * FROM table_name;
END;
- What is the difference between UNION and UNION ALL?
- UNION: Removes duplicates.
- UNION ALL: Includes duplicates.
- What are the ACID properties in databases?
- Atomicity: Transactions are all-or-nothing.
- Consistency: Ensures data validity.
- Isolation: Transactions do not interfere with each other.
- Durability: Completed transactions persist permanently.
- How do you create a database in SQL?
CREATE DATABASE database_name;
- What is a trigger? A trigger is a procedural code executed automatically in response to certain events on a table.
Example:
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
— code
END;
Advanced Level
- What is a CTE (Common Table Expression)? A CTE is a temporary result set defined within the execution scope of a single query.
Example:
WITH CTE_Name AS (
SELECT column_name FROM table_name
)
SELECT * FROM CTE_Name;
- What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
- ROW_NUMBER(): Assigns a unique number to each row.
- RANK(): Assigns a rank, skipping numbers for ties.
- DENSE_RANK(): Assigns a rank without skipping numbers for ties.
- What is the purpose of the EXISTS operator? EXISTS checks for the existence of rows returned by a subquery.
Example:
SELECT column_name
FROM table_name
WHERE EXISTS (
SELECT 1 FROM another_table WHERE condition
);
- How can you optimize SQL queries?
- Use indexes.
- Avoid SELECT *.
- Use JOINs instead of subqueries when possible.
- Limit the result set with WHERE and LIMIT clauses.
- Use EXPLAIN to analyze query execution plans.
- What is a partition in SQL? Partitioning divides a table into parts for performance improvement. Types include range, list, hash, and composite partitions.
- Explain window functions. Window functions perform calculations across a set of table rows related to the current row.
Example:
SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY another_column)
FROM table_name;
- What is sharding? Sharding is the process of dividing a database into smaller, faster parts called shards for horizontal scalability.
- How do you handle NULL values in SQL? Use IS NULL, IS NOT NULL, COALESCE(), or NULLIF() functions.
Example:
SELECT COALESCE(column_name, ‘default_value’) FROM table_name;
- What is the difference between an alias and a synonym?
- Alias: A temporary name for a table or column.
- Synonym: A permanent alias for a database object.
- What are materialized views? Materialized views store the results of a query physically for faster access.
Practical Scenarios and Problem-Solving
- How to find the second highest salary in a table?
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
- How to retrieve duplicate rows from a table?
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
- How to delete duplicate rows in a table?
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id)
FROM table_name
GROUP BY column_name
);
- How to fetch the top N records?
SELECT * FROM table_name
ORDER BY column_name DESC
LIMIT N;
- Write a query to find employees who earn more than their department’s average salary.
SELECT employee_name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
- How to calculate the running total in SQL?
SELECT column_name, SUM(column_name) OVER (ORDER BY another_column) AS running_total
FROM table_name;
- Write a query to pivot data in SQL.
SELECT *
FROM (SELECT column1, column2, value_column FROM table_name) AS SourceTable
PIVOT (
SUM(value_column) FOR column2 IN ([Value1], [Value2], [Value3])
) AS PivotTable;
- How to unpivot data in SQL?
SELECT column1, UnpivotedColumn, Value
FROM table_name
UNPIVOT (
Value FOR UnpivotedColumn IN (column2, column3, column4)
) AS UnpivotTable;
- How do you write recursive queries using CTEs?
WITH RecursiveCTE AS (
SELECT column_name FROM table_name WHERE condition
UNION ALL
SELECT column_name FROM table_name JOIN RecursiveCTE ON condition
)
SELECT * FROM RecursiveCTE;
- How to find the nth highest salary in a table?
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET n-1;
- How to perform a full-text search in SQL?
SELECT * FROM table_name
WHERE CONTAINS(column_name, ‘search_term’);
- Write a query to merge data from two tables.
MERGE INTO target_table USING source_table
ON target_table.id = source_table.id
WHEN MATCHED THEN UPDATE SET target_table.column = source_table.column
WHEN NOT MATCHED THEN INSERT (columns) VALUES (values);
- How to use JSON functions in SQL?
- To parse JSON data: Use JSON_VALUE().
- To query nested JSON: Use JSON_QUERY().
- To modify JSON: Use JSON_MODIFY().
Example:
SELECT JSON_VALUE(json_column, ‘$.key’) AS key_value
FROM table_name;
- How to find gaps in a sequence of numbers?
SELECT a.id + 1 AS start_gap, MIN(b.id) – 1 AS end_gap
FROM table_name a, table_name b
WHERE a.id < b.id
GROUP BY a.id
HAVING start_gap <= end_gap;
- How to rank employees based on their salary?
SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
- How to calculate the difference between two dates?
SELECT DATEDIFF(day, start_date, end_date) AS days_difference
FROM table_name;
- How to list all tables in a database?
SELECT table_name
FROM information_schema.tables
WHERE table_schema = ‘database_name’;
- How to find the longest name in a column?
SELECT column_name
FROM table_name
WHERE LEN(column_name) = (SELECT MAX(LEN(column_name)) FROM table_name);
- How to find common records between two tables?
SELECT *
FROM table1
INTERSECT
SELECT *
FROM table2;
- How to generate a sequence of numbers in SQL?
WITH Numbers AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1 FROM Numbers WHERE num < 10
)
SELECT * FROM Numbers;
I hope you found value in the above material. If you did please comment and let us know about it.
Curated Reads
