---

Table of Contents

1. Introduction
2. What is the ORDER BY Clause?
3. Basic Syntax of ORDER BY
4. Sorting in Ascending Order (ASC)
5. Sorting in Descending Order (DESC)
6. Default Sorting Behavior
7. Sorting by Multiple Columns
8. Sorting by Column Positions
9. Sorting by Aliased Columns
10. Sorting Text vs Numbers
11. Sorting by Date and Time
12. Sorting with NULL Values
13. Using ORDER BY with LIMIT
14. Combining ORDER BY with WHERE
15. Real-World Examples of Sorting
16. Performance Considerations
17. ORDER BY vs GROUP BY
18. Common Mistakes and How to Avoid Them
19. Best Practices for Sorting
20. Summary and What’s Next

---

1. Introduction

When working with SQL queries, the order in which rows are returned is not guaranteed unless you explicitly define it using the ORDER BY clause. Whether you’re building a leaderboard, a product list, or a list of recent transactions — ORDER BY is essential.

---

2. What is the ORDER BY Clause?

The ORDER BY clause in SQL is used to sort the result set returned by a SELECT query. You can sort data ascendingly or descendingly based on one or more columns.

---

3. Basic Syntax of ORDER BY

sqlCopyEditSELECT column1, column2
FROM table_name
ORDER BY column1 [ASC | DESC];

Example:

sqlCopyEditSELECT name, age FROM users
ORDER BY age;

---

4. Sorting in Ascending Order (ASC)

This is the default behavior:

sqlCopyEditSELECT name FROM employees
ORDER BY name ASC;

It sorts alphabetically (A–Z) or numerically (low to high).

---

5. Sorting in Descending Order (DESC)

Use DESC to sort in reverse order:

sqlCopyEditSELECT name, salary FROM employees
ORDER BY salary DESC;

This is useful for rankings, top-scorers, etc.

---

6. Default Sorting Behavior

If you do not specify a direction, SQL assumes ASC by default:

sqlCopyEditORDER BY age;  -- same as ORDER BY age ASC

---

7. Sorting by Multiple Columns

Sort first by one column, then by another:

sqlCopyEditSELECT * FROM users
ORDER BY city ASC, age DESC;

This sorts users by city alphabetically and then by age (older users first) within each city.

---

8. Sorting by Column Positions

You can also use column position numbers:

sqlCopyEditSELECT name, age, salary FROM employees
ORDER BY 3 DESC, 2 ASC;

This sorts by the 3rd column (salary), then 2nd column (age). While concise, this is less readable and more error-prone — use with caution.

---

9. Sorting by Aliased Columns

If you use AS to alias a column, you can sort by that alias:

sqlCopyEditSELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

---

10. Sorting Text vs Numbers

• Strings sort lexicographically (alphabetically)
• Numbers sort mathematically

Example:

sqlCopyEditSELECT name, department FROM employees
ORDER BY department;

Departments are ordered alphabetically:
Accounting, Engineering, Finance, etc.

---

11. Sorting by Date and Time

sqlCopyEditSELECT * FROM orders
ORDER BY order_date DESC;

This is especially useful to get:

• Most recent orders
• Latest login
• Chronological events

---

12. Sorting with NULL Values

Different databases handle NULL differently:

• MySQL: NULL appears first with ASC, last with DESC
• PostgreSQL: allows NULLS FIRST or NULLS LAST explicitly:

sqlCopyEditSELECT * FROM products
ORDER BY discount DESC NULLS LAST;

---

13. Using ORDER BY with LIMIT

You can combine ORDER BY with LIMIT to retrieve top/bottom results:

sqlCopyEdit-- Top 5 highest salaries
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

---

14. Combining ORDER BY with WHERE

sqlCopyEditSELECT name, age FROM users
WHERE is_active = TRUE
ORDER BY age DESC;

Filter first, then sort — always.

---

15. Real-World Examples of Sorting

Sort Top 3 Cities by Number of Users:

sqlCopyEditSELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
ORDER BY user_count DESC
LIMIT 3;

Sort Products by Price (Low to High):

sqlCopyEditSELECT product_name, price FROM products
ORDER BY price ASC;

---

16. Performance Considerations

• Sorting large datasets may slow down performance.
• Use indexes on frequently sorted columns.
• Avoid sorting unfiltered data unless necessary.

---

17. ORDER BY vs GROUP BY

Feature	ORDER BY	GROUP BY
Purpose	Sorts data	Aggregates data
Output	Returns individual rows in order	Returns one row per group
Used With	Any query	Often with COUNT, SUM, AVG

---

18. Common Mistakes and How to Avoid Them

• Assuming implicit order: SQL results are not ordered unless you specify ORDER BY
• Sorting by columns not included in SELECT without understanding scope
• Using column positions instead of names — prone to break in refactors
• Sorting before filtering (should be after WHERE)

---

19. Best Practices for Sorting

• Always explicitly specify ASC or DESC for clarity
• Use column names over column positions
• Combine ORDER BY with LIMIT for better performance on top results
• Add indexes to columns that are frequently sorted

---

20. Summary and What’s Next

The ORDER BY clause empowers you to return data in a meaningful order, making your results more readable and more useful in applications. By sorting on columns like names, prices, dates, or computed values, you can build everything from reports to dashboards.

---

Table of Contents

1. Introduction
2. What Are Logical Operators in SQL?
3. The Role of Logical Operators in Filtering
4. Basic Syntax and Usage
5. Using AND to Combine Conditions
6. Using OR to Widen Conditions
7. Using NOT to Invert Conditions
8. Combining AND and OR
9. Operator Precedence: NOT, AND, OR
10. Controlling Logic with Parentheses
11. Real-World Use Case: Employee Filtering
12. Real-World Use Case: Product Search
13. Using Logical Operators in UPDATE
14. Using Logical Operators in DELETE
15. Logical Operators with NULL Checks
16. Performance Considerations
17. Common Mistakes to Avoid
18. Best Practices
19. Summary Table of Logical Use Cases
20. Summary and What’s Next

---

1. Introduction

SQL logical operators — AND, OR, and NOT — are essential for writing powerful and flexible filtering conditions. They allow you to combine multiple expressions in your WHERE clause to get precise control over query results.

---

2. What Are Logical Operators in SQL?

Logical operators are used to combine or negate conditions in a WHERE clause. You can filter data using multiple rules instead of just one.

---

3. The Role of Logical Operators in Filtering

Instead of writing separate queries for each condition, logical operators let you write complex filters within a single SQL statement:

sqlCopyEditSELECT * FROM employees 
WHERE department = 'IT' AND age > 30;

---

4. Basic Syntax and Usage

sqlCopyEditSELECT column1, column2
FROM table_name
WHERE condition1 [AND|OR|NOT] condition2;

Example:

sqlCopyEditSELECT * FROM users
WHERE city = 'Mumbai' AND is_active = TRUE;

---

5. Using AND to Combine Conditions

The AND operator returns true only if both conditions are true:

sqlCopyEditSELECT * FROM orders 
WHERE status = 'shipped' AND payment_method = 'UPI';

Example Output Logic:

status	payment_method	Included
shipped	UPI	✅
shipped	Credit Card	❌
pending	UPI	❌

---

6. Using OR to Widen Conditions

The OR operator returns true if either condition is true:

sqlCopyEditSELECT * FROM employees 
WHERE department = 'HR' OR department = 'Admin';

You can match rows from either department.

---

7. Using NOT to Invert Conditions

The NOT operator negates the result of a condition:

sqlCopyEditSELECT * FROM users WHERE NOT city = 'Delhi';

This returns all users not from Delhi.

More examples:

sqlCopyEditSELECT * FROM orders WHERE NOT status = 'cancelled';
SELECT * FROM customers WHERE NOT age BETWEEN 18 AND 25;

---

8. Combining AND and OR

You can use both AND and OR together for more advanced logic:

sqlCopyEditSELECT * FROM employees 
WHERE (department = 'HR' OR department = 'Finance') 
AND age > 30;

This selects employees from HR or Finance who are also older than 30.

---

9. Operator Precedence: NOT, AND, OR

SQL evaluates operators in the following order:

1. NOT
2. AND
3. OR

So this query:

sqlCopyEditSELECT * FROM users 
WHERE NOT age < 18 AND city = 'Mumbai' OR is_active = TRUE;

Is interpreted as:

sqlCopyEdit((NOT age < 18) AND (city = 'Mumbai')) OR (is_active = TRUE)

Use parentheses to avoid ambiguity.

---

10. Controlling Logic with Parentheses

Parentheses clarify and control precedence:

sqlCopyEditSELECT * FROM orders 
WHERE status = 'shipped' AND (payment_method = 'UPI' OR payment_method = 'Wallet');

Without parentheses, SQL may evaluate logic differently than intended.

---

11. Real-World Use Case: Employee Filtering

sqlCopyEditSELECT * FROM employees
WHERE department = 'IT' 
  AND (experience > 5 OR salary > 60000);

This returns IT employees who either have more than 5 years of experience or earn more than 60K.

---

12. Real-World Use Case: Product Search

sqlCopyEditSELECT * FROM products 
WHERE (category = 'Books' OR category = 'Stationery') 
  AND price < 500;

Use logical operators to create advanced filters on e-commerce search or dashboards.

---

13. Using Logical Operators in UPDATE

sqlCopyEditUPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01' AND is_active = TRUE;

You can update based on multiple logical checks.

---

14. Using Logical Operators in DELETE

sqlCopyEditDELETE FROM logs 
WHERE severity = 'low' OR created_at < '2023-01-01';

This removes either old or low-priority logs.

---

15. Logical Operators with NULL Checks

sqlCopyEditSELECT * FROM users
WHERE phone IS NULL OR email IS NULL;

You can combine logical operators with IS NULL, IS NOT NULL.

---

16. Performance Considerations

• Use indexed columns in conditions to speed up filtering.
• Avoid overly complex conditions in huge datasets without proper indexing.
• Group conditions for efficient evaluation.

---

17. Common Mistakes to Avoid

• Forgetting parentheses when combining AND and OR
• Incorrect assumptions about precedence
• Using = for NULL comparisons (instead of IS NULL)
• Neglecting quotes around string literals

---

18. Best Practices

• Always group complex expressions with parentheses
• Test each condition individually before combining
• Prioritize readability — future you will thank you
• Use logical operators to reduce the number of queries

---

19. Summary Table of Logical Use Cases

Operator	Use Case	Example
AND	All conditions must be true	WHERE salary > 50000 AND city = 'Delhi'
OR	At least one condition must be true	WHERE city = 'Delhi' OR city = 'Mumbai'
NOT	Invert a condition	WHERE NOT is_active = TRUE
Combined	Advanced conditions	WHERE (role = 'admin' OR role = 'editor') AND active

---

20. Summary and What’s Next

Logical operators are indispensable in SQL for writing meaningful filters. They make your WHERE clauses far more powerful by allowing multiple conditions and combinations.

---

Table of Contents

1. Introduction
2. What is the WHERE Clause?
3. Basic Syntax of WHERE
4. Filtering with Equality Conditions
5. Using !=, <>, and NOT
6. Greater Than and Less Than Comparisons
7. Filtering Text with LIKE
8. Wildcards in LIKE: % and _
9. Using IN to Match Multiple Values
10. Filtering Between Ranges with BETWEEN
11. Handling NULL Values
12. Logical Operators: AND, OR, and NOT
13. Precedence in Conditional Expressions
14. Combining Multiple Conditions
15. WHERE Clause in Numeric Fields
16. WHERE Clause in Date Fields
17. WHERE Clause in String Fields
18. Using WHERE with Subqueries
19. Common Mistakes and Troubleshooting
20. Summary and What’s Next

---

1. Introduction

The WHERE clause is one of the most important tools in SQL. It allows you to filter records in a table by specifying one or more conditions. This means instead of retrieving every row, you can retrieve just the data you need.

---

2. What is the WHERE Clause?

The WHERE clause is used in SQL to filter records before they are selected, updated, or deleted. It is applicable in:

• SELECT
• UPDATE
• DELETE

---

3. Basic Syntax of WHERE

sqlCopyEditSELECT column1, column2
FROM table_name
WHERE condition;

Example:

sqlCopyEditSELECT * FROM employees WHERE age > 30;

---

4. Filtering with Equality Conditions

The simplest use case of WHERE is to check equality:

sqlCopyEditSELECT * FROM users WHERE country = 'India';
SELECT * FROM orders WHERE status = 'delivered';

---

5. Using !=, <>, and NOT

You can use either != or <> for “not equal to”:

sqlCopyEditSELECT * FROM products WHERE category != 'Electronics';
SELECT * FROM students WHERE grade <> 'A';

Or use NOT:

sqlCopyEditSELECT * FROM customers WHERE NOT city = 'Delhi';

---

6. Greater Than and Less Than Comparisons

sqlCopyEditSELECT * FROM sales WHERE amount > 1000;
SELECT * FROM inventory WHERE quantity < 50;

Also:

• >= greater than or equal to
• <= less than or equal to

---

7. Filtering Text with LIKE

The LIKE operator is used to match text patterns:

sqlCopyEditSELECT * FROM users WHERE name LIKE 'A%';   -- starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- ends with

---

8. Wildcards in LIKE: % and _

Wildcard	Meaning
%	Matches any number of characters
_	Matches exactly one character

Example:

sqlCopyEditSELECT * FROM books WHERE title LIKE '_ar%';  -- Matches 'Harry', 'Dark', etc.

---

9. Using IN to Match Multiple Values

sqlCopyEditSELECT * FROM employees WHERE department IN ('HR', 'Finance', 'Sales');

This is cleaner than using multiple OR conditions.

---

10. Filtering Between Ranges with BETWEEN

sqlCopyEditSELECT * FROM products WHERE price BETWEEN 100 AND 500;

This is inclusive — both 100 and 500 are part of the result.

---

11. Handling NULL Values

Use IS NULL and IS NOT NULL:

sqlCopyEditSELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

NULL cannot be compared using = or !=.

---

12. Logical Operators: AND, OR, and NOT

You can combine multiple conditions:

sqlCopyEditSELECT * FROM orders 
WHERE status = 'shipped' AND payment_method = 'Credit Card';

sqlCopyEditSELECT * FROM users 
WHERE age > 30 OR city = 'Mumbai';

sqlCopyEditSELECT * FROM employees 
WHERE NOT department = 'Sales';

---

13. Precedence in Conditional Expressions

Parentheses help control evaluation order:

sqlCopyEditSELECT * FROM users 
WHERE (age > 25 AND city = 'Pune') OR is_active = TRUE;

Always group related conditions to avoid logical errors.

---

14. Combining Multiple Conditions

sqlCopyEditSELECT * FROM events
WHERE city = 'Delhi' AND category IN ('Music', 'Art') AND date BETWEEN '2024-01-01' AND '2024-12-31';

You can build powerful filters using combinations of:

• IN
• BETWEEN
• LIKE
• AND/OR logic

---

15. WHERE Clause in Numeric Fields

sqlCopyEditSELECT * FROM invoices WHERE total_amount > 10000;

Use numeric filtering for salaries, scores, stock counts, etc.

---

16. WHERE Clause in Date Fields

sqlCopyEditSELECT * FROM reservations 
WHERE reservation_date >= '2025-01-01' AND reservation_date <= '2025-01-31';

SQL supports date comparisons directly. Use the proper format (YYYY-MM-DD).

---

17. WHERE Clause in String Fields

sqlCopyEditSELECT * FROM customers WHERE name = 'Ravi Kumar';
SELECT * FROM customers WHERE email LIKE '%@yahoo.com';

Always enclose string values in single quotes (').

---

18. Using WHERE with Subqueries

sqlCopyEditSELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Subqueries help filter based on the result of another query.

---

19. Common Mistakes and Troubleshooting

• Using = instead of IS NULL
• Forgetting quotes around string values
• Mixing AND/OR without parentheses
• Using incorrect operators (=> instead of >=)
• Not checking for case sensitivity in string filters

---

20. Summary and What’s Next

The WHERE clause is essential for filtering data in SQL. It supports comparisons, pattern matching, range checks, null checks, and more — making your queries precise and powerful.

---

Table of Contents

1. Introduction
2. What is the SELECT Statement?
3. Basic Syntax of SELECT
4. Selecting All Columns with *
5. Selecting Specific Columns
6. Using Aliases with AS
7. Selecting Distinct Records
8. Filtering Results with WHERE
9. Logical Operators: AND, OR, NOT
10. Comparison Operators
11. Working with NULL
12. Sorting Results with ORDER BY
13. Limiting Results with LIMIT
14. Selecting Based on Ranges: BETWEEN
15. Using IN to Match Multiple Values
16. Pattern Matching with LIKE
17. Combining Conditions
18. Real-World Query Examples
19. Best Practices for SELECT Queries
20. Summary and What’s Next

---

1. Introduction

The SELECT statement is the foundation of SQL. It’s the command used to retrieve data from a database. Whether you’re pulling records from a table, filtering data, or aggregating it for analysis, SELECT is your go-to tool.

In this module, we’ll explore everything you need to know to write your first SELECT queries confidently.

---

2. What is the SELECT Statement?

The SELECT statement retrieves rows from one or more tables or views. It allows you to specify which columns you want, apply conditions, sort, limit, and more.

---

3. Basic Syntax of SELECT

SELECT column1, column2
FROM table_name;

Example:

SELECT name, email FROM users;

---

4. Selecting All Columns with *

The asterisk (*) selects all columns:

SELECT * FROM users;

Useful for quick debugging or exploration, but avoid it in production queries for performance reasons.

---

5. Selecting Specific Columns

You can specify only the columns you need:

SELECT name, age FROM employees;

This improves performance and makes results easier to understand.

---

6. Using Aliases with AS

Aliases give columns or tables temporary names:

SELECT name AS full_name, age AS years_old FROM users;

You can also alias tables:

SELECT u.name FROM users AS u;

---

7. Selecting Distinct Records

To eliminate duplicates:

SELECT DISTINCT city FROM customers;

DISTINCT ensures only unique values are returned.

---

8. Filtering Results with WHERE

The WHERE clause is used to filter records based on conditions.

SELECT * FROM orders WHERE total > 500;

You can use:

• Comparison operators: =, !=, <, >, <=, >=
• Logical operators: AND, OR, NOT
• Special conditions: IN, BETWEEN, LIKE, IS NULL

---

9. Logical Operators: AND, OR, NOT

Combine multiple conditions:

sqlCopyEditSELECT * FROM employees 
WHERE department = 'HR' AND age > 30;

sqlCopyEditSELECT * FROM employees 
WHERE department = 'HR' OR department = 'Finance';

sqlCopyEditSELECT * FROM employees 
WHERE NOT age < 18;

---

10. Comparison Operators

Operator	Meaning
=	Equal to
!= or <>	Not equal to
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater or equal

Example:

sqlCopyEditSELECT * FROM products WHERE price <= 1000;

---

11. Working with NULL

NULL represents missing or unknown data. You cannot use = or != to compare NULL.

Use:

sqlCopyEditSELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

---

12. Sorting Results with ORDER BY

sqlCopyEditSELECT name, age FROM users ORDER BY age;
SELECT name, age FROM users ORDER BY age DESC;

You can sort by one or more columns.

---

13. Limiting Results with LIMIT

Restrict number of rows:

sqlCopyEditSELECT * FROM users LIMIT 5;

MySQL and PostgreSQL support:

sqlCopyEditSELECT * FROM users LIMIT 5 OFFSET 10;

SQLite also supports this syntax.

---

14. Selecting Based on Ranges: BETWEEN

Check if a value falls within a range:

sqlCopyEditSELECT * FROM products WHERE price BETWEEN 100 AND 500;

Inclusive: includes both 100 and 500.

---

15. Using IN to Match Multiple Values

sqlCopyEditSELECT * FROM customers 
WHERE country IN ('India', 'Nepal', 'Bangladesh');

IN is cleaner and more efficient than multiple OR conditions.

---

16. Pattern Matching with LIKE

Use LIKE for partial matches with % and _.

sqlCopyEditSELECT * FROM users WHERE name LIKE 'A%';  -- Starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- Ends with
SELECT * FROM users WHERE name LIKE '_ohn'; -- Matches 4-letter name ending in "ohn"

---

17. Combining Conditions

sqlCopyEditSELECT * FROM employees
WHERE department = 'Sales' 
  AND (age > 30 OR city = 'Delhi');

Use parentheses to group complex logic for clarity and accuracy.

---

18. Real-World Query Examples

Find all active users above 25 years of age:

sqlCopyEditSELECT name, email FROM users
WHERE age > 25 AND is_active = TRUE;

Get top 3 highest-paying jobs:

sqlCopyEditSELECT title, salary FROM jobs
ORDER BY salary DESC
LIMIT 3;

Search for customers not in India or Nepal:

sqlCopyEditSELECT * FROM customers
WHERE country NOT IN ('India', 'Nepal');

---

19. Best Practices for SELECT Queries

• Avoid using SELECT * in production
• Use meaningful aliases for readability
• Filter early with WHERE to improve performance
• Sort data after filtering, not before
• Use indexes wisely for large datasets

---

20. Summary and What’s Next

The SELECT statement is your entry point to reading and analyzing data. Mastering it will help you query any relational database confidently. We’ve covered syntax, filtering, sorting, and limiting — all crucial to practical SQL use.

---

Table of Contents

1. Introduction
2. Why Data Types Matter in SQL
3. Overview of SQL Data Type Categories
4. Integer Types
5. Decimal and Floating-Point Types
6. Character and String Types
7. Date and Time Types
8. Boolean Type
9. Binary and Blob Types
10. ENUM and SET (MySQL Specific)
11. Serial and Auto-Incrementing Types
12. Choosing the Right Data Type
13. Data Type Compatibility Across Databases
14. Implicit and Explicit Type Casting
15. NULL Values and Data Types
16. Performance Implications of Data Types
17. Data Type Errors and Troubleshooting
18. Best Practices for Using Data Types
19. Real-World Schema Example
20. Summary and Next Steps

---

1. Introduction

In SQL, every column in a table must be assigned a data type. Data types determine what kind of data can be stored in each column — numbers, text, dates, boolean values, and more. Choosing the right data type is essential for data integrity, performance, and storage efficiency.

---

2. Why Data Types Matter in SQL

• Validation: Prevents incorrect data from being stored
• Storage Optimization: Saves space with efficient types
• Performance: Speeds up indexing and searching
• Data Integrity: Helps avoid logic and arithmetic errors
• Compatibility: Ensures consistency when exporting/importing data

---

3. Overview of SQL Data Type Categories

Category	Common Data Types
Numeric	INT, BIGINT, DECIMAL, FLOAT
Character/String	CHAR, VARCHAR, TEXT
Date and Time	DATE, TIME, TIMESTAMP, DATETIME
Boolean	BOOLEAN, BOOL
Binary	BLOB, BYTEA
Special/Other	ENUM, SET, SERIAL

---

4. Integer Types

Used to store whole numbers.

Data Type	Range	Notes
TINYINT	-128 to 127	Small storage (1 byte)
SMALLINT	-32,768 to 32,767	Good for small numbers
INT	-2^31 to 2^31-1	Common choice for integers
BIGINT	Very large	Up to 2^63-1, useful for IDs

Example:

age INT;
population BIGINT;

---

5. Decimal and Floating-Point Types

Used for storing numbers with decimals.

• DECIMAL(p, s): Precise fixed-point values.
• FLOAT/REAL/DOUBLE: Approximate values, prone to rounding errors.

Example:

price DECIMAL(10, 2);  -- 99999999.99
weight FLOAT;

Use DECIMAL for financial calculations; FLOAT for scientific or approximate values.

---

6. Character and String Types

Type	Description
CHAR(n)	Fixed length, pads with spaces
VARCHAR(n)	Variable length, up to n characters
TEXT	Long text blocks (blogs, descriptions)

Example:

username VARCHAR(50);
postal_code CHAR(6);
bio TEXT;

Avoid using TEXT unless necessary — it lacks indexing in some systems.

---

7. Date and Time Types

Type	Description
DATE	Only date (YYYY-MM-DD)
TIME	Only time (HH:MM:SS)
DATETIME	Date and time
TIMESTAMP	Unix timestamp + time zone support

Example:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
birthday DATE;

PostgreSQL supports more options like INTERVAL, TIME WITH TIME ZONE.

---

8. Boolean Type

Stores true or false values.

is_active BOOLEAN;

• In MySQL: stored as TINYINT(1) behind the scenes
• In PostgreSQL: true BOOLEAN type

---

9. Binary and Blob Types

Used to store binary data like images, PDFs, or files.

Type	Description
BLOB	Binary Large Object (MySQL)
BYTEA	Binary type (PostgreSQL)

Avoid using binary types for large media — use external storage + reference.

---

10. ENUM and SET (MySQL Specific)

ENUM

status ENUM('active', 'inactive', 'pending');

SET

tags SET('blog', 'news', 'video');

They restrict allowed values and save space but reduce portability.

---

11. Serial and Auto-Incrementing Types

Used for primary key columns that auto-increment:

SQLite:

id INTEGER PRIMARY KEY AUTOINCREMENT

MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:

id SERIAL PRIMARY KEY

---

12. Choosing the Right Data Type

• For IDs: use INT or BIGINT with AUTO_INCREMENT or SERIAL
• For names/emails: VARCHAR(100)
• For monetary values: DECIMAL(10, 2)
• For status flags: BOOLEAN or ENUM
• For timestamps: TIMESTAMP

Avoid over-allocating space — use just enough to fit expected data.

---

13. Data Type Compatibility Across Databases

SQL Standard	SQLite	MySQL	PostgreSQL
INT	INTEGER	INT	INTEGER
VARCHAR(n)	TEXT (dynamic)	VARCHAR(n)	VARCHAR(n)
BOOLEAN	NUMERIC (0/1)	TINYINT(1)	BOOLEAN
TIMESTAMP	TEXT or NUM	TIMESTAMP	TIMESTAMP

SQLite uses dynamic typing, so types are more relaxed.

---

14. Implicit and Explicit Type Casting

SQL can convert data types:

SELECT '123' + 1; -- Implicit cast to integer

Use CAST() or :: (PostgreSQL):

SELECT CAST('2024-01-01' AS DATE);
SELECT '10'::INT;  -- PostgreSQL

---

15. NULL Values and Data Types

NULL means the value is unknown or not applicable.

age INT NULL;

Be cautious: NULL != 0 and NULL != ''

Always use IS NULL or IS NOT NULL when filtering.

---

16. Performance Implications of Data Types

• Smaller types = faster performance
• Indexes on large TEXT or BLOB fields are costly
• Use appropriate precision in numbers (avoid FLOAT if DECIMAL is safer)

---

17. Data Type Errors and Troubleshooting

Common errors include:

• Type mismatch (e.g., inserting text into INT)
• Truncation (e.g., inserting long strings into VARCHAR(10))
• Implicit conversions breaking logic

Solution: always validate types and apply constraints carefully.

---

18. Best Practices for Using Data Types

• Be explicit with data types — avoid relying on defaults
• Always constrain string lengths with VARCHAR(n)
• Use DECIMAL for money, never FLOAT
• Don’t use TEXT unless necessary
• Use BOOLEAN for true/false flags, not strings like ‘yes’/’no’

---

19. Real-World Schema Example

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    is_available BOOLEAN DEFAULT TRUE,
    added_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This schema combines most of the common data types used in real projects.

---

20. Summary and Next Steps

Understanding SQL data types is foundational to writing clean, optimized, and scalable queries. By assigning the right type to each column, you gain better control over your application’s data.