SQL Cheat Sheet for SELECT, INSERT, DELETE and UPDATE Commands

Problem

I’m new to relational databases and SQL (Structured Query Language). I’d like to know what the basic SQL Statements are and how they are used.

Solution

In this tutorial, I provide a summary of basic T-SQL Statements and simple examples of each in order to learn SQL. Also, included are references to MSSQLTips articles to learn more about each of the basic SQL Statements which is great for beginners.

This tutorial will cover what is known in T-SQL as Data Manipulation Language or DML and will include the following statements: SELECT, INSERT, UPDATE, DELETE. This tutorial can be used as a quick reference guide to each of these DML statements.

For the examples I will use table “dbo.Pets” which has the data below. There is a script at the very bottom of this tutorial that shows how to create the table and load this sample data.

ID	PetName	Age	Class
1	Cletus	1	Dog
2	Velma	11	Dog
3	Joey	16	Cat
4	Chattie	5	Cat

SQL SELECT Syntax

The SELECT statement is used to retrieve SQL data from Tables or Views.

The SQL SELECT statement typically includes 3 main parts:

SELECT, lists the table columns to be returned;
FROM, specifies the table or tables that the data will be retrieved from;
WHERE clause, which defines the filters on the data to be returned.
Optionally, an ORDER BY clause can be included to sort the data being returned.

Tables names have two parts: the schema and the table name.

The default schema is “dbo”. Custom schema names can be created to help organize tables in a database. Referencing the schema name with the table name is optional in some circumstances but it is a best practice to always use the schema.

Example 1a: Basic SELECT Statement

Select columns ID, PetName, Age, and class from table dbo.Pets.

--1a) Basic SELECT Statement
SELECT ID, PetName, Age, Class
FROM dbo.Pets;
--OR
SELECT * FROM dbo.Pets;
GO

Note: SELECT * syntax can also be used to return all columns from the table.

Result Set:

Example 1b: Basic SELECT Statement with a Filter

This example selects the same list of columns, but applies a filter to only return rows with Class of ‘Dog’.

--1b) Basic SELECT Statement with WHERE Clause
SELECT ID, PetName, Age, Class
FROM dbo.Pets
WHERE Class = 'Dog';
GO

Result Set:

Example 1c: Basic SELECT Statement with a Filter

This example selects all the columns from the dbo.Pets tables but sorts the table by the Age of the pets.

--1c) Basic SELECT Statement with ORDER BY Clause
SELECT ID, PetName, Age, Class
FROM dbo.Pets
ORDER BY Age; -- DESC or ASC
GO

Result Set:

Learn more about SELECT:

SQL INSERT INTO Syntax

The INSERT statement is used to add new rows to a table.

The INSERT is structured with the keyword INSERT, followed by the table name and sometimes optional but always good to include the schema that the table is part of. Next is the list of columns from the table. Technically the column list is optional, but it is good practice to include the column list. Last is the VALUES clause which includes the values in the same order as the columns to be populated. If the column list is not included then the values must match the column order in the table, less Identity columns and timestamp columns which are excluded, as they are auto-populated. When the column list is provided, nullable columns can be excluded. The article linked below will show a few other methods for inserting data into a table.

Example 2: Basic INSERT INTO Statement

This example inserts a new row into the dbo.Pets table. The SELECT * statement shows all rows in the table including the newly inserted row.

--2) Basic INSERT Statement
INSERT dbo.Pets (ID, PetName, Age, Class)
VALUES (5, 'Perry', 2, 'Rat');
GO
SELECT * FROM dbo.Pets;
GO

Result Set:

Learn More About SQL INSERT INTO

SQL UPDATE Syntax

The UPDATE statement is used to change table column values.

It starts with the UPDATE keyword followed by the table name and optionally schema name. Next is the SET clause that lists the columns being updated, an equal sign and the value being set. Last is the WHERE clause which is optional but necessary to limit the rows in the table to be updated.

Example 3: Basic UPDATE Statement

This example changes the class if Pet ID 5 from ‘Rat’ to ‘Hamster’ and the Age from 2 to 3. The SELECT statement shows the results of the UPDATE statement for ID = 5.

--3) Basic UPDATE Statement
UPDATE dbo.Pets 
SET Class = 'Hamster',
    Age = 3
WHERE ID = 5;
GO
SELECT * FROM dbo.Pets WHERE ID = 5;
GO

Result Set:

Link To Learn More About UPDATE

SQL DELETE Syntax

The DELETE statement is used to remove rows from a table.

The DELETE statement starts with the keyword DELETE, followed by the schema name and table name. Again, the schema name may be optional, but it is good practice to include it. Last is the WHERE clause, also optional but necessary to limit the rows being removed from the table. If the WHERE clause is excluded, then the DELETE will empty the table removing all rows.

Example 3: Basic DELETE Statement

This example removes the row for ID = 5. The SELECT shows the end results.

--4) Basic DELETE Statement
DELETE FROM dbo.Pets 
WHERE ID = 5;
GO
SELECT * FROM dbo.Pets;
GO

Result Set:

Learn More About SQL DELETE

SQL Table Create and Load Syntax

Here is the table creation statement and an INSERT statement to set up the dbo.Pets table. Note, in this example I insert multiple rows in with a single INSERT statement.

--5) Basic Create Table and Load Statement
CREATE TABLE dbo.Pets (
   ID int,
   PetName varchar(20),
   Age int,
   Class varchar(20));
GO
--Load the Table
INSERT dbo.Pets (ID, PetName, Age, Class)
VALUES (1, 'Cletus',  1, 'Dog'),
       (2, 'Velma',  11, 'Dog'),
       (3, 'Joey',   16, 'Cat'),
       (4, 'Chattie', 5, 'Cat');
GO

Check out these tips:

Next Steps

Use this as a quick reference cheat sheet for basic SQL data manipulation language (DLM) statements including SELECT, INSERT, UPDATE and DELETE. These statements are the basic building blocks for writing T-SQL code. Refer to the links in the article for more examples and to learn more about each statement.

To recap, here is a list of the links to learn more about basic SQL Statements:

Learn How to Write a SQL SELECT Statement
Getting Started with Basic SQL INSERT Statement with Examples
Read more: Basic SQL UPDATE Statement with Examples
Learn about Basic SQL DELETE Statement with Examples
Learn more about Creating tables in SQL: Create Tables in SQL Server with T-SQL
Check out the SQL Cheat Sheet for Basic T-SQL Commands
Read about SQL Server Window Aggregate Functions SUM, MIN, MAX and AVG
Learn What are the Aggregate Functions in SQL
Check out the SQL Server Data Types Quick Reference Guide
SQL Server Inner Join, Left Join and Right Join with Examples

Resources

Community

Subscribe