SQL Interview Questions & Answers

Basic SQL Concepts:

1. What is SQL?

   • A: SQL (Structured Query Language) is a standard programming language for managing and manipulating relational databases.

2. Explain the difference between SQL and MySQL.

   • A: SQL is a language, while MySQL is one of several database management systems that support SQL. MySQL is an open-source relational database management system.

3. What are the different types of SQL statements?

   • A: SQL statements include Data Query Language (DQL), Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

4. What is a primary key?

   • A: A primary key is a unique identifier for a record in a database table. It must contain unique values and cannot have NULL values.

5. Explain the purpose of the SELECT statement.

   • A: The SELECT statement is used to retrieve data from one or more database tables.

SQL Queries:

6. Write an SQL query to fetch all the columns from a table named "employees."

   • A: SELECT * FROM employees;

7. How do you retrieve distinct values from a column?

   • A: Use the DISTINCT keyword. Example: SELECT DISTINCT column_name FROM table_name;

8. Explain the difference between WHERE and HAVING clauses.

   • A: WHERE is used to filter rows before grouping, and HAVING is used to filter groups after grouping.

9. Write an SQL query to find the second highest salary from an "employees" table.

   • A: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

10. What is a subquery?

    • A: A subquery is a query nested inside another query, usually enclosed in parentheses.

Joins and Relationships:

11. Explain INNER JOIN.

    • A: INNER JOIN returns rows that have matching values in both tables based on the specified condition.

12. What is the difference between INNER JOIN and LEFT JOIN?

    • A: INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table and matching rows from the right table.

13. How do you perform a self-join?

    • A: Use an alias to reference the same table within the query. Example: SELECT e1.name, e2.name FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id;

14. Explain the concept of a foreign key.

    • A: A foreign key is a column that establishes a link between data in two tables. It creates a relationship between the tables.

15. What is a composite key?

    • A: A composite key is a key that consists of two or more columns to uniquely identify a record in a table.

Aggregate Functions:

16. List some aggregate functions in SQL.

    • A: COUNT, SUM, AVG, MAX, and MIN.

17. Write an SQL query to find the total number of rows in a table.

    • A: SELECT COUNT(*) FROM table_name;

18. How do you calculate the average value of a column?

    • A: SELECT AVG(column_name) FROM table_name;

19. Explain GROUP BY and its usage.

    • A: GROUP BY is used to group rows that have the same values in specified columns. It is often used with aggregate functions.

20. What is the purpose of the HAVING clause in conjunction with GROUP BY?

    • A: HAVING is used to filter the results of a GROUP BY clause based on a specified condition.

Data Modification:

21. Write an SQL query to insert a new record into a table.

    • A: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

22. How do you update data in a table?

    • A: UPDATE table_name SET column1 = value1 WHERE condition;

23. Explain the DELETE statement.

    • A: DELETE is used to remove rows from a table based on a specified condition.

24. What is a transaction in SQL?

    • A: A transaction is a sequence of one or more SQL statements executed as a single unit of work.

25. How do you ensure the atomicity of a transaction?

    • A: Use the COMMIT and ROLLBACK statements. If a transaction completes successfully, use COMMIT to make changes permanent; otherwise, use ROLLBACK to undo changes.

Indexing and Optimization:

26. What is an index in SQL?

    • A: An index is a database object that improves the speed of data retrieval operations on a database table.

27. Explain the difference between clustered and non-clustered indexes.

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

28. How do you optimize a SQL query?

    • A: Optimization techniques include using indexes, minimizing the use of wildcard characters, and avoiding SELECT *.

29. What is normalization, and why is it important?

    • A: Normalization is the process of organizing data to reduce redundancy and improve data integrity. It helps in avoiding data anomalies.

30. Write an SQL query to find duplicate records in a table.

    • A: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

Advanced SQL Concepts:

31. What is a stored procedure?

    • A: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit.

32. Explain the concept of a view in SQL.

    • A: A view is a virtual table based on the result of a SELECT statement. It does not store the data itself but provides a way to represent data stored in tables.

33. How do you use the UNION operator in SQL?

    • A: UNION is used to combine the results of two or more SELECT statements. It returns distinct rows.

34. What is the purpose of the COALESCE function?

    • A: COALESCE returns the first non-null expression among its arguments. It is often used to provide a default value.

35. Explain the concept of a trigger.

    • A: A trigger is a set of instructions that are automatically executed or fired in response to specified events on a particular table or view.

Security and Permissions:

36. How do you grant SELECT permission on a table to a user in SQL?

    • A: GRANT SELECT ON table_name TO user_name;

37. What is SQL injection, and how can it be prevented?

    • A: SQL injection is a code injection technique where an attacker can execute malicious SQL statements.

Prevention involves using parameterized queries and validating user inputs.

38. How can you encrypt sensitive data in a database?

    • A: Use encryption functions and algorithms provided by the database management system.

39. Explain the concept of role-based access control in SQL.

    • A: Role-based access control involves assigning permissions to roles, and then assigning roles to users. This simplifies the management of permissions.

40. How do you revoke permissions in SQL?

    • A: REVOKE permission_name ON table_name FROM user_name;

Database Design and Modeling:

41. What is the purpose of normalization in database design?

    • A: Normalization minimizes redundancy and dependency by organizing data into separate tables based on their relationships.

42. Explain the concept of denormalization.

    • A: Denormalization involves combining tables to reduce the number of joins and improve query performance. It sacrifices some level of normalization for performance gains.

43. What is an ER diagram, and how is it used in database design?

    • A: An Entity-Relationship (ER) diagram is a visual representation of the relationships between entities in a database. It helps in designing and understanding the structure of a database.

44. How do you create an index on a table column?

    • A: CREATE INDEX index_name ON table_name (column_name);

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

    • A: ACID stands for Atomicity, Consistency, Isolation, and Durability. It ensures that database transactions are reliable even in the event of system failures.

Advanced Querying:

46. Write an SQL query to find the nth highest salary from an "employees" table.

    • A: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT n-1,1;

47. How do you use the CASE statement in SQL?

    • A: The CASE statement is used for conditional logic in SQL. Example:

      SELECT column_name,
             CASE
               WHEN condition1 THEN 'Value1'
               WHEN condition2 THEN 'Value2'
               ELSE 'DefaultValue'
             END
      FROM table_name;
      

48. What is the purpose of the OFFSET and FETCH clauses in SQL?

    • A: OFFSET and FETCH are used for pagination. OFFSET specifies the number of rows to skip, and FETCH specifies the number of rows to return.

49. How can you perform a cross join in SQL?

    • A: Use the CROSS JOIN keyword. Example: SELECT * FROM table1 CROSS JOIN table2;

50. Write an SQL query to find the employees who have not been assigned any project.

    • A: SELECT * FROM employees WHERE employee_id NOT IN (SELECT employee_id FROM projects);