Scribd Logo
Upload

Welcome to Scribd!

  • 20 views

    Senior SQL Questions

    Uploaded by

    pbiegonitc

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Senior SQL Questions

    Uploaded by

    pbiegonitc
    20 views3 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    20 views3 pages

    Senior SQL Questions

    Uploaded by

    pbiegonitc

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    Senior-Level SQL, SQL Server, and

    MySQL Interview Questions and


    Answers
    Explain how you would optimize a complex query that involves multiple JOINs
    and large datasets.
    Optimizing a complex query with multiple JOINs involves several strategies:

    1. **Indexing**: Ensure that the columns used in JOIN, WHERE, and ORDER BY clauses are
    indexed. Composite indexes (multi-column indexes) might also be necessary if queries filter
    by multiple columns.
    2. **Use of EXISTS vs. IN**: If you’re using `IN` with subqueries, consider replacing it with
    `EXISTS`, as it generally performs better with large datasets.
    3. **Avoid SELECT ***: Always specify the required columns instead of using `SELECT *` to
    reduce the amount of data returned.
    4. **Denormalization**: If your data model involves many JOINs between multiple tables,
    consider denormalizing certain parts of the schema to reduce the number of JOINs required.
    5. **Partitioning**: Partition large tables to speed up query execution on specific subsets of
    data.
    6. **Query Execution Plan**: Use the `EXPLAIN` or `EXPLAIN ANALYZE` command to
    understand the query execution plan. It helps identify bottlenecks like full table scans,
    missing indexes, or inefficient join operations.
    7. **Temporary Tables**: For extremely complex queries, break them into smaller
    subqueries, using temporary tables to store intermediate results.

    How do window functions work in SQL? Can you provide an example?


    Window functions perform calculations across a set of rows that are related to the current
    row, but unlike aggregate functions, they do not collapse the result set. A window function
    uses the `OVER()` clause, which defines the partitioning and ordering of rows.

    Common window functions include `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`,


    `LEAD()`, `LAG()`, and `SUM()`.

    Example: Calculate the running total of sales for each salesperson:


    ```sql
    SELECT
    SalesPersonID,
    OrderID,
    OrderAmount,
    SUM(OrderAmount) OVER (PARTITION BY SalesPersonID ORDER BY OrderDate) AS
    RunningTotal
    FROM
    Orders
    ORDER BY
    SalesPersonID, OrderDate;
    ```
    In this example, the `SUM` function calculates a running total of sales (`OrderAmount`) for
    each `SalesPersonID`, ordered by `OrderDate`.

    What are the different types of indexes in SQL Server, and when would you use
    each?
    SQL Server provides several types of indexes, each with specific use cases:

    1. **Clustered Index**: Stores data rows in a sorted order based on the indexed column.
    2. **Non-Clustered Index**: A separate data structure that stores pointers to the actual data
    rows.
    3. **Unique Index**: Ensures that all values in the indexed column are unique.
    4. **Filtered Index**: An index with a WHERE clause that covers a subset of rows in a table.
    5. **Full-Text Index**: Used for full-text search capabilities.
    6. **Columnstore Index**: Stores data in a columnar format, optimized for queries involving
    large scans across columns.

    What is the difference between `ROWLOCK`, `PAGLOCK`, and `TABLOCK` in SQL


    Server?
    These are lock hints in SQL Server that specify the level of locking used during transactions:

    1. **ROWLOCK**: Locks individual rows during the transaction.


    2. **PAGLOCK**: Locks an entire page (typically 8 KB of data) in the table.
    3. **TABLOCK**: Locks the entire table, preventing access until the lock is released.

    What is the difference between MyISAM and InnoDB storage engines in


    MySQL?
    MyISAM and InnoDB are two different storage engines in MySQL:

    - **MyISAM**: Does not support foreign keys or transactions, provides faster read
    operations.
    - **InnoDB**: Supports ACID properties, row-level locking, foreign keys, and crash recovery.

    How do you handle deadlocks in MySQL?


    Deadlocks occur when two or more transactions are waiting for each other to release locks.

    1. **Proper Transaction Design**: Ensure transactions access resources in a consistent


    order.
    2. **Retry Logic**: Implement retry logic in the application layer after a brief delay.
    3. **Lock Timeout**: Adjust `innodb_lock_wait_timeout` to define lock wait limits.
    4. **Analyze Deadlock Reports**: Use `SHOW ENGINE INNODB STATUS` for deadlock
    information.

    You might also like