1. Install AdventureWorks database:
Copy the GitHub data files and scripts for
AdventureWorks Links to an external site.
to the C:\Samples\AdventureWorks folder on your local client.
Or,
download AdventureWorks-oltp-install-script.zip Links to an external site.
and extract the zip file to the C:\Samples\AdventureWorks folder.
Open C:\Samples\AdventureWorks\instawdb.sql in SQL Server Management Studio and follow the instructions at the top of the file. Please note that you can change the name of your database from AdventureWorks on the SQL script to AdventureWorks2022 because you need this name in the “SELECT examples (Transact-SQL)” step in the next step.
sql-server-samples/samples/databases/adventure-works at master · microsoft/sql-server-samples · GitHu Links to an external site.
2. SELECT examples (Transact-SQL)
1. Open SSMS and click on the New Query (Press Ctrl+N).2. Write the following T-SQL command in your .sql file:USE AdventureWorks2022; –OR USE AdventureWorks if you have not changed the database name in the previous step.GO3. Go to the File menu and save the “.sql” file with the name AdventureWorks2022.sql4. Copy, paste, and run each of the SELECT statements available in the following link (SELECT examples (Transact-SQL) – SQL Server | Microsoft Lear Links to an external site.). There are 30 SELECT statement queries.
5. Capture screen after executing each SELECT statement and add the capture screen to a new Word document called AdventureWorks2022.doc or AdventureWorks2022.docx
6. Your first query is:
USE AdventureWorks2022;GOSELECT *FROM Production.ProductORDER BY Name ASC;– Alternate way.USE AdventureWorks2022;GOSELECT p.*FROM Production.Product AS pORDER BY Name ASC;GO
And your last query is:
USE AdventureWorks2022;GOIF OBJECT_ID(‘dbo.EmployeeOne’, ‘U’) IS NOT NULLDROP TABLE dbo.EmployeeOne;GOIF OBJECT_ID(‘dbo.EmployeeTwo’, ‘U’) IS NOT NULLDROP TABLE dbo.EmployeeTwo;GOIF OBJECT_ID(‘dbo.EmployeeThree’, ‘U’) IS NOT NULLDROP TABLE dbo.EmployeeThree;GOSELECT pp.LastName, pp.FirstName, e.JobTitleINTO dbo.EmployeeOneFROM Person.Person AS ppINNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = ‘Johnson’;GOSELECT pp.LastName, pp.FirstName, e.JobTitleINTO dbo.EmployeeTwoFROM Person.Person AS ppINNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = ‘Johnson’;GOSELECT pp.LastName, pp.FirstName, e.JobTitleINTO dbo.EmployeeThreeFROM Person.Person AS ppINNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = ‘Johnson’;GO– Union ALLSELECT LastName, FirstName, JobTitleFROM dbo.EmployeeOne
UNION
ALL
SELECT LastName, FirstName, JobTitleFROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName, JobTitleFROM dbo.EmployeeThree;GOSELECT LastName, FirstName, JobTitleFROM dbo.EmployeeOneUNIONSELECT LastName, FirstName, JobTitleFROM dbo.EmployeeTwoUNIONSELECT LastName, FirstName, JobTitleFROM dbo.EmployeeThree;GOSELECT LastName, FirstName, JobTitleFROM dbo.EmployeeOneUNION ALL( SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree);GO
3. Report any issues, problems, troubleshooting, etc. at the end of your Word file