A-Z SQL Overview

SQL, or Structured Query Language, is the backbone of relational databases, allowing users to manage, manipulate, and retrieve data efficiently. Whether you're a beginner or an advanced SQL user, understanding the A-Z of SQL is crucial for database mastery. In this comprehensive guide, we’ll walk through essential SQL concepts, commands, and best practices with real-world examples.

A - Aggregate Functions

Aggregate functions perform calculations on multiple rows of data and return a single value. These include:

• COUNT() - Returns the number of rows.
• SUM() - Calculates the total sum of a numeric column.
• AVG() - Computes the average value of a column.
• MIN() / MAX() - Finds the smallest or largest value in a column.

Example:

SELECT department, AVG(salary) FROM employees GROUP BY department; 

This query calculates the average salary per department.

B - BETWEEN

The BETWEEN operator filters values within a specific range.

Example:

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

This query retrieves all orders placed in 2023.

C - CREATE TABLE

Used to create a new table.

Example:

CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) UNIQUE ); 

This creates a customers table with unique email constraints.

D - DELETE

Removes records from a table.

Example:

DELETE FROM employees WHERE department = 'Sales'; 

Deletes all employees in the Sales department.

E - EXISTS

Checks if a subquery returns results.

Example:

SELECT * FROM customers WHERE EXISTS ( SELECT 1 FROM orders WHERE customers.customer_id = orders.customer_id ); 

Returns customers who have placed an order.

F - FOREIGN KEY

Links two tables together.

Example:

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id); 

Ensures referential integrity between orders and customers.

G - GROUP BY

Groups results based on column values.

Example:

SELECT department, COUNT(*) FROM employees GROUP BY department; 

Counts employees in each department.

H - HAVING

Filters results after grouping.

Example:

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10; 

Only shows departments with more than 10 employees.

I - INNER JOIN

Combines data from two tables.

Example:

SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id; 

Fetches employee names along with their department names.

J - JOIN

Merges records from multiple tables based on a common field.

Example:

SELECT orders.order_id, customers.name FROM orders JOIN customers ON orders.customer_id = customers.customer_id; 

Lists orders along with customer names.

K - KEY

A unique identifier in a table, such as a Primary or Foreign Key.

Example:

PRIMARY KEY (employee_id) 

Uniquely identifies an employee.

L - LIKE

Searches for patterns in text data.

Example:

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

Finds customers whose names start with 'A'.

M - MODIFY

Alters an existing table.

Example:

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2); 

Changes the salary column to a decimal format.

N - NULL

Represents missing or undefined data.

Example:

SELECT * FROM customers WHERE email IS NULL; 

Finds customers without an email.

O - ORDER BY

Sorts query results.

Example:

SELECT * FROM employees ORDER BY salary DESC; 

Sorts employees by salary in descending order.

P - PRIMARY KEY

Uniquely identifies a row.

Example:

PRIMARY KEY (customer_id) 

Ensures each customer has a unique ID.

Q - QUERY

A request for database information.

Example:

SELECT * FROM products; 

Retrieves all products.

R - ROLLBACK

Undoes uncommitted transactions.

Example:

ROLLBACK; 

Restores the database to its previous state.

S - SELECT

Retrieves data.

Example:

SELECT name, email FROM customers; 

Gets customer names and emails.

T - TRUNCATE

Removes all records from a table without logging individual deletions.

Example:

TRUNCATE TABLE orders; 

Deletes all orders.

U - UPDATE

Modifies existing records.

Example:

UPDATE employees SET salary = salary * 1.1 WHERE department = 'HR'; 

Increases salaries in the HR department by 10%.

V - VIEW

A virtual table.

Example:

CREATE VIEW high_salary AS SELECT * FROM employees WHERE salary > 70000; 

Creates a view for high-salary employees.

W - WHERE

Filters query results.

Example:

SELECT * FROM employees WHERE department = 'IT'; 

Finds IT department employees.

X - (E)XISTS

Tests row existence.

Example:

SELECT 1 FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id); 

Checks if customers have placed orders.

Z - ZERO

Represents the absence of a value in numeric fields.

Example:

SELECT * FROM inventory WHERE quantity = 0; 

Finds out-of-stock items.

This A-Z SQL guide serves as a foundation for anyone looking to master database management. By understanding and applying these SQL concepts, you’ll be well on your way to becoming an SQL expert!

WhatsApp Channel:
https://whatsapp.com/channel/0029VahGttK5a24AXAJDjm2R

SQL Mindmap👇
https://t.me/sqlresourcestp/13

Like this post if you need more 👍❤️

Hope it helps :)