SQL interview questions for beginners, covering fundamental concepts

Basic SQL Concepts

1. What is SQL and what are its main components?

Answer:
SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. Its main components are:

• DDL (Data Definition Language): Defines database structure

  • CREATE, ALTER, DROP, TRUNCATE

• DML (Data Manipulation Language): Manages data within tables

  • SELECT, INSERT, UPDATE, DELETE

• DCL (Data Control Language): Controls access to data

  • GRANT, REVOKE

• TCL (Transaction Control Language): Manages transactions

  • COMMIT, ROLLBACK, SAVEPOINT

2. What are the different types of SQL commands?

Answer:

• Data Definition Language (DDL): Creates/modifies database structure

• Data Manipulation Language (DML): Works with actual data

• Data Control Language (DCL): Manages permissions

• Transaction Control Language (TCL): Controls transactions

• Data Query Language (DQL): Retrieves data (mainly SELECT)

3. What is the difference between DELETE, TRUNCATE, and DROP?

Answer:

• DELETE: Removes specific rows from a table. Can be rolled back. Uses WHERE clause.

• TRUNCATE: Removes all rows from a table. Faster than DELETE. Cannot be rolled back.

• DROP: Deletes entire table structure and data. Cannot be recovered.

sql

DELETE FROM employees WHERE id = 101;  -- Deletes only row with id 101
TRUNCATE TABLE employees;              -- Deletes all rows, keeps table
DROP TABLE employees;                  -- Deletes entire table

Basic Queries

4. How do you retrieve all data from a table?

Answer:
Use the SELECT statement with * (wildcard):

sql

SELECT * FROM customers;

This returns all columns and all rows from the customers table.

5. How do you retrieve specific columns from a table?

Answer:
List the column names after SELECT:

sql

SELECT first_name, last_name, email FROM customers;

6. What is the WHERE clause used for?

Answer:
The WHERE clause filters records based on specified conditions:

sql

SELECT * FROM employees WHERE department = 'Sales';
SELECT * FROM products WHERE price > 100;
SELECT * FROM orders WHERE order_date >= '2024-01-01';

7. What are the different types of operators in SQL WHERE clause?

Answer:

• Comparison: =, <> or !=, >, <, >=, <=

• Logical: AND, OR, NOT

• Range: BETWEEN

• Pattern Matching: LIKE

• Set Membership: IN

sql

SELECT * FROM employees 
WHERE salary BETWEEN 50000 AND 80000 
AND department IN ('Sales', 'Marketing');

Sorting and Limiting Results

8. How do you sort query results?

Answer:
Use the ORDER BY clause:

sql

SELECT * FROM products ORDER BY price DESC;        -- Highest to lowest
SELECT name, salary FROM employees ORDER BY salary ASC;  -- Lowest to highest
SELECT * FROM customers ORDER BY last_name, first_name;  -- Multiple columns

9. How do you limit the number of results returned?

Answer:
Use LIMIT (MySQL, PostgreSQL) or TOP (SQL Server) or ROWNUM (Oracle):

sql

-- MySQL, PostgreSQL
SELECT * FROM products LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM products;

-- Oracle
SELECT * FROM products WHERE ROWNUM <= 10;

10. How do you remove duplicate values from results?

Answer:
Use the DISTINCT keyword:

sql

SELECT DISTINCT department FROM employees;
SELECT DISTINCT city, country FROM customers;

Data Modification

11. How do you insert data into a table?

Answer:
Use the INSERT INTO statement:

sql

-- Insert specific columns
INSERT INTO employees (first_name, last_name, department) 
VALUES ('John', 'Doe', 'Sales');

-- Insert all columns (must provide values for all)
INSERT INTO employees VALUES (1, 'John', 'Doe', 'Sales', 50000);

12. How do you update existing data in a table?

Answer:
Use the UPDATE statement with SET and WHERE:

sql

UPDATE employees 
SET salary = 55000 
WHERE id = 101;

UPDATE products 
SET price = price * 1.1  -- Increase price by 10%
WHERE category = 'Electronics';

13. How do you delete specific records from a table?

Answer:
Use the DELETE statement with WHERE clause:

sql

DELETE FROM customers WHERE id = 205;
DELETE FROM orders WHERE order_date < '2023-01-01';

⚠️ Important: Always use WHERE clause with DELETE, or you'll delete all records!

Table Operations

14. How do you create a new table?

Answer:
Use the CREATE TABLE statement:

sql

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

15. What are SQL constraints and can you name some common ones?

Answer:
Constraints enforce rules on data in tables. Common constraints:

• PRIMARY KEY: Uniquely identifies each record

• FOREIGN KEY: Links two tables together

• NOT NULL: Ensures column cannot have NULL value

• UNIQUE: Ensures all values in column are different

• CHECK: Ensures values meet specific conditions

• DEFAULT: Sets a default value for column

sql

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category VARCHAR(50) DEFAULT 'General',
    created_date DATE DEFAULT CURRENT_DATE
);

16. How do you add a new column to an existing table?

Answer:
Use the ALTER TABLE statement with ADD COLUMN:

sql

ALTER TABLE employees ADD COLUMN phone_number VARCHAR(15);
ALTER TABLE products ADD COLUMN discount DECIMAL(5,2) DEFAULT 0;

Advanced Basics

17. What are SQL functions and can you give examples?

Answer:
SQL functions perform operations on data. Common types:

Aggregate Functions:

sql

SELECT COUNT(*) FROM employees;                    -- Count all rows
SELECT AVG(salary) FROM employees;                 -- Average salary
SELECT MAX(price) FROM products;                   -- Highest price
SELECT MIN(price) FROM products;                   -- Lowest price
SELECT SUM(quantity) FROM order_items;             -- Total quantity

String Functions:

sql

SELECT UPPER(name) FROM customers;                 -- Convert to uppercase
SELECT LOWER(email) FROM users;                    -- Convert to lowercase
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT LENGTH(address) FROM customers;             -- Length of string

18. What is the GROUP BY clause used for?

Answer:
GROUP BY groups rows that have the same values into summary rows, often used with aggregate functions:

sql

SELECT department, COUNT(*) as employee_count
FROM employees 
GROUP BY department;

SELECT category, AVG(price) as average_price
FROM products 
GROUP BY category;

19. What is the HAVING clause and how is it different from WHERE?

Answer:

• WHERE: Filters rows before grouping

• HAVING: Filters groups after grouping

sql

-- WHERE filters individual rows
SELECT department, AVG(salary) 
FROM employees 
WHERE salary > 30000 
GROUP BY department;

-- HAVING filters groups
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 50000;

20. How do you combine results from multiple queries?

Answer:
Use UNION or UNION ALL:

sql

-- Get all cities from both tables (removes duplicates)
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- Get all cities including duplicates
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Difference: UNION removes duplicates, UNION ALL keeps duplicates.

Practical Examples

Let's see some of these concepts in action with a complete example:

sql

-- Create a sample table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    major VARCHAR(100),
    gpa DECIMAL(3,2),
    graduation_year INT
);

-- Insert data
INSERT INTO students VALUES 
(1, 'Alice', 'Johnson', 'Computer Science', 3.8, 2024),
(2, 'Bob', 'Smith', 'Mathematics', 3.5, 2024),
(3, 'Charlie', 'Brown', 'Computer Science', 3.9, 2025),
(4, 'Diana', 'Lee', 'Physics', 3.7, 2024);

-- Basic queries
SELECT * FROM students WHERE graduation_year = 2024;
SELECT major, AVG(gpa) as avg_gpa FROM students GROUP BY major;
SELECT * FROM students WHERE gpa > 3.5 ORDER BY gpa DESC;

These questions cover the fundamental SQL concepts that every beginner should understand. Practice writing these queries and understanding the logic behind them to build a strong foundation for more advanced SQL topics!