Pass Your Microsoft Azure DP-203 Exam Easy!

Microsoft DP-203 Data Engineering on Microsoft Azure exam dumps vce, practice test questions, study guide & video training course to study and pass quickly and easily. Microsoft DP-203 Data Engineering on Microsoft Azure exam dumps & practice test questions and answers. You need avanset vce exam simulator in order to study the Microsoft Azure DP-203 certification exam dumps & Microsoft Azure DP-203 practice test questions in vce format.

Design and implement data storage - Overview on Transact-SQL

1. Section Introduction

Hi and welcome to the section in which I am just giving an overview of the basic SQL commands. When it comes to Microsoft Transact-SQL, this is the case. SQL Server. Now obviously, if you are well versed with SQL-based commands, then you can skip this section and move on to the next section, wherein we will be looking at SQL data warehouses. The main reason I'm having this section in place is for those students who might not be so familiar with SQL-based commands. So the basic commands in Sequel when it comes to the select, the where clause, the group by clause, and the join clause are all very important when you work with a SQL data warehouse when it comes to Azure Synapse. That's why I don't want to make any assumptions when it comes to what a student knows. So I said if you're already familiar with sequel-based commands, yes, you do have the option of skipping this particular section. But then if you really want to just go through the commands—it's just a quick section—you can take the section and then move on to Azure Synapse. Another main reason as to why I am going through these TSQL commands is that it is important to also understand how these commands actually work in terms of performance when it comes to your synopsis. So when it comes to deciding the types of tables that you create in Azure Synapse, for example, hash distribute tables, replicate tables, or round robin tables, So designing your tables also depends on the type of queries that you're actually going to fire against your data. That's why understanding SQL commands is very important. That's why I had him sectioned based on this. So let's move ahead.

2. The internals of a database engine

Now, in the next chapter, we are going to create a new SQL database. In the prior chapters, we have seen how to create a SQL database that is being hosted on a SQL database server. We will create another SQL database on that same SQL database server. Before we go ahead, let me just make some very important points. So a database is nothing but a collection of tables. And in the tables you have data, you have other aspects that are also available as part of the SQL database, such as views, stored procedures, et cetera. So the tables in the underlying database are used for storing structured data. You can then fire queries against the SQL database. The underlying SQL Database Engine is responsible for taking your queries and then giving you the desired results. So for example, if you want to go ahead and fetch rows from an underlying table in the underlying database, the database engine will then process the operation, fetch the rows, and then return it back to you. Now, when a database engine runs, the server itself works with the underlying database. One very important thing to remember is that, in the end, your data is stored on underlying storage for you. Everything is represented, has accessible data, and has structured data. Remember that your data is stored on underlying physical disks. So when the database engine needs to work with data and needs to, let's say, fetch data and then return it back to you, the database engine, let's say in this case Microsoft SQL Server, needs to go on to the underlying storage device, fetch that data, and then give it to you in the format that you desire. Now, why am I bringing this up? A very important note is that when you go onto AzureSynapse, when we look at a SQL data warehouse, understanding this basic concept that your underlying data is stored on underlying physical infrastructure is very important because over there, a SQL data warehouse is used for storing terabytes of data or even petabytes of data. And all of the data, remember, is, in the end, on disk, on underlying physical infrastructure, on underlying storage devices. So the Engine, which is responsible for processing the request that you fire against the SQL data warehouse, needs to be efficient enough to ensure that it can go through all of the data on the underlying storage devices and then give you the desired results. That's actually the power of a SQL data warehouse—its ability to actually process large amounts of data. So I said that for you, in the end, when it comes to structured data, when it comes to a structured SQL database, everything for you is in the form of tables, in the form of columns, in the form of rows. And that's because the underlying database engine is doing all of that work in the background to give you that sort of representation of data. As I said, in the end, all of the data resides on the underlying physical disc on the underlying physical infrastructure. Right. With this point in mind, let's move on to the next chapter.

3. Lab - Setting up a new Azure SQL database

So here we are back in the Azure Portal. Now I'll select Create from the list of available resources. So I want to create a new SQL database. I'll go ahead and choose the Sequel database option. I'll choose my resource group, and that's Data GRP. Yeah, I'll give a database name now in terms of the server. So I want to choose the same server. So I have my server in the North Europe location; that's part of the Data GRP group. This server is something that I created early on when I was actually practising myself for building this course. So we're going to choose the server that we have been using as part of this course. So I'll go ahead and choose that. Now here, when it comes to compute and storage, I'll click on "Configure Database." I want to ensure that I choose a cost-effective option, so I'll choose Standard again. I'll reduce the size of the underlying database. I'll hit "Apply." I'll go on to Next for networking. I'll leave everything as it is, go onto security, and leave everything as it is. I'll go on to additional settings. And over here, in terms of using existing data, I'll choose Sample. So this particular configuration will now create a sample database. So there's a sample database known as AdventureWorks, and this database will now be installed. The data will be in place in the form of tables, and it will be part of this new database (new DB), and then we can work in the subsequent labs when it comes to the TCQ statements we will be seeing when working with the tables in the underlying database. I'll go on to Next to Tags. I'll go on to review and create. And finally, let me go ahead and hit on Create. Now, this might take just around three or four minutes. Let's come back once we have the database in place. Now, once the database is in place, you can move on to the resource. So remember, this database is on our same database server, which I created earlier on. Now, in SQL Server Management Studio, I can connect to the same database server, and I should see both of my databases. I should see DB, and I should see a new DB as well. So if I go onto databases, I can see a new DB over here. If I go and expand my tables, I can see different tables already over here. So we have information about customers, information about products, information about the product model, et cetera. This is part of the sample data that gets installed as part of the Adventure Works database. And if you want to play around with data, this is a good option to start with.

4. Lab - T-SQL - SELECT clause

Now in this chapter, we'll just quickly go through the select statement. Now, this ID that I have is Visual Studio code. So you're not going to be running any code from here. This is to give a good representation of what I want to show you in terms of the SQL statements. If you want to add visuals to your code, I'll keep the link in this chapter as a reference. Again, it's a free, available tool. So the select statement is used to predict the columns that you want to see. Select "Star" from the Sales List Product, for example, if I issue this statement. So Product is the name of the table, and Sales Lot is the name of the schema. If I go ahead and take the statement, if I go on to New DB, let me right-click and issue a new query, and I'll paste in my statement. I'll hit Execute. Here you will see all of the information that is present in the Sales List Product table. Here, you can see all of the columns that are being displayed to you. If you want to see only certain selected columns from that particular table, you can give the names of the columns that you want to see. This is the production when it comes to the selected statement. So I can go ahead and just highlight this and hit on Execute.And I'm only seeing the information from those three rows. Then, if you just want to see the number of rows in a specific table, do Account Start and press Execute. And here, if you want, you can see the number of rows in the particular table if you want.See here, there's no column name. So you can go ahead and name the column an alias, say Count, and click Execute. And here you can see a name assigned to the column when it comes to a SQL data warehouse. So I'm going to be making a reference to the SQL data warehouse very often when we go through these T SQL commands because it's very important. So when you're looking at the columns that you need to return as part of your query, this is also important when it comes to a SQL Data Warehouse. So when SQL Data Warehouse actually stores the data on the underlying disk, it actually does so in a columnar format. And the reason for this is because it actually increases the efficiency of the queries that are actually fired against the SQL data warehouse, right? So in this chapter, I just want to go to the select clause, which is available as part of your SQL statement.

5. Lab - T-SQL - WHERE clause

Let's go through our next SQL file. So this is the where clause. So the where clause is used to extract only those records that fulfil a specified condition. So over here, let's say I want to select the product ID, the name, and the product number. So only certain columns from the Sales LT product table where the product ID is equal to 680 So I only want a particular row or a set of rows wherein the ProductID is equal to the value of 680. So let me go ahead and paste it over here, then hit execute. So I'm only getting this particular row of data. I have another statement over here. I want now to get all of the rows where the product ID is greater than 680. So I can go ahead and just change it over here and hit execute. And now I'm getting all of the rows where the park ID is greater than 680. Next, I want to fetch the rows where the pub ID is between a range of values. Over here, I'll go ahead and hit execute, and we get the desired result. And finally, where's the name? Now I'm looking at the name where it's actually equal to a string that contains the keyword "silver." So over here, the person means that we can have a number of characters before silver and a number of characters after silver. So let's go ahead and also execute this, and we can see all of the desired rows. So again, when we are making a reference to the SQL data warehouse when we use the where clause, when it comes to queries against the SQL data warehouse, we should be using something known as partitions in the SQL data warehouse to ensure that we increase the efficiency of queries that are doing filtering. So when you're adding the where clause, you're basically filtering the results. You only want results based on a certain condition. If you want to increase the efficiency of the queries that are fired when you have these filtering conditions, then you have to ensure that you use something known as partitions. and obviously this is something that we are going to look at when we look at the SQL data warehouse.

6. Lab - T-SQL - ORDER BY clause

Let's go to the next sequel file. So this is the order by clause. Now, the order by clause is used to sort the result set in either ascending or descending order. By default, the records are sorted in ascending order. So I'm selecting all of the rows from the SalesLt product table and sorting them by list price. So I'll just replace it over here and hit execute. and I'm seeing now everything that is being sorted as per the list price. If you want to list it in descending order, simply enter the descending keyword and press the execute list price button, or the rows will be displayed to you in descending order. So, in this chapter, I'll just go through the order clause by clause.

7. Lab - T-SQL - Aggregate Functions

Let's go on to our next SQL file. Now, here I am looking at aggregate functions. Now, aggregation is very important when it comes to the analysis of your data. So more often than not, you will actually be performing an aggregation of your data to look at different stats about the data itself. Here, let's say I want to find the number of products. I'm using the product ID over here where the product name is. Like I said, it contains the name "silver." It contains a string of silver. You can go ahead and copy this, and I can execute over here. So I can see that I have 30 rows that actually fulfil this condition. If you want to find the Max Proctor ID, you can go out and execute that as well. So the Max Product ID is nine, eight, and similarly, you can go find the Minimum ProductID and hit Execute to get the Minimum Product ID where this condition is met. If you want to find the sum or the average, you can go ahead and actually use these aggregation functions. We'll also be using these aggregate functions in Azure Stream Analytics because normally data engineers will use aggregate functions when it comes to their streaming data.

8. Lab - T-SQL - GROUP BY clause

Now let's go on to the next sequel statement. Now this is the Group I clause, probably one of the most important clauses. So the group I keyword is used to group the rows into summary rows, and it is normally used along with aggregate functions. So over here, I want to see the count of the product ID from the Sales, Ltd. product table, and I'm grouping it by color. So the colour column value, if I go ahead and copy this and replace it, Over here. Hit on Execute.So here I can see the number of product IDs that are grouped by the color. I can go a step further and give an alias to the count of the product ID because over here there is no column name. So I can take this, copy it over here, and hit Execute to see that our column now has a name. So I can take it a step further with the product ID account. I can now see the colour as well, so I can see a better representation of my data. I'll hit "execute," so now I can see the color, let's say black. We have 89 product ideas that match this color, so we can see all of these details. Here you can see that there are 50 products that have no colour at all. Null values are again very important when you're working with your data. How do you actually consider rows that have null values? Sometimes you might want to put a default value over there if there is a null value for that particular column. Or you might totally discard the rows that have a null value for a particular column. You might actually consider discarding those rows if they don't add any meaning to the final results set on which you are trying to form your analysis. Performing or giving a default value is also sometimes a risk because it can skew your data. So you should always think very carefully about your performance analysis and your data. What are you going to do for those roles that actually have a null value? So over here, if you only now want to see an account of the products wherein the colour is not null, we can go ahead and execute the statement, and we're getting the results as desired. Now, when it comes to the group by clause in the SQL data warehouse, that is again a very important concept. And over there, when you want to increase the efficiency of the group by crossing, you will actually be using different types of tables. So we have hash-distributed tables, and we also have something known as hash-replicated tables; we also have round-robin tables. But when it comes to the group by clause, and we're going to see this later on, we are going to be working with something known as fact tables and dimension tables. And normally you use grouping when you're workingwith your fact and your dimension tables. And at that point in time, to have an effective distribution of data, you'll actually create tables—different types of tables. So in the SQL data warehouse, hash the distributed table types and replicated distribution table types. So we'll be looking at all of these table types when we are looking at the SQL data warehouse.

Go to testing centre with ease on our mind when you use Microsoft Azure DP-203 vce exam dumps, practice test questions and answers. Microsoft DP-203 Data Engineering on Microsoft Azure certification practice test questions and answers, study guide, exam dumps and video training course in vce format to help you study with ease. Prepare with confidence and study using Microsoft Azure DP-203 exam dumps & practice test questions and answers vce from ExamCollection.