SQL

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.

Scroll to Top