SQL Cheat Sheet

Iulian Timis

1. Introduction

A sql cheat sheet is a notepaper where you write down some information that you will need sometimes in the feature.

Have you ever wondered how useful will be to have a cheat sheet with some information?

SQL Cheat Sheet will introduce you from the basic concepts to some advanced concepts with explanations for each concept.

2. What is SQL?

SQL stands for Structured Query Language. This used as a language to manipulate data from relational databases (RDBMS). You can construct complex queries to handle structured data from databases and incorporate relations among entities and variables. It is able to access many records in a single command.

2.1 A bit of history

SQL language was initially developed at IBM by Donald D. Chamberlin and Raymond F. Boyce. The initial namewas called initially SEQUEL (Structured English Query Language) and was designed to manipulate data stored in IBM databases.

The first attempt of Donald D. Chamberlin and Raymond F. Boyce when developing a relational database was called SQUARE (Specifying Queries in a Relational Environment). Later on, it changed to SQL (dropping the vowels from the SEQUEL) because of a trademark of the UK-based Hawker Siddeley Dynamics Engineering Limited Company. This acronym stands for Structured Query Language.

In 1986 ANSI and ISO standard groups adopted the standard ‘Database Language SQL’ as language definition. Later on, a new version of this standard was published. The last recent had published in 2016. Source Wikipedia.

3. Why you should learn/need SQL?

Most of the biggest companies like Uber, Airbnb, Facebook, Adobe System, and so on. Others use their high-performance database systems that are a derivate from SQL but with a different dialect.

If you are a data analyst, data science, SQL developer, or simply a developer working with applications where the persisted data is a database, you will have to be familiar with SQL. This plays an important role in this area (SQL is one of the easiest languages you can learn). This is easy to use because if you will learn how to SELECT, INSERT, UPDATE and DELETE you will be proficient because most of the time you will need it.

A recent developer survey shows that SQL is ranked in the first three most used programming by the developers.

4. All SQL Keywords

The Keywords are reserved words on a database that will never allow you as an identifier or column name. Some of the keywords can be used AS name for a column but you have to escape with ‘ (apostrophe).

Some of the databases may have different keywords than others. The SQL dialect differs from one database to another, but not so much because many databases platforms are modeled after SQL.

In this article, include the next sections we will focus only on these three databases: MySQL, PostgreSQL, and MSQL Server.

The most comment keywords that will be applied on all databases that are modeled after SQL are: 

SELECT
INSERT 
UPDATE 
DELETE 
WHERE 
ON
OR 
AND 
AS 
JOIN 
IS 
NOT 
CREATE 
TABLE 
INT
DISTINCT 
ASC 
DESC 
ANY 
CASE 
CHECK 
CONSTRAINT and others.

A list with all keywords and reserved words for MySQL you can find in the official documentation. For PostgreSQL, you can find it on this link and for MSQL Server here.

Built-in function for PostgreSQL that you can find all keywords. 

SELECT *
FROM pg_get_keywords()
ORDER BY 1;

5. Types of comments in SQL

There are two types of comments in SQL, line comments and block comments or multi-line comments that apply for Postgress and MSQL Server. 

SELECT * FROM Users;  -- line comment
/* SELECT * FROM Users;
SELECT * FROM Accounts; */ --multi line comments or block comments.

Besides the above-mentioned comments MySQL supports another symbol for line comments as official documentation related. 

SELECT * FROM Users;  # line comment

6. SQL Data Types

Data Types in SQL refers to the information that can store in a column, to be able to easily handle some of the data based on some criteria.

6.1 MySQL Data Types

6.2 PostgreSQL

PostgreSQL offers a lot of data types to store your information in a database.

6.3 Data Types in MSQL

Exact numeric types includes number without decimals: bigint, numeric, bit, smallint, decimal,
smallmoney, int, tinyint, money

For approximate numeric we have: float and real.

Date and time: date, datetimeoffset, datetime2, smalldatetime, datetime, time.

Character strings: char, varchar, ntext.

Binary Strings: binary, varbinary, image.

Other: cursor, rowversion, hierarchyid, uniqueidentifier, sql_variant, xml, Spatial Geometry Types, Spatial Geography Types, table.

7. SQL Operators

SQL Operators are reserved words or characters used mostly in WHERE statement to perform various operations such as: comparison, arithmetic, and logic.

7.1 MySQL operators

A list with all operators in MySQL you can find here.

7.2 PostgreSQL operators

7.3 MSQL Operators

8. SQL Functions

Built-in function are some utility that can help without needed to write some boilerplate.

8.1 MySQL Functions

Some of the most know SQL functions:

A list with all function you can find in the official documentation.

8.2 PostgreSQL functions

String functions: bit_length(string), char_length(string) or character_length(string), lower(string), octet_length(string), trim([leading | trailing | both] [characters] from string), upper(string)ascii(string), chr(int), concat(str “any” [, str “any” [, …] ]), md5(string). Some of the string functions presented in PostgreSQL. If you want to find more string functions you can find here.

Binary String functions: btrim(string bytea, bytes bytea), decode(string text, format text), encode(data bytea, format text), get_bit(string, offset), get_byte(string, offset), length(string), set_bit(string, offset, newvalue), set_byte(string, offset, newvalue).

Data type formatting functions: to_char(timestamp, text), to_char(interval, text), to_char(int, text), to_char(double precision, text), to_char(numeric, text), to_date(text, text), to_number(text, text), to_timestamp(text, text), to_timestamp(double precision).

Date/Time functions: age(timestamp, timestamp), age(timestamp), clock_timestamp(), extract(field from timestamp), now(), timeofday() and others. The full list you can find here.

The list with all supported functions you can find in the official documentation.

8.3 MSQL Functions

To find system functions in MSQL you can go in your SQL Server application login on a database, go to Object Explorer -> Databases -> <your_db_name> -> Programmability -> Functions -> System Functions. There you can find a list of built-in functions.

We can mentioned few usual functions:

String functions: ASCII, CHAR, CHARINDEX, CONCAT, LOWER, LTRIM, NCHAR. A list will all string you can find in the official documentation.

Math SQL functions: ABS, ACOS, ASIN, LOG, POWER, RAND, ROUND, SQRT.

There are also other type of function like Conversion Functions, JSON Functions, Security Functions, System Functions. All supported functions in MSQL you can find here.

9. Wildcard characters in SQL

Wildcards in SQL is a reserved instruction where you can match some records based on some criteria that you define.

9.1 Wildcards/Pattern Matching in all SQL databases

MySQL provides build-in functions to match the records from DB based on some regex pattern used by UNIX in vi, grep, or sed.

Types of pattern matching: 

SELECT * FROM Users WHERE username LIKE 'j%' -- all users that starts with "j"
SELECT * FROM Users WHERE username LIKE '%ch' -- all users that ends with "ch"
SELECT * FROM Users WHERE username LIKE 'j%o' -- all users that start with "j" and ends with "o"
SELECT * FROM Users WHERE username LIKE '____' -- all users where username has exactly 4 letters
SELECT * FROM Users WHERE username NOT LIKE 'j%'  -- all users where username does not start with "j"

9.2 Special functions for patter matching in MySQL

MySQL provided one additional pattern matching function: REGEXP_LIKE() and two others operators: REGEXP and RLIKE. 

SELECT * FROM Users WHERE username REGEXP_LIKE(username, '^j') --match all users that start with "j"
SELECT * FROM Users WHERE REGEXP_LIKE(username, 'n') --match all users that contains "n"
SELECT * FROM Users WHERE REGEXP_LIKE(username, 'on$'); -- match all users that ends with "on"

9.3 Special functions for pattern matching in PostgreSQL

In terms of pattern matching, PostgreSQL have a powerful engine to match values based on some patterns. One important operator to mention is SIMILAR TO and NOT SIMILAR TO that supports regex expression: pattern [ESCAPE escape-character].

9.4 Additional pattern matching in MSQL Server

MSQL Server unlike the previous databases supports regex matching inside the LIKE operator. 

SELECT * FROM Users WHERE username LIKE 'j[a-z]' -- match all users that starts with j having second letter in the range of a - z.

10. SQL Keys

SQL Keys are a combination of multiple fields between one or more tables that will be used to retrieve records/data from the table according to a condition. These are used in the relationship between tables or views.

10.1 Keys in MySQL

10.2 Keys in PostgreSQL

For more details about each of the keys you can find in the official documentation.

10.3 Keys in MSQL

MSQL supports the same constraints as PostgreSQL.

11. SQL Joins

The concept of Join means a relationship for more than two tables to retrieve data. It is useful for the tables where the data are mapped in multiple tables through a foreign key and you want to gather the data together with that constraint. These used in a SELECT statement.

11.1 Type of SQL Joins

In SQL there are 7 types of JOINS that you have to keep in mind. 

SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.ID = B.ID
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON A.ID = B.ID
SELECT * FROM TABLEA A INNER JOIN TABLEB B ON A.ID = B.ID
SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.ID = B.ID WHERE A.ID = B.ID WHERE B.ID IS NULL
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON A.ID = B.ID WHERE A.ID IS NULL
SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.ID = B.ID
SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.ID = B.ID WHERE A.ID IS NULL OR B.ID IS NULL

12. Summary

If you manage to read the entire article, you will be familiar with all the SQL concepts that you need if you want to become a data science, data analyst.

In this article, you get familiar with what SQL is, the needs of SQL, and in which areas you will be able to apply the concepts that you learn. In the last of the chapters you will get familiar with the SQL Keywords, SQL data types, SQL Operators, SQL Functions, SQL Keys and SQL Joins, based on the most popular databases. With these concepts in mind further you can study by your own other concepts that you like.

Related Articles
↑
Exit mobile version