50 Latest SQL Interview Questions and Answers

Leave a Comment / News, SQL

Basic SQL questions

  1. What is SQL?
    SQL (Structured Query Language) is used for accessing and managing databases. It allows users to perform operations like data retrieval, insertion, update, and deletion.
  2. What are the different types of SQL statements?
    • DDL (Data Definition Language): CREATE, ALTER, DROP
    • DML (Data Manipulation Language): INSERT, UPDATE, DELETE
    • DQL (Data Query Language): SELECT
    • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT
    • DCL (Data Control Language): GRANT, REVOKE
  3. What is the difference between WHERE and HAVING clauses?
    • WHERE filters rows before grouping.
    • HAVING filters groups after grouping.
  4. What is a Primary Key?
    A Primary Key uniquely identifies each row in a table and cannot contain NULL.
  5. What is the difference between Primary Key and Unique Key?
    • Primary Key: Only one per table, cannot be NULL.
    • Unique Key: Multiple allowed, can have one NULL.

Intermediate SQL Questions

  1. What is a Foreign Key?
    A Foreign Key is a field in one table that links to the Primary Key in another table, ensuring referential integrity.
  2. What is the difference between INNER JOIN and OUTER JOIN?
    • INNER JOIN: Returns rows with matching values in both tables.
    • OUTER JOIN: Returns matching rows and non-matching rows from one or both tables (LEFT, RIGHT, or FULL).
  3. What is the use of the GROUP BY clause?
    It groups rows with the same values in specified columns and allows aggregate functions (COUNT, SUM, etc.) to be applied.
  4. What is normalization?
    Normalization organizes data to reduce redundancy and dependency by dividing a database into smaller tables.
  5. What are SQL indexes, and why are they used?
    Indexes improve query performance by allowing faster data retrieval but can slow down INSERT and UPDATE operations.

Advanced SQL Questions

  1. What is a Common Table Expression (CTE)?
    A CTE is a temporary result set defined within an SQL statement using WITH and can be referenced multiple times.
  2. What is a Window Function in SQL?
    It performs calculations across rows related to the current row within a result set. Example: ROW_NUMBER(), RANK().
  3. What is the difference between RANK() and DENSE_RANK()?
    • RANK(): Leaves gaps in ranking when values are tied.
    • DENSE_RANK(): No gaps in ranking.
  4. Explain the difference between TRUNCATE and DELETE.
    • DELETE: Removes specific rows, can be rolled back.
    • TRUNCATE: Removes all rows, faster, cannot be rolled back.
  5. What is a stored procedure?
    A stored procedure is a precompiled set of SQL statements that can be executed with a call.

Performance Optimization Questions

  1. What are database partitions?
    Partitioning divides a table into smaller pieces for faster query processing.
  2. What is a clustered index?
    A clustered index sorts and stores data rows in the table based on the indexed column.
  3. How do you optimize a slow query?
    • Use indexes.
    • Avoid SELECT *.
    • Use proper WHERE clauses.
    • Analyze execution plans.
  4. What is query execution plan?
    It is a visual representation of the steps taken by the database to execute a query.
  5. What are materialized views?
    Materialized views store query results physically for faster access compared to regular views.

Real-World Scenario Questions

  1. How would you find duplicate records in a table?sqlCopy codeSELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;
  2. How can you find the second-highest salary in a table?sqlCopy codeSELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
  3. What is the difference between a view and a table?
    • View: A virtual table based on a query.
    • Table: A physical structure storing data.
  4. How do you handle NULL values in SQL?
    Use IS NULL, IS NOT NULL, or functions like COALESCE() to replace NULL.
  5. How do you implement transactions in SQL?sqlCopy codeBEGIN TRANSACTION; -- SQL operations COMMIT;

Latest SQL Features and Trends (2024 Focus)

  1. What are JSON functions in SQL?
    Functions like JSON_VALUE() and JSON_ARRAYAGG() help handle JSON data.
  2. What are Recursive CTEs?
    Recursive CTEs repeatedly reference themselves to process hierarchical or tree-structured data.
  3. What are temporal tables?
    Temporal tables track data changes over time with historical data storage.
  4. What is the difference between SQL and NoSQL databases?
    • SQL: Structured, relational, uses schemas.
    • NoSQL: Flexible, unstructured, document-based or key-value.
  5. What is database sharding?
    Sharding distributes data across multiple servers to improve scalability and performance.

Miscellaneous

  1. How do you ensure data integrity in SQL?
    Use constraints like PRIMARY KEY, FOREIGN KEY, CHECK, and UNIQUE.
  2. What is the difference between UNION and UNION ALL?
    • UNION: Removes duplicates.
    • UNION ALL: Includes duplicates.
  3. What is the use of the CASE statement in SQL?
    It provides conditional logic in queries.
  4. What is ACID in databases?
    • Atomicity, Consistency, Isolation, Durability ensure reliable transactions.
  5. What is the purpose of the MERGE statement?
    Combines INSERT, UPDATE, and DELETE operations in one statement.

Common Tricky Questions

  1. Can we create a table without a primary key?
    Yes, but it may lead to data integrity issues.
  2. What is the difference between VARCHAR and CHAR?
    • CHAR: Fixed-length.
    • VARCHAR: Variable-length.
  3. What is the purpose of the CHECK constraint?
    Ensures data meets specific criteria before insertion or update.
  4. How do you drop a column from a table?sqlCopy codeALTER TABLE table_name DROP COLUMN column_name;
  5. What is the difference between EXISTS and IN?
    • EXISTS: Checks row existence, efficient with subqueries.
    • IN: Checks specific values, better for small lists.

Advanced Scenario-Based Questions

  1. How do you handle deadlocks in SQL?
    Analyze using tools like query execution plans and optimize locking mechanisms.
  2. How do you calculate running totals?sqlCopy codeSELECT column_name, SUM(column_name) OVER (ORDER BY column_name) AS running_total FROM table_name;
  3. What are surrogate keys?
    Keys generated programmatically to uniquely identify rows.
  4. How do you handle dynamic SQL?
    Use prepared statements or execute SQL strings programmatically.
  5. What is the purpose of CROSS APPLY?
    Combines results from table-valued functions with each row.

Latest Tools and Practices

  1. What are New SQL Databases?
    Databases combining SQL’s reliability with NoSQL scalability (e.g., CockroachDB, Google Spanner).
  2. How do you implement database version control?
    Use tools like Flyway or Liquibase for schema management.
  3. What are SQL triggers?
    Triggers automatically execute specified actions in response to events.
  4. What are lateral joins in SQL?
    Lateral joins allow subqueries to reference columns from preceding joins.
  5. What is the importance of big data in SQL?
    SQL integrates with big data tools (e.g., Apache Hive, SparkSQL) for analytics

Leave a Comment

Your email address will not be published. Required fields are marked *