A SQL Cheat Sheet

The most common-used commands of SQL.

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

SELECT — filter your columns

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.

WHERE — filter the rows

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

Syntax: SELECT * FROM table_name WHERE col_name=value;

GROUP BY — aggregate the data

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

ORDER BY — order the results

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:

INSERT INTO — insert values manually

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

INSERT INTO — insert into one table using another

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.

Creating View from a single table:

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

Creating View from multiple tables:

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

Different types of Joins are:

INNER JOIN

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;

LEFT JOIN

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;

RIGHT JOIN

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;

FULL JOIN

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;

Update on JOINed Queries

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 ;

Semi JOINs

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

INDEXES

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.

Creating an index

Syntax: CREATE INDEX index ON table_name col_name;

For multiple columns

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

UNIQUE INDEX

Syntax: CREATE UNIQUE INDEX index ON table_name col_name;

Some Useful Utility Functions

convert strings to dates:

TO_DATE (Oracle, PostgreSQL), STR_TO_DATE (MySQL)

return the first non-NULL argument:

COALESCE (col1, col2, “default value”)

return current time:

CURRENT_TIMESTAMP

compute set operations on two result sets

SELECT col1, col2 FROM table1

UNION / EXCEPT / INTERSECT

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

Thank You! Happy Learning

Feel free to connect me on Linkedin.