SQL Cheat Sheet

For quick reference, below is the cheat sheet I created for an introduction to SQL course I took in 2005. Use CTRL-F to find specific terms.

#####################################################################################

SQL Cheatsheet

SELECT			selects columns
ORDER BY (DESC, ASC)	sorts output, must be the last clause. Can sort
			multiple levels. ASC is default.
WHERE			filters output, contains logical expression:
	=		Text and dates need delimiters, numbers/money no.
	>
	<
	<>
	>=
	<=
	= 'Db'
	IN (1985, 1995, 1997)			or NOT IN
	IN ('CMP', 'PGM', 'SCI')		or NOT IN
	BETWEEN 				inclusive, range must be increasing. 
	BETWEEN 1990 AND 1993

Text value tests: leading blanks are significant.

Null - a value missing from the table. Must be explicitly tested for.
       Use IS NULL or IS NOT NULL.

Zero length strings - these are strings, and they aren't null.
                      Use two single quotes, '' to denote a zero length string. 

AND, OR, NOT	Use IN list for multiple ORs. Precedence is NOT, AND, OR.

Use parentheses to group logical operators.

Logic tables: Databases allow null values, so a logic table can have TRUE, FALSE,
              and UNKNOWN entries.

CROSS JOIN - cartesian product
INNER JOIN, ON - can be nested
LEFT/RIGHT OUTER JOIN - include all records from the left or right once. 
FULL JOIN

DISTINCT	removes duplicate rows from output (that might result from a join),
		i.e. SELECT DISTINCT

TOP n 	must include ORDER BY, then returns first n rows

WITH TIES	includes ties for the last place, where n = int or int PERCENT. 

Example: TOP 30 PERCENT WITH TIES

AS	column or table aliasing. Use [] delimiter for spaces/punctuation
	can't use alias in criterion. 

Example: AS [Zip Code]

+	use in SELECT to concatenate fields. Use ' ' for spaces. Use with AS.
	if a field is null, entire concatenation is null.

Simple arithmetic: +, -, *, /, % (ints only)
Use operators in SELECT, WHERE, ORDER BY, BETWEEN, IN

As with concatenation, arithmetic operations on nulls result in a null.

Order of Precedence
Exponentiation
Negation(-)
Multiplication, Division (/)
Modulus Arithmetic (mod)
Addition, Subtraction

Math Functions
Round(numeric expression, numDigits) This is rounding, not formatting(STR)
Round(numeric expr, numDigits, 1) Truncates values
Floor(numeric expr)		returns an int expression
Ceiling(numberic expr)	returns an int expression

Cast and Convert Functions
Cast(valueToConvert AS dataTypeDesired)
Convert(dataTypeDesired, ExpressionToConvert, Style) p.138 for styles

Dates and Times
Remember to always include years.
GETDATE()		retrieves the system date. 

Predefined Convert Styles(text only)
100	Jun 5 2005 4:51PM
101	06/05/2005
102	2005.06.05
104	05.06.2005
111	2005/06/05
112	20050605

To see the date expressed as an integer: Convert(int, getdate()). You can do date
arithmetic. The days unit is used in date arithmetic.

Date Functions
Year(Date Expression)
Month(Date Expression)
Day(Date Expression)
Week(Date Expression)

DatePart(DateUnit, DateExpression)		returns dateunit part of expr
DateAdd(DateUnit, Number, DateExpression)	where Number is an integer.
						adds number of dateunits to date
DateName(DateUnit, DateExpression)		returns the name of DateUnit (day or week)

DateUnit is:	Year, yy, yyyy
		Month, mm, m
		Day, dd, d
		Quarter, qq, q
		DayOfYear, dy, y
		Week, wk, w
		Weekday, dw (Sunday is 1)

DateDiff(DateUnit, Date1, Date2)	returns the dateunit intervals crossed 
					between Date1 and Date2 (Sunday is 1st)

CHAR - fixed width (blanks padded on if value is too short)
VARCHAR - actual width
Zero length string - ""
Null string - not zero length, not blank, just nothing at all

String Functions

Upper(str), Lower(str)		manipulates case
Left(str, n), Right(str, n)	returns the start or end of a string, n?0
Ltrim(str), Rtrim(str)		removes leading or trailing blanks
Len(str)			returns the number of characters in expression
Substring(str, startnum, length)	returns specified part of str
Charindex(str1, str2)	searches for starting position of str1 inside str2. Returns 0
			if not found. 3rd argument specifies starting position.
str(NumExpr, length, DecDigits)	formats a number with a specific number of digits after
				the decimal point. Length is the field width.
ascii(str)			returns the ASCII value of str
char(n)				returns ASCII character for number n 0-255
Nchar(n)			returns UNICODE character for number n 0-65535
Replace(str1, str2, str3)	replaces str2 with str3 in str1
Space(n) 			returns a string of n spaces
Replicate(str, n)		repeats str n times

Logical Functions

ISNULL(expression, replacement)	returns replacement when expression is null
ISNUMERIC(expression)		returns 1 if expression can be converted to a number.
				0 if not.

CASE expression
	WHEN value1 THEN result1	all values must be the same type
	WHEN value2 THEN result2
	ELSE result99
END

CASE
	WHEN logExpr1 THEN result1
	WHEN logExpr2 THEN result2
	ELSE result99
END

COALESCE(expressions)	returns first expression that is not null. If all expressions
			are null, returns null. Expressions must all be the same type.

Wildcards (for matching strings only)

example:	SELECT	blabla
		FROM	blablabla
		WHERE	bla LIKE '%, [ei]'	<==note LIKE keyword

%		matches zero or more of any character (not nulls)
_		matches exactly one of any character
[list]		matches exactly one of the items in the list
[^list]		matches exactly one of the items not in the list
		list can be a range, but range must be ascending.
[#]		match '#' literally, i.e. [] is escape.

Changing Data Values(Action queries)

UPDATE	tableName
SET	columnName1 = value or expression,
	columnName2 = value or expression,
	.
	.
	columnName99 = value or expression
WHERE	condition

DELETE
FROM	tableName
WHERE	condition

INSERT INTO tableName
	VALUES(field1value, field2value, field3 value, etc.)

can also use SELECT in place of VALUES (p161)

Create and Alter Table Design

DROP TABLE tablename

CREATE TABLE tablename(columnname, type(length))
with constraints:
PRIMARY KEY
UNIQUE
REFERENCES
CHECK
NULL, NOT NULL
                                       optional
CREATE TABLE name(columnName datatype CONSTRAINT constraintName constraintType)

Example:
CREATE TABLE test(column1 int CONSTRAINT column1_ck CHECK (column1 < 0))

ALTER TABLE name
ADD                        optional
     columnName datatype (CONSTRAINT constraintName constraintType)

Each ALTER must be done separately.

Creating a compound(composite) primary key:

CONSTRAINT tablename_pk PRIMARY KEY (column1, column2)

Adding a foreign key constraint with cascade delete after table creation:

ALTER TABLE tblName
ADD CONSTRAINT	constraintName 
FOREIGN KEY 	(fieldname) 
REFERENCES 	foreignTblName 
ON DELETE CASCADE

Copying data from one table to another:

SELECT 	columns
INTO	newTable
FROM	existingTable
WHERE	condition

Parameters

CREATE PROCEDURE "StoredProcedure"
	(@parametername1 datatype,
	 @parametername2 datatype,
	 .
	 .
	 @parametername9 datatype)
AS
whatever, using @parameternames for values

Aggregates and Grouping of Data

Count(1)	counts the number of rows in the table (or count(*))
Count(field)	counts the number of rows with non-nulls in that field

Max(field), Min(field)

Avg(field)	ignores null values, returns truncated integer if used with integer

Sum(field)

Count(Distinct field)	counts the number of unique values for that field

#####################################################################################

Back to Technology--->