Mastering SQL 100 Essential Interview Questions and Answers

1. Basic SQL Queries

1. What is SQL?

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

2. What are the different types of SQL commands?

   • DDL (Data Definition Language): Commands like CREATE, ALTER, DROP.
   • DML (Data Manipulation Language): Commands like SELECT, INSERT, UPDATE, DELETE.
   • DCL (Data Control Language): Commands like GRANT, REVOKE.
   • TCL (Transaction Control Language): Commands like COMMIT, ROLLBACK.

3. What is a primary key?

   • A primary key is a unique identifier for a record in a table. It cannot contain NULL values.

4. What is a foreign key?

   • A foreign key is a field in one table that uniquely identifies a row of another table. It establishes a relationship between the two tables.

5. What is normalization?

   • Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

6. What are the different types of joins?

   • INNER JOIN: Returns records with matching values in both tables.
   • LEFT JOIN: Returns all records from the left table and matched records from the right table.
   • RIGHT JOIN: Returns all records from the right table and matched records from the left table.
   • FULL JOIN: Returns all records when there is a match in either left or right table.

7. What is a subquery?

   • A subquery is a query nested inside another SQL query. It can return a single value or a set of values.

8. What is a view?

   • A view is a virtual table based on the result of a SQL query. It does not store data physically.

9. What is an index?

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

10. What is a stored procedure?

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

2. Intermediate SQL Concepts

11. What is a trigger?

    • A trigger is a special type of stored procedure that automatically runs when a specific event occurs in the database.

12. Explain ACID properties.

    • Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted.
    • Consistency: Ensures that a transaction brings the database from one valid state to another.
    • Isolation: Ensures that transactions are executed in isolation from each other.
    • Durability: Ensures that once a transaction has been committed, it will remain so even in the event of a system failure.

13. What are aggregate functions?

    • Aggregate functions perform calculations on multiple values and return a single value. Examples include COUNT(), SUM(), AVG(), MAX(), and MIN().

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

    • UNION combines the result sets of two or more SELECT statements and removes duplicates, while UNION ALL includes all duplicates.

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

    • A CTE is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

16. What is the difference between a clustered index and a non-clustered index?

    • A clustered index determines the physical order of data in a table, while a non-clustered index is a separate structure that points to the physical data.

17. How do you delete duplicate rows in a table?

    • You can delete duplicates by using a Common Table Expression (CTE) with the ROW_NUMBER() function to identify duplicates and then delete them.

18. What is a schema?

    • A schema is a collection of database objects, including tables, views, and indexes, associated with a particular database user.

19. What are stored functions?

    • Stored functions are similar to stored procedures but return a single value and can be used in SQL expressions.

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

    • The GROUP BY clause is used to arrange identical data into groups. It is often used with aggregate functions.

3. Advanced SQL Concepts

21. What are window functions?

    • Window functions perform calculations across a set of table rows related to the current row, providing insights like running totals.

22. Explain the difference between a correlated subquery and a non-correlated subquery.

    • A correlated subquery depends on the outer query for its values, while a non-correlated subquery can be executed independently.

23. What is the purpose of the HAVING clause?

    • The HAVING clause is used to filter records after the GROUP BY operation has been performed, unlike WHERE, which filters records before aggregation.

24. How can you optimize SQL queries?

    • Optimizing queries can involve creating indexes, avoiding SELECT *, using WHERE clauses to filter results, and analyzing execution plans.

25. What are the different types of constraints?

    • NOT NULL: Ensures a column cannot have a NULL value.
    • UNIQUE: Ensures all values in a column are different.
    • CHECK: Ensures that all values in a column satisfy a specific condition.
    • DEFAULT: Provides a default value for a column when none is specified.

26. What is a transaction?

    • A transaction is a sequence of one or more SQL operations that are executed as a single unit of work.

27. What is the purpose of the EXPLAIN statement?

    • The EXPLAIN statement provides information about how SQL statements are executed, including how tables are joined and the use of indexes.

28. What are materialized views?

    • Materialized views store the results of a query physically, improving performance for complex queries that do not change often.

29. What is SQL injection?

    • SQL injection is a security vulnerability that allows an attacker to interfere with the queries made to a database.

30. How can you prevent SQL injection?

    • Use prepared statements, parameterized queries, and stored procedures to safeguard against SQL injection attacks.

4. SQL Functions and Operators

31. What are scalar functions?

    • Scalar functions return a single value based on the input value(s). Examples include UPPER(), LOWER(), and LEN().

32. What is a date function?

    • Date functions manipulate date values. Examples include GETDATE(), DATEDIFF(), and DATEADD().

33. What is the COALESCE function?

    • COALESCE returns the first non-null value in a list of expressions.

34. What is the difference between CHAR and VARCHAR?

    • CHAR is a fixed-length data type, while VARCHAR is variable-length and can store strings of varying lengths.

35. How do you find the current database user?

    • You can use the SQL command SELECT USER(); or SELECT CURRENT_USER(); depending on the SQL dialect.

36. What are string functions in SQL?

    • String functions manipulate string values, including CONCAT(), SUBSTRING(), and TRIM().

37. What is the RANK() function?

    • RANK() is a window function that assigns a rank to each row within a partition of a result set, with gaps in ranking for ties.

38. What is a CASE statement?

    • The CASE statement provides conditional logic in SQL queries, allowing different outputs based on certain conditions.

39. How can you convert data types in SQL?

    • You can use the CAST() or CONVERT() functions to change data types in SQL.

40. What is the purpose of the EXISTS operator?

    • The EXISTS operator checks for the existence of rows returned by a subquery, returning true if rows exist.

5. Database Design and Optimization

41. What is denormalization?

    • Denormalization is the process of intentionally introducing redundancy into a database design to improve read performance.

42. What is a star schema?

    • A star schema is a type of database schema that consists of a central fact table surrounded by dimension tables.

43. What is a snowflake schema?

    • A snowflake schema is a more complex version of a star schema, where dimension tables are normalized into multiple related tables.

44. How do you design a database for a new application?

    • Start by gathering requirements, identify entities and relationships, create an ER diagram, define the schema, and normalize the database.

45. What is the purpose of indexing?

    • Indexing speeds up the retrieval of rows from a database table, improving query performance.

46. How do you identify performance bottlenecks in SQL queries?

    • Analyze execution plans, check for long-running queries, monitor system resources, and look for missing indexes.

47. What is data integrity?

    • Data integrity ensures the accuracy and consistency of data over its lifecycle through constraints and validation rules.

48. What is referential integrity?

    • Referential integrity ensures that relationships between tables remain consistent, preventing orphaned records.

49. How do you implement cascading updates/deletes?

    • You can implement cascading updates or deletes using foreign key constraints with the ON UPDATE CASCADE or ON DELETE CASCADE options.

50. What are the advantages of using stored procedures?

    • Stored procedures encapsulate business logic, enhance security, reduce network traffic, and improve performance.

6. SQL in Practice

51. Write a query to find the second highest salary.

    SELECT MAX(salary) 
    FROM employees 
    WHERE salary < (SELECT MAX(salary) FROM employees);
    

52. How do you count the number of rows in a table?

    SELECT COUNT(*) FROM table_name;
    

53. Write a SQL query to find all employees who joined after January 1, 2020.

    SELECT * FROM employees 
    WHERE join_date > '2020-01-01';
    

54. How can you retrieve unique records from a table?

    SELECT DISTINCT column_name FROM table_name;
    

55. Write a query to concatenate first and last names.

    SELECT CONCAT(first_name, ' ', last_name) AS full_name 
    FROM employees;
    

56. How do you update a specific record in a table?

    UPDATE employees 
    SET salary = 60000 
    WHERE employee_id = 1;
    

57. Write a query to delete employees who have not worked for more than a year.

    DELETE FROM employees 
    WHERE join_date < DATEADD(YEAR, -1, GETDATE());
    

58. How can you retrieve the maximum salary from a table?

    SELECT MAX(salary) FROM employees;
    

59. Write a SQL query to retrieve records with a specific condition.

    SELECT * FROM employees 
    WHERE department = 'Sales' AND salary > 50000;
    

60. How do you perform a self-join?

    SELECT a.employee_id, a.first_name, b.first_name AS manager_name 
    FROM employees a 
    JOIN employees b ON a.manager_id = b.employee_id;
    

7. Advanced SQL Use Cases

61. What is the purpose of the GROUP_CONCAT function?

    • GROUP_CONCAT concatenates values from multiple rows into a single string, typically used in aggregate queries.

62. How do you implement pagination in SQL?

    SELECT * FROM employees 
    ORDER BY employee_id 
    OFFSET 10 ROWS 
    FETCH NEXT 10 ROWS ONLY;
    

63. What is a temporary table?

    • A temporary table is a table that is created and used temporarily within a session, automatically dropped at the end of the session.

64. How do you create a trigger?

    CREATE TRIGGER trigger_name 
    AFTER INSERT ON table_name 
    FOR EACH ROW 
    BEGIN
        -- Trigger logic here
    END;
    

65. What is the purpose of the TRANSACTION statement?

    • The TRANSACTION statement groups a set of SQL commands to ensure they are executed as a single unit, maintaining data integrity.

66. How do you create a recursive CTE?

    WITH RECURSIVE cte_name AS (
        SELECT initial_value FROM table_name
        UNION ALL
        SELECT column FROM cte_name WHERE condition
    )
    SELECT * FROM cte_name;
    

67. Write a SQL query to find the total sales per product.

    SELECT product_id, SUM(sales_amount) AS total_sales 
    FROM sales 
    GROUP BY product_id;
    

68. What is the difference between TRUNCATE and DELETE?

    • TRUNCATE removes all rows from a table without logging individual row deletions, while DELETE logs each row deletion and can include a WHERE clause.

69. How can you create a backup of a database?

    • You can use the BACKUP DATABASE command in SQL Server or mysqldump in MySQL.

70. What is the purpose of the SET NOCOUNT ON statement?

    • SET NOCOUNT ON prevents the message indicating the number of affected rows from being returned, which can improve performance in stored procedures.

8. SQL Best Practices

71. What are the best practices for writing SQL queries?

    • Use descriptive names, avoid SELECT *, use joins instead of subqueries where possible, and always test queries for performance.

72. How do you ensure data security in SQL?

    • Implement user roles and permissions, use parameterized queries, and encrypt sensitive data.

73. What is the significance of database backups?

    • Database backups are essential for data recovery in case of corruption, data loss, or hardware failures.

74. What is the difference between a logical and physical data model?

    • A logical data model outlines the structure of the data without concern for how it will be implemented physically, while a physical data model includes details about how data is stored.

75. What is data warehousing?

    • Data warehousing is the process of collecting and managing data from various sources to provide meaningful business insights.

76. How do you handle error management in SQL?

    • Use TRY...CATCH blocks in T-SQL to handle exceptions and log errors.

77. What are some common performance tuning techniques?

    • Optimize queries, use proper indexing, avoid unnecessary columns in SELECT statements, and analyze execution plans.

78. What is database partitioning?

    • Database partitioning is the process of dividing a database into smaller, more manageable pieces, improving performance and maintainability.

79. How can you manage large datasets in SQL?

    • Use indexing, partitioning, and archiving older data to improve performance with large datasets.

80. What is a database migration?

    • Database migration involves moving data from one database to another or upgrading to a newer version of a database system.

9. SQL and Application Integration

81. How do you connect to a database from an application?

    • Use a database driver or ORM (Object-Relational Mapping) framework in the application language to establish a connection.

82. What is ORM?

    • ORM (Object-Relational Mapping) is a programming technique that converts data between incompatible type systems in object-oriented programming languages.

83. How can you secure database connections in an application?

    • Use secure connection strings, implement SSL/TLS for connections, and limit user privileges.

84. What is the role of an API in database interactions?

    • An API (Application Programming Interface) allows applications to communicate with the database, executing SQL queries and retrieving results.

85. How do you manage database schema changes in applications?

    • Use migration tools that track and apply schema changes incrementally and manage version control for database changes.

86. What are some common database design patterns?

    • Common patterns include repository pattern, unit of work pattern, and data mapper pattern.

87. How do you handle concurrency in SQL databases?

    • Implement locking mechanisms, isolation levels, and optimistic concurrency control to manage concurrent access to data.

88. What is the significance of data types in database design?

    • Choosing appropriate data types ensures optimal storage efficiency, data integrity, and performance.

89. How can you handle large result sets in an application?

    • Use pagination techniques, lazy loading, or caching to manage large datasets efficiently.

90. What is the role of a database administrator (DBA)?

    • A DBA is responsible for managing, backing up, and ensuring the availability and performance of the database systems.

10. SQL in Industry Applications

91. How is SQL used in data analysis?

    • SQL is used to query large datasets, aggregate data, and generate reports for insights and decision-making.

92. What role does SQL play in business intelligence?

    • SQL is used to extract, transform, and load (ETL) data into data warehouses, enabling analytics and reporting.

93. How can SQL be used in machine learning?

    • SQL can be used to preprocess data, query training datasets, and integrate with machine learning platforms for model training.

94. What is the significance of data governance in SQL databases?

    • Data governance ensures data quality, compliance, and management practices are in place for data handling.

95. How does SQL interact with cloud databases?

    • SQL can be used to query and manage cloud-based databases like AWS RDS, Azure SQL Database, and Google Cloud SQL.

96. What is the difference between OLTP and OLAP?

    • OLTP (Online Transaction Processing) systems are optimized for transaction-oriented applications, while OLAP (Online Analytical Processing) systems are designed for complex queries and data analysis.

97. What are data lakes, and how does SQL fit in?

    • Data lakes store vast amounts of raw data in its native format. SQL can be used to query structured data within a data lake.

98. How can SQL be utilized in e-commerce applications?

    • SQL manages product catalogs, customer data, orders, and transactions in e-commerce systems.

99. What is the impact of NoSQL on SQL databases?

    • NoSQL databases provide flexibility for unstructured data and scalability, leading traditional SQL databases to adapt and coexist.

100. How do you see the future of SQL in data management? - SQL will continue to evolve with support for new data types, integration with big data technologies, and enhanced capabilities for cloud-native environments.