SELECT Statement

The SELECT statement is used to retrieve data from one or more tables in a database.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Common SELECT Variations:

• Select All Columns: SELECT * FROM table_name;
• Distinct Values: SELECT DISTINCT column_name FROM table_name;
• Order Results: SELECT column_name FROM table_name ORDER BY column_name ASC|DESC;
• Limit Results: SELECT column_name FROM table_name LIMIT number;
• Aliases: SELECT column_name AS alias_name FROM table_name;

INSERT Statement

The INSERT statement is used to add new rows into a table.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Common INSERT Variations:

• Insert Multiple Rows: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value3, value4, ...);
• Insert Data from Another Table: INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM another_table WHERE condition;

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Common UPDATE Variations:

• Update All Rows: UPDATE table_name SET column1 = value;
• Update with Conditions: UPDATE table_name SET column1 = value WHERE condition;

DELETE Statement

The DELETE statement is used to delete rows from a table.

DELETE FROM table_name WHERE condition;

Important Note:

• Use DELETE with caution. If you omit the WHERE clause, all rows will be deleted.
• Delete All Rows: DELETE FROM table_name;

JOINS

SQL JOIN clauses are used to retrieve data from multiple tables based on relationships between them.

Types of JOINS:

• INNER JOIN: Returns records that have matching values in both tables.

  SELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.column = table2.column;

• LEFT JOIN: Returns all records from the left table and the matched records from the right table. If no match, NULL is returned.

  SELECT columns
  FROM table1
  LEFT JOIN table2
  ON table1.column = table2.column;

• RIGHT JOIN: Returns all records from the right table and the matched records from the left table. If no match, NULL is returned.
• FULL OUTER JOIN: Returns all records when there is a match in either table.

Subqueries

A subquery is a query nested inside another query, used to retrieve data to be used in the main query.

Example of Subquery:

SELECT column1
FROM table1
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);

Common Subquery Variations:

• In SELECT Clause: SELECT column1, (SELECT column2 FROM table2 WHERE condition) AS alias_name FROM table1;
• In WHERE Clause: SELECT column1 FROM table1 WHERE column1 IN (SELECT column1 FROM table2);

SQL Functions

SQL provides built-in functions for performing operations on data. Here are some of the most commonly used functions:

Aggregate Functions:

• COUNT(): Counts the number of rows. SELECT COUNT(column_name) FROM table_name;
• SUM(): Returns the sum of a numeric column. SELECT SUM(column_name) FROM table_name;
• AVG(): Returns the average value of a numeric column. SELECT AVG(column_name) FROM table_name;
• MIN() / MAX(): Returns the minimum or maximum value. SELECT MIN(column_name) FROM table_name;

String Functions:

• CONCAT(): Concatenates two or more strings. SELECT CONCAT(column1, column2) FROM table_name;
• SUBSTRING(): Extracts a substring from a string. SELECT SUBSTRING(column_name, start_position, length) FROM table_name;
• UPPER() / LOWER(): Converts text to uppercase or lowercase. SELECT UPPER(column_name) FROM table_name;
• TRIM(): Removes leading and trailing spaces. SELECT TRIM(column_name) FROM table_name;

Indexes

Indexes are used to speed up query performance by allowing the database to find rows more quickly.

CREATE INDEX index_name
ON table_name (column1, column2);

Common Index Types:

• Unique Index: Ensures that all values in a column are unique. CREATE UNIQUE INDEX index_name ON table_name (column_name);
• Composite Index: An index on multiple columns. CREATE INDEX index_name ON table_name (column1, column2);
• Drop Index: Removes an index from a table. DROP INDEX index_name;

SQL Best Practices

Follow these best practices to ensure optimal performance and maintainability of your SQL queries:

• Use Indexes Wisely: Indexes improve query performance but can slow down inserts, updates, and deletes. Use them where appropriate.
• Avoid SELECT *: Select only the columns you need to improve performance and readability.
• Normalize Your Data: Ensure your database is normalized to reduce redundancy and maintain data integrity.
• Use Aliases for Readability: Use aliases to simplify long table or column names.
• Optimize Joins: Ensure that joined columns are indexed to improve performance.