The Ultimate SQL Cheat Sheet: Mastering Relational Databases in One Place

List of content you will read in this article:

What is SQL Cheat Sheet?

A SQL Cheat Sheet is a reference guide for SQL commands. It covers the most commonly used SQL commands for basic query operations, such as SELECT, FROM, WHERE, ORDER BY, and GROUP BY. SQL Cheat Sheets are invaluable for coders, providing them with a go-to guide for quickly recalling SQL syntax. SQL Cheat Sheets can help SQL coders save time when coding SQL operations, allowing them to quickly and easily find the SQL syntax they need to solve their problems.

It also includes other useful commands like JOIN, UNION, and UPDATE, which are often used to manipulate data in complex ways. SQL is a powerful language that enables us to create, view, and update databases. SQL is short for Structured Query Language, a standardized language used by database management systems to interact with the data stored in the databases. SQL makes complex data manipulation processes simpler and faster, allowing users to access, manipulate and analyze data quickly. SQL is an indispensable resource for any developer or analyst who needs to work with data!

What are SQL’s Features?

SQL allows users to manipulate databases and the data in them. By using SQL, users can add any data to their database in the form of tables. The list below shows the most common functions of SQL.

Creating and deleting databases.
Creating, deleting, and manipulating tables in a database.
Selecting data from tables.
Inserting data into tables.
Updating the data in tables.
Deleting the data in tables.
Creating Views in a database.
Executing different aggregate functions.

SQL Cheat Sheet (Concepts)

The sections below teach different SQL concepts; each has a description and syntax. Let’s get started.

Installation

The first step is setting up a database management system server. The database management system that you install will have all the tools necessary for different operations through SQL. To install your preferred database, visit its official website and get the official version.

Tables

Any data you put in a database is organized as a table. Your database can also be a collection of tables where you can use each table for storing different kinds of data. And further on, link them together by relations.

Create Table

This command will create a table in your database. Take a look at the syntax below to learn how to use it.

CREATE TABLE [table_name](

ID INT NOT NULL,

Name varchar(25),

Phone varchar(12),

Class INT

);

To delete this table, use the drop table command like the example below.

DROP TABLE [table-name];

SQL Datatypes

SQL allows users different data types to handle different types of data. Read on through the next section to learn every data type in SQL.

String Datatypes

There are tens of string datatypes, each with its unique usage. Take a look at the section below.

CHAR (size): A fixed-length string that contains numbers, letters, or any special character. Length may vary from zero to 255.

VARCHAR (size): A variable-length string whose length varies from zero to 65535. Similar to the CHAR string type.

TEXT (size): Contains a string of 65536 bytes.

TINY TEXT: Contains a string of 255 characters.

MEDIUM TEXT: Contains a string of 16777215 characters.

LONG TEXT: Contains a string of 4294967295 characters.

BINARY (size): Similar to the CHAR() string type but instead stores binary byte strings.

VARBINARY (size): Similar to the VARCHAR() string type, but instead stores binary byte strings.

BLOB (size): Holds blobs of up to 65536 bytes.

TINYBLOB: It is used for any Large Binary Object, and its maximum size is 255 bytes.

MEDIUMBLOB: Holds blobs of up to 16777215 bytes in size.

LONGBLOB: Holds blobs of up to 4294967295 bytes in size.

ENUM (val1,val2,…): This string object can have only one possible value from a size list of at most 65536 values in any ENUM list. If you insert no value, a blank value will be inserted.

SET (val1,val2,…): This string object has 0 or more values from a list of values. Its maximum limit is 64 values.

Numeric Datatypes

The section below lists all the Numeric Datatypes in SQL along with their descriptions:

BIT (size): There is a bit-value type called BIT(size), which has a value between 1 and 6. The default value of this type is 1.

INT (size): An integer with a signed range of -2147483648 to 2147483647 and an unsigned range of 0 to 4294967295.

TINYINT (size): An integer that can be expressed as a signed value between -128 and 127 or as an unsigned value between 0 and 255, depending on the size.

SMALLINT (size): An integer in which the values range from -32768 to 32767 (signed range) and, in the unsigned range, between 0 and 65535.

MEDIUMINT (size): It contains values in a signed range between -8388608 and 8388607 and values between 0 and 16777215 (unsigned).

BIGINT (size): A BIGINT value ranges from 922337236854775808 to 922337236854775807 (signed). As for the values in the unsigned range, they are from 0 to 18446744073709551615.

BOOLEAN: In the case of BOOLEAN values, the value 0 is considered FALSE, and the value other than zero is considered TRUE.

FLOAT (p): This data type stores floating-point numbers. If the precision parameter is set between 0 and 24, the data type is FLOAT(); otherwise, the data type is DOUBLE().

DECIMAL (size,d): This value has several digits before a decimal place, and the size parameter sets them. However, the numbers after the decimal point are set by the d parameter. Its default values are 10 and d=10. Its max values are 65 and d=30.

Date & Time Datatypes

The Date/Time datatypes in SQL handle Date/Time operations effectively. Read the section below to learn the rules and descriptions associated with each date/time variable in SQL.

DATE: It stores dates in the format YYYY-MM-DD in which dates range from ‘1000-01-01’ to ‘9999-12-31’ and a range of 1000-1-01 to 9999-12-3.

TIME (fsp): Time is stored as hh:mm:ss and with a time range of ‘-838:59:59’ to ‘838:59:59’.

DATETIME (fsp): It stores a combination of timestamps and dates in YYYY-MM-DD format with values ranging from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

TIMESTAMP: In other words, it is a Unix Timestamp, which stores values relative to the Unix Epoch, essentially a Unix Timestamp with its values ranging from 1970-01-01 00:00:01 to 2038-01-09 03:14:0 UTC.

YEAR: A 4-digit number format is used to store values of years, with a range between -1901 and 2155.

SQL Commands

As mentioned, SQL commands help users communicate with databases to perform certain tasks. We have five types of SQL commands: DDL, DML, DCL, TCL, and DQL. Let’s see what they each do and what their syntaxes are.

Data Definition Language (DDL)

This language can change a table’s structure. This is done by adding, deleting, or altering a table’s content. Keep in mind that these changes are automatically and permanently applied. Some of its commands are:

Create

Creates a new table.

CREATE TABLE tablename (column1 datatype, column2 datatype, column3 datatype);

Alter

Alters a table by adding columns or rows to it.

ALTER TABLE tablename ADD columnname datatype;

Drop

Deletes a table’s structure and records the stored in the table.

DROP TABLE table_name;

Truncate

Deletes every row in the table to free up space.

TRUNCATE TABLE table_name;

Data Manipulation Language (DML)

As the name suggests, this language is used for manipulating databases. But, unlike DDL, its changes aren’t automatic or permanent. Some of its commands are:

Insert

Inserts your data in a table’s row.

INSERT INTO tablename (column1, column2, column3) VALUES (value1, ‘value2’, value3);

Update

Updates the values in your table’s column.

UPDATE table_nameSET some_column = some_valueWHERE some_column = some_value;

Delete

Deletes any number of rows in a table.

DELETE FROM table_nameWHERE some_column = some_value;

Data Control Language (DCL)

This language is used to grant or revoke any user’s access to the database. Some of its commands are:

Grant

Grants user access to the database.

GRANT SELECT, UPDATE ON TABLE_1 TO USER_1, USER_2;

Revoke

Revokes a user’s access to the database.

REVOKE SELECT, UPDATE ON TABLE_1 FROM USER_1, USER_2;

Transition Control Language (TCL)

TCL commands are used in conjunction with DML commands and are auto-committed. Some of its commands are:

Commit

Saves every transaction made on a database.

DELETE FROM table_name

WHERE row_name;

COMMIT;

Rollback

This will undo transactions that haven’t been saved.

DELETE FROM table_name

WHERE row_name;

ROLLBACK;

Save point

Takes the transaction back to a specific point without taking back the entire transaction entity.

SAVEPOINT SAVED;

DELETE FROM table_name

WHERE row_name;

ROLLBACK TO SAVED;

Data Query Language (DQL)

It fetches data from a database. The only command for it is:

Select

Retrieves certain data with some conditions. These conditions are described with the “where” clause.

SELECT column_name FROM table_name;

The Where clause: SELECT column_name(s)FROM table_nameWHERE column_name operator value;

SQL Functions

SQL servers have numerous built-in functions, and the section below teaches you all of them. Keep on reading to find out more.

SQL Server String Functions

ASCII: ASCII values will be returned for a specific character.

CHAR: The character will be returned according to the ASCII code.

CONCAT: 2 strings will be concatenated together.

SOUNDEX: The similarities of 2 strings will be returned as a 4-character code.

DIFFERENCE: 2 SOUNDEX values will be compared and returned to the result as integers.

SUBSTRING: Substrings will be extracted from a given string.

TRIM: Leading and trailing whitespaces will be removed from a string.

UPPER: A string will be converted to upper-case.

SQL Server Numeric Functions

ABS: Returns a number’s absolute value.

ASIN: Returns a number’s arc sinc value.

AVG: Returns an expression’s absolute value.

COUNT: Counts the record number that’s returned by a SELECT query.

EXP: Returns the e that has been raised to the power of a number.

FLOOR: Returns the number to the greatest integer.

RAND: Returns any random number.

SIGN: Returns the sign of any number.

SQRT: Returns any number’s square root.

SUM: Returns the sum of any group of values.

SQL Server Date Functions

CURRENT_TIMESTAMP: Returns the current date/time.

DATEADD: Adds the date’s date/time interval and returns the new date.

DATENAME: Returns a specific part of dates as strings.

DATEPART: Returns a specific part of dates as integers.

DAY: Returns the day of a specific date.

GETDATE: Returns today’s date from your database.

Final Word

In conclusion, a SQL cheat sheet can be a great way to help you quickly recall all the information you need to master your database work. Whether you are just getting started or are looking for an edge to help you stand out from the crowd, having a cheat sheet handy can make all the difference. With a little practice, you can become an SQL master in no time.

SQL, or Structured Data Query language, is used to communicate with databases and get any data interpretation you want.
SQL is used to store, manipulate, and retrieve data from databases.
Databases are collections of small units of data that are arranged systematically.
SQL’s features include creating databases, creating tables in databases, and executing different aggregate functions.

People also read:

Category: Tutorials