A SQL Cheat Sheet

The most common-used commands of SQL.

Image for post
Image for post

What is SQL?

Structured Query Language or SQL is a standard Database language which is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. The recent ISO standard version of SQL is SQL:2019.

As the name suggests, it is used when we have structured data (in the form of tables). All databases that are not relational (or do not use fixed structure tables to store data) and therefore do not use SQL, are called NoSQL databases. Examples of NoSQL are MongoDB, DynamoDB, Cassandra, etc

To know more about SQL Click here:

Basic Queries:-

The SELECT statement in SQL is used to retrieve or fetch data from a database.

Syntax : SELECT col1, col2,…colN FROM table_name;

Some useful Keywords of SELECT

  • DISTINCT — A keyword that returns only unique rows within a result set
  • BETWEEN a AND b — limit the range, the values can be numbers, text, or dates
  • LIKE — pattern search within the column text
  • IN (a, b, c) — check if the value is contained among given.

The SELECT statement returned all the results from the queried database table.

Syntax: SELECT * FROM table_name WHERE col_name=value;

The GROUP BY clause groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.

Syntax: SELECT col_name FROM table_name WHERE condition GROUP BY col_name(s);

The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

Syntax: SELECT col1, col2,…colN FROM table_name ORDER BY col1, col2,..colN ASC|DESC;

Data Modification:-

Data Manipulation Language (DML) besides of the SELECT statement that retrieves information from databases includes also statements modifying data state. These statements are:

Syntax: INSERT INTO table_name(col1, col2, ….colN) VALUES(value1, value2,…valueN);

Syntax: INSERT INTO table1 [(col1, col2, … colN)] SELECT col1, col2,..colN FROM table2 [WHERE condition];

Views:-

A VIEW is a virtual table, which is a result of a query. They can be used to create virtual tables of complex queries.

Syntax: CREATE VIEW view_name AS SELECT col1, col2,…colN FROM table_name WHERE condition;

Syntax: CREATE VIEW view_name AS SELECT table1.col1,…table1.colN, table2.col1,…table2.colN, …. FROM table1, table2,…tableN WHERE condition;

SQL JOIN:-

A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them.

General Syntax: SELECT col_name FROM table1 JOIN table2 ON col-name1 = col-name2 WHERE condition

Image for post
Image for post

The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies.

Syntax: SELECT table1.col1, table1.col2, table2.col1,… FROM table1 INNER JOIN table2 ON condition;

Image for post
Image for post

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join.

Syntax: SELECT table1.col1, table1.col2, table2.col1,… FROM table1 LEFT JOIN table2 ON condition;

Image for post
Image for post

RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join.

Syntax: SELECT table1.col1, table1.col2, table2.col1,… FROM table1 RIGHT JOIN table2 ON condition;

Image for post
Image for post

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables.

Syntax: SELECT table1.col1, table1.col2, table2.col1,… FROM table1 FULL JOIN table2 ON condition;

In SQL Server, we can use these join clauses in the UPDATE statement to perform a cross-table update.

Syntax: UPDATE table1 SET table1.col1 = table2.col2, table1.col2 = expression, … FROM table1 [INNER | LEFT] JOIN table2 ON join_condition WHERE where_condition ;

A Semi-JOIN between two tables returns rows from the first table where one or more matches are found in the second table.

Syntax: SELECT col1, col2 FROM table1 WHERE id IN (SELECT table1_id FROM table2 WHERE condition);

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O by using a rapid path access method to locate data quickly.

Syntax: CREATE INDEX index ON table_name col_name;

Syntax: CREATE index ON table_name (col1, col2,… colN);

Syntax: CREATE UNIQUE INDEX index ON table_name col_name;

Some Useful Utility Functions

TO_DATE (Oracle, PostgreSQL), STR_TO_DATE (MySQL)

COALESCE (col1, col2, “default value”)

CURRENT_TIMESTAMP

SELECT col1, col2 FROM table1

SELECT col1, col2 FROM table2;

Reporting

COUNT — return the number of rows

SUM — cumulate the values

AVG — return the average for the group

MIN / MAX — smallest / largest value

Feel free to connect me on Linkedin.

Full Stack Web Developer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store