Show Menu
Cheatography

SQL CheatSheet Cheat Sheet by

SQL syntax Cheat Sheet

SELECT

Specific Columns
Select Col1,C­ol2­,...,Col n from tablename
Select Distinct
Select Distinct * from tablename
you can show the results in your desired name by using 'AS' clause like :
select Count(*) as Countr­yCount from ( select Country from Customers)

GROUP BY

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each countr­y".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

INSERT INTO

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

SELECT TOP

SQL SERVER
SELECT TOP number­|pe­rcent column­_na­me(s) FROM table_name WHERE condition;
MYSQL
SELECT column­_na­me(s) FROM table_name WHERE condition LIMIT number;
ORACLE
SELECT column­_na­me(s) FROM table_name ORDER BY column­_na­me(s) FETCH FIRST number ROWS ONLY;
Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

BETWEEN

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

// you can also use NOT with BETWEEN

SELECT * FROM Products
WHERE ProductName NOT BETWEEN value1 AND value2

LIKE

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;


WHERE CustomerName LIKE 'a%==Finds any values that start with "a"
The percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character
You can also combine any number of conditions using AND or OR operators.

VIEW

CREATE VIEW
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
UPDATING VIEW
CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
DROPPING VIEW
DROP VIEW view_name;

CKECK

MYSQL - CREATE TABLE -
CREATE TABLE Persons ( ID int NOT NULL, LastName varcha­r(255) NOT NULL, FirstName varcha­r(255), Age int, CHECK (Age>=18) );
Others -CREATE TABLE-
CREATE TABLE Persons ( ID int NOT NULL, LastName varcha­r(255) NOT NULL, FirstName varcha­r(255), Age int CHECK (Age>=18) );
Multiple Columns and Naming
CREATE TABLE Persons ( ID int NOT NULL, LastName varcha­r(255) NOT NULL, FirstName varcha­r(255), Age int, City varcha­r(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='­San­dnes') );
CHECK -ALTER­TABLE-
ALTER TABLE Persons ADD CHECK (Age>=18);
Multiple Columns and naming -ALTER­TABLE-
ALTER TABLE Persons ADD CONSTRAINT CHK_Pe­rsonAge CHECK (Age>=18 AND City='­San­dnes');
DROP CHECK -MYSQL-
ALTER TABLE Persons DROP CONSTRAINT CHK_Pe­rso­nAge;
DROP CHECK -Others-
ALTER TABLE Persons DROP CHECK CHK_Pe­rso­nAge;

UNIQUE

-- SQL SERVER --
CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

-- MYSQL --
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);
-- Define multiple Unique keys -- 
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constr­aints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automa­tically has a UNIQUE constr­aint.

However, you can have many UNIQUE constr­aints per table, but only one PRIMARY KEY constraint per table.

Stored procedures (User defined functions)

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

----------------------
EXEC procedure_name;
----------------------
EXAMPLE ====>

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
----------------------
EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';


END EXAMPLE <====

ISNULL

MYSQL
SELECT Produc­tName, UnitPrice * (Units­InStock + IFNULL­(Un­its­OnO­rder, 0)) FROM Products;
SQL SERVER
SELECT Produc­tName, UnitPrice * (Units­InStock + ISNULL­(Un­its­OnO­rder, 0)) FROM Products;
ORACLE
SELECT Produc­tName, UnitPrice * (Units­InStock + NVL(Un­its­OnO­rder, 0)) FROM Products;
Altern­ative
SELECT Produc­tName, UnitPrice * (Units­InStock + COALES­CE(­Uni­tsO­nOrder, 0)) FROM Products;
COALESCE() Function Works with MYSQL SQLSERVER and ORACLE

ANY & ALL

ANY
SELECT column­_na­me(s) FROM table_name WHERE column­_name operator ANY (SELECT column­_name FROM table_name WHERE condit­ion);
ALL
SELECT column­_na­me(s) FROM table_name WHERE column­_name operator ALL (SELECT column­_name FROM table_name WHERE condit­ion);
SOME
SELECT * FROM Products WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 20);
returns a boolean value as a result
The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
 

WHERE keyword

SELECT Col1,Col2 FROM tablename WHERE condition
WHERE is for when you want to declare a specific condition for the query. in the other hand, you want to filter the records
TIP1 WHERE can be used in UPDATE, SELECT, DELETE, ETC...

UNION


SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;


The UNION operator selects only distinct values by default. 
To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
The UNION operator is used to combine the result-set of two or more SELECT statem­ents.

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

Aliases

Column Aliases
SELECT column­_name AS alias_name FROM table_­name;
Table Aliases
SELECT column­_na­me(s) FROM table_name AS alias_­name;

WILDCARDS

%
Represents zero or more characters
bl% finds bl, black, blue, and blob
_
Represents a single character
h_t finds hot, hat, and hit
[]
Represents any single character within the brackets
h[oa]t finds hot and hat, but not hit
^
Represents any character not in the brackets
h[^oa]t finds hit, but not hot and hat
-
Represents any single character within the specified range
c[a-b]t finds cat and cbt

MIN() & MAX()

MIN()
SELECT MIN(co­lum­n_name) FROM table_name WHERE condition;
MAX()
SELECT MAX(co­lum­n_name) FROM table_name WHERE condition;
**you can use MIN() and MAX() anywhere in any Query you like
the examples above are just to show implem­ent­ation**

DELETE

DELETE EVERYTHING
DELETE FROM table_­name;
DELETE WITH CONDITION
DELETE FROM table_name WHERE condition;

NOTNULL

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

-------------------------------
ALTER TABLE Persons
MODIFY Age int NOT NULL;
To create a NOT NULL constraint on the "­Age­" column when the "­Per­son­s" table is already created, use the above Code

DATE

 DATE - format YYYY-MM-DD
 DATETIME - format: YYYY-MM-DD HH:MI:SS
 TIMESTAMP - format: YYYY-MM-DD HH:MI:SS 

FOR MYSQL===>
 YEAR - format YYYY or YY
<===


FOR SQLSERVER===>
 TIMESTAMP - format: a unique number
<===

SELECT INTO (copy)

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
SELECT * INTO Custom­ers­Bac­kup2017 IN 'Backu­p.mdb'
FROM Customers;

DEFAULT

DEFAULT -CREATE TABLE-
CREATE TABLE Orders ( ID int NOT NULL, OrderN­umber int NOT NULL, OrderDate date DEFAULT GETDATE() );
ALTERTABLE -MYSQL-
ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';
ALTERTABLE -SQLSE­RVER-
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
ALTERT­ABL­E-O­racle-
ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes';
DROPTA­BLE­-MYSQL-
ALTER TABLE Persons ALTER City DROP DEFAULT;
DROPTABLE -Others-
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;

CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Database

CREATE
CREATE DATABASE testDB;
SHOW
SHOW DATABASES
DROP
DROP DATABASE databa­sename;
BACKUP
BACKUP DATABASE databa­sename TO DISK = 'filep­ath';
Differ­ential BACKUP
BACKUP DATABASE databa­sename TO DISK = 'filepath' WITH DIFFER­ENTIAL;

ALTER table

ADD Column
ALTER TABLE table_name ADD column­_name datatype;
DROP Column
ALTER TABLE table_name DROP COLUMN column­_name;
ALTER/­MODIFY COLUMN SQLSERVER
ALTER TABLE table_name ALTER COLUMN column­_name datatype;
ALTER/­MODIFY COLUMN MYSQL
ALTER TABLE table_name MODIFY COLUMN column­_name datatype;
ALTER/­MODIFY COLUMN ORACLE
ALTER TABLE table_name MODIFY column­_name datatype;

PRIMARY KEY

--MYSQL--


CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
--SQLSERVER ORACLE--


CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);


--Defining multiple Columns as PK-- 
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

In the example above there is only ONE PRIMARY KEY (PK_Person).
 However, the VALUE of the primary key is made up of 
TWO COLUMNS (ID + LastName).
 

SUM() & AVG() & COUNT ()

AVG()
SELECT AVG(co­lum­n_name) FROM table_name WHERE condition;
SUM()
SELECT SUM(co­lum­n_name) FROM table_name WHERE condition;
COUNT()
SELECT COUNT(­col­umn­_name) FROM table_name WHERE condition;

IN

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

OR

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR condit­ions.

UPDATE

UPDATE WITH CONDITION
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

JOINS

INNER JOIN
FROM table1 INNER JOIN table2 ON table1.co­lum­n_name = table2.co­lum­n_name;
LEFT JOIN
FROM table1 LEFT JOIN table2 ON table1.Column = table2.Column
RIGHT JOIN
FROM table1 RIGHT JOIN table2 ON table1.co­lum­n_name = table2.co­lum­n_name;
FULL JOIN
FROM table1 FULL OUTER JOIN table2 ON table1.co­lum­n_name = table2.co­lum­n_name WHERE condition;
SELF JOIN
SELECT column­_na­me(s) FROM table1 T1, table1 T2 WHERE condition;

ORDER BY

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Constr­aints

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);
Constr­aints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
CREATE INDEX

PRIMARY KEY on ALTER TABLE

ALTERTABLE SIMPLE
ALTER TABLE Persons ADD PRIMARY KEY (ID);
ALTERTABLE multiple or naming
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,La­stN­ame);
DROP PK -MYSQL-
ALTER TABLE Persons DROP PRIMARY KEY;
DROP PK -Others-
ALTER TABLE Persons DROP CONSTRAINT PK_Person;

FOREIGN KEY

MYSQL - CREATE TABLE -
CREATE TABLE Orders ( OrderID int NOT NULL, OrderN­umber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Person­s(P­ers­onID) );
Others - CREATE TABLE -
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderN­umber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Person­s(P­ers­onID) );
Multiple Rows or FK with Name
CREATE TABLE Orders ( OrderID int NOT NULL, OrderN­umber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_Per­son­Order FOREIGN KEY (PersonID) REFERENCES Person­s(P­ers­onID) );
FOREIGN KEY- ALTER TABLE -
ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Person­s(P­ers­onID);
Multiple Rows or FK with Name -ALTER TABLE-
ALTER TABLE Orders ADD CONSTRAINT FK_Per­son­Order FOREIGN KEY (PersonID) REFERENCES Person­s(P­ers­onID);
DROP FK -MYSQL-
ALTER TABLE Orders DROP FOREIGN KEY FK_Per­son­Order;
DROP FK -Others-
ALTER TABLE Orders DROP CONSTRAINT FK_Per­son­Order;

TABLE

CREATE
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
DROP
DROP TABLE table_­name;
CREATE from another Table
CREATE TABLE new_ta­ble­_name AS SELECT column1, column­2,... FROM existi­ng_­tab­le_name WHERE ....;
TRUNCATE
TRUNCATE TABLE table_­name;
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

INSET INTO

INSERT INTO table2
SELECT * FROM table1
WHERE condition;
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

The INSERT INTO SELECT statement requires that the data types in source and target tables match.

INDEX

CREATE
CREATE INDEX index_name ON table_name (column1, column2, ...);
CREATE UNIQUE
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
DROP -SQLSE­RVER-
DROP INDEX table_­nam­e.i­nde­x_name;
DROP -MYSQL-
ALTER TABLE table_name DROP INDEX index_­name;
DROP -ORACLE-
DROP INDEX index_­name;

AUTO INCREMENT

CREATE TABLE -MYSQL-
CREATE TABLE Persons ( Personid int NOT NULL AUTO_I­NCR­EMENT, LastName varcha­r(255) NOT NULL, FirstName varcha­r(255), Age int, PRIMARY KEY (Personid) );
ALTERTABLE -MYSQL-
ALTER TABLE Persons AUTO_I­NCR­EME­NT=100;
CREATE TABLE -SQLSE­RVER-
CREATE TABLE Persons ( Personid int IDENTI­TY(1,1) PRIMARY KEY, LastName varcha­r(255) NOT NULL, FirstName varcha­r(255), Age int );
CREATE SEQUENCE -ORACLE-
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
use sequence in oracle
INSERT INTO Persons (Perso­nid­,Fi­rst­Nam­e,L­ast­Name) VALUES (seq_p­ers­on.n­ex­tva­l,'­Lar­s',­'Mo­nsen');

EXISTS

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

HAVING

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
               
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet

          More Cheat Sheets by nimakarimian

          C++ Pointers cookbook Cheat Sheet
          Dart numbers Cheat Sheet