Queries for AdventureWork Database

For this assignment, assume you have just been hired by Adventure Works Cycles company. As part of the orientation process, you have been asked to learn a little about the company by reading the “Adventure Works Cycles Company Story.”

Your supervisor also wants you to gain an understanding of the content included in the company database. To do this, you must utilize SELECT and FROM statements like the example provided below.

EXAMPLE: SELECT * FROM [Database].[Schema_Name].[Table_Name]

Access the “AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3” database and use SELECT and FROM statements to find the answer to each of the questions below. Create a Word document that includes the SQL queries used to explore the database tables and answer the following questions using the queries completed in steps 1-5.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to an Excel file for a visual check of accuracy.

  1. Locate the “Person” table and run a basic SELECT query as listed in the example. List all the available fields and how many records exist in that table.
  2. On what table and schema would you locate an applicant’s resume data?
  3. When is the CEO’s Birthday?
  4. What is the list “ListPrice” of the product “HL Touring Seat Assembly?”
  5. Is “Holiday Skate & Cycle” a preferred vendor? 

Solution 

1)_

List all the available fields of the table Person:

The table Person exist in the schema Person.

Query:

SELECT * FROM [Person].[Person]

how many records exist in that table:

Query: SELECT count(*) FROM [Person].[Person]

2)_

Applicants resumes data are in the table JobCandidate in the schema HumanResources

3)_

The CEO birthday is in the column birthday of the table Employee in the schema HumanResources

4)_

The ListPriceof the product “HL Touring Seat Assembly” is:

196.92

Query: SELECT ListPriceof From [Production].[Product] WHERE Name=’HL Touring Seat Assembly’

5)_

Yes is a prefered vendor

Query: SELECT PreferredVendorStatus FROM [Purchasing].[Vendor]