fbpx

Top 100 SQL Interview Questions and Answers for Data Analysts

Top 100 SQL Interview Questions and Answers for Data Analysts

Contents show

1. What is SQL?

Answer: SQL (Structured Query Language) is a programming language for managing and querying relational databases. It allows users to retrieve and manipulate data stored in tables.

-- Example: Selecting all columns from a table
SELECT * FROM table_name;

2. What is a Database?

Answer: A database is a structured collection of data that is organized and stored electronically. It allows for efficient retrieval, insertion, and management of data.

-- Example: Creating a new database
CREATE DATABASE database_name;

3. What is a Table?

Answer: A table is a collection of related data stored in rows and columns. It is the fundamental structure for organizing data in a database.

-- Example: Creating a new table
CREATE TABLE table_name (
  column1 datatype1,
  column2 datatype2,
  ...
);

4. What is a Primary Key?

Answer: A primary key is a unique identifier for a record in a table. It ensures that each row in a table can be uniquely identified.

-- Example: Adding a primary key to a table
ALTER TABLE table_name
ADD CONSTRAINT pk_column PRIMARY KEY (column);

5. What is a Foreign Key?

Answer: A foreign key is a field in a table that refers to the primary key in another table. It establishes a relationship between two tables.

-- Example: Adding a foreign key constraint
ALTER TABLE table_name
ADD CONSTRAINT fk_column
FOREIGN KEY (column) 
REFERENCES referenced_table (referenced_column);

6. How do you retrieve all records from a table?

Answer: Use the SELECT statement to retrieve all records from a table.

-- Example: Selecting all records from a table
SELECT * FROM table_name;

7. How do you filter records in a SQL query?

Answer: Use the WHERE clause to filter records based on specified conditions.

-- Example: Selecting records where condition is met
SELECT * FROM table_name
WHERE condition;

8. What is the difference between GROUP BY and HAVING?

Answer: GROUP BY is used to group rows that have the same values, while HAVING is used to filter groups based on conditions.

-- Example: Grouping and filtering
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;

9. How do you perform calculations in SQL queries?

Answer: Use arithmetic operators like +, -, *, / for calculations.

-- Example: Calculating total
SELECT SUM(column) FROM table_name;

10. What is a subquery?

Answer: A subquery is a query embedded within another query. It’s used for more complex filtering or calculations.

-- Example: Subquery for filtering
SELECT column
FROM table_name
WHERE column IN (SELECT column FROM another_table WHERE condition);

11. What is the difference between JOIN and UNION?

Answer: JOIN combines rows from two or more tables based on a related column, while UNION combines the result sets of two or more queries.

-- Example: JOIN
SELECT * FROM table1
JOIN table2 ON table1.column = table2.column;

-- Example: UNION
SELECT column FROM table1
UNION
SELECT column FROM table2;

12. How do you find the highest value in a column?

Answer: Use the MAX() function to find the highest value in a column.

-- Example: Finding the highest value
SELECT MAX(column) FROM table_name;

13. What is a view in SQL?

Answer: A view is a virtual table that is based on the result of a SELECT query. It can be queried like a regular table.

-- Example: Creating a view
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

14. How do you update existing records in a table?

Answer: Use the UPDATE statement to modify existing records.

-- Example: Updating records
UPDATE table_name
SET column = new_value
WHERE condition;

15. What is a stored procedure?

Answer: A stored procedure is a set of SQL statements stored in the database and executed as a single unit.

-- Example: Creating a stored procedure
CREATE PROCEDURE procedure_name
AS
BEGIN
  -- SQL statements
END;

16. How do you delete records from a table?

Answer: Use the DELETE statement to remove records from a table.

-- Example: Deleting records
DELETE FROM table_name
WHERE condition;

17. What is a trigger in SQL?

Answer: A trigger is a set of SQL statements that are automatically executed (or “triggered”) in response to certain events on a particular table.

-- Example: Creating a trigger
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements
END;

18. How do you handle NULL values in SQL?

Answer: Use the IS NULL or IS NOT NULL operators to check for NULL values.

-- Example: Checking for NULL values
SELECT * FROM table_name WHERE column IS NULL;

19. What is a self-join?

Answer: A self-join is a join where a table is joined with itself. It’s used to combine rows based on a related column within the same table.

-- Example: Self-join
SELECT t1.column, t2.column
FROM table_name t1, table_name t2
WHERE t1.related_column = t2.related_column;

20. How do you find the second highest value in a column?

Answer: Use a subquery with LIMIT and OFFSET to find the second highest value.

-- Example: Finding the second highest value
SELECT MAX(column) FROM table_name
WHERE column < (SELECT MAX(column) FROM table_name);

21. What is a subquery?

Answer: A subquery is a query nested within another query. It can be used to retrieve data that will be used by the main query.

-- Example: Using a subquery
SELECT column1
FROM table_name
WHERE column2 = (SELECT MAX(column2) FROM table_name);

22. What is the difference between HAVING and WHERE?

Answer: WHERE is used to filter rows before the grouping, while HAVING is used to filter groups after the grouping.

-- Example: Using HAVING
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;

23. How do you perform a case-insensitive search?

Answer: Use the LOWER() or UPPER() functions to convert the column and search term to lowercase or uppercase.

-- Example: Case-insensitive search
SELECT * FROM table_name WHERE LOWER(column) = 'search_term';

24. What is a primary key?

Answer: A primary key is a unique identifier for a record in a table. It ensures each record is uniquely identifiable.

-- Example: Creating a primary key
CREATE TABLE table_name (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

25. How do you calculate the average of a column?

Answer: Use the AVG() function to calculate the average of a numeric column.

-- Example: Calculating the average
SELECT AVG(column) FROM table_name;

26. What is a foreign key?

Answer: A foreign key is a field in a table that refers to the primary key in another table. It establishes a relationship between two tables.

-- Example: Creating a foreign key
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

27. How do you retrieve the current date in SQL?

Answer: Use the GETDATE() or CURRENT_DATE() function to get the current date.

-- Example: Getting the current date
SELECT GETDATE();

28. What is a self-contained subquery?

Answer: A self-contained subquery doesn’t rely on the outer query for its values.

-- Example: Self-contained subquery
SELECT column1
FROM table_name
WHERE column1 > (SELECT AVG(column1) FROM table_name);

29. What is a common table expression (CTE)?

Answer: A CTE is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

-- Example: Using a CTE
WITH cte_name (column1, column2) AS (
  SELECT column1, column2 FROM table_name
)
SELECT * FROM cte_name;

30. How do you find duplicate records in a table?

Answer: Use the GROUP BY and HAVING clause to find duplicate records.

-- Example: Finding duplicates
SELECT column, COUNT(*) FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;

31. How do you find the second highest salary in a table?

Answer: Use the LIMIT and OFFSET clauses to retrieve the second highest salary.

-- Example: Finding the second highest salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

32. What is a self-join?

Answer: 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 in the same table.

-- Example: Self-join
SELECT e1.name, e2.name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

33. How do you get the nth highest salary from a table?

Answer: Use a combination of LIMIT and OFFSET to get the nth highest salary.

-- Example: Getting the 3rd highest salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

34. What is a correlated subquery?

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

-- Example: Correlated subquery
SELECT column1
FROM table1 t1
WHERE column1 > (SELECT AVG(column2) FROM table2 WHERE t1.id = table2.id);

35. How do you concatenate two strings in SQL?

Answer: Use the CONCAT() function to concatenate strings.

-- Example: Concatenating strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

36. What is the difference between UNION and UNION ALL?

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

-- Example: Using UNION
SELECT column1 FROM table1
UNION
SELECT column2 FROM table2;

37. How do you find the length of a string in SQL?

Answer: Use the LEN() or LENGTH() function to find the length of a string.

-- Example: Finding the length of a string
SELECT LEN(column1) FROM table1;

38. What is a trigger in SQL?

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

-- Example: Creating a trigger
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
  -- Trigger logic here
END;

39. How do you update a record in SQL?

Answer: Use the UPDATE statement to modify existing records.

-- Example: Updating a record
UPDATE table_name
SET column1 = 'new_value'
WHERE condition;

40. What is a view in SQL?

Answer: A view is a virtual table based on the result of a SELECT query. It doesn’t store the data itself but provides a way to represent it.

-- Example: Creating a view
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

41. How do you delete a record in SQL?

Answer: Use the DELETE statement to remove records from a table.

-- Example: Deleting a record
DELETE FROM table_name
WHERE condition;

42. What is a primary key?

Answer: A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified.

-- Example: Creating a table with a primary key
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

43. What is a foreign key?

Answer: A foreign key is a field in one table that refers to the primary key in another table. It establishes a relationship between the two tables.

-- Example: Creating a table with a foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

44. How do you find the highest value in a column?

Answer: Use the MAX() function to find the highest value in a column.

-- Example: Finding the highest salary
SELECT MAX(salary) FROM employees;

45. What is a subquery?

Answer: A subquery is a query within another query. It can be used to retrieve data based on the result of an inner query.

-- Example: Using a subquery
SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

46. What is a stored procedure?

Answer: A stored procedure is a precompiled set of SQL statements that can be stored and executed in a database.

-- Example: Creating a stored procedure
CREATE PROCEDURE get_employee_details (IN employee_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = employee_id;
END;

47. How do you find the average value in a column?

Answer: Use the AVG() function to find the average value in a column.

-- Example: Finding the average salary
SELECT AVG(salary) FROM employees;

48. What is the purpose of the GROUP BY clause?

Answer: The GROUP BY clause is used to group rows with the same values into summary rows.

-- Example: Using GROUP BY
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

49. How do you perform a case-insensitive search in SQL?

Answer: Use the LOWER() or UPPER() function to convert both the search term and the column data to lowercase or uppercase.

-- Example: Case-insensitive search
SELECT * FROM employees WHERE LOWER(first_name) = 'john';

50. What is a self-contained subquery?

Answer: A self-contained subquery is a subquery that doesn’t depend on the outer query. It can be executed independently.

-- Example: Self-contained subquery
SELECT column1
FROM table1
WHERE column2 > (SELECT AVG(column2) FROM table2);

51. What is a self-join in SQL?

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

-- Example: Self-join
SELECT e1.first_name, e2.first_name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;

52. Explain the difference between INNER JOIN and LEFT JOIN.

Answer: An INNER JOIN returns only the rows where there is a match in both tables, while a LEFT JOIN returns all rows from the left table and matching rows from the right table. If there’s no match, NULL values are returned for the right table.

-- Example: INNER JOIN
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

-- Example: LEFT JOIN
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

53. How do you add a new column to an existing table?

Answer: Use the ALTER TABLE statement to add a new column to an existing table.

-- Example: Adding a new column
ALTER TABLE employees
ADD email VARCHAR(100);

54. Explain the purpose of the HAVING clause.

Answer: The HAVING clause is used with the GROUP BY clause to filter the results of a group based on a specified condition. It operates on grouped rows, similar to the WHERE clause which operates on individual rows.

-- Example: Using HAVING
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

55. What is normalization in database design?

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

-- Example: Normalizing a database
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

56. What is a view in SQL?

Answer: A view is a virtual table created by a query. It doesn’t store data itself but provides a way to access and manipulate data from one or more tables.

-- Example: Creating a view
CREATE VIEW employee_details AS
SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

57. Explain the concept of indexing in SQL.

Answer: Indexing is a database optimization technique that improves the speed of data retrieval operations on database tables. It creates a data structure that allows for faster searching of rows based on indexed columns.

-- Example: Creating an index
CREATE INDEX idx_employee_id ON employees (employee_id);

58. What is a SQL injection and how can it be prevented?

Answer: SQL injection is a security vulnerability where an attacker can manipulate SQL queries by injecting malicious SQL code. To prevent it, use prepared statements or parameterized queries to sanitize user input.

-- Example: Using prepared statements in PHP
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->bindParam(':username', $username);
$stmt->execute();

59. How do you find the nth highest salary in a table?

Answer: You can find the nth highest salary by using the LIMIT clause with an OFFSET value.

-- Example: Finding the 2nd highest salary
SELECT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

60. What is a transaction in SQL?

Answer: A transaction is a sequence of one or more SQL statements that are treated as a single unit of work. It ensures that all the statements are executed successfully or none of them are, maintaining data integrity.

-- Example: Starting a transaction
START TRANSACTION;
-- SQL statements
COMMIT; -- or ROLLBACK; if an error occurs

Certainly! Here are more SQL interview questions along with their answers and code snippets:


61. What is a trigger in SQL?

Answer: A trigger is a set of instructions that are automatically executed (“triggered”) in response to certain events on a particular table or view. Triggers can be used to enforce business rules, perform complex calculations, or log changes.

-- Example: Creating a trigger
CREATE TRIGGER log_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_log (employee_id, old_salary, new_salary)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary);
END;

62. Explain the difference between UNION and UNION ALL.

Answer: UNION and UNION ALL are used to combine the result sets of two or more SELECT queries. The key difference is that UNION eliminates duplicate rows, while UNION ALL includes all rows, including duplicates.

-- Example: Using UNION
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- Example: Using UNION ALL
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

63. How do you find the number of rows in a table?

Answer: Use the COUNT function to find the number of rows in a table.

-- Example: Counting rows
SELECT COUNT(*) FROM employees;

64. What is the purpose of the CASE statement in SQL?

Answer: The CASE statement allows for conditional logic within a SQL query. It evaluates a set of conditions and returns a result based on the first condition that is true.

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

65. How can you find the maximum value in a column?

Answer: Use the MAX function to find the maximum value in a column.

-- Example: Finding the maximum salary
SELECT MAX(salary) FROM employees;

66. 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 and cannot have a NULL value.

-- Example: Creating a table with a primary key
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

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

Answer: A clustered index determines the physical order of data in a table, and there can only be one clustered index per table. A non-clustered index is a separate structure from the table and contains a sorted list of references to the table’s rows.

-- Example: Creating a clustered index
CREATE CLUSTERED INDEX idx_lastname ON employees(last_name);

68. What is a subquery in SQL?

Answer: A subquery is a query embedded within another query. It can be used to retrieve data that will be used as a condition or value in the main query.

-- Example: Using a subquery
SELECT * FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

69. How do you calculate the average of a column?

Answer: Use the AVG function to calculate the average of a column.

-- Example: Calculating the average salary
SELECT AVG(salary) FROM employees;

70. What is a self-contained subquery?

Answer: A self-contained subquery is a subquery that can run independently of the outer query. It doesn’t rely on any columns from the outer query for its execution.

-- Example: Self-contained subquery
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

71. What is a correlated subquery?

Answer: A correlated subquery is a subquery that refers to one or more columns in the outer query. It executes once for every row processed by the outer query.

-- Example: Correlated subquery
SELECT employee_id, first_name, salary
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

72. Explain the purpose of the HAVING clause in SQL.

Answer: The HAVING clause is used in conjunction with the GROUP BY clause to filter rows returned by a GROUP BY query based on a condition. It operates on aggregated data.

-- Example: Using HAVING
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

73. What is a view in SQL?

Answer: 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 data in a customized manner.

-- Example: Creating a view
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > 70000;

74. How can you remove duplicates from a result set?

Answer: Use the DISTINCT keyword to remove duplicates from a result set.

-- Example: Removing duplicates
SELECT DISTINCT department_id FROM employees;

75. Explain the purpose of the GROUP BY clause in SQL.

Answer: The GROUP BY clause is used to group rows with identical data into summary rows. It is often used with aggregate functions like COUNT, SUM, AVG, etc.

-- Example: Using GROUP BY
SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id;

76. What is a self-join in SQL?

Answer: A self-join is a join where a table is joined with itself. It is used to combine rows with related data.

-- Example: Self-join
SELECT e1.employee_id, e1.first_name, e2.first_name as manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

77. How do you find the second highest salary in a table?

Answer: Use the MAX function along with a subquery to find the second highest salary.

-- Example: Finding second highest salary
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

78. What is a stored procedure in SQL?

Answer: A stored procedure is a set of SQL statements that are stored and can be reused. It enhances performance and reduces network traffic.

-- Example: Creating a stored procedure
CREATE PROCEDURE GetEmployeeDetails AS
BEGIN
    SELECT * FROM employees;
END;

79. How can you find the nth highest salary in a table?

Answer: Use a subquery with LIMIT or OFFSET to find the nth highest salary.

-- Example: Finding the third highest salary
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

80. What is a transaction in SQL?

Answer: A transaction is a series of operations performed as a single unit. It either completes fully or leaves the database in a consistent state.

-- Example: Starting a transaction
BEGIN TRANSACTION;
-- SQL operations
COMMIT;

81. What is a trigger in SQL?

Answer: A trigger is a special type of stored procedure that is automatically executed (or fired) when certain events occur in a database, such as before or after an insert, update, or delete operation.

-- Example: Creating a trigger
CREATE TRIGGER UpdateEmployeeSalary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

82. Explain the difference between INNER JOIN and OUTER JOIN.

Answer:

  • INNER JOIN: Returns only the matching rows between two tables based on the specified condition.
  • OUTER JOIN: Returns all the rows from one table and the matched rows from the other, filling in the missing values with NULLs for non-matching rows.
-- Example: INNER JOIN
SELECT employees.employee_id, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
-- Example: LEFT OUTER JOIN
SELECT employees.employee_id, departments.department_name
FROM employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id;

83. What is a subquery in SQL?

Answer: A subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.

-- Example: Subquery in WHERE clause
SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

84. How do you find the number of rows in a table?

Answer: Use the COUNT function to find the number of rows in a table.

-- Example: Counting rows
SELECT COUNT(*) FROM employees;

85. Explain the concept of normalization in databases.

Answer: Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves breaking down large tables into smaller, related tables.

-- Example: Creating normalized tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

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

86. What is a primary key in a database?

Answer: A primary key is a unique identifier for a record in a table. It ensures that each record can be uniquely identified.

-- Example: Creating a table with a primary key
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

87. How can you update data in a table?

Answer: Use the UPDATE statement to modify existing data in a table.

-- Example: Updating data
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

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

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are reliable and consistent.

  • Atomicity: Transactions are all or nothing.
  • Consistency: Transactions take the database from one consistent state to another.
  • Isolation: Transactions operate independently of each other.
  • Durability: Once a transaction is committed, the changes are permanent.

89. What is a composite key in a database?

Answer: A composite key is a key that consists of more than one attribute to uniquely identify a record in a table. It is created by combining multiple columns.

-- Example: Creating a table with a composite key
CREATE TABLE sales (
    product_id INT,
    order_id INT,
    PRIMARY KEY (product_id, order_id)
);

90. What is a self-join in SQL?

Answer: A self-join is a type of join where a table is joined with itself. It is used to combine rows within the same table based on a related column.

-- Example: Self-join
SELECT e1.employee_id, e1.first_name, e2.first_name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

91. What is a view in SQL?

Answer: A view is a virtual table that is based on the result of a SELECT query. It does not store data but provides a way to represent complex queries as if they were tables.

-- Example: Creating a view
CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 50000;

92. Explain the purpose of the GROUP BY clause in SQL.

Answer: The GROUP BY clause is used to group rows that have the same values into summary rows. It is often used with aggregate functions like COUNT, SUM, AVG, etc.

-- Example: Using GROUP BY
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

93. What is a stored procedure in SQL?

Answer: A stored procedure is a pre-compiled SQL code that can be stored in the database. It can be called multiple times, making it more efficient than executing individual SQL queries.

-- Example: Creating a stored procedure
CREATE PROCEDURE GetEmployeeDetails
    @employee_id INT
AS
BEGIN
    SELECT * FROM employees WHERE employee_id = @employee_id;
END;

94. Explain the difference between DELETE and TRUNCATE in SQL.

Answer:

  • DELETE: Removes specific rows from a table based on a condition. It can be rolled back.
  • TRUNCATE: Removes all rows from a table and resets auto-incremented columns. It cannot be rolled back.
-- Example: Using DELETE
DELETE FROM employees WHERE department_id = 30;

-- Example: Using TRUNCATE
TRUNCATE TABLE employees;

95. What is a cursor in SQL?

Answer: A cursor is a database object used to retrieve data from a result set one row at a time. It provides more control over the processing of data.

-- Example: Declaring and using a cursor
DECLARE emp_cursor CURSOR FOR
SELECT * FROM employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor;
-- Process the data
CLOSE emp_cursor;

96. Explain the concept of an index in SQL.

Answer: 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.

-- Example: Creating an index
CREATE INDEX idx_last_name ON employees(last_name);

97. What is a trigger in SQL?

Answer: A trigger is a special type of stored procedure that is automatically executed (or fired) in response to certain events, such as before or after an insert, update, or delete operation.

-- Example: Creating a trigger
CREATE TRIGGER update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

98. How do you handle NULL values in SQL?

Answer: Use the IS NULL and IS NOT NULL operators to check for NULL values in SQL queries.

-- Example: Finding NULL values
SELECT * FROM employees WHERE manager_id IS NULL;

99. Explain the concept of a foreign key in SQL.

Answer: A foreign key is a field or combination of fields that uniquely identifies a record in another table. It establishes a relationship between two tables.

-- Example: Creating a foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

100. What is a common table expression (CTE) in SQL?

Answer: A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It simplifies complex queries and makes them more readable.

-- Example: Using a CTE
WITH HighSalaryEmployees AS (
    SELECT * FROM employees WHERE salary > 60000
)
SELECT * FROM HighSalaryEmployees;