SQL Queries Tutorial
If you are just getting started with writing SQL queries and looking for an introduction on how to write basic queries for Microsoft SQL Server take a look at this tutorial for some examples on how to learn SQL SELECT, INSERT, UPDATE and DELETE commands.
In this new SQL tutorial, we cover basic the SQL syntax for selecting, inserting, updating and deleting a table.
Requirements for the SQL Examples
To follow along, the following would be helpful:
- SSMS or Azure Data Studio installed.
- An instance of SQL Server installed.
- We will use the AdventureWorks sample database in SQL Server.
Depending on the SQL Server version installed, you can use that version of AdventureWorks or earlier. So, if you have SQL Server 2019 you can use the AdventureWorks2019.bak or lower versions. If you have SQL Server 2012, you should use AdventureWorks2012 or lower versions.
For more information about installing the AdventureWorks database, refer to this link: AdventureWorks Database Installation Steps.
What is SQL?
SQL stands for Structure Query Language. It is a language used by relational databases to SELECT, INSERT, UPDATE and DELETE (DML commands - Data Manipulation Language) data for most of the database platforms like Oracle, SQL Server, MySQL, PostgreSQL, etc. that use the SQL language to query data and manipulate it. You can also use SQL to manage security and other database objects like CREATE DATABASE, CREATE TABLE, stored procedures, functions and more with (DDL commands - Data Definition Language).
To connect from SSMS to SQL Server, use the following link if you are not familiar: Use SQL Server Management Studio to Connect to Database.
SQL SELECT Examples
The select statement is the most basic and simple SQL to query data. Here is the basic syntax:
SELECT column1, column2 FROM schema.table
The following example will query the information from the Person.Person table from the Adventurework2019 database.
SELECT [BusinessEntityID], [PersonType], [NameStyle] FROM [Person].[Person]
The following SQL will query all the columns from the table, by using * instead of specifying column names.
SELECT * FROM [Person].[Person]
You could also use the following with the same result where you use the table name in front of the *.
SELECT Person.* FROM [Person].[Person]
You can also filter the data using a WHERE clause. The following example will filter the data where the BusinessEntityID is equal to 7.
SELECT * FROM [Person].[Person] WHERE BusinessEntityID = 7
The following example will show how to get persons with first name equal to Gail.
SELECT * FROM [Person].[Person] WHERE FirstName = 'Gail'
Also, we have an example to show all the persons with the last name that ends will the letter l.
SELECT FirstName, LastName FROM [Person].[Person] WHERE LastName LIKE '%l'
For more examples about SELECT statements in a SQL database see these tips and tutorials:
- SQL Server SELECT Examples
- SQL Server Subquery Example
- SQL Server IN vs EXISTS
- What are the Aggregate Functions in SQL
- SQL Server T-SQL Aggregate Functions
- SQL Server Window Aggregate Functions SUM, MIN, MAX and AVG
- SQL SELECT COUNT Function
- UNION vs. UNION ALL in SQL Server
SQL JOIN Examples
JOIN logic is very important in the SQL language. As the name states, it is used to join tables to let you build queries from multiple tables in a SQL database.
The INNER JOIN SQL command will get data from two tables where a value matches in both tables.
The following example shows the NationalIDNumber, JobTitle, FirstName and LastName of employees. The HumanResources.Employee table does not contain FirstName and LastName, so we have to join to the Person.Person table using the BusinesssEntityID which is shown as E.BusinessEntityID = P.BusinessEntityID.
We are also using a table alias for the tables. So instead of having to type [HumanResources].[Employee] repeatedly we use the alias "E". We also do the same for the [Person].[Person] with an alias of "P".
SELECT E.NationalIDNumber, E.JobTitle, P.FirstName, P.LastName FROM [HumanResources].[Employee] as E INNER JOIN [Person].[Person] as P on E.BusinessEntityID = P.BusinessEntityID
LEFT OUTER JOIN
The LEFT OUTER JOIN SQL command will pull all data from one table and only the matching data for the left table in the JOIN.
Here we have an example of a LEFT JOIN. The following query shows how to get all person records even when there is not a matching employee record. This will return every Person record and also show data from the Employee table when there is a match.
SELECT P.BusinessEntityID, E.FirstName, E.LastName FROM [Person].[Person] as P LEFT JOIN [HumanResources].[Employee] as E on E.BusinessEntityID = P.BusinessEntityID
Finally, we have the CROSS JOIN which is a cartesian join with all possible combinations. The following example shows how to display the all the combinations using the NationalIDNumer, JobTitle, FirstName, Lastname columns of the employee and person tables. We use the TOP 1000 to show only the first 1000 combinations.
SELECT TOP 1000 E.NationalIDNumber, E.JobTitle, P.FirstName, P.LastName FROM [HumanResources].[Employee] as E CROSS JOIN [Person].[Person] as P
For more information and examples about JOINS, refer to the following links:
- SQL Server Join Example
- Learn about SQL Joins on Multiple Columns
- SQL RIGHT JOIN Examples
- SQL Server JOIN Hints
- Getting Started with SQL INNER JOIN
SQL ORDER BY Examples
Using ORDER BY sorts the data in ascending (asc) or descending (desc) order according to one or more columns specified. By default, it is in ascending order.
The following example will order the columns of the person table in ascending order according to the FirstName column.
SELECT FirstName, LastName FROM [Person].[Person] ORDER BY FirstName
The result set will be in alphabetic order.
You do not need to specify ascending order, but you could as follows:
SELECT FirstName, LastName FROM [Person].[Person] ORDER BY FirstName ASC
For descending order, you use the following synatx:
SELECT FirstName, LastName FROM [Person].[Person] ORDER BY FirstName DESC
For more examples and explanations please refer to the following links:
- SQL ORDER BY Clause Examples
- SQL ORDER BY Clause
- SELECT with WHERE and ORDER BY
- Avoid ORDER BY in SQL Server views
SQL GROUP BY Examples
The GROUP BY command groups or consolidates the same data using one or more columns.
The following example will group the data by TerritoryID and get a sum of the Freight for each TerritoryID.
SELECT SUM(Freight) as TotalFreight, TerritoryID FROM [Sales].[SalesOrderHeader] GROUP BY TerritoryID
You could also include the HAVING clause to filter the GROUP BY condition.
The following example illustrates how to use the HAVING clause and only includes rows where the sum is greater than 700000.
SELECT SUM(Freight) as TotalFreight, TerritoryID FROM [Sales].[SalesOrderHeader] GROUP BY TerritoryID HAVING SUM(Freight) > 700000
INSERT, UPDATE, DELETE and TRUNCATE Examples
INSERT allows you to add a new row or rows to a table or view. You can insert a single row or multiple rows when the INSERT is combined with the SELECT statement.
The following example shows how to insert a new row into the Sales.Currency table.
INSERT INTO [Sales].[Currency] VALUES ('MSS', 'SQL Dollars', GETDATE())
UPDATE allows you to update existing data for a table or view.
Here we are updating our new row and changing CurrencyCode from "MSS" to "SQL".
UPDATE [Sales].[Currency] SET CurrencyCode = 'SQL' WHERE CurrencyCode = 'MSS'
DELETE lets you delete one or more rows from a table or view.
The following example deletes the data previously inserted and updated.
DELETE FROM [Sales].[Currency] WHERE CurrencyCode = 'MSS'
SQL SELECT ... INTO
This example shows how to create a new table called Sales.Currency2 based on the existing table Sales.Currency. This will create the new table and make a copy of all the data into the new table with the SQL SELECT ... INTO command.
SELECT * INTO [Sales].[Currency2] FROM [Sales].[Currency]
For more information about these commands refer to these links:
- SELECT command for SQL Server Tutorial
- SQL INSERT INTO SELECT Examples
- INSERT INTO SQL Server Command
- SQL Server INSERT Command Tutorial
- SQL UPDATE Statement
- Delete SQL Statement in SQL Server, Oracle and PostgreSQL
- Delete duplicate rows with no primary key on a SQL Server table
- Differences between Delete and Truncate in SQL Server
- Fastest way to Delete Large Number of Records in SQL Server
FFor more information refer to the following links:
- What is SQL Server?
- SQL Server 101 Tutorial Outline and Overview
- Learning SQL Server
- SQL Server Data Types Quick Reference Guide
- Varchar Data Types in SQL Server, Oracle and PostgreSQL
About the author
View all my tips
Article Last Updated: 2022-02-23