SQL 101: Essential Concepts

​

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:

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:

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:

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:

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:

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 ensures your data is safe and consistent.

• Atomicity: Either all steps of a task are done, or none.
• Consistency: Data stays valid.
• Isolation: Multiple users can work without issues.
• Durability: 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:

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 most databases.
   • This means SELECT and select are treated the same.

   Example:

   -- 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:

   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:

   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.