Listen to this blog
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
- 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
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.
CREATE DATABASE database_name
CREATE DATABASE Codoid;
Here ‘Codoid’ is the database name
The USE keyword is used when there are multiple databases present and you want to use a specific database.
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.
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
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
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.
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.
Now that we have created the table, you can display the table structure by using the DESC keyword.
Here, ‘Employee’ is the name of the table.
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.
INSERT INTO table_name VALUE ( value1, value2,…..);
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.
INSERT INTO table_name (column_list) VALUES (value_list_1),(value_list_2),….);
INSERT INTO Employee (Name, Contact, City, Id, ProjectId) VALUES ('Robert', '8594230688','Willisburg',2201,001 ), ('Peter', '6155805664','Smithville',2202,001 );
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.
SELECT * FROM table_name
To print all the columns from the table.
SELECT * FROM Employee
SELECT column_name1, column_name2,... FROM table_name
To print selective columns from the table
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.
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.
SELECT * FROM table_name;
SELECT * FROM Employee;
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.
SELECT column_name FROM table_name
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.
- Inner Join
- Left Join
- Right Join
- Full Join
- Cross Join
- UNION ALL
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.
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.
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
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.
SELECT Project.Name, Employee.Name FROM Employee LEFT JOIN Project ON Project.Id=Employee.ProjectId
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.
SELECT Project.Name, Employee.Name FROM Employee RIGHT JOIN Project ON Project.Id=Employee.ProjectId
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.
SELECT Project.Name, Employee.Name FROM Employee FULL JOIN Project ON Project.Id=Employee.ProjetctId
Note: FULL JOIN is not accessible in MYSQL
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.
SELECT Project.Name AS Project_name, Employee.Name AS Employee_name FROM Project
CROSS JOIN Employee
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.
SELECT NAME FROM PROJECT UNION SELECT NAME FROM EMPLOYEE;
We have gotten an output without any duplicates.
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.
SELECT NAME FROM PROJECT UNION ALL SELECT NAME FROM EMPLOYEE;
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.
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.
- GROUP BY
- ORDER BY
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.
SELECT * FROM EMPLOYEE WHERE NAME='Tony'
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.
SELECT CITY,COUNT(*) FROM EMPLOYEE GROUP BY CITY;
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.
SELECT CITY,COUNT(*) as COUNT FROM EMPLOYEE GROUP BY CITY HAVING COUNT>1;
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.
SELECT CITY FROM EMPLOYEE ORDER BY CITY DESC
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.
SELECT * FROM EMPLOYEE LIMIT 4;
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,
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.
SELECT column_name1, column_name2, … FROM table_name WHERE column_name LIKE pattern
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%';
It has found the words starting with the letter ‘P’.
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';
It has found the words ending with the letter ‘g’.
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%';
Data with the word ‘Testing’ has been found.
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.
SELECT 1+2 as ADDITION, 4-2 as SUBTRACTION, 4*2 as MULTIPLICATION, 4/2 as DIVISION, 8%3 as MODULUS
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.
SELECT NAME, SALARY FROM EMPLOYEE WHERE SALARY=1000
NOT EQUAL TO ( != )
Similarly, we can also get the list of employees whose salaries are not 1000 by using this operator.
SELECT NAME, SALARY FROM EMPLOYEE WHERE SALARY!=1000
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.
SELECT NAME, SALARY FROM EMPLOYEE WHERE SALARY>1001
LESSER THAN ( < )
In the below example, we will be getting a list of the employees whose salary is below 2000.
SELECT NAME, SALARY FROM EMPLOYEE WHERE SALARY<2000
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.
SELECT * FROM EMPLOYEE WHERE NAME IN ('Robert','Tony')
AND, OR, NOT, and BETWEEN are the logical operators that are used along Boolean operators to compare two values in a logical statement.
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.
SELECT * FROM EMPLOYEE WHERE NAME='Robert' AND id =2201
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.
SELECT * FROM EMPLOYEE WHERE NAME='Robert' AND id =2200
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.
SELECT * FROM EMPLOYEE WHERE NOT NAME='Robert'
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.
SELECT * FROM EMPLOYEE WHERE ID BETWEEN 2202 AND 2204
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.
SELECT DISTINCT CITY FROM EMPLOYEE
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.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END
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.
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.
It is used to delete an entire database.
DROP DATABASE database name;
It is used to delete a particular table.
DROP TABLE table name;
It is used to delete a particular column from a particular table.
ALTER TABLE table name DROP COLUMN column_name;
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.