https://www.runoob.com/sql/sql-quickref.html

SQLsyntax_1.md

SELECT [ hints ] [ ALL | DISTINCT ] { named_expression | star_clause } [, ...]
  FROM from_item [, ...]
  [ LATERAL VIEW clause ]
  [ PIVOT clause ]
  [ WHERE clause ]
  [ GROUP BY clause ]
  [ HAVING clause]
  [ QUALIFY clause ]

from_item
{ table_name [ TABLESAMPLE clause ] [ table_alias ] |
  JOIN clause |
  [ LATERAL ] table_valued_function [ table_alias ] |
  VALUES clause |
  [ LATERAL ] ( query ) [ TABLESAMPLE clause ] [ table_alias ] }

named_expression
   expression [ column_alias ]

star_clause
   [ { table_name | view_name } . ] *

ALL

Select all matching rows from the relation. Enabled by default.

DISTINCT

Select all matching rows from the relation after removing duplicates in results.

named_expression

An expression with an optional assigned name.

• expression

A combination of one or more values, operators, and SQL functions that evaluates to a value.

• column_alias

An optional column identifier naming the expression result. If no column_alias is provided Databricks Runtime derives one.

star_clause

A shorthand to name all the referencable columns in the FROM clause. The list of columns is ordered by the order of from_items and the order of columns within each from_item.

The _metadata column is not included this list. You must reference it explicitly.

• table_name

If present limits the columns to be named to those in the specified referencable table.

• view_name

If specified limits the columns to be expanded to those in the specified referencable view.

from_item

A source of input for the SELECT. One of the following:

• table_name

Identifies a table that may contain a temporal specification. See Query an older snapshot of a table (time travel) for details.

• view_name

Identifies a view.

• JOIN

Combines two or more relations using a join.

• [ LATERAL ] table_valued_function

Invokes a table function. To refer to columns exposed by a preceding from_item in the same FROM clause you must specify LATERAL.

• VALUES

Defines an inline table.

• [ LATERAL ] ( query )

Computes a relation using a query. A query prefixed by LATERAL may reference columns exposed by a preceding from_item in the same FROM clause. Such a construct is called a correlated or dependent query.

LATERAL is supported since Databricks Runtime 9.0.

• TABLESAMPLE

Optionally reduce the size of the result set by only sampling a fraction of the rows.

• table_alias

Optionally specifies a label for the from_item. If the table_alias includes column_identifiers their number must match the number of columns in the from_item.

PIVOT

Used for data perspective; you can get the aggregated values based on specific column value.

LATERAL VIEW

Used in conjunction with generator functions such as EXPLODE, which generates a virtual table containing one or more rows. LATERAL VIEW applies the rows to each original output row.

WHERE

Filters the result of the FROM clause based on the supplied predicates.

GROUP BY

The expressions that are used to group the rows. This is used in conjunction with aggregate functions (MIN, MAX, COUNT, SUM, AVG) to group rows based on the grouping expressions and aggregate values in each group. When a FILTER clause is attached to an aggregate function, only the matching rows are passed to that function.

HAVING

The predicates by which the rows produced by GROUP BY are filtered. The HAVING clause is used to filter rows after the grouping is performed. If you specify HAVING without GROUP BY, it indicates a GROUP BY without grouping expressions (global aggregate).

QUALIFY

The predicates that are used to filter the results of window functions. To use QUALIFY, at least one window function is required to be present in the SELECT list or the QUALIFY clause.

SQLsyntax_2.md

Type	Size	Range(with marks)	Range(w/o marks)	For
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

SQL WORD	SQL GRAMMER
AND / OR	SELECT column_name(s)
FROM table_name
WHERE condition
AND	OR condition

ALTER TABLE | ALTER TABLE table_name
ADD column_name datatype
or

ALTER TABLE table_name
DROP COLUMN column_name

AS (alias) | SELECT column_name AS column_alias
FROM table_name
or

SELECT column_name
FROM table_name AS table_alias

BETWEEN | SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
CREATE DATABASE | CREATE DATABASE database_name
CREATE TABLE | CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...
)
CREATE INDEX | CREATE INDEX index_name
ON table_name (column_name)
or

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE VIEW | CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DELETE | DELETE FROM table_name
WHERE some_column=some_value
or

DELETE FROM table_name
(Note: Deletes the entire table!!)

DELETE * FROM table_name
(Note: Deletes the entire table!!)

DROP DATABASE | DROP DATABASE database_name
DROP INDEX | DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLE | DROP TABLE table_name
GROUP BY | SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING | SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN | SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTO | INSERT INTO table_name
VALUES (value1, value2, value3,....)
or

INSERT INTO table_name
(column1, column2, column3,...)
VALUES (value1, value2, value3,....)

INNER JOIN | SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOIN | SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN | SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN | SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE | SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY | SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
SELECT | SELECT column_name(s)
FROM table_name
SELECT * | SELECT *
FROM table_name
SELECT DISTINCT | SELECT DISTINCT column_name(s)
FROM table_name
SELECT INTO | SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_table_name
or

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_table_name

SELECT TOP | SELECT TOP number|percent column_name(s)
FROM table_name
TRUNCATE TABLE | TRUNCATE TABLE table_name
UNION | SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL | SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
UPDATE | UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
WHERE | SELECT column_name(s)
FROM table_name
WHERE column_name operator value

warehouse.sql

select *
from [Warehouse].[VehicleTemperatures]

1)
select distinct VehicleRegistration, ChillerSensorNumber
from [Warehouse].[VehicleTemperatures]

2)
select VehicleRegistration, min(RecordedWhen) as FirstReading, max(RecordedWhen) as LastReading
from [Warehouse].[VehicleTemperatures]
group by VehicleRegistration

3)
select VehicleRegistration, ChillerSensorNumber, avg(temperature) 
from [Warehouse].[VehicleTemperatures]
group by VehicleRegistration, ChillerSensorNumber

4)
select SI.StockItemName, C.ColorName
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	left join [Warehouse].[Colors] C
		on C.ColorID = SI.ColorID
where C.ColorName is NULL

5)
select SI.StockItemName, C.ColorName
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	left join [Warehouse].[Colors] C
		on C.ColorID = SI.ColorID
where C.ColorName is NULL
and (SI.StockItemName like '%Green%'
or SI.StockItemName like '%Pink%'
or SI.StockItemName like '%Brown%'
or SI.StockItemName like '%White%'
or SI.StockItemName like '%Black%'
or SI.StockItemName like '%Red%'
or SI.StockItemName like '%Blue%'
or SI.StockItemName like '%Yellow%'
or SI.StockItemName like '%Orange%')

6)
select SI.StockItemName, SH.QuantityOnHand, SH.ReorderLevel
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	left join [Warehouse].[Colors] C
		on C.ColorID = SI.ColorID
where SH.QuantityOnHand <= SH.ReorderLevel

7)
select SG.StockGroupName, SUM(SH.QuantityOnhand) as Quantity
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockItemStockGroups] IG
		on IG.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockGroups] SG
		on SG.StockGroupID = IG.StockGroupID
group by SG.StockGroupName
order by SG.StockGroupName

8)
select SG.StockGroupName, SUM(SH.LastStocktakeQuantity*SH.LastCostPrice) as LastCost
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockItemStockGroups] IG
		on IG.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockGroups] SG
		on SG.StockGroupID = IG.StockGroupID
group by SG.StockGroupName
having SUM(SH.LastStocktakeQuantity*SH.LastCostPrice) < 100000000.00
order by SG.StockGroupName

9)
select SG.StockGroupName, SUM(SH.LastStocktakeQuantity*SH.LastCostPrice) as ReorderCost
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockItemStockGroups] IG
		on IG.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockGroups] SG
		on SG.StockGroupID = IG.StockGroupID
where QuantityOnHand <= ReorderLevel
group by SG.StockGroupName
order by SG.StockGroupName

10)
select SG.StockGroupName, SUM(SH.QuantityOnHand) as TotalQuantity
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockItemStockGroups] IG
		on IG.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockGroups] SG
		on SG.StockGroupID = IG.StockGroupID
group by SG.StockGroupName
having SUM(SH.QuantityOnHand) =
(
select max(TotalQuantity) as MaxQuantity
from 
(
select SG.StockGroupName, SUM(SH.QuantityOnHand) as TotalQuantity
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[StockItemHoldings] SH
		on SH.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockItemStockGroups] IG
		on IG.StockItemID = SI.StockItemID
	inner join [Warehouse].[StockGroups] SG
		on SG.StockGroupID = IG.StockGroupID
group by SG.StockGroupName
) A
)
order by SG.StockGroupName

11)
select SI.StockItemName, PU.PackageTypeName, PI.PackageTypeName
from [Warehouse].[StockItems] SI
	inner join [Warehouse].[PackageTypes] PU
		on PU.PackageTypeID = SI.UnitPackageID
	inner join [Warehouse].[PackageTypes] PI
		on PI.PackageTypeID = SI.OuterPackageID
where PU.PackageTypeName <> PI.PackageTypeName