Technical Interview Questions and Answers: Core SQL Essentials

SQL (Structured Query Language) serves as the foundational data management standard across modern backend systems, data analytics pipelines, and cloud databases. Leading technology firms—including Netflix, Meta, Uber, Google, and Microsoft—rely heavily on relational database structures to manage mission-critical data. Whether designing database schemas as a software engineer, developing data engineering pipelines, or performing business intelligence analytics, mastering core SQL concepts is essential for technical interviews and production environments.

1. Database Foundations: DBMS vs. RDBMS

At a architectural level, data systems are broadly divided into traditional Database Management Systems (DBMS) and Relational Database Management Systems (RDBMS).

• Database Management System (DBMS): Stores data as individual, unstructured, or semi-structured files directly on disk. In a standard DBMS, there are no structural foreign key relationships linking individual files. Searching for specific entries requires scanning files sequentially, leading to high time complexity for retrieval operations ($O(N)$ file scans).
• Relational Database Management System (RDBMS): Organizes data into structured tables consisting of explicit rows and columns. Tables are interconnected through normalized key relationships (Primary and Foreign Keys). RDBMS platforms (such as MySQL, PostgreSQL, Oracle, and SQL Server) support ACID compliance, dynamic concurrency control, indexed data retrieval, and data redundancy reduction.

Real-World Analogy

Imagine a classroom where a teacher maintains student records across physical paper files. In a flat DBMS setup, the teacher writes roll numbers on one paper, names on another, and phone numbers on a third, keeping them in unlinked folders. Looking up a student requires pulling and reading every file manually.

In an RDBMS setup, the teacher stores data in a structured, interconnected spreadsheet table where a student's unique Roll Number directly links their name, address, and enrolled subjects across related sheets.

2. Relational Integrity: Primary Keys vs. Foreign Keys

Enforcing structural integrity across relational tables depends on key constraints that define unique identification and transactional linkage.

Primary Key

A Primary Key uniquely identifies each row (entity record) in a database table.

• Must contain strictly unique values across all rows.
• Cannot accept NULL values (NOT NULL is implicitly enforced).
• A table can contain only one Primary Key (which can be a single column or a composite key composed of multiple columns).

Foreign Key

A Foreign Key is a column (or set of columns) in a child table that references the Primary Key of a parent table.

• Establishes a relational link between two distinct tables.
• Ensures referential integrity: A Foreign Key value in a child table must either match an existing Primary Key value in the parent table or be explicitly set to NULL.
• Prevents orphaned records in child tables when parent records are updated or deleted.

-- Creating Parent Table with Primary Key
CREATE TABLE Students (
    roll_number INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

-- Creating Child Table with Foreign Key
CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    roll_number INT,
    course_name VARCHAR(50) NOT NULL,
    FOREIGN KEY (roll_number) REFERENCES Students(roll_number)
);

3. Column-Level Governance: SQL Constraints

SQL constraints are declarative rules defined on table columns to enforce data quality, domain rules, and integrity policies. Attempting an operation (INSERT, UPDATE, DELETE) that violates a constraint causes the database engine to abort the transaction with an error.

Real-World Analogy

SQL constraints act like validation rules in a digital Google Form. Mandatory questions represent NOT NULL rules, phone number length checks represent CHECK constraints, and pre-selected default country dropdowns act as DEFAULT values.

CREATE TABLE Employee_Schema (
    emp_id INT PRIMARY KEY,                       -- Primary Key (NOT NULL + UNIQUE)
    email VARCHAR(100) UNIQUE NOT NULL,           -- Unique and compulsory
    age INT CHECK (age >= 18),                    -- Business Rule: Must be 18 or older
    country VARCHAR(50) DEFAULT 'India'           -- Fallback value if omitted
);

4. SQL Command Classification: Structural (DDL) vs. Data (DML) Operations

SQL commands are categorized based on their underlying operational scope: modifying the database structure (DDL) versus manipulating stored records (DML).

Data Definition Language (DDL)

DDL statements define, alter, and manage database structures (schemas, tables, indexes, views). They do not directly modify individual rows, and in most relational databases (such as MySQL), DDL operations auto-commit instantly.

• CREATE: Builds new database objects (tables, indexes, views).
• ALTER: Modifies the schema of existing tables (adding, dropping, or renaming columns).
• DROP: Permanently deletes an object and its structural metadata.
• TRUNCATE: Removes all data rows from a table while keeping the schema structure intact.

Data Manipulation Language (DML)

DML statements manage individual rows stored within existing schema structures. DML operations occur inside transactional scopes and can be committed (COMMIT) or rolled back (ROLLBACK).

• INSERT: Adds new data rows into a table.
• UPDATE: Modifies column values of existing rows based on filter predicates.
• DELETE: Removes specific existing rows based on filter predicates.

-- DDL Examples (Structure Operations)
CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    department VARCHAR(50)
);

ALTER TABLE Employees ADD salary DECIMAL(10, 2);

-- DML Examples (Record Operations)
INSERT INTO Employees (emp_id, emp_name, department, salary) 
VALUES (101, 'Joe', 'Manager', 85000.00);

UPDATE Employees 
SET salary = 92000.00 
WHERE emp_id = 101;

5. Data Removal Mechanisms: DELETE, TRUNCATE, and DROP

Understanding the behavioral differences between DELETE, TRUNCATE, and DROP is a common requirement in system administration and data architecture interviews.

Real-World Analogy

Consider a wooden bookshelf packed with books:

• DELETE: Selectively pulling out specific red books off the shelf while leaving all other books untouched. You can place them back if you change your mind (ROLLBACK).
• TRUNCATE: Wiping all books off the shelf in one quick sweep. The shelf remains completely empty and ready to hold new books.
• DROP: Demolishing the wooden shelf entirely and throwing it away. Neither the books nor the physical shelf exist anymore.

6. Summarization vs. Presentation: GROUP BY vs. ORDER BY

Both GROUP BY and ORDER BY organize query output, but they operate at distinct pipeline stages and serve different purposes.

GROUP BY

• Collapses rows that share identical values in specified columns into aggregate summary rows.
• Typically used alongside aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX()).
• Reduces the total output row count based on the unique combinations of the grouped key columns.

ORDER BY

• Sorts the final result set in ascending (ASC) or descending (DESC) order based on specified columns.
• Does not alter row contents or aggregate data; it changes only the presentation sequence.

-- Combining GROUP BY and ORDER BY
SELECT 
    department, 
    AVG(salary) AS avg_salary
FROM Employees
GROUP BY department
ORDER BY avg_salary DESC;

7. Sequence-Based Filtering: WHERE vs. HAVING Clauses

A common point of confusion in SQL queries is deciding whether to place filter criteria in the WHERE clause or the HAVING clause. This decision depends on where the operation occurs in the SQL execution logical order.

Real-World Analogy

• WHERE (Row Filtering): A gatekeeper checking IDs before letting individual visitors into an event hall. Anyone without a valid pass is turned away before group entry.
• HAVING (Group Filtering): Judges evaluating team performances in a talent show competition. Whole groups perform first, their collective scores are calculated, and then groups with average scores below a threshold are eliminated after aggregation.

-- Valid Query displaying both WHERE and HAVING filters
SELECT 
    department, 
    COUNT(emp_id) AS total_employees
FROM Employees
WHERE age >= 18                              -- Filter individual rows BEFORE aggregation
GROUP BY department
HAVING COUNT(emp_id) > 5;                    -- Filter aggregated groups AFTER aggregation

8. Mathematical Aggregation: Core SQL Aggregate Functions

Aggregate functions calculate scalar values across a set of column inputs. All built-in aggregate functions ignore standard NULL values during calculation (with the exception of COUNT(*)).

Assume the following sample Employees dataset:

SELECT 
    COUNT(*) AS total_records,
    COUNT(salary) AS total_valid_salaries,
    SUM(salary) AS total_payroll,
    AVG(salary) AS mean_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM Employees;

Aggregate Execution Matrix

9. Performance Optimization: Database Indexing & Clustered Indexes

Database indexes are specialized Data Structures (typically B-Trees or B+ Trees) that optimize data retrieval speeds without requiring a full table scan ($O(N)$).

Without an index, looking up a record forces the engine to scan through every physical block on disk. An index maintains a sorted tree structure mapping indexed search keys to physical record pointers, reducing query lookup time complexity to $O(\log N)$.

Clustered Index Architecture

A Clustered Index alters the physical storage layout of table data on disk, sorting records directly according to the clustered index key.

• Because physical table data rows can only be ordered on disk in one sequence, a table can have only one Clustered Index (typically assigned automatically to the Primary Key).
• Non-Clustered Indexes create separate index structures that store sorted search keys alongside direct pointers to the actual data rows stored elsewhere on disk.

Real-World Analogy

• Clustered Index: A telephone directory or textbook where pages are physically organized alphabetically by last name or incrementally by page numbers. The physical content is the index.
• Non-Clustered Index: The index section at the back of a textbook. It lists key terms alphabetically alongside page number references pointing back to where the topics live in the main body.

10. Data Architecture: Normalization & Normal Forms

Normalization is a systematic database design methodology that minimizes data redundancy, prevents operational anomalies, and maintains storage efficiency.

Stages of Normal Forms (1NF through BCNF)

1. First Normal Form (1NF):
   • Every table cell must store a single, atomic value (no nested arrays or multi-value lists).
   • All entries in a column must share the same data type.
   • Each column must have a unique name.
2. Second Normal Form (2NF):
   • Must satisfy all requirements of 1NF.
   • Eliminates partial dependencies: Every non-key column must depend fully on the primary key, rather than just part of a composite primary key.
3. Third Normal Form (3NF):
   • Must satisfy all requirements of 2NF.
   • Eliminates transitive functional dependencies: Non-key columns must not depend on other non-key columns ($A \rightarrow B$ and $B \rightarrow C$ functional dependencies are split into dedicated tables).
4. Boyce-Codd Normal Form (BCNF):
   • A stricter variant of 3NF.
   • For every functional dependency $X \rightarrow Y$, $X$ must be a candidate key.

11. Set Operations: UNION vs. UNION ALL

UNION and UNION ALL allow queries to combine the output of two or more SELECT queries into a single unified result set. Both queries must output identical column counts with compatible data types.

Real-World Analogy

Imagine merging guest lists for a party (School Friends list vs. Work Friends list).

• UNION: Cross-references both lists to merge them while removing duplicate names so no guest receives two physical invitations.
• UNION ALL: Prints out both lists combined directly into one paper document without checking for overlapping names.

-- Query using UNION (Unique records only)
SELECT student_name FROM School_Friends
UNION
SELECT student_name FROM Work_Friends;

-- Query using UNION ALL (Includes duplicates)
SELECT student_name FROM School_Friends
UNION ALL
SELECT student_name FROM Work_Friends;

12. Query Logic Walkthrough: Finding the 2nd Highest Salary

A standard technical interview query challenges candidate logic by requiring them to extract the $N$-th highest value without using non-standard vendor extensions (such as TOP or LIMIT).

Step-by-Step Logic Evolution (Nested Subquery Strategy)

To find the second highest salary using ANSI-compliant subqueries:

Step 1: Compute absolute maximum salary
   |    SELECT MAX(salary) FROM Employees
   v    (Example Result: $100,000)
   
Step 2: Find maximum salary strictly less than absolute maximum
   |    SELECT MAX(salary) FROM Employees WHERE salary < ($100,000)
   v    (Example Result: $90,000)
   
Step 3: Retrieve employee profile matching Step 2 scalar result
        SELECT name, salary FROM Employees WHERE salary = ($90,000)

-- Production ANSI-SQL Solution: Finding the 2nd Highest Salary
SELECT emp_name, salary 
FROM Employees 
WHERE salary = (
    SELECT MAX(salary) 
    FROM Employees 
    WHERE salary < (
        SELECT MAX(salary) 
        FROM Employees
    )
);

Alternative Method: If using window functions, this can also be cleanly solved using DENSE_RANK():

SQL

Copy

WITH RankedSalaries AS (
    SELECT emp_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM Employees
)
SELECT emp_name, salary FROM RankedSalaries WHERE rnk = 2;

13. Logical Abstraction & Security: Database Views

A View is a dynamic, virtual table defined by an underlying SQL query. Views do not store physical data rows directly on disk (unlike materialized views); instead, they query underlying physical tables dynamically when invoked.

Key Advantages of Views

1. Security and Permission Control: Hides sensitive columns (such as social security numbers, salaries, or internal IDs) while granting users read access to a non-sensitive subset of columns.
2. Simplified Complex Joins: Encapsulates multi-table joins, transformations, and filters behind a single virtual interface.

-- Constructing a Virtual Security View
CREATE VIEW details_view AS
SELECT 
    student_name, 
    address 
FROM Student_Master_Table
WHERE student_id < 5;

-- Executing a Read Call Against Virtual View
SELECT * FROM details_view;

14. Data Transformation & Event Automation: Date Conversions and Triggers

String to Date Transformations

Databases often ingest raw data feeds containing dates formatted as strings (such as '27102024'). Converting non-standard text strings into standardized temporal structures requires data parsing functions like STR_TO_DATE().

-- Converting string format 'DDMMYYYY' to a temporal DATE structure
SELECT STR_TO_DATE('27102024', '%d%m%Y') AS formatted_date;
-- Output Result: 2024-10-27

Automated Database Operations: Triggers

A Database Trigger is a procedural block of code that automatically executes in response to specified Data Manipulation Language (INSERT, UPDATE, DELETE) events on a target table.

Real-World Analogy

Think of a library borrowing checkout counter. When a librarian records a newly checked-out book (INSERT into borrowed_books), an automated workflow immediately runs behind the scenes to reduce the library's available stock count by 1 (UPDATE book SET stock = stock - 1).

-- Automated Inventory Management Trigger Implementation
DELIMITER //
CREATE TRIGGER update_book_stock
AFTER INSERT ON borrowed_books
FOR EACH ROW
BEGIN
    UPDATE books
    SET available_count = available_count - 1
    WHERE book_id = NEW.book_id;
END;
//
DELIMITER ;

Summary and Key Takeaways

• RDBMS vs DBMS: Relational databases replace unlinked flat files with structured, indexed tables linked by key constraints to improve query efficiency and enforce ACID compliance.
• Key Controls: Primary Keys guarantee unique entity identification within a table, while Foreign Keys maintain referential integrity across related tables.
• Declarative Rules: Constraints (NOT NULL, UNIQUE, CHECK, DEFAULT) automatically enforce data validation and business rules at the schema level.
• Command Scope: DDL commands (CREATE, ALTER, DROP, TRUNCATE) manage structural schemas, whereas DML commands (INSERT, UPDATE, DELETE) manage data rows within those structures.
• Data Deletion: DELETE removes specific target rows inside transactional bounds; TRUNCATE rapidly deallocates table data while preserving schema structure; DROP permanently destroys both the data and the schema structure.
• Filtering Sequence: The WHERE clause filters individual rows before aggregation occurs, whereas the HAVING clause filters aggregated group buckets after the GROUP BY execution phase.
• Query Performance: Indexes leverage B-Tree data structures to reduce search time complexity from $O(N)$ scans down to $O(\log N)$ tree lookups. Clustered indexes define the physical storage sequence of data rows on disk.
• Schema Design: Database normalization systematically reduces redundancy and eliminates insertion, deletion, and update anomalies through structured normal forms (1NF to BCNF).
• Combining Datasets: UNION merges result sets while performing an explicit deduplication pass, whereas UNION ALL concatenates result sets directly for faster linear execution.
• Automation & Virtualization: Views provide abstract security layers over sensitive underlying tables, while Triggers automate event-driven backend tasks during data manipulation.