SQL
Elaboration on the mentioned SQL concepts with examples and emojis to make them more engaging:
Basic Queries
SELECT
The SELECT
statement is used to fetch data from a database.
SELECT name, age FROM users;
🗂️ Fetches the name and age of all users.
WHERE
The WHERE
clause is used to filter records that meet certain criteria.
SELECT * FROM users WHERE age > 25;
🔍 Selects all users older than 25.
DISTINCT
The DISTINCT
keyword is used to return unique values.
SELECT DISTINCT city FROM users;
🌆 Returns unique cities from the users table.
LIKE
The LIKE
operator is used for pattern matching.
SELECT * FROM users WHERE name LIKE 'A%';
🔡 Finds users whose names start with ‘A’.
BETWEEN
The BETWEEN
operator filters data within a specified range.
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
📅 Selects orders made in 2023.
ORDER BY
The ORDER BY
clause is used to sort the result set.
SELECT * FROM users ORDER BY age DESC;
⬇️ Sorts users by age in descending order.
LIMIT
The LIMIT
clause is used to specify the number of records to return.
SELECT * FROM users LIMIT 10;
🔢 Returns the first 10 users.
GROUP BY
The GROUP BY
statement is used to arrange identical data into groups.
SELECT city, COUNT(*) FROM users GROUP BY city;
📊 Groups users by city and counts them.
HAVING CLAUSE
The HAVING
clause is used to filter groups based on a condition.
SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;
🔎 Returns cities with more than 5 users.
INSERT
The INSERT
statement is used to add new records to a table.
INSERT INTO users (name, age, city) VALUES ('Alice', 30, 'New York');
➕ Inserts a new user into the users table.
UPDATE
The UPDATE
statement modifies existing records.
UPDATE users SET age = 31 WHERE name = 'Alice';
🔄 Updates Alice’s age to 31.
ALTER
The ALTER
statement is used to modify the structure of a database table.
ALTER TABLE users ADD COLUMN email VARCHAR(255);
🛠️ Adds an email column to the users table.
IMPORT
Importing data typically involves loading data from external files into a database table.
LOAD DATA INFILE '/path/to/file.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\\\n' (name, age, city);
📥 Imports data from a CSV file into the users table.
Data Types
Specifies the type of data that a column can hold (e.g., INTEGER, VARCHAR, DATE).
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
signup_date DATE
);
📅 Defines data types for a users table.
Advanced Queries
Date Time Function
Functions that work with date and time data types.
SELECT NOW();
🕒 Returns the current date and time.
Window Function
Functions that perform operations across a set of table rows related to the current row.
SELECT name, age, AVG(age) OVER (PARTITION BY city) AS avg_age FROM users;
🪟 Calculates the average age of users in each city.
Sub Query
A query nested inside another query.
SELECT name FROM users WHERE age = (SELECT MAX(age) FROM users);
🔄 Finds the name of the oldest user.
CASE WHEN Statement
Provides if-then-else logic within SQL queries.
SELECT name, age,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age >= 18 AND age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM users;
⚖️ Classifies users into age groups.
CTE (Common Table Expressions)
Temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
WITH avg_age AS (
SELECT city, AVG(age) AS avg_age FROM users GROUP BY city
)
SELECT users.name, users.city, avg_age.avg_age
FROM users
JOIN avg_age ON users.city = avg_age.city;
🔗 Uses a CTE to find average age per city and join it with users.
Query Optimization
Improving query performance.
EXPLAIN SELECT * FROM users WHERE age > 25;
⚙️ Analyzes how the query is executed and provides optimization insights.
JOINS
Inner Join
Returns records that have matching values in both tables.
SELECT users.name, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;
🔗 Finds users and their orders.
Outer Join
Returns all records when there is a match in either left or right table.
SELECT users.name, orders.order_date
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
🔗 Finds all users and orders, including those without matches.
Left Join
Returns all records from the left table and matched records from the right table.
SELECT users.name, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
⬅️ Finds all users and their orders, including users without orders.
Right Join
Returns all records from the right table and matched records from the left table.
SELECT users.name, orders.order_date
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
➡️ Finds all orders and their users, including orders without users.
These examples cover the basics and some advanced SQL concepts, illustrated with emojis to help remember their functions.