Skip to content
TheChatArchiveThe Chat Archive
Qwenqwen3-235b-a22b-no-thinking· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

sql important ques for interview

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with o3-mini’s answer.
  1. User

    sql important ques for interview

  2. Qwen Assistant

    Here are important SQL interview questions categorized by difficulty level and topic. These are commonly asked in interviews for roles like Data Analyst, Data Scientist, Software Developer, Database Administrator, etc.


    🟢 Basic SQL Interview Questions

    1. What is SQL?

    • Answer: SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases.

    2. What is the difference between DELETE and TRUNCATE commands?

    FeatureDELETETRUNCATE
    DML/DLLDML commandDDL command
    WHERE clauseCan be usedCannot be used
    RollbackPossibleNot possible in most RDBMS
    Auto-increment resetNoYes (in some DBs like MySQL)

    3. Explain JOIN types.

    • INNER JOIN: Returns matching rows from both tables.
    • LEFT JOIN (LEFT OUTER JOIN): All rows from left table + matched rows from right.
    • RIGHT JOIN (RIGHT OUTER JOIN): All rows from right table + matched rows from left.
    • FULL JOIN (FULL OUTER JOIN): All rows when there's a match in either table.
    • CROSS JOIN: Cartesian product of two tables.

    4. What is a primary key vs. unique key?

    FeaturePrimary KeyUnique Key
    Null valuesNot allowedOne null allowed (in some DBs)
    Number per tableOneMultiple
    IndexingAutomatically indexedAutomatically indexed

    5. What is normalization? Name different normal forms.

    • Normalization: Process of organizing data to reduce redundancy.
    • Normal Forms:
      • First Normal Form (1NF)
      • Second Normal Form (2NF)
      • Third Normal Form (3NF)
      • Boyce-Codd Normal Form (BCNF)

    🟠 Intermediate SQL Interview Questions

    6. What is a subquery?

    • A query inside another query.
    • Types:
      • Scalar
      • Row
      • Table
      • Correlated

    Example:

    SELECT name FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);
    

    7. Explain GROUP BY and HAVING.

    • GROUP BY: Groups rows sharing same value.
    • HAVING: Filters grouped result (used with GROUP BY, unlike WHERE).

    Example:

    SELECT department_id, COUNT(*)
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5;
    

    8. Difference between WHERE and HAVING?

    ClauseApplies onUsed with
    WHEREIndividual rowsSELECT, UPDATE, DELETE
    HAVINGAggregated groupsGROUP BY in SELECT

    9. What is an index?

    • An index is a performance optimization structure that allows faster retrieval of records.
    • Types: Clustered, Non-clustered, Unique, Composite, Full-text

    10. What are constraints?

    Constraints enforce rules on data in a table.

    • NOT NULL
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • CHECK
    • DEFAULT

    🔴 Advanced SQL Interview Questions

    11. What is a view?

    • A virtual table based on the result-set of an SQL query.
    • Syntax:
    CREATE VIEW employee_view AS
    SELECT name, department FROM employees;
    

    12. What is a stored procedure?

    • A precompiled collection of SQL statements and control flow logic.
    • Example:
    CREATE PROCEDURE GetEmployeeCount
    AS
    BEGIN
        SELECT COUNT(*) FROM employees;
    END
    

    13. What is a trigger?

    • A special type of stored procedure that runs automatically when a specific event occurs (e.g., INSERT, UPDATE, DELETE).
    • Used for auditing, logging, enforcing business rules.

    14. Explain ACID properties.

    • Atomicity: Transaction is all or nothing.
    • Consistency: Only valid data is written.
    • Isolation: Transactions are isolated from each other.
    • Durability: Once committed, changes are permanent.

    15. What is a transaction?

    • A sequence of operations performed as a single logical unit of work.
    • Commands: BEGIN TRANSACTION, COMMIT, ROLLBACK

    16. What are Common Table Expressions (CTEs)?

    • Temporary named result set used within a SELECT, INSERT, UPDATE, or DELETE statement.
    • Example:
    WITH CTE_Employee AS (
        SELECT * FROM employees WHERE salary > 50000
    )
    SELECT * FROM CTE_Employee;
    

    17. Explain RANK(), DENSE_RANK(), and ROW_NUMBER().

    All are window functions:

    • ROW_NUMBER() – Assigns unique numbers.
    • RANK() – Same rank for ties, skips next number.
    • DENSE_RANK() – Same rank for ties, doesn’t skip next number.

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

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

    Or using subquery:

    SELECT salary FROM employees
    ORDER BY salary DESC LIMIT 1 OFFSET 1;
    

    🧪 Practical / Scenario-Based SQL Questions

    19. Find duplicate records in a table.

    SELECT emp_id, COUNT(*)
    FROM employees
    GROUP BY emp_id
    HAVING COUNT(*) > 1;
    

    20. How to delete duplicate records?

    DELETE FROM employees
    WHERE id NOT IN (
        SELECT MIN(id)
        FROM employees
        GROUP BY emp_id
    );
    

    21. Find Nth highest salary using a subquery or window function.

    Using window function:

    SELECT name, salary
    FROM (
        SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk
        FROM employees
    ) AS ranked
    WHERE rnk = N;
    

    📚 Bonus: SQL for Data Analysis

    22. What is a pivot table in SQL?

    • It transforms rows into columns.
    • Often done using CASE WHEN or PIVOT (in some SQL dialects like SQL Server).

    23. Time-based analysis queries

    Example: Monthly sales report

    SELECT DATE_TRUNC('month', order_date) AS month,
           SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
    ORDER BY month;
    

    🧠 Tips for SQL Interviews

    1. Practice writing queries manually – don't rely only on tools.
    2. Understand execution plans – helps in optimizing queries.
    3. Know your SQL dialect – MySQL, PostgreSQL, SQL Server, Oracle, etc.
    4. Be comfortable with joins, subqueries, window functions, and CTEs.
    5. Use real datasets like those on LeetCode, HackerRank, or Kaggle.

    Would you like me to provide a PDF version, or a list of SQL practice problems, or sample answers for these in a specific SQL dialect like MySQL, PostgreSQL, or SQL Server?

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.

Identical first prompt