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:

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:

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:

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:

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:

Change SQL Server Table Structures with SQL ALTER TABLE

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:

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 BETWEEN Operator for WHERE Clause, CASE, INSERT, DELETE and UPDATE statements

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 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 full name and job title from Employees and Tables where there is someone who holds the job using an INNER JOIN.

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 full name and job title from Employees and Tables including all job titles using a LEFT JOIN.

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 full name and job title from Employees and Tables excluding job titles where no one has that job with a RIGTH JOIN.

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

Show average employee salary with the AVG function.

SELECT AVG(Salary) AS [AverageSalary]
FROM [dbo].[Employees];

Additional Resources

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

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

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

View all my tips

Article Last Updated: 2022-10-17

Comments For This Article

Monday, October 31, 2022 - 12:56:49 PM - Joe Gavin	Back To Top (90651)
Good eye Armin. FROM is optional so both ways are correct. It would appear using FROM would make the statement more portable to other DBMs though and it would be the preferred way.

Monday, October 31, 2022 - 12:39:13 PM - Joe Gavin	Back To Top (90650)
Thanks Hristo. Stay tuned...

Monday, October 31, 2022 - 4:30:05 AM - Armin Oertel	Back To Top (90648)
Hello Joe, I think, that for a beginner it‘s helpfull to have such a quickstart. Unfortunately I found a mistake in your examples. In the „Delete data from a table“ area, you forgot the „from“ keyword.

Monday, October 31, 2022 - 4:00:10 AM - Joost	Back To Top (90647)
Small error in creating tables: Address2 - data type nvarchar(50) - can't be null, [...] [Address2] [nvarchar](50) NULL,

Tuesday, October 18, 2022 - 2:05:27 AM - Hristo Hristov	Back To Top (90611)
Hi Joe, it would be helpful making such a "cheat sheet" for more complex examples, e.g., CTE's, grouping, nested queries, triggers, indexes...

SQL Cheat Sheet for Basic T-SQL Commands

Problem

Solution

Creating a SQL Server Database

Using a SQL Server Database

Creating SQL Server Tables

Populating SQL Server Tables with INSERT INTO Syntax

Querying SQL Server Tables

Add a Column to a SQL Server Table

Updating Data in a SQL Server Table

More SQL Query Examples

SQL WHERE Clause Examples

SQL BETWEEN Clause Example

SQL ORDER BY Clause Examples

SQL JOIN Examples - INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

IS NULL SQL Query Example

SQL Subquery Examples

SQL Function Examples

Dropping a Column from a SQL Server Table

Deleting Data from a SQL Server Table

Truncating a SQL Server Table

Drop a SQL Server Table

Next Steps

About the author

Comments For This Article

Related Articles