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 :)
Top comments (0)