Ultimate SQL Interview Guide

SQL (Structured Query Language) is the standard for managing and manipulating relational data. It includes commands for querying, updating, and defining database structures. This guide is divided into logical sections for easy navigation, with full theory explanations, code examples, diagrams (described in text for readability), and best practices. At the end of relevant sections, you'll find an enhanced Q&A bank (questions 1-114) drawn from common interview topics, expanded with clearer explanations, additional SQL snippets, and edge-case insights.

Key Features of This Guide:

• Theory & Best Practices: In-depth explanations with real-world applications.
• Examples: Runnable SQL code blocks for illustration.
• Diagrams: Text-based representations (e.g., ASCII art) where helpful for visualizing joins or transactions.
• Q&A Bank: 115 questions, polished and expanded for deeper understanding.
• Assumptions: Examples use MySQL syntax unless noted; adapt for other DBMS as needed.

Tips for Interview Success:

• Practice writing queries by hand—interviews often involve whiteboarding.
• Understand differences between DBMS (e.g., MySQL vs. Oracle).
• Focus on optimization: Indexes, query plans, and avoiding common pitfalls like SQL injection.
• Know ACID properties for transactions and normalization for schema design.

Let's dive in!

Table of Contents

<!-- TOC start -->

• Section 1: SQL Fundamentals
  • Theory
  • Examples
  • Q&A Bank (Questions 1-10)
• Section 2: Querying Data
  • Theory
  • Diagrams
  • Examples
  • Q&A Bank (Questions 11-42)
• Section 3: Joins and Set Operations
  • Theory
  • Diagrams
  • Examples
  • Q&A Bank (Questions 43-54)
• Section 4: Subqueries and Advanced Queries
  • Theory
  • Examples
  • Q&A Bank (Questions 55-60)
• Section 5: Data Definition and Manipulation (DDL, DML, DCL)
  • Theory
  • Examples
  • Q&A Bank (Questions 61-91)
• Section 6: Indexes, Privileges, and Security
  • Theory
  • Examples
  • Q&A Bank (Questions 92-96, 115)
• Section 7: Transactions and Concurrency
  • Theory
  • Diagrams
  • Examples
  • Q&A Bank (Questions 97-104)
• Section 8: Views, Stored Procedures, Triggers, and Advanced Topics
  • Theory
  • Examples
  • Q&A Bank (Questions 105-114)

<!-- TOC end -->

Section 1: SQL Fundamentals

Theory

SQL is a declarative language for interacting with RDBMS. It was developed in the 1970s by IBM and standardized by ANSI. SQL handles data definition (DDL), manipulation (DML), control (DCL), and querying (DQL). Key commands include SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP.

Unlike procedural languages (e.g., PL/SQL), SQL focuses on what to do, not how. It supports standards but has vendor-specific extensions (e.g., MySQL's LIMIT vs. SQL Server's TOP).

Best Practices:

• Use consistent casing (e.g., uppercase keywords) for readability.
• Always quote string literals with single quotes.
• Avoid SELECT * in production; specify columns to reduce data transfer.
• Handle NULLs explicitly, as they represent unknown values and can affect comparisons.

Examples

Basic SELECT:

-- Select all columns from a table
SELECT * FROM customers;

Handling NULLs:

-- Use IS NULL to check for missing values
SELECT * FROM products WHERE price IS NULL;

Q&A Bank (Questions 1-10)

1. SQL was developed as an integral part of
   SQL (Structured Query Language) is a domain-specific language for managing data in RDBMS or RDSMS. It's an ANSI standard with major commands like SELECT, UPDATE, DELETE, INSERT, and WHERE. SQL can query, insert/update/delete data, create databases/tables/views, and manage permissions. Vendor extensions (e.g., MySQL's proprietary functions) exist beyond the standard.
   Example:

   SELECT * FROM employees WHERE salary > 50000;
   

2. What does SQL stand for?
   SQL stands for Structured Query Language. It accesses and manipulates databases and is an ANSI standard.

3. SQL is (referring to it as a Programming Language)
   SQL is declarative: you specify the desired result without detailing steps (e.g., no need to manage indexes manually). It's non-procedural, unlike languages like C++ that require explicit operations. This abstraction makes SQL efficient for data tasks.

4. Which of the following is NOT a SQL command? (SELECT, REMOVE, UPDATE, INSERT)
   REMOVE is not a valid SQL command. Use DELETE instead. Key commands: SELECT (extract), UPDATE (modify), DELETE (remove), INSERT INTO (add), CREATE/ALTER/DROP (for structures), and INDEX operations.

5. What is MySQL?
   MySQL is an open-source RDBMS by Oracle, using SQL as its language. Difference between SQL and MySQL: SQL is the language; MySQL is the system implementing it.
   Best Practice: Use MySQL for web apps due to its speed; consider PostgreSQL for complex transactions.

6. What are some properties of PL/SQL?
   PL/SQL (Procedural Language/SQL) extends SQL with procedural features like loops and variables, developed by Oracle. It's embedded in Oracle Database alongside SQL and Java. Difference between SQL and PL/SQL: SQL is for single queries/DML; PL/SQL for full programs. SQL is data-oriented; PL/SQL is application-oriented. SQL executes statements individually; PL/SQL in blocks. SQL can embed in PL/SQL, but not vice versa.
   Example (PL/SQL block):

   BEGIN
     DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
   END;
   

7. What are the possible values for the BOOLEAN data field in MySQL?
   MySQL uses TINYINT(1) for booleans: 0 is false, non-zero is true.
   Example:

   CREATE TABLE users (is_active TINYINT(1));
   INSERT INTO users VALUES (1);  -- True
   

8. What data type would you choose if you wanted to store the distance (rounded to the nearest mile)?
   INTEGER (or INT) for whole numbers. Use DECIMAL for precision if needed.
   Best Practice: Choose data types based on range and precision to optimize storage.

9. Which are valid SQL keywords (statements & clauses)
   Valid: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, UPDATE, DELETE, INSERT INTO, CREATE/ALTER DATABASE/TABLE, DROP TABLE, CREATE/DROP INDEX.

10. Which of the following are valid SQL comments?
    Single-line: -- Comment. Multi-line: /* Comment */. Comments explain code or prevent execution.
    Example:

    -- Single-line
    SELECT * FROM customers;  /* Multi-line comment */
    

Section 2: Querying Data

Theory

Querying uses SELECT to retrieve data, with clauses like FROM (source), WHERE (filter), ORDER BY (sort), LIMIT (paginate). Operators include =, >, LIKE (patterns), BETWEEN (ranges), IN (lists). Handle duplicates with DISTINCT; NULLs with IS NULL/IFNULL.

Best Practices:

• Use indexes on WHERE columns for speed.
• Avoid wildcard % at LIKE's start for performance.
• Combine conditions with AND/OR logically.

Diagrams

Text-based JOIN visualization (later in Joins section).

Examples

Pattern matching:

SELECT * FROM products WHERE name LIKE 'A%';  -- Starts with A

Q&A Bank (Questions 11-42)

11. Which SQL statement is used to extract data from a database?
    SELECT retrieves rows. Optional clauses: WHERE, GROUP BY, HAVING, ORDER BY, AS (aliases).
    Example:

    SELECT name AS full_name FROM customers WHERE age > 30 ORDER BY age DESC;
    

12. How to select all records from the table 'Products'?

    SELECT * FROM products;
    

    Best Practice: Avoid * in production; list columns explicitly.

13. Can we rename a column in the output of SQL query?
    Yes, using AS for aliases.
    Example:

    SELECT first_name AS fname FROM customers;
    

14. With SQL, how do you select a column named "FirstName" from a table named "Customers"?

    SELECT FirstName FROM Customers;
    

15. What does the SQL FROM clause do?
    Specifies tables/joins for data extraction.

16. Which SQL statement is used to return only different values?
    SELECT DISTINCT.
    Example:

    SELECT DISTINCT city FROM addresses;
    

17. Consider the following schema ADDRESSES (id, street_name, number, city, state) Which of the following query would display the distinct cities in the ADDRESSES table?

    SELECT DISTINCT city FROM addresses;
    

18. With SQL, how do you select all the records from a table named "Customers" where the value of the column "FirstName" is "John"?

    SELECT * FROM Customers WHERE FirstName = 'John';
    

    Use single quotes for strings.

19. The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true.
    True. Combine with NOT for negation.
    Example:

    SELECT * FROM customers WHERE age > 18 AND city = 'NY' OR status = 'active';
    

20. **Which of the f