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.

SQL