📘 CRUD Operations

In this chapter, we will explore the core **CRUD** operations in SQL: `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. These operations are essential for interacting with relational databases. We'll also cover how to filter data using `WHERE` clauses along with various comparison operators like `IN`, `LIKE`, and `BETWEEN`.

💡 Understanding CRUD Operations

• `CREATE`: Adding new records to a database (INSERT)
• `READ`: Retrieving data from a database (SELECT)
• `UPDATE`: Modifying existing records in a database (UPDATE)
• `DELETE`: Removing records from a database (DELETE)

💡 Basic SELECT Statement

The `SELECT` statement is used to retrieve data from one or more tables in a database.

SELECT column1, column2 FROM table_name;

💡 Filtering Data with WHERE Clause

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

SELECT * FROM customers WHERE age > 25;

• `=` Equals
• `>` Greater than
• `<` Less than
• `>=` Greater than or equal to
• `<=` Less than or equal to
• `<>` Not equal to

💡 IN Operator

The `IN` operator is used to check if a value exists in a specified list of values.

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

💡 LIKE Operator

The `LIKE` operator is used to search for a specified pattern in a column.

SELECT * FROM customers WHERE name LIKE 'S%';

• `%`: Matches any number of characters
• `_`: Matches a single character

💡 BETWEEN Operator

The `BETWEEN` operator is used to filter values within a range.

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

✅ INSERT Statement

The `INSERT INTO` statement is used to add new records to a table.

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

✅ UPDATE Statement

The `UPDATE` statement is used to modify existing records in a table.

UPDATE users SET email = 'john.new@example.com' WHERE id = 1;

✅ DELETE Statement

The `DELETE` statement is used to remove records from a table.

DELETE FROM users WHERE id = 1;

❌ Common Mistakes to Avoid

• Always use a `WHERE` clause with `DELETE` or `UPDATE` statements to avoid modifying all records.
• Be careful with wildcard operators (`%`) in `LIKE` statements as they can impact performance.
• Never use `SELECT *` in production queries - specify only the columns you need.

💡 Best Practices for CRUD Operations

• Use parameterized queries to prevent SQL injection attacks.
• Index your tables properly to optimize query performance.
• Test your queries in a development environment before deploying them.

📘 Sorting and Limiting

Sorting and limiting data is a fundamental aspect of working with databases. In SQL, we use the `ORDER BY` clause to sort records in ascending or descending order, and the `LIMIT`, `TOP`, or `FETCH` clauses to control the number of rows returned. These operations are essential for efficiently retrieving and presenting data.

💡 Sorting Data with ORDER BY

The `ORDER BY` clause is used to sort records in a result set based on one or more columns. By default, the sorting is done in ascending order (`ASC`). To sort in descending order, you can use the `DESC` keyword.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

You can sort by multiple columns and specify different orders for each column:

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC, column2 ASC;

💡 Handling NULL Values in Sorting

By default, `NULL` values are treated as the lowest possible value when sorting. You can control this behavior using the `NULLS FIRST` or `NULLS LAST` clauses (supported in PostgreSQL and other modern databases).

SELECT column1
FROM table_name
ORDER BY column1 ASC NULLS LAST;

💡 Limiting Results with LIMIT/OFFSET

The `LIMIT` clause is used to restrict the number of rows returned by a query. It's commonly used together with `OFFSET` to implement pagination.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC
LIMIT 10 OFFSET 20;

This query returns rows 21 to 30 (10 rows total) from the sorted result set.

💡 Alternative Limiting Syntax for Different Databases

Different database systems may use different syntax for limiting results:

• `TOP` clause in SQL Server: `SELECT TOP 10 * FROM table_name;`
• `FETCH FIRST n ROWS ONLY` in PostgreSQL: `SELECT * FROM table_name FETCH FIRST 10 ROWS ONLY;`

✅ Best Practices for Sorting and Limiting

• Always use indexes on columns used in `ORDER BY` clauses to improve performance.
• Use `LIMIT` or similar clauses to prevent returning unnecessary large result sets.
• Consider using `OFFSET` with `FETCH NEXT` for pagination in PostgreSQL.
• Avoid sorting on expensive operations (e.g., functions or expressions) when possible.

❌ Common Mistakes to Avoid

• Don't sort more data than necessary. Limit the result set before sorting.
• Avoid using `SELECT *` with sorting and limiting if you only need specific columns.
• Don't forget to handle `NULL` values appropriately in your queries.

✅ Practical Examples

-- Get the top 5 highest-paid employees
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Paginate through results (showing rows 21-30)
SELECT product_id, product_name, price
FROM products
ORDER BY price ASC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

📘 Aliases and Expressions

In SQL, **aliases** allow you to temporarily rename columns or tables in your query results. This can improve readability or help avoid column name conflicts. **Expressions** enable you to perform calculations or manipulations on data within the query.

• `AS`: Used to alias column names or table names.
• `CASE`: Conditional statements for creating calculated fields.
• Built-in functions like `LENGTH`, `ROUND`, `LOWER`, etc. for data manipulation.
• String concatenation using `||` operator.

💡 Basic Aliases with AS

The `AS` keyword is used to rename columns or tables in the result set. It's optional, but recommended for clarity.

SELECT customer_name AS FullName, 
       order_date AS OrderDate
FROM customers;

❌ Common Mistakes with Aliases

• Don't use spaces in alias names without quotes.
• Avoid using reserved SQL keywords as aliases.
• Don't forget to reference aliases correctly in ORDER BY or WHERE clauses.

✅ Advanced Expressions with CASE

The `CASE` statement allows you to create conditional expressions within your SQL queries, similar to an if-else structure.

SELECT product_name,
       CASE
           WHEN price < 10 THEN 'Cheap'
           WHEN price BETWEEN 10 AND 50 THEN 'Moderate'
           ELSE 'Expensive'
       END AS PriceCategory
FROM products;

💡 String Manipulation and Built-in Functions

SQL provides many built-in functions for string manipulation, rounding numbers, converting data types, and more.

SELECT 
   LENGTH(first_name) AS NameLength,
   ROUND(price * 1.075, 2) AS TaxInclusivePrice,
   LOWER(last_name) AS LowercaseName
FROM users;

✅ String Concatenation

The `||` operator is used for string concatenation in SQL.

SELECT first_name || ' ' || last_name AS FullName
FROM users;

💡 Special Functions: GREATEST and LEAST

The `GREATEST` and `LEAST` functions allow you to find the maximum or minimum value among multiple expressions.

SELECT LEAST(price, discounted_price) AS FinalPrice
FROM products;

✅ Best Practices for Using Aliases and Expressions

• Always use `AS` for aliases to improve readability.
• Use meaningful alias names that describe their purpose.
• Test complex expressions with sample data first.
• Avoid using reserved keywords as aliases unless necessary.