SQL

• cheatsheet for sql cuz i have goldfish memory

• used a lot for [[SQL Injection]]

Data Selection:

• SELECT: Choose specific columns from a table.

• *: Select all columns from a table.

• FROM: Specify the table containing the data.

• WHERE: Filter results based on conditions.

• AND: Combine multiple conditions (all must be true).

• OR: Combine multiple conditions (any can be true).

• ORDER BY: Sort the results by a specific column (ascending/descending).

• LIMIT: Restrict the number of returned rows.

Basic Operators:

• =: Equal to.

• !=: Not equal to.

• <: Less than.

• <=: Less than or equal to.

• >: Greater than.

• >=: Greater than or equal to.

• LIKE: Search for patterns (with wildcards like %).

Aggregations:

• COUNT: Count the number of rows.

• SUM: Calculate the sum of values in a column.

• AVG: Calculate the average of values in a column.

• MAX: Find the highest value in a column.

• MIN: Find the lowest value in a column.

Working with Multiple Tables:

• JOIN: Combine data from multiple tables based on relationships.

• INNER JOIN: Include rows where the join condition matches in both tables.

• LEFT JOIN: Include all rows from the left table, even if no match is found in the right table.

• RIGHT JOIN: Include all rows from the right table, even if no match is found in the left table.

Data Manipulation:

• INSERT: Add new rows to a table.

• UPDATE: Change existing data in a table.

• DELETE: Remove rows from a table.

Other Useful Clauses:

• DISTINCT: Remove duplicate rows from the result.

• HAVING: Filter grouped data based on aggregate functions.

• GROUP BY: Group data by one or more columns.

• AS: Create aliases for columns or tables.

Data Selection:

• Retrieve all columns from the "customers" table:

  • SELECT * FROM customers;

• Retrieve only the "name" and "email" columns from the "customers" table:

  • SELECT name, email FROM customers;

• Retrieve customers with a salary greater than 50,000:

  • SELECT * FROM employees WHERE salary > 50000;

• Retrieve products with a price over 100 in the "Electronics" category:

  • SELECT * FROM products WHERE price > 100 AND category = 'Electronics';

• Retrieve orders that are either "Shipped" or "Delivered":

  • SELECT * FROM orders WHERE status = 'Shipped' OR status = 'Delivered';

• Sort products by price in descending order:

  • SELECT * FROM products ORDER BY price DESC;

• Retrieve the first 10 customers:

  • SELECT * FROM customers LIMIT 10;

Data Manipulation:

• Insert a new customer:

  • INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]');

• Update salaries for employees in the "Sales" department:

  • UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

• Delete discontinued products:

  • DELETE FROM products WHERE discontinued = 1;

Data Definition:

• Create a new "products" table:

  • CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT, price REAL);

• Drop the "customers" table:

  • DROP TABLE customers;

• Add a "hire_date" column to the "employees" table:

  • ALTER TABLE employees ADD COLUMN hire_date DATE;

Joining Tables:

• Retrieve customer names and order dates by joining the "customers" and "orders" tables:

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

Aggregation:

• Count the total number of products:

  • SELECT COUNT(*) FROM products;

• Calculate the total order value:

  • SELECT SUM(price) FROM orders;

• Find the average salary:

  • SELECT AVG(salary) FROM employees;

• Retrieve the most recent order date:

  • SELECT MAX(order_date) FROM orders;

• Find the lowest product price:

  • SELECT MIN(price) FROM products;