SQL Fundamentals

SQL (Structured Query Language) is a declarative language used to communicate with relational databases.

Concept

SQL (Structured Query Language) is a declarative language used to communicate with relational databases. "Declarative" means you describe what result you want, not how to compute it. The database engine decides the most efficient way to produce the result.

You did not say which file to open, which index to use, or how to loop through rows. The database's query optimizer decides all of that.

SQL is used to:

Define structures (tables, indexes, views).

Insert, change, and delete data.

Query and analyze data.

Control access (users, roles, privileges).

Manage transactions (group changes into all-or-nothing units).

SQL is pronounced either "S-Q-L" or "sequel". Both are accepted. It was developed at IBM in the 1970s (originally called SEQUEL) based on Edgar F. Codd's relational model, and was standardized by ANSI in 1986 and ISO in 1987.

What Is a Database?

A database is an organized collection of related data stored so that it can be easily accessed, managed, and updated.

A university database stores students, courses, enrollments, and grades.

A bank database stores customers, accounts, and transactions.

An automotive diagnostics database stores vehicles, ECUs, diagnostic trouble codes (DTCs), and test results.

A spreadsheet is a simple form of data storage, but it is not a real database: it has weak integrity rules, poor multi-user concurrency, and does not scale.

What Is a DBMS?

A DBMS (Database Management System) is the software that creates, stores, secures, and retrieves data in a database. It sits between users/applications and the physical data files.

A DBMS provides:

Data storage and retrieval A query language Concurrency control (many users at once) Transactions and recovery Security and access control Backup and restore

DBMS types include hierarchical, network, relational, object-oriented, document, key-value, graph, and column-family systems.

What Is an RDBMS?

An RDBMS (Relational Database Management System) is a DBMS based on the relational model, in which data is stored in tables (relations) made of rows and columns, and relationships between tables are expressed through keys. RDBMSs use SQL as their language.

Examples: PostgreSQL, MySQL, MariaDB, Microsoft SQL Server, Oracle Database, SQLite, IBM Db2.

Database vs DBMS vs RDBMS The data itself (organized collection) Software that manages a database DBMS that follows the relational model Files, hierarchies, documents, tables Tables with rows and columns Optional / navigational Through primary and foreign keys Limited or none in some systems

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL Designed for it Transactions / ACID Full ACID support is standard "The library's book data"

File systems, IMS, early dBase, MongoDB (document DBMS) PostgreSQL, MySQL, Oracle, SQL Server

A simple analogy: the database is the library's collection of books; the DBMS is the librarian and catalogue system; an RDBMS is a librarian who organizes everything into strictly structured, cross-referenced catalogue tables.

Relational Database Concept The relational model, introduced by E. F. Codd in 1970, rests on a few ideas:

Data is stored in relations (tables). Each table represents one kind of entity (Customer, Order, Product).

Each row (tuple) is unique, identified by a primary key.

Each column (attribute) has a domain (a data type and set of allowed values).

Column values are atomic (a single value, not a list).

Relationships are expressed by values, not by physical pointers. An order "belongs to" a customer because orders.customer_id matches customers.customer_id.

Order of rows and columns has no meaning. A table is a set; if you need an order, you must use ORDER BY.

Tables, Rows, and Columns Relational theory term A named collection of rows with the same structure

One instance of the entity (one customer) One property of the entity (the customer's city) The set of allowed values for a column Number of columns Number of rows

Keys identify rows and connect tables. Understanding keys is essential for database design and a very common interview topic.

Consider this students table:

Super Key — any set of one or more columns that uniquely identifies a row. Examples: {student_id}, {roll_no}, {email}, {student_id, name}, {roll_no, email, dept}. Adding extra columns to a super key still gives a super key.

Candidate Key — a minimal super key: no column can be removed while keeping uniqueness. Examples: {student_id}, {roll_no}, {email}. {student_id, name} is not a candidate key because name is unnecessary. passport_no is not a candidate key because it can be NULL (not every student has one).

Primary Key (PK) — the candidate key chosen as the main identifier of the table. Rules: unique, NOT NULL, one per table, should be stable (rarely or never changes). Here: student_id.

Alternate Key — a candidate key that was not chosen as the primary key. Usually enforced with a UNIQUE constraint. Here: roll_no, email.

Composite Key — a key made of two or more columns together. Example: in order_items, (order_id, product_id) is the primary key. Neither column alone is unique.

Foreign Key (FK) — a column (or columns) in one table that refers to the primary key (or a unique key) of another table, enforcing referential integrity. Example: orders.customer_id references customers.customer_id. The database refuses an order for a customer that does not exist.

Natural Key — a key that has real-world business meaning and exists naturally in the data. Examples: email address, vehicle VIN, ISBN, national ID, country code IN.

Surrogate Key — an artificial key with no business meaning, usually an auto-generated integer or UUID. Examples: customer_id generated by IDENTITY, SERIAL, AUTO_INCREMENT, or a UUID.

How many per table {student_id, name} No (by design) Exactly one (recommended) Yes (combined) (order_id, product_id) No (can repeat) Yes, unless NOT NULL Natural vs surrogate keys: Meaningful to users Meaningless to users

May change (email changes, national ID reissued) Can be wide (long strings) → larger indexes Compact (4 – 8 bytes for integers) Avoids an extra column

Enforces real-world uniqueness automatically Real-world uniqueness still needs a separate UNIQUE constraint

A common and recommended practice: use a surrogate primary key and also put a UNIQUE constraint on the natural key.

NULL means "unknown" or "not applicable". It is not zero, not an empty string, and not the word "NULL".

salary = 0 means the salary is known to be zero.

salary IS NULL means the salary is unknown.

email = '' means the email is known to be empty; email IS NULL means no email is recorded.

The crucial rule: any comparison with NULL yields UNKNOWN, not TRUE or FALSE. Therefore WHERE email = NULL never matches anything. You must use IS NULL. NULL handling is covered deeply in Module 40.

SQL-86 / SQL-87 First ANSI/ISO standard Explicit JOIN syntax, CASE, CAST, temporary tables, isolation levels SQL:1999 (SQL3)

Recursive queries (WITH RECURSIVE), triggers, BOOLEAN, procedural SQL, user-defined types

Window functions, MERGE, sequences, identity columns, XML SQL:2006 / 2008

XQuery integration; TRUNCATE, FETCH FIRST, INSTEAD OF triggers Temporal (system-versioned) tables

JSON functions, row pattern recognition (MATCH_RECOGNIZE) Property graph queries (SQL/PGQ), JSON data type improvements

No database implements the full standard; each implements a large subset plus its own extensions.

Dialect / procedural language PostgreSQL SQL + PL/pgSQL MySQL / MariaDB MySQL SQL + stored-program language T-SQL (Transact-SQL) Oracle SQL + PL/SQL SQLite SQL (no stored procedures) Db2 SQL + SQL PL Examples of differences:

TOP 5 / OFFSET … FETCH FETCH FIRST 5 ROWS ONLY CONCAT(a, b) a + b / CONCAT SYSDATE / SYSTIMESTAMP GENERATED … AS IDENTITY / SERIAL GENERATED … AS IDENTITY INTEGER PRIMARY KEY

Note: in MySQL, || is the logical OR operator by default, not concatenation (unless PIPES_AS_CONCAT SQL mode is enabled).

Common SQL Database Systems Open source (GPL) + commercial Easy, fast reads, huge ecosystem Web apps (LAMP stack), WordPress, SaaS Open source (PostgreSQL license)

Standards compliance, advanced types (JSONB, arrays), extensions (PostGIS), strong concurrency

General purpose, analytics, geospatial, startups to enterprise Microsoft SQL Server

Commercial (free Express/Developer editions) Tooling, BI integration, T-SQL Enterprise,.NET applications Oracle Database Commercial (free XE edition) Scalability, RAC clustering, PL/SQL Banking, telecom, ERP, large enterprise

Serverless, single file, zero config, embedded Mobile apps, browsers, embedded devices, automotive infotainment, testing Open source (GPL)

MySQL fork, community-driven, extra storage engines Replacement for MySQL SQL (relational) NoSQL (non-relational) Tables with fixed schema

Documents, key-value, wide-column, graph Schema-on-write (defined first) Often schema-on-read (flexible) Joins, foreign keys

Embedding, references, or application-side joins Varies; many offer ACID per document or configurable consistency

Traditionally vertical; horizontal via replication/sharding Designed for horizontal scale SQL (standardized)

Product-specific APIs (MongoDB query language, CQL, Cypher) Structured data, complex queries, integrity, reporting

Huge scale, flexible or rapidly changing schema, specific access patterns

MongoDB, Redis, Cassandra, DynamoDB, Neo4j The line is blurring: PostgreSQL has JSONB documents; MongoDB has multi-document transactions; many NoSQL systems offer SQL-like query languages. Choose based on access patterns and consistency needs, not hype.

Application backends — storing users, orders, sessions.

Reporting and business intelligence — sales dashboards, KPIs.

Data engineering — ETL/ELT pipelines, data warehouses (Snowflake, BigQuery, Redshift all use SQL).

Data analysis and data science — exploration, feature extraction.

Testing — testers verify that application actions produced correct database state.

Embedded and automotive — SQLite in infotainment units, diagnostic tools, and test benches.

Log and telemetry analysis — CAN bus logs, sensor data, test results.

-- "Give me the names of employees earning more than 100000."
SELECT first_name, last_name
FROM employees
WHERE salary > 100000;

customers                               orders
+-------------+--------------+          +----------+-------------+------------+
| customer_id | name         |          | order_id | customer_id | order_date |
+-------------+--------------+          +----------+-------------+------------+
| 1           | Ananya Gupta |<----+----| 1001     | 1           | 2026-01-05 |
| 2           | Ben Carter   |     +----| 1003     | 1           | 2026-02-02 |
+-------------+--------------+          +----------+-------------+------------+
     primary key                               foreign key -> customers

Table: products                         <- table (relation)
+------------+--------------+-------------+----------+
| product_id | product_name | category    | price    |   <- columns (attributes)
+------------+--------------+-------------+----------+
| 101        | Laptop       | Electronics | 75000.00 |   <- a row (record / tuple)
| 102        | Mouse        | Electronics |   800.00 |
+------------+--------------+-------------+----------+

Super keys (any unique column set)
                  ┌───────────────────────────────────────┐
                  │   Candidate keys (minimal super keys) │
                  │   ┌──────────────┐  ┌────────────────┐│
                  │   │ Primary key  │  │ Alternate keys ││
                  │   └──────────────┘  └────────────────┘│
                  └───────────────────────────────────────┘

Application / User
        ↓
SQL Query (text)
        ↓
Database Engine
   ├── Parser         → checks syntax, builds a parse tree
   ├── Binder/Analyzer→ resolves table/column names, checks permissions and types
   ├── Query Optimizer→ chooses the cheapest execution plan (indexes, join order)
   └── Executor       → runs the plan
        ↓
Storage Engine
   ├── Buffer pool / cache (pages in memory)
   ├── Transaction & lock manager
   └── Write-ahead log (for durability)
        ↓
Tables / Indexes (data files on disk)
        ↓
Result set returned to the application

Why It Matters

SQL is one of the most in-demand technical skills across software engineering, data engineering, analytics, QA, and DevOps.

Nearly every system of record (payments, healthcare, vehicles, inventory) lives in a relational database.

Understanding keys, NULL, and the relational model prevents entire classes of bugs: duplicate records, orphaned data, and wrong reports.

Knowledge of SQL transfers across products; once you learn one dialect, others are easy.

Syntax

A SQL statement is made of keywords, identifiers, literals, operators, and ends with a semicolon.

General rules:

Keywords are case-insensitive: SELECT, select, and Select are the same. Convention: uppercase keywords, lowercase identifiers.

Identifiers (table/column names) may be case-sensitive depending on the database and OS (MySQL table names on Linux are case-sensitive; PostgreSQL folds unquoted names to lowercase; SQL Server depends on collation).

String literals use single quotes: 'Mumbai'. Double quotes are for identifiers in standard SQL ("Order"). MySQL uses backticks (` order ); SQL Server uses square brackets ([order]`).

Statements end with ; (required in most CLI tools and when running multiple statements).

Comments: -- single line and /* multi-line */. MySQL also accepts #.

Whitespace and line breaks do not matter; format for readability.

SELECT first_name, salary * 12 AS annual_salary   -- keywords, identifiers, expression
FROM employees                                    -- table identifier
WHERE dept_id = 1;                                -- literal 1, operator =

Example

-- List all Engineering employees with their salary, highest first.
SELECT emp_id, first_name, last_name, salary
FROM employees
WHERE dept_id = 1
ORDER BY salary DESC;

Expected Result

(Rows 3 and 8 tie on salary; their relative order is not guaranteed unless you add a tie-breaker such as ORDER BY salary DESC, emp_id.)

Common Mistakes

Thinking SQL executes top-to-bottom like a script. It is declarative; the optimizer decides execution order.

Using double quotes for strings: WHERE city = "Mumbai" fails in PostgreSQL/Oracle/SQL Server (it looks for a column named Mumbai). Use 'Mumbai'.

Assuming rows come back in insertion order. Without ORDER BY, order is undefined.

Treating NULL like zero or an empty string.

Assuming MySQL syntax is "standard SQL".

Choosing a mutable natural key (email, phone) as the primary key.

Best Practices

Learn standard SQL first, then dialect extensions.

Uppercase keywords, lowercase snake_case identifiers.

Give every table a primary key.

Prefer surrogate primary keys plus UNIQUE constraints on natural keys.

Always state the dialect when sharing SQL with others.

Interview Relevance

Very high. Expect: "What is SQL?", "DBMS vs RDBMS?", "Primary key vs unique key?", "Candidate vs super key?", "Natural vs surrogate key?", "What is NULL?", "SQL vs NoSQL?".

Practice Questions

In your own words, explain the difference between a database and a DBMS.

List four features that an RDBMS provides that a plain spreadsheet does not.

In a vehicles table with columns vehicle_id, vin, registration_no, model, identify candidate keys, a suitable primary key, and alternate keys.

Is {vin, model} a super key? Is it a candidate key? Why?

Give two natural keys and two surrogate keys from systems you use daily.

Explain why WHERE email = NULL returns no rows.

Name the procedural SQL language used by SQL Server, Oracle, and PostgreSQL.

When would you choose SQLite over PostgreSQL?