SQL Topics 1. Basic SQL Commands: o SELECT: This is the most fundamental SQL command. It's used to retrieve data from a database. You can specify which columns to return and from which table.  Example: SELECT column_name FROM table_name; o INSERT: This command is used to add new records to a table. You must provide values corresponding to the columns of the table.  Example: INSERT INTO table_name (column1, column2) VALUES (value1, value2); o UPDATE: This command is used to modify the existing records in a table. It's commonly used with a WHERE clause to target specific rows.  Example: UPDATE table_name SET column1 = value1 WHERE condition; o DELETE: This command is used to delete one or more records from a table. As with UPDATE, you should use WHERE to specify which records to delete.  Example: DELETE FROM table_name WHERE condition; 2. Data Filtering and Sorting: o WHERE Clause: This is used to filter records based on specific conditions. It's essential for limiting the rows returned by your query.  Example: SELECT * FROM table_name WHERE column_name = 'value'; o ORDER BY Clause: This is used to sort the results of your query in ascending or descending order.  Example: SELECT * FROM table_name ORDER BY column_name DESC; o LIMIT/OFFSET: LIMIT restricts the number of rows returned by the query, and OFFSET skips a specified number of rows before starting to return results.  Example: SELECT * FROM table_name LIMIT 10 OFFSET 20; 3. Aggregate Functions: o COUNT(): This function returns the number of rows that match a specified condition.  Example: SELECT COUNT(*) FROM table_name; o SUM(): This function calculates the total of a numeric column.  Example: SELECT SUM(salary) FROM employees; o AVG(): This function calculates the average value of a numeric column.  Example: SELECT AVG(salary) FROM employees; o MIN() and MAX(): These functions return the smallest and largest values in a column, respectively.  Example: SELECT MIN(salary), MAX(salary) FROM employees; o GROUP BY: This groups rows sharing a property so that aggregate functions can be applied to each group.  Example: SELECT department, AVG(salary) FROM employees GROUP BY department; o HAVING Clause: This is similar to WHERE, but it’s used to filter groups created by GROUP BY.  Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000; 4. Joins: o INNER JOIN: This returns only the rows that have matching values in both tables.  Example: SELECT employees.name, departments.name FROM employees INNER JOIN departments ON employees.department_id = departments.id; o LEFT (OUTER) JOIN: This returns all rows from the left table and matched rows from the right table. If no match is found, NULL values are returned for the right table.  Example: SELECT employees.name, departments.name FROM employees LEFT JOIN departments ON employees.department_id = departments.id; o RIGHT (OUTER) JOIN: This returns all rows from the right table and matched rows from the left table. If no match is found, NULL values are returned for the left table.  Example: SELECT employees.name, departments.name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id; o FULL (OUTER) JOIN: This returns all rows when there is a match in either table. If there’s no match, NULL values will appear.  Example: SELECT employees.name, departments.name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id; o SELF JOIN: This is a join where a table is joined with itself, typically using an alias.  Example: SELECT a.name, b.name FROM employees a, employees b WHERE a.manager_id = b.id; 5. Subqueries: o Single-row Subqueries: A subquery that returns only one value.  Example: SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); o Multiple-row Subqueries: A subquery that returns multiple rows. You can use it with IN or ANY to match a list of values.  Example: SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York'); o Correlated Subqueries: These refer to columns from the outer query. The inner query is executed once for each row processed by the outer query.  Example: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); 6. Set Operations: o UNION: Combines results from two or more queries, removing duplicates.  Example: SELECT name FROM employees WHERE department = 'HR' UNION SELECT name FROM employees WHERE department = 'IT'; o UNION ALL: Combines results without removing duplicates.  Example: SELECT name FROM employees WHERE department = 'HR' UNION ALL SELECT name FROM employees WHERE department = 'IT'; o INTERSECT: Returns only the rows that appear in both result sets.  Example: SELECT name FROM employees WHERE department = 'HR' INTERSECT SELECT name FROM employees WHERE department = 'IT'; o EXCEPT: Returns rows from the first query that are not in the second query.  Example: SELECT name FROM employees WHERE department = 'HR' EXCEPT SELECT name FROM employees WHERE department = 'IT'; 7. Indexes and Constraints: o PRIMARY KEY: A unique identifier for each record in a table. It cannot be null and must be unique across rows. o FOREIGN KEY: Establishes a relationship between two tables. It ensures referential integrity by linking to the primary key of another table. o UNIQUE: Ensures all values in a column are unique. o CHECK: Ensures that all values in a column satisfy a specified condition. o INDEX: Speeds up the retrieval of rows from a table. Indexes are particularly useful when dealing with large datasets. 8. Stored Procedures and Functions: o Stored Procedures: These are precompiled collections of SQL queries that can be executed on demand. They encapsulate business logic. o User-Defined Functions (UDFs): Functions created to perform specific operations. These can be used in queries like built-in functions. o Triggers: A trigger is a set of SQL commands that automatically execute when certain events occur, such as after inserting, updating, or deleting data. 9. Transactions and Concurrency: o BEGIN TRANSACTION, COMMIT, ROLLBACK: Transactions allow you to bundle a series of SQL operations into a single unit. If one part of the transaction fails, the entire operation can be rolled back to ensure data integrity. o ACID Properties: These are the key properties that guarantee reliable transaction processing: Atomicity, Consistency, Isolation, and Durability. o Isolation Levels: These control the visibility of changes made by one transaction to other concurrent transactions (e.g., Read Uncommitted, Read Committed, Repeatable Read, Serializable). 10. Normalization and Database Design: o Normalization: This process is used to reduce redundancy and dependency by organizing data into multiple related tables. o Normalization Forms:  1NF: Ensure that each column contains atomic values and there are no repeating groups.  2NF: Eliminate partial dependencies (no column should depend on a part of a composite primary key).  3NF: Remove transitive dependencies (non-key columns should not depend on other non-key columns).  BCNF: A stricter version of 3NF, ensuring every determinant is a candidate key. o Entity-Relationship (ER) Modeling: A method of visually representing the database structure, where entities (tables) are linked by relationships (keys). o Referential Integrity: This ensures that relationships between tables remain consistent, and foreign keys are properly matched to primary keys.