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
- Numeric Data Types
- Date and Time Data Types
- String Data Types
- Spatial Data Types
- The JSON Data Type
- Data Type Default Values
- Data Type Storage Requirements
6.2 PostgreSQL
PostgreSQL offers a lot of data types to store your information in a database.
- Numeric Types
- Monetary Types
- Character Types
- Binary Data Types
- Date/Time Types
- Boolean Type
- Enumerated Types
- Geometric Types
- Network Address Types
- Bit String Types
- Text Search Types
- UUID Type
- XML Type
- JSON Types
- Arrays
- Composite Types
- Range Types
- Object Identifier Types
- pg_lsn Type
- Pseudo-Types
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
- Logic operator
- Comparison operators
- Modulo operator
- Arithmetic operator
A list with all operators in MySQL you can find here.
7.2 PostgreSQL operators
- Logical Operators
- Comparison Operators
- Mathematical Functions and Operators
- String Functions and Operators
- Binary String Functions and Operators
- Bit String Functions and Operators
- Date/Time Functions and Operators
- Geometric Functions and Operators
- Network Address Functions and Operators
- Text Search Functions and Operators
- Array Functions and Operators
- Row and Array Comparisons
7.3 MSQL Operators
- Arithmetic operators
- Relational operators
- Assignment operator
- Scope resolution operator
- Bitwise operators
- Comparison operators
- String Concatenation operator
- Compound operators
- Unary operators
- Logical operators
- EXCEPT and INTERSECT, UNION
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:
ABS()ACOS()ADDDATE()ADDTIME()AES_DECRYPT()AES_ENCRYPT()ANY_VALUE()ASCII()ASIN()ATAN()ATAN2(),ATAN()AVG()BENCHMARK()CAST()CONCAT()COS()COUNT()CURTIME()DATE()
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:
- Match everything after a letter:
SELECT * FROM Users WHERE username LIKE 'j%' -- all users that starts with "j"
- Match everything in the beginning and ends with a given letter:
SELECT * FROM Users WHERE username LIKE '%ch' -- all users that ends with "ch"
- Everything within a range of letters, that starts with and ends with:
SELECT * FROM Users WHERE username LIKE 'j%o' -- all users that start with "j" and ends with "o"
- Expression that match exactly a number of letters:
SELECT * FROM Users WHERE username LIKE '____' -- all users where username has exactly 4 letters
- Expression that don’t match a certain letter:
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
- Partitioning keys – partitioning by keys is similar to partitioning by hash data column in a table. In case no column name is specified as partitioning key, the table’s primary key will be used.
- Primary keys – primary key may be one field or a combination of multiple fields in a table which. Usually could be the ID of each record. The primary keys can be for multiple columns. These columns will have an associated index, that will be used to retrieve data faster.
- Unique keys – can be one field or a combination of multiple fields that will ensure the value that will be store will be unique.
- Foreign keys – foreign keys are references between table which will help to keep the data consist. The foreign key relationship is constructed by a parent table that hold the initial column value and a child table with the column value. Foreign keys are defined in the child table.
10.2 Keys in PostgreSQL
- Constraints key – which will help you to put a validation on a column only to accept certain values
- Not null values
- Unique key
- Primary keys
- Foreign keys
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.
- Left Join – will get all the values that are in the left table and common values from both tables.
SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.ID = B.ID
- Right Join – will get all the values that are on the right table and common values from the left table.
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON A.ID = B.ID
- Inner Join – will get you the values that are common between both two tables.
SELECT * FROM TABLEA A INNER JOIN TABLEB B ON A.ID = B.ID
- LEFT JOIN with values that exist on the left table except common values between A and B.
SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.ID = B.ID WHERE A.ID = B.ID WHERE B.ID IS NULL
- RIGHT JOIN with values that exist on the right table except common values between A and B.
SELECT * FROM TABLEA A RIGHT JOIN TABLEB B ON A.ID = B.ID WHERE A.ID IS NULL
- Full JOIN – will get all the values between both two tables.
SELECT * FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.ID = B.ID
- Full Outer JOIN that will get all the values between both two table except the common part.
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.
