5 min readCURD Operation

Select Statement

✅ 1. Selecting All Columns

SELECT * FROM actor

🟦 Explanation: The * wildcard selects all columns from the actors table. 🔎 Useful when you need the entire row for each record.

✅ 2. Selecting Specific Columns

SELECT first_name, last_name FROM actor LIMIT 1

🟦 Explanation: Fetches only the first_name and last_name columns from the table. 🔎 More efficient and readable when you don’t need all columns.

✅ 3. Using Literal Values

SELECT 'Hello World'

🟦 Explanation: Returns the literal string 'Hello World!' as a result. 🔎 Often used to test queries or return hardcoded values. Similar to console.log

✅ 4. Aliasing Columns

SELECT first_name AS FName, last_name AS LName FROM actor LIMIT 1;

🟦 Explanation: Renames output columns using the AS keyword. 🔎 Useful for shorter names, custom formatting, or frontend display.

🔄 Optional: Combine Concepts

SELECT 
	first_name AS FName,
    last_name AS LName,
    'Actor' AS Role
FROM actor LIMIT 2

🟦 Explanation:

• Aliases with spaces using quotes
• Adds a literal value ('Actor') to every row

DISTINCT Keyword

✅ 1. DISTINCT on One Column

SELECT DISTINCT customer_name FROM purchases;

🟰 Shows each unique customer.

✅ 2. DISTINCT on Two Columns (Pair)

SELECT DISTINCT customer_name, product_name FROM purchases

🟰 Shows each unique (customer, product) combination. Combination should be unique

✅ 3. DISTINCT with All Columns

SELECT DISTINCT * FROM purchases

🧨 Why You See “Duplicates” Even With DISTINCT *

• Every row has a unique id.
• Even if other fields are the same, the id column makes each row distinct.

✅ 4. DISTINCT with ORDER BY

SELECT DISTINCT product_name FROM purchases ORDER BY product_name ASC;

🟰 Lists distinct products in alphabetical order.

✅ 5. DISTINCT Inside Aggregates

You can use it inside aggregate functions:

SELECT COUNT(DISTINCT product_name) AS unique_product FROM purchases;

🟰 Counts how many different products were purchased.

✅ 6. DISTINCT ON (…) (PostgreSQL only)

To get the first product per customer:

SELECT DISTINCT ON (customer_name) customer_name, product_name, amount
FROM purchases
ORDER BY customer_name, amount DESC;

🟰 Returns one row per customer, choosing the product with the highest amount. 🔸 Not supported in MySQL or SQLite directly.

Where Clause

✅ 1. Basic Usage

SELECT * FROM film WHERE rating = 'PG-13'

🔹 Explanation: Filters the films table to return only rows where the rating is 'PG-13'.

✅ 2. Using AND, OR, and NOT

SELECT * FROM film WHERE rating = 'PG-13' AND release_year = 2007 LIMIT 2

🔹 Returns films that are both 'PG-13' and released in 2012.

SELECT * FROM film WHERE rating = 'PG-13' OR rating = 'G' LIMIT 2

🔹 Returns films that are either 'PG-13' or 'G'.

SELECT * FROM film WHERE NOT rating = 'G' LIMIT 2

🔹 Returns films that are not rated 'R'.

✅ 3. Comparison Operators

SELECT * FROM film WHERE length > 50 AND length < 80 LIMIT 2

🔹 Use =, != or <>, <, >, <=, >= for comparing values.

✅ 4. Range Filtering with BETWEEN

SELECT * FROM film WHERE release_year BETWEEN 2006 AND 2007 LIMIT 2

🔹 Returns films released between 2010 and 2020 (inclusive).

✅ 5. Set Filtering with IN

SELECT * FROM film WHERE rating IN ('G', 'PG', 'PG-13') LIMIT 2

🔹 Filters rows where rating matches any value in the list.

SELECT * FROM film WHERE rating NOT IN('G', 'PG-13', 'PG') LIMIT 2

🔹 Excludes rows matching values in the list.

✅ 6. Pattern Matching with LIKE

SELECT * FROM actor WHERE last_name LIKE 's%' LIMIT 2

🔹 Finds actors whose last names start with 'S' or 's' case insensitive.

SELECT * FROM actor WHERE first_name LIKE '%an%' LIMIT 2

🔹 Finds names containing 'an'.

Symbol	Meaning
%	Zero or more characters
_	Exactly one character

✅ 7. IS NULL and IS NOT NULL

SELECT * FROM customer WHERE email IS NULL LIMIT 1

🔹 Returns customers without email addresses.

SELECT * FROM customer WHERE email IS NOT NULL LIMIT 1

🔹 Returns customers with phone numbers.

✅ 8. Subqueries in WHERE

SELECT * FROM film_actor WHERE actor_id IN (SELECT actor_id FROM actor) LIMIT 2

🔹 Filters orders based on nested query from another table.

✅ 9. Arithmetic in WHERE

SELECT * FROM product WHERE price*quantity > 13000

🔹 Filters based on a calculated expression.

📌 Bonus: WHERE with UPDATE and DELETE

UPDATE users SET is_active = 0 WHERE last_login < '2023-01-01';
DELETE FROM sessions WHERE is_expired = true;

📘 SQL Comparison Operators Reference

Comparison operators are used in the WHERE, HAVING, ON, and CASE clauses to compare values and filter rows.

Operator	Description	Example	Result
=	Equal to	price = 100	Matches rows where price is exactly 100
!=	Not equal to (ANSI SQL)	status != ‘inactive’	Matches rows where status is not ‘inactive’
<>	Not equal to (standard)	rating <> ‘R’	Matches rows where rating is not ‘R’
<	Less than	quantity < 50	Matches rows where quantity is less than 50
>	Greater than	price > 500	Matches rows where price is more than 500
<=	Less than or equal to	age <= 18	Matches rows where age is 18 or younger
>=	Greater than or equal to	score >= 90	Matches rows where score is 90 or higher
IS NULL	Is null value	email IS NULL	Matches rows where email is missing or undefined
IS NOT NULL	Is not null value	email IS NOT NULL	Matches rows with a defined email
BETWEEN	Within a range	price BETWEEN 100 AND 200	Matches prices including 100 and 200
IN (…)	Matches any in list	status IN (‘new’, ‘open’)	Matches rows where status is either ‘new’ or ‘open’
NOT IN (…)	Not in list	id NOT IN (1, 2, 3)	Excludes rows with those id values
LIKE	Pattern match (text)	name LIKE ‘A%’	Matches names starting with “A”
NOT LIKE	Not matching pattern	email NOT LIKE ‘%.edu’	Excludes emails ending with .edu

🔍 Usage in WHERE Clause

SELECT * FROM employees WHERE salary > 60000;
SELECT * FROM customers WHERE status IN ('active', 'pending');
SELECT * FROM products WHERE name LIKE '%Phone%';

🧠 Notes:

• Use != or <> for not equal to — both work in most SQL dialects.

SELECT * FROM film WHERE rating != 'PG-13' LIMIT 1

SELECT * FROM film WHERE rating <> 'PG-13' LIMIT 1

SELECT * FROM film WHERE NOT rating = 'PG-13' LIMIT 1

• IS NULL and IS NOT NULL are special comparisons for NULL values — do not use = NULL.
• LIKE supports % (wildcard for any number of characters) and _ (one character).

Would you like this in Markdown or a cheat sheet format (PDF or printable)? Ask ChatGPT

Scalar Function

Category	Description	Example Functions
String Functions	Operate on string values	LENGTH(), LOWER(), UPPER(), SUBSTRING(), CONCAT(), TRIM(), REPLACE()
Numeric Functions	Perform operations on numbers	MOD(), ROUND(), CEIL(), FLOOR(), ABS(), POWER(), SQRT()
Date/Time Functions	Operate on date and time values	NOW(), DATE_ADD(), DATEDIFF(), CURDATE(), YEAR(), MONTH(), DAY()
Conversion Functions	Convert data from one type to another	CAST(), CONVERT(), TO_CHAR(), TO_DATE()
Miscellaneous	Other utility functions	COALESCE(), IFNULL(), NULLIF(), ISNULL(), GREATEST(), LEAST()

ORDER BY Clause

The ORDER BY clause is used to sort the result set of a query by one or more columns, either in ascending (ASC) or descending (DESC) order.

✅ Syntax:

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

• ASC = Ascending (default)
• DESC = Descending

🔹 1. Sort by a Single Column

SELECT * FROM film
ORDER BY rental_duration DESC;

🟦 Explanation: Sorts the films in descending order of rental duration — longest rentals first.

🔹 2. Sort by Multiple Columns

SELECT * FROM film
ORDER BY title, release_year;

🟦 Explanation: First sorts by title (ascending by default), and then for films with the same title, sorts by release_year.

🔹 3. Explicit Order for Each Column

SELECT * FROM film
ORDER BY rating DESC, release_year ASC;

🟦 Explanation: Sorts by rating from high to low, and then by release_year from old to new within the same rating group.

🔹 4. Sort by Column Index (Not Recommended)

SELECT * FROM film ORDER BY 2 ASC LIMIT 10;

🟦 Explanation: Sorts by the second selected column (rental_duration) in descending order. ⚠️ Not recommended — column names are safer and clearer.

🔹 5. Combine with LIMIT for Top/Bottom Results

SELECT title, rental_rate FROM film
ORDER BY rental_rate DESC
LIMIT 5;

🟦 Explanation: Returns the top 5 highest rental rate films.

UPDATE, DELETE, TRUNCATE, and DROP Explained

1. UPDATE Command: Used to modify existing records. UPDATE table_name SET column_name = value WHERE condition;
2. DELETE Command: Removes rows from a table. DELETE FROM table_name WHERE condition;
3. DROP Command: Removes the entire table from the database. DROP TABLE table_name;
4. TRUNCATE Command: Removes all data from a table but retains the table structure. TRUNCATE TABLE table_name;

• Safe Mode: In MySQL, “safe mode” enforces the use of a WHERE clause for UPDATE and DELETE statements .