Top 50 SQL Interview Questions and Answers | Crack SQL Interviews Easily
SQL (Structured Query Language) remains one of the most essential skills for developers, data analysts, and database administrators. Whether you’re a fresh graduate or an experienced professional, SQL interview questions are inevitable in technical interviews. This comprehensive guide covers the top 50 SQL queries that frequently appear in interviews, complete with explanations and interactive answers.
What is SQL?
SQL (often pronounced “sequel”) is a domain-specific language designed for managing data held in relational database management systems. Unlike general-purpose programming languages, SQL was specifically created to handle structured data, enabling users to define, manipulate, and control access to data in a relational database.
At its core, SQL follows a declarative programming paradigm—you specify what you want to accomplish, not how to accomplish it. The database engine determines the most efficient way to execute your query, abstracting away the complexities of data storage, indexing, and retrieval algorithms.
The Importance of SQL in Today’s Data Ecosystem
In our data-driven world, SQL remains indispensable for several reasons:
- Universal Adoption: Virtually every major database platform—from traditional systems like Oracle, SQL Server, and MySQL to modern cloud-native solutions like Amazon Redshift, Google BigQuery, and Snowflake—supports SQL.
- Integration with Modern Technologies: Despite being developed decades ago, SQL continues to evolve and integrate with modern technologies. It interfaces seamlessly with big data frameworks like Hadoop (through Hive), streaming platforms (via systems like Kafka SQL), and even NoSQL databases (through SQL-like query layers).
- Foundation for Data Analytics: SQL serves as the foundation for data analytics and business intelligence. Tools like Tableau, Power BI, and Looker all rely on SQL to transform raw data into actionable insights.
- Essential for Data Engineering: Data engineers use SQL to build ETL (Extract, Transform, Load) pipelines, data warehouses, and data lakes, forming the backbone of an organization’s data infrastructure.
- Critical for Software Development: Most web applications and enterprise software rely on SQL databases to store and retrieve application data, making SQL knowledge essential for backend developers.
- Accessible Entry Point to Data Science: SQL often serves as an entry point for aspiring data scientists, providing a relatively straightforward way to start working with structured data before moving to more complex analytical techniques.
The SQL Ecosystem
The SQL ecosystem encompasses various implementations and extensions:
- Database Management Systems: Major SQL database systems include Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, and many others. Each has its own dialect of SQL with unique features and optimizations.
- SQL Variants: Different SQL implementations may have variations in syntax and features. Common variants include T-SQL (Microsoft), PL/SQL (Oracle), and SQL/PSM (SQL/Persistent Stored Modules).
- SQL Standards: The ANSI/ISO SQL standard provides a common framework, though most database systems implement extensions beyond the standard.
- NewSQL: A class of modern relational database systems that provide the scalability of NoSQL systems while maintaining the ACID guarantees of traditional SQL databases.
Core SQL Concepts
SQL is built around several fundamental concepts:
Query Optimization: SQL databases employ sophisticated query optimizers to determine the most efficient execution plan.
Data Definition Language (DDL): Commands like CREATE, ALTER, and DROP for defining and modifying database structures.
Data Manipulation Language (DML): Commands like SELECT, INSERT, UPDATE, and DELETE for querying and modifying data.
Data Control Language (DCL): Commands like GRANT and REVOKE for controlling access to data.
Transaction Control Language (TCL): Commands like COMMIT and ROLLBACK for managing transactions.
Relational Model: Data organized in tables (relations) with rows (tuples) and columns (attributes), with relationships established through keys.
ACID Properties: SQL databases traditionally follow ACID principles: Atomicity, Consistency, Isolation, and Durability.
Understanding the Foundation
Before getting into the questions, let’s understand that most of these queries are based on common database tables like worker, title, and bonus. These represent typical business scenarios you’ll encounter in real-world applications.
Sample Table Structure
-- Worker Table
CREATE TABLE worker (
worker_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10,2),
joining_date DATE,
department VARCHAR(50)
);
-- Title Table
CREATE TABLE title (
worker_ref_id INT,
worker_title VARCHAR(50),
affected_from DATE
);
-- Bonus Table
CREATE TABLE bonus (
worker_ref_id INT,
bonus_amount DECIMAL(10,2),
bonus_date DATE
);
String Manipulation and Basic Queries
Q1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as WORKER_NAME.
SELECT first_name AS WORKER_NAME FROM worker;
Key Learning: Aliases make query results more readable and are essential for report generation.
Q2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.
SELECT UPPER(first_name) FROM worker;
Variation: You can also use LOWER() for lowercase conversion.
Q3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table.
SELECT DISTINCT department FROM worker;
Important: DISTINCT eliminates duplicate rows from the result set.
Q4. Write an SQL query to print the first three characters of FIRST_NAME from Worker table.
SELECT SUBSTRING(first_name, 1, 3) FROM worker;
Note: Different databases may use SUBSTR() instead of SUBSTRING().
Q5. Write an SQL query to find the position of the alphabet (‘b’) in the first name column ‘Amitabh’ from Worker table.
SELECT INSTR(first_name, 'B') FROM worker
WHERE first_name = 'Amitabh';
Alternative: PostgreSQL uses POSITION() function.
String Trimming and Cleaning
Q6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.
SELECT RTRIM(first_name) FROM worker;
Best Practice: Always clean data when importing from external sources.
Q7. Write an SQL query to print the DEPARTMENT from Worker table after removing white spaces from the left side.
SELECT LTRIM(department) FROM worker;
Note: The original question mentions FIRST_NAME but the solution uses department, showing a common interview twist.
Q8. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length
SELECT DISTINCT department, LENGTH(department) FROM worker;
Use Case: Helpful for data validation and understanding column constraints.
Q9. Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.
SELECT REPLACE(first_name, 'a', 'A') FROM worker;
Advanced: You can chain multiple REPLACE functions for complex replacements.
Q10. Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME. A space char should separate them.
SELECT CONCAT(first_name, ' ', last_name) AS COMPLETE_NAME FROM worker;
Alternative: Some databases use || operator: first_name || ' ' || last_name
Sorting and Filtering
Q11. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.
SELECT * FROM worker ORDER BY first_name;
Performance Tip: Add indexes on columns frequently used in ORDER BY clauses.
Q12. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending and DEPARTMENT Descending.
SELECT * FROM worker ORDER BY first_name, department DESC;
Note: Each column can have its own sort direction.
Q13. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.
SELECT * FROM worker WHERE first_name IN ('Vipul', 'Satish');
Alternative: WHERE first_name = 'Vipul' OR first_name = 'Satish'
Q14. Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker table.
SELECT * FROM worker WHERE first_name NOT IN ('Vipul', 'Satish');
Caution: NOT IN with NULL values can produce unexpected results.
Pattern Matching with LIKE
Q15. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin*”.
SELECT * FROM worker WHERE department LIKE 'Admin%';
Pattern: % matches any sequence of characters after “Admin”.
Q16. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.
SELECT * FROM worker WHERE first_name LIKE '%a%';
Case Sensitivity: This is case-sensitive in most databases.
Q17. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.
SELECT * FROM worker WHERE first_name LIKE '%a';
Pattern: % at the beginning matches any characters before ‘a’.
Q18. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.
SELECT * FROM worker WHERE first_name LIKE '_____h';
Pattern Guide: Each _ represents exactly one character, so 5 underscores + ‘h’ = 6 characters total.
Range and Date Queries
Q19. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.
SELECT * FROM worker WHERE salary BETWEEN 100000 AND 500000;
Alternative: WHERE salary >= 100000 AND salary <= 500000
Q20. Write an SQL query to print details of the Workers who have joined in Feb 2014.
SELECT * FROM worker
WHERE YEAR(joining_date) = 2014 AND MONTH(joining_date) = 02;
Alternative: WHERE joining_date BETWEEN '2014-02-01' AND '2014-02-28'
Aggregation Functions
Q21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.
SELECT department, COUNT(*) FROM worker WHERE department = 'Admin';
Note: Including department in SELECT helps identify which department the count refers to.
Q22. Write an SQL query to fetch worker full names with salaries >= 50000 and <= 100000.
SELECT CONCAT(first_name, ' ', last_name) FROM worker
WHERE salary BETWEEN 50000 AND 100000;
Best Practice: Always use meaningful column aliases for calculated fields.
Q23. Write an SQL query to fetch the no. of workers for each department in the descending order.
SELECT department, COUNT(worker_id) AS no_of_worker
FROM worker
GROUP BY department
ORDER BY no_of_worker DESC;
Key Concept: GROUP BY is required when mixing aggregate and non-aggregate columns.
Advanced Joins and Subqueries
Q24. Write an SQL query to print details of the Workers who are also Managers.
SELECT w.* FROM worker w
INNER JOIN title t ON w.worker_id = t.worker_ref_id
WHERE t.worker_title = 'Manager';
Join Type: INNER JOIN only returns records that exist in both tables.
Q25. Write an SQL query to fetch number (more than 1) of same titles in the ORG of different types.
SELECT worker_title, COUNT(*) as count FROM title
GROUP BY worker_title
HAVING count > 1;
HAVING vs WHERE: HAVING filters groups, WHERE filters individual rows.
Row Selection Techniques
Q26. Write an SQL query to show only odd rows from a table.
SELECT * FROM worker WHERE MOD(WORKER_ID, 2) <> 0;
Alternative: Use % operator: WHERE WORKER_ID % 2 != 0
Q27. Write an SQL query to show only even rows from a table.
SELECT * FROM worker WHERE MOD(WORKER_ID, 2) = 0;
Use Case: Useful for data sampling and testing scenarios.
Table Operations
Q28. Write an SQL query to clone a new table from another table.
CREATE TABLE worker_clone LIKE worker;
INSERT INTO worker_clone SELECT * FROM worker;
SELECT * FROM worker_clone;
Method 1: Creates exact structure then copies data. Method 2: CREATE TABLE worker_clone AS SELECT * FROM worker; (varies by database)
Q29. Write an SQL query to fetch intersecting records of two tables.
SELECT worker.* FROM worker
INNER JOIN worker_clone USING(worker_id);
Alternative: SELECT * FROM worker WHERE worker_id IN (SELECT worker_id FROM worker_clone);
Q30. Write an SQL query to show records from one table that another table does not have.
SELECT worker.* FROM worker
LEFT JOIN worker_clone USING(worker_id)
WHERE worker_clone.worker_id IS NULL;
Concept: LEFT JOIN + IS NULL finds records that exist only in the left table.
Date and Time Functions
Q31. Write an SQL query to show the current date and time.
-- Current date only
SELECT CURDATE();
-- Current date and time
SELECT NOW();
Database Variations:
- PostgreSQL:
CURRENT_DATE,CURRENT_TIMESTAMP - SQL Server:
GETDATE(),GETUTCDATE()
Advanced Ranking Queries
Q32. Write an SQL query to show the top n (say 5) records of a table order by descending salary.
SELECT * FROM worker ORDER BY salary DESC LIMIT 5;
SQL Server: Use TOP 5 instead of LIMIT 5 Oracle: Use ROWNUM <= 5 with subquery
Q33. Write an SQL query to determine the nth (say n=5) highest salary from a table.
SELECT * FROM worker ORDER BY salary DESC LIMIT 4,1;
Explanation: LIMIT 4,1 means skip first 4 records and return 1 record. Alternative: Use ROW_NUMBER() window function for better readability.
Q34. Write an SQL query to determine the 5th highest salary without using LIMIT keyword.
SELECT salary FROM worker w1
WHERE 4 = (
SELECT COUNT(DISTINCT(w2.salary))
FROM worker w2
WHERE w2.salary >= w1.salary
);
Logic: Counts how many distinct salaries are greater than or equal to current salary. If count is 4, then current salary is 5th highest.
Q35. Write an SQL query to fetch the list of employees with the same salary.
SELECT w1.* FROM worker w1, worker w2
WHERE w1.salary = w2.salary AND w1.worker_id != w2.worker_id;
Modern Approach: Use window functions with COUNT() OVER() for better performance.
Q36. Write an SQL query to show the second highest salary from a table using sub-query.
SELECT MAX(salary) FROM worker
WHERE salary NOT IN (SELECT MAX(salary) FROM worker);
Edge Case: This fails if there are NULL salaries. Better to use WHERE salary < (SELECT MAX(salary) FROM worker)
Data Manipulation Tricks
Q37. Write an SQL query to show one row twice in results from a table.
SELECT * FROM worker
UNION ALL
SELECT * FROM worker
ORDER BY worker_id;
UNION vs UNION ALL: UNION removes duplicates, UNION ALL keeps all records.
Q38. Write an SQL query to list worker_id who does not get bonus.
SELECT worker_id FROM worker
WHERE worker_id NOT IN (SELECT worker_ref_id FROM bonus);
NULL Safety: If bonus table has NULLs, use LEFT JOIN approach instead.
Percentage and Statistical Queries
Q39. Write an SQL query to fetch the first 50% records from a table.
Click to show answer
SELECT * FROM worker
WHERE worker_id <= (SELECT COUNT(worker_id)/2 FROM worker);
Assumption: This assumes worker_id is sequential from 1. For non-sequential IDs, use ROW_NUMBER().
Q40. Write an SQL query to fetch the departments that have less than 4 people in it.
SELECT department, COUNT(department) as depCount FROM worker
GROUP BY department
HAVING depCount < 4;
Key Point: HAVING is used to filter grouped results.
Q41. Write an SQL query to show all departments along with the number of people in there.
SELECT department, COUNT(department) as depCount FROM worker
GROUP BY department;
Alternative: COUNT(*) vs COUNT(department) – the latter excludes NULL values.
First and Last Record Queries
Q42. Write an SQL query to show the last record from a table.
SELECT * FROM worker
WHERE worker_id = (SELECT MAX(worker_id) FROM worker);
Alternative: ORDER BY worker_id DESC LIMIT 1 (assumes worker_id represents insertion order)
Q43. Write an SQL query to fetch the first row of a table.
SELECT * FROM worker
WHERE worker_id = (SELECT MIN(worker_id) FROM worker);
Note: “First” can mean different things – by insertion order, by sorting, or by primary key.
Q44. Write an SQL query to fetch the last five records from a table.
(SELECT * FROM worker ORDER BY worker_id DESC LIMIT 5)
ORDER BY worker_id;
Technique: Inner query gets last 5 in reverse order, outer query corrects the order.
Complex Analytical Queries
Q45. Write an SQL query to print the name of employees having the highest salary in each department.
SELECT w.department, w.first_name, w.salary
FROM (SELECT MAX(salary) as maxsal, department FROM worker GROUP BY department) temp
INNER JOIN worker w ON temp.department = w.department AND temp.maxsal = w.salary;
Modern Approach: Use RANK() or ROW_NUMBER() window functions for cleaner code.
Q46. Write an SQL query to fetch three max salaries from a table using co-related subquery.
SELECT DISTINCT salary FROM worker w1
WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM worker w2 WHERE w1.salary <= w2.salary)
ORDER BY w1.salary DESC;
-- Simpler alternative:
SELECT DISTINCT salary FROM worker ORDER BY salary DESC LIMIT 3;
Performance: Correlated subqueries can be slow on large datasets.
Q47. Write an SQL query to fetch three min salaries from a table using co-related subquery.
SELECT DISTINCT salary FROM worker w1
WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM worker w2 WHERE w1.salary >= w2.salary)
ORDER BY w1.salary DESC;
Logic: Similar to Q46 but changes the comparison operator to find minimum salaries.
Q48. Write an SQL query to fetch nth max salaries from a table.
SELECT DISTINCT salary FROM worker w1
WHERE n >= (SELECT COUNT(DISTINCT salary) FROM worker w2 WHERE w1.salary <= w2.salary)
ORDER BY w1.salary DESC;
Usage: Replace ‘n’ with actual number. This is a template for finding any rank.
Financial and Summary Queries
Q49. Write an SQL query to fetch departments along with the total salaries paid for each of them.
SELECT department, SUM(salary) as depSal FROM worker
GROUP BY department
ORDER BY depSal DESC;
Business Value: Useful for budget analysis and cost center reporting.
Q50. Write an SQL query to fetch the names of workers who earn the highest salary.
SELECT first_name, salary FROM worker
WHERE salary = (SELECT MAX(salary) FROM worker);
Multiple Results: This returns all workers with the maximum salary (handles ties correctly).
SQL Theoretical Interview Questions
Common Mistakes to Avoid
- NULL Handling: Remember that NULL values behave differently in comparisons
- Data Types: Be aware of implicit type conversions
- Aggregation: Understand GROUP BY requirements when using aggregate functions
Best Practices
- Consistent Formatting: Use consistent indentation and capitalization
- Meaningful Aliases: Use descriptive aliases for tables and columns
- Comments: Add comments for complex queries
- Error Handling: Consider edge cases and empty result sets
Join Telegram group for more resources & discussions!
🧰 Useful Resources for Your Placement Prep
Lets Code
Contributing Writer