Basic SQL Cheat Sheet using SELECT, INSERT, DELETE and UPDATE
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.
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.
SQL SELECT Syntax
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.
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
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
Learn more about SELECT:
- SQL Server SELECT Examples
- How to Write a SQL SELECT Statement
- Learning the SQL GROUP BY Clause
- SQL GROUP BY Queries to Summarize and Rollup Sets of Records
- Group By in SQL Server with CUBE, ROLLUP and GROUPING SETS Examples
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
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
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
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:
- Create Tables in SQL Server with T-SQL
- How To Create a Table in SQL Server
- SQL SELECT INTO Examples
- Creating a table using the SQL SELECT INTO clause - Part 1
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
About the author
View all my tips
Article Last Updated: 2023-02-10