Partial preview of the text
Download SQL shortcuts it's very useful and more Cheat Sheet Computer Science in PDF only on Docsity!
SQL Cheat Sheet @—~ SQL Commands, Functions & Clauses -- Basic SQL commands: SELECT - retrieves data from a database FROM - specifies which tables to retrieve data from WHERE - specifies which rows to retrieve based on certain conditions GROUP BY - groups rows that have the same values in the specified columns HAVING - filters groups based on a specified condition ORDER BY - sorts the retrieved rows in a specified order Aggregate functions: AVG() - returns the average value of a set of values COUNT() - returns the number of rows in a table or the number of non-null values in a column FIRST() - returns the first value in a set of values LAST() - returns the last value in a set of values MAX() - returns the maximum value in a set of values MIN() - returns the minimum value in a set of values SUM() - returns the sum of a set of values -- String functions: CONCAT() - concatenates two or more strings together INSTR() - returns the position of a substring within a string LENGTH() - returns the length of a string LOWER() - converts a string to lowercase LTRIM() - removes leading spaces from a string REPLACE() - replaces all occurrences of a specified string with another string RTRIM() - removes trailing spaces from a string SUBSTR() - returns a portion of a string TRIM() - removes leading and trailing spaces from a string UPPER() - converts a string to uppercase -- Date functions: CURDATE() - returns the current date CURTIME() - returns the current time DATE() - extracts the date portion from a date/time value DATEADD() - adds a specified time interval to a date DATEDIFF() - returns the difference between two dates DATEPART() - extracts a specified part of a date/time value DAY() - returns the day of the month for a date value MONTH() - returns the month for a date value NOW() - returns the current date and time YEAR() - returns the year for a date value -- Other functions: COALESCE() - returns the first non-null value in a list of values IFNULL() - returns a specified value if a value is null NULLIF() - returns null if two values are equal -- Joins: INNER JOIN - returns rows that have matching values in both tables OUTER JOIN - returns rows that have matching values in either of the two tables LEFT JOIN - returns all rows from the left table and any matching rows from the right table RIGHT JOIN - returns all rows from the right table and any matching rows from the left table FULL JOIN - returns all rows from both tables, whether or not there are matching values CROSS JOIN - returns all rows from both tables, with each row from the first table being paired with each row from the second table -- Set operations: UNION - combines the results of two or more SELECT statements, eliminating duplicates UNION ALL - combines the results of two or more SELECT statements, including duplicates INTERSECT - returns rows that are present in the results of two or more SELECT statements EXCEPT - returns rows that are present in the first SELECT statement but not in the results of any subsequent SELECT statements -- Subqueries: SELECT - retrieves data from a database within another SELECT statement FROM - specifies which tables to retrieve data from within the subquery WHERE - specifies which rows to retrieve based on certain conditions within the subquery GROUP BY - groups rows that have the same values in the specified columns within the subquery HAVING - filters groups based on a specified condition within the subquery -- Data manipulation commands: INSERT INTO - adds a new row to a table UPDATE - modifies existing data in a table DELETE - removes existing rows from a table TRUNCATE TABLE - removes all rows from a table -- Data definition commands: CREATE TABLE - creates a new table ALTER TABLE - modifies the structure of an existing table DROP TABLE - deletes a table TRUNCATE TABLE - removes all rows from a table CREATE INDEX - creates an index on a table to improve search performance DROP INDEX - deletes an index from a table By Steve Nouri -- NATURAL JOIN: -- INNER JOIN: SELECT * FROM table1 NATURAL JOIN table2; SELECT * FROM table1 INNER JOIN table2 ON table1.col1 = table2.col2 Outer Join . a . This type of join automatically matches rows from both tables based on their common columns. The common columns must have the same name and data type in both tables. -- LEFT JOIN: SELECT * FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2 Outer Join -- OUTER APPLY: -- RIGHT JOIN: SELECT t1.*, t2.* SELECT * FROM tabled RIGHT JOIN table2 ON tablei.col1 = table2.col2 FROM tabled t1 OUTER APPLY table valued function(t1.col1) t2; ~~ FUEL OUTER JOIN: This type of join is similar to CROSS APPLY, but it returns all rows from the left table, SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.coli = . . a even if there are no matching rows in the table2.col2 — right table. Right Outer Join with Exclusion a SELECT * FROM tablet SELECT * FROM table1 CROSS JOIN table2 CROSS JOIN table3; RIGHT JOIN table2 ON tablei.coli = table2.col2 WHERE table1.col1 IS NULL; Left Outer Join with Exclusion This type of join allows you to apply a table- SELECT * valued function to each row in a table and FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2 WHERE table2.col1 IS NULL; join the results to the input rows. Full Outer Join with Exclusion -- SELF JOIN: SELECT * FROM tablet SELECT t1.col1, t2.col2 FULL OUTER JOIN table2 ON table1.coli = table2.col2 FROM table1 t1 INNER JOIN table1 t2 ON t1.col3 = t2.col4; WHERE table1.col1 IS NULL OR table2.col1 IS NULL; Anti Semi Join Returns all rows from the left table that do not have a match in the right table. Similar to a LEFT JOIN, but it only returns the rows from the left SELECT * FROM tablea WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.col1 - table2.col2); table that do not have a match in the right table, Semi Join ( Less duplication than Inner Join) Returns only the rows from the left table that have a match in the right table. Similar to an SELECT * INNER JOIN, but it only returns the rows from FROM tablet the left table that have a match in the right WHERE EXISTS (SELECT 1 FROM table2 WHERE tablei.col1 = table2.col2); table, rather than returning all rows from both. Two Inner Joins This query will return rows from all three tables that have matching values in the joined columns. It will first join table1 and table2 based on the values in the col1 and col2 columns SELECT * FROM table1 INNER JOIN table2 ON table1.col1 = table2.col2 LEFT JOIN table3 ON table2.col3 = table3.col4; Two Left Outer Joins It returns all rows from the leftmost table, as well as any matching rows from the other two tables. Rows from the other two tables that do not have a match in the leftmost table are NULL. SELECT * FROM tablet LEFT JOIN table2 ON table1.coli = table2.col2 LEFT JOIN table3 ON table2.col3 = table3.col4; Be 1-Recursive queries: These are queries that can reference themselves in order to perform a certain action, such as querying hierarchical data. -- Recursive queries: WITH RECURSIVE cte_name AS ( SELECT ... UNION [ALL] SELECT ... FROM cte_name WHERE ... ) SELECT cc. FROM cte_name; 2-Window functions: These are functions that perform a calculation over a set of rows, similar to an aggregate function, but return a value for each row in the result set. -- Window functions: SELECT col1, col2, function_name(col3) OVER (PARTITION BY coll ORDER BY col2) as col4 FROM table_name; 3-Common table expressions (CTEs): These are named temporary result sets that can be used within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. They are often used to simplify complex queries by breaking them up into smaller, more manageable pieces. -- Common table expressions (CTEs): WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name; 4-Pivot tables: These are tables that allow you to transform data from rows to columns, or vice versa, in order to generate more meaningful insights from your data. -- Pivot tables: SELECT * FROM ( SELECT coli, col2, col3 FROM table_name ) PIVOT ( SUM(co13) FOR col2 IN (vali, val2, val3) 5-Analytic functions: These are functions that perform a calculation over a group of rows and return a single value for each row in the result set. -- Analytic functions: SELECT coli, col2, function_name(col3) OVER (PARTITION BY coli ORDER BY col2) as col4 FROM table_name; 6-Triggers: These are pieces of code that are automatically executed in response to certain events, such as inserting, updating, or deleting data ina table. -- Triggers: CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN -- trigger code here END; 7--Stored procedures: These are pre-compiled SQL statements that can be stored in the database and executed repeatedly with different parameters. -- Stored procedures: CREATE PROCEDURE procedure_name (IN param1 datatype, IN param2 datatype, ...) BEGIN -- procedure code here END; CALL procedure_name(parami_value, param2_value, ...); 8-Indexes: These are data structures that are used to improve the performance of certain types of queries by allowing the database to quickly locate the desired data. -- Indexes: CREATE INDEX index_name ON table_name (col1, col2, ...); DROP INDEX index_name; 9-Cursor-based processing: This is a method of processing data in which a cursor is used to retrieve a small batch of rows from a result set, process the rows, and then retrieve the next batch of rows until all rows have been processed. This can be useful when working with large result sets or when the processing needs to be done row by row. -- Cursor-based processing: DECLARE cursor_name CURSOR FOR SELECT coli, col2, ... FROM table_name WHERE ...35 OPEN cursor_name; FETCH NEXT FROM cursor_name INTO variablei, variable2, ...; WHILE @aFETCH_STATUS = © BEGIN -- processing code here FETCH NEXT FROM cursor_name INTO variable1, variable2, ...; END; CLOSE cursor_name; DEALLOCATE cursor_name; 
