SQL Quick Reference Guide

For a software Developer or a programmer, basic knowledge on SQL is a must. This helps in retrieving the information from the database and to perform modifications and several operations on the data.
This SQL cheat sheet provides you with the necessary concepts required to start on with SQL. It is helpful for the beginners willing to learn and work on SQL.
SQL Basic Cheat Sheet
Further, if you want to learn SQL in-depth, you can refer to the tutorial blog on SQL

You can download the printable PDF of this SQL cheat sheet

SQL Basic Cheat Sheet
SQL: Structured query language (SQL) is a domain specific language used for programming and querying a database.

Wish to get certified in SQL! Learn from our SQL expert and do excel in your career with intellipaat’s SQL certification.

Watch this SQL Tutorial for Beginners video

Basic Keywords in SQL:

KeywordsExplanation
SELECTIt is used to specify which column to query. Use * for all
FROMIt is used to declare the table to select from
WHEREIt is used to define a condition
=Used to compare a value with the given input
LIKEIt is a special operator used with WHERE to search for a specific pattern from a column or row
GROUP BYIt is used to group identical data
HAVINGIt is used to specify that rows with aggregate values which meets the specifies condition must be returned
INNER JOINIt is used to return all rows where key records of one table is same as that of the other table
LEFT JOINIt is used to return all rows from the left table with the matching rows in the right table
RIGHT JOINIt is used to return all rows from right table with the matching rows in the left table
FULL OUTER JOINIt is used to return rows that match either in the left or right table

Basic Keywords in SQL

SQL Architecture

SQL Data Types:

  • Exact Numeric’s:
    • INTEGER
    • SMALLINT
    • BIGINT
    • NUMERIC
    • DECIMAL
  • Approximate Numeric’s:
    • REAL
    • DOUBLE PRECISION
    • FLOAT
    • DECFLOAT
  • Binary Strings:
    • BINARY
    • BINARY VARYING
    • BINARY LARGE OBJECT
  • Boolean:
    • BOOLEAN
  • Character Strings:
    • CHARACTER
    • CHARACTER VARYING (VARCHAR)
    • CHARACTER LARGE OBJECT
    • NATIONAL CHARACTER
    • NATIONAL CHARACTER VARYING
    • NATIONAL CHARACTER LARGE OBJECT
  • Date times:
    • DATE
    • TIME WITHOUT TIMEZONE
    • TIMESTAMP WITHOUT TIMEZONE
    • TIME WITH TIMEZONE
    • TIMESTAMP WITH TIMEZONE
  • Intervals:
    • INTERVAL DAY
    • INTERVAL YEAR
  • Collection Types:
    • ARRAY
    • MULTISET
  • Other Types:
    • ROW
    • XML

SQL functions:

FunctionDescription
TO_DATEIt is used to convert a string to date.
COALESCEReturns the first non NULL results, when querying with the columns that contain NULL
CURRENT_TIMESTAMPReturns the correct time on the database server
COUNTAn aggregate function that returns the number of rows in the result set
SUMAn aggregate function that sums up the values in a result set
AVGTo compute the mean average of the values in the result set
MIN/MAXAn aggregate function to return the largest/smallest value among the result set
LISTAGGIt is used to transform values from a group of rows into a delimited string

A set operation can be used on the returned results called ‘UNION’ which can append the result of one query to another
Syntax:

SELECT col1, col2 FROM table1
UNION
SELECT col3, col4 FROM table2;

Aggregate function: It is a function where the values of multiple rows are combined to form a single value

SQL Operators:

These are the reserved words or characters used in the SQL statements to perform operations:

OperatorSyntaxDescription
UNIONSELECT c1 FROM t1
UNION [ALL]
SELECT c1 FROM t2
Selecting column 1 for table t1 and column 2 from table t2 and combine the rows of these two queries
INTERSECTSELECT c1 FROM t1
INTERSECT
SELECT c1 FROM t2
It is used to return the intersection of two queries
MINUSSELECT c1 FROM t1
MINUS
SELECT c1 FROM t2
It is used to subtract the second result set from the first
NOT LIKESELECT c1 FROM t
WHERE c1 [NOT] LIKE pattern
It is used to return the query of rows using the matching pattern
BETWEENSELECT c1 FROM t
WHERE c1 BETWEEN min AND max
It returns the rows where c1 is between MIN and MAX
NOT NULLSELECT c1 FROM t
WHERE c1 IS [NOT] NULL
To check if the values are NULL or NOT NULL

Have you got more queries in SQL? Come to our SQL Community and get them clarified today!

View:

It is a virtual table which is a result of a query. It is often used as a security mechanism letting users to access the data through the views
Syntax:

CREATE VIEW view1 AS
SELECT c1, c2
FROM t1
WHERE condition

INDEXES:

It is used to speed up the performance of the queries by reducing the number of database pages to be visited
Syntax:
To create an index- CREATE INDEX index_name ON t(c1, c2)
Create an unique index: CREATE UNIQUE INDEX index_name ON t(c3, c4)
Drop an index: DROP INDEX index_name

Stored Procedure:

It is a set of SQL statements with assigned names that can be shared and reused by multiple programs
Syntax:

CREATE PROCEDURE procedure_name
@variable AS datatype = value
AS
-- Comments
SELECT * FROM t GO

Triggers:

It is a special type of stored procedure that automatically executes when a user tries to modify through a DML event
Syntax:

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

Explanation:

  • WHEN:
    • BEFORE: Invokes before an event occurs
    • AFTER: Invokes after an event occurs
  • EVENT:
    • INSERT: Invoke for Insert
    • UPDATE: Invoke for Update
    • DELETE: Invoke for Delete
  • TRIGGER TYPE:
    • FOR EACH ROW
    • FOR EACH STATEMENT

2. To delete or drop trigger:
It deletes a specific trigger
Syntax: DROP TRIGGER trigger_name

Looking for SQL Training All-in-1 Combo Course? Enroll now!

Using SQL constraints:

Primary key: Set c1 and c2 as primary key
Syntax:

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

Foreign Key: Set c2 column as foreign key
Syntax:

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

Unique: Making the values in C1 and C2 as unique
Syntax:

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

Download a Printable PDF of this SQL Cheat Sheet

With this, we come to an end of SQL Cheat sheet. To get in-depth knowledge, check out our interactive, live-online SQL certification Training here, that comes with 24*7 support to guide you throughout your learning period. Intellipaat’s SQL online training equips you with you with managing the database solutions, managing various operations on databases, migrate it to the cloud and scale on demand.

Check out the top SQL Interview Questions to learn what is expected from SQL professionals!

Leave a Reply

Your email address will not be published. Required fields are marked *