SQL Query Examples

Problem

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.

Solution

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'

select with a filter where with characters

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 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.

INNER JOIN

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

CROSS JOIN

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

SQL INSERT

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())

SQL UPDATE

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'

SQL DELETE

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:

Next Steps

FFor more information refer to the following links:

Resources

Community

Subscribe