Top 20 SQL Interview Questions & Answers

 Top 20 SQL Interview Questions & Answers

What is SQL?

SQL stands for Structured Query Language. It is a programming language used to manage and manipulate relational databases.

What is a primary key in SQL?

A primary key is a column or set of columns in a table that uniquely identifies each row in that table. It is used to enforce entity integrity, which means that each row must be uniquely identifiable.

What is the difference between a primary key and a unique key?

Both primary key and unique key constraints enforce uniqueness on one or more columns in a table. However, a primary key constraint also enforces that the column or set of columns cannot contain null values, while a unique key constraint does not.

What are the different types of joins in SQL?

There are four types of joins in SQL: inner join, left join (or left outer join), right join (or right outer join), and full outer join.

What is the difference between a left join and an inner join?

A left join returns all the rows from the left table and the matching rows from the right table, while an inner join only returns the matching rows from both tables.

What is normalization and why is it important in database design?

Normalization is the process of organizing data in a database so that it is free from redundant data and dependencies. It is important in database design because it helps to ensure data integrity, reduce data duplication, and improve query performance.

What is an index in SQL and how does it improve query performance?

An index is a database object that improves query performance by allowing the database to quickly locate and retrieve specific rows from a table. It works like a book index, providing a way to quickly find specific data in a table.

What are triggers in SQL and how are they used?

A trigger is a special type of stored procedure that is automatically executed in response to certain database events, such as INSERT, UPDATE, or DELETE operations. Triggers are used to enforce business rules, maintain data integrity, and automate certain database tasks.

Explain the difference between a clustered and non-clustered index.

A clustered index determines the physical order of data in a table, while a non-clustered index does not. A table can only have one clustered index, but it can have multiple non-clustered indexes.

What is the difference between a view and a table?

A table is a database object that stores data in a structured format, while a view is a virtual table that does not store data but retrieves data from one or more tables in the database.

What is the purpose of the GROUP BY clause in SQL?

The GROUP BY clause is used to group rows in a table based on one or more columns, and then perform aggregate functions on each group. It is commonly used with functions such as SUM, AVG, COUNT, MAX, and MIN.

What is the difference between the HAVING and WHERE clauses?

The WHERE clause is used to filter rows in a table based on a condition, while the HAVING clause is used to filter groups based on a condition that involves an aggregate function.

Explain the ACID properties in the context of databases.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are a set of properties that ensure that database transactions are executed reliably and consistently, even in the event of system failures or errors.

What is the difference between a stored procedure and a function in SQL?

A stored procedure is a database object that performs a set of actions and can return multiple values, while a function is a database object that performs a calculation and returns a single value.

What is the purpose of the UNION operator in SQL?

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default.

What is a subquery in SQL and how is it used?

A subquery, also known as a nested query or inner query, is a query nested within another query. It is used to retrieve data based on the results of another query. A subquery can be used in the SELECT, FROM, WHERE, or HAVING clause of a SQL statement.

What is the difference between UNION and UNION ALL?

UNION combines and returns only distinct rows from the result sets of multiple SELECT statements, while UNION ALL combines and returns all rows, including duplicates, from the result sets of multiple SELECT statements.

How can you prevent SQL injection attacks?

To prevent SQL injection attacks, you should use parameterized queries (or prepared statements) instead of dynamically building SQL queries by concatenating strings. Parameterized queries separate the SQL code from the user input, eliminating the risk of malicious SQL injections.

What is the purpose of the COMMIT and ROLLBACK statements in SQL?

The COMMIT statement is used to save all changes made within a transaction to the database, making them permanent. On the other hand, the ROLLBACK statement is used to undo all changes made within a transaction and restore the database to its previous state.

Explain the difference between a database and a schema in SQL.

A database is a container that holds a collection of related tables, views, procedures, and other database objects. It is used to organize and store data. A schema, on the other hand, is a logical container within a database that holds objects such as tables, views, and procedures. It provides a way to organize and separate database objects into different namespaces.