SQL Queries Cheat Sheet

We all know that searching for a needle in a haystack is a very difficult or almost impossible task. We can figuratively compare it with searching for data in a large database or data warehouse. But with the help of SQL (Structured Query Language), you can do that with ease. Though having a strong grasp of all the SQL queries is important, a practical SQL Queries Cheat Sheet can be beneficial, especially if you’re a beginner.

We have split our SQL Queries Cheatsheet into different categories to help you easily locate the command you are looking for. We have categorized them based on the most common use cases such as creating a table, inserting a value into the table, and so on as mentioned below.

SQL Queries Cheat Sheet

• Database
• Create a Table
• Insert values into the Table
• Get Data from a Single Table
• Get Data from Multiple Tables
• Filtering the Data from the Table
• Case Expression
• Drop

Database

CREATE Database

Creating a new database is one of the most basic functions one must know and you can do so by using the CREATE DATABASE keyword. We have specified the syntax you’ll need and given an SQL command as well. Likewise, we would have followed a similar procedure throughout our SQL Queries Cheat Sheet to make it easier for you.

SYNTAX:

CREATE DATABASE database_name

SQL Query:

CREATE DATABASE Codoid;

Here ‘Codoid’ is the database name

USE DATABASE

The USE keyword is used when there are multiple databases present and you want to use a specific database.

SYNTAX:

USE database_name;

SQL Query:

USE Codoid;

Once again, ‘Codoid’ is the database name here.

Next up in our SQL Queries Cheat Sheet, we will be seeing how to create a table.

CREATE A TABLE

You can use the CREATE TABLE keyword to create a new table as shown in the below syntax. You’ll have to specify the datatype for the columns based on your needs. Once we have created a table, we’ll see how we can display it as well.

SYNTAX:

CREATE TABLE table_name 
(
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

CONSTRAINTS

Using CONSTRAINTS, you will be able to define the rules for the columns while creating or updating the table. Though there are different types of CONSTRAINTS, we will be seeing only the most used ones in our SQL Queries Cheat Sheet. We have specified the CONSTRAINTS and briefly explained their purpose as well.

• NOT NULL – It doesn’t accept the NULL values in the column
• UNIQUE – It will accept only NULL values and not DUPLICATE values
• PRIMARY KEY – It doesn’t accept both NULL and DUPLICATE values
• FOREIGN KEY – It will create a link between the two tables. The FOREIGN KEY value is usually the primary key of the foreign table.
• DEFAULT – It will set a default value for a column if we don’t provide any particular values.

Now let’s create two new tables (Project and Employee) using the CREATE TABLE and CONSTRAINTS

SQL Query:

CREATE TABLE Project 
(
ID int primary key, name varchar(100)
);

So in the above example, we are creating a table by the name ‘Project’ in the Codoid database. It has two columns (ID and Name). We have defined the ID column to have an integer data type and it is also set as the primary key. So the ID column must be unique and shouldn’t be null.

And the name column has a varchar data type with a maximum length of 100 characters.

SQL Query:

CREATE TABLE Employee (
    Name Varchar(100) NOT NULL,
    Contact bigint UNIQUE,
    City varchar(100) DEFAULT 'Boston',
    Id int,
    ProjectId int,
    PRIMARY KEY (Id),
    FOREIGN KEY (ProjectId) REFERENCES Project(ID)
);

Same as in the prior example, we are creating another table in the Codoid database and assigning constraints. So the Employee table has 5 columns: Name, Contact, City, Id, and ProjectId.

• Name – Varchar type field with a max length of 100 characters and shouldn’t be left blank.
• Contact – bigint type where each value must be unique.
• City – Varchar type that can store 100 characters. If it is left blank, it will automatically assign “Boston” as the default value.
• Id – It is int type and is also the primary key for the table.
• ProjectId – int type and is a foreign key that references the ID column from the Project table we created earlier.

DESC

Now that we have created the table, you can display the table structure by using the DESC keyword.

SYNTAX:

DESC table_name;

SQL Query:

DESC Employee;

Here, ‘Employee’ is the name of the table.

Output:

INSERT VALUES INTO THE TABLE

Next up in our SQL Queries Cheat Sheet, we’ll be focusing on how to insert values in the table we have created. You can insert a single value or multiple values into your table based on your requirements.

INSERT SINGLE VALUE

We have given an example where we insert the single value ‘001’ into the Project table we had created earlier.

SYNTAX

INSERT INTO table_name VALUE ( value1, value2,…..);

SQL Query:

INSERT INTO Project VALUE (001, ‘ETL Testing’);
INSERT MULTIPLE VALUES

If you wish to insert multiple values into the table, you can do so by specifying the list of columns you will be inserting the data into and then mentioning the multiple values as shown below.

SYNTAX:

INSERT INTO table_name (column_list) VALUES (value_list_1),(value_list_2),….);

SQL Query:

INSERT INTO Employee (Name, Contact, City, Id, ProjectId)
VALUES
     ('Robert', '8594230688','Willisburg',2201,001 ),
     ('Peter', '6155805664','Smithville',2202,001 );

SELECT

We have seen how we can insert the values into the table, now let’s see how to display the values which we inserted into the tables by using the SELECT.

SYNTAX:

SELECT * FROM table_name

To print all the columns from the table.

SQL Query:

SELECT * FROM Employee

SYNTAX:

SELECT column_name1, column_name2,... FROM table_name

To print selective columns from the table

SQL Query:

SELECT id,contact,city FROM Employee

GET DATA FROM A SINGLE TABLE

There will definitely be scenarios where you’ll have to get data from a table. And so we will now be covering the different ways you can get data from a single table. There are ways to get data from multiple tables as well and we will be covering that next in our SQL Queries Cheat Sheet.

When it comes to getting data from a single table, you can either display all columns value from the table or choose which column values have to be displayed.

All Columns

So previously in our SQL Queries Cheat Sheet, we had created a table by the name ‘Employee’. It had details such as Name, ID, Contact, City, & ProjectID. So let’s see how to get all this data now.

SYNTAX:

SELECT * FROM table_name;

SQL Query:

SELECT * FROM Employee;

Output:

Specific Column Values

If at all you need only one of the column values, you can use the below syntax to get it. In our example, we will be getting only the names of the employees from the table.

SYNTAX:

SELECT column_name FROM table_name

SQL Query:

SELECT Name FROM Employee;

This query will display only Name column values.

GET DATA FROM MULTIPLE TABLES

Based on your needs, you might encounter scenarios where you’ll have to get data from multiple tables. We have already created two tables by the names ‘Project’ and ‘Employee’ in our SQL Queries cheat sheet.

So let’s use these tables in our examples to illustrate the different SQL Queries listed below.

• Joins
  • Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Cross Join
• UNION
• UNION ALL
• Content

JOINS

As the name suggests, these SQL queries can be used to get data from multiple tables. As mentioned above, there are multiple types of Joins. Let’s take a look at them one by one in our SQL Queries Cheat Sheet.

INNER JOIN

Just JOIN or INNER JOIN can be used to get or display records that are present in both tables. In our example, we will be getting the names from one table and the project IDs from another. We have also shown the output along with the Syntax and SQL queries to make it easier for you to understand.

SYNTAX:

SELECT column_name1, column_name2 FROM table1
INNER JOIN table2 ON table1.column_name=table2.column_name
SQL Queries:
 SELECT Project.Name, Employee.Name FROM Employee
 INNER JOIN Project ON Project.Id=Employee.ProjectId

Output

LEFT JOIN

LEFT JOIN can be used to display all the rows from the LEFT table with corresponding rows from the RIGHT table. If there’s no matching row, NULLs will be returned as values from the RIGHT table.

SQL Query:

SELECT Project.Name, Employee.Name FROM Employee
 LEFT JOIN Project ON Project.Id=Employee.ProjectId

Output:

RIGHT JOIN

Likewise, RIGHT JOIN can be used to display all rows from the RIGHT table with corresponding rows from the LEFT table. Same as before, NULLs values will be returned if there are no matching rows on the LEFT table.

SQL Query:

SELECT Project.Name, Employee.Name FROM Employee
 RIGHT JOIN Project ON Project.Id=Employee.ProjectId

Output:

FULL JOIN

If you wish to get all the data from both the left & right tables, you can use the FULL JOIN SQL query. If there are no matching rows on either side, it will return NULL values.

SQL Query:

SELECT Project.Name, Employee.Name FROM Employee
FULL JOIN Project ON Project.Id=Employee.ProjetctId

Output:

Note: FULL JOIN is not accessible in MYSQL

CROSS JOIN

This join is different from the pattern of join queries we have seen earlier in our SQL Queries cheat sheet. It can be used to generate a paired combination of each row of the first table with each row of the second table. But it is different as it will eliminate null values from both tables.

SQL Query:

SELECT Project.Name AS Project_name, Employee.Name AS Employee_name FROM Project

CROSS JOIN Employee

Output:

UNION

Union query can be used to combine the result set of two or more SELECT statements without any data duplications in the result set. In our example, we are selecting the names from both the Project & Employee tables.

SQL Query:

SELECT NAME FROM PROJECT 
UNION 
SELECT NAME FROM EMPLOYEE;

Output:

We have gotten an output without any duplicates.

UNION ALL

If there is a scenario where you don’t want the duplicates to be removed when using the UNION query, you can make use of this query. So we are selecting the same data from the same tables as before.

SQL Query:

SELECT NAME FROM PROJECT 
UNION ALL
SELECT NAME FROM EMPLOYEE;

Output:

But we can see that the ‘ETL Testing’ duplicate hasn’t been removed.

FILTERING DATA FROM THE TABLE

We can filter the data from a SQL query by adding CLAUSE, OPERATORS, and DISTINCT to the statement. There are different types of filters that you can use to get customized results based on your needs. That is what we’re going to look at now in our SQL Queries check sheet.

CLAUSES

SQL clauses are part of SQL statements that contain an expression, predicate, or query. We will be seeing an example for each of the 5 clauses we have listed below as they have their own use case.

• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT

WHERE

It is used to filter the rows in the result set based on a specific condition. The specific condition is usually an expression that involves one or more columns from the table being queried.
For example, we are going to filter data using the name in the Employee table. We have taken the name ‘Tony’ in the below example.

SQL Query:

SELECT * FROM EMPLOYEE
WHERE NAME='Tony'

Output:

GROUP BY

It is used to group rows in the result set according to specified columns or expressions. The GROUP BY clause is usually used along with aggregate functions such as SUM, AVG, and COUNT.
So in the below example in our SQL Queries cheat sheet, we have grouped the cities to identify the number of times each city is present in the same Employee table.

SQL Query:

SELECT CITY,COUNT(*) FROM EMPLOYEE 
GROUP BY CITY;

Output:

HAVING

It is usually used alongside the GROUP BY clause to filter groups in the result set based on specific conditions. It works in a way that is similar to the WHERE clause with the difference being that it operates on the grouped data.

So in the below example, we have grouped the results data by cities that have a count that is greater than 1.

SQL Query:

SELECT CITY,COUNT(*) as COUNT FROM EMPLOYEE 
GROUP BY CITY HAVING COUNT>1;

Output:

ORDER BY

It is easy to guess the functionality of this clause with just the name. By default, the result set is sorted in Ascending order. And this clause can be used to sort rows in the result set in ascending or descending order. It also supports multiple sorting criteria if you have more complex sorting needs.
So we’re going to change the sorting order of the cities from ascending to descending in the Employee table.

SQL Query:

SELECT CITY FROM EMPLOYEE 
ORDER BY CITY DESC

Output:

LIMIT

The last clause we are going to see in our SQL Queries cheat sheet is the LIMIT clause. You could be working with large amounts of data and may not want a very long list of data in your results. So you can limit the number of rows returned from a query by using the LIMIT clause.

So we have limited the number of rows returned from our query to 4 in the below example.

SQL Query:

SELECT * FROM EMPLOYEE 
LIMIT 4;

Output:

OPERATORS

An operator is a symbol or keyword that is used to perform an action in SQL. Operators can be used to search, combine, calculate, or compare data in a database.

The 4 different types of operators we are going to see in our SQL Queries cheat sheet are,

• LIKE
• ARITHMETIC
• COMPARISON
• LOGICAL

LIKE OPERATORS

The LIKE operator allows you to perform wildcard searches using the two major wildcards, the percentage ( % ) and underscore( _ ). The placement of the (%) symbol is particularly important when you define the pattern you want to search for.

SYNTAX:

SELECT column_name1, column_name2, …
FROM table_name
WHERE column_name LIKE pattern

SQL Query:

If you want to find all words that start with the letter ‘P’, then you’ll have to specify the % symbol after your target letter as shown below.

SELECT NAME FROM EMPLOYEE
WHERE NAME LIKE 'P%'; 

Output:

It has found the words starting with the letter ‘P’.

SQL Query:

Likewise, if you wish to find words that are ending with a particular letter, you’ll just have to shift the position of the % symbol to before the targetted letter as shown below.

SELECT NAME FROM PROJECT
WHERE NAME LIKE '%g';

Output:

It has found the words ending with the letter ‘g’.

SQL Query:

But what if you have to find an exact word? You can enclose the targetted word within two % symbols as shown below. We’re going to search for data that have the word ‘Testing’ in any position.

SELECT NAME FROM PROJECT
WHERE NAME LIKE '%Testing%';

Output:

Data with the word ‘Testing’ has been found.

ARITHMETIC OPERATORS

Arithmetic operators are addition( + ), subtraction ( – ), multiplication ( * ), division ( / ), and modulus( % ). They can be used to perform arithmetic calculations. We can also use these operators in WHERE clauses.

SQL Query:

SELECT 1+2 as ADDITION, 4-2 as SUBTRACTION, 4*2 as MULTIPLICATION, 
4/2 as DIVISION, 8%3 as MODULUS

Output:

COMPARISON OPERATOR

It is used to compare two different values or expressions. These operators include ‘=’, ‘!=’, ‘ <’, ‘>’, ‘<=’, ‘>=’ and ‘IN’. Now let’s see a sample for each in our SQL Queries cheat sheet.

EQUAL TO ( = )

In the below example, we are going to take a list of employees whose salary is 1000.

SQL Query:

SELECT NAME, SALARY
FROM EMPLOYEE WHERE SALARY=1000

Output:

NOT EQUAL TO ( != )

Similarly, we can also get the list of employees whose salaries are not 1000 by using this operator.

SQL Query:

SELECT NAME, SALARY
FROM EMPLOYEE WHERE SALARY!=1000

Output:

GREATER THAN ( > )

It is one of the two comparison operators that you can to get data that is greater than a defined value. In this case, it is above 1001.

SQL Query:

SELECT NAME, SALARY
FROM EMPLOYEE WHERE SALARY>1001

Output:

LESSER THAN ( < )

In the below example, we will be getting a list of the employees whose salary is below 2000.

SQL Query:

SELECT NAME, SALARY
FROM EMPLOYEE WHERE SALARY<2000

Output:

IN OPERATOR

The IN operator allows you to specify multiple values in a WHERE clause for a single condition. It tests whether a specified value matches any value in a list of values. So in this example, we will be getting a list of employees whose names are Robert & Tony.

SQL Query:

SELECT * FROM EMPLOYEE 
WHERE NAME IN ('Robert','Tony')

Output:

LOGICAL OPERATORS

AND, OR, NOT, and BETWEEN are the logical operators that are used along Boolean operators to compare two values in a logical statement.

AND

If you want to check two conditions are true, you can use this logical operator as shown in the example. Since there could be multiple people with the same name, we are going to find a person named ‘Robert’ with 2201 as the ID.

SQL Query:

SELECT * FROM EMPLOYEE 
WHERE NAME='Robert' AND id =2201

Output:

OR

When it comes to OR, it’ll extract data that satisfy any one of the two mentioned conditions. In our example, we will be searching for a particular person named ‘Robert’ or with 2200 as their ID. Though Robert’s ID isn’t 2200, it will string bring that data as the name is correct.

SQL Query:

SELECT * FROM EMPLOYEE 
WHERE NAME='Robert' AND id =2200

Output:

NOT

It can be used to check if the condition is not satisfying. So if you want everyone’s information other than Robert’s, you can use this operator as shown below in our SQL Queries cheat sheet.

SQL Query:

SELECT * FROM EMPLOYEE 
WHERE NOT NAME='Robert'

Output:

BETWEEN

You can even assign upper and lower limits to extract data that are in that range. So we will be getting the list of employees who have their IDs between 22202 and 2204.

SQL Query:

SELECT * FROM EMPLOYEE 
WHERE ID BETWEEN 2202 AND 2204

Output:

DISTINCT

It is used to return unique records in a result set by eliminating the duplicate values in a specific column or expression list to return only one instance of each distinct row. There are multiple cities mentioned in our Employee table and there are a few duplications as well. So we will be using this SQL query to get only the unique cities from that table.

SQL Query:

SELECT DISTINCT CITY FROM EMPLOYEE

Output:

CASE EXPRESSION

It is an expression that works the same as an If Then Else loop. It will evaluate the condition one by one to find the first out which is the first condition to be true and then returns a corresponding result.

So we will be identifying the cities of the employees and printing an appropriate statement.

SYNTAX

CASE
     WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      ELSE result3
END 

SQL Query:

SELECT NAME, CITY,
CASE
 WHEN CITY='Willisburg' THEN 'He belongs to Willisburg'
    WHEN CITY='Boston' THEN 'He belongs to Boston'
    ELSE 'He does not belong to Willisburg and Boston'
end as Belongs_To
FROM employee;

So using the above SQL statement, we are selecting the name, city, and case statement from the Employee table that we have. The function of the case statement is to check whether the employee belongs to Willisburg or Boston and then output a string that reflects that. If an employee doesn’t belong to any of the two cities, it will output a message saying that the person does not belong to either as shown below.

Output:

DROP

The DROP keyword is used to remove the data from a database table or object. It can also be used to delete entire tables, individual rows, or specific columns from a table. So let’s take a look at the queries you’ll need to perform these actions in our SQL Queries cheat sheet.

DROP DATABASE

It is used to delete an entire database.

SYNTAX:

DROP DATABASE database name;

DROP TABLE

It is used to delete a particular table.

SYNTAX:

DROP TABLE table name;

DROP COLUMN

It is used to delete a particular column from a particular table.

SYNTAX:

ALTER TABLE table name
DROP COLUMN column_name;

Conclusion

We hope you are now clear on what the important SQL queries are and how you can use them after reading our SQL Queries cheat sheet. Being a leading Data Analytics testing company, we are sure that these SQL Queries cover all the important ones that you will definitely need to know. We will also be publishing informative blogs such as this, so make sure you subscribe to our newsletter to never miss out on any of our updates.