Mgmtblog886

SQL commands Cheat Sheet by sjm - Cheatography.com Created Date: 0104Z. This cheat sheet provides helpful tips and best practices for building dedicated SQL pool (formerly SQL DW) solutions. The following graphic shows the process of designing a data warehouse with dedicated SQL pool (formerly SQL DW): Queries and operations across tables.

Download this 2-page SQL Basics Cheat Sheet in PDF or PNG format, print it out, and stick to your desk.

• May 27, 2020 - Explore jaine lum's board 'sql cheat sheet' on Pinterest. See more ideas about sql cheat sheet, sql, cheat sheets.
• This 3-page SQL Cheat Sheet provides you with the most commonly used SQL statements. Download the SQL cheat sheet, print it out, and stick to your desk.
• DBA Cheat Sheet @sqlservermama Page 2 In SQL 2012 and above SELECT servicename AS 'Service Name',serviceaccount,startuptypedesc AS 'Startup Type',statusdesc as 'Status',laststartuptime as 'Last Startup Time' FROM sys.dmserverservices.

The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. You can download this cheat sheet as follows:

You may also read the contents here:

SQL Basics Cheat Sheet

SQL

SQL, or Structured Query Language, is a language to talk to databases. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in practically all technologies that process data.

SAMPLE DATA

QUERYING SINGLE TABLE

Fetch all columns from the country table:

Fetch id and name columns from the city table:

Fetch city names sorted by the rating column in the default ASCending order:

Fetch city names sorted by the rating column in the DESCending order:

Aliases

Columns

Tables

FILTERING THE OUTPUT

COMPARISON OPERATORS

Fetch names of cities that have a rating above 3:Fetch names of cities that are neither Berlin nor Madrid:

TEXT OPERATORS

Fetch names of cities that start with a 'P' or end with an 's':Fetch names of cities that start with any letter followed by'ublin' (like Dublin in Ireland or Lublin in Poland):

OTHER OPERATORS

Fetch names of cities that have a population between 500K and 5M:Fetch names of cities that don't miss a rating value:Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

QUERYING MULTIPLE TABLES

INNER JOIN

JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables.

LEFT JOIN

LEFT JOIN returns all rows from the left table with corresponding rows from the right table. If there's no matching row, NULLs are returned as values from the second table.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table with corresponding rows from the left table. If there's no matching row, NULLs are returned as values from the left table.

FULL JOIN

FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows from both tables – if there's no matching row in the second table, NULLs are returned.

CROSS JOIN

CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available.

NATURAL JOIN

NATURAL JOIN will join tables by all columns with the same name.

NATURAL JOIN used these columns to match rows:
city.id, city.name, country.id, country.name.
NATURAL JOIN is very rarely used in practice.

AGGREGATION AND GROUPING

GROUP BYgroups together rows that have the same values in specified columns. It computes summaries (aggregates) for each unique combination of values.

AGGREGATE FUNCTIONS

• avg(expr) − average value for rows within the group
• count(expr) − count of values for rows within the group
• max(expr) − maximum value within the group
• min(expr) − minimum value within the group
• sum(expr) − sum of values within the group

EXAMPLE QUERIES

Find out the number of cities:

Find out the number of cities with non-null ratings:

Find out the number of distinctive country values:

Find out the smallest and the greatest country populations:

Find out the total population of cities in respective countries:

Find out the average rating for cities in respective countries if the average is above 3.0:

SUBQUERIES

A subquery is a query that is nested inside another query, or inside another subquery. There are different types of subqueries.

SINGLE VALUE

The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=.

This query finds cities with the same rating as Paris:

MULTIPLE VALUES

A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY.

This query finds cities in countries that have a population above 20M:

CORRELATED

A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.

This query finds cities with a population greater than the average population in the country:

This query finds countries that have at least one city:

SET OPERATIONS

Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different

UNION

UNION combines the results of two result sets and removes duplicates. UNION ALL doesn't remove duplicate rows.

This query displays German cyclists together with German skaters:

INTERSECT

INTERSECT returns only rows that appear in both result sets.

This query displays German cyclists who are also German skaters at the same time:

EXCEPT

Sql Basics Cheat Sheet

EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.

This query displays German cyclists unless they are also German skaters at the same time:

The SQL cheat sheet provides you with the most commonly used SQL statements for your reference. You can download the SQL cheat sheet as follows:

Querying data from a table

Query data in columns c1, c2 from a table

Query all rows and columns from a table

Query data and filter rows with a condition

Query distinct rows from a table

Sort the result set in ascending or descending order

Skip offset of rows and return the next n rows

Group rows using an aggregate function

Filter groups using HAVING clause

Querying from multiple tables

Inner join t1 and t2

Left join t1 and t1

Right join t1 and t2

Perform full outer join

Produce a Cartesian product of rows in tables

Another way to perform cross join

Join t1 to itself using INNER JOIN clause

Using SQL Operators

Combine rows from two queries

Best Sql Cheat Sheet 2019

Return the intersection of two queries

Subtract a result set from another result set

Query rows using pattern matching %, _

Query rows in a list

Best Sql Cheat Sheet

Query rows between two values

Check if values in a table is NULL or not

Managing tables

Create a new table with three columns

Delete the table from the database

Add a new column to the table

Drop column c from the table

Add a constraint

Drop a constraint

Rename a table from t1 to t2

Rename column c1 to c2

Remove all data in a table

Using SQL constraints

Set c1 and c2 as a primary key

Set c2 column as a foreign key

Make the values in c1 and c2 unique

Ensure c1 > 0 and values in c1 >= c2

Set values in c2 column not NULL

Modifying Data

Insert one row into a table

Insert multiple rows into a table

Insert rows from t2 into t1

Update new value in the column c1 for all rows

Update values in the column c1, c2 that match the condition

Delete all data in a table

Delete subset of rows in a table

Managing Views

Create a new view that consists of c1 and c2

Create a new view with check option

Create a recursive view

Create a temporary view

Delete a view

Managing indexes

Create an index on c1 and c2 of the t table

Create a unique index on c3, c4 of the t table

Drop an index

Managing triggers

Create or modify a trigger

WHEN

• BEFORE – invoke before the event occurs
• AFTER – invoke after the event occurs

EVENT

• INSERT – invoke for INSERT
• UPDATE – invoke for UPDATE
• DELETE – invoke for DELETE

TRIGGER_TYPE

• FOR EACH ROW
• FOR EACH STATEMENT

Delete a specific trigger