Write a Transact-SQL query to create a view for customer addresses in Canada.Your manager has asked you to make sure that the team can get the data they require, but ensure that they don’t access the underlying source data when getting their information. This team will need access to address details on Canadian customers only. The team wants to create a map of all of the customer addresses in Canada. Challenge 1: Create a viewĪdventure Works is forming a new sales team located in Canada. If you get stuck, suggested answers are provided at the end of this lab. Tip: Try to determine the appropriate code for yourself. Now it’s your turn to use table expressions. Your derived table enabled you to easily build on top of your initial query based on your additional criteria, without making the initial query any more complex. Your new code uses that derived table and fetches the ProductID, Name, and ListPrice of products that have a PriceType of High only. You’ve created derived table based on your previous query. Replace the previous code with the code below: SELECT DerivedTable.ProductID, DerivedTable.Name, DerivedTable.ListPriceĬASE WHEN ListPrice > 1000 THEN N'High' ELSE N'Normal' END AS PriceType In order to do this, you can create a derived table for it. But you’d like to be able to further build on this query based on additional criteria, without further adding to its complexity. The query calculates whether the price of a product is considered high or normal. WHERE ListPrice 1000 THEN N'High' ELSE N'Normal' END AS PriceType In the query editor, replace the code you entered previously with the following code: SELECT ProductID, Name, ListPrice For example, you can use your view to get a list of any road bikes based on their ListPrice. ![]() Select ⏵Run to run the code and create the view. This code creates a view called vProductsRoadBikes for all road bikes. But what if you wanted to use a view for this data to ensure applications don’t need to access the underlying table to fetch it? Replace your previous code with the code shown below: CREATE VIEW SalesLT.vProductsRoadBikes AS The query returned all products that are categorized as road bikes. Enter the following query to retrieve all products that are classified as road bikes ( ProductCategoryID=6) from the SalesLT.Products table: SELECT ProductID, Name, ListPrice In the new SQLQuery_… pane, use the Connect button to connect the query to the AdventureWorks saved connection.Īfter you’ve connected to your database, you can query it. Start Azure Data Studio, and create a new query (you can do this from the File menu or on the welcome page). You use the CREATE VIEW statement to create a view. Note: If you’re familiar with the standard AdventureWorks sample database, you may notice that in this lab we are using a simplified version that makes it easier to focus on learning Transact-SQL syntax. For your reference, the following diagram shows the tables in the database (you may need to resize the pane to see them clearly). Additional SQL Server help and feedbackĭid you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.In this lab, you’ll use table expressions to query the adventureworks database.Microsoft SQL Server License Terms and Information.Reddit: General discussion about SQL Server.Stack Overflow (tag sql-server): Answers to SQL development questions.DBA Stack Exchange (tag sql-server): Ask SQL Server questions. ![]() Ideas for SQL: Have suggestions for improving SQL Server?.Run R or Python scripts from Transact-SQL. ![]() Learn how to work with SQL Server data using R or Python. Learn how to create basic reports and subscriptions. Learn how to set up and run replication topologies using SQL Server Management Studio. Learn how to create and deploy SSIS packages. Learn how to manage information in an enterprise using Microsoft EIM technologies. Learn how to connect to an instance of the database engine and then build and run Transact-SQL statements.Įnterprise Information Management (EIM) Tutorials Also learn how to use tools such as PowerPivot to analyze the data in the models. Learn how to develop and deploy Analysis Services tabular or multidimensional models. Power View support is no longer available after SQL Server 2017.Ĭheck out the new Wide World Importers sample available on the sql-server-samples GitHub repository.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |