Top 100 SQL Interview Questions and Answers for Freshers
Top 100 SQL Interview Questions and Answers for Fresh Data Analytics Jobs 2025
Landing your first data analytics job is exciting, but SQL interviews can feel daunting. As a fresher, mastering SQL is key, as it’s the backbone of data querying and analysis. This blog post features 50 carefully curated SQL interview questions and answers, designed to help you prepare for entry-level data analytics roles. Each question is presented in a visually appealing format, with clear explanations and examples to boost your confidence. Whether you’re facing a technical round or a practical test, these questions cover beginner to intermediate SQL concepts, from basic queries to joins, aggregations, and optimization.
Stay tuned for Part 2 with the next 50 questions, or press “Yes” to continue reading!
1. What is SQL, and why is it important in data analytics?
Answer: SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It allows analysts to query, insert, update, and delete data efficiently. In data analytics, SQL is critical for extracting insights from large datasets, generating reports, and supporting data-driven decisions.
2. What is the difference between SELECT and SELECT DISTINCT?
Answer: SELECT retrieves all rows from a table, including duplicates. SELECT DISTINCT retrieves only unique rows by eliminating duplicates.
This returns unique department names.
3. How do you retrieve all columns from a table?
Answer: Use the asterisk (*) with SELECT:
This fetches all columns and rows from the specified table.
4. What is the purpose of the WHERE clause?
Answer: The WHERE clause filters rows based on a condition.
This returns names of employees with a salary greater than 50,000.
5. What are the main types of SQL joins?
Answer: The main types are:
- INNER JOIN: Returns rows with matching values in both tables.
- LEFT (OUTER) JOIN: Returns all rows from the left table, with matching rows from the right table (NULL if no match).
- RIGHT (OUTER) JOIN: Returns all rows from the right table, with matching rows from the left table.
- FULL (OUTER) JOIN: Returns all rows when there’s a match in either table.
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
6. What is a primary key?
Answer: A primary key is a unique identifier for each row in a table. It ensures that no two rows have the same value and cannot be NULL.
emp_id INT PRIMARY KEY,
name VARCHAR(50)
);
7. What is a foreign key?
Answer: A foreign key is a column in one table that links to the primary key of another table, enforcing referential integrity.
order_id INT PRIMARY KEY,
emp_id INT,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
8. How do you sort query results?
Answer: Use the ORDER BY clause to sort results. By default, it sorts in ascending order (ASC); use DESC for descending.
9. What does the GROUP BY clause do?
Answer: The GROUP BY clause groups rows with the same values into summary rows, often used with aggregate functions like COUNT, SUM, etc.
FROM employees
GROUP BY department;
10. What is the HAVING clause used for?
Answer: The HAVING clause filters grouped results, similar to WHERE but applied after GROUP BY.
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
11. What is the difference between WHERE and HAVING?
Answer: WHERE filters individual rows before grouping, while HAVING filters groups after GROUP BY is applied.
12. How do you limit the number of rows returned?
Answer: Use LIMIT (or TOP in SQL Server) to restrict the number of rows.
13. What is an aggregate function? Give examples.
Answer: Aggregate functions perform calculations on multiple rows and return a single value. Examples: COUNT, SUM, AVG, MIN, MAX.
14. How do you handle NULL values in SQL?
Answer: NULL represents missing or unknown data. Use IS NULL or IS NOT NULL to check for NULL values.
15. What is the COALESCE function?
Answer: COALESCE returns the first non-NULL value from a list of arguments.
16. What is the difference between DELETE and TRUNCATE?
Answer: DELETE removes specific rows based on a condition and can be rolled back. TRUNCATE removes all rows from a table without logging individual deletions, making it faster but less reversible.
TRUNCATE TABLE employees;
17. How do you update data in a table?
Answer: Use the UPDATE statement with SET to modify column values.
18. What is a subquery?
Answer: A subquery is a query nested inside another query, used to return data that will be used by the outer query.
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = ‘Sales’);
19 paying customers by city?
Answer: Use GROUP BY with COUNT to aggregate customers by city.
FROM customers
WHERE is_paying = 1
GROUP BY city;
20. What is the difference between a correlated and non-correlated subquery?
Answer: A non-correlated subquery runs independently and returns a result used by the outer query. A correlated subquery depends on the outer query and runs for each row of the outer query.
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
21. How do you create a table in SQL?
Answer: Use the CREATE TABLE statement to define the table structure.
customer_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
22. What is the CASE statement used for?
Answer: The CASE statement adds conditional logic to queries, allowing you to return values based on conditions.
CASE
WHEN salary > 50000 THEN ‘High’
ELSE ‘Low’
END as salary_category
FROM employees;
23. How do you find duplicate rows in a table?
Answer: Use GROUP BY and HAVING to identify rows with duplicate values.
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
24. What is the UNION operator?
Answer: The UNION operator combines the results of two or more SELECT queries, removing duplicates. Columns must have compatible data types.
UNION
SELECT name FROM contractors;
25. What is the difference between UNION and UNION ALL?
Answer: UNION removes duplicates from the combined result set, while UNION ALL includes all rows, making it faster.
26. How do you add a new column to an existing table?
Answer: Use the ALTER TABLE statement with ADD.
27. What is the IN operator?
Answer: The IN operator checks if a value matches any value in a list or subquery.
WHERE dept_id IN (1, 2, 3);
28. What is the BETWEEN operator?
Answer: The BETWEEN operator selects values within a range, inclusive of the endpoints.
WHERE salary BETWEEN 30000 AND 50000;
29. How do you rename a table?
Answer: Use the ALTER TABLE statement with RENAME TO (syntax may vary by database).
30. What is a view in SQL?
Answer: A view is a virtual table based on the result of a SELECT query. It simplifies complex queries and enhances security.
SELECT name, salary FROM employees WHERE salary > 50000;
31. How do you drop a table?
Answer: Use the DROP TABLE statement to delete a table and its data permanently.
32. What is the LIKE operator?
Answer: The LIKE operator is used for pattern matching in strings, with wildcards % (any characters) and _ (single character).
33. How do you concatenate strings in SQL?
Answer: Use the || operator or CONCAT function (varies by database).
34. What is the purpose of the INDEX in SQL?
Answer: An index improves query performance by allowing faster data retrieval. It’s created on columns frequently used in WHERE or JOIN clauses.
35. How do you find the second highest salary in a table?
Answer: Use a subquery with MAX or LIMIT with OFFSET.
WHERE salary < (SELECT MAX(salary) FROM employees);
36. What is normalization in databases?
Answer: Normalization organizes a database to reduce redundancy and improve data integrity by dividing tables into smaller, related units following rules (e.g., 1NF, 2NF, 3NF).
37. What is denormalization?
Answer: Denormalization combines tables to improve query performance by reducing joins, often used in data warehouses at the cost of some redundancy.
38. How do you calculate the total sales by month?
Answer: Use GROUP BY with a date function like MONTH or DATE_TRUNC.
FROM orders
GROUP BY MONTH(order_date);
39. What is a self-join?
Answer: A self-join joins a table with itself, useful for hierarchical data or comparing rows within the same table.
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;
40. How do you handle case-sensitive string comparisons?
Answer: Use LOWER or UPPER to convert strings to the same case before comparing.
WHERE LOWER(name) = ‘john’;
41. What is the COUNT function’s behavior with NULLs?
Answer: COUNT(column_name) counts non-NULL values in the specified column, while COUNT(*) counts all rows, including those with NULLs.
42. How do you pivot data in SQL?
Answer: Pivoting transforms rows into columns, often using CASE or database-specific PIVOT operators.
SUM(CASE WHEN year = 2023 THEN sales ELSE 0 END) as sales_2023,
SUM(CASE WHEN year = 2024 THEN sales ELSE 0 END) as sales_2024
FROM sales_data
GROUP BY department;
43. What is the difference between INNER JOIN and LEFT JOIN?
Answer: INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table, with NULLs for non-matching rows from the right table.
44. How do you calculate a running total in SQL?
Answer: Use a window function like SUM() OVER.
SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;
45. What is a window function?
Answer: Window functions perform calculations across a set of rows related to the current row, without grouping the result set. Examples: ROW_NUMBER, RANK, SUM() OVER.
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as rank
FROM employees;
46. How do you remove duplicate rows from a table?
Answer: Use a CTE or subquery with ROW_NUMBER to identify and delete duplicates.
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) as rn
FROM customers
)
DELETE FROM duplicates WHERE rn > 1;
47. What is the EXISTS operator?
Answer: The EXISTS operator checks if a subquery returns any rows. It’s often used in correlated subqueries.
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id
);
48. How do you find the top 5 employees by salary?
Answer: Use ORDER BY with LIMIT.
ORDER BY salary DESC
LIMIT 5;
49. What is the difference between RANK and DENSE_RANK?
Answer: RANK assigns the same rank to tied values but skips subsequent ranks. DENSE_RANK assigns the same rank to ties without skipping ranks.
RANK() OVER (ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
50. How do you calculate the percentage of total sales by department?
Answer: Use a window function to calculate total sales and then compute the percentage.
(SUM(sales) / SUM(SUM(sales)) OVER ()) * 100 as sales_percentage
FROM sales_data
GROUP BY department;
Want the next 50 questions? Press “Yes” to continue!
51. What is a database?
Answer: A database is an organized collection of data, typically stored and accessed electronically from a computer system. It allows efficient storage, retrieval, and management of data using tables.
52. What is a table in SQL?
Answer: A table is a collection of data organized in rows and columns, where each column represents a data attribute, and each row represents a record. For example, an “Employees” table might have columns like ID, Name, and Salary.
53. What is a column in a database?
Answer: A column is a vertical structure in a table that holds data of a specific type (e.g., text, number, date) for each record. For instance, a “Salary” column stores salary values for all employees.
54. What is a row in a database?
Answer: A row, also called a record or tuple, is a horizontal entry in a table that contains data for one instance of the table’s entity, such as one employee’s details.
55. What does a query mean in SQL?
Answer: A query is a request for data or information from a database, written in SQL. It can retrieve, insert, update, or delete data based on specified conditions.
56. What is the purpose of a database management system (DBMS)?
Answer: A DBMS is software that manages databases, allowing users to create, read, update, and delete data. It ensures data security, integrity, and efficient access.
57. What is a relational database?
Answer: A relational database organizes data into tables with relationships defined by keys (e.g., primary and foreign keys). It uses SQL to manage and query data.
58. What is a primary key?
Answer: A primary key is a unique identifier for each row in a table. It ensures no duplicate or NULL values and helps link tables.
59. What is a foreign key?
Answer: A foreign key is a column in one table that refers to the primary key in another table, establishing a relationship between the tables.
60. What is meant by data integrity?
Answer: Data integrity refers to the accuracy, consistency, and reliability of data in a database, maintained through constraints like primary keys and foreign keys.
61. What is a constraint in SQL?
Answer: A constraint is a rule applied to a table’s columns to enforce data integrity, such as NOT NULL, UNIQUE, PRIMARY KEY, or FOREIGN KEY.
62. What does NULL mean in SQL?
Answer: NULL represents a missing or unknown value in a database. It is not the same as zero or an empty string.
63. What is a data type in SQL?
Answer: A data type defines the kind of data a column can store, such as INT (integer), VARCHAR (text), DATE, or BOOLEAN.
64. What is the role of the SELECT statement?
Answer: The SELECT statement retrieves data from one or more tables in a database, allowing users to specify columns and conditions.
65. What is the purpose of the INSERT statement?
Answer: The INSERT statement adds new rows of data to a table, specifying the values for each column.
66. What does the UPDATE statement do?
Answer: The UPDATE statement modifies existing data in a table, typically used with a WHERE clause to target specific rows.
67. What is the DELETE statement used for?
Answer: The DELETE statement removes one or more rows from a table based on a specified condition.
68. What is a join in SQL?
Answer: A join combines rows from two or more tables based on a related column, such as a primary key and foreign key.
69. What is an INNER JOIN?
Answer: An INNER JOIN returns only the rows where there is a match in both tables being joined.
70. What is a LEFT JOIN?
Answer: A LEFT JOIN returns all rows from the left table and the matching rows from the right table. Non-matching rows from the right table return NULL.
71. What is a RIGHT JOIN?
Answer: A RIGHT JOIN returns all rows from the right table and the matching rows from the left table. Non-matching rows from the left table return NULL.
72. What is a FULL JOIN?
Answer: A FULL JOIN returns all rows from both tables, with NULLs in places where there is no match.
73. What is the WHERE clause used for?
Answer: The WHERE clause filters rows in a query based on a specified condition, such as selecting employees with a certain salary.
74. What does the ORDER BY clause do?
Answer: The ORDER BY clause sorts the result of a query in ascending (ASC) or descending (DESC) order based on one or more columns.
75. What is the GROUP BY clause?
Answer: The GROUP BY clause groups rows with the same values in specified columns into summary rows, often used with aggregate functions like COUNT or SUM.
76. What is an aggregate function?
Answer: An aggregate function performs a calculation on a set of values and returns a single value, such as COUNT, SUM, AVG, MIN, or MAX.
77. What is the HAVING clause?
Answer: The HAVING clause filters grouped data after a GROUP BY, used with aggregate functions to apply conditions to groups.
78. What is a subquery?
Answer: A subquery is a query nested inside another query, used to return data that the outer query processes.
79. What is the purpose of the CREATE TABLE statement?
Answer: The CREATE TABLE statement defines a new table in a database, specifying its columns, data types, and constraints.
80. What does the DROP TABLE statement do?
Answer: The DROP TABLE statement deletes a table and all its data from the database permanently.
81. What is the ALTER TABLE statement used for?
Answer: The ALTER TABLE statement modifies an existing table, such as adding, removing, or changing columns or constraints.
82. What is a view in SQL?
Answer: A view is a virtual table created from a SELECT query, used to simplify queries or restrict data access.
83. What is an index in a database?
Answer: An index is a database structure that improves the speed of data retrieval for specific columns, but it may slow down data modification.
84. What is the difference between a clustered and non-clustered index?
Answer: A clustered index determines the physical order of data in a table (only one per table). A non-clustered index is a separate structure pointing to the data, allowing multiple per table.
85. What is normalization?
Answer: Normalization is the process of organizing a database into tables to reduce redundancy and ensure data integrity, following rules like 1NF, 2NF, and 3NF.
86. What is the purpose of the first normal form (1NF)?
Answer: The first normal form (1NF) ensures that a table has no repeating groups, each column contains atomic (single) values, and each row is unique.
87. What is denormalization?
Answer: Denormalization combines tables to improve query performance by reducing joins, often at the cost of some data redundancy.
88. What is a transaction in SQL?
Answer: A transaction is a sequence of SQL operations treated as a single unit, ensuring data consistency (e.g., transferring money between accounts).
89. What is the COMMIT statement?
Answer: The COMMIT statement saves all changes made during a transaction to the database permanently.
90. What is the ROLLBACK statement?
Answer: The ROLLBACK statement undoes all changes made during a transaction, restoring the database to its previous state.
91. What is a schema in a database?
Answer: A schema is a logical container for database objects like tables, views, and indexes, defining their structure and relationships.
92. What is the difference between SQL and MySQL?
Answer: SQL is a standard query language for managing databases. MySQL is a specific relational database management system (RDBMS) that uses SQL.
93. What is a data warehouse?
Answer: A data warehouse is a large, centralized database optimized for analytical queries and reporting, often storing historical data from multiple sources.
94. What is the role of a database administrator (DBA)?
Answer: A DBA manages databases, ensuring their performance, security, backups, and availability, and assists with schema design and optimization.
95. What is a default value in SQL?
Answer: A default value is a predefined value assigned to a column if no value is provided during data insertion.
96. What is the purpose of the TRUNCATE statement?
Answer: The TRUNCATE statement removes all rows from a table quickly, without logging individual row deletions, unlike DELETE.
97. What is a unique constraint?
Answer: A unique constraint ensures that all values in a column or set of columns are distinct, preventing duplicates.
98. What is a check constraint?
Answer: A check constraint enforces a condition on a column’s values, such as ensuring a salary is positive.
99. What is the difference between a database and a spreadsheet?
Answer: A database is designed for large-scale data management with relationships, queries, and security. A spreadsheet is a simpler tool for small datasets, lacking advanced querying or relational features.
100. Why is SQL important for data analytics?
Answer: SQL is essential for data analytics because it allows analysts to efficiently query, filter, and summarize large datasets, enabling data-driven insights and reporting.
Ready to ace your SQL interview? Share your thoughts in the comments or check out Part 1 for more questions!