SQL Cheatsheet: Core Commands, Joins, and Functions

By /

Featured Image
SQL (Structured Query Language) is essential for managing relational databases. Below is a detailed breakdown of SQL commands, clauses, joins, and functions, along with practical examples.

1️⃣ Core SQL Commands

DDL (Data Definition Language)

CREATE TABLE employees (id INT, name VARCHAR(100), salary DECIMAL); 
DROP TABLE employees; 
ALTER TABLE employees ADD COLUMN department VARCHAR(50); 
TRUNCATE TABLE employees; 
CREATE VIEW emp_view AS SELECT id, name FROM employees;

DML (Data Manipulation Language)

INSERT INTO employees VALUES (1, 'John Doe', 75000); 
UPDATE employees SET salary = 80000 WHERE id = 1; 
DELETE FROM employees WHERE id = 1;

DCL (Data Control Language)

GRANT SELECT ON employees TO user1; 
REVOKE SELECT ON employees FROM user1;

DQL (Data Query Language)

SELECT  FROM employees; 
SELECT name, salary FROM employees WHERE salary > 70000;

2️⃣ Clauses & Operators

SELECT  FROM employees WHERE salary BETWEEN 50000 AND 100000; 
SELECT  FROM employees WHERE department IN ('IT', 'HR'); 
SELECT  FROM employees WHERE name LIKE 'J%'; 
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;

3️⃣ ORDER BY Examples

SELECT  FROM employees ORDER BY salary ASC; 
SELECT  FROM employees ORDER BY hire_date DESC;

4️⃣ Joins

SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.dept_id = d.id;

SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.dept_id = d.id;

5️⃣ Functions

Aggregate Functions

SELECT COUNT() FROM employees; 
SELECT AVG(salary) FROM employees; 
SELECT MAX(salary), MIN(salary) FROM employees;

Window Functions

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as salary_rank 
FROM employees;

SELECT name, salary, LAG(salary) OVER (ORDER BY hire_date) as prev_salary 
FROM employees;

6️⃣ Aliasing

SELECT e.name AS employee_name, d.name AS department_name 
FROM employees e 
JOIN departments d ON e.dept_id = d.id;

7️⃣ Table Management

CREATE TABLE projects (id INT, name VARCHAR(100), deadline DATE); 
ALTER TABLE projects ADD COLUMN status VARCHAR(20); 
TRUNCATE TABLE projects; 
DROP TABLE projects;

8️⃣ Data Handling

SELECT  FROM employees; 
SELECT id, name FROM employees;

9️⃣ Views

CREATE VIEW high_salary_employees AS 
SELECT name, salary FROM employees WHERE salary > 80000;

DROP VIEW high_salary_employees;

🔟 Schema Reference

  • DATABASE → Contains TABLES → Contains FIELDS
  • TABLE → Stores ROWS (Records)

    You Should Know: Practical SQL Commands in Linux & Windows

Running SQL in Linux (MySQL/MariaDB)

sudo apt install mysql-server 
sudo systemctl start mysql 
mysql -u root -p

Exporting Query Results to CSV (Linux)

mysql -u user -p -e "SELECT  FROM employees" database_name > output.csv

Running SQL in Windows (SQL Server)

sqlcmd -S localhost -U sa -P your_password -Q "SELECT  FROM employees"

Automating SQL Backups (Linux Cron Job)

0 3    mysqldump -u root -p your_db > /backups/db_backup.sql

SQL Injection Prevention (PHP Example)

$stmt = $pdo->prepare("SELECT  FROM users WHERE username = :username"); 
$stmt->execute(['username' => $input_username]);

What Undercode Say

SQL remains a fundamental skill for database management, cybersecurity (SQL injection), and DevOps (managing cloud databases). Mastering these commands enhances efficiency in querying and securing databases.

Related Linux Commands for DB Admins

pg_dump -U postgres dbname > backup.sql  PostgreSQL backup 
sqlite3 database.db ".dump" > backup.sql  SQLite backup

Windows SQL Server Management

Get-Service -Name MSSQLSERVER  Check SQL Server status 
Start-Service MSSQLSERVER  Start SQL Server

Security Best Practices

-- Always sanitize inputs 
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password'; 
GRANT SELECT, INSERT ON db. TO 'app_user'@'localhost';

Expected Output

A comprehensive SQL reference with practical commands for database administrators, developers, and cybersecurity professionals.

Prediction

As databases grow, AI-driven SQL optimization will automate query tuning, and NoSQL-SQL hybrid models will become more prevalent in cloud environments.

Further Reading:

References:

Reported By: Naresh Kumari – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram

Related Posts: