SQL Cheat Sheet for Basic T-SQL Commands
By: Joe Gavin | Updated: 2022-10-17 | Comments (5) | Related: More > TSQL
Problem
I am new to the world of relational databases with Microsoft SQL Server. Is there a source to learn SQL (Structured Query Language) syntax? Can you provide a SQL Cheat Sheet?
Solution
Welcome to SQL Server! There are so many resources today for all things SQL Server. But look no further! Here is a SQL cheat sheet for general SQL syntax you can copy, paste, modify, and execute.
Creating a SQL Server Database
To get started, we'll create a database called AcmeRetailStores with the data and log files in the default file locations with the following SQL commands.
USE master; GO -- create db CREATE DATABASE [AcmeRetailStores]; GO
Additional Resources:
- SQL Server Create Database Examples
- Create a Microsoft SQL Server Database
- Create SQL Server Database using SQL Server Management Studio
Using a SQL Server Database
-- use db USE [AcmeRetailStores]; GO
Creating SQL Server Tables
Create three new tables with the following column names, data types and null properties:
- Employees
- EmpNum - data type int - primary key
- FirstName - data type nvarchar(30) - can't be null
- LastName - data type nvarchar(30) - can't be null
- MiddleInitial - data type nchar(1) - can be null
- Job - data type int - can't be null
- Jobs
- JobNum - data type int - primary key
- Title - data type nvarchar(3) - can't be null
- Stores
- StoreNum - data type int - primary key
- Address - data type nvarchar(50) - can't be null
- Address2 - data type nvarchar(50) - can't be null,
- City - data type nvarchar(50) - can't be null
- State - data type nchar(2) - can't be null
- Zipcode - data type nchar(5) - can't be null
- Phone - data type nchar(12) - can't be null
-- create table [dbo].[Employees] CREATE TABLE [dbo].[Employees]( [EmpNum] [int] PRIMARY KEY, [FirstName] [nvarchar](30) NOT NULL, [LastName] [nvarchar](30) NOT NULL, [MiddleInitial] [nchar](1) NULL, [Job] [int] NOT NULL ); GO -- create table [dbo].[Jobs] CREATE TABLE [dbo].[Jobs]( [JobNum] [int] PRIMARY KEY, [Title] [nvarchar](30) NOT NULL ); GO -- create table [dbo].[Stores] CREATE TABLE [dbo].[Stores]( [StoreNum] [int] PRIMARY KEY, [Address] [nvarchar](50) NOT NULL, [Address2] [nvarchar](50) NULL, [City] [nvarchar](50) NOT NULL, [State] [nchar](2) NOT NULL, [Zipcode] [nchar](5) NOT NULL, [Phone] [nchar](12) NOT NULL ); GO
Additional Resources:
- Create Tables in SQL Server with T-SQL
- How To Create a Table in SQL Server
- How to Create a SQL Server Database, Tables, Foreign Keys and Data for an Application
- Create a Table in Microsoft SQL Server
- How to create a table using SQL Server Management Studio
- Create SQL Server Tables Using Foreign Keys for Referential Integrity
Populating SQL Server Tables with INSERT INTO Syntax
Populate the tables with some sample data with INSERT INTO syntax:
INSERT table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
-- populate employees INSERT [dbo].[Employees]([EmpNum], [FirstName], [LastName], [MiddleInitial], [Job]) VALUES(1001, 'Robert', 'Smith', '', 1); INSERT [dbo].[Employees]([EmpNum], [FirstName], [LastName], [MiddleInitial], [Job]) VALUES(1002, 'Sue', 'Jones', '', 5); INSERT [dbo].[Employees]([EmpNum], [FirstName], [LastName], [MiddleInitial], [Job]) VALUES(1003, 'Alan', 'Doe', 'A', 3); INSERT [dbo].[Employees]([EmpNum], [FirstName], [LastName], [MiddleInitial], [Job]) VALUES(1004, 'Tim', 'Kelly', '', 2); INSERT [dbo].[Employees]([EmpNum], [FirstName], [LastName], [MiddleInitial], [Job]) VALUES(1005, 'Louis', 'Sullivan', '', 2); -- populate jobs INSERT [dbo].[Jobs]([JobNum], [Title])VALUES(1, 'Store Manager'); INSERT [dbo].[Jobs]([JobNum], [Title])VALUES(2, 'Assistant Store Manager'); INSERT [dbo].[Jobs]([JobNum], [Title])VALUES(3, 'Cashier'); INSERT [dbo].[Jobs]([JobNum], [Title])VALUES(4, 'Warehouse Supervisor'); INSERT [dbo].[Jobs]([JobNum], [Title])VALUES(5, 'Warehouse Picker'); -- populate stores INSERT [dbo].[Stores]([StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone]) VALUES(1, '100 Main St', '#2', 'Sometown', 'TX', '12345', '123-456-7890'); INSERT [dbo].[Stores]([StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone]) VALUES(2, '220 Elms St', '', 'Anothertown', 'TX', '23456', '123-455-7891'); INSERT [dbo].[Stores]([StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone]) VALUES(3, '5 Cherry St', '#6', 'Mayberry', 'NC', '75986', '552-123-4567'); INSERT [dbo].[Stores]([StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone]) VALUES(4, '1 Broadway St', '', 'Bigcity', 'NY', '58463', '212-598-5897'); INSERT [dbo].[Stores]([StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone]) VALUES(5, '45 Fifth St', '', 'Smalltown', 'NY', '56987', '123-555-7890');
Additional Resources:
- INSERT INTO SQL Server Command
- Basic SQL INSERT Statement with Examples
- SQL Server INSERT Command Tutorial
- INSERT INTO for SQL Server, Oracle and PostgreSQL
- SQL INSERT INTO SELECT Examples
Querying SQL Server Tables
Select all fields and records from the three tables with SELECT statements.
SELECT [EmpNum], [FirstName], [LastName], [MiddleInitial], [Job] FROM [dbo].[Employees]; SELECT [JobNum], [Title] FROM [dbo].[Jobs]; SELECT [StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone] FROM [dbo].[Stores];

Select all fields for the stores that are in Texas with the WHERE clause.
SELECT [StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone] FROM [dbo].[Stores] WHERE [State] = 'TX';

Select all fields for the stores that have a '123' area code in the WHERE clause.
SELECT [StoreNum], [Address], [Address2], [City], [State], [Zipcode], [Phone] FROM [dbo].[Stores] WHERE [Phone] LIKE '123%';

Additional Resources:
- SQL Server SELECT Examples
- SQL SELECT DISTINCT Examples
- How to Write a SQL SELECT Statement
- SQL Server SELECT Tutorial
Add a Column to a SQL Server Table
Add a column called Salary with data type Money to Jobs.
ALTER TABLE [dbo].[Employees] ADD [Salary] [Money];
Additional Resources:
Updating Data in a SQL Server Table
Update Jobs with salaries.
UPDATE [dbo].[Employees] SET [Salary] = 100000 WHERE [EmpNum] = '1001'; UPDATE [dbo].[Employees] SET [Salary] = 90000 WHERE [EmpNum] = '1004'; UPDATE [dbo].[Employees] SET [Salary] = 50000 WHERE [EmpNum] = '1005'; UPDATE [dbo].[Employees] SET [Salary] = 45000 WHERE [EmpNum] = '1002'; UPDATE [dbo].[Employees] SET [Salary] = 40000 WHERE [EmpNum] = '1003'; SELECT * FROM [dbo].[Employees];

Additional Resources:
- SQL UPDATE Statement
- Basic SQL UPDATE Statement with Examples
- UPDATE Statement Performance in SQL Server
- SQL Update Statement with Join in SQL Server vs Oracle vs PostgreSQL
More SQL Query Examples
SQL WHERE Clause Examples
Show all employees with a salary greater than $90,000.
SELECT [FirstName],[LastName] FROM [dbo].[Employees] WHERE [Salary] > 90000;

Show all employees with a salary greater than or equal to $90,000.
SELECT [FirstName],[LastName] FROM [dbo].[Employees] WHERE [Salary] >= 90000;

Show all employees with a salary less than $90,000.
SELECT [FirstName],[LastName] FROM [dbo].[Employees] WHERE [Salary] <= 90000;

Show all employees with a salary not equal to $90,000.
SELECT [FirstName],[LastName] FROM [dbo].[Employees] WHERE [Salary] != 90000;

Additional Resources:
SQL BETWEEN Clause Example
Show all employees with a salary between $49,000 and 51,000.
SELECT [FirstName],[LastName] FROM [dbo].[Employees] WHERE [Salary] BETWEEN 49000 AND 51000;

Additional Resources:
SQL ORDER BY Clause Examples
Show all Employees in ascending order of salary.
SELECT [FirstName],[LastName],[Salary] FROM [dbo].[Employees] ORDER BY [Salary]; -- ASC is default

Show all columns in Jobs in descending order of salary.
SELECT [FirstName],[LastName],[Salary] FROM [dbo].[Employees] ORDER BY [Salary] DESC;

Additional Resources:
- SQL ORDER BY Clause
- SQL ORDER BY Clause Examples
- Advanced Use of the SQL Server ORDER BY Clause
- Different Methods to Sort SQL Server Result Sets
SQL JOIN Examples - INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
Select the full name and job title from Employees and Tables where someone holds the job using an INNER JOIN.
SELECT [e].[FirstName], [e].[LastName], [j].[Title] FROM [dbo].[Jobs] [j], [dbo].[Employees] [e] WHERE [j].[JobNum] = [e].Job;

Select the full name and job title from Employees and Tables, including all job titles using a LEFT JOIN.
SELECT [e].[FirstName], [e].[LastName], [j].[Title] FROM [dbo].[Jobs] [j] LEFT JOIN [dbo].[Employees] [e] ON [j].[JobNum] = [e].Job;

Select the full name and job title from Employees and Tables, excluding job titles where no one has that job with a RIGHT JOIN.
SELECT [e].[FirstName], [e].[LastName], [j].[Title] FROM [dbo].[Jobs] [j] RIGHT JOIN [dbo].[Employees] [e] ON [j].[JobNum] = [e].Job;

Select all full names and job titles from Employees and Tables, excluding job titles where no one has that job with a FULL JOIN.
SELECT [e].[FirstName], [e].[LastName], [j].[Title] FROM [dbo].[Jobs] [j] FULL JOIN [dbo].[Employees] [e] ON [j].[JobNum] = [e].Job;

IS NULL SQL Query Example
Show unfilled job titles with IS NULL.
SELECT [e].[FirstName], [e].[LastName], [j].[Title] FROM [dbo].[Jobs] [j] FULL JOIN [dbo].[Employees] [e] ON [j].[JobNum] = [e].Job WHERE [e].[FirstName] IS NULL AND [e].[LastName] IS NULL;

SQL Subquery Examples
Find any unfilled jobs with a subquery.
SELECT [Title] FROM [dbo].[Jobs] WHERE [JobNum] NOT IN ( SELECT [Job] FROM [dbo].[Employees] );

Additional Resources:
SQL Function Examples
Show the number of employees using COUNT and an alias
SELECT COUNT(*) AS [NumberOfEmployees] FROM [dbo].[Employees];

Additional Resources
- SQL COUNT Function
- Count of rows with the SQL Server COUNT Function
- SQL Server Row Count for all Tables in a Database
- Getting Starting with SQL COUNT() and COUNT_BIG() Functions
- SQL Server COUNT() Function Performance Comparison
Show average employee salary with the AVG function.
SELECT AVG(Salary) AS [AverageSalary] FROM [dbo].[Employees];

Additional Resources
- Max, Min, and Avg SQL Server Functions
- 5 use cases of SQL Average
- SQL Server Window Aggregate Functions SUM, MIN, MAX and AVG
Combine the Firstname and LastName fields in the Employees table into FullName with the CONCAT function.
SELECT CONCAT([FirstName], ' ', [LastName]) FROM [dbo].[Employees];

Additional Resources
- Learn how to concatenate data in SQL Server
- Multiple Ways to Concatenate Values Together in SQL Server
- CONCAT and CONCAT_WS function in SQL Server
- Using SQL Server Concatenation Efficiently
- Concatenation of Different SQL Server Data Types
- New FORMAT and CONCAT Functions in SQL Server 2012
- Concatenate SQL Server Columns into a String with CONCAT()
Dropping a Column from a SQL Server Table
Drop the MiddleInitial column from the Employees table with ALTER TABLE.
ALTER TABLE [dbo].[Employees] DROP COLUMN [MiddleInitial];
Deleting Data from a SQL Server Table
Delete the 'Warehouse Supervisor' record from the Jobs table with DELETE.
DELETE [dbo].[Jobs] WHERE [Title] = 'Warehouse Supervisor';
Truncating a SQL Server Table
Delete all the records in the Jobs table without logging the transaction with TRUNCATE TABLE.
TRUNCATE TABLE [dbo].[Jobs];
Drop a SQL Server Table
Remove the Jobs table structure and data with DROP TABLE.
DROP TABLE [dbo].[Jobs];
Next Steps
- This link has an extensive list of T-SQL tips: SQL Server T-SQL Tips
- Check out this SQL Queries Tutorial
About the author

View all my tips
Article Last Updated: 2022-10-17