Chapter 01 of 35

Introduction to SQL

Every app you use daily stores data somewhere. Your Swiggy order history, your IRCTC bookings, your college's attendance records, the list of videos you liked on YouTube — all of it lives in a database.

Storing data is only half the job. Someone also has to ask questions about that data: "Which orders are still pending?", "Who are the top 5 earners in the Sales team?", "How much revenue did we make in March?"

The language used to ask these questions — and to create, change and protect the data — is SQL. It is one of the oldest programming languages still in heavy use, and it shows up in almost every placement interview for software, data and analyst roles.

In this chapter we will understand what SQL is, why it looks so different from C or Java, which "flavours" of SQL exist, and how this tutorial is organised. By the end you will have read (and understood) your first few queries.


What is SQL?

SQL (Structured Query Language) is a standard language for defining, storing, changing, querying and controlling data in a relational database.

You can pronounce it either as "S-Q-L" (letter by letter) or as "sequel". Both are common, and both are correct in interviews.

A relational database stores data in tables. A table looks a lot like a spreadsheet:

  • Each column has a name and a type, for example dept_name holds text.

  • Each row is one record, for example one department.

Here is a real table from the sample database we use throughout this tutorial, and the SQL that fetches it:

SELECT * FROM departments;
+---------+-------------+-----------+
| dept_id | dept_name   | location  |
+---------+-------------+-----------+
|      10 | Engineering | Bengaluru |
|      20 | Sales       | Mumbai    |
|      30 | HR          | Delhi     |
|      40 | Marketing   | Pune      |
|      50 | Research    | Hyderabad |
+---------+-------------+-----------+

Read the query out loud: "select everything (*) from the departments table". That is SQL in a nutshell — it reads almost like an English sentence.

The software that actually stores the tables and runs your SQL is called a DBMS (Database Management System), or more precisely an RDBMS (Relational DBMS). MySQL, PostgreSQL, Oracle, SQL Server and SQLite are all RDBMSs. SQL is the language you use to talk to them.

   You / your app                       RDBMS (e.g. MySQL)
 ┌──────────────────┐   SQL query    ┌─────────────────────────┐
 │ SELECT name ...  │ ─────────────→ │  parse → plan → execute │
 │                  │ ←───────────── │  tables stored on disk  │
 └──────────────────┘  result rows   └─────────────────────────┘

Why is SQL Different? It is Declarative

In C, Java or Python you write how to do something: loop over this, check that, append to a list. SQL works the other way round. You describe what result you want, and the database figures out how to get it.

Suppose our data were in a plain Python list and we wanted the names and salaries of everyone in department 10. We would write a loop:

result = []
for emp in employees:              # walk through every employee
    if emp["dept_id"] == 10:       # keep only department 10
        result.append((emp["name"], emp["salary"]))

In SQL we just state the result we want:

SELECT name, salary
FROM employees
WHERE dept_id = 10;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
+--------------+-----------+

Notice what is missing from the SQL version: there is no loop, no counter, no "if", and no instruction about where the data lives on disk.

Why does this matter? Because the database has a component called the query optimizer. It looks at your query, the size of the tables and the indexes available, and chooses the fastest strategy. If the table has ten rows, it may just scan them all. If it has ten crore rows and an index on dept_id, it can jump straight to the matching rows. Your query stays exactly the same — only the plan changes. (We will see indexes in detail in the chapter Indexes.)

Declarative means you describe the result, not the steps. This is why one short SQL query can replace many lines of loop-based code, and why the same query keeps working as the data grows.


A Short History (Just What You Need)

  • 1970 – E. F. Codd at IBM publishes the relational model: store data as relations (tables) and query it using mathematics, not pointers.

  • Mid-1970s – IBM researchers build a language for it called SEQUEL, later renamed SQL. This is where the "sequel" pronunciation comes from.

  • 1986–87 – SQL becomes an official ANSI and ISO standard. The standard has been revised many times since (SQL-92, SQL:1999, SQL:2003 ... SQL:2023).

  • 1995 – MySQL is first released. Today it is owned by Oracle and is one of the most widely used open-source databases in the world.

The important takeaway: SQL is a standard, so the core of the language is the same everywhere.


SQL Dialects: MySQL, PostgreSQL, Oracle, SQL Server

Every database vendor implements the standard, and then adds its own extras. These variations are called dialects. About 90% of what you learn — SELECT, WHERE, JOIN, GROUP BY, INSERT, UPDATE — works the same everywhere. The differences are mostly in small details.

A classic example is "give me only the first 3 rows":

Database

How to get the first 3 rows

MySQL, PostgreSQL, SQLite

SELECT ... LIMIT 3

SQL Server

SELECT TOP 3 ...

Oracle (12c and later), PostgreSQL, SQL standard

SELECT ... FETCH FIRST 3 ROWS ONLY

Some other names you will hear:

  • T-SQL – Microsoft SQL Server's dialect, with its own procedural extensions.

  • PL/SQL – Oracle's procedural language built around SQL.

  • PL/pgSQL – PostgreSQL's procedural language.

Why This Tutorial Uses MySQL

  • It is free, open source and runs on Windows, Linux and macOS.

  • It is extremely common in Indian companies, startups and college labs.

  • Most online SQL practice platforms and interview rounds accept MySQL syntax.

  • Its syntax is close to the standard, so moving to PostgreSQL or Oracle later is easy.

Every query and every output in this tutorial was actually run on MySQL 8.4, a long-term-support (LTS) release that is very widely installed. If you use MySQL 8.0 or a newer release such as 9.7 LTS, the results will be the same for almost everything we do.


The Five Sub-Languages of SQL

SQL commands are traditionally grouped by what they do. This grouping is a very common interview and university exam question.

Category

Full form

Purpose

Main commands

DDL

Data Definition Language

Create and change the structure (databases, tables, columns)

CREATE, ALTER, DROP, TRUNCATE, RENAME

DML

Data Manipulation Language

Add, change and remove the rows

INSERT, UPDATE, DELETE

DQL

Data Query Language

Read data

SELECT

DCL

Data Control Language

Give or take away permissions

GRANT, REVOKE

TCL

Transaction Control Language

Group changes into all-or-nothing units

COMMIT, ROLLBACK, SAVEPOINT, START TRANSACTION

A small example of each, using our sample tables:

-- DDL: define a new table (structure only)
CREATE TABLE skills (skill_id INT PRIMARY KEY, skill_name VARCHAR(50));

-- DML: add / change / remove rows
INSERT INTO skills VALUES (1, 'SQL');
UPDATE skills SET skill_name = 'MySQL' WHERE skill_id = 1;
DELETE FROM skills WHERE skill_id = 1;

-- DQL: read rows
SELECT name, city FROM employees WHERE salary > 90000;

-- DCL: permissions
GRANT SELECT ON company_db.* TO 'intern'@'localhost';   -- the user must already exist

-- TCL: all-or-nothing changes
START TRANSACTION;
UPDATE employees SET salary = salary + 5000 WHERE dept_id = 30;
COMMIT;

You do not need to run these yet. Each one gets its own chapter later in the tutorial.

Note: Many textbooks do not have a separate DQL group and simply put SELECT inside DML. If an exam asks for "four types of SQL commands", the expected answer is usually DDL, DML, DCL and TCL. Also remember that TRUNCATE is DDL, not DML, even though it removes rows — we will see why in ALTER, DROP & TRUNCATE.


What Happens When You Run a Query?

You do not need to know the internals to write SQL, but having a rough picture helps you understand error messages and performance later.

SELECT name FROM employees WHERE dept_id = 10;
        │
        ▼
1. Parser      → Is the syntax valid? Do the table and columns exist?
        │          (spelling mistakes are caught here)
        ▼
2. Optimizer   → Which plan is cheapest? Full scan or use an index?
        │
        ▼
3. Executor    → Runs the plan, asks the storage engine for rows
        │
        ▼
4. Storage engine (InnoDB in MySQL) → reads pages from memory or disk
        │
        ▼
Result rows are sent back to you

In MySQL, the default storage engine is InnoDB. It is the part that actually stores rows on disk, enforces foreign keys and supports transactions.


Basic Syntax Rules

Before writing queries, learn these small rules. Most beginner errors come from them.

1. Statements End With a Semicolon

The semicolon ; tells the MySQL client "my statement is complete, run it". A statement can span many lines — whitespace and line breaks do not matter.

SELECT name, city, salary
FROM employees
WHERE salary > 90000
ORDER BY salary DESC;
+--------------+-----------+-----------+
| name         | city      | salary    |
+--------------+-----------+-----------+
| Aarav Sharma | Bengaluru | 150000.00 |
| Sneha Kapoor | Mumbai    | 110000.00 |
| Priya Iyer   | Chennai   |  95000.00 |
| Rohan Mehta  | Bengaluru |  95000.00 |
+--------------+-----------+-----------+

2. Keywords Are Not Case-Sensitive

SELECT, select and SeLeCt are all the same. By convention we write keywords in UPPERCASE and table/column names in lowercase, so queries are easy to read. Column names are also case-insensitive in MySQL.

3. Table Names May Be Case-Sensitive

This one surprises people. In MySQL, each database is a folder and each table is a file on disk, so whether table names are case-sensitive depends on the operating system:

  • Linux – case-sensitive by default (employees and EMPLOYEES are different).

  • Windows and macOS – case-insensitive by default.

Here is what happens on a Linux server:

select NAME from EMPLOYEES where EMP_ID = 3;
ERROR 1146 (42S02): Table 'company_db.EMPLOYEES' doesn't exist

The keywords and column names were fine — only the table name failed. Since most production servers run Linux, a query that worked on your Windows laptop can break after deployment. Always write table names exactly as they were created (we use lowercase everywhere).

4. Text Goes in Single Quotes

SELECT name FROM employees WHERE name = 'aarav sharma';
+--------------+
| name         |
+--------------+
| Aarav Sharma |
+--------------+

Two things to notice:

  • Strings use single quotes '...'. That is the SQL standard; double quotes mean something else in other databases, so build the single-quote habit now.

  • The match worked even though we typed lowercase. MySQL's default collation (utf8mb4_0900_ai_ci) compares text case-insensitively — the ci stands for "case-insensitive". PostgreSQL and Oracle compare case-sensitively, so do not rely on this when switching databases.

5. Comments

-- a single-line comment (note the space after the two dashes)
# also a single-line comment, MySQL only
/* a comment that can
   span many lines */
SELECT dept_name FROM departments;  -- comment at the end of a line

In MySQL, -- must be followed by a space (or a tab/newline) to start a comment.


The Sample Database: company_db

Learning SQL is much easier when every example uses the same familiar data. So the whole tutorial uses one small database called company_db with four tables:

Table

Rows

What it stores

departments

5

Department id, name and office location. Research has no employees yet.

employees

10

Name, email, department, manager, salary, hire date and city of each employee.

customers

5

Customers who buy from the company. One of them has never placed an order.

orders

8

Orders with date, amount and status. One is a guest order with no customer.

The tables are connected to each other:

departments                employees                       customers            orders
───────────                ─────────                       ─────────            ──────
dept_id  ◄──────────────── dept_id                         customer_id ◄─────── customer_id
dept_name                  emp_id   ◄───┐                  name                 order_id
location                   manager_id ──┘ (a manager is    city                 order_date
                           name, email,    also an         signup_date          amount
                           salary, ...     employee)                            status

Some rows are deliberately "awkward" — an employee with no department, an employee with no email, a department with no employees, a customer with no orders and an order with no customer. These edge cases are exactly what interviewers love to ask about, and they make the results of joins and NULL handling interesting later on.

In the next chapter, Setting Up MySQL, you will install MySQL and load this database with a single script.


A Taste of What You Will Be Able to Do

Here is a query that combines several features you will learn over the coming chapters. It shows how many employees each department has and their average salary:

SELECT d.dept_name,
       COUNT(*)      AS employees,
       AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;
+-------------+-----------+---------------+
| dept_name   | employees | avg_salary    |
+-------------+-----------+---------------+
| Engineering |         3 | 113333.333333 |
| Marketing   |         1 |  82000.000000 |
| Sales       |         3 |  76666.666667 |
| HR          |         2 |  59000.000000 |
+-------------+-----------+---------------+

Don't worry if JOIN and GROUP BY look new — they are covered in GROUP BY & HAVING and SQL Joins. Just notice how much work seven lines of SQL do: combining two tables, grouping rows, counting, averaging and sorting. (Also notice that Research is missing because it has no employees, and Ishita, who has no department, is not counted. You will learn exactly why in the joins chapters.)


Where is SQL Used?

  • Backend development – every login, signup, cart and payment touches a database through SQL (directly or through an ORM that generates SQL).

  • Data analysis and business intelligence – analysts write SQL daily to build reports and dashboards.

  • Data engineering – tools like BigQuery, Snowflake, Spark SQL and Hive all speak SQL.

  • Mobile apps – Android and iOS apps commonly use SQLite, an embedded SQL database.

  • Interviews – SQL rounds are standard for SDE, data analyst, data engineer and business analyst roles, and DBMS/SQL is a core GATE and university subject.

SQL vs NoSQL in One Table

You will often hear about "NoSQL" databases such as MongoDB, Redis and Cassandra. They are not replacements for SQL — they solve different problems.

Point

SQL (relational) databases

NoSQL databases

Data model

Tables with fixed columns

Documents, key-value pairs, wide columns or graphs

Schema

Defined up front (CREATE TABLE)

Flexible, can vary per record

Relationships

Joins and foreign keys are built in

Usually handled by the application or by embedding data

Transactions

Strong ACID guarantees are the norm

Varies by product

Examples

MySQL, PostgreSQL, Oracle, SQL Server, SQLite

MongoDB, Redis, Cassandra, Neo4j


Common Beginner Misconceptions

  • "SQL is a database." No — SQL is a language. MySQL is the database software (RDBMS) that understands it.

  • "SQL and MySQL are the same thing." MySQL is one product that implements SQL, with its own extras.

  • "Rows come back in insertion order." A table has no guaranteed order. If you need a particular order, you must ask for it with ORDER BY (see ORDER BY & LIMIT).

  • "SQL is case-insensitive, so table names don't matter." Keywords are case-insensitive; table names may not be (see the Linux example above).

  • "I need to learn every dialect." Learn one dialect well. The core is the same, and the differences can be looked up in minutes.


Interview Questions

What is SQL?

SQL (Structured Query Language) is a standard declarative language for creating, querying, modifying and controlling access to data in relational databases.

What does "SQL is declarative" mean?

You describe what result you want, not how to compute it. The database's query optimizer decides the execution plan (full scan, index lookup, join order and so on).

What is the difference between SQL and MySQL?

SQL is the language. MySQL is a relational database management system that uses (a dialect of) SQL. PostgreSQL, Oracle and SQL Server are other RDBMSs that also use SQL.

Name the categories of SQL commands with examples.

DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE), DQL (SELECT), DCL (GRANT, REVOKE) and TCL (COMMIT, ROLLBACK, SAVEPOINT).

Is SQL case-sensitive?

Keywords are not. In MySQL, column names are not, but table and database names can be, depending on the operating system (case-sensitive on Linux by default). String comparisons depend on the collation; MySQL's default collation is case-insensitive.

Is TRUNCATE a DDL or DML command?

DDL. It removes all rows by recreating the table rather than deleting rows one by one, and in MySQL it causes an implicit commit, so it cannot be rolled back like DELETE.


The Main Idea

SQL is the standard language for working with relational databases. You describe the result you want, and the database works out how to produce it.

SQL          → language (standard since 1986)
MySQL        → the RDBMS we use (version 8.4)
Declarative  → say WHAT you want, the optimizer decides HOW
Sub-languages→ DDL (structure), DML (rows), DQL (SELECT),
               DCL (permissions), TCL (transactions)
Syntax rules → end with ;   strings in '...'   keywords any case
               table names: match exactly (Linux is case-sensitive)
Sample data  → company_db: departments, employees, customers, orders

Next, in Setting Up MySQL, we install MySQL, connect to it and load company_db, so you can run every query in this tutorial yourself.