Show Menu
Cheatography

SQL Cheatseet with all important commands

COMMANDS

COMMAND
CODE
DESCRI­PTION
Create
CREATE DATABASE <DA­TABASE NAME>
CREATE TABLE <TABLE NAME>
used to create a new database or table
Drop
DROP DATABASE <DA­TABASE NAME>
DROP TABLE <TABLE NAME>
used to delete an existing database or table
Truncate
TRUNCATE TABLE <TABLE NAME>
used to delete inform­ation in the table but doesn t delete the table itself
Alter
ALTER TABLE <TABLE NAME>
ADD <COLUMN NAME> <DATA TYPE>
ALTER TABLE <TABLE NAME>
DROP COLUMN <COLUMN NAME>
ALTER TABLE <TABLE NAME>
ALTER COLUMN <COLUMN NAME> <DATA TYPE>
used to delete, add or modify constr­aints or columns in a table
Backup
BACKUP DATABASE <DA­TABASE NAME>
TO DISK = ‘<P­ATH­>’
used to create a backup on an existing database
Insert
INSERT INTO <TABLE NAME> (<C­OLU­MN1­>, ....)
VALUES (<V­ALU­E1>, ....)
used to insert new tuples (rows) in a table
*you do not need to specify all columns if you will add values for all the columns
Delete
DELETE FROM <TABLE NAME>
WHERE <CO­NDI­TIO­N>
used to delete tuples (rows) from a table
*if you don t add the WHERE clause, all rows will be deleted
Update
UPDATE <TABLE NAME>
SET <COLUMN NAME> = <NEW VALUE>
WHERE <CO­NDI­TIO­N>
used to modify existing records in a table
Select
SELECT <AT­TRIBUTE LIST>
FROM <TABLE NAME>
WHERE <CO­NDI­TIO­N>
used to select data from a table
*if you want all attributes of a table use (*)
Union, Intersect, Except
<FIRST SELECT STATEM­ENT>
UNION / INTERSECT / EXCEPT
<SECOND SELECT STATEM­ENT>
equivalent to the set operat­ions: union, inters­ection and differ­ence.
In
SELECT <AT­TRIBUTE LIST>
FROM <TABLE NAME>
WHERE <VA­LUE> IN <AN­OTHER SELECT QUERY>
compares a value with a set of values, returns true if the value is one of the elements of the set.
Null
<AT­TRIBUTE NAME> IS (NOT) NULL
used to check whether a value is NULL
Join
SELECT <AT­TRI­BUTES LIST>
FROM <TABLE 1> JOIN <TABLE 2>
ON <JOIN CONDIT­ION>
WHERE <SE­LECTION CONDIT­ION>
used to join two tables based on a related column between them
Assertion
CREATE ASSERTION <AS­SERTION NAME>
CHECK (<C­OND­ITI­ON>)
used to ensure a certain condition is always met in the database
Trigger
CREATE TRIGGER <TR­IGGER NAME>
BEFORE / AFTER
INSERT / UPDATE / DELETE
ON <TABLE NAME>
FOR EACH ROW
<TRIGGER BODY>
Triggers are activated when a defined action is executed for the table
Data Types
Numeric - INT, SMALLINT, DECIMAL(i, j)
String - CHAR, CHAR(n), VARCHAR(n)
Bit Sring - BIT, BIT(n)
Date and Time - DATE, TIME, TIME(i)
Timestamp -TIMESTAMP
Refere­ntial Triggered Action
ON DELETE <OP­TIO­N>
ON UPDATE <OP­TIO­N>
used to set what happens on updating or deleting a tuple (row) in the database that references another row
OPTIONS:
SET NULL
SET DEFAULT
CASCADE
Renaming (Aliasing)
<TABLE NAME> AS <NEW TABLE NAME>
(<NEW ATTRIBUTE 1 NAME>, .....)
Relation and attribute names can be renamed for conenience or to remove ambiguity using the keyword AS
Cross Product (,)
SELECT <AT­TRIBUTE LIST>
FROM <TABLE 1>, <TABLE 2>
used to produce a result table that has the number of rows of the first table multiplied by the number of rows of the second table
Duplicates
SELECT ALL <AT­TRIBUTE LIST>
FROM <TABLE NAME> <AT­TRI­BUT­E> LIKE <PA­TTE­RN> <AT­TRIBUTE NAME> IS (NOT) NULL
SELECT <AT­TRI­BUTES LIST>
FROM <TABLE 1> JOIN <TABLE 2> ON <JOIN CONDIT­ION> WHERE <SE­LECTION CONDIT­ION> SELECT DISTINCT <AT­TRIBUTE LIST> FROM <TABLE NAME>
DISTINCT is used to eliminate duplicates
ALL is used to allow duplicates *SELECT without ALL or DISTINCT is equivalent to ALL
String Compar­isons
<AT­TRI­BUT­E> LIKE <PA­TTE­RN>
LIKE is used for string comparisoJ
(%) replaces an arbitary number of characters
(_) replaces one character
Arithmetic Operators
# (+) add  ­ ­ ­ ­ ­ ­ ­ ­ ­ # (*) multiply
# ( ) subtract  ­ ­ ­ ­ # (/) divide
Ordering
<SELECT STATEM­ENT>
ORDER BY <AT­TRI­BUT­E> <ASC / DESC>
ORDER BY is used to order the resulting tuples
The keyword ASC (ascen­ding) and DESC can be used. *The default is ASC (ascen­ding)
Set Compar­isons
SELECT <AT­TRIBUTE LIST>
FROM <TABLE NAME>
WHERE <VA­LUE> > ALL / ANY <AN­OTHER SELECT QUERY>
ANY and ALL can be used with (=, >, >=, <, <=, <>) to compare a value with a set
#CONTAINS Compares two sets and returns true if one set contains the other
#EXISTS It checks whether the result of a nested query is empty or not
#UNIQUE checks if the table has duplicates
Aggregate Functions
  
#COUNT - Counts how many rows in a particular column
#SUM - adds together all the values in a particular column
#MIN returns the minumum value in a column
#MAX returns the maximum value in a column
#AVG - returns the average of a group of selected values
Types of Join
  
Inner join
Left Join
Right Join
Full Outer Join
Credit: @yosra­codes
           
 

Metadata

  • Languages:
  • Published: 17th May, 2022

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

          Web Programming Cheat Sheet
          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet

          More Cheat Sheets by rahilkasimi

          CSS Selectors Cheatsheet Cheat Sheet
          Five ways to center DIV element in CSS Cheat Sheet