Land Your Dream Job: Master Top 50 SQL Interview Questions with Expert Answers
1. What is SQL?
Answer: SQL (Structured Query Language) is a standard language used to communicate with and manipulate relational databases. It allows you to perform various operations like:
- Data Retrieval: Selecting, filtering, and sorting data.
- Data Manipulation: Inserting, updating, and deleting data.
- Data Definition: Creating, altering, and dropping tables and databases.
- Data Control: Managing user access and permissions.
2. What are the different types of SQL commands?
Answer:
- DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
- DCL (Data Control Language): GRANT, REVOKE
3. Explain the difference between WHERE and HAVING clauses.
Answer:
- WHERE: Used to filter rows before grouping.
- HAVING: Used to filter rows after grouping.
4. What is a JOIN clause?
Answer: JOIN is used to combine data from two or more tables based on a related column. Common types include:
- INNER JOIN: Returns rows where there is a match in both tables.
- LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
- RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
- FULL JOIN: Returns all rows from both tables.
5. What is a subquery?
Answer: A subquery is a nested SELECT statement within another SQL statement. It can be used in various places like WHERE, FROM, and HAVING clauses.
6. What are aggregate functions in SQL?
Answer: Functions that perform calculations on a set of values and return a single result. Examples include:
- COUNT: Counts the number of rows.
- SUM: Calculates the sum of values.
- AVG: Calculates the average of values.
- MAX: Finds the maximum value.
- MIN: Finds the minimum value.
7. What is a primary key?
Answer: A unique identifier for each row in a table. It cannot contain NULL values.
8. What is a foreign key?
Answer: A column in one table that references the primary key of another table. It establishes a link between the two tables.
9. What is a view in SQL?
Answer: A virtual table that is created from the result of an SQL query. It provides a simplified view of the underlying data.
10. What is an index in SQL?
Answer: An index is a data structure that improves the speed of data retrieval operations. It creates a sorted list of values for a specific column, allowing for faster searches.
11. What is normalization in SQL?
Answer: The process of organizing data in a database to minimize redundancy and improve data integrity. It involves breaking down large tables into smaller, more manageable tables.
12. What is denormalization in SQL?
Answer: The process of adding redundant data to a database to improve performance. It can be useful in situations where read operations are more frequent than write operations.
13. Explain the difference between DELETE and TRUNCATE commands.
Answer:
- DELETE: Removes specific rows from a table.
- TRUNCATE: Removes all rows from a table quickly.
14. What is a transaction in SQL?
Answer: A logical unit of work that consists of one or more SQL statements. Transactions are atomic, meaning they either succeed completely or fail completely.
15. What are the ACID properties of transactions?
Answer:
- Atomicity: All operations within a transaction are treated as a single unit.
- Consistency: The database must remain in a consistent state after a transaction.
- Isolation: Concurrent transactions do not interfere with each other.
- Durability: Once a transaction is committed, its changes are permanent.
16. What is a stored procedure?
Answer: A precompiled set of SQL statements that can be executed as a single unit. They improve performance and code reusability.
17. What are triggers in SQL?
Answer: Special types of stored procedures that are automatically executed in response to certain events, such as inserting, updating, or deleting rows in a table.
18. What is a cursor in SQL?
Answer: A pointer that moves through a set of rows returned by a SELECT statement. It allows you to process the results row by row.
19. How do you write a SQL query to find the second highest salary?
Answer:
SQL
SELECT Salary
FROM employees
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;
20. How do you write a SQL query to find the employees who earn more than their managers?
Answer:
SQL
SELECT e.EmployeeName
FROM employees e
JOIN employees m ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;
21. How do you write a SQL query to find the number of orders placed in each month?
Answer:
SQL
SELECT MONTH(OrderDate) as Month, COUNT(*) as NumOrders
FROM orders
GROUP BY Month;
22. How do you write a SQL query to find the customers who have never placed an order?
Answer:
SQL
SELECT c.CustomerID, c.CustomerName
FROM customers c
LEFT JOIN orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;
23. What is a self-join?
Answer: A join between a table and itself. It’s useful for comparing rows within the same table.
24. What is a cross join?
Answer: Returns the Cartesian product of two tables, resulting in a table with all possible combinations of rows from both tables.
25. What is the difference between UNION and UNION ALL?
Answer:
- UNION: Combines the results of two SELECT statements, removing duplicate rows.
- UNION ALL: Combines the results of two SELECT statements, including duplicate rows.
26. What is the use of the LIKE operator?
Answer: Used for pattern matching in strings. It’s often used with wildcards like ‘%’ (matches any number of characters) and ‘_’ (matches a single character).
27. What is the use of the IN operator?
Answer: Used to check if a value exists within a list of values.
28. What is the use of the BETWEEN operator?
Answer: Used to select values within a specified range.
29. What are the different data types in SQL?
Answer:
- Numeric: INT, DECIMAL, FLOAT, DOUBLE
- Character: CHAR, VARCHAR, TEXT
- Date/Time: DATE, TIME, TIMESTAMP
30. What is the use of the DISTINCT keyword?
Answer: Used to remove duplicate rows from the result set.
31. How do you comment out a single line in SQL?
Answer: Use two dashes:
32. How do you comment out multiple lines in SQL?
Answer: Use /* to start the comment and */ to end it.
33. What is a database?
Answer: An organized collection of structured information.
34. What is a table?
Answer: A collection of related data organized in rows and columns.
35. What is a column?
Answer: A vertical set of data elements in a table, representing a particular attribute or characteristic.
36. What is a row?
Answer: A horizontal set of data elements in a table, representing a single record or entity.
37. What is a database schema?
Answer: The logical structure of a database, including tables, columns, data types, and relationships between tables.
38. What is a database administrator (DBA)?
Answer: A professional responsible for managing and maintaining a database system.
39. What is a relational database?
Answer: A database that stores data in tables that are related to each other through common fields.
40. What is a non-relational database?
Answer: A database that does not rely on the traditional table-based relational model. Examples include NoSQL databases like MongoDB and Cassandra.
41. What is the difference between a database and a data warehouse?
Answer:
Database: Stores operational data for day-to-day business activities.
Data warehouse: Stores historical data for business analysis and reporting.
43. What is data mining?
Answer: The process of extracting meaningful patterns and insights from large datasets.
44. What is data warehousing?
Answer: The process of collecting and storing data from various sources for analysis and reporting.
45. What is OLTP (Online Transaction Processing)?
Answer: Systems designed for handling large numbers of short online transactions.
46. What is OLAP (Online Analytical Processing)?
Answer: Systems designed for complex analysis of large volumes of data.
47. What is a database cluster?
Answer: A group of interconnected database servers that work together to provide high availability, scalability, and fault tolerance.
48. What is database replication?
Answer: The process of creating and maintaining copies of data across multiple servers.
49. What is database partitioning?
Answer: The process of dividing a large table into smaller, more manageable partitions.
50. What are some of the popular database management systems?
Answer:
- Relational: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite
- NoSQL: MongoDB, Cassandra, Redis
Popular Courses
