Top 20 SQL Interview Questions for Freshers

 Top 20 SQL Interview Questions for Freshers

What is SQL and what are its main components?

Answer: SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational databases. The main components of SQL are Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

What is a primary key in SQL?

Answer: A primary key is a unique identifier for a record in a table. It ensures that each row in a table is uniquely identifiable. It can be a single column or a combination of columns.

What is the difference between CHAR and VARCHAR data types?

Answer: CHAR and VARCHAR are both used to store character data. The main difference is that CHAR has a fixed length, while VARCHAR has a variable length. CHAR will pad the extra spaces at the end of the string to reach the specified length, whereas VARCHAR will only use the necessary storage space.

Explain the different types of joins in SQL.

Answer: The different types of joins in SQL are:

INNER JOIN: Returns the matching records from both tables.

LEFT JOIN: Returns all records from the left table and the matching records from the right table.

RIGHT JOIN: Returns all records from the right table and the matching records from the left table.

FULL JOIN: Returns all records when there is a match in either the left or right table.

CROSS JOIN: Returns the Cartesian product of both tables.

What is the difference between UNION and UNION ALL in SQL?

Answer: Both UNION and UNION ALL are used to combine the result sets of two or more SELECT statements. The difference is that UNION removes duplicate rows from the combined result set, while UNION ALL includes all rows from the SELECT statements, including duplicates.

What is normalization and why is it important in databases?

Answer: Normalization is the process of organizing data in a database to minimize redundancy and dependency. It helps in improving data integrity, reducing data duplication, and ensuring efficient data management.

Explain the concept of indexing in SQL.

Answer: Indexing is a technique used to improve the performance of database operations. It involves creating an index on one or more columns of a table, which allows the database to quickly locate the rows based on the indexed columns.

What is the difference between clustered and non-clustered indexes?

Answer: A clustered index determines the physical order of data in a table, while a non-clustered index is a separate structure that points to the data. A table can have only one clustered index, but multiple non-clustered indexes.

How can you prevent SQL injection attacks?

Answer: To prevent SQL injection attacks, you should use parameterized queries or prepared statements, which ensure that user input is treated as data and not executable SQL code. Additionally, input validation and proper access control should be implemented.

What are stored procedures and what are their advantages?

Answer: Stored procedures are precompiled SQL code that is stored in the database. They can accept input parameters and return output. Advantages of stored procedures include improved performance, code reusability, enhanced security, and easier maintenance.

What is a view in SQL and why would you use it?

Answer: A view in SQL is a virtual table derived from one or more tables. It does not store any data itself but retrieves data from the underlying tables. Views provide a way to simplify complex queries, hide sensitive information, and present a customized subset of data to users.

What is the difference between DELETE and TRUNCATE commands?

Answer: DELETE is a DML command used to remove specific rows from a table based on specified conditions. It can be rolled back, and triggers associated with the table are fired for each affected row. TRUNCATE is a DDL command used to remove all rows from a table, resetting the table to its initial state. It cannot be rolled back, and triggers are not fired.

How can you find duplicate records in a table?

Answer: To find duplicate records in a table, you can use the GROUP BY clause with the HAVING clause. 

SELECT column1, column2, COUNT(*)

FROM table_name

GROUP BY column1, column2

HAVING COUNT(*) > 1;

Explain the difference between GROUP BY and HAVING clauses in SQL.

Answer: GROUP BY is used to group rows based on one or more columns. It is typically used with aggregate functions like SUM, COUNT, etc., to perform calculations on each group. HAVING is used to filter the grouped rows based on a condition. It is used to specify conditions on the result of the GROUP BY clause.

How can you retrieve the top N records from a table in SQL?

Answer: To retrieve the top N records from a table, you can use the LIMIT or TOP clause, depending on the SQL dialect you are using. Here are examples for different databases:

MySQL, PostgreSQL:

SELECT column1, column2

FROM table_name

ORDER BY column1

LIMIT N;

What is a self-join and when would you use it?

Answer: A self-join is a join operation where a table is joined with itself. It is useful when you want to combine rows from a table based on a related column within the same table. For example, if you have an employee table with a manager_id column, you can perform a self-join to retrieve the employees and their corresponding managers.

Explain the difference between a database and a schema.

Answer: In SQL, a database is a collection of related data and objects, including tables, views, indexes, etc. It is the top-level container for data. A schema, on the other hand, is a logical container within a database that holds objects such as tables, views, and procedures. A database can have multiple schemas.

What are triggers in SQL and how are they used?

Answer: Triggers in SQL are special types of stored procedures that are automatically executed or fired in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers can be used to enforce data integrity, perform auditing, or automate certain tasks before or after a data change.

How can you calculate the average of a column in SQL?

Answer: To calculate the average of a column in SQL, you can use the AVG function. 

SELECT AVG(column_name)

FROM table_name;

What are the ACID properties in the context of database transactions?

Answer:

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are reliable and maintain data integrity.

Atomicity: Atomicity ensures that a transaction is treated as a single unit of work. It means that either all the changes made within a transaction are successfully committed to the database, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database remains unchanged.

Consistency: Consistency ensures that the database remains in a valid state before and after the transaction. It ensures that the data meets certain predefined rules or constraints. If a transaction violates any of these rules, the database will reject the transaction, and it will not be committed.

Isolation: Isolation ensures that concurrent transactions do not interfere with each other. Each transaction operates independently of other transactions, and the changes made by one transaction are not visible to others until it is committed. Isolation prevents issues such as dirty reads, non-repeatable reads, and phantom reads.

Durability: Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent system failures. The changes are stored permanently in the database and cannot be undone. Even in the event of power loss or system crashes, the committed data remains intact.

The ACID properties are essential for ensuring data integrity, reliability, and concurrency control in database systems. They provide a solid foundation for maintaining the consistency and durability of data in a transactional environment.