Scribd
Upload
36 views5 pages

SQL Syntax Cheat Sheet with Examples

Uploaded by

nithinkumarsharatha2002
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
36 views5 pages

SQL Syntax Cheat Sheet with Examples

Uploaded by

nithinkumarsharatha2002
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Cheat Sheet — Syntax & Examples

SQL CHEAT SHEET — Syntax & Examples


A compact, practical reference for learning SQL. Examples use ANSI SQL and common RDBMS syntax (MyS

1. Basics — SELECT
SELECT column1, column2
FROM table_name;

Example:
SELECT id, name, email
FROM users;

2. Filtering — WHERE / Operators


SELECT * FROM products
WHERE price > 100 AND stock > 0;

Common operators:
=, <>, !=, >, <, >=, <=, BETWEEN, IN (...), LIKE 'pattern', IS NULL

3. Sorting & Limiting — ORDER BY / LIMIT / FETCH


ORDER BY column ASC|DESC
LIMIT n -- (MySQL, PostgreSQL)
-- SQL Server: SELECT ... ORDER BY ... OFFSET 0 ROWS FETCH NEXT n ROWS ONLY

Example:
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10;

4. Aggregate Functions & GROUP BY / HAVING


Aggregates: COUNT(), SUM(), AVG(), MIN(), MAX()

Example:
SELECT customer_id, COUNT(*) AS orders_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

5. Joins — combining tables


INNER JOIN: returns matching rows only
LEFT JOIN: all left rows + matches
RIGHT JOIN: all right rows + matches
FULL OUTER JOIN: all rows from both (not supported in MySQL without workaround)

Example:
SELECT [Link], [Link], [Link] AS order_id, [Link]
FROM users u
INNER JOIN orders o ON [Link] = o.user_id;

6. Subqueries (nested queries)


Scalar subquery, IN, EXISTS

Example (IN):
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');

Example (EXISTS):
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = [Link] AND [Link] = 'shipped');

7. Set operators
UNION (removes duplicates), UNION ALL (keeps duplicates), INTERSECT, EXCEPT (or MINUS)

Example:
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

8. Data Modification — INSERT / UPDATE / DELETE


INSERT INTO table (col1, col2) VALUES (v1, v2);
-- Insert multiple rows
INSERT INTO fruits (name, color) VALUES ('Apple','Red'), ('Banana','Yellow');

UPDATE table SET col = val WHERE condition;


DELETE FROM table WHERE condition;

Example:
UPDATE products SET price = price * 1.1 WHERE category = 'Books';
DELETE FROM sessions WHERE last_active < '2024-01-01';

9. DDL — CREATE / ALTER / DROP


CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);


DROP TABLE old_table;

10. Constraints & Indexes


Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK
Example:
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

Create index:
CREATE INDEX idx_users_email ON users(email);

11. Transactions & Locking (TCL)


BEGIN; -- or START TRANSACTION;
-- multiple statements
COMMIT; -- persist
ROLLBACK; -- undo

Example:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

12. Views, Materialized Views


CREATE VIEW active_customers AS
SELECT id, name FROM customers WHERE active = TRUE;

-- Materialized view (Postgres):


CREATE MATERIALIZED VIEW mv_sales AS SELECT date_trunc('day', created_at) AS day, SUM(total) FROM o

13. Window (analytic) functions


OVER() clause, ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER (PARTITION BY ... ORDER BY ...)

Example:
SELECT id, amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING A
FROM payments;

14. Common Table Expressions (CTE)


WITH cte_name AS (
SELECT ...
)
SELECT * FROM cte_name;

Example:
WITH top_customers AS (
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10
)
SELECT c.*, t.total_spent FROM customers c JOIN top_customers t ON [Link] = t.customer_id;

15. Stored Procedures & Functions (brief)


-- MySQL example (simplified):
DELIMITER $$
CREATE PROCEDURE add_product(IN name VARCHAR(100), IN price DECIMAL(10,2))
BEGIN
INSERT INTO products (name, price) VALUES (name, price);
END$$
DELIMITER ;

16. Error handling / Best practices


- Always use parameterized queries / prepared statements to avoid SQL injection.
- Use transactions for multi-step updates.
- Prefer explicit column lists in INSERT.
- Index columns used in JOIN, WHERE, ORDER BY (but avoid over-indexing).
- Use LIMIT for large queries during development.

Example (parameterized in pseudo):


SELECT * FROM users WHERE email = ?;

17. Useful built-in functions (strings, numbers, dates)


Strings: CONCAT(), LENGTH(), SUBSTRING(), REPLACE()
Numbers: ROUND(), FLOOR(), CEIL(), ABS()
Dates: NOW(), CURRENT_DATE, DATE_ADD()/DATE_SUB() or INTERVAL (Postgres: NOW() + INTERVAL '1 day')

Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT DATE_TRUNC('month', created_at) FROM orders;

18. Examples: Reporting queries


1) Monthly sales:
SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS sales
FROM orders
GROUP BY month
ORDER BY month;

2) Top 5 products by sales:


SELECT [Link], [Link], SUM([Link]) AS qty_sold
FROM order_items oi
JOIN products p ON [Link] = oi.product_id
GROUP BY [Link], [Link]
ORDER BY qty_sold DESC
LIMIT 5;

19. Cheats & Differences across engines


- AUTO_INCREMENT (MySQL) vs SERIAL (Postgres) vs IDENTITY (SQL Server)
- LIMIT (MySQL/Postgres) vs TOP (SQL Server) vs FETCH/OFFSET
- CONCAT in MySQL, || operator in Postgres

Tip: Check your RDBMS docs for tiny syntax differences.

20. Next steps & learning path


- Practice SELECTs, JOINs, GROUP BY, subqueries.
- Learn indexing and query planning (EXPLAIN / EXPLAIN ANALYZE).
- Build small projects and use prepared statements.
- Explore advanced topics: window functions, partitioning, replication, performance tuning.

Appendix: Quick reference examples


CREATE TABLE example (
id INT PRIMARY KEY,
name VARCHAR(100),
value DECIMAL(10,2)
);

INSERT INTO example (id, name, value) VALUES (1, 'A', 10.5);

SELECT * FROM example WHERE value BETWEEN 5 AND 20 ORDER BY name ASC;

Common questions

Powered by AI

Strategies include using LIMIT for large queries during development to minimize data retrieval, indexing columns used in JOIN, WHERE, and ORDER BY to speed up search queries, and using parameterized queries to enhance security and efficiency. However, over-indexing can lead to increased disk space usage and slower update/delete operations, as indexes need to be maintained whenever data changes .

Constraints maintain database integrity by enforcing rules that data must adhere to. The FOREIGN KEY constraint ensures referential integrity by restricting values in one table to values from another. For example, a FOREIGN KEY from the orders table (user_id) referencing the users table's id column ensures users associated with orders exist, preventing orphaned records .

When using DDL commands, principles include ensuring you have comprehensive backups as they change the structure of the database and can result in data loss; understanding the implications on existing data, especially with DROP and ALTER; planning future scalability and changes to minimize frequent structural changes. Use descriptive naming for variables and tables to maintain clarity and ease of understanding .

Views are beneficial for encapsulating complex queries and providing a simplified interface. They dynamically retrieve data, ensuring up-to-date results with minimal storage use. Materialized views improve performance by caching the result set, which is useful for memory-intensive queries. However, they require more storage and manual updates or refreshing mechanisms to stay current, adding maintenance overhead .

Subqueries provide the capability to perform operations that require intermediate query results for further filtering or calculations. They are suitable when querying internal results that are re-used multiple times or when the query structure is more intuitive as a nested entity. For example, using IN to filter products by categories is simpler with subqueries, whereas joins are ideal for combining related datasets efficiently .

Efficient table design involves selecting appropriate data types to minimize storage and improve performance, using VARCHAR for variable-length strings, and choosing INT for numerical data when suitable. Implementing PRIMARY KEY for unique row identification and indexing commonly used columns enhance data retrieval speed. Plan for constraints like NOT NULL for mandatory fields and UNIQUE for distinct values to ensure data integrity .

INNER JOIN returns only matching rows from both tables based on the specified condition. LEFT JOIN returns all rows from the left table and the matched rows from the right table; unmatched rows in the right table result in NULL. RIGHT JOIN returns all rows from the right table, and the matched rows from the left table; unmatched rows in the left table result in NULL .

Window functions like ROW_NUMBER() operate over a set of rows and return a value for each row within the window (set of rows). They do not reduce the number of rows returned like aggregate functions do. For example, ROW_NUMBER() assigns a unique sequential integer to rows within a partition, whereas aggregate functions provide a summary result (like SUM) for the entire rowset in a group .

Transactions ensure data integrity by bundling multiple operations into a single unit of work, guaranteeing all operations complete successfully, or none do. This is critical for data consistency in failure scenarios. For example, banking transactions often include deducting from one account and crediting another. Using BEGIN, COMMIT, and ROLLBACK can prevent partial updates, ensuring the correct balance across accounts .

Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() perform calculations on a set of values and return a single value. GROUP BY is used to arrange identical data into groups and perform aggregate functions on each group. For example, to find the total amount spent by each customer, use: SELECT customer_id, SUM(total) AS total_spent FROM orders GROUP BY customer_id; this query groups orders by customer_id and calculates the total spending per customer .

You might also like