Job interview questions and answers
Get ready to tackle the most commonly asked SQL interview questions. Whether you are a beginner or an expert, this page will help you prepare for your SQL interviews. Explore our comprehensive collection of questions and their detailed answers. Good luck!
1. What is SQL?
SQL stands for Structured Query Language, which is a programming language designed for managing data in a relational database management system (RDBMS).
2. What are the different types of SQL statements?
SQL statements can be classified into four main types:
– Data Definition Language (DDL): Used for creating, altering, and deleting database objects.
– Data Manipulation Language (DML): Used for retrieving, inserting, updating, and deleting data in a database.
– Data Control Language (DCL): Used for managing access and permissions on database objects.
– Data Query Language (DQL): Used for querying and retrieving data from a database.
3. What is the difference between CHAR and VARCHAR data types?
The CHAR data type is used to store fixed-length character strings, while the VARCHAR data type is used to store variable-length character strings. CHAR always uses the specified length, padding with spaces if necessary, while VARCHAR only uses the necessary amount of storage.
4. Explain the difference between PRIMARY KEY and UNIQUE constraint.
A PRIMARY KEY is a column or combination of columns that uniquely identifies each row in a table. It enforces uniqueness and does not allow NULL values. On the other hand, a UNIQUE constraint also enforces uniqueness but allows NULL values in the column(s).
5. What is the difference between DELETE and TRUNCATE commands?
The DELETE command is used to delete specific rows from a table, allowing a WHERE clause condition to be specified. It also generates transaction logs and can be rolled back. The TRUNCATE command is used to remove all rows from a table, resetting the table’s identity and generating no transaction logs. It cannot be rolled back.
6. What is a JOIN in SQL?
A JOIN in SQL combines rows from two or more tables based on a related column between them. It allows data from different tables to be retrieved together in a single result set.
7. What are the different types of JOINs?
The different types of JOINs are:
– INNER JOIN: Returns only the matching rows from 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 OUTER JOIN: Returns all rows from both tables, including non-matching ones.
8. What is an INDEX in SQL?
An INDEX is a database object that improves the retrieval speed of data from a table. It is created on one or more columns of a table and provides a faster way to access specific data.
9. What is a foreign key in SQL?
A foreign key is a column or group of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, enforcing referential integrity.
10. Explain the difference between UNION and UNION ALL operators.
The UNION operator combines the result sets of two or more SELECT statements, removing duplicate rows. It only keeps distinct values. The UNION ALL operator also combines result sets but does not remove duplicate rows. It keeps all rows from each SELECT statement.
11. What is a subquery in SQL?
A subquery is a query embedded within another query. It can be used to retrieve data that will be used for filtering, joining, or performing calculations in the main query.
12. What is the difference between WHERE and HAVING clauses?
The WHERE clause is used to filter rows based on conditions while retrieving data from a single table. It operates before the result set is generated. The HAVING clause is used to filter rows based on conditions after the result set has been generated, usually when retrieving data from multiple tables using GROUP BY.
13. Explain the ACID properties in database transactions.
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability and integrity of database transactions.
– Atomicity: A transaction is treated as a single unit. It is either completed entirely or rolled back if any part of it fails.
– Consistency: A transaction brings the database from one valid state to another, satisfying constraints.
– Isolation: Each transaction is isolated from other concurrent transactions, so the intermediate states are not visible.
– Durability: Once a transaction is committed, its changes are permanent and survive any subsequent system failures.
14. What is a stored procedure?
A stored procedure is a prepared SQL code that can be stored and reused on a database server. It allows complex operations to be performed efficiently and reduces network traffic by executing multiple SQL statements at once.
15. What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order of data in a table. A table can have only one clustered index. On the other hand, a non-clustered index does not determine the physical order of data but provides a quick way of locating rows based on the indexed columns.
16. What is the role of the GROUP BY clause in SQL?
The GROUP BY clause is used to group rows based on one or more columns for aggregation purposes. It is usually used with aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on a group of rows rather than on individual rows.
17. How can you prevent SQL injection attacks?
SQL injection attacks can be prevented by using prepared statements or parameterized queries, which separate the SQL code from the user input. Additionally, input validation and sanitization techniques should be implemented to ensure only valid data is accepted by the database.
18. What is the difference between a view and a table?
A table is a stored collection of data, while a view is a virtual table derived from the result of a query. The data in a view is not physically stored but is dynamically generated based on the underlying tables.
19. Explain the difference between a primary key and a unique key.
A primary key is used to uniquely identify each row in a table and cannot contain NULL values. It enforces entity integrity. On the other hand, a unique key ensures the uniqueness of values in a column but allows NULL values.
20. What is the purpose of the COMMIT and ROLLBACK commands?
The COMMIT command is used to permanently save any changes made during a transaction to the database. It marks the end of a transaction. The ROLLBACK command is used to undo all changes made during a transaction, restoring the database to its previous state. It is used to handle errors or unwanted changes.
1. What is SQL?
SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational database systems.
2. What are the different types of SQL statements?
SQL statements are categorized into three types:
– DDL (Data Definition Language): Used to define the structure of the database.
– DML (Data Manipulation Language): Used to manipulate and query data within the database.
– DCL (Data Control Language): Used to control access to the database.
3. What is a primary key in a table?
A primary key is a unique identifier for each record in a table. It ensures that each record is uniquely identifiable and can be used to establish relationships between tables.
4. What is a foreign key?
A foreign key is a field in a table that refers to the primary key of another table. It establishes a relationship between the two tables and ensures data integrity by enforcing referential integrity constraints.
5. What is the difference between UNION and UNION ALL?
UNION combines the result sets of two or more SELECT statements, removing duplicate rows. UNION ALL, however, includes all the rows from the combined result sets, including duplicates.
6. What is the difference between VARCHAR and CHAR data types?
VARCHAR is a variable-length character data type that stores non-fixed length strings. CHAR is a fixed-length character data type that stores strings of a specific length. The storage space for CHAR is always allocated, while VARCHAR allocates space only for the actual data.
7. What is a view?
A view is a virtual table created from one or more tables. It does not store data physically but retrieves data dynamically from its underlying tables based on the query used to create it. It can be used for security, abstraction, and simplification of complex queries.
8. What is the difference between DELETE and TRUNCATE?
DELETE is a DML statement that removes specific rows from a table, based on the condition specified. TRUNCATE, on the other hand, is a DDL statement that removes all the rows from a table, resetting the table’s identity counter.
9. What is a self-join?
A self-join is a SQL join that occurs when a table is joined with itself. It is used to relate rows within the same table based on a related column or condition.
10. What is normalization in a database?
Normalization is the process of organizing data in a database to reduce data redundancy and dependency. It involves dividing larger tables into smaller ones and establishing relationships between them using keys. Normalization ensures data integrity and makes the database more efficient.
11. What is an index in a database?
An index is a database structure used to improve the speed of data retrieval operations. It stores a copy of selected columns from a table, sorted in a particular order, along with a pointer to the original row. Indexes are used to quickly locate data without scanning the entire table.
12. What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order of data in a table. Each table can have only one clustered index. A non-clustered index is a separate structure that contains a copy of the selected columns from the table, along with a pointer to the actual row.
13. What is a stored procedure?
A stored procedure is a prepared SQL code that is saved in the database and can be executed later. It is used to group and encapsulate commonly used SQL statements. Stored procedures improve performance, security, and code reusability.
14. Explain the ACID properties of a database transaction.
ACID stands for Atomicity, Consistency, Isolation, and Durability. They represent the properties that ensure reliable processing of database transactions. Atomicity ensures that a transaction is treated as a single unit and either fully completes or is rolled back. Consistency ensures that the database remains in a consistent state before and after a transaction. Isolation prevents interference between concurrent transactions. Durability ensures that once a transaction is committed, its changes are permanent and survive system failures.
15. What is a subquery?
A subquery is a query embedded within another query. It is used to retrieve data from one table that depends on the result of another query. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.
16. What is the difference between a left join and an inner join?
An inner join returns only the matching rows from both tables involved in the join, based on the join condition. A left join, also known as a left outer join, returns all the rows from the left table and matching rows from the right table. If there is no match, NULL values are returned for the right table columns.
17. What is the purpose of the GROUP BY clause?
The GROUP BY clause is used to group rows based on one or more columns and perform calculations or aggregations on the grouped data using aggregate functions like SUM, AVG, COUNT, etc.
18. What is the difference between a candidate key and a primary key?
A candidate key is a column or a set of columns that can uniquely identify a record in a table. A primary key is a candidate key that is chosen as the main identifier for a table. A table can have multiple candidate keys, but only one primary key.
19. How do you perform a backup and restore in SQL?
To perform a backup in SQL, you can use the BACKUP statement, specifying the database and destination for the backup file. To restore a backup, you can use the RESTORE statement, providing the backup file and the target database.
20. What are views in SQL?
Views are virtual tables created from one or more underlying tables or views. They are used to simplify complex queries, provide security by restricting access to sensitive data, and provide a consistent and controlled view of the data to users.
1. What is the difference between UNION and UNION ALL?
The UNION operator is used to combine the result sets of two or more SELECT statements and removes duplicate rows. UNION ALL, on the other hand, combines the result sets without removing any duplicates.
2. What is a self-join?
A self-join is a query in which a table is joined with itself. It is used to combine rows with related data from the same table.
3. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
– INNER JOIN returns the matched rows between two tables.
– LEFT JOIN returns all the rows from the left table and the matched rows from the right table.
– RIGHT JOIN returns all the rows from the right table and the matched rows from the left table.
– FULL JOIN returns all the rows from both tables, combining the result of LEFT JOIN and RIGHT JOIN.
4. What is a correlated subquery?
A correlated subquery is a subquery that depends on the outer query. It executes once for each row returned by the outer query.
5. What are common table expressions (CTE)?
Common table expressions (CTE) are temporary named result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They are defined using the WITH clause.
6. What is a recursive CTE?
A recursive CTE is a CTE that refers to its own defined subquery. It is used to solve problems that involve hierarchical or self-referencing data.
7. Explain the difference between DELETE, TRUNCATE, and DROP commands.
– DELETE is used to remove specific rows from a table.
– TRUNCATE is used to remove all rows from a table, resulting in an empty table.
– DROP is used to delete an entire table structure along with its data.
8. What is the difference between WHERE and HAVING clauses?
The WHERE clause is used to filter rows before grouping them, while the HAVING clause is used to filter groups after they have been formed.
9. What are the advantages of stored procedures?
– Code reusability: Stored procedures can be called multiple times from different parts of an application.
– Improved performance: Precompiled execution plans and reduced network traffic can lead to faster execution.
– Enhanced security: Access to data can be controlled through stored procedures, limiting direct table access.
– Easy maintenance: Changes to business logic can be made in a single location.
10. What is an index and how does it improve query performance?
An index is a database structure that provides quick access to data based on specific columns. It improves query performance by reducing the need for a full table scan, allowing the database engine to locate data faster.
11. What are triggers and how are they used?
Triggers are special types of stored procedures that are automatically executed in response to specific data manipulation operations, such as INSERT, UPDATE, or DELETE. They are used to enforce business rules, perform calculations, or maintain data integrity.
12. What is the difference between clustered and non-clustered indexes?
– Clustered indexes determine the physical order of rows in a table and store data in the order of the index columns. Each table can have only one clustered index.
– Non-clustered indexes create a separate structure that contains a copy of the indexed columns and a pointer to the actual data. Multiple non-clustered indexes can be created on a single table.
13. What is the purpose of the GROUP BY clause?
The GROUP BY clause is used to group rows based on one or more columns. It is used with aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on each group of rows.
14. Explain the concept of normalization in database design.
Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves breaking down tables into smaller tables and defining relationships between them.
15. What is the difference between a primary key and a unique key?
– A primary key is used to uniquely identify each row in a table and cannot contain duplicate values.
– A unique key is used to enforce uniqueness but allows NULL values. It can be used to identify rows, but multiple rows can have NULL values in a unique key column.
16. What are some common SQL functions?
Common SQL functions include AVG, COUNT, MAX, MIN, SUM, CONCAT, SUBSTRING, DATE functions, etc., which perform calculations or manipulate data.
17. What is an SQL injection and how can it be prevented?
SQL injection is a code injection technique used to attack databases by inserting malicious SQL code through user input. It can be prevented by using prepared statements or parameterized queries, input validation, and proper user authentication.
18. What is the purpose of the ORDER BY clause?
The ORDER BY clause is used to sort the result set based on one or more columns in ascending or descending order.
19. What are views in SQL and why are they used?
Views are virtual tables derived from the result of a query. They are used to simplify complex queries, restrict access to certain columns, or provide summarized data to users based on their privileges.
20. What is the difference between OLTP and OLAP databases?
– OLTP (Online Transaction Processing) databases are designed for day-to-day transactional operations and focus on fast data retrieval and updates.
– OLAP (Online Analytical Processing) databases are designed for complex analytical queries and reporting, providing aggregated and summarized data for decision-making.
suresh