SQL Fundamentals Interview Questions

Core SQL concepts every interview starts with — NULLs, keys, constraints, data types and aliases.

8 Questions
Detailed Answers
All Topics

Filter by Difficulty

1
Beginner

What is SQL and what is it used for?

Fundamentals 2 min read

SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It enables users to create, read, update, and delete data in database systems.

Key Points:

  • SQL is declarative - you specify what you want, not how to get it
  • Used across all major database systems (MySQL, PostgreSQL, Oracle, SQL Server)
  • Four main categories: DDL, DML, DCL, and TCL
  • Case-insensitive but conventionally written in uppercase
Example Query: 
SELECT first_name, last_name, email
FROM employees
WHERE department = 'Engineering'
ORDER BY last_name;
4
Intermediate

What is a Primary Key and Foreign Key?

Constraints 4 min read

A Primary Key uniquely identifies each record in a table and cannot contain NULL values. A Foreign Key is a field that links to the Primary Key of another table, establishing relationships between tables.

Primary Key Example: 
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);
Foreign Key Example: 
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Key Differences:

  • Primary Key: Unique identifier, cannot be NULL
  • Foreign Key: Can have duplicate values and NULL values
  • Primary Key: Only one per table
  • Foreign Key: Can have multiple per table
8
Expert

What is database normalization and what are its normal forms?

Database Design 8 min read

Database normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them.

Normal Forms:

  • 1NF (First Normal Form): Eliminate repeating groups, ensure atomic values
  • 2NF (Second Normal Form): Remove partial dependencies (meet 1NF + all non-key attributes depend on entire primary key)
  • 3NF (Third Normal Form): Remove transitive dependencies (meet 2NF + no non-prime attribute depends on another non-prime attribute)
  • BCNF (Boyce-Codd Normal Form): Stricter version of 3NF
  • 4NF (Fourth Normal Form): Remove multi-valued dependencies
  • 5NF (Fifth Normal Form): Remove join dependencies
Denormalized Table (Before): 
CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    department VARCHAR(100),
    dept_location VARCHAR(100),
    skill1 VARCHAR(50),
    skill2 VARCHAR(50),
    skill3 VARCHAR(50)
);
Normalized Tables (After 3NF): 
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100)
);

CREATE TABLE employee_skills (
    employee_id INT,
    skill VARCHAR(50),
    PRIMARY KEY (employee_id, skill)
);

Benefits of Normalization:

  • Reduces data redundancy
  • Improves data integrity
  • Simplifies data maintenance
  • Eliminates data anomalies (insert, update, delete)
12
Beginner

What is NULL in SQL and how is it different from empty string or zero?

NULL Values 3 min read

NULL represents missing, unknown, or inapplicable data. It's not the same as empty string ('') or zero (0).

NULL vs Empty String vs Zero: 
-- Sample data
INSERT INTO employees (id, name, phone, salary) VALUES
(1, 'John Doe', NULL, 50000),      -- phone is unknown
(2, 'Jane Smith', '', 60000),      -- phone is empty string
(3, 'Bob Wilson', '555-1234', 0);  -- phone exists, salary is 0

-- Different behaviors
SELECT * FROM employees WHERE phone IS NULL;     -- Returns John only
SELECT * FROM employees WHERE phone = '';        -- Returns Jane only
SELECT * FROM employees WHERE salary = 0;        -- Returns Bob only

Important NULL Characteristics:

  • NULL represents "unknown" or "not applicable"
  • NULL != NULL (use IS NULL or IS NOT NULL to check)
  • Arithmetic with NULL returns NULL (10 + NULL = NULL)
  • Aggregate functions ignore NULL values (except COUNT(*))
  • NULL sorts differently: usually last in ASC, first in DESC
  • Use COALESCE() or ISNULL() to handle NULL values
Handling NULL values: 
-- Use COALESCE to provide default values
SELECT name, COALESCE(phone, 'No phone') as contact
FROM employees;

-- Use NULLIF to treat specific values as NULL
SELECT name, NULLIF(salary, 0) as actual_salary
FROM employees;
18
Beginner

What is the difference between clustered and non-clustered indexes?

Indexing 4 min read

Clustered indexes determine the physical order of data in a table, while non-clustered indexes are separate structures that point to the data.

Creating Indexes: 
-- Clustered index (only one per table, SQL Server syntax)
CREATE CLUSTERED INDEX idx_employees_id 
ON employees(id);

-- Non-clustered index (multiple per table)
CREATE NONCLUSTERED INDEX idx_employees_email 
ON employees(email);

CREATE NONCLUSTERED INDEX idx_employees_dept_salary 
ON employees(department, salary);

Clustered Index:

  • Determines physical storage order of rows
  • Only one per table (the table IS the clustered index)
  • Faster for range queries (data is physically ordered)
  • Primary key is often the clustered index
  • Slower for inserts (may require data reorganization)
  • Examples: Dictionary organized alphabetically

Non-Clustered Index:

  • Separate structure from table data
  • Contains index columns + pointer to data
  • Multiple can exist per table
  • Faster for exact match lookups
  • Additional storage required
  • Examples: Book index pointing to pages

When to Use Which:

  • Use clustered index for columns frequently used in range queries
  • Use clustered index for columns with sequential values
  • Use non-clustered index for columns in WHERE, JOIN, ORDER BY
  • Use non-clustered index for foreign key columns
  • Avoid too many indexes - each slows down INSERT/UPDATE/DELETE
25
Beginner

What are SQL constraints and what types exist?

Constraints 4 min read

Constraints enforce rules on data columns to maintain data integrity and accuracy.

Common Constraints: 
CREATE TABLE employees (
    -- NOT NULL: Column cannot contain NULL
    id INT NOT NULL,
    
    -- UNIQUE: All values must be different
    email VARCHAR(100) UNIQUE,
    
    -- PRIMARY KEY: Unique identifier for table
    employee_id INT PRIMARY KEY,
    
    -- FOREIGN KEY: Links to another table
    department_id INT,
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(id),
    
    -- CHECK: Validates data based on condition
    salary DECIMAL(10,2) CHECK (salary >= 0),
    age INT CHECK (age >= 18 AND age <= 65),
    
    -- DEFAULT: Sets default value if not specified
    hire_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'active'
);
Adding Constraints After Table Creation: 
-- Add NOT NULL constraint
ALTER TABLE employees MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- Add UNIQUE constraint
ALTER TABLE employees ADD CONSTRAINT uc_email UNIQUE (email);

-- Add CHECK constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary 
    CHECK (salary >= 0);

-- Add FOREIGN KEY constraint
ALTER TABLE employees ADD CONSTRAINT fk_dept
    FOREIGN KEY (department_id) REFERENCES departments(id);

-- Add DEFAULT constraint
ALTER TABLE employees ALTER COLUMN status 
    SET DEFAULT 'active';

Constraint Types:

  • NOT NULL: Column must have a value
  • UNIQUE: All values in column must be different
  • PRIMARY KEY: Unique + NOT NULL, identifies each row
  • FOREIGN KEY: References PRIMARY KEY in another table
  • CHECK: Validates data based on expression
  • DEFAULT: Provides default value for column
  • COMPOSITE PRIMARY KEY: Primary key using multiple columns
  • CASCADE constraints: Automatic updates/deletes on related tables
37
Beginner

What are SQL aliases and why are they used?

Aliases 3 min read

Aliases give temporary names to tables, columns, or expressions, improving query readability and enabling self-joins.

Column Aliases: 
-- Basic column aliases
SELECT 
    first_name || ' ' || last_name AS full_name,
    salary * 12 AS annual_salary,
    department AS dept,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- Aliases with special characters or spaces
SELECT 
    name AS "Employee Name",
    salary AS "Monthly Salary (USD)",
    hire_date AS "Date Hired"
FROM employees;

-- Aliases in ORDER BY
SELECT name, salary * 1.1 AS new_salary
FROM employees
ORDER BY new_salary DESC;
Table Aliases: 
-- Simple table aliases
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Self-join using aliases (essential!)
SELECT 
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Multiple table aliases in complex queries
SELECT 
    c.name AS customer_name,
    o.order_date,
    p.name AS product_name,
    oi.quantity,
    s.name AS supplier_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN suppliers s ON p.supplier_id = s.supplier_id;
Subquery Aliases: 
-- Subquery in FROM clause requires alias
SELECT dept_stats.department, dept_stats.avg_salary
FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary,
        COUNT(*) AS emp_count
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE dept_stats.emp_count > 10;

-- CTE aliases
WITH department_summary AS (
    SELECT 
        department,
        AVG(salary) AS avg_sal,
        MAX(salary) AS max_sal
    FROM employees
    GROUP BY department
)
SELECT * FROM department_summary
WHERE avg_sal > 60000;

Benefits of Aliases:

  • Improves query readability
  • Enables self-joins (same table referenced twice)
  • Shortens long table names
  • Provides meaningful names for expressions
  • Required for derived tables and some subqueries
  • Helps avoid column name conflicts
43
Beginner

What are SQL data types and their common uses?

Data Types 5 min read

SQL data types define what kind of data a column can hold. Choosing the right type affects storage, performance, and data integrity.

Numeric Data Types: 
CREATE TABLE example_numbers (
    -- Integer types
    id INT PRIMARY KEY,              -- 4 bytes, -2B to 2B
    small_id SMALLINT,               -- 2 bytes, -32K to 32K
    big_id BIGINT,                   -- 8 bytes, huge range
    
    -- Exact numeric (decimal)
    price DECIMAL(10, 2),            -- 10 total digits, 2 decimal places
    precise NUMERIC(15, 5),          -- Same as DECIMAL
    
    -- Floating point (approximate)
    weight FLOAT,                    -- 4 bytes, ~7 digits precision
    measurement DOUBLE PRECISION,    -- 8 bytes, ~15 digits precision
    rating REAL                      -- 4 bytes, ~6 digits precision
    
    -- Specialized
    serial_id SERIAL,                -- Auto-incrementing integer (PostgreSQL)
    money_amount MONEY               -- Currency type (SQL Server/PostgreSQL)
);
Character/String Data Types: 
CREATE TABLE example_strings (
    -- Fixed length (padded with spaces)
    country_code CHAR(2),            -- Exactly 2 characters
    ssn CHAR(9),                     -- Exactly 9 characters
    
    -- Variable length
    name VARCHAR(100),               -- Max 100 characters
    description TEXT,                -- Very large text (unlimited in some DBs)
    
    -- Unicode support
    unicode_name NVARCHAR(100),      -- SQL Server unicode
    postgres_unicode VARCHAR(100),   -- PostgreSQL (always unicode)
    
    -- Specialized
    email VARCHAR(255) CHECK (email LIKE '%@%'),
    url VARCHAR(2083),               -- Max URL length
    json_data JSON,                  -- JSON document (PostgreSQL/MySQL 5.7+)
    xml_data XML                     -- XML document
);
Date/Time Data Types: 
CREATE TABLE example_dates (
    -- Date only
    birth_date DATE,                 -- '2023-12-25'
    
    -- Time only
    meeting_time TIME,               -- '14:30:00'
    meeting_time_tz TIME WITH TIME ZONE, -- '14:30:00-05'
    
    -- Date and time
    created_at TIMESTAMP,            -- '2023-12-25 14:30:00'
    updated_at TIMESTAMPTZ,          -- With timezone
    
    -- Specialized
    appointment TIMESTAMP(0),        -- No fractional seconds
    interval_field INTERVAL,         -- Time interval '1 day 2 hours'
    
    -- Database-specific
    year_field YEAR,                 -- MySQL only
    datetime_field DATETIME,         -- MySQL/SQL Server
    smalldatetime_field SMALLDATETIME -- SQL Server
);

Best Practices:

  • Use smallest data type that fits your data
  • Use VARCHAR instead of CHAR for variable-length strings
  • Use DECIMAL for financial data (exact precision)
  • Use TIMESTAMP WITH TIME ZONE for timezone-aware dates
  • Consider storage requirements and performance implications
  • Use appropriate constraints (CHECK, NOT NULL) with data types

Browse other topics

Joins Queries Functions Performance Advanced