> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL 101: Essential Concepts

> Every Beginner Must Know

### 1. **Do you know what a declarative language is?**

Let’s start with something simple. SQL is a **declarative language**, which means you just tell the computer *what* you want, not *how* to do it.\
For example, in SQL, you would write:

```sql theme={"system"}
SELECT * FROM employees WHERE age > 30;
```

This means, “Hey, database, get me all employees older than 30.”\
You don’t have to worry about how the database finds them—that's its job!

### 2. **Ever heard of a database?**

Think of a database as a big digital filing cabinet. It stores data in an organized way so you can quickly find what you need.\
For instance, an online store like Flipkart has a database that keeps track of products, orders, and customer details.

Each piece of data (like a product) is stored in **tables**—imagine a spreadsheet with rows and columns.

### 3. **What’s the difference between data and information?**

Good question!

* **Data** is raw facts, like: “Ravi, 25.”
* **Information** is meaningful, like: “Ravi is 25 years old and works in IT.”

SQL helps you turn random data into useful information. For example, you can find out how many customers made purchases in December.

### 4. **Have you heard of relational databases?**

Relational databases store data in tables with rows and columns.\
For example:

* A **table** called `students` might have columns like `id`, `name`, and `age`.
* Each **row** is a student’s record:

| id | name  | age |
| -- | ----- | --- |
| 1  | Rahul | 20  |
| 2  | Priya | 22  |

This structure makes it easy to organize and search your data.

### 5. **Why do data types matter?**

Imagine if you wrote someone’s name in the column where their salary is stored! That’s why we use **data types** to define what kind of data goes in each column.

Here are some common ones:

* `INT`: Numbers without decimals (e.g., age: `25`)
* `VARCHAR`: Text (e.g., name: `"Amit"`)
* `DATE`: Dates (e.g., `2025-01-01`)

For example, when creating a table, you’ll specify data types:

```sql theme={"system"}
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    hire_date DATE
);
```

### 6. **What’s a primary key?**

Imagine you’re identifying a person in a class. A primary key is like their **roll number**—it uniquely identifies each record in a table.\
For example, in the `students` table:

| id | name  | age |
| -- | ----- | --- |
| 1  | Rahul | 20  |
| 2  | Priya | 22  |

Here, the `id` column is the primary key because no two students can have the same ID.

### 7. **Why do tables need relationships?**

If you have one table for employees and another for their departments, you’ll want a way to link them.

Here’s how it works:

* The `departments` table has a column called `dept_id`.
* The `employees` table uses this `dept_id` to say which department they belong to.

Example:

```sql theme={"system"}
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
```

This is how databases stay organized and connected.

### 8. **What are ACID properties?**

Think of these as the golden rules for databases. [ACID](/glossary/acid-properties)ensures your data is safe and consistent.

* **A**tomicity: Either all steps of a task are done, or none.
* **C**onsistency: Data stays valid.
* **I**solation: Multiple users can work without issues.
* **D**urability: Once saved, it’s permanent.

For example, if you’re transferring money between bank accounts, ACID makes sure the amount is subtracted from one account *and* added to the other.

### 9. **Do you know about indexes?**

An index is like a book’s table of contents—it helps you find data faster.

Without an index: You’d have to scan every row to find something.

With an index: You can jump straight to the relevant rows.

For example:

```sql theme={"system"}
CREATE INDEX idx_employees_name ON employees(name);
```

### 10. **Case Sensitivity in SQL**

Understanding case sensitivity is crucial when working with SQL because it varies based on the database you're using.

1. **SQL Keywords**:

   * SQL commands like `SELECT`, `INSERT`, and `WHERE` are **not case-sensitive** in <Tooltip tip="SQL Server, MySQL are not case-sensitive for Keywords but PostgreSQL is by default case-sensitive for keywords.">most </Tooltip>databases.
   * This means `SELECT` and `select` are treated the same.

   Example:

   ```sql theme={"system"}
   -- Both queries will work exactly the same.
   SELECT * FROM employees;
   select * from employees;
   ```

2. **Table and Column Names**:

   * **Some databases are case-sensitive**, while others are not.
     * MySQL is **case-sensitive** if your file system is case-sensitive. For example, on Linux (which uses a case-sensitive file system), `employees` and `Employees` are different. On Windows, they are treated the same.
     * SQL Server and PostgreSQL are **not case-sensitive** by default. But table and column names are **case-sensitive in PostgreSQL** if enclosed in double quotes (`"`).

   Example:\
   If `employees` is your table name in a case-sensitive database:

   ```sql theme={"system"}
   SELECT * FROM employees;  -- Works
   SELECT * FROM Employees;  -- May fail in case-sensitive setups
   ```

3. **String Comparisons**:

   * Text comparisons in SQL **can be case-sensitive** depending on the database or collation settings.
   * For example, `'Matt'` and `'matt'` may not match unless you explicitly make the comparison case-insensitive.

   Example of a case-insensitive comparison:

   ```sql theme={"system"}
   SELECT * FROM employees WHERE LOWER(name) = 'matt';
   ```

To avoid confusion, it’s good practice to keep table and column names consistent (e.g., always UPPERCASE, lowercase or snake\_case).

By understanding case sensitivity, you can avoid subtle bugs and write queries that behave predictably across databases.

[^1]: