9124122, 459 PM SOL Cheat Sheet. Starter guide for standard SAL sya... | by Jason Lee | Towards Data Science To make Medium work, we log Privacy Po @ Publish... ... we You have 2 free member-only stories left this month, Sign up for Medium and get an extra one 3 Jason Lee (Follow) Apr 19,2020 . Sminread . + » Listen sve v Of & SQL Cheat Sheet Starter guide for standard SQL syntax used in PostgreSQL PostgreSQL cheatsheet by Jason Lee Photo by panumas nikhomkhai from Pexels SQL is the most important coding language to learn for data analysis. Some might i Ips:Mowardsdatascionce.comsq-cheat-sheot-776!8e3168!a 1109124122, 459 PM SOL Cheat Sheet. Starter guide for standard SAL sya... | by Jason Lee | Towards Data Science sow) Amazon, wh lata teams use SQL to query data and perform analysis. Like every language, you need to keep practicing to understand and grasp keep concepts. In my opinion, SQL is or guages to use once you SK Qe understand the basic structure of thy vw. «1 usw wticle, I share the necessary steps to getting started with SQL querying. Standard SQL Structure This is Part 1 to a series of PostgreSQL cheat sheets and will cover s WHERE, GROUP BY, HavING, oxpsR By and uiurr. The basic structure of a query pulling results from a single table is as follows. SELECT COLUMN_NAME (S) FROM TABLE_NAME, E CONDITION SROUP BY COLUMN_NAME (S) HAVING AGGREGATE_CONDITION ORDER BY COLUMN_NAME LIM N What is SQL? SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database or retrieve data from a database. a hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a 2109124122, 459 PM SOL Cheat Sheet. Starter guide for standard SQL sya... | by Jason Lee | Towards Data Science ow) © Rows — ais suvwii as recurs: + Columns — also known as fields, have a descriptive name and specific data type. What is PostgreSQL? PostgreSQL is a general-purpose and relational database management system, the most advanced open-source database system. Other common database management systems are MySQL, Oracle, IBM Db2, and MS. Access. Let's begin! SELECT The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. Specific columns FROM TABLE_NAM All columns Using the - you can query every column in your table SRLROT + hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a a09124122, 459 PM SOL Cheat Sheet. Starter guide for standard SQL sya... | by Jason Lee | Towards Data Science ou sess) Finding all t DISTINCT (COLUMN NAME) FROM TABLE NAME ‘COUNT all rows If you want to know alll the values in the entire table use couvs(*) you will get a single number. SELEC’ FROM TABLE_NAME ‘COUNT DISTINCT values If you want the number of distinct values in a column using couvr with orsrmver and you will get a number representing the total unique values of a column SELECT COUNT (DISTINCT COLUMN_NAME) FROM TABLE_NAME, WHERE Using the wasar the clause, you can create conditions to filter out values you want or tl hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a ano9124122, 459 PM SOL Cheat Sheet. Starter guide for standard SAL sya... | by Jason Lee | Towards Data Science @0 To make Medium work our Privacy Policy, including g user data. By using N kie policy. TABLE_NAME, CONDITION Conditions There are a variety of conditions that can be used in SQL. Below are some examples of a table that consists of students’ grades in school. You only need to specify wese once, for the sake of the example, I have included wisks: in each step. FIRSTNAME = "BOB" = exact match FIRSTNAME I= "BOBT -- everything excluding BOB NO? FIRSTNAME ="BOB' =~ everything excluding BOB IN ('BOB', 'JASON') -- either condition is met NOT IN ('BOB', 'JASON') -~ excludes both values = 'BOB' AND LASTNAME = 'SMITH' -- both conditions = 'BOB' OR FIRSTNAME = 'JASON' -- either condition GRADES eater than 90 GRADES less than 90 GRADES greater than or equal to 90 GRADES less than or equal to 90 WHERE SUBJECT IS NULL -- returns values with missing values WHERE SUBJECT NOT NUL returns values with no missing values Conditions — Wildcards uke operator is used ina wuese clause to search for a specified pattern in a column. When you pass the i1x= operator in the ‘+ upper and lower case matters. There are two wildcards often used in conjunction with the u:x= operator: tl hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a 5109124122, 459 PM @0 To make Medium work WHERE FIhoswewin WHERE FIRSTNAME WHERE PIRSTNAME position WHERE PIRSTNAME position WHERE FIRSTNAME have at least 3 SOL Cheat Sheet. Starter guide for standard SQL sya... | by Jason Lee | Towards Data Science er data, By using Medium, you tated our Privacy Policy, including cookie policy. Bins pe "= Linus Values SLaLLuNy UppeLase BD LIKE ‘Sb’ -- finds values ending lowercase b LIKE ‘Sané’ -- find values that have “an” in any LIKE ‘_n%’ -- find values that have “n” in the second LIKE ‘B_%’ -- find values that start with “B” and characters in length LIKE ‘Btb’ -- find values that start with “B” and end WHERE FIRSTNAME with “b” WHERE FIRSTNAME .FY OR OLS WHERE FIRSTNAME ‘cr, OR YDt WHERE PIRSTNAME that start with WHERE FIRSTNAME starting with LIKE ‘[BFL]’ -- find all values that start with LIKE ‘(B-D]’ -- find all values that start with ‘BY, LIKE *[!BFL]%/ -- find everything exlcusing values ‘Br, SPOOR CL NOT LIKE *[BFL]$" -- same as above. excludes values 1 ET, OR SLE WHERE GRADES BETWEEN 80 and 90 -- find grades between 80 and 90 GROUP BY The rove sy function helps calculate summary values by the chosen column, Itis often used with aggregate functions (cous7, suv, av, vax, rv). SELECT “a tl hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a 6109124122, 459 PM SOL Cheat Sheet. Starter guide for standard SQL sya... | by Jason Lee | Towards Data Science e sess) ‘The query above will group each subject and calculate the average grades. SELECT SUBJECT, FROM GROUP BY ‘The above query will calculate the number (count) of students in each subject. HAVING The savine clause is similar to wees” but is catered for filtering aggregate functions. The savine function comes after the croup sy, in comparison the wares comes before the croup ay. If we wanted to find which subject had an average grade of 90 or more, we could use the following. SELECT SUBJECT, AVG (GRADES) FROM UDENT GROUP BY SUBJECT HAVING AVG (GRADES) >= 90 tt Q hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a m09124122, 459 PM SOL Cheat Sheet. Starter guide for standard SAL sya... | by Jason Lee | Towards Data Science ORDER BY Using the orper ey function, you can specity now you want your values sorted, Continuing with the Student tables from earlier. SELECT. FROM TUDENTS ORDER BY GRADES DESC When using the onssn sy by default, the sort will be in ascending order. If you want to descend, you need to specify orsc after the column name. LIMIT In Postgres, we can use the u1yz7 function to control how many rows are outputted in the query. For example, if we wanted to find the top 3 students with the highest grades. SELECT FROM STUDENTS ORDER BY GRADES DESC LIMIT 3 Since we use ompR BY © we have the order of students with the highest grades on top - now limiting it to 3 values, we see the top 3. hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a ano9124122, 459 PM ‘SQL Cheat Sheet. Starter guide for standard SAL. syntax... | by Jason Lee | Towards Data Science e sess) data froma sharing more SQL cheat sheets that will expand advanced syntax. If you want to learn specific techniques, check out my other tutorials. * Date/Time Function in SQL « Intro to Window Function: © Howto use CTEs in SQL * Creating Tables in SQL Sign up for The Variable By Towards Data Science Every Thursday, the Variable delivers the very best of Towards Data Science: from hands-on tutorials and cutting-edge research to original features you don't want to miss. Take a look, By signing up, you will ereate a Medium account ityou dont already have one. Review ‘our Privacy Policy for more information about our privacy practices. — Getinenewsietter) S Get this newsletter ) hitpsstowardsdatascionce.com/sqhcheat-shoot-776/803188!a9124122, 459 PM ‘SQL Cheat Sheet. Starter guide for standard SAL. syntax... | by Jason Lee | Towards Data Science ow) hitpsitowarcsdatascience.com/sql-cheat-a son0