SQL Interview Questions Advanced

SQL Joins & Relationships

1. What are SQL joins and why are they important?

Answer:
SQL joins combine rows from two or more tables based on related columns. They're essential for working with normalized databases where data is spread across multiple tables.

sql

-- Basic join syntax
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;

2. What are the different types of SQL joins?

Answer:

• INNER JOIN: Returns only matching rows from both tables

• LEFT JOIN: Returns all rows from left table + matching rows from right table

• RIGHT JOIN: Returns all rows from right table + matching rows from left table

• FULL OUTER JOIN: Returns all rows when there's a match in either table

• CROSS JOIN: Returns Cartesian product (all possible combinations)

3. What is the difference between INNER JOIN and LEFT JOIN?

Answer:

• INNER JOIN: Only returns rows where there's a match in both tables

• LEFT JOIN: Returns all rows from the left table, even if no match exists in right table (NULL values for non-matching rows)

sql

-- Sample tables: employees and departments
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- Only employees with departments

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- All employees, even those without departments

4. How do you join three or more tables?

Answer:
Chain multiple JOIN clauses:

sql

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

Data Filtering & Pattern Matching

5. What is the LIKE operator and how do you use it?

Answer:
LIKE is used for pattern matching in WHERE clauses with wildcards:

• % - Matches any sequence of characters

• _ - Matches any single character

sql

SELECT * FROM customers WHERE name LIKE 'A%';     -- Names starting with A
SELECT * FROM products WHERE name LIKE '%phone%'; -- Names containing 'phone'
SELECT * FROM employees WHERE email LIKE '%@gmail.com'; -- Gmail addresses
SELECT * FROM users WHERE username LIKE 'john_';  -- 5-letter names starting with 'john'

6. What is the IN operator used for?

Answer:
IN allows you to specify multiple values in a WHERE clause:

sql

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing', 'HR');
SELECT * FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE active = 1);
SELECT * FROM orders WHERE status IN ('Shipped', 'Delivered');

This is equivalent to multiple OR conditions but more readable.

7. What is the BETWEEN operator?

Answer:
BETWEEN selects values within a given range (inclusive):

sql

SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM orders WHERE quantity BETWEEN 5 AND 20;

8. How do you handle NULL values in SQL?

Answer:
Use IS NULL or IS NOT NULL to check for NULL values:

sql

SELECT * FROM customers WHERE phone_number IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;
SELECT * FROM products WHERE description IS NULL OR description = '';

Data Aggregation

9. What is the difference between COUNT(*) and COUNT(column_name)?

Answer:

• COUNT(*) counts all rows, including those with NULL values

• COUNT(column_name) counts only non-NULL values in that column

sql

SELECT COUNT(*) FROM employees;                    -- Total employees
SELECT COUNT(manager_id) FROM employees;          -- Employees with managers
SELECT COUNT(DISTINCT department) FROM employees; -- Unique departments

10. How do you use the HAVING clause with examples?

Answer:
HAVING filters groups after GROUP BY, while WHERE filters rows before grouping:

sql

-- Find departments with more than 5 employees
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Find product categories with average price > $50
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

-- Customers with more than 3 orders
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

11. What is the difference between WHERE and HAVING?

Answer:

• WHERE: Filters individual rows BEFORE grouping

• HAVING: Filters groups AFTER grouping

• WHERE can't use aggregate functions, HAVING can

sql

-- Correct: HAVING with aggregate
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

-- Incorrect: WHERE with aggregate (will cause error)
SELECT department, AVG(salary) 
FROM employees 
WHERE AVG(salary) > 50000  -- ERROR!
GROUP BY department;

Table Operations & Maintenance

12. How do you modify an existing column in a table?

Answer:
Use ALTER TABLE with ALTER COLUMN or MODIFY COLUMN:

sql

-- SQL Server, MySQL
ALTER TABLE employees MODIFY COLUMN phone_number VARCHAR(20);

-- PostgreSQL
ALTER TABLE employees ALTER COLUMN phone_number TYPE VARCHAR(20);

-- Add NOT NULL constraint
ALTER TABLE employees MODIFY email VARCHAR(100) NOT NULL;

13. How do you delete a column from a table?

Answer:
Use ALTER TABLE with DROP COLUMN:

sql

ALTER TABLE employees DROP COLUMN temporary_code;
ALTER TABLE products DROP COLUMN old_category;

14. How do you rename a table or column?

Answer:
Rename table:

sql

-- MySQL
RENAME TABLE old_name TO new_name;

-- SQL Server
EXEC sp_rename 'old_name', 'new_name';

-- PostgreSQL
ALTER TABLE old_name RENAME TO new_name;

Rename column:

sql

-- MySQL
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- SQL Server
EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUMN';

Data Types & Constraints

15. What are common SQL data types?

Answer:

• String: VARCHAR(n), CHAR(n), TEXT

• Numeric: INT, DECIMAL(p,s), FLOAT, DOUBLE

• Date/Time: DATE, TIME, DATETIME, TIMESTAMP

• Boolean: BOOLEAN (or BIT in some databases)

sql

CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT,
    salary DECIMAL(10,2),
    birth_date DATE,
    created_at TIMESTAMP,
    is_active BOOLEAN
);

16. What is a PRIMARY KEY and why is it important?

Answer:
A PRIMARY KEY:

• Uniquely identifies each record in a table

• Cannot contain NULL values

• Must contain unique values

• Only one primary key per table

sql

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Or with auto-increment (common for primary keys)
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

17. What is a FOREIGN KEY?

Answer:
A FOREIGN KEY creates a relationship between two tables. It points to the PRIMARY KEY in another table.

sql

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Practical Scenarios & Problem Solving

18. How do you find duplicate records in a table?

Answer:
Use GROUP BY and HAVING to find duplicates:

sql

-- Find duplicate emails in users table
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Find duplicate names with other criteria
SELECT first_name, last_name, COUNT(*)
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

19. How do you calculate the total and average from a table?

Answer:
Use aggregate functions:

sql

-- Calculate total sales
SELECT SUM(amount) as total_sales FROM sales;

-- Calculate average salary by department
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- Multiple aggregates at once
SELECT 
    COUNT(*) as total_employees,
    AVG(salary) as avg_salary,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary
FROM employees;

20. How do you use CASE statements in SQL?

Answer:
CASE allows conditional logic in SQL queries:

sql

-- Categorize employees by salary
SELECT name, salary,
    CASE 
        WHEN salary < 30000 THEN 'Junior'
        WHEN salary BETWEEN 30000 AND 60000 THEN 'Mid-level'
        WHEN salary > 60000 THEN 'Senior'
        ELSE 'Unknown'
    END as level
FROM employees;

-- Flag active/inactive products
SELECT product_name, quantity,
    CASE 
        WHEN quantity > 0 THEN 'In Stock'
        ELSE 'Out of Stock'
    END as stock_status
FROM products;

-- Calculate bonus based on performance
SELECT employee_name, performance_score,
    CASE 
        WHEN performance_score >= 90 THEN salary * 0.2
        WHEN performance_score >= 80 THEN salary * 0.1
        ELSE salary * 0.05
    END as bonus
FROM employees;

Comprehensive Example

Let's put several concepts together with a practical example:

sql

-- Create sample tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insert sample data
INSERT INTO customers VALUES 
(1, 'John Smith', 'New York', 'USA'),
(2, 'Maria Garcia', 'Madrid', 'Spain'),
(3, 'Wei Zhang', 'Beijing', 'China'),
(4, 'Anna Müller', 'Berlin', 'Germany');

INSERT INTO orders VALUES 
(101, 1, '2024-01-15', 250.00),
(102, 1, '2024-02-20', 180.50),
(103, 2, '2024-01-22', 320.75),
(104, 3, '2024-03-10', 95.00);

-- Complex query using multiple concepts
SELECT 
    c.customer_name,
    c.country,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as total_spent,
    CASE 
        WHEN SUM(o.total_amount) > 300 THEN 'VIP'
        WHEN SUM(o.total_amount) > 100 THEN 'Regular'
        ELSE 'New'
    END as customer_type
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country IN ('USA', 'Spain', 'Germany')
GROUP BY c.customer_id, c.customer_name, c.country
HAVING COUNT(o.order_id) > 0
ORDER BY total_spent DESC;

These additional questions build upon the foundational knowledge and introduce more practical, real-world SQL scenarios that beginners will encounter in their work. Practice these concepts to become comfortable with the most common SQL operations!