SQL CheatSheet Cheat Sheet

A Programming › SQL cheat sheet in English by nimakarimian

SQL syntax Cheat Sheet

SQL Database Server Sqlserver Sql-server

11,565 views 2,634 downloads 18 Jul 2022 History

PRs

SELECT

Specific Columns	`Select Col1,Col2,...,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 CountryCount 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 country".

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\|percent column_name(s) FROM table_name WHERE condition;`
MYSQL	`SELECT column_name(s) FROM table_name WHERE condition LIMIT number;`
ORACLE	`SELECT column_name(s) FROM table_name ORDER BY column_name(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.

ANY & ALL

ANY	`SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);`
ALL	`SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);`
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 <=).

ISNULL

MYSQL	`SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;`
SQL SERVER	`SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products;`
ORACLE	`SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products;`
Alternative	`SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;`

COALESCE() Function Works with MYSQL SQLSERVER and ORACLE

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 <====

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 constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

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

CKECK

MYSQL - CREATE TABLE -	`CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );`
Others -CREATE TABLE-	`CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );`
Multiple Columns and Naming	`CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );`
CHECK -ALTERTABLE-	`ALTER TABLE Persons ADD CHECK (Age>=18);`
Multiple Columns and naming -ALTERTABLE-	`ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');`
DROP CHECK -MYSQL-	`ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;`
DROP CHECK -Others-	`ALTER TABLE Persons DROP CHECK CHK_PersonAge;`

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;`

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 statements.

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_name(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(column_name) FROM table_name WHERE condition;`
MAX()	`SELECT MAX(column_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 implementation

DELETE

DELETE EVERYTHING	`DELETE FROM table_name;`
DELETE WITH CONDITION	`DELETE FROM table_name WHERE condition;`

SELECT INTO (copy)

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

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 databasename;`
BACKUP	`BACKUP DATABASE databasename TO DISK = 'filepath';`
Differential BACKUP	`BACKUP DATABASE databasename TO DISK = 'filepath' WITH DIFFERENTIAL;`

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;`

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 "Persons" table is already created, use the above Code

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).*

DEFAULT

DEFAULT -CREATE TABLE-	`CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() );`
ALTERTABLE -MYSQL-	`ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';`
ALTERTABLE -SQLSERVER-	`ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;`
ALTERTABLE-Oracle-	`ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes';`
DROPTABLE-MYSQL-	`ALTER TABLE Persons ALTER City DROP DEFAULT;`
DROPTABLE -Others-	`ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;`

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**
<===

SUM() & AVG() & COUNT ()

AVG()	`SELECT AVG(column_name) FROM table_name WHERE condition;`
SUM()	`SELECT SUM(column_name) FROM table_name WHERE condition;`
COUNT()	`SELECT COUNT(column_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 conditions.

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.column_name = table2.column_name;`
LEFT JOIN	`FROM table1 LEFT JOIN table2 ON table1.Column = table2.Column`
RIGHT JOIN	`FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;`
FULL JOIN	`FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;`
SELF JOIN	`SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;`

ORDER BY

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

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.

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.

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.

TABLE

CREATE	`CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );`
DROP	`DROP TABLE table_name;`
CREATE from another Table	`CREATE TABLE new_table_name AS SELECT column1, column2,... FROM existing_table_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.

Constraints

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

Constraints 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,LastName);`
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, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );`
Others - CREATE TABLE -	`CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, PersonID int FOREIGN KEY REFERENCES Persons(PersonID) );`
Multiple Rows or FK with Name	`CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );`
FOREIGN KEY- ALTER TABLE -	`ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);`
Multiple Rows or FK with Name -ALTER TABLE-	`ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);`
DROP FK -MYSQL-	`ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;`
DROP FK -Others-	`ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;`

INDEX

CREATE	`CREATE INDEX index_name ON table_name (column1, column2, ...);`
CREATE UNIQUE	`CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);`
DROP -SQLSERVER-	`DROP INDEX table_name.index_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_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (Personid) );`
ALTERTABLE -MYSQL-	`ALTER TABLE Persons AUTO_INCREMENT=100;`
CREATE TABLE -SQLSERVER-	`CREATE TABLE Persons ( Personid int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(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 (Personid,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen');`

Comments (1)

Daniel Webster 10:54 28 Nov 2023

Brilliant work. But the problem is the commands are overlapping with other text/commands, so it's difficult to see. Otherwise good Job.