/sql-one-pagers

All common SQL data query language and Window functions in one page with query explanations and notes. A file to test your knowledge for each included.

Primary LanguageJupyter Notebook

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