Top 10 Interview Question For Data Analyst in 2023

1 What is the difference between INNER JOIN and OUTER JOIN in SQL?

INNER JOIN returns only the matching rows from both tables, while OUTER JOIN returns all the rows from both tables, along with null values for non-matching rows.

Example:

sql
SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id;

2 What is a subquery in SQL? Provide an example.

A subquery is a query that is nested within another query. It is used to retrieve data that will be used in the main query.

Example:

sql
SELECT *
FROM table1
WHERE id IN (SELECT id FROM table2 WHERE column = 'value');

3 What is a view in SQL? Provide an example.

A view is a virtual table that is based on the result of a SELECT statement. It is used to simplify complex queries and to provide a level of security by limiting access to certain data.

Example:

sql
CREATE VIEW view1 AS
SELECT column1, column2
FROM table1
WHERE column1 = 'value';

4 What is the difference between a clustered index and a non-clustered index in SQL?

A clustered index determines the physical order of data in a table, while a non-clustered index creates a separate structure that references the original data.

5 What is normalization in SQL? Provide an example.

Normalization is the process of organizing data in a database to eliminate redundancy and dependency. It involves breaking down a table into smaller tables with related data.

Example:

sql
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    customer_email VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

6 What is a stored procedure in SQL? Provide an example.

A stored procedure is a set of SQL statements that are stored in the database and can be called by other SQL statements or applications.

Example:

sql
CREATE PROCEDURE procedure1
AS
BEGIN
    SELECT * FROM table1
END;

7 What is a transaction in SQL? Provide an example.

A transaction is a series of SQL statements that are executed as a single unit of work.

Example:

sql                                                         
BEGIN TRANSACTION;
UPDATE table1 SET column1 = 'value' WHERE id = 1;
DELETE FROM table2 WHERE column1 = 'value';
COMMIT TRANSACTION;

8 What is the difference between GROUP BY and ORDER BY in SQL?

GROUP BY is used to group rows based on a specific column, while ORDER BY is used to sort the result set based on a specific column.

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

A function returns a single value, while a stored procedure does not necessarily return a value. Additionally, a function can be used as part of a SELECT statement, while a stored procedure cannot.

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

UNION removes duplicate rows from the result set, while UNION ALL does not.

Example:

sql
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;