SQL Interview Questions with Detailed Answers and Project Examples

SQL Basics

  1. What is SQL?
    Structured Query Language (SQL) is a language for managing and querying relational databases.
    Example: Used SQL to query customer data from PostgreSQL database to generate monthly sales reports.
  2. What are the different types of SQL commands?
    DML (SELECT, INSERT, UPDATE, DELETE), DDL (CREATE, ALTER, DROP), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK).
    Example: Created tables using DDL and inserted transactional data using DML in a finance project.
  3. What is a primary key?
    Unique identifier for each record in a table; ensures entity integrity.
    Example: Defined customer_id as primary key in customer table to prevent duplicates.
  4. What is a foreign key?
    A field in one table that references primary key in another, establishing relationships.
    Example: Linked orders table to customer table via foreign key customer_id for order tracking.
  5. How do you select unique records?
    Using DISTINCT keyword in SELECT statement.
    Example: SELECT DISTINCT country FROM customers; to list all countries once in a sales report.
  6. What is a JOIN?
    Combines rows from two or more tables based on related columns.
    Example: Used INNER JOIN between orders and customers to show order details with customer information.
  7. Explain INNER JOIN vs OUTER JOIN.
    INNER JOIN returns records with matching keys in both tables.
    OUTER JOIN returns all records from one (LEFT/RIGHT) or both (FULL) tables including unmatched rows.
    Example: Used LEFT JOIN to include customers without orders in a marketing campaign.
  8. What are aggregate functions in SQL?
    Functions like COUNT, SUM, AVG, MIN, MAX used to summarize data.
    Example: Calculated total sales using SUM(amount) grouped by region.
  9. What is GROUP BY?
    Groups rows with same values for aggregation.
    Example: Grouped sales by month to calculate monthly revenue.
  10. What is HAVING clause?
    Filters groups after aggregation (unlike WHERE which filters rows before grouping).
    Example: Retrieved customers with more than 10 orders by HAVING COUNT(order_id) > 10.

SQL Querying and Filtering

  1. How to filter records?
    Use WHERE clause with conditions (e.g., WHERE age > 30).
    Example: Filtered active customers who made purchases this year.
  2. Explain LIKE operator.
    Used for pattern matching with % (any sequence) and _ (single character).
    Example: WHERE name LIKE 'Jo%' to find names starting with ‘Jo’.
  3. What are subqueries?
    A query nested inside another query.
    Example: Used subquery in WHERE clause to find customers with orders exceeding average order amount.
  4. What are correlated subqueries?
    Subqueries dependent on outer query row using its values.
    Example: Found customers with orders exceeding their average order value by correlated subquery.
  5. Explain EXISTS keyword.
    Checks for existence of rows in a subquery, returns boolean.
    Example: Used EXISTS to check customers having at least one late payment.
  6. What is UNION vs UNION ALL?
    UNION merges and removes duplicates; UNION ALL merges including duplicates.
    Example: Combined historical and current year sales data with UNION ALL for full analysis.
  7. What are the different set operations?
    UNION, UNION ALL, INTERSECT, EXCEPT (or MINUS).
    Example: Used INTERSECT to find customers common to two campaigns.
  8. Explain CASE statement.
    Conditional expressions in SQL queries.
    Example: Categorized orders as ‘High’ or ‘Low’ value based on total_amount with CASE.
  9. What is the difference between WHERE and ON clause?
    WHERE filters after joins; ON clause determines join conditions.
    Example: Combined filtering in ON clause to optimize join for customers in active regions.
  10. How to limit records returned?
    Use LIMIT (MySQL/Postgres) or TOP (SQL Server).
    Example: Retrieved top 10 customers by revenue with ORDER BY revenue DESC LIMIT 10.

SQL Functions and Operations

  1. Explain string functions like CONCAT, SUBSTRING.
    CONCAT joins strings; SUBSTRING extracts part of string.
    Example: Extracted area code from phone numbers with SUBSTRING to group by region.
  2. What are date functions?
    Functions like NOW(), DATEADD(), DATEDIFF() manipulate dates.
    Example: Used DATEDIFF to calculate customer age from birthdate.
  3. Difference between TRUNCATE and DELETE?
    TRUNCATE removes all rows quickly with fewer logging, cannot be rolled back; DELETE removes with row-level logging and can be filtered.
    Example: Used DELETE to remove specific inactive customers; TRUNCATE to reset staging table before load.
  4. What is indexing?
    Database structure to speed up data retrieval.
    Example: Added index on customer_id to improve query performance on large tables.
  5. Explain transactions and ACID properties.
    Transactions are sets of SQL statements executed atomically ensuring Atomicity, Consistency, Isolation, Durability.
    Example: Used transactions to ensure order and payment records update consistently.

SQL Advanced and Optimization

  1. What are stored procedures?
    Predefined SQL code saved for reuse with parameters.
    Example: Created a stored procedure to calculate monthly sales commissions with input parameter for month.
  2. What are triggers?
    SQL code executed automatically on table events (INSERT, UPDATE, DELETE).
    Example: Trigger sending notification email when a new order is placed.
  3. Explain normalization and denormalization.
    Normalization structures data to reduce redundancy; denormalization introduces redundancy for query performance.
    Example: Normalized customer and order data for integrity; denormalized for reporting views to improve speed.
  4. What is indexing? Explain different types.
    Clustered index sorts data physically; non-clustered index is a separate sorted structure.
    Example: Clustered index on primary key and non-clustered indexes on frequently searched fields.
  5. How do you optimize SQL queries?
    Use EXPLAIN plans, indexes, avoid SELECT *, minimize joins, use appropriate data types.
    Example: Optimized slow report query by adding efficient indexes and rewriting subqueries.

SQL Joins & Relationships

  1. Explain different types of joins with examples.
    INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN.
    Example: Used LEFT JOIN to include all customers even without orders.
  2. What is a self join?
    Joining a table to itself for hierarchical or comparative queries.
    Example: Retrieved employees and their managers from single employee table using self join.
  3. What is a cross join?
    Cartesian product join returning every combination of rows.
    Example: Generated all possible product-color combinations using cross join.
  4. How do you find duplicates in a table?
    Use GROUP BY with HAVING COUNT(*) >1.
    Example: Detected users with duplicate emails to clean customer data.
  5. How to delete duplicates?
    Use CTE with ROW_NUMBER() partitioned by the duplicate columns, then delete rows with row_number >1.
    Example: Cleaned duplicate entries in contacts list maintaining latest record.

SQL Transactions & Concurrency

  1. What is a transaction in SQL?
    A unit of work performed with guarantee of completeness.
    Example: Wrapped insert and update queries in a transaction to keep order data consistent.
  2. Explain isolation levels.
    Read Uncommitted, Read Committed, Repeatable Read, Serializable determine visibility of uncommitted data.
    Example: Set Read Committed isolation to avoid dirty reads in banking app.
  3. What is locking?
    Mechanism to control simultaneous data access preventing conflicts.
    Example: Understood locking to solve deadlocks in multi-user online booking system.
  4. What are deadlocks? How to resolve?
    Deadlocks are situations when two sessions wait indefinitely for resources locked by each other.
    Example: Implemented retry logic and consistent locking order to handle deadlocks.
  5. What is optimistic vs pessimistic concurrency?
    Optimistic assumes conflicts are rare; pessimistic locks data during transaction.
    Example: Used optimistic concurrency control for web app to improve throughput with conflict detection.

SQL Subqueries & Set Operations

  1. What is a correlated subquery?
    A subquery referring to columns from the outer query.
    Example: Selected customers whose orders exceed average order amount using correlated subquery.
  2. What is the difference between subquery and join?
    Subquery returns single scalar or row result for condition; join merges tables horizontally.
    Example: Used join for product info in sales; subquery for filtering top customers.
  3. Explain UNION vs JOIN?
    UNION stacks results vertically; JOIN combines columns horizontally.
    Example: Combined two years of sales data with UNION; showed sales info with customer data via JOIN.
  4. Explain INTERSECT and EXCEPT set operations.
    INTERSECT returns common records; EXCEPT returns records from first query not in second.
    Example: Found customers in 2024 campaign but not 2023 using EXCEPT.
  5. What are window functions?
    Functions operating on partitions of data over a result set, e.g., ROW_NUMBER, RANK, SUM OVER.
    Example: Used ROW_NUMBER() to paginate query results and assign rankings by sales.

SQL Views and Security

  1. What are views in SQL?
    Virtual tables saving queries for reuse and abstraction.
    Example: Created view to simplify summary sales queries for analysts.
  2. What is materialized view?
    View storing physical data, refreshing periodically for performance.
    Example: Used materialized views to speed up company-wide sales dashboards.
  3. How do you secure SQL databases?
    Use roles, permissions, encryption, auditing.
    Example: Limited access to HR tables with role-based security removing SELECT permission for general users.
  4. What is SQL injection? How to prevent?
    Injection attacks through unsafe user inputs; prevent using parameterized queries and input validation.
    Example: Secured web app backend queries with prepared statements to avoid injection.
  5. Explain indexing and how to maintain indexes?
    Indexes speed queries but degrade on heavy writes and need rebuilding/reorganizing.
    Example: Scheduled maintenance jobs to rebuild indexes during off-hours for consistent performance.

SQL Performance and Tuning

  1. What is EXPLAIN plan?
    Displays how database executes queries to analyze performance bottlenecks.
    Example: Used EXPLAIN to identify full table scans and added needed indexes.
  2. What is a covering index?
    An index including all columns for the query to avoid hitting base table.
    Example: Created covering index on user_id and date for customer activity report.
  3. Explain table partitioning.
    Splitting large tables into smaller parts for manageable and faster access.
    Example: Partitioned sales data by year to speed historical queries.
  4. What is denormalization?
    Adding redundant data to speed queries at cost of update complexity.
    Example: Denormalized product category into sales table for faster reporting.
  5. What are temp tables?
    Temporary tables created during query execution or session lifetime.
    Example: Used temp tables in complex ETL scripts to stage intermediate query results.

SQL Functions and Operators

  1. What are aggregate functions? Give examples.
    Functions like COUNT(), AVG(), SUM(), MAX(), MIN().
    Example: Used COUNT() to find total orders per customer.
  2. Explain string functions.
    Functions like CONCAT(), SUBSTR(), LENGTH(), UPPER(), LOWER().
    Example: Extracted domain from email addresses with SUBSTR and LOCATE.
  3. What are mathematical functions?
    Functions like ROUND(), CEIL(), FLOOR(), ABS().
    Example: Rounded product prices for display using ROUND.
  4. Explain NULL in SQL and how to handle it?
    NULL represents unknown or missing value; use IS NULL / IS NOT NULL in queries.
    Example: Replaced NULL phone numbers with default ‘N/A’ in customer reports.
  5. What are logical operators?
    AND, OR, NOT for combining filter conditions.
    Example: Filtered customers with status ‘Active’ AND credit over 1000.

SQL Advanced Queries

  1. What is a recursive query?
    Query referencing itself to handle hierarchical data.
    Example: Retrieved employee-management hierarchy with a recursive CTE.
  2. Explain Common Table Expressions (CTEs).
    Temporary named result sets to simplify complex queries.
    Example: Used CTEs for stepwise computation of monthly rolling sales.
  3. What are window functions?
    Functions with OVER() clause to compute aggregates across partitions.
    Example: Calculated running total sales per month using SUM() OVER().
  4. Explain pivoting data in SQL.
    Transforming rows into columns to summarize data.
    Example: Pivoted monthly sales per region from rows to column format for reporting.
  5. What is the difference between TRUNCATE and DROP?
    TRUNCATE clears rows retaining table; DROP deletes table structure and data.
    Example: Used TRUNCATE to clear staging area before new ETL loads.

SQL Joins Continued

  1. What is NATURAL JOIN?
    Join based on columns with same names in both tables.
    Example: Used NATURAL JOIN cautiously to combine related tables without specifying keys.
  2. Explain CROSS APPLY and OUTER APPLY.
    Apply operators invoke table-valued functions for each row in outer query.
    Example: Used CROSS APPLY to join sales table with function returning top products per region.
  3. What is self-referencing table?
    A table referencing itself via foreign key for hierarchical data.
    Example: Modeled organization chart with employee table having manager_id foreign key.
  4. Difference between CROSS JOIN and INNER JOIN?
    CROSS JOIN creates cartesian product; INNER JOIN filters based on matching keys.
    Example: Generated combinations of all products and stores for price listing with CROSS JOIN.
  5. Explain NULL-safe comparisons?
    Special operators (IS NULL, NULL-safe equals) to compare NULL values reliably.
    Example: Used IS NULL to find missing mailing addresses.

SQL Transactions and Locks

  1. What is autocommit mode?
    Each statement is automatically committed unless transaction explicitly started.
    Example: Turned off autocommit during batch data updates for transactional integrity.
  2. What are implicit and explicit transactions?
    Implicit commits each statement; explicit groups multiple statements within BEGIN and COMMIT.
    Example: Wrapped multi-step order inserts in explicit transactions.
  3. What types of locks does SQL use?
    Shared, exclusive, update locks for concurrency control.
    Example: Analyzed lock contention issues affecting online booking system.
  4. What is lock escalation?
    Consolidating many row/page locks into a table lock for resource efficiency.
    Example: Monitored lock escalation to prevent blocking in high concurrency tables.
  5. Explain dirty reads, non-repeatable reads, phantom reads.
    Isolation anomalies causing inconsistent data reads in concurrent transactions.
    Example: Set appropriate isolation levels to avoid dirty reads in banking app.

SQL Data Types and Schemas

  1. What are the common SQL data types?
    INT, VARCHAR, DATE, TIMESTAMP, BOOLEAN, BLOB, DECIMAL.
    Example: Defined VARCHAR(50) for customer names and DECIMAL(10,2) for prices.
  2. Explain schema in SQL?
    Logical grouping or namespace for database objects.
    Example: Separated HR and Finance tables in different schemas for organizational clarity.
  3. What are constraints?
    Rules enforcing data integrity like NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY.
    Example: Used CHECK constraint to restrict order quantities above zero.
  4. Difference between CHAR and VARCHAR?
    CHAR fixed length, VARCHAR variable length strings.
    Example: Used CHAR for country codes, VARCHAR for descriptions.
  5. How do you handle reserved keywords in SQL?
    Use escape characters or quotes around identifiers.
    Example: Queried table named “user” using "user" to avoid syntax errors.

SQL Backup and Recovery

  1. What are backup types in SQL?
    Full, differential, and transaction log backups.
    Example: Scheduled full backups weekly with daily differential and hourly log backups.
  2. Explain point-in-time recovery.
    Ability to restore database up to a specific transaction time.
    Example: Restored database to just before accidental deletion using log backups.
  3. What is replication?
    Copying and distribution of data between databases for redundancy or load balancing.
    Example: Configured replication for read-only copies of production database.
  4. Explain disaster recovery strategies for databases.
    Using backups, replication, failover clustering to ensure data availability.
    Example: Designed disaster recovery plan with automated failover clusters.
  5. How do you monitor database health?
    Using logs, monitoring tools, performance counters.
    Example: Used monitoring scripts to alert on slow queries and blocking sessions.

SQL Miscellaneous

  1. What is a cursor?
    A database object to traverse rows one-by-one.
    Example: Used cursor cautiously to perform row-based complex calculations not possible in set operations.
  2. What are temporary tables and how used?
    Tables stored in tempdb for session or transaction lifetime.
    Example: Used temporary tables in ETL to hold intermediate data before final load.
  3. Explain the difference between DELETE and DROP.
    DELETE removes rows; DROP deletes table structure.
    Example: Used DELETE to clear old records and DROP to remove obsolete staging tables.
  4. How do you export data from SQL?
    Use tools like bcp, SQL Server Management Studio Export Wizard, or COPY command.
    Example: Exported monthly sales data to CSV for external analytics.
  5. What are views and why use them?
    Simplify complex queries, encapsulate business logic, secure base tables.
    Example: Created a view for sales with discounts applied to simplify report generation.

Job/Project Specific and Final Concepts

  1. How do you handle slow running queries?
    Review query plans, indexing, statistics, avoid cursors, use temp tables.
    Example: Improved slow report query by indexing join keys and rewriting correlated subquery.
  2. Explain EXISTS vs IN operator.
    EXISTS returns boolean, usually faster for subqueries; IN tests value presence in set.
    Example: Optimized query by replacing IN with EXISTS for large subquery.
  3. What is query optimization?
    Process of rewriting queries or adding structures to improve performance.
    Example: Optimized monthly sales report queries to reduce execution time by factor of 10.
  4. How is data integrity maintained in SQL?
    Through constraints, transactions, triggers, and referential integrity.
    Example: Implemented check constraints and cascades to prevent orphaned records.
  5. What is a cross database query?
    Query accessing multiple databases on same server or linked servers.
    Example: Joined customer data from sales database with support tickets from helpdesk database.
  6. Explain the difference between synchronous and asynchronous replication.
    Synchronous waits for all replicas to confirm transaction; asynchronous doesn’t wait, faster but less safe.
    Example: Used synchronous replication for critical financial transactions.
  7. How do you import large datasets efficiently?
    Use bulk insert, partitioning, disable constraints during load.
    Example: Bulk inserted millions of log records nightly with minimal downtime.
  8. What is a surrogate key?
    Artificial key introduced to uniquely identify records.
    Example: Added surrogate keys in a data warehouse for slowly changing dimensions.
  9. What are temporary stored procedures?
    Procedures scoped to session and dropped on disconnect.
    Example: Used temporary procedure for one-time complex data transformation.
  10. What are common SQL interview best practices?
    Know basics well, write clean queries, explain logic clearly, showcase performance awareness, demonstrate practical examples.
    Example: Practiced writing optimized queries during project life cycle and explained indexing strategy during interviews.

Leave a Comment