words per minute
9
AlonsoSnchez
00:00
Speed
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.