Design and implement data storage - Overview on Transact-SQL

6. Lab - T-SQL - ORDER BY clause

Let's go to the next SQL 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 here I am selecting all the rows from the sales as the product table, but I'm ordering them by the list price. So I'll just replace it over here and hit execute. And I'm seeing everything that is being sorted as per the list price. If you want to list it in descending order, just add the descending keyword over here and hit execute. And now the list price or the rows are being displayed to you based on the descending order of the list price. So in this chapter, I just want to quickly go through the order by clause.

7. Lab - T-SQL - Aggregate Functions

Let's go on to our next sequel file. Now, here I am looking at aggregate functions. Aggregation is now very important when it comes to analysis of your data. So, more often than not, you will actually be performing aggregation of your data. To look at stats about the data itself here, let's say I want to find the number of products. I'm using Product ID over here. When the product name is like Isai, it contains the name of silver. It contains a string of silver. You can go ahead and copy this, and I can execute it over here. So I can see there are three things that actually fulfil this condition. If you want to find the Max Product ID, you can go ahead and execute that as well. So the Max product ID is nine, eight, eight. Similarly, you can also go ahead and find the minimum Product ID and hit Execute so you get the minimum Product ID wherein this condition is being fulfilled. If you want to find the sum, or if you want to find the average, you can go ahead and actually use these Aggregation function. We’ll also be using these aggregate functions in Azure Stream Antics 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 this is used normally along with aggregate functions. So over here I want to see the count of the product ID from the sales table and I'm grouping it by the color. So the color 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 on to the count of the product ID because as you can see over here, there is no column name. So I can take this, copy it over here, and it will execute. So I can see now a name is being given to our column. So product ID counts. I can take it even a step further. I can now see the color as well, so I can see a better representation of my data. I'll hit on execute. So now I can see for the color, let's say black, we have 89 products that match this color. So we can see all of these details. Here you can see that there are products which have no color at all. Null Values: Again, very important when you're working with your data is 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 the null value for a particular column. You might actually consider discarding those rows if they don't give any meaning to the final result set on which you are trying to form an analysis. Performing or giving a default value is also sometimes a risk because it can sometimes skew your data. So you should always think very carefully about your data performance analysis.

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 color 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, again a very important concept. And over there, when you want to increase the efficiency of the group pi cross, you will actually be using different types of tables. So we have hash distributed tables and we also have something known as replicated tables. We also have round robin tables. But when it comes to the group by clause and you are 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 are working with your fact and dimension tables, and at that point in time, to have an effective distribution of data, you'll actually create tables, types of tables. So in the SQL data warehouse, you have hash distribute table types and replicated distribution table types. So we will be looking at all of these table types when we are looking at the SQL data warehouse.

9. Lab - T-SQL - HAVING clause

So I'll go on to the next file. So this is just the having clause. So here, the aggregate functions of the sum count cannot be used along with the where clause. So you can actually use it in the possessing clause here. The only difference is that I only want to have a result set wherein the number of products that actually match a particular color is greater than ten. That's it. If I take the statement, if I place it over here and hit execute, we are only getting those rows. Then where the number of products is greater than ten, the count of the products based on a particular color is greater than ten.

10. Quick Review on Primary and Foreign Keys

Now in this chapter, I just want to have a quick review when it comes to primary keys and foreign keys. So, primary keys are used to uniquely identify rows within a particular table. So if you are certain that you don't have duplicate rows in place, you can go in and enforce the primary key constraint on a particular table. This is a feature that is available in most SQL database systems, and then you have the foreign key. So, if you have one table that has a dependency on another table, you can have a foreign key constraint. If I go on to my Object Explorer, when you look at the sample tables that we have over here, I'm sure that there is a relationship between the tables over here.

So if I consider the product, the product category, the description, and the model, I want to see what the relationship between these tables is. If I, for example, go on to the sales table, if I go on to the keys, So here I can see that there is an primary key that is based on the product ID. So this means that the Product ID value is unique for every row. That means you can't have two rows that have the same product ID. I can see that there are also foreign key constraints that are being mapped onto other tables. So there is a relationship between the product table and the product category table, and there is a relationship between the product table and the product model table. If you want to see the visualization of the relationships again, you can actually do it from SQL Server Management Studio. You can right click on database diagrams. You can click on the new database diagram here. It will give you a message that one or more of these support objects required is not in place.

Do you want to create them? I'll click "yes." Then it should give me a screen wherein I can choose which tables I want to add on to my database diagram. So I'll choose my product table, I'll choose my product category table, and my product model table. I'll click on Add, and it will add all of these tables onto my database diagram. Once I have the tables in place, I'll close this. So here you can see that we have an primary key that is defined on the product table.

There is also a primary key on the product category table and a primary key on the product model table. There is also a relationship between the product table and the product category table, and there is a relationship between the product table and the product model table. Here the columns that are actually mapping tables are so we have the product category ID in the product table.

This is actually being mapped onto the product category id column that we have in the product category table. And then we have the Product Model ID column that will be mapped on to the Product Mode lid column in the Product Model table. When it comes to understanding the relationships between tables, this is going to be a very important concept that we will be learning in the sequel data warehouse. So based on relationships, based on the columns, you can actually construct something known as our Dimension Tables, which will be used in something known as the star Schema, and that's something that we're going to look at in detail at this point in time. Just a quick review to keep in mind that you have your primary keys and you have the foreign keys that are used to depict the relationships between the tables.

Prepared by Top Experts, the top IT Trainers ensure that when it comes to your IT exam prep and you can count on ExamSnap Data Engineering on Microsoft Azure certification video training course that goes in line with the corresponding Microsoft DP-203 exam dumps, study guide, and practice test questions & answers.