Preparing for an SQL interview requires a solid understanding of database concepts, SQL queries, and best practices. Here are 30 commonly asked SQL interview questions and their detailed answers to help you excel in your interview.
1. What is SQL?
SQL (Structured Query Language) is a standard language used to communicate with relational databases. It allows users to perform various operations such as querying, updating, inserting, and deleting data. SQL is essential for database management and manipulation.
2. What is the difference between SQL and MySQL?
SQL is a language used for querying and managing databases, while MySQL is a specific database management system (DBMS) that uses SQL as its query language. SQL can be used with various DBMSs like MySQL, SQL Server, Oracle, and PostgreSQL.
3. Explain the different types of joins in SQL.
There are several types of joins in SQL:
- INNER JOIN: Returns records that have matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. Records from the left table with no match in the right table will contain NULL values.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. Records from the right table with no match in the left table will contain NULL values.
- FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table. Records with no match will contain NULL values.
- CROSS JOIN: Returns the Cartesian product of both tables, i.e., all possible combinations of records.
4. What is a primary key?
A primary key is a unique identifier for a record in a database table. It ensures that each record can be uniquely identified and prevents duplicate entries. Each table can have only one primary key, which can consist of one or multiple columns (composite key).
5. What is a foreign key?
A foreign key is a column or a set of columns in one table that uniquely identifies a row in another table. It is used to establish and enforce a link between the data in the two tables. Foreign keys help maintain referential integrity by ensuring that the values in the foreign key column correspond to valid values in the referenced table.
6. What is normalization?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them. The goal is to minimize data duplication and ensure data consistency.
7. Explain the different normal forms.
Normalization involves several normal forms:
- First Normal Form (1NF): Ensures that the table has a primary key and that all columns contain atomic (indivisible) values.
- Second Normal Form (2NF): Ensures that the table is in 1NF and that all non-key columns are fully functionally dependent on the primary key.
- Third Normal Form (3NF): Ensures that the table is in 2NF and that all columns are only dependent on the primary key, eliminating transitive dependencies.
- Boyce-Codd Normal Form (BCNF): A stronger version of 3NF that deals with certain types of anomalies not handled by 3NF.
8. What is an index in SQL?
An index is a database object that improves the speed of data retrieval operations on a table. It works by creating a data structure that allows quick lookups of values in one or more columns. Indexes can be created on primary keys, unique keys, and other columns to enhance query performance.
9. Explain the difference between UNION and UNION ALL.
UNION: Combines the results of two or more SELECT queries and removes duplicate rows from the final result set.
UNION ALL: Combines the results of two or more SELECT queries but includes all rows, including duplicates.
10. What is a subquery?
A subquery is a query nested inside another query. It is used to retrieve data that will be used by the outer query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements. They can be scalar (returning a single value) or table-valued (returning multiple rows and columns).
11. What is a view in SQL?
A view is a virtual table created by a query that selects data from one or more tables. It does not store data itself but provides a way to present data in a specific format. Views can simplify complex queries, provide security by restricting access to certain columns, and present data in a way that is more understandable to users.
12. What is an aggregate function in SQL?
Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:
- COUNT: Returns the number of rows.
- SUM: Returns the sum of values in a numeric column.
- AVG: Returns the average value of a numeric column.
- MIN: Returns the minimum value in a column.
- MAX: Returns the maximum value in a column.
13. Explain the use of GROUP BY clause.
The GROUP BY clause is used to group rows that have the same values in specified columns into aggregate data. It is often used with aggregate functions to produce summary results. For example, you can group sales data by region and calculate the total sales for each region.
14. What is the purpose of the HAVING clause?
The HAVING clause is used to filter the results of a GROUP BY operation based on aggregate function results. It is similar to the WHERE clause but is applied after the grouping and aggregation have been performed. For example, you can use HAVING to filter groups that have a total sales amount greater than a certain value.
15. What is a stored procedure?
A stored procedure is a precompiled collection of SQL statements and optional control-flow statements stored under a name and processed as a unit. Stored procedures can accept parameters, execute complex logic, and return results. They help improve performance, provide reusability, and enhance security by encapsulating the SQL code.
16. What is a trigger in SQL?
A trigger is a special type of stored procedure that automatically executes in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE operations. Triggers can be used to enforce business rules, validate data, and maintain audit trails.
17. Explain the concept of transaction in SQL.
A transaction is a sequence of one or more SQL operations that are executed as a single unit. Transactions ensure that either all operations are completed successfully (commit) or none of them are applied (rollback) in case of an error. This guarantees data integrity and consistency. Key properties of transactions are known as ACID properties (Atomicity, Consistency, Isolation, Durability).
18. What is the difference between TRUNCATE and DELETE?
DELETE: Removes rows from a table based on a condition specified in the WHERE clause. It can be rolled back and activates triggers.
TRUNCATE: Removes all rows from a table without logging individual row deletions. It is faster than DELETE but cannot be rolled back if used outside a transaction and does not activate triggers.
19. What is an SQL injection attack?
SQL injection is a security vulnerability that occurs when an attacker is able to manipulate a SQL query by injecting malicious SQL code through user input fields. This can lead to unauthorized access, data leakage, and data manipulation. To prevent SQL injection, use parameterized queries and stored procedures, and validate user inputs.
20. What is a schema in SQL?
A schema is a logical container that holds database objects such as tables, views, indexes, and procedures. It helps in organizing and managing database objects and provides a way to group related objects together. Schemas also provide a level of security and access control by defining permissions at the schema level.
21. What is the difference between CHAR and VARCHAR data types?
CHAR: A fixed-length character data type. It pads the data with spaces to the specified length.
VARCHAR: A variable-length character data type. It stores only the actual length of the data plus a few additional bytes.
22. What is an index in SQL and why is it used?
An index is a database object that improves the speed of data retrieval operations. It provides a faster way to search and access data by creating a data structure (such as a B-tree) that allows quick lookups of values in a table. Indexes are used to optimize query performance but can impact insert, update, and delete operations due to additional maintenance overhead.
23. What is a composite key?
A composite key is a primary key that consists of two or more columns. It is used when a single column is not sufficient to uniquely identify a record in a table. Each column in the composite key contributes to the uniqueness of the record, and together they form the primary key.
24. What is the use of the DISTINCT keyword?
The DISTINCT keyword is used in a SELECT query to remove duplicate rows from the result set. It ensures that only unique rows are returned. For example, if you want to retrieve a list of unique customer IDs from an orders table, you would use the DISTINCT keyword in your query.
25. Explain the use of the LIKE operator.
The LIKE operator is used in SQL to search for a specified pattern in a column. It is often used with wildcard characters:
- %: Represents zero or more characters (e.g.,
LIKE 'A%'matches any string starting with 'A'). - _: Represents a single character (e.g.,
LIKE 'A_'matches any string with 'A' followed by one more character).
26. What is a self-join?
A self-join is a regular join but the table is joined with itself. It is used to compare rows within the same table. For example, if you have an employees table with a manager column that references employee IDs, a self-join can be used to list employees and their managers.
27. What are SQL constraints?
SQL constraints are rules applied to table columns to enforce data integrity and accuracy. Common constraints include:
- PRIMARY KEY: Ensures unique values and identifies each record.
- FOREIGN KEY: Maintains referential integrity between tables.
- UNIQUE: Ensures all values in a column are unique.
- NOT NULL: Ensures a column cannot have NULL values.
- CHECK: Ensures values in a column meet a specific condition.
28. What is a common table expression (CTE)?
A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword and provides a way to simplify complex queries, especially those involving recursive operations.
29. What is a cursor in SQL?
A cursor is a database object used to retrieve and manipulate a set of rows returned by a query. It allows row-by-row processing of the result set. Cursors are useful for performing operations on individual rows but can be less efficient than set-based operations for large datasets.
30. What is the purpose of the ORDER BY clause?
The ORDER BY clause is used to sort the result set of a query by one or more columns. It can sort data in ascending (default) or descending order. For example, you can use ORDER BY to sort employee records by their hire date or salary.
0 Comments