Introduction to SQL and Databases

What Is It?

What Is a Database?

A database is an organized collection of data, stored so that it can be quickly searched, updated, and analyzed. The keyword is organized. A WhatsApp chat backup as a giant text file is just data. The same chats stored in a structured format with columns for sender, receiver, message, and timestamp — that is a database.

Every app on your phone uses a database behind the scenes. When you open Instagram and see your feed, an SQL query is fetching posts from a database. When Paytm shows your last 10 transactions, that is a database query. When BookMyShow tells you Seat A12 is already booked, again a database query.

What Is a DBMS?

A DBMS (Database Management System) is the software that manages a database. You don't talk to the database file directly — you talk to the DBMS, and the DBMS handles storage, indexing, concurrency, security, and crash recovery for you. Think of it like this: if a database is the books in a library, the DBMS is the librarian.

Popular DBMS software includes MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and SQLite. Each is a separate piece of software, and most of them speak SQL with small variations.

What Is SQL?

SQL stands for Structured Query Language. It is the standard language used to interact with relational databases. With SQL you can create tables, insert data, search through millions of rows, update records, delete them, and join multiple tables together. SQL is pronounced either as "S-Q-L" (each letter spoken) or as "sequel" — both are acceptable in industry.

SQL was invented in the early 1970s and is still the dominant query language more than 50 years later. Very few technologies survive that long. SQL did because the relational model it is built on (tables with rows and columns, related to each other by keys) turned out to be one of the most useful ideas in computing.

Why Does It Matter?

Why SQL Still Dominates After 50+ Years

Most technologies become obsolete in 5 to 10 years. JavaScript frameworks come and go every two years. Yet SQL, a language designed in 1974, is on the most-asked-skill list for almost every developer, data analyst, and data scientist job in 2026. Here is why.

1. Almost Every Application Uses a Relational Database

Banking apps, e-commerce sites, hospital management systems, government portals like Aadhaar and IRCTC, college ERPs, hotel booking systems, food delivery apps — they all use relational databases for the parts of their data where consistency matters. If you are building anything that stores user accounts, orders, payments, or transactions, you will use SQL.

2. SQL Is the Common Language of Data

Whether the database is MySQL at a startup, PostgreSQL at a fintech, Oracle at a bank, or BigQuery at Google, the core SELECT/JOIN/GROUP BY syntax is almost identical. Learn SQL once, use it everywhere — including modern cloud data warehouses (Snowflake, Redshift, BigQuery) which all expose SQL interfaces.

3. Data Roles Demand SQL

Data analyst, data engineer, data scientist, business intelligence developer, backend developer — every one of these roles lists SQL in the first line of the job description. Companies like Flipkart, Amazon, Razorpay, Zomato, and Swiggy ask SQL questions in their first technical round, often before any other coding question.

4. SQL Survived Because the Relational Model Is Sound

SQL was built on the relational model proposed by Edgar F. Codd at IBM in 1970. The idea — represent data as tables with rows and columns, with relationships defined by shared keys — is mathematically elegant and matches how humans naturally think about structured information. NoSQL databases tried to replace SQL in the 2010s, but most teams ended up using both: SQL for the structured core, NoSQL for specific use cases.

Detailed Explanation

Detailed Explanation

1. Data, Tables, Rows, and Columns

In a relational database, data is stored in tables. A table has columns (also called fields or attributes) that define what kind of information is stored, and rows (also called records or tuples) that hold the actual data.

Here is a sample students table:

This table has 4 columns (id, name, city, marks) and 5 rows. Each row is one student. Each column holds one type of information about every student. This rectangular shape — same set of columns for every row — is what makes it a relational table.

2. Primary Key (Quick Intro)

Notice the id column. Each student has a unique id. This unique identifier is called a primary key. Primary keys are how the database (and you) tell two rows apart, even if other fields look identical. Two different students could both be named "Aarav Sharma" living in Mumbai, but their id values will differ. We will go deep into primary keys in Chapter 3.

3. SQL vs NoSQL — When to Use What

SQL databases (also called relational databases) store data in tables with a fixed schema. Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite. They are best when:

• Data has a clear structure that doesn't change often (users, orders, payments, inventory).
• You need ACID guarantees — transactions that either fully complete or fully roll back. Critical for banking and any money-related system.
• You need to JOIN data from multiple tables (e.g., "give me all orders by users in Mumbai").

NoSQL databases are a different family. They store data as documents (MongoDB), key-value pairs (Redis), wide columns (Cassandra), or graphs (Neo4j). They are best when:

• Data is unstructured or semi-structured (JSON documents, social media posts).
• You need horizontal scaling across many servers (massive Twitter-scale workloads).
• The schema changes often or differs per record.

In practice, most companies use BOTH. A typical e-commerce stack might use PostgreSQL for orders and payments, MongoDB for product catalogs, and Redis for caching session data.

4. Popular Databases (and Where They Are Used)

For learning, we recommend MySQL — it is free, widely used in industry, has good Windows/Mac/Linux installers, and what you learn transfers easily to PostgreSQL, SQLite, and SQL Server.

5. A Brief History of SQL

In 1970, Edgar F. Codd, a researcher at IBM, published a paper titled "A Relational Model of Data for Large Shared Data Banks". This paper proposed organizing data as relations (tables) instead of the navigational hierarchies common at the time.

By 1974, IBM researchers Donald Chamberlin and Raymond Boyce designed a query language called SEQUEL (Structured English Query Language) to work with the relational model. The name was later shortened to SQL due to a trademark conflict.

In 1979, a small company called Relational Software (later renamed Oracle) released the first commercial SQL database, beating IBM to market by two years. Oracle still exists today as one of the largest database companies in the world.

SQL was standardized by ANSI in 1986 and by ISO in 1987. Major revisions: SQL-92 (joins, subqueries), SQL:1999 (recursion, triggers), SQL:2003 (window functions, XML), SQL:2016 (JSON support). Most databases support a subset plus their own extensions.

6. The Four Categories of SQL Commands

SQL commands are grouped into four categories based on what they do. Memorizing this is interview gold.

DDL — Data Definition Language

Defines the structure of the database. Affects schema, not data values.

DML — Data Manipulation Language

Works with the data inside tables.

DCL — Data Control Language

Controls who can do what — permissions and access rights.

TCL — Transaction Control Language

Manages transactions — groups of statements that should succeed or fail together.

Memory tip: DDL = structure, DML = data, DCL = permissions, TCL = transactions.

7. Case Sensitivity

SQL keywords (SELECT, FROM, WHERE) are not case-sensitive. select, SELECT, and SeLeCt all work. Convention is to write keywords in UPPERCASE so they stand out against table and column names.

Whether table and column names are case-sensitive depends on the database and the operating system. On MySQL on Linux, table names are case-sensitive (Students and students are different). On MySQL on Windows, they are not. Safe rule: pick lowercase_with_underscores naming and stick to it.

Code Examples

-- This is the students table that we will use throughout this chapter.
-- The actual SQL to create and populate it comes in Chapter 2.

SELECT * FROM students;

-- DDL: CREATE defines the structure of a new table.
-- This says: I want a table called employees with three columns.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

-- Notice: no data is inserted yet. This only creates an empty shell.
SELECT * FROM employees;

-- DML: INSERT adds new rows into the employees table.
INSERT INTO employees VALUES (101, 'Kavya Nair', 75000.00);
INSERT INTO employees VALUES (102, 'Arjun Mehta', 92000.50);
INSERT INTO employees VALUES (103, 'Sneha Patel', 68000.00);

-- DML: SELECT reads the rows back.
SELECT * FROM employees;

-- TCL: Transactions group multiple statements together.
START TRANSACTION;

UPDATE employees SET salary = salary + 5000 WHERE emp_id = 101;
UPDATE employees SET salary = salary + 5000 WHERE emp_id = 102;

-- Oops, we changed our mind. Undo everything since START TRANSACTION.
ROLLBACK;

SELECT * FROM employees;

-- DCL: GRANT gives a user permission to do something.
-- This says: user 'analyst_kavya' can SELECT from the employees table.

GRANT SELECT ON company.employees TO 'analyst_kavya'@'localhost';

-- Later, take that permission away.
REVOKE SELECT ON company.employees FROM 'analyst_kavya'@'localhost';

-- DDL: structure
CREATE TABLE products (id INT, name VARCHAR(50), price INT);
ALTER TABLE products ADD COLUMN stock INT;
DROP TABLE products;

-- DML: data
INSERT INTO students VALUES (6, 'Diya Kapoor', 'Chennai', 88);
UPDATE students SET marks = 90 WHERE id = 6;
DELETE FROM students WHERE id = 6;
SELECT name, marks FROM students;

-- DCL: permissions
GRANT INSERT ON school.students TO 'teacher_meera'@'%';
REVOKE INSERT ON school.students FROM 'teacher_meera'@'%';

-- TCL: transactions
START TRANSACTION;
UPDATE students SET marks = marks + 5 WHERE city = 'Mumbai';
COMMIT;

Common Mistakes

Statement: "MySQL is a database."

Statement: "MySQL is a Database Management System (DBMS). The database is the actual collection of data managed by MySQL."

Statement: "I know SQL because I learned MySQL."

Statement: "I know SQL, and I have practiced it on MySQL. The same SELECT/JOIN/GROUP BY queries also work on PostgreSQL, SQL Server, and SQLite with minor syntax differences."

Statement: "TRUNCATE is a DML command because it removes data."

Statement: "TRUNCATE is a DDL command. It drops and recreates the table internally, which is faster than DELETE but cannot be rolled back in most databases."

Statement: "SQL is old. Modern apps use NoSQL like MongoDB."

Statement: "SQL and NoSQL solve different problems. Most modern stacks use both — SQL for transactional, structured data; NoSQL for flexible documents, caching, or massive scale-out. SQL is still on virtually every backend job description."

Summary