- #PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE CODE#
- #PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE SERIES#
- #PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE DOWNLOAD#
If yes, what is their phone number?Ī customer with a high-end store wants to know what the three most expensive wheels are for any bike.Ī customer, Gary Vargas from Exercise Center, wants to know what email we have on file for him.
#PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE CODE#
You will have to look at the data in the database and think about the query you need to code and result set you need to solve a problem.Ī customer comes in and asks if you carry medium sports shorts and what colors they come in.Ī customer asks if anybody at their company "Sharp Bikes" is a customer. In real life, schemas can also be used to manage. The AdventureWorks database takes advantage of schemas to group tables. Here is an example: Person.Address Person is the schema, and Address is the table. Separate the schema and table name with a period. The following questions will not specifically point out a table/columns that you need to work with. Actually, this is a good practice even if the database has just dbo. Sort the results by Color in descending order and then by Name in ascending order
Select all of the Products that have a name containing "Mountain Frame" or "Road Frame" and have a Color of "Red". Using your solution from question 4, add another condition to narrow your previous results to only include Products with a weight over 9000.
Select all of the Products that are Red and have a ListPrice between $500 and $1500 Select all the customers with a first name of "Mike" whose phone number starts with 454 There have actually been several different versions of this database as it evolved from the first edition in 2004 and then through the SQL Server 2008 CTP test period. Select all the customers who do not have a middle name. This database, called Adventure Works with SQL Server 2005 and AdventureWorks2008 with SQL Server 2008, is a sample database included with SQL Server. Sort the results by LastName, then FirstName, then CompanyName. Select the FirstName, LastName, and CompanyName of all Customers whose company name contains the word "Bike". …you can see that our resultset now starts at the row where OrderDateKey = 20030104 and CustomerKey=23120.Notice the schema name "SalesLT" in front of the table name? Questions If you compare the results of the following query with the results of the previous query: You could start off by downloading the AdventureWorks. We can also order our resultset by using an Order By clause, for example:Īnd there’s also a Start At clause where, for every column that appears in the Order By clause, you can specify to start the result set at the first row where a particular set of values appears I would imagine that this will be useful for pagination in reports. Answer (1 of 3): Since you are preparing for a Microsoft exam, there’s no better place to practice than MSDN. Here, I’m getting all the columns from FactInternetSales but only those rows where the OrderDateKey is greater than 20030101.
#PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE DOWNLOAD#
We can filter the rows we receive from a query by using the Filter function: Download the backup for the Adventure Works database with data from 2020 from the following link: GitHub Link (there is a backup for SQL Server 2019 and one for SQL Server 2016). Here’s what you see when you run this query in an MDX query window (!) in SQL Server Management Studio:Īs you can see, the Evaluate statement is at the heart of the new DAX query language: it returns the results of a DAX table expression as the results of a query. This returns every row and every column from the FactInternetSales table. The simplest DAX query we can write on this model is probably this: (I know I’ve not talked about how you would actually go about building a Tabular model yet, but this post from the BISM team is a good starting point and in any case it’s not too different from how you’d build a PowerPivot model)
#PRACTICE QUESTIONS ON ADVENTUREWORKS DATABASE SERIES#
Let’s take a look at how the language works…įor the purposes of this post and the others in this series I built a very simple Tabular mode with two tables from the Adventure Works database, DimDate, DimProduct, DimProductSubCategory, DimProductCategory and FactInternetSales.
It feels like years (in fact it has been years) since I’ve had anywhere near this much new functionality to learn and discuss here – it’s better than Christmas! And where better to start than with DAX queries? We’ve had DAX calculations for a while now, of course, but now in BISM Tabular mode we can actually write queries in DAX too. So at long last Denali CTP3 has been released and I can blog about all the cool new stuff in it.
0 kommentar(er)
