Top 50 SQL Interview Questions: Your Pathway to Success with Well-crafted Answers

Are you preparing for an upcoming SQL interview and looking for a comprehensive resource to help you tackle those tricky questions? Look no further! In this article, we have compiled a list of 50 SQL interview questions that are designed to test your knowledge and problem-solving skills. Whether you’re a beginner or an experienced SQL developer, these questions cover a wide range of topics and will challenge you to think critically. But that’s not all – we also provide detailed answers and explanations for each question to help you understand the concepts and ace your interview. So, get ready to enhance your SQL expertise and confidently answer those tricky interview questions with our expert guidance. Let’s dive in!

SQL Interview: What is SQL?

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It provides a standard way to interact with databases, enabling users to store, retrieve, modify, and delete data.

SQL (Structured Query Language) is a programming language specifically designed for managing and manipulating relational databases. It is the standard language for interacting with relational database management systems (RDBMS) such as MySQL, Oracle, SQL Server, and PostgreSQL. SQL allows users to create, modify, and query databases, as well as define and enforce relationships between tables.

With SQL, you can perform a wide range of tasks, including creating and altering database tables, inserting, updating, and deleting data, retrieving and filtering data using various conditions, joining multiple tables to retrieve related data, and performing aggregate calculations on data.

SQL is a declarative language, meaning you specify what data you want to retrieve or modify, rather than how to do it. SQL statements are written in a structured format and consist of keywords, operators, clauses, and functions. It provides a powerful and efficient way to manage and analyze structured data, making it a fundamental skill for anyone working with databases or pursuing a career in data management or data analysis.

What are the different types of SQL statements?

There are mainly four types of SQL statements:

• DDL (Data Definition Language): Used to define and manage the structure of the database, including creating, altering, and dropping tables, indexes, and constraints.
• DML (Data Manipulation Language): Used to manipulate the data in the database, including inserting, updating, and deleting records in tables.
• DQL (Data Query Language): Used to retrieve data from the database, primarily using the SELECT statement.
• DCL (Data Control Language): Used to control access to the database, including granting or revoking privileges and permissions.

What is a primary key?

A primary key is a column or a combination of columns that uniquely identifies each record in a table. It ensures the uniqueness and integrity of the data. By defining a primary key, you guarantee that each row in the table can be uniquely identified.

A primary key in a database is a unique identifier for each record (row) in a table. It is used to ensure the uniqueness and integrity of the data within the table. A primary key can consist of one or more columns in the table, and its values must be unique and not null.

For example, let’s consider a table named “Customers” that stores information about customers. We can define a primary key on the “CustomerID” column to uniquely identify each customer. Here’s how the table might look:

In this example, the “CustomerID” column serves as the primary key. Each value in the “CustomerID” column is unique and identifies a specific customer. The primary key ensures that no two customers have the same ID, and it allows for efficient retrieval and manipulation of data within the “Customers” table.

What is a foreign key?

A foreign key is a column or a combination of columns that establishes a link between two tables. It refers to the primary key of another table, creating a relationship between the two tables. The foreign key constraint ensures referential integrity, enforcing that the values in the foreign key column(s) match the values in the primary key column(s) of the referenced table.

A foreign key in a database is a field or a set of fields in a table that refers to the primary key of another table. It establishes a relationship between two tables, allowing data in one table to reference data in another table. The foreign key constraint ensures referential integrity, meaning that the values in the foreign key column(s) must correspond to existing values in the referenced primary key column(s).

Here’s an example to illustrate the concept of a foreign key:

Let’s consider two tables, “Orders” and “Customers“. The “Orders” table stores information about orders placed by customers, while the “Customers” table stores information about the customers themselves. To establish a relationship between these tables, we can use a foreign key.

Customers Table:

Orders Table:

In this example, the “CustomerID” column in the “Orders” table is a foreign key that references the primary key “CustomerID” in the “Customers” table. It establishes a relationship between the two tables, indicating that each order in the “Orders” table belongs to a specific customer in the “Customers” table.

The foreign key constraint ensures that the values in the “CustomerID” column of the “Orders” table must exist in the “CustomerID” column of the “Customers” table. This constraint maintains referential integrity and helps enforce the relationship between the two tables.

Explain the different types of joins in SQL.

There are four types of joins commonly used in SQL:

• INNER JOIN: Returns only the rows that have matching values in both tables involved in the join. It combines the rows based on the common column(s) specified in the join condition.
• LEFT JOIN: Returns all the rows from the left table and the matched rows from the right table. If there are no matches, NULL values are returned for the columns of the right table.
• RIGHT JOIN: Returns all the rows from the right table and the matched rows from the left table. If there are no matches, NULL values are returned for the columns of the left table.
• FULL JOIN: Returns all the rows from both tables, matching the rows based on the common column(s). If there are no matches, NULL values are returned for the columns of the table that does not have a matching row.

INNER JOIN

The INNER JOIN returns only the rows where there is a match between the columns in both tables being joined.

Example:
Consider two tables, “Customers” and “Orders“, with a common column “CustomerID“.

Query:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

LEFT JOIN:

The LEFT JOIN returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table columns.

Example:
Using the same “Customers” and “Orders” tables as above.

Query:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

RIGHT JOIN:

The RIGHT JOIN returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table columns.

Example:
Using the same “Customers” and “Orders” tables as above.

Query:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

FULL JOIN:

The FULL JOIN returns all the rows from both the left and right tables, combining the result of both LEFT JOIN and RIGHT JOIN. If there is no match, NULL values are returned for the respective table’s columns.

Example:

Using the same “Customers” and “Orders” tables as above.

Query:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Output:

What is normalization in SQL?

Normalization is the process of organizing data in a database to eliminate redundancy and dependency issues. It involves breaking down larger tables into smaller tables and defining relationships between them. The main goal of normalization is to reduce data duplication and ensure data integrity.

Example: Tables

Customers

To normalize this table, we identify a potential redundancy. In this case, the “Email” column holds unique values, which means we can extract it into a separate table to avoid repetition.

Normalized Customers Table:

Emails Table:

In the normalized structure, we separated the email addresses into a new table called “Emails.” This table has a foreign key, “CustomerID,” which references the primary key in the “Customers” table. This way, we eliminate the repetition of email addresses and ensure data consistency.

By applying normalization, we enhance data integrity, reduce redundancy, and establish proper relationships between tables. Normalization is typically performed in multiple stages, called normal forms, to ensure the database is efficiently organized and optimized for data management.

What is denormalization?

Denormalization is the process of combining normalized tables into larger tables or duplicating data to improve query performance. It involves adding redundant data to eliminate the need for joins and reduce the complexity of queries. Denormalization is often used in data warehousing and reporting scenarios where read performance is crucial.

Example

Normalized Customers Table:

Deformalized Customers Table with Email:

In this example, we deformalized the table by reintroducing the “Email” column directly into the “Customers” table. By doing so, we eliminate the need for an additional table join when retrieving customer information along with their email addresses. This deformalized structure simplifies queries and can improve read performance.

Denormalization is a trade-off between performance and data redundancy. While it can improve query performance, it may result in duplicated data and potential data inconsistencies if updates are not properly managed. Denormalization should be carefully considered based on specific use cases and performance requirements.

Explain the difference between DELETE, TRUNCATE, and DROP commands.

• DELETE: The DELETE command is used to remove specific rows from a table based on a condition. It allows you to selectively delete rows while keeping the table structure intact.
• TRUNCATE: The TRUNCATE command is used to remove all the rows from a table, effectively resetting the table. It is faster than DELETE as it deallocates the data pages, but it cannot be rolled back.
• DROP: The DROP command is used to remove an entire table from the database. It deletes both the table structure and the data within it. The DROP command is not reversible, and the table cannot be recovered once dropped.

What is the difference between CHAR and VARCHAR data types?

• CHAR: The CHAR data type is used to store fixed-length character strings. It allocates a fixed amount of storage space for each value, padding the remaining space with trailing blanks if necessary. CHAR is more suitable for storing data with a consistent length.
• VARCHAR: The VARCHAR data type is used to store variable-length character strings. It allocates storage space based on the actual length of the value, without padding. VARCHAR is more suitable for storing data with varying lengths to optimize storage.

What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of values and return a single value. The commonly used aggregate functions in SQL are:

• COUNT: Returns the number of rows in a table or a group.
• SUM: Calculates the sum of values in a column.
• AVG: Calculates the average value of a column.
• MIN: Returns the minimum value in a column.
• MAX: Returns the maximum value in a column.
• GROUP_CONCAT: Concatenates values from multiple rows into a single string.

What is a subquery?

A subquery, also known as a nested query, is a query nested within another query. It is used to retrieve data that will be used by the main query. The result of the subquery is used as a condition or a filter in the outer query. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.

Examples

Example 1: Using a Subquery in the WHERE Clause

Suppose we want to retrieve the orders made by customers whose names start with the letter ‘J‘. We can use a subquery in the WHERE clause to accomplish this:

SELECT OrderID, CustomerID, Amount
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE CustomerName LIKE 'J%');

The subquery (SELECT CustomerID FROM Customers WHERE CustomerName LIKE ‘J%’) retrieves the CustomerIDs of customers whose names start with ‘J‘. The main query then uses this result to filter the Orders table and returns the corresponding orders.

Output:

Example 2: Using a Subquery in the SELECT Clause

Suppose we want to retrieve the total amount spent by each customer. We can use a subquery in the SELECT clause to calculate the sum:

SELECT CustomerName, (SELECT SUM(Amount) FROM Orders WHERE CustomerID = Customers.CustomerID) AS TotalAmount
FROM Customers;

The subquery (SELECT SUM(Amount) FROM Orders WHERE CustomerID = Customers.CustomerID) calculates the sum of the amounts for each customer using the CustomerID as the reference. The main query returns the customer name and the corresponding total amount spent.

Output:

These are just a few examples showcasing the usage of subqueries in SQL. Subqueries offer a powerful way to retrieve and manipulate data by leveraging the results of other queries within a larger SQL statement.

What is a view in SQL?

A view is a virtual table derived from one or more tables or views. It does not store any data itself but provides an alternate way to present the data stored in tables. Views are created based on the result of a query and can be used to simplify complex queries, restrict access to data, or provide a customized view of the data to different users.

Views offer several benefits in SQL, including:

• Simplified Data Access: Views provide a simplified and abstracted way to access and query complex data structures.
• Data Security: Views allow you to control the visibility of certain columns or rows, enabling you to restrict access to sensitive data.
• Data Abstraction: Views can present a subset of columns or perform calculations, hiding the underlying complexity of the database schema.
• Query Reusability: Views encapsulate complex queries, allowing them to be reused across different parts of an application or within other views.

Views are dynamic, meaning they reflect the current state of the underlying tables. If the data in the original tables changes, the view will automatically reflect those changes when queried.

Example: Creating a View

Let’s create a view called “HR_Employees” that only includes employees from the HR department. The view will contain EmployeeID, First Name, and Last Name columns (in Employees table).

CREATE VIEW HR_Employees AS
SELECT EmployeeID, First Name, Last Name
FROM Employees
WHERE Department = 'HR';

The view “HR_Employees” is created using the SELECT statement, filtering the rows based on the condition Department = ‘HR‘. Now, we can query the view as if it were a table:

Example: Querying a View

SELECT * FROM HR_Employees;

Output:

The query on the view retrieves only the rows that meet the specified condition, i.e., employees from the HR department.

Explain the difference between UNION and UNION ALL.

• UNION: 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 from the final result set.
• UNION ALL: The UNION ALL operator also combines the result sets of multiple SELECT statements into a single result set. However, it does not remove duplicate rows and includes all rows from the individual result sets.

UNION Example:

Example: Using UNION to Combine Customers and Suppliers

Suppose we want to retrieve a list of all customers and suppliers together. We can use the UNION operator to combine the results of two SELECT statements:

SELECT CustomerID, CustomerName
FROM Customers
UNION
SELECT SupplierID, SupplierName
FROM Suppliers;

The first SELECT statement retrieves the CustomerID and CustomerName from the Customers table. The second SELECT statement retrieves the SupplierID and SupplierName from the Suppliers table. The UNION operator combines the results of these two SELECT statements, removing any duplicate rows.

Output:

The final result set includes all the unique records from both the Customers and Suppliers tables.

It’s important to note that when using UNION, the number and order of columns in all SELECT statements involved should match. Additionally, UNION only removes duplicate rows; if you want to include all rows, including duplicates, you can use the UNION ALL operator instead of UNION.

UNION ALL Example

Example: Using UNION ALL to Combine Customers and Suppliers
Suppose we want to retrieve a list of all customers and suppliers together, including duplicate rows. We can use the UNION ALL operator to combine the results of two SELECT statements:

SELECT CustomerID, CustomerName
FROM Customers
UNION ALL
SELECT SupplierID, SupplierName
FROM Suppliers;

The first SELECT statement retrieves the CustomerID and CustomerName from the Customers table. The second SELECT statement retrieves the SupplierID and SupplierName from the Suppliers table. The UNION ALL operator combines the results of these two SELECT statements, including any duplicate rows.

Output:

What is a stored procedure?

A stored procedure is a named set of SQL statements that are stored in the database and can be executed multiple times. It provides a way to encapsulate and reuse complex database operations. Stored procedures can have input and output parameters, and they are compiled and stored in the database, improving performance and security.

What is a trigger in SQL?

A trigger is a special type of stored procedure that is automatically executed in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers are defined to enforce business rules, maintain data integrity, or perform additional actions based on the changes made to the data.

What is indexing in SQL?

Indexing is the process of creating a data structure that improves the speed of data retrieval operations on database tables. Indexes are created on columns to facilitate faster search and retrieval of data. They act like an index in a book, allowing the database engine to quickly locate the required data.

Explain the concept of ACID properties in the context of database transactions.

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability, which are the four properties that ensure reliable and consistent database transactions:

• Atomicity: It guarantees that a transaction is treated as a single unit of work. Either all the changes made in a transaction are committed, or none of them are.
• Consistency: It ensures that the database remains in a consistent state before and after the transaction. The data must satisfy all the defined integrity constraints.
• Isolation: It ensures that concurrent transactions do not interfere with each other. Each transaction appears to execute in isolation from others, maintaining data integrity.
• Durability: It guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent failures or system crashes.

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

• Clustered index: A clustered index determines the physical order of the data in a table. Each table can have only one clustered index, and it determines the way data is stored on disk. It is helpful for improving the performance of queries that retrieve data based on the index key.
• Non-clustered index: A non-clustered index is a separate structure from the actual data, containing a sorted list of key values and a pointer to the corresponding data rows. Each table can have multiple non-clustered indexes. It is useful for improving the performance of queries that involve searching or sorting operations.

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 typically used with aggregate functions to perform calculations on each group of rows. The result is a summary of data grouped by the specified column(s). The GROUP BY clause is commonly used in conjunction with the SELECT statement.

Explain the HAVING clause in SQL.

The HAVING clause is used to filter groups in a query result based on a condition. It is similar to the WHERE clause, but the HAVING clause operates on groups created by the GROUP BY clause. It allows you to filter the groups based on aggregate functions and specify conditions that involve the summarized data.

What is the difference between a LEFT JOIN and a RIGHT JOIN?

• LEFT JOIN: A LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there are no matches, NULL values are returned for the columns of the right table.
• RIGHT JOIN: A RIGHT JOIN returns all the rows from the right table and the matched rows from the left table. If there are no matches, NULL values are returned for the columns of the left table.

The main difference between LEFT JOIN and RIGHT JOIN is the order of the tables involved in the join.

What is a self-join?

A self-join is a join operation where a table is joined with itself. It is useful when you want to combine rows from the same table based on a related column. In a self-join, two instances (aliases) of the same table are used in the join condition to establish the relationship between the rows.

How do you handle NULL values in SQL?

NULL represents the absence of a value or an unknown value. To handle NULL values in SQL, you can use functions like IS NULL, IS NOT NULL, COALESCE, and NULLIF. These functions allow you to check for NULL values, replace NULL values with a default value, or perform conditional operations based on NULL values.

Explain the concept of database normalization.

Database normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves breaking down large tables into smaller, more manageable tables and defining relationships between them. Normalization follows a set of rules called normal forms (such as First Normal Form, Second Normal Form, etc.) to ensure data integrity, eliminate data anomalies, and optimize database performance.

What is the purpose of the ORDER BY clause?

The ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns. It allows you to specify the sorting order for the retrieved data, ensuring consistent and meaningful presentation of the results.

How do you create a table in SQL?

To create a table in SQL, you use the CREATE TABLE statement. You specify the table name and define the columns and their data types within the CREATE TABLE statement. Additionally, you can define constraints, such as primary keys, foreign keys, and check constraints, to enforce data integrity rules on the table.

What is the difference between UNION and UNION ALL?

• UNION: 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 from the final result set.
• UNION ALL: The UNION ALL operator also combines the result sets of multiple SELECT statements into a single result set. However, it does not remove duplicate rows and includes all rows from the individual result sets.

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

Both unique keys and primary keys are used to enforce uniqueness in a column or a combination of columns.

• Unique key: A unique key ensures that the values in a column or a set of columns are unique and not duplicated. It allows NULL values, and multiple unique keys can exist in a table.
• Primary key: A primary key is a unique identifier for each row in a table. It enforces uniqueness and also prohibits NULL values. Each table can have only one primary key, and it serves as a reference for foreign keys in related tables.

What is the purpose of the LIKE operator?

The LIKE operator is used in conjunction with the WHERE clause to search for a specified pattern in a column. It allows you to perform pattern matching using wildcard characters:

• % (percent sign): Matches any sequence of characters (zero or more).
• _ (underscore): Matches any single character.

The LIKE operator is commonly used with string data types to perform flexible and powerful searches.

How do you calculate the total number of rows in a table?

To calculate the total number of rows in a table, you can use the COUNT() aggregate function. The COUNT() function returns the number of rows that match a specific condition. To count all the rows in a table, you can use the COUNT() syntax, where the asterisk () represents all columns.

Explain the concept of database transactions.

A database transaction is a logical unit of work that consists of one or more database operations. Transactions ensure the atomicity, consistency, isolation, and durability (ACID properties) of the database. The changes made within a transaction are treated as a single operation that is either committed to make the changes permanent or rolled back to undo all the changes if any error occurs.

How do you perform an inner join in SQL?

To perform an inner join in SQL, you use the INNER JOIN keyword in the SELECT statement. You specify the tables to join and the join condition that defines how the tables are related. The join condition is usually based on the equality of values in common columns between the tables. The result of the inner join includes only the rows that have matching values in both tables.

What is the purpose of the DISTINCT keyword?

The DISTINCT keyword is used in conjunction with the SELECT statement to retrieve unique values from a column. It eliminates duplicate rows in the result set and returns only the distinct (unique) values. The DISTINCT keyword is helpful when you want to get a unique list of values from a specific column in a table.

How do you add a new column to an existing table in SQL?

To add a new column to an existing table in SQL, you use the ALTER TABLE statement. You specify the table name and use the ADD COLUMN clause to define the new column name, data type, and any constraints or default values associated with the column. The new column will be added to the existing table structure.

What is the purpose of the TOP keyword in SQL?

The TOP keyword is used to limit the number of rows returned in a query result set. It is often used with the SELECT statement to specify the maximum number of rows to retrieve. The actual syntax for using the TOP keyword may vary depending on the database system. For example, in MySQL, you can use the LIMIT clause instead of the TOP keyword.

Explain the concept of database indexes.

A database index is a data structure that improves the speed of data retrieval operations on database tables. It works similar to an index in a book, allowing the database engine to quickly locate the required data. Indexes are created on one or more columns of a table, and they contain a sorted copy of the indexed column(s), along with pointers to the actual data rows.

What is a correlated subquery?

A correlated subquery is a subquery that refers to a column from the outer query. It is executed for each row of the outer query and depends on the values of the outer query. The result of the correlated subquery is used to filter or evaluate the conditions in the outer query. Correlated subqueries are useful when you need to perform calculations or filtering based on values from related tables.

How do you retrieve data from multiple tables using joins?

To retrieve data from multiple tables using joins, you can specify the tables to join in the FROM clause of the SELECT statement. You also define the join condition that specifies how the tables are related. The join condition usually involves the equality of values in common columns between the tables. You can use different types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN, based on your requirements.

What is the purpose of the ISNULL() function?

The ISNULL() function is used to check if an expression or a column is NULL and replace it with a specified value. It takes two arguments: the expression to check and the value to return if the expression is NULL. The ISNULL() function is commonly used to handle NULL values and provide default values in SQL queries.

How do you retrieve the current date and time in SQL?

To retrieve the current date and time in SQL, you can use the GETDATE() function or its equivalent, depending on the database system. For example, in MySQL, you can use the NOW() function. These functions return the current system date and time in the default format. You can also format the date and time using date formatting functions specific to the database system.

Explain the concept of a self-contained subquery.

A self-contained subquery, also known as a standalone subquery, is a subquery that can be executed independently without any reference to the outer query. It does not depend on the outer query for its execution and can be evaluated separately. The result of the self-contained subquery is used as a value or condition in the outer query.

What is the purpose of the CASE statement in SQL?

The CASE statement in SQL is used to perform conditional logic within a query. It allows you to specify different conditions and corresponding actions or values based on those conditions. The CASE statement has two forms: simple CASE and searched CASE. It is often used in the SELECT statement to perform calculations, transformations, or conditional aggregations.

How do you update data in a table using the UPDATE statement?

To update data in a table, you use the UPDATE statement in SQL. You specify the table name, set the column(s) you want to update, and define the new values. Additionally, you can include a WHERE clause to specify the condition for updating specific rows. The UPDATE statement modifies the existing data in the table based on the specified conditions.

What is the purpose of the EXISTS keyword in SQL?

The EXISTS keyword is used to check if a subquery returns any rows. It is often used in conjunction with the WHERE clause to filter rows based on the existence of related data in another table. The EXISTS keyword returns true if the subquery result is not empty and false otherwise. It helps to conditionally execute certain statements or perform actions based on the existence of data.

How do you delete data from a table using the DELETE statement?

To delete data from a table, you use the DELETE statement in SQL. You specify the table name and include a WHERE clause to specify the condition for deleting specific rows. The DELETE statement removes the specified rows from the table. It is important to use caution when deleting data as it cannot be recovered once deleted.

Explain the concept of a self-join with an example.

A self-join is a join operation where a table is joined with itself. It is useful when you want to combine rows from the same table based on a related column. Let’s consider an example with an “Employees” table that contains information about employees, including their names and managers. To retrieve the names of employees along with their respective manager names, you can perform a self-join on the “Employees” table using a join condition that matches the employee ID with the manager ID. This will result in a table with columns such as “EmployeeName” and “ManagerName,” showing the relationship between employees and their managers.

What is the purpose of the TRUNCATE TABLE statement?

The TRUNCATE TABLE statement is used to remove all the rows from a table while keeping the table structure intact. It is a fast and efficient way to delete all the data from a table without generating individual DELETE statements for each row. However, it should be used with caution as it cannot be rolled back, and the data cannot be recovered once truncated. Additionally, TRUNCATE TABLE resets the identity column values, if present, and frees up the storage space used by the table.

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

To calculate the average of a column in SQL, you can use the AVG() aggregate function. The AVG() function takes the column name as an argument and returns the average value of the specified column across all rows that meet the specified conditions. It is commonly used in conjunction with the SELECT statement to calculate average values in a query result.

What is the purpose of the GROUP BY clause in combination with aggregate functions?

The GROUP BY clause is used to group rows based on one or more columns in a query result set. When used in combination with aggregate functions, such as SUM(), COUNT(), AVG(), MAX(), or MIN(), the GROUP BY clause divides the rows into groups based on the specified columns and calculates the aggregate value for each group. The result is a summary of data grouped by the specified column(s).

Explain the concept of a self-contained subquery with an example.

A self-contained subquery, also known as a standalone subquery, is a subquery that can be executed independently without any reference to the outer query. It does not depend on the outer query for its execution and can be evaluated separately. Let’s consider an example where you want to retrieve the names of customers who have made at least two orders. You can use a self-contained subquery to calculate the count of orders for each customer and then filter the result to retrieve the names of customers with a count greater than or equal to two. The self-contained subquery is executed independently to calculate the count, and the outer query retrieves the names based on the result of the subquery.

Summary:

Mastering SQL is essential for success in many data-centric roles, and SQL interview questions can be a real challenge. To help you prepare, we have compiled a comprehensive list of 50 tricky SQL interview questions along with detailed answers. Whether you’re a seasoned SQL professional or a beginner looking to enhance your skills, this post provides valuable insights and strategies to tackle common SQL interview questions with confidence. From understanding primary keys and foreign keys to exploring different types of joins and normalization concepts, this resource covers a wide range of SQL topics. Sharpen your SQL knowledge, boost your interview readiness, and excel in your next SQL interview with these 50 tricky questions and expertly crafted answers.

 

About Post Author