![assignments on adventureworks database assignments on adventureworks database](https://cwebbbi.files.wordpress.com/2013/05/image_thumb17.png)
You can go through this link that gives a brief overview of schemas used in AW A simple query against each table will give you a fair amount of information about data. The table names are self-explanatory to a great extent.
![assignments on adventureworks database assignments on adventureworks database](https://miro.medium.com/max/1400/1*9zwPs3vJMQ8pB2T-4SinLg.png)
The schemas involved in AW database are: HumanResources, Person, Production, Purchasing, Sales. The schemas help a lot in understanding their business, sales, employee data and product info. If reading is not feasible I will show you another approach:ĪdventureWorks (AW) is an imaginary manufacturer and seller of bicycles and its accessories (Table Production.ProductCategory). So I suggest you read the first 3 chapters of this book as it simplifies learning the business. From sales to technical people and others. The business requirements ranges from a variety of people. This is the best book that will give you a detailed understanding of AW business. I am currently going through this book- The Microsoft Datawarehouse Toolkit by Ralph Kimball. You will only understand those things that I will describe here. Sort b圜ostRate then Name.I can go on explaining the business of AW but it will consume a lot of time as I cannot completely give the business understanding. Sort results by MonthlyPay in descendingorder.Įxercise15 Write a query that retrieves theLocationID, Name, CostRate, and Availability for all locations witha CostRate and Availability more than 0.00 and a Name that startswith the letter F or S using the Production.Location table. Limit the query tothe first 3 managers.Įxercise 14 Write a query using theHumanResources.EmployeePayHistory table that retrieves theBusinessEntityID, Rate, PayFrequency for all employees that getpaid once a month, and calculate the monthly gross pay based on a40 hour work week with 4 weeks in a month and store the results ina field name MonthlyPay. Sort the results by Namein ascending order.Įxercise 13 Who is a manager? List the Name ofall managers in the Person.ContactType table. Exercise 12 Write a query that retrieves the Nameand Currenc圜ode for all Currenc圜ode that begin with A-C and endwith R-T using the Sales.Currency table. Sort the results by StartDate thenBusinessEntityID. Also, create a new column named age and calculate the daysbetween the start date and end date.Įxercise 11 Using the same table as Exercise 9,write a query that retrieves all fields with a NULL EndDate and aTerritoryID of 1.
Assignments on adventureworks database full#
Exclude all NULLs and sort by Full Address in ascendingorder.Įxercise 6 Write a query that retrieves allfields with a CardType as SuperiorCard, the ExpMonth of 11, and theExpYear as 2006 from the Sales.CreditCard table.Įxercise 7 How many items in theProduction.ProductCostHistory table have a StandardCost more than10.00? List the Item ProductID and StandardCost.Įxercise 8 Write a query that retrievesQuantity and ProductID for ShoppingCartItemID 1 through 4 using theSales.ShoppingCartItem table.Įxercise 9 Write a query that retrieves theBusinessEntityID and TerritoryID for BusinessEntityID’s that startwith 28 using the Sales.SaleTerritoryHistory table.Įxercise 10 Using the same table as Exercise 9,write a query that retrieves the TerritoryID and BusinessEntityIDthat have a start date of and end date of Nov. sql)Įxercise 1 Write a query that retrieves thecolumns ProductID, Name, Color and ListPrice from theProduction.Product table, with no filter.Įxercise 2 Continue to work with the previousquery and exclude those rows that are 0 for the columnListPrice.Įxercise 3 Write a query that retrieves theName of all stores with “Bike” in the Name from the Sales.Storetable in descending order.Įxercise 4 Write a query that retrievesAddressLine 1, AddressLine 2, City, and PostalCode who do not haveNULL for AddressLine2 from the Person.Address table.Įxercise 5 Using the table in Exercise 4retrieve AddressID then combine AddressLine 1, AddressLine 2, City,and PostalCode into a column named FullAddress with commadelimiters.
![assignments on adventureworks database assignments on adventureworks database](https://s33046.pcdn.co/wp-content/uploads/2017/04/word-image-21.png)
![assignments on adventureworks database assignments on adventureworks database](https://www.coursehero.com/thumb/8b/39/8b39cf56cc6a8ac3c196e6157c786f59e12c47a3_180.jpg)
For this assignment, you will use the AdventureWorks Database tocomplete the following exercise.