Jagroop Singh

Posted on Oct 16

SQL Cheatsheet: A Handy Reference for SQL Commands with Examples

#webdev #database #programming #sql

Welcome to the ultimate SQL Cheatsheet! If you're working with databases, you know how crucial SQL is. This cheatsheet provides practical examples for commonly used SQL commands, focusing on SELECT queries, joins, updates, and more. Let’s dive right into it!

1. Basic SQL Commands

SELECT – Fetch Data

-- Select all columns from a table  
SELECT * FROM employees;  

-- Select specific columns  
SELECT name, position FROM employees;

WHERE – Filter Data

-- Fetch employees with a salary greater than 50000  
SELECT name, salary FROM employees WHERE salary > 50000;

-- Fetch employees working in 'HR' department  
SELECT * FROM employees WHERE department = 'HR';

ORDER BY – Sort Results

-- Sort by salary in ascending order  
SELECT name, salary FROM employees ORDER BY salary ASC;

-- Sort by salary in descending order  
SELECT name, salary FROM employees ORDER BY salary DESC;

DISTINCT – Remove Duplicates

-- Get unique job positions  
SELECT DISTINCT position FROM employees;

2. JOIN Operations

INNER JOIN – Fetch Matching Records from Two Tables

-- Get employees with their department names  
SELECT e.name, d.department_name  
FROM employees e  
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN – All Records from the Left Table

-- Get all employees and their departments, if available  
SELECT e.name, d.department_name  
FROM employees e  
LEFT JOIN departments d ON e.department_id = d.id;

RIGHT JOIN – All Records from the Right Table

-- Get all departments and their employees, if available  
SELECT e.name, d.department_name  
FROM employees e  
RIGHT JOIN departments d ON e.department_id = d.id;

3. GROUP BY and Aggregation Functions

COUNT – Count Rows

-- Count the number of employees  
SELECT COUNT(*) FROM employees;

SUM – Sum of Column Values

-- Get the total salary expense  
SELECT SUM(salary) FROM employees;

GROUP BY – Group Data

-- Get the total salary per department  
SELECT department_id, SUM(salary)  
FROM employees  
GROUP BY department_id;

HAVING – Filter Grouped Data

-- Get departments with a total salary greater than 100000  
SELECT department_id, SUM(salary)  
FROM employees  
GROUP BY department_id  
HAVING SUM(salary) > 100000;

4. INSERT, UPDATE, and DELETE

INSERT – Add New Records

-- Insert a new employee record  
INSERT INTO employees (name, position, salary, department_id)  
VALUES ('Alice', 'Manager', 75000, 2);

UPDATE – Modify Records

-- Update salary of an employee  
UPDATE employees  
SET salary = 80000  
WHERE name = 'Alice';

DELETE – Remove Records

-- Delete an employee record  
DELETE FROM employees WHERE name = 'Alice';

5. Advanced Queries

CASE – Conditional Logic in Queries

-- Assign performance categories based on salary  
SELECT name,  
CASE  
  WHEN salary > 70000 THEN 'High'  
  WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'  
  ELSE 'Low'  
END AS performance  
FROM employees;

LIKE – Pattern Matching

-- Fetch employees whose names start with 'A'  
SELECT * FROM employees WHERE name LIKE 'A%';

-- Fetch employees with 'an' in their name  
SELECT * FROM employees WHERE name LIKE '%an%';

6. Views and Indexes

CREATE VIEW – Virtual Table

-- Create a view for high-salary employees  
CREATE VIEW high_salary_employees AS  
SELECT name, salary FROM employees WHERE salary > 70000;

CREATE INDEX – Improve Query Performance

-- Create an index on the salary column  
CREATE INDEX idx_salary ON employees (salary);

7. SQL Functions

String Functions

-- Convert name to uppercase  
SELECT UPPER(name) FROM employees;

-- Concatenate first and last name  
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Date Functions

-- Get current date  
SELECT CURRENT_DATE;

-- Extract year from a date  
SELECT EXTRACT(YEAR FROM hire_date) FROM employees;

8. Data Types and Constraints

Common Data Types

INT: Integer values
VARCHAR(size): String values with a specified length
DATE: Date values
BOOLEAN: True/False values

Adding Constraints

-- Create a table with constraints  
CREATE TABLE employees (  
  id INT PRIMARY KEY,  
  name VARCHAR(100) NOT NULL,  
  salary INT CHECK (salary > 0)  
);

This SQL Cheatsheet covered a wide range of commands and examples to make your work with SQL more efficient. Bookmark it for quick reference and practice writing queries regularly to enhance your SQL skills! Whether you’re filtering data, performing joins, or managing transactions, this guide has you covered.

Happy querying! 🚀

Connect with me at :