SQL Cheat Sheet for Basic T-SQL Commands

By:   |   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:

  1. 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
  2. Jobs
    • JobNum - data type int - primary key
    • Title - data type nvarchar(3) - can't be null
  3. 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 and records from the three tables

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 are in Texas

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%';
Select all fields for the stores that have a ‘123’ area code.

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:

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];
Update Jobs with salaries.

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 $90,000

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 greater than or equal to $90,000

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 less than $90,000.

Show all employees with a salary not equal to $90,000.

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

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;
Show all employees with a salary between $49,000 and 51,000

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 Employees in ascending order of salary

Show all columns in Jobs in descending order of salary.

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

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;
Select all full names and job titles from Employees and Tables excluding job titles where no one has that job with a FULL JOIN.

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;
Show unfilled job titles with 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]
                      );
Find any unfilled jobs with a subquery

Additional Resources:

SQL Function Examples

Show the number of employees using COUNT and an alias

SELECT COUNT(*) AS [NumberOfEmployees]
FROM [dbo].[Employees];
Show the number of employees using COUNT and an alias

Additional Resources

Show average employee salary with the AVG function.

SELECT AVG(Salary) AS [AverageSalary]
FROM [dbo].[Employees];
Show average employee salary with the AVG function.

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];
Combine the Firstname and LastName fields in the Employees table into FullName with the CONCAT function.

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


Related Articles

Popular Articles




get scripts

next tip button



About the author
MSSQLTips author Joe Gavin 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...














get free sql tips
agree to terms