iopbrilliant.blogg.se - Adventureworks2012 queries

#Adventureworks2012 queries iso

#Adventureworks2012 queries iso

Lookup table containing standard ISO currencies.Ĭurrent customer information. Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts. One way hashed authentication information Lookup table containing the ISO standard codes for countries and regions.Ĭompanies from whom Adventure Works Cycles purchases parts or other goods.Ĭross-reference table mapping ISO currency codes to a country or region. Lookup table containing the types of business entity contacts. See PurchaseOrderHeader.Ĭross-reference table mapping stores, vendors, and employees to people Individual products associated with a specific purchase order. Source of the ID that connects vendors, customers, and employees with address and contact information.Ĭross-reference table mapping vendors with the products they supply.Ĭross-reference table mapping customers, vendors, and employees to their addresses. Types of addresses stored in the Address table. Street address information for customers, employees, and vendors. Résumés submitted to Human Resources by job applicants. Record of each purchase order, sales order, or work order transaction year to date.

Manufacturing failure reasons lookup table. Lookup table containing the departments within the Adventure Works Cycles company.Ĭustomer reviews of products they have purchased.Įmployee information such as salary, department, and title.

Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY.CATCH construct.Ĭross-reference table mapping products and product photos. Data is captured by the database trigger ddlDatabaseTriggerLog.Īudit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY.CATCH construct. Production.ProductModelProductDescriptionCultureĬross-reference table mapping product descriptions and the language the description is written in.Īudit table tracking all DDL changes made to the AdventureWorks database. (Last updated on Thu, Nov 26th, 2015 at 12:25 PM)Ĭurrent version number of the AdventureWorks 2012 sample database. INNER JOIN Sales.SalesTerritory AS sst ON soh.TerritoryID = sst.Generated using SQL Data Dictionary demo version. Here’s the query: USE AdventureWorks2012 In this case, we need to use the WITH TIES clause when defining the TOP statement. In this case, the number of orders being returned may be more than 100

Customers with TOP 100 orders by total value = this means that we need to first fetch the TOP 100 order totals and then look for all customers which have orders with these totals.

TOP 100 orders by total value = this means that we should have only 100 orders being returned, in the order defined by the ORDER BY clause.

These two requirements are different, and here’s why: This query would work in most cases, but analyzing the requirement, we realize that we don’t want the TOP 100 orders, we want all customers with the TOP 100 orders. INNER JOIN Sales.SalesTerritory AS sst ON soh.TerritoryID = sst.TerritoryID

The following query is the first one that comes to mind: USE AdventureWorks2012 Please provide the list of customers with TOP 100 orders from Australia with the highest order totals. When working with the AdventureWorks database, assume that we have the following requirement: