Module 2: Summary and Quiz

Previous Tutorial - Next Tutorial

In this module, we learned some core language elements, such as clauses, expressions, and statements, and then we will explore the general structure of the SELECT statement and also learn the role of other clauses associated with it. Then we had learned each phase involved in the logical query processing.

We had learned what happens in each phase according to logical query processing. Then we learned how we can filter data using the WHERE clause. We also learn about the different types of operators used with the WHERE clause. We had learned how we can sort data using ORDER by clause and see how it sorts the results based on the specified columns. Then we learned how we can group the data using the GROUP by clause.

We had also gone through some aggregated functions used with the GROUP by clause. Then we had learned how TOP(n) clause helps us to return only a limited number of rows, we had also explored ties options which will include the additional rows if they're value match the values of the last rows. Now you have a basic understanding of T-SQL fundamentals.

Let's play one small quiz. In this quiz, we will go through some queries and you need to think about the answer. Let's start with this simple SELECT query here, if I select only the SELECT statement then it will show me the record.

Now, WHERE clause evaluates to true, then it will return this data. Now, in the WHERE clause, I have given the conditional null equal to null. What do you think? If I execute this record, do you think it will give me any result? You can think of it by pausing this video. Now let us execute this query. Why I have not got any record over here. As I said earlier, that null is unknown in SQL server, so unknown can never be equal to unknown. That's why if I execute this query, I will never get this value over here.

Now let us move to another query. If I execute this SELECT statement, I should get 10 records. Think about it and pause this video. Now let's execute this query. Why I got this 1 record instead of 10, think of this query like this, like if I execute this query. I will get one row. This is the reason I will get the one row in the count statement.

SQL Summary

Now let's move to another query. If I execute this SELECT statement, then I will get the total 504 records. Now in this count aggregate function, if I specify this color column. What do you think? How many records I should get, 504 or some other value? Let us execute this query. So why do I get 256 instead of 504? So let us execute this query again.

Here, if you note that there are some records, which contain a null value in the color column. In the count aggregate function, if I specify the column name, then it will ignore the null value instead of the column name. If I write star, then it will include the null value. I will get the 504 records.

Now let us move to another query, Now in the count function, if I specify the column name in the single code, what do you think? How many records will I get? Let us execute this query. This time I will get 504 records.

Please note that I will define the column name in a single code. Now if I specify any value in a single code in the count statement, then it becomes a constant. You can think of this query like this (SELECT ‘color’ FROM Prodcution.Product).

Now, if executed this query, we get 504 rows over here the single code value is a constant, It will give me the total number of records from this product table. Let us move to another query. Now, what if I specify null in the count statement? I think it should return 504 records. Let us execute this query. This time we'll get the exception, which means I cannot specify the null in the count aggregate function.

Now, let us move to another query. If I execute this query, it will give me the product data now for this list price column. If I specify the MAX value, then it should return me the maximum number of records of the list price column.

Now instead of this list price column, what if I specify the name column? Do you think this query will return me any results? I think this query should return an error. Let us execute this query. I have not got any error and I will get this product name. So when you use the max aggregate function with an alphanumeric column, then it will give you the record as per the alphabetically.

SQL Quiz

Let us move to the end of the query. In the ORDER by clause, I specified the one and two. I think this query should return an error. Let us execute this query. Instead of an error, I will get the record. Here one indicates the name column and two indicates the color column. You will get this record based on the order by name and then the color column.

In this query now in the like operator, what if I specify the percentage in the last what does it indicate? Let us execute this query. Here you will notice that if I specify the percentage in the last, then it indicates that it will return me only those products whose names start with the LL.

Let's move to the end of the query. Here I have used one alias column. Now, what if I execute this query? Do you think it will give me any result? Let us execute this query. Here, why do I get the invalid column name? In this case, remember our logical processing query order. In this query WHERE clause occurs before the SELECT clause.

The alias defined in this SELECT clause is not accessible in the WHERE clause. That is the reason we get this error. Now let us execute this query, how many of you think that this query should give me any result? Let us execute the query. This time I will get the record instead of an error. So again, let us remember our logical query processing order.

As for the logical query, processing the ORDER by clause occurs after the logical processing of the SELECT clause. The alias column, defined in the SELECT clause, is accessible to the ORDER by clause. That is the reason this query will execute successfully and it will give me the result.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: