SQL Cheat Sheet @— SQL Commands, Functions & Clauses retrieves data (ron 0 database ORDER BY = sorts the retedeved rows in specified order vce) = returns the average value of a set of values CUNTC) = returns the mimber of roms Sn a table or the imbue of nom-nl values FIRST() = returns the First value Sn set of valves STO) ~ returns the Last value in set of values x() = cote the maximum valve Sn a sot of aloes IN ~ seta the minim value Sn a sot of alles SINC) = returns the sim of @ set of values CONCAT() ~ concatenates tho or more strings together INSTR() ~ returns the position of a substring within a string LENGTH() = ceturns the Iength of a string LoweRG) LIRING) ~ removes leading spaces fron a string converts a string to lowercase REPLACE() - replaces oll occurrences of @ specified string with ARIRIM() ~ removes ‘ailing spaces from a string SUBSTR() ~ retuens @ portion of @ string TRING) ~ removes leading and trailing spaces fron a string UPPERG) ~ converts a string to uppercase (CURDATE() ~ returns the curee CURTINE() ~ returns the current tine DATE() - extracts the date portion from a date/tine value xTe900(), DATEDIFF() DOATEPART() - extracts a specified part of @ Jate/tine value DAY() = returns the day of the month for a date value POITHQ) = returns the month for a date value ou{) ~ returns the current date and time ‘YEARG) ~ returns the year for @ date value returns the difference beteen two dates COALESCEQ) - returns the First non-null value in a List of values TrMLLO NULLIF() ~ returns ull 4€ two values are equal returns @ specified value if @ value is null INWER JOIN ~ returns rows that have matching values in both tables OUTER 201N = returns rows that have matching values in either of the tio tables LEFT 0IN ~ returns all rows from the left table and any matching rows from the right table AIGAT 201M - returns oll rows from the right table and any matching rows from the left t: ble FULL 30IN - returns all rows from both tables, whether or not there fre matching volues ross 201" the first table being paired with each row from the second table ‘returns all rows from both tables, with each row from UNION = combines the results of two or more SELECT statements, climinating duplicates UWTON ALL = combines the results of two or more SELECT statements, including duplicates INTERSECT ~ returns rows that ore present in the results of two or more SELECT statements [EXCEPT ~ returns nous that are present in the First SELECT statement but not in the results of ony subsequent SELECT statements SELECT - retrieves data fron a database within another SELECT statenent FRO - specifies which tables to retrieve data fron within the subquery WIERE ~ specifies which rows to retrieve based on certain conditions ‘within the subquery (snoue BY - groups rows that have the same volucs in the specified coluans within the subquery HAVING - Filters groups based on o specified condition within the subquery INSERT INTO - adds @ new row to @ table roar DELETE ~ removes existing rows from a table TRUNCATE TABLE - removes all rous from @ table modifies existing data in a table CREATE TABLE ~ creates @ new table ALTER TABLE DROP TABLE TRUNCATE TABLE - removes all rows from a table rodifies the structure of an existing table deletes a table CREATE INDEX - creates an index on a table to improve search performance Poncx By Steve Nouri deletes an index fron a tableSELECT * FROM tabled ZI9E% 200% table? Ol tablet.cold = table2.col2 SELECT * FROM tabled waTURAL 3010 table2: Outer Jom SELECT * FROM tabled LEFT 201" table? OW tablet.colt = table2.col2 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. Outer Jon SELECT * FROH table RIGHT 201% table2 Ol tablet.colt = table2.col2 rms taba tt Tes ABpLY table vlund fncttonttctt) 12; SELECT * FROW fablen FULL OUTER 20IN table? OW tablen.colL = This type of join is similar to CROSS APPLY, but it returns all rows from the left table, even if there are no matching rows in the right table. Right Outer Join with Exclusion S96 abled cold 15 MLS ‘Join with Exclusion LEFT 2010 tablea ou tablen.cold = table2.col2 eRe taben.cold. 158 Full Out Fuh OUTER 2008 tabled table. cold = tablen.cok2 Join This type of join allows you to apply a table- valued function to each row in a table and join the results to the input rows. Anti Semi Join Foon tabler HERE NOT EXISTS (SELECT 1 FROM table? WERE tabled.colt = table2.col2) Semi Join ( Less duplication than Inner Join) sevect * om tables [WERE OAISTS (SELECT 2 FROM Kable? WHERE tableL-colt = table2.col2); 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 table that do not have a match in the right table, Returns only the rows from the left table that have a match in the right table. Similar to an INNER JOIN, but it only returns the rows from the left table that have a match in the right table, rather than returning all rows from both. Two Inner Joins, sevect * oH tablet rT0.eR 01H table? ON tablen.colt = table2.col? LEFT 201N tablea 0 table2.col3 = table3.col4; Two Left Outer Joins SELECT * FROM tablet LEFT 20TH table? OW table1.col1 = table2.col2 LEFT JOIN table3 oN table?.col3 = table3.col4; This query will return rows from all three tables. that have matching values in the joined columns. Itwill first join tablet and table2 based on the values in the colt and col2 columns 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.~- Sample data for the “enployees™ table: 44 | name | department | salary | hired on a | Alice | #8 | 55000 2 | Bob | Marketing | 65000 3 | charite| 11 | 73000 4 [pave | sales | 0000 | 2670-04-01 Slee | me | 60000 | 2620-05-01 Sample data for the “sales” table: aia | tage zia | Gadger 312 | tage a2 | Gadget S12 ' eis ' 714 | widget aia | Gadget | 2021-01-01 | 000 oa | thinganajigl 2021-02-01 | 9800 wis [widget | 2021 | 20000 Sample data for the “products” table: 46 | product | price 2 | waget | 200 2 | odget | 200 3. | Thingasajigl 200 1- Retrieve all rows from the 'employees' table where the salary is greater than 155000. SELECT * FROM employees HERE salary > 65000; 2-Retrieve the name and department of all rows from the ‘employees’ table, sorted by salary in descending order. SELECT name, department Fo! employees OROER BY salary ESCs '3-Retrieve the product and total sales for each product in the ‘sales table, sorted by total sales in descending order. SELECT piproduct, SUM(s.sale amount) as total_sales io" sales 5 {TWMER 01% products p Oli S.product = p-product GROUP SY p.product ORDER WY otal_sales ESC: 4-Retrieve the name and total sales forall employees inthe Marketing {department from the ‘employees’ and 'sles' tables. ‘SELECT e.name, Sun(s.sale_amount) as total_sales 0% employees e {WER DOIN sales 5 ON e.id = s.employee_id WiERE e.department = “Marketing” GROUP BY e.name; S5-Retrieve the name, product, and sale date forall sales inthe sales table where| the sale amount is greater than 2500. SELECT e.name, p.product, 5-sale_date £0! employees © IER J01N Sales 5 0¥ e.fd = s.employee 4d Iivich 20IN products p On s.product = product Wine 5.sale_oaount > 25 By Steve Nouri ‘Retrieve the name, product, and sale amount for all sales in the sales table that ‘occurred between June 1, 2020 and December 31, 2020. SELECT e.mame, p.product, s.sale amount 0% employees @ rHvict 2008 sales 5 ON €.1d = s.employee_{d NER 201M products p Ol product = Peproduct MIERE s.sale_date EETWEEN °2070-06-01" AND °2020-12-31°3 7-Retrieve the name, product, and sale amount for the sale with the highest sale ‘amount inthe sales table, along with the corresponding employee name from the ‘employees table. SELECT e.mame, p.product, 5.sale_anount 0% employees «01% 200% sales 5 ON eid = s.employee_id IWvic% 01M products p Olt s.product = Peproduct WHERE s.sale_amount ri (SELECT MAK(sale_amount) FRO! sales); ‘-Retrieve the name and manager ofall employees in the employees table, ‘showing the name of the manager from the employees table as well SELECT el.nane 25 manager, e2.name 2: employee F70! employees et {IWWER 2010 employees €2 0h et.id = e2.monager_{d; ‘Retrieve the name and number of employees forall managers in the employees table who have atleast one employee. SELECT e.name, (SELECT COUNT(*) FROM employees MERE manager_{d = e-id) 25 mum employees Ao! employees € Wich: mum employees > ©; 10-Retrieve the name ofall employees in the employees table who do not have a ‘manager. SELECT el.name, e2.name as manager (ch employees et LEFT 20181 ‘employees €2 li e1.manager_id = e2-4d WHERE e2.name TS WILLS 1 -Retrieve the name and total sales forall employees in the employees table who have made more than 5000 in sales in the past year (from the current date). SELECT e.name, SUM(s.sale_amount) ss total_sales £0" employees © NWR 2000 sales 5 ON e-4d = s.employee id WERE s.sale date BETWEEN WHERE ‘This order can be masified by the use of subqueries oF GROUP BY | common table expressions (CTES), In these cases, the subquey or CTE is executed fst and the results ae used in HAVING | the auter query SELECT ORDER BY Here is an example query to show the order of execution: ‘SELECT e.name, SiM(s.salle_amount) 2 total_sales FAO employees © WER 201% sales = OW e.Sd = s.employee id ERE e.department = ‘00 5.sale_date BETWEEN "2020-01-01" 10 sales ‘oRouP 8Y esmame HAVING SUm(S.sale_emount) > 5008 ‘ORDER BY total_sales DESC; FROM and WHERE clauses are executed fst to select the relevant rows from the ‘emplayees and sales tables. These rows are then grouped by employee name and aggregated using the SUM function in the GROUP BY clause. The HAVING clause filters out any groups that do not meet the specified condition. Finally, the SELECT clause selects the name and total sales for each group, and the ORDER BY clause sorts the results by total sales in descending order.@— Some Advanced Topics in SQL 1-Recursive queries: These are queries that can reference themselves in order to perform a certain action, such as querying hierarchical data WITH RECURSIVE cte_name AS ( SELECT se warow (AL) ) 2.Window functions: These are functions that perform a calculation over a set of rows, siilar to an aggregate function, but return a value for each row in the result set. SELECT colt, col2, function name(col3) OVER (PARTITION BY cold ORDER BY col2) 2s cole Ro table_nane; 3-Common table expressions (CTEs): These are named temporary result sets ‘that can be used within @ SELECT, INSERT, UPOATE, DELETE, or CREATE VIEW statement. They are often used to simplify complex queries by breaking them up into smaller, more manageable pieces, En ete_name AS ( > 4-Pivt 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 Fron ( SELECT colt, col2, col3 0 table_name > ror ( sm(eol3) ot eol2 in (vala, val2, val3) S5-Analytc functions: These are functions that perform a calculation over a 4aroup of rows and return a single value for each row inthe result set. SELECT colt, col2, Function nane(col3) OVER (PARTITION BY colL ORDER BY cold) as colt 70H table names 6-Triggers: These are pieces of code that ae automatically executed in response to certain events, such as inserting, updating or deleting data ina table. ‘CREATE TRIGGER trigger_name AFTER 1NSERT OW table name FOR EACH ROW Stored procedures: These are pre-complled SQL statements that can be stored in the database and executed repeatedly with different parameters. [CREATE PROCEDURE procedure name (10 param datatype, 1! paran2 éatatype, --.) GIN (CALL procedure nane(parant_value, paran?_value, B-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, ‘CREATE: INDEX Andex.name OW table_nawe (C011, cOl2, .. ‘080? INDEX Snex_name; 9-Cursor-based processing: This isa method of processing data in which a cursor is used to retrieve a small batch of rows from a result set, pracess the rows, and then retrieve the next batch of rows until all ows have been processed. This can be useful when working with large result sets or when the processing needs to be done row by row. FETCH MEXT FROM cursoR name INTO varfabled, vardable2, arue gercroH_ staTUS = © FETCH NEXT FRCH CURSOR name INTO varsableL, vardable2, BRT