SQL Statements

Purpose: Practice running SQL statements.

Prepares you for: Assignment 4

Get Started

Log in to the virtual desktop
Start SQL Server 2014 Management Studio
Start SQL Server 2014 Management Studio

Note: This activity uses the AdventureWorks2014 database, which is available within the virtual desktop.
Run Statements

Run the following SQL statements on SQL Server using the SQL Server Management Studio.

Example 1

Retrieves all records from the table in descending order.

USE AdventureWorks2014;
SELECT *
FROM HumanResources.Department
ORDER BY DepartmentID DESC
Example 2

Retrieves records that satisfy the WHERE clause.

USE AdventureWorks2014;
SELECT *
FROM HumanResources.Department
WHERE DepartmentID > 4
Example 3

Shows the use of the BETWEEN operator.

USE AdventureWorks2014;
SELECT
AccountNumber,
SalesOrderID,
OrderDate
FROM Sales.SalesOrderHeader
WHERE
OrderDate BETWEEN '7/1/2011' AND '12/31/2011'
Example 4

Shows the use of the AND operator.

USE AdventureWorks2014;
SELECT
AccountNumber,
SalesOrderID,
OrderDate
FROM Sales.SalesOrderHeader
WHERE
OrderDate >= '5/1/2011 00:00:00' AND OrderDate<= '12/31/2011'
Example 5

Shows the use of the AND and BETWEEN operators together.

USE AdventureWorks2014;
SELECT
SalesOrderDetailID,
OrderQty,
ProductID,
ModifiedDate
FROM Sales.SalesOrderDetail
WHERE
ModifiedDate BETWEEN '5/1/2011' AND '12/31/2011' AND
ProductID = 776
Example 6

Shows the use of the IN operator.

USE AdventureWorks2014;
SELECT
SalesOrderDetailID,
OrderQty,
ProductID,
ModifiedDate
FROM Sales.SalesOrderDetail
WHERE
ProductID IN (776, 778, 747, 809)
Example 7

Shows the use of the LIKE operator with a % wildcard.

USE AdventureWorks2014;
SELECT *
FROM HumanResources.Department
WHERE
Name LIKE 'Pr%'
Example 8

Shows the use of the AS operator with an alias.

USE AdventureWorks2014;
SELECT
DepartmentID,
Name AS DepartmentName,
GroupName AS DepartmentGroupName
FROM HumanResources.Department
Example 9

Shows the use of INNER JOIN to join two tables based on the condition you specify.

USE AdventureWorks2014;
SELECT
p.FirstName,
p.LastName,
ea.EmailAddress
FROM Person.Person AS p
INNER JOIN Person.EmailAddress AS ea
ON p.BusinessEntityID = ea.BusinessEntityID
Example 10

Another example of INNER JOIN to join two tables.

USE AdventureWorks2014;
SELECT
p.ProductID,
p.Name AS ProductName,
sd.OrderQty,
sd.UnitPrice
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail sd
ON p.ProductID = sd.ProductID
Example 11

Shows how UNION can be used to combine two SELECT statements into one result.

USE AdventureWorks2014;
SELECT
Name AS ProductName
FROM Production.Product
WHERE
Color = 'Black'
UNION
SELECT
Name AS ProductName
FROM Production.Product
WHERE
Color = 'Silver'
Example 12

Uses the CAST function to convert data from one data type to another.

USE AdventureWorks2014;
SELECT TOP(10)
SalesOrderNumber, TotalDue,
CAST(TotalDue AS decimal(10,2)) AS TotalDueCast
FROM Sales.SalesOrderHeader;
Example 13

Shows the use of OUTER JOIN to join two tables based on the condition you specify.

USE AdventureWorks2014;
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID