SQL One Pagers

INTRO

These SQL one pagers include every query for the most common SQL queries, all in one page.

The most common DQL (Data Query Language) queries are in one page with a separate 'test yourself' file.

The most common Window functions for more advanced analysis are included in a separate file, with another 'test yourself' file.

CONTENTS

sql-dql-queries.ipynb - 11 sections of the most common SQL DQL query examples in one page with clear query aims and notes.

sql-dql-queries-syntax-only.ipynb - Quick syntax guide - only queries

sql-dql-queries-test-yourself.ipynb- Simple query tests. A great way to test your knowledge.

sql-window-functions - 10 most common Window functions for data analysis with SQL. Clear query aims and notes added.

sql-window-functions-test-yourself - Simple query tests. A great way to test your knowledge.

DQL QUERIES SECTIONS

1. SQL Query Order
2. Sorting - Order by
3. Limiting - Offset, Fetch, Select Top
4. Filtering - Distinct, And Or In, Between, Like, Column Aliases
5. Joins - Inner Join, Left Join, Right Join, Full Outer Join, Cartesian Join, Self Join
6. Grouping - Group By, Having, Grouping Sets, Cube, Rollup
7. SubQuery - Correlated subquery, Exists, Any, All
8. Sets - Union, Union All, Intersect, Except
9. Common Table Expressions - CTEs, Recursive CTEs
10. Pivot
11. Expressions - Case, Coalesce, NullIf

WINDOW FUNCTIONS

1. Cumulative distribution
2. Dense Rank
3. First Value
4. Lag
5. Last Value
6. Lead
7. NTILE
8. Percent Rank
9. Rank
10. Row Number

GETTING STARTED

• If you already know SQL, maybe test your knowledge with the 'test-yourself' files.
• If you are new to SQL, see the links in the related section to setup and download a practice database.
• The queries are here are used (mainly) with the BikeStore database from the sqlservertutorial in the related section.

TOOLS

• SQL Server
• BikeStore Database

RELATED

• SQL Server https://docs.microsoft.com/en-us/sql/sql-server/tutorials-for-sql-server-2016?view=sql-server-ver15 https://www.sqlservertutorial.net

• The topics covered in the DQL (Data Query Language) files are used across all database servers (although the syntax will vary.) The 'test_yourself' files could be used to also check overall knowledge with Postgres and MySQL databases.

• Postgresql https://www.postgresql.org/docs/8.0/tutorial.html https://www.postgresqltutorial.com/

• MySQL https://dev.mysql.com/doc/refman/8.0/en/tutorial.html https://www.mysqltutorial.org/