fbpx

Top 100 Business Analyst SQL Interview Questions and Answers

Software Engineer Coding 31
Contents show

Question 1: What is SQL?

SQL (Structured Query Language) is a domain-specific language used for managing and querying relational databases. It allows users to interact with databases by defining, manipulating, and querying data.

Answer: SQL is a standard language for managing and querying relational databases. It provides powerful tools for retrieving, inserting, updating, and deleting data.

Code Example:

SELECT * FROM employees WHERE department = 'Sales';

Official Reference: SQL W3Schools


Question 2: Explain the difference between SQL and NoSQL databases.

SQL databases are relational, using tables to store structured data, while NoSQL databases are non-relational, using various data models like document, key-value, or graph to store unstructured or semi-structured data.

Answer: SQL databases are well-suited for structured data and complex queries, while NoSQL databases offer flexibility and scalability for semi-structured or unstructured data.

Code Example: N/A

Official Reference: SQL vs. NoSQL


Question 3: How do you retrieve all records from a table named ‘orders’?

To retrieve all records from a table named ‘orders,’ you can use the SQL SELECT statement without any conditions.

Answer: You can use the following SQL query to retrieve all records from the ‘orders’ table:

Code Example:

SELECT * FROM orders;

Official Reference: SQL SELECT Statement


Certainly! Here are more Business Analyst SQL interview questions and answers:

Question 4: What is a primary key in SQL?

A primary key is a column or a set of columns in a table that uniquely identifies each record. It ensures that there are no duplicate values and allows for efficient retrieval of data.

Answer: A primary key is essential for data integrity and serves as a reference point for relationships between tables.

Code Example: N/A

Official Reference: SQL PRIMARY KEY Constraint


Question 5: Explain the difference between ‘INNER JOIN’ and ‘LEFT JOIN’.

INNER JOIN returns only the matching records between two tables, while LEFT JOIN returns all records from the left table and the matching records from the right table.

Answer: INNER JOIN is used when you only want to retrieve records with matching values, while LEFT JOIN retrieves all records from the left table, even if there are no matches in the right table.

Code Example:

SELECT * FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

Official Reference: SQL JOINs


Question 6: What is a subquery in SQL?

A subquery, also known as a nested query, is a query within another query. It allows for complex queries by using the result of one query as a condition in another.

Answer: Subqueries are powerful tools for filtering, aggregating, and performing calculations on data.

Code Example:

SELECT product_name 
FROM products 
WHERE product_id IN (SELECT product_id FROM orders WHERE order_date > '2023-01-01');

Official Reference: SQL Subqueries


Question 7: How do you calculate the total number of records in a table?

To calculate the total number of records in a table, you can use the SQL COUNT() function.

Answer: COUNT() provides a quick and efficient way to get the total number of records in a table.

Code Example:

SELECT COUNT(*) FROM table_name;

Official Reference: SQL COUNT() Function


Certainly! Here are more Business Analyst SQL interview questions and answers:

Question 8: What is a self-join in SQL?

A self-join is a type of join where a table is joined with itself. It is useful for retrieving related information from the same table.

Answer: Self-joins are used when you need to compare rows within the same table.

Code Example:

SELECT a.employee_name, b.supervisor_name
FROM employees a, employees b
WHERE a.supervisor_id = b.employee_id;

Official Reference: SQL Self-Join


Question 9: Explain the use of the GROUP BY clause.

The GROUP BY clause is used to group rows returned by a SELECT query based on the values in one or more columns. It is often used with aggregate functions like COUNT(), SUM(), AVG(), etc.

Answer: GROUP BY helps in summarizing data and performing aggregate operations on groups of rows.

Code Example:

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

Official Reference: SQL GROUP BY


Question 10: What is a correlated subquery?

A correlated subquery is a subquery that depends on the outer query. It references columns from the outer query and is evaluated once for each row processed by the outer query.

Answer: Correlated subqueries are used when you need to perform operations based on values from the outer query.

Code Example:

SELECT product_name
FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);

Official Reference: Correlated Subqueries


Question 11: What is a view in SQL?

A view is a virtual table that is based on the result of a SELECT query. It does not store data on its own but provides a way to represent data from one or more tables.

Answer: Views simplify complex queries and can be used to restrict access to certain columns or rows.

Code Example: N/A

Official Reference: SQL Views


Certainly! Here are more Business Analyst SQL interview questions and answers:

Question 12: What is a stored procedure in SQL?

A stored procedure is a set of SQL statements that perform a specific task. It is stored in the database and can be executed by calling its name.

Answer: Stored procedures help in reusing code, improving performance, and providing a layer of security.

Code Example:

CREATE PROCEDURE get_employee_count
AS
BEGIN
    SELECT COUNT(*) FROM employees;
END;

Official Reference: SQL Stored Procedures


Question 13: What is a transaction in SQL?

A transaction is a sequence of operations performed as a single unit of work. It ensures data integrity by either committing all changes or rolling back to the previous state in case of an error.

Answer: Transactions are used to maintain the consistency and integrity of a database.

Code Example:

BEGIN TRANSACTION
    -- SQL statements here
COMMIT;

Official Reference: SQL Transactions


Question 14: Explain the purpose of the HAVING clause.

The HAVING clause is used in conjunction with the GROUP BY clause to filter rows returned by a query based on a condition. It is applied after the grouping is done.

Answer: HAVING is used to filter aggregated data.

Code Example:

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Official Reference: SQL HAVING


Question 15: What is a primary key in SQL?

A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified and helps in maintaining data integrity.

Answer: A primary key prevents duplicate and null values.

Code Example: N/A

Official Reference: SQL Primary Key


Question 16: What is a foreign key in SQL?

A foreign key is a field or a set of fields in a table that refers to the primary key in another table. It establishes a relationship between two tables.

Answer: Foreign keys are used to enforce referential integrity in a database.

Code Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Official Reference: SQL Foreign Keys


Question 17: What is a self-join in SQL?

A self-join is a join where a table is joined with itself. It is used to combine rows from the same table based on a related column.

Answer: Self-joins are useful when you want to compare rows within the same table.

Code Example:

SELECT A.employee_name, B.manager_name
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

Official Reference: SQL Self-Join


Question 18: Explain the difference between UNION and UNION ALL.

UNION combines the result sets of two or more SELECT queries and removes duplicates. UNION ALL also combines result sets but retains duplicates.

Answer: UNION ALL is faster than UNION, but it includes duplicate records.

Code Example:

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Official Reference: SQL UNION and UNION ALL


Question 19: What is a view in SQL?

A view is a virtual table based on the result of a SELECT query. It does not store the data itself but provides a way to present the data stored in one or more tables.

Answer: Views simplify complex queries and provide an extra layer of security.

Code Example:

CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 50000;

Official Reference: SQL Views


Question 20: Explain the purpose of an index in SQL.

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and decreased performance on data modification operations.

Answer: Indexes are used to speed up query performance.

Code Example: N/A

Official Reference: SQL Indexes


Question 21: What is a stored procedure in SQL?

A stored procedure is a set of SQL statements that are stored in the database and can be executed later. It can accept input parameters, perform operations, and return results.

Answer: Stored procedures enhance code reusability and security.

Code Example:

CREATE PROCEDURE get_employee_details (IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END;

Official Reference: SQL Stored Procedures


Question 22: Explain the purpose of a trigger in SQL.

A trigger is a special type of stored procedure that is automatically executed or fired when a specific event occurs in a database.

Answer: Triggers are used to enforce business rules and automate actions.

Code Example:

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 30000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary too low';
    END IF;
END;

Official Reference: SQL Triggers


Question 23: What is a cursor in SQL?

A cursor is a database object used to process a result set one row at a time. It allows you to perform operations on individual rows returned by a query.

Answer: Cursors are typically used in stored procedures.

Code Example: N/A

Official Reference: SQL Cursors


Question 24: What is normalization in database design?

Normalization is the process of organizing data in a database to reduce redundancy and dependency by organizing data into separate tables based on their attributes.

Answer: It helps in reducing data anomalies and improves data integrity.

Code Example: N/A

Official Reference: Database Normalization


Question 25: Explain the concept of ACID properties in database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the four key properties that guarantee reliability in database transactions.

Answer: ACID properties ensure that database transactions are processed reliably.

Code Example: N/A

Official Reference: ACID Properties


Question 26: What is a self-join in SQL?

A self-join is a specific type of join 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.

Answer: Self-joins are typically used to compare rows within a table.

Code Example:

SELECT e1.name, e2.name 
FROM employees e1, employees e2 
WHERE e1.manager_id = e2.employee_id;

Official Reference: Self-Joins


Question 27: What is the difference between a clustered and non-clustered index?

A clustered index determines the physical order of data in a table and changes the way records are stored in the database. A non-clustered index creates a separate structure for the index and keeps a pointer to the actual record.

Answer: Clustered indexes are faster for retrieval but slower for inserts, while non-clustered indexes have the opposite behavior.

Code Example: N/A

Official Reference: Clustered vs Non-Clustered Index


Question 28: Explain the purpose of the GROUP BY clause in SQL.

The GROUP BY clause is used to group rows that have the same values into summary rows, like “find the total salary of each department”.

Answer: It is often used with aggregate functions like COUNT, SUM, AVG, etc.

Code Example:

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;

Official Reference: GROUP BY Clause


Question 29: What is a subquery in SQL?

A subquery is a query within another query. It is used to return data that will be used in the main query as a condition.

Answer: Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Code Example:

SELECT name 
FROM employees 
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Official Reference: Subqueries


Question 30: Explain the concept of a view in SQL.

A view is a virtual table that contains the results of a SELECT query. It does not store the data itself but provides a way to represent it.

Answer: Views are used for security, simplicity, and to present data in a specific format.

Code Example:

CREATE VIEW sales_employees AS 
SELECT * FROM employees WHERE department = 'Sales';

Official Reference: SQL Views


Question 31: What is a correlated subquery in SQL?

A correlated subquery is a subquery that depends on the outer query for its execution. It is executed once for each row processed by the outer query.

Answer: Correlated subqueries are used when you want to compare each row with the result of the subquery.

Code Example:

SELECT name 
FROM employees e 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

Official Reference: Correlated Subqueries


Question 32: What is a trigger in SQL?

A trigger is a special type of stored procedure that is automatically executed or fired in response to certain events on a particular table.

Answer: Triggers are used to enforce business rules, perform logging, and complex validations.

Code Example: N/A

Official Reference: SQL Triggers


Question 33: Explain the difference between UNION and UNION ALL in SQL.

UNION and UNION ALL are used to combine the result sets of two or more SELECT queries.

Answer: UNION removes duplicate rows, while UNION ALL includes all rows, including duplicates.

Code Example:

SELECT name FROM employees WHERE department = 'Sales'
UNION ALL
SELECT name FROM employees WHERE department = 'Marketing';

Official Reference: UNION vs UNION ALL


Question 34: What is a stored procedure in SQL?

A stored procedure is a precompiled set of SQL statements that perform a specific task. It is stored in the database and can be called by name.

Answer: Stored procedures are used to encapsulate logic, improve performance, and enhance security.

Code Example: N/A

Official Reference: SQL Stored Procedures


Question 35: What is a user-defined function (UDF) in SQL?

A user-defined function is a set of SQL statements that can be used to perform a specific task. It can return a value or a table.

Answer: UDFs are used to encapsulate complex logic and can be called in SQL queries.

Code Example:

CREATE FUNCTION dbo.GetEmployeeCount(@departmentId INT)
RETURNS INT
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM employees WHERE department_id = @departmentId)
END;

Official Reference: SQL User-Defined Functions


Question 36: Explain the concept of indexing in databases.

Indexing is a technique used to improve the speed of data retrieval operations on a table at the cost of additional space and decreased performance on data modification operations. It creates a separate data structure that allows faster access to rows based on the indexed columns.

Answer: Indexing is crucial for optimizing queries, especially when dealing with large datasets.

Code Example: N/A

Official Reference: Database Indexing


Question 37: What is a deadlock in SQL?

A deadlock occurs when two or more transactions are waiting for each other to release locks, resulting in a standstill where none of the transactions can proceed.

Answer: Deadlocks can lead to performance issues and need to be resolved.

Code Example: N/A

Official Reference: SQL Server Deadlocks


Question 38: What is normalization in database design?

Normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables and establishing relationships between them.

Answer: Normalization helps in reducing data anomalies and ensures efficient use of storage.

Code Example: N/A

Official Reference: Database Normalization


Question 39: What is denormalization?

Denormalization is the process of adding redundant data to a normalized database to improve performance or simplify queries. It involves combining tables and columns to reduce the number of joins needed for certain types of queries.

Answer: Denormalization can lead to faster query performance but may increase data redundancy.

Code Example: N/A

Official Reference: Database Denormalization


Question 40: Explain the purpose of the SQL CASE statement.

The SQL CASE statement is a conditional expression that allows you to perform different actions based on different conditions. It is similar to the IF-THEN-ELSE statement in other programming languages.

Answer: CASE statements are useful for performing conditional logic within SQL queries.

Code Example:

SELECT 
    name,
    CASE 
        WHEN age < 18 THEN 'Minor'
        ELSE 'Adult'
    END AS age_group
FROM persons;

Official Reference: SQL CASE Statement


Question 41: What is a self-join in SQL?

A self-join is a type of join where a table is joined with itself. This is useful when you want to combine rows from the same table based on a related column.

Answer: Self-joins are common when working with hierarchical data or when comparing rows within the same table.

Code Example:

SELECT a.name AS employee, b.name AS supervisor
FROM employees a
JOIN employees b ON a.supervisor_id = b.employee_id;

Official Reference: Self Joins


Question 42: Explain the purpose of the SQL UNION operator.

The UNION operator is used to combine the result sets of two or more SELECT queries. It removes duplicate rows by default.

Answer: UNION is helpful when you need to combine data from different tables with compatible column types.

Code Example:

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

Official Reference: UNION Operator


Question 43: What is a correlated subquery in SQL?

A correlated subquery is a subquery that refers to columns in the outer query. It executes once for each row processed by the outer query.

Answer: Correlated subqueries can be slower than non-correlated ones but are necessary for certain types of complex queries.

Code Example:

SELECT name, (SELECT MAX(salary) FROM employees WHERE department_id = d.id) AS max_salary
FROM departments d;

Official Reference: Correlated Subqueries


Question 44: Explain the purpose of the SQL HAVING clause.

The HAVING clause is used in combination with the GROUP BY clause to filter the results of a grouped query.

Answer: HAVING allows you to apply conditions to aggregated data.

Code Example:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Official Reference: HAVING Clause


Question 45: What is a SQL view and why is it used?

A SQL view is a virtual table based on the result of a SELECT query. It doesn’t store data but provides a way to represent complex queries as if they were tables.

Answer: Views help simplify complex queries, provide data security by limiting access to specific columns, and offer a level of abstraction.

Code Example:

CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 60000;

Official Reference: SQL Views


Question 46: Explain the purpose of the SQL CASE statement.

The CASE statement is a conditional expression that allows you to perform different actions based on different conditions.

Answer: It’s useful for transforming data within a query, providing custom output, and handling conditional logic.

Code Example:

SELECT name,
       CASE 
           WHEN salary > 60000 THEN 'High'
           ELSE 'Low'
       END AS salary_category
FROM employees;

Official Reference: CASE Statement


Question 47: What is a SQL trigger and why is it used?

A SQL trigger is a special kind of stored procedure that is activated (“triggered”) in response to a specific event on a particular table or view.

Answer: Triggers are used to enforce business rules, perform validation, or automate tasks when data is modified.

Code Example:

CREATE TRIGGER audit_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
   INSERT INTO audit_table (user, action, timestamp)
   VALUES (CURRENT_USER, 'INSERT', NOW());
END;

Official Reference: SQL Triggers


Question 48: What is normalization in database design?

Normalization is the process of organizing data in a database to minimize redundancy and dependency by organizing table and column data.

Answer: It helps maintain data integrity, reduces storage space, and simplifies data retrieval.

Official Reference: Database Normalization


Question 49: Explain the concept of a primary key in SQL.

A primary key is a column or set of columns in a table that uniquely identifies each row. It enforces entity integrity by ensuring that no duplicate or null values are allowed.

Answer: A primary key is crucial for identifying and relating records in different tables.

Code Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

Official Reference: Primary Key


Question 50: What is an SQL index and why is it important?

An SQL index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional space and decreased performance on data modification operations.

Answer: Indexes speed up query execution by allowing the database to find rows more quickly.

Code Example:

CREATE INDEX idx_last_name ON employees (last_name);

Official Reference: SQL Indexes


Question 51: Explain the concept of a foreign key in SQL.

A foreign key is a column or set of columns in a table that refers to the primary key in another table. It establishes a relationship between two tables.

Answer: It enforces referential integrity by ensuring that relationships between tables are maintained.

Code Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Official Reference: Foreign Key


Question 52: What is a self-join in SQL?

A self-join is a join where a table is joined with itself. It’s useful when you want to combine rows with related rows within the same table.

Answer: It’s commonly used to find hierarchical relationships or to compare rows.

Code Example:

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

Official Reference: Self-Join


Question 53: What is a subquery in SQL?

A subquery, also known as an inner query or nested query, is a query embedded within another query. It’s used to return data that will be used in the main query.

Answer: Subqueries are used for complex filtering, data retrieval, or as a source for aggregate functions.

Code Example:

SELECT name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE name = 'Sales'
);

Official Reference: Subquery


Question 54: Explain the purpose of the SQL UNION operator.

The UNION operator combines the result sets of two or more SELECT statements into a single result set.

Answer: It’s useful for combining data from different tables with similar structures.

Code Example:

SELECT name FROM employees
UNION
SELECT name FROM contractors;

Official Reference: UNION Operator


Question 55: What is a correlated subquery in SQL?

A correlated subquery is a subquery that depends on the outer query for its values. It executes once for every row processed by the outer query.

Answer: It’s used to filter or process data based on values from the outer query.

Code Example:

SELECT name
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

Official Reference: Correlated Subquery


Question 56: What is a trigger in SQL?

A trigger is a special type of stored procedure that is automatically executed (“triggered”) in response to certain events on a particular table or view.

Answer: Triggers are commonly used for enforcing business rules or auditing changes.

Code Example:

CREATE TRIGGER before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.join_date = NOW();
END;

Official Reference: Triggers


Question 57: What is a view in SQL?

A view is a virtual table that represents the result of a SELECT query. It does not store the data itself, but provides a way to present the data in a specific format.

Answer: Views can simplify complex queries and provide a level of security by restricting access to certain columns.

Code Example:

CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, salary
FROM employees;

Official Reference: Views


Question 58: Explain the concept of normalization in databases.

Normalization is the process of organizing data in a database to reduce redundancy and dependency by organizing data into separate tables.

Answer: It helps maintain data consistency and improves data integrity.

Code Example: (Normalization is a design principle, not implemented through SQL code)

Official Reference: Database Normalization


Question 59: What is a stored procedure in SQL?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

Answer: It’s beneficial for modularity, security, and performance.

Code Example:

CREATE PROCEDURE GetEmployeeByID (IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE employee_id = emp_id;
END;

Official Reference: Stored Procedures


Question 60: What is a cursor in SQL?

A cursor is a database object used to process a set of rows returned by a query. It allows you to iterate through a result set, one row at a time.

Answer: Cursors are used when you need to perform row-by-row operations.

Code Example:

DECLARE emp_cursor CURSOR FOR 
SELECT employee_id, first_name, last_name FROM employees;

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor;

Official Reference: Cursors


Question 61: What is the difference between a primary key and a unique key?

A primary key is a column or a set of columns that uniquely identifies each record in a table. It enforces entity integrity.

A unique key, on the other hand, ensures that all values in a column are different. Multiple unique keys can be defined in a table.

Answer: While both ensure uniqueness, a table can have only one primary key, but multiple unique keys.

Code Example:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

Official Reference:


Question 62: Explain the difference between DELETE and TRUNCATE in SQL.

DELETE is a DML (Data Manipulation Language) command that removes one or more rows from a table based on a specified condition. It is slower than TRUNCATE.

TRUNCATE is a DDL (Data Definition Language) command that removes all records from a table. It’s faster than DELETE as it doesn’t maintain transaction logs.

Answer: Use DELETE for selective row removal and TRUNCATE for removing all rows.

Code Example:

DELETE FROM employees WHERE department_id = 10;

TRUNCATE TABLE employees;

Official Reference:


Question 63: What is a self-join in SQL?

A self-join is a join that occurs when a table references itself. It’s like joining a table with itself.

Answer: It’s used to combine rows with other rows in the same table.

Code Example:

SELECT e1.first_name, e2.first_name 
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

Official Reference: Self-Join


Question 64: What is a correlated subquery in SQL?

A correlated subquery is a subquery that depends on the outer query for its values. It’s executed once for each row processed by the outer query.

Answer: Correlated subqueries are used when the subquery needs to refer to the values from the outer query.

Code Example:

SELECT first_name, last_name 
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

Official Reference: Correlated Subqueries


Question 65: What is a view in SQL?

A view is a virtual table that contains the result set of a SELECT query. It doesn’t store data but provides a way to present it in a structured manner.

Answer: Views simplify complex queries and provide a level of security by allowing users to access specific columns.

Code Example:

CREATE VIEW high_paid_employees AS
SELECT first_name, last_name, salary 
FROM employees
WHERE salary > 80000;

Official Reference: Views


Question 66: Explain the ACID properties of a transaction.

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the key properties of a transaction.

  1. Atomicity: A transaction is either fully completed or not completed at all. There are no partial transactions.
  2. Consistency: A transaction must take the database from one valid state to another. It ensures that the data is accurate.
  3. Isolation: Multiple transactions can occur concurrently without interfering with each other. Each transaction appears to be isolated.
  4. Durability: Once a transaction is committed, the changes made by it persist, even in the case of system failures.

Answer: These properties ensure the reliability and integrity of transactions.

Official Reference: ACID Properties


Question 67: What is a trigger in SQL?

A trigger is a special kind of stored procedure that is executed automatically in response to certain events on a particular table.

Answer: Triggers are used to enforce business rules, perform cascading updates, or maintain audit information.

Code Example:

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (action, user, timestamp) VALUES ('insert', NEW.user, NOW());
END;

Official Reference: Triggers


Question 68: Explain the difference between UNION and UNION ALL in SQL.

UNION and UNION ALL are used to combine the result sets of two or more SELECT queries.

  • UNION: It removes duplicate rows from the combined result set.
  • UNION ALL: It includes all rows, even if they are duplicates.

Answer: UNION ALL is faster than UNION because it doesn’t perform the duplicate removal process.

Code Example:

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Official Reference:


Question 69: What is a stored procedure in SQL?

A stored procedure is a set of SQL statements that can be saved and reused. It enhances code reusability and security.

Answer: Stored procedures reduce the need to rewrite the same SQL code and provide a level of abstraction.

Code Example:

CREATE PROCEDURE get_employee_details (IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE employee_id = emp_id;
END;

Official Reference: Stored Procedures


Question 70: What is a self-join in SQL?

A self-join is a join where a table is joined with itself. It’s useful when you want to combine rows with related data from the same table.

Answer: It requires aliasing the table to differentiate between the two instances of the same table.

Code Example:

SELECT e1.first_name, e2.first_name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

Official Reference: Self-Joins


Question 71: Explain the purpose of the GROUP BY clause in SQL.

The GROUP BY clause is used to group rows that have the same values into summary rows, like “find the total salary of employees in each department.”

Answer: It’s often used with aggregate functions (e.g., COUNT, SUM) to perform operations on each group.

Code Example:

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id;

Official Reference: GROUP BY


Question 72: What is the purpose of the HAVING clause in SQL?

The HAVING clause filters the result set of a GROUP BY query based on a condition. It’s used to filter groups, not individual rows.

Answer: It’s similar to the WHERE clause but operates on the result of an aggregate function.

Code Example:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

Official Reference: HAVING


Question 73: What is a CASE statement in SQL?

The CASE statement is a conditional expression that allows you to perform different actions depending on different conditions.

Answer: It’s similar to a switch statement in programming languages.

Code Example:

SELECT employee_id, 
       first_name, 
       CASE 
         WHEN salary > 50000 THEN 'High'
         WHEN salary > 30000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_range
FROM employees;

Official Reference: CASE Statement


Question 74: What is a correlated subquery in SQL?

A correlated subquery is a subquery that depends on the outer query for its values. It executes once for each row processed by the outer query.

Answer: It’s used when you want to perform a query for each row in the outer query.

Code Example:

SELECT employee_id, first_name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

Official Reference: Correlated Subqueries


Question 75: Explain the difference between UNION and UNION ALL in SQL.

UNION and UNION ALL are used to combine the result sets of two or more SELECT queries.

Answer: UNION removes duplicate rows, while UNION ALL includes all rows, even duplicates.

Code Example:

-- UNION
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

-- UNION ALL
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Official Reference: UNION and UNION ALL


Question 76: What is the purpose of the TRUNCATE TABLE statement in SQL?

The TRUNCATE TABLE statement is used to remove all records from a table quickly. It’s faster than using DELETE.

Answer: However, it’s important to note that TRUNCATE TABLE cannot be rolled back.

Code Example:

TRUNCATE TABLE table_name;

Official Reference: TRUNCATE TABLE


Question 77: What is an index in SQL?

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and decreased performance on data modification operations.

Answer: It’s used to locate and access rows quickly.

Code Example (Creating an index):

CREATE INDEX index_name ON table_name (column1, column2);

Official Reference: Indexes


Question 78: What is a stored procedure in SQL?

A stored procedure is a precompiled set of SQL statements that can be saved and reused. It can accept input parameters, perform operations, and return results.

Answer: They are often used to encapsulate business logic on the database server.

Code Example (Creating a stored procedure):

CREATE PROCEDURE procedure_name
    @parameter1 datatype,
    @parameter2 datatype
AS
    -- SQL statements

Official Reference: Stored Procedures


Question 79: What is a trigger in SQL?

A trigger is a special type of stored procedure that is activated (“triggered”) in response to a particular event in a database, such as an INSERT, UPDATE, or DELETE operation.

Answer: Triggers are used to enforce business rules, perform data validation, or update other tables.

Code Example (Creating a trigger):

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
    -- SQL statements

Official Reference: Triggers


Question 80: Explain the difference between clustered and non-clustered indexes.

A clustered index determines the physical order of data in a table. A table can have only one clustered index, and it reorders the table rows based on the indexed column.

Answer: A non-clustered index creates a separate structure that contains pointers to the table rows, leaving the table data in its original order.

Code Example (Creating a clustered index):

CREATE CLUSTERED INDEX index_name ON table_name (column1);

Official Reference: Clustered and Nonclustered Indexes


Question 81: What is a view in SQL?

A view is a virtual table that contains the result of a SELECT query. It doesn’t store the data itself but provides a way to represent the data in a specific way.

Answer: Views can be used to simplify complex queries or to restrict access to specific columns.

Code Example (Creating a view):

CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

Official Reference: View


Question 82: What is normalization in database design?

Normalization is the process of organizing data in a database to minimize redundancy and dependency by organizing table and column data into smaller, more manageable structures.

Answer: This involves dividing a database into two or more tables and defining relationships between the tables.


Question 83: Explain the concept of ACID properties in database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are four key properties that guarantee the reliability of database transactions.

Answer:

  • Atomicity ensures that either all operations of a transaction are completed successfully or none of them are.
  • Consistency ensures that the database remains in a valid state before and after a transaction.
  • Isolation ensures that the operations of one transaction do not interfere with those of another.
  • Durability guarantees that once a transaction is committed, its effects are permanent.

Question 84: What is a self-join in SQL?

A self-join is a join that can be used to combine rows from the same table based on a related column.

Answer: It is essentially treated as two separate tables for the purpose of the join.

Code Example (Self-join):

SELECT e1.name, e2.name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

Official Reference: Self-Joins


Question 85: What is a subquery in SQL?

A subquery, also known as an inner query or inner select, is a query nested inside another query.

Answer: It is used to return data that will be used in the main query as a condition.

Code Example (Subquery):

SELECT column1, column2
FROM table_name
WHERE column1 IN (SELECT column1 FROM another_table WHERE condition);

Official Reference: Subqueries


Question 86: Explain the difference between UNION and UNION ALL in SQL.

UNION combines the result sets of two or more SELECT statements, but it removes duplicates.

Answer: UNION ALL also combines result sets, but it includes all rows, including duplicates.

Code Example (UNION and UNION ALL):

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

Official Reference: UNION and UNION ALL


Question 87: What is a correlated subquery in SQL?

A correlated subquery is a subquery that refers to values from the outer query. It can be thought of as a subquery that is executed once for each row processed by the outer query.

Answer: This allows the subquery to be dependent on the outer query.

Code Example (Correlated Subquery):

SELECT column1
FROM table1 t1
WHERE column2 > (SELECT AVG(column2) FROM table1 t2 WHERE t2.column1 = t1.column1);

Official Reference: Correlated Subqueries


Question 88: What is a trigger in SQL?

A trigger is a special type of stored procedure that is activated (“triggered”) in response to a particular event within a database.

Answer: Triggers can be set to execute either before or after a data modification operation (e.g., INSERT, UPDATE, DELETE).

Code Example (Trigger):

CREATE TRIGGER audit_trigger
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
   INSERT INTO audit_table (column1, column2) VALUES (NEW.column1, NEW.column2);
END;

Official Reference: Triggers


Question 89: What is a view in SQL?

A view is a virtual table that is based on the result of a SELECT query. It does not store the data itself but provides a way to represent the data stored in one or more tables.

Answer: Views can simplify complex queries and provide an additional layer of security.

Code Example (Creating a View):

CREATE VIEW view_name AS
SELECT column1, column2
FROM table1
WHERE condition;

Official Reference: Views


Question 90: What is a cursor in SQL?

A cursor is a database object that allows for sequential processing of result sets. It is particularly useful when working with individual rows of a result set one at a time.

Answer: Cursors are commonly used within stored procedures.

Code Example (Cursor):

DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM table1
WHERE condition;

OPEN cursor_name;

FETCH cursor_name INTO var1, var2;

CLOSE cursor_name;

Official Reference: Cursors


Question 91: What is the difference between clustered and non-clustered indexes?

A clustered index determines the physical order of data in a table, while a non-clustered index does not.

Answer: A table can have only one clustered index, but multiple non-clustered indexes.

Code Example (Creating Indexes):

-- Clustered Index
CREATE CLUSTERED INDEX index_name ON table1 (column1);

-- Non-Clustered Index
CREATE INDEX index_name ON table1 (column1);

Official Reference: Clustered and Non-Clustered Indexes


Question 92: What is a deadlock in SQL?

A deadlock is a situation in a multi-user environment where two or more transactions are unable to proceed because each is waiting for the other to release a lock.

Answer: SQL Server automatically detects deadlocks and resolves them by rolling back the least expensive transaction.

Official Reference: Deadlocks


Question 93: Explain the purpose of the COMMIT and ROLLBACK statements in SQL.

The COMMIT statement is used to save all the transactions to the database, while the ROLLBACK statement is used to undo transactions that have not already been saved to the database.

Answer: COMMIT makes all data changes permanent, while ROLLBACK reverts the database to the state it was in at the beginning of the transaction.

Code Example (COMMIT and ROLLBACK):

COMMIT;

ROLLBACK;

Official Reference: COMMIT and ROLLBACK


Question 94: What is the purpose of the GROUP BY clause in SQL?

The GROUP BY clause is used to arrange identical data into groups. This is especially useful when we want to perform aggregate functions like COUNT, SUM, AVG, etc., on each group.

Answer: It’s often used in conjunction with aggregate functions to generate summaries of data.

Code Example (GROUP BY):

SELECT column1, COUNT(*)
FROM table1
GROUP BY column1;

Official Reference: GROUP BY


Question 95: What is a self-join in SQL?

A self-join is a join where a table is joined with itself. This can be useful for querying hierarchical data or when you need to compare rows within the same table.

Answer: It requires aliasing the table to differentiate between the two instances of the same table.

Code Example (Self-Join):

SELECT t1.column1, t2.column2
FROM table1 t1
JOIN table1 t2 ON t1.column3 = t2.column3;

Official Reference: Self-Join


Question 96: What is a common table expression (CTE) in SQL?

A Common Table Expression (CTE) is a named temporary result set that can be referenced within the context of a SELECT, INSERT, UPDATE, or DELETE statement.

Answer: CTEs improve the readability and maintainability of complex queries.

Code Example (CTE):

WITH cte_name (column1, column2) AS (
  SELECT column1, column2 FROM table1 WHERE condition
)
SELECT * FROM cte_name;

Official Reference: Common Table Expressions


Question 97: What is a window function in SQL?

A window function performs a calculation across a set of table rows related to the current row. It does not cause rows to become grouped into a single output row like aggregate functions.

Answer: Window functions are typically used for tasks such as ranking, running totals, and moving averages.

Code Example (Window Function – ROW_NUMBER()):

SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY column4) as row_num
FROM table1;

Official Reference: Window Functions


Question 98: What is a correlated subquery in SQL?

A correlated subquery is a subquery that refers to values from the outer query. It can be thought of as a subquery that is executed once for each row processed by the outer query.

Answer: This allows the subquery to be dependent on the outer query.

Code Example (Correlated Subquery):

SELECT column1
FROM table1 t1
WHERE column2 > (SELECT AVG(column2) FROM table1 t2 WHERE t2.column1 = t1.column1);

Official Reference: Correlated Subqueries


Question 99: What is a trigger in SQL?

A trigger is a special kind of stored procedure that is activated (“triggered”) in response to a particular event occurring in a table or view. This event can be an INSERT, UPDATE, or DELETE operation.

Answer: Triggers are commonly used for enforcing business rules, auditing, or replicating data.

Code Example (Trigger – After Insert):

CREATE TRIGGER trig_name
AFTER INSERT ON table1
FOR EACH ROW
BEGIN
   -- Trigger code here
END;

Official Reference: Triggers


Question 100: What is a view in SQL?

A view is a virtual table that presents data from one or more tables in a specific way, based on a predefined query. It doesn’t store the data itself but provides a way to represent it.

Answer: Views are useful for simplifying complex queries, providing security by limiting access to specific columns, and more.

Code Example (Creating a View):

CREATE VIEW view_name AS
SELECT column1, column2
FROM table1
WHERE condition;

Official Reference: Views