In this SQL cheat sheet, we’ll look at sample SQL queries that can help you learn basic T-SQL queries as quickly as possible.
Introduction
Transact-SQL (T-SQL) is an extension of the Structured Query Language (SQL) that is used to manipulate and fetch data from the Microsoft SQL Server. Despite the clear and rigid specifications of standard SQL, it does allow database vendors to add their extensions to set them apart from other products. Moving from this idea, T-SQL had been developed by Microsoft to program SQL Server and it has a common widely-usage. This SQL cheat will allow you to learn SQL queries quickly and simply.
Pre-requisites
To practice the examples on this SQL Cheat Sheet, you can use the following query to create the tables and also populate them with some synthetic data. Besides this option, you can use this SQL Fiddle link, to practice exercises online.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE TABLE Online_Customers (Customer_Id INT PRIMARY KEY IDENTITY(1, 1), Customer_Name VARCHAR(100), Customer_City VARCHAR(100), Customer_Mail VARCHAR(100)) GO CREATE TABLE Orders (Order_Id INT PRIMARY KEY IDENTITY(1, 1), Customer_Id INT, Order_Total float, Discount_Rate float, Order_Date DATETIME) GO CREATE TABLE Sales (Sales_Id INT PRIMARY KEY IDENTITY(1, 1), Order_Id INT, Sales_Total FLOAT) GO INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Salvador', N'Philadelphia', N'tyiptqo.wethls@chttw.org') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Gilbert', N'San Diego', N'rrvyy.wdumos@lklkj.org') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Ernest', N'New York', N'ymuea.pnxkukf@dwv.org') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Stella', N'Phoenix', N'xvsfzp.rjhtni@rdn.com') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Jorge', N'Los Angeles', N'oykbo.vlxopp@nmwhv.org') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Jerome', N'San Antonio', N'wkabc.ofmhetq@gtmh.co') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'Edward', N'Chicago', N'wguexiymy.nnbdgpc@juc.co') INSERT INTO [dbo].Online_Customers(Customer_Name, Customer_City, Customer_Mail) VALUES(N'John', N'Chicago', N'jxkn.nnbdgpc@juc.co') GO INSERT INTO [dbo].Orders(Customer_Id, Order_Total, Discount_Rate, Order_Date) VALUES(3, 1910.64, 5.49, CAST('03-Dec-2019' AS DATETIME)) INSERT INTO [dbo].Orders(Customer_Id, Order_Total, Discount_Rate, Order_Date) VALUES(4, 150.89, 15.33, CAST('11-Jun-2019' AS DATETIME)) INSERT INTO [dbo].Orders(Customer_Id, Order_Total, Discount_Rate, Order_Date) VALUES(5, 912.55, 13.74, CAST('15-Sep-2019' AS DATETIME)) INSERT INTO [dbo].Orders(Customer_Id, Order_Total, Discount_Rate, Order_Date) VALUES(7, 418.24, 14.53, CAST('28-May-2019' AS DATETIME)) INSERT INTO [dbo].Orders(Customer_Id, Order_Total, Discount_Rate, Order_Date) VALUES(55, 512.55, 13.74, CAST('15-Jun-2019' AS DATETIME)) INSERT INTO [dbo].Orders(Customer_Id, Order_Total, Discount_Rate, Order_Date) VALUES(57, 118.24, 14.53, CAST('28-Dec-2019' AS DATETIME)) GO INSERT INTO [dbo].Sales(Order_Id, Sales_Total)VALUES(3, 370.95) INSERT INTO [dbo].Sales(Order_Id, Sales_Total)VALUES(4, 882.13) INSERT INTO [dbo].Sales(Order_Id, Sales_Total)VALUES(12, 370.95) INSERT INTO [dbo].Sales(Order_Id, Sales_Total)VALUES(13, 882.13) INSERT INTO [dbo].Sales(Order_Id, Sales_Total)VALUES(55, 170.95) INSERT INTO [dbo].Sales(Order_Id, Sales_Total)VALUES(57, 382.13) |
SQL Cheat Sheet
Command | Syntax | Description |
SELECT | SELECT col1,col2,col3,…,coln FROM Table_Name | Used to fetch data from a table |
TOP | SELECT TOP(1) col1,col2,col3,…,coln FROM Table_Name | Used to limit the number of the result set |
ORDER BY | SELECT col1,col2,col3,…,coln FROM Table_Name ORDER BY col1,col2,col3 …,coln | Used to sort result set according to specified columns in the ascending or descending order |
COUNT() | SELECT COUNT(col1) FROM Table_Name | Used to determine how many rows the result set returns. |
DISTINCT | SELECT DISTINCT col1 FROM Table_Name | Used to fetch distinct values of the specified column(s). |
INNER JOIN | SELECT col1,col2,col3,…,coln FROM Table_A INNER JOIN Table_B ON Table_A.col = Table_B.col | Used to fetch matched rows of TableA and TableB |
LEFT JOIN | SELECT col1,col2,col3,…,coln FROM Table_A LEFT JOIN Table_B ON Table_A.col = Table_B.col | Used to fetch all rows of TableA and matched rows from TableB |
RIGHT JOIN | SELECT col1,col2 FROM Table_A RIGHT JOIN Table_B ON Table_A.col = Table_B.col | Used to fetch all rows of TableB and matched rows from TableA |
WHERE | SELECT col1,col2,col3,…,coln FROM Table_Name WHERE col1=’col_value’ | Used to filter the result set of the table |
LIKE | SELECT col1,col2,col3,…,coln FROM Table_Name WHERE col1 LIKE ‘col_value%’ | Used to filter wildcard patterns. |
IN | SELECT col1,col2,col3,…,coln FROM Table_Name WHERE col1 IN (‘col_value1′,’col_value2′,’col_value3′,…,’col_valuen’) | Used to filter multiple values for a column. |
AND | SELECT col1,col2,col3,…,coln FROM Table_Name WHERE col1 = col_value’ AND col2=’col_value’ | Used to combine two or more conditions in filtering. The matched row(s) must satisfy all conditions |
OR | SELECT col1,col2,col3,…,coln FROM Table_Name WHERE col1 = col_value’ OR col2=’col_value’ | Used to combine two or more conditions in filtering. The matched row(s) is enough to satisfy one of the conditions. |
BETWEEN | SELECT col1,col2,col3,…,coln FROM Table_Name WHERE col1 BETWEEN ‘col_value1’ AND ‘col_value2’ | Used to filter the result set according to the given result set |
SQL SELECT Statement
The SELECT statement is used to fetch data from any database table. The syntax of the SQL SELECT statement is:
|
1 2 3 |
SELECT Column_Name_1, Column_Name_2, ....., Column_Name_N FROM Table_Name; |
In this syntax the Column_Name_1, Column_Name_2, ….., and Column_Name_N indicate the column name of the tables and we should separate them with a comma (;)after placing the FROM clause we need to write the table name which we want to fetch data.
|
1 2 3 |
SELECT CustomerName, CustomerCity FROM Online_Customers |
If we want to retrieve all columns of the table we can use an asterisk sign (*) in the SELECT statements.
|
1 2 3 |
SELECT * FROM Online_Customers |
Tip: Although using the asterisk sign (*) is included in our SQL Cheat Sheet, we recommend not using it as much as possible as, because it may cause performance problems.
SQL Aliases
Aliases are the temporary names that we can give to table or column names. So that, we can make them more readable and understandable. Using the aliases does not make any changes neither to the original table name or its column names.
|
1 2 3 4 5 6 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer |
As you can see, the column names of the result set have been changed after using aliases in the above query because of the alias usage.
SQL TOP Statement
The SELECT TOP statement is used to limit the row numbers of the query resultset. For example, the following query will return only 2 rows of the customer table randomly.
|
1 2 3 4 5 6 |
SELECT TOP(2) Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer |
SQL ORDER BY Clause
The ORDER BY statement is used to sort the fetched result set of the query in either ascending or descending according to one or more columns. For example, the following query will sort the result set of the query according to “CustomerName” in a descending manner.
|
1 2 3 4 5 6 7 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer ORDER BY Customer_Name DESC |
SQL COUNT() Function
In this part of the SQL Cheat Sheet, we will learn the COUNT() function. The SQL COUNT() function returns the number of rows in the result set. For example, the following query will return the number of rows in the Customer table.
|
1 2 3 4 5 |
SELECT COUNT(Customer.Customer_City) AS [Row_Number] FROM Online_Customers Customer |
SQL DISTINCT Operator
The DISTINCT operator allows us to retrieve only distinct values of the specified columns in the queries. As we can the following query only returns the different values of the city names.
SQL Join Operations
Due to the nature of the relational database relationship approach, we do not store all data in one table. Because of this, we need to create a result set by combining the data we should get data from different tables. This is exactly the point, joining the tables will help us. Commonly, we use 3 different joining methods in SQL. Now let’s look at these 3 different join types which are involved in our SQL Cheat Sheet.
The inner join allows joined tables to return rows that match each other.
The following query will return the customers who have only orders.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT oc.Customer_Id AS [Customer_Id] , o.Customer_Id AS [Customer_Id] , oc.Customer_Name AS [Name of the Customer] , oc.Customer_City AS [City Name of the Customer] , o.Order_Total AS [Order_Total] FROM Online_Customers AS oc INNER JOIN Orders AS o ON oc.Customer_Id = o.Customer_Id |
The left join allows returning all rows from the left table and any matching records from the right table.
The following query will return all customers and their matched orders of them.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT oc.Customer_Id AS [Customer_Id] , o.Customer_Id AS [Customer_Id] , oc.Customer_Name AS [Name of the Customer] , oc.Customer_City AS [City Name of the Customer] , o.Order_Total AS [Order_Total] FROM Online_Customers AS oc LEFT JOIN Orders AS o ON oc.Customer_Id = o.Customer_Id |
The right join allows returning all rows from the right table and any matching records from the right table.
The following query will return all orders and their matched customers of them.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT oc.Customer_Id AS [Customer_Id] , o.Customer_Id AS [Customer_Id] , oc.Customer_Name AS [Name of the Customer] , oc.Customer_City AS [City Name of the Customer] , o.Order_Total AS [Order_Total] FROM Online_Customers AS oc RIGHT JOIN Orders AS o ON oc.Customer_Id = o.Customer_Id |
SQL Server Basic Filter Operations
In this part of the SQL Cheat Sheet, we will look at how to filter out the data from a table for the basic requirements.
Equal Operator (=): We use the equal operator to compare the values of the column with any value. For example, if we want to return customers who are located in New York we can use the following query.
|
1 2 3 4 5 6 7 8 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_City='New York' |
Non-Equal Operator (<>): The Non-Equal operator works completely reverse of the equal operator, and it returns all rows except rows equal to any value. For example, if we want to return customers whose names are not equal to “Edward“, we can use the following query.
|
1 2 3 4 5 6 7 8 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_Name<> 'Ernest' |
We can use the following operators in T-SQL to filter out the rows of the tables.
Operator | Description |
= | Equals to |
<> | Not Equal |
!= | Not Equal |
> | Greater than |
>= | Greater than to equals to |
< | Less than |
<= | Less than or equal to |
Tip: There is no difference between the “<>” sign and “!=”, we can use both of them as non-equal operators.
SQL Server LIKE Operator
The LIKE operator is a logical operator and filters the matched record in the table according to a specified string pattern.
The percent wildcard (%) indicates zero or more characters. The following query returns the customers whose name starts with an “S” character.
|
1 2 3 4 5 6 7 8 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_Name LIKE 'S%' |
The underscore (_) wildcard indicates exactly one character in a string pattern.
|
1 2 3 4 5 6 7 8 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_City LIKE 'N_w Y_rk' |
SQL Server IN Keyword
We’ll now take a look at another operator from the SQL Cheat Sheet. The IN operator allows us to filter out multiple values in the WHERE clause. The values must be entered in parentheses and separated with a comma sign.
|
1 2 3 4 5 6 7 8 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_Name IN ('Salvador', 'Edward') |
SQL Server AND Operator
The AND operator is used to combine two or more two conditions in the where clause and it enables to return of data that satisfies all the conditions criteria. For example, the following query returns customers whose customer name is “Edward” and who live in the city of “Chicago”. For any row to be included in the query’s result set, it must satisfy both conditions.
|
1 2 3 4 5 6 7 8 9 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_City ='Chicago' AND Customer.Customer_Name='Edward' |
SQL Server OR Operator
The OR operator is used to combine two or more two conditions in the where clause and returns data that satisfies any of the conditions.
|
1 2 3 4 5 6 7 8 9 |
SELECT Customer.Customer_Name AS [Name of the Customer], Customer.Customer_City AS [City Name of the Customer] FROM Online_Customers Customer WHERE Customer.Customer_City ='Chicago' OR Customer.Customer_Name='Edward' |
SQL Server BETWEEN Operator
The BETWEEN operator filters the values within a given range.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT oc.Customer_Id AS [Customer_Id] , o.Customer_Id AS [Customer_Id] , oc.Customer_Name AS [Name of the Customer] , oc.Customer_City AS [City Name of the Customer] , o.Order_Total AS [Order_Total] FROM Online_Customers AS oc INNER JOIN Orders AS o ON oc.Customer_Id = o.Customer_Id WHERE Order_Total BETWEEN 158 AND 418.24 |
Summary
In this article, we looked at a SQL Cheat Sheet that helps to learn the fundamentals of T-SQL queries.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023