Backend interview prep

Start

SQL Basics Interview Questions

Master SQL fundamentals with interview-focused questions covering SQL categories, DDL, DML, DQL, DCL, TCL, tables, keys, constraints, NULL handling, data types, and enterprise database best practices.

Introduction

SQL (Structured Query Language) is the standard language used to communicate with relational databases.

Almost every enterprise application uses SQL to

  • Store Data
  • Retrieve Data
  • Update Records
  • Delete Records
  • Manage Transactions
  • Secure Databases

Popular SQL databases include

  • MySQL
  • PostgreSQL
  • Oracle
  • Microsoft SQL Server
  • MariaDB
  • IBM Db2

SQL is one of the most frequently asked topics in

  • Java Interviews
  • Backend Interviews
  • Database Interviews
  • Solution Architect Interviews
  • DevOps Interviews

SQL Architecture

flowchart LR

Application --> SqlQuery["SQL Query"]

SqlQuery["SQL Query"] --> DatabaseEngine["Database Engine"]

DatabaseEngine["Database Engine"] --> Tables

Tables --> Rows

1. What is SQL?

Answer

SQL (Structured Query Language) is a standard language used to interact with relational databases.

SQL is used to

  • Create Databases
  • Create Tables
  • Insert Data
  • Retrieve Data
  • Update Data
  • Delete Data
  • Manage Users
  • Control Transactions

2. Why is SQL important?

SQL allows applications to

  • Store Business Data
  • Retrieve Information
  • Generate Reports
  • Maintain Data Integrity
  • Support Transactions

Without SQL,

most enterprise applications cannot function.

3. What are the main SQL categories?

SQL commands are divided into five categories.

Category Purpose
DDL Data Definition Language
DML Data Manipulation Language
DQL Data Query Language
DCL Data Control Language
TCL Transaction Control Language

SQL Categories

SQL

├── DDL
├── DML
├── DQL
├── DCL
└── TCL

4. What is DDL?

DDL (Data Definition Language)

is used to define database objects.

Commands include

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • RENAME

DDL Example

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

5. What is DML?

DML (Data Manipulation Language)

is used to modify table data.

Commands include

  • INSERT
  • UPDATE
  • DELETE
  • MERGE (supported by some databases)

DML Example

INSERT INTO Employee(id, name, salary)
VALUES (1, 'John', 50000);

6. What is DQL?

DQL (Data Query Language)

is used to retrieve data.

Main command

SELECT

DQL Example

SELECT *
FROM Employee;

7. What is DCL?

DCL (Data Control Language)

controls database permissions.

Commands include

  • GRANT
  • REVOKE

DCL Example

GRANT SELECT
ON Employee
TO app_user;

8. What is TCL?

TCL (Transaction Control Language)

manages transactions.

Commands include

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

TCL Example

BEGIN;

UPDATE Employee
SET salary = salary + 1000
WHERE id = 1;

COMMIT;

9. What is a Database?

A Database is an organized collection of related data.

Example

Company Database

↓

Employees

Departments

Projects

Payroll

10. What is a Table?

A Table stores related data in

  • Rows
  • Columns

Example

id name salary
1 John 50000
2 Alice 60000

Table Structure

Employee

+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
| 1  | John  | 50000  |
| 2  | Alice | 60000  |
+----+-------+--------+

11. What is a Row?

A Row represents

one complete record.

Example

1

John

50000

One employee.

12. What is a Column?

A Column represents

one attribute.

Example

salary

stores salary for all employees.

13. What is a Primary Key?

A Primary Key uniquely identifies every row.

Properties

  • Unique
  • Not NULL
  • One per table

Primary Key Example

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

14. What is a Foreign Key?

A Foreign Key establishes a relationship between two tables.

Foreign Key Example

CREATE TABLE Department (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id)
    REFERENCES Department(id)
);

Foreign Key Relationship

flowchart LR

Department --> Employee

15. What are Constraints?

Constraints enforce rules on data.

Common constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK
  • DEFAULT

16. What is NOT NULL?

Ensures

a column

cannot contain

NULL values.

Example

name VARCHAR(100) NOT NULL

17. What is UNIQUE?

Ensures

duplicate values

are not allowed.

Example

email VARCHAR(255) UNIQUE

18. What is DEFAULT?

Automatically assigns

a default value.

Example

status VARCHAR(20)
DEFAULT 'ACTIVE'

19. What is CHECK?

Restricts values

based on a condition.

Example

salary DECIMAL(10,2)
CHECK (salary > 0)

20. What is NULL?

NULL means

missing

or

unknown value.

It is not

  • Zero
  • Empty String
  • False

NULL Example

Employee

Phone Number

NULL

↓

Unknown

21. What are SQL Data Types?

Common data types

Numeric

  • INT
  • BIGINT
  • DECIMAL
  • FLOAT

Character

  • CHAR
  • VARCHAR
  • TEXT

Date

  • DATE
  • TIME
  • TIMESTAMP

Boolean

  • BOOLEAN

22. Difference between CHAR and VARCHAR?

CHAR VARCHAR
Fixed Length Variable Length
Faster for Fixed Data Saves Space
Pads Extra Spaces No Padding

23. Banking Example

Accounts Table

Account Number

Customer

Balance

Status

24. E-Commerce Example

Products

Product ID

Name

Price

Stock

25. Healthcare Example

Patients

Patient ID

Name

DOB

Blood Group

26. Social Media Example

Users

User ID

Username

Email

Followers

27. Production Example

HR Database

Employees

Departments

Payroll

Attendance

Benefits

All connected using

Foreign Keys.

28. Common SQL Mistakes

  • Forgetting WHERE in UPDATE
  • Forgetting WHERE in DELETE
  • Using SELECT *
  • Ignoring NULL values
  • Poor table design

29. SQL Best Practices

  • Use meaningful table names.
  • Use Primary Keys.
  • Define Foreign Keys.
  • Normalize data.
  • Use appropriate data types.
  • Avoid SELECT * in production.
  • Always use transactions for critical updates.
  • Validate constraints.
  • Index frequently searched columns.
  • Follow naming conventions.

30. Real-World SQL Workflow

flowchart LR

Application --> SqlQuery["SQL Query"]

SqlQuery["SQL Query"] --> Database

Database --> Table

Table --> Rows

Rows --> Response

Enterprise Best Practices

  • Always define Primary Keys.
  • Use Foreign Keys to maintain referential integrity.
  • Normalize the database before optimization.
  • Use constraints to enforce data quality.
  • Choose the smallest suitable data type.
  • Use transactions for business-critical operations.
  • Secure access with GRANT and REVOKE.
  • Backup databases regularly.
  • Monitor query performance.
  • Document database schemas.

Quick Revision

Topic Key Point
SQL Structured Query Language
DDL Create Database Objects
DML Modify Data
DQL Retrieve Data
DCL Manage Permissions
TCL Manage Transactions
Table Collection of Rows
Row Single Record
Column Attribute
Primary Key Unique Identifier
Foreign Key Table Relationship
Constraints Data Validation
NULL Unknown Value
VARCHAR Variable Length
CHAR Fixed Length

Interview Tips

Interviewers frequently ask

  • What is SQL?
  • What are SQL categories?
  • DDL vs DML.
  • DML vs DQL.
  • DCL vs TCL.
  • Primary Key vs Foreign Key.
  • What is NULL?
  • CHAR vs VARCHAR.
  • What are constraints?
  • Explain the SQL execution workflow.

A strong interview explanation is:

"SQL is the standard language for interacting with relational databases. It includes DDL for defining database objects, DML for manipulating data, DQL for querying data, DCL for managing permissions, and TCL for controlling transactions. SQL also provides mechanisms such as primary keys, foreign keys, constraints, and transactions to ensure data integrity, consistency, and reliability in enterprise applications."

Summary

SQL is the foundation of relational database management and is essential for storing, retrieving, updating, and securing business data. Understanding SQL fundamentals—including DDL, DML, DQL, DCL, TCL, Primary Keys, Foreign Keys, Constraints, NULL handling, and data types—is critical for every Backend Developer, Java Developer, Database Engineer, DevOps Engineer, and Solution Architect.

Mastering these basics provides the foundation for advanced topics such as Joins, Indexes, Transactions, Window Functions, Query Optimization, and Performance Tuning.