Tag: Sql Server Cheat Sheet

SQL Server: Functions Cheat Sheet

String Functions

ASCII
CHAR
CHARINDEX
DIFFERENCE
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
REPLACE
QUOTENAME
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
UNICODE
UPPER

Exact Numerics

bit
tinyint
smallint
bigint

Approximate Numerics

float

Date and Time

smalldatetime
datetime
Strings
char
varchar

Unicode Strings

nchar
nvarchar

Binary Strings

binary
varbinary

Miscellaneous

cursor
sql_variant
decimal
money
numeric
real
timestamp
text
ntext
image
table
xml

Date Functions

DATEADD (datepart, number, date)
DATEDIFF (datepart, start, end)
DATENAME (datepart, date)
DATEPART (datepart, date)
DAY (date)
GETDATE()
GETUTCDATE()
MONTH (date)
YEAR (date)

Dateparts

Year	yy, yyyy
Quarter	qq, q
Month	mm, m
Day of Year	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
Minute	mi, n
Second	ss, s
Millisecond	ms

Type Conversion

CAST (expression AS datatype)
CONVERT (datatype, expression)

Ranking Functions

RANK
DENSE_RANK
NTILE
ROW_NUMBER

Grouping (Aggregate) Functions

AVG
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AVG
COUNT
COUNT_BIG
GROUPING
MAX
MIN
SUM
STDEV
STDEVP
VAR
VARP

Table Functions

ALTER
CREATE
DROP
TRUNCATE

Mathematical Functions

ABS
ACOS
ASIN
ATAN
ATN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQUARE
SQRT
TAN

Managing Triggers

Create or modify a trigger

CREATE OR MODIFY TRIGGER trigger_name
WHEN EVENT
ON table_name TRIGGER_TYPE
EXECUTE stored_procedure;

Create a trigger invoked before a new row is inserted into the person table

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
CREATE TRIGGER before_insert_person
BEFORE INSERT
ON person FOR EACH ROW
EXECUTE stored_procedure;

Delete a specific trigger

DROP TRIGGER trigger_name

SQL Aggregate: Functuions Cheat Sheet

AVG returns the average of a list

COUNT returns the number of elements of a list

SUM returns the total of a list

MAX returns the maximum value in a list

MIN returns the minimum value in a list

Managing Indexes

Create an index on c1 and c2 of the table t

CREATE INDEX idx_name
ON t(c1,c2);

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

CREATE UNIQUE INDEX idx_name
ON t(c3,c4);

Drop an index

DROP INDEX idx_name;

Modifying Data

Insert one row into a table

INSERT INTO t(column_list)
VALUES(value_list);

Insert multiple rows into a table

INSERT INTO t(column_list)
VALUES (value_list),
(value_list), ….;

Insert rows from t2 into t1

INSERT INTO t1(column_list)
SELECT column_list
FROM t2;

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

UPDATE t
SET c1 = new_value,
c2 = new_value
WHERE condition;

Delete all data in a table

DELETE FROM t;

Delete subset of rows in a table

DELETE FROM t
WHERE condition;

Using SQL Constraints

Set c1 and c2 as a primary key

CREATE TABLE t(
c1 INT, c2 INT, c3 VARCHAR,
PRIMARY KEY (c1,c2)
);

Set c2 column as a foreign key

CREATE TABLE t1(
c1 INT PRIMARY KEY,
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);

Make the values in c1 and c2 unique

CREATE TABLE t(
c1 INT, c1 INT,
UNIQUE(c2,c3)
);

Ensure c1 > 0 and values in c1 >= c2

CREATE TABLE t(
c1 INT, c2 INT,
CHECK(c1> 0 AND c1 >= c2)
);

Set values in c2 column not NULL

CREATE TABLE t(
c1 INT PRIMARY KEY,
c2 VARCHAR NOT NULL
);

Managing Tables

Create a new table with three columns

CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR NOT NULL,
price INT DEFAULT 0
);

Delete the table from the database

DROP TABLE t ;

Add a new column to the table

ALTER TABLE t ADD column;

Drop column c from the table

ALTER TABLE t DROP COLUMN c ;

Add a constraint

ALTER TABLE t ADD constraint;

Drop a constraint

ALTER TABLE t DROP constraint;

Rename a table from t1 to t2

ALTER TABLE t1 RENAME TO t2

Rename column c1 to c2

ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all data in a table

TRUNCATE TABLE t;

Using SQL Operators

Combine rows from two queries

SELECT c1, c2 FROM t1
UNION [ALL]
SELECT c1, c2 FROM t2;

Return the intersection of two queries

SELECT c1, c2 FROM t1
INTERSECT
SELECT c1, c2 FROM t2;

Subtract a result set from another result set

SELECT c1, c2 FROM t1
MINUS
SELECT c1, c2 FROM t2;

Query rows using pattern matching %, _

SELECT c1, c2 FROM t1
WHERE c1 [NOT] LIKE pattern;

Query rows in a list

SELECT c1, c2 FROM t
WHERE c1 [NOT] IN value_list;

Query rows between two values

SELECT c1, c2 FROM t
WHERE c1 BETWEEN low AND high;

Check if values in a table is NULL or not

SELECT c1, c2 FROM t
WHERE c1 IS [NOT] NULL;

Managing Views

Create a new view that consists of c1 and c2

CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;

Create a new view with check option

CREATE VIEW v(c1,c2)
AS
SELECT c1, c2
FROM t;
WITH [CASCADED | LOCAL] CHECK OPTION;

Create a recursive view

CREATE RECURSIVE VIEW v
AS
select-statement -- anchor part
UNION [ALL]
select-statement; -- recursive part

Create a temporary view

CREATE TEMPORARY VIEW v
AS
SELECT c1, c2
FROM t;

Delete a view

DROP VIEW view_name

Querying data from multiple tables

Inner join t1 and t2

SELECT c1, c2
FROM t1
INNER JOIN t2 ON condition;

Left join t1 and t1

SELECT c1, c2
FROM t1
LEFT JOIN t2 ON condition;

Perform full outer join

SELECT c1, c2
FROM t1
FULL OUTER JOIN t2 ON condition;

Produce a Cartesian product of rows in tables

SELECT c1, c2
FROM t1
CROSS JOIN t2;

Join t1 to itself using INNER JOIN clause

SELECT c1, c2
FROM t1 A
INNER JOIN t2 B ON condition;

Querying Data From Table

Query data in columns c1, c2 from a table

SELECT c1, c2 FROM t;

Query all rows and columns from a table

SELECT * FROM t;

Query data and filter rows with a condition

SELECT c1, c2 FROM t
WHERE condition;

Query distinct rows from a table

SELECT DISTINCT c1 FROM t
WHERE condition;

Group rows using an aggregate function

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;

Filter groups using HAVING clause

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition;

SQL Server: Functions Cheat Sheet
Managing Triggers
SQL Aggregate: Functuions Cheat Sheet
Managing Indexes
Modifying Data
Using SQL Constraints
Managing Tables
Using SQL Operators
Managing Views
Querying data from multiple tables
Querying Data From Table