Basic SQL Queries For Beginners

8. Sub Query

A Sub Query can be called an Inner Query or nested query that can be used in a Where clause of a select, insert, update or delete statement.


9. Joins

So far we have seen all the somethings related to one table. Now we see how to join more than one table and display the results. Select statements will not be effective until we use a Join. The main purpose of using SQL Server is to use the normalization and increase the performance by displaying the records. With normalization we split large tables into small related tables. Now we need to display the related table data as a result of one single select statement. To accomplish this we use a Join in our SQL Select statement and combine all the data and display it.

Simple Join


Inner Join, Left Outer Join, Right Outer Join and Full outer Join

We can see each join example as in the following with comments.


10. UNION and UNION ALL

Before we see joins in SQL. In a join we join 2 or more tables and display all the common related result. If we need to display 2 or more tables to be combined and return all the data then we use a UNION or UNION ALL.

Here we can see UNION and UNION ALL. So what do you think about the difference between these two?

A Union will display only the unique results but a Union ALL will display the data with duplicate results also.

If we only need the distinct results of combining tables then we can use a UNION. If we need the results with duplicate data then we use the UNION ALL. That means it will display all the data.

Note: Both or All Select Column count, Name and data type should be the same to use a Union in SQL.

The syntax for using a union is like:
Example
11. Common Table Expressions (CTE) - With

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

We need to use the With clause in CTE. CTE will be very useful for displaying the records in some certain range. Here now for a simple example I have 2 dates with certain ranges, for example I have a Start_Date and an End_Date. Both have a 20 days difference. Now if I want to display all the dates with weekNo and WeekStart Day then as a result here we can see this query created using CTE.


In the following Multiple CTE sample I have used more than one CTE. Now let's see how to use the Multiple CTE. Here you can see in the following sample as in the preceding that will display all the days but with one more CTE added.

In this example I have used a UNION ALL.


12. View

Many people will be confused whether a view is the same as a select. In a view we use the same select query but we need a view. So what is the use of a view?

I will say a view will be very useful in actual projects.

For example we have a long select query with more than 20 columns and more than 4 table joins. We write one big query and for that suppose we need to use that query in other places we need to call that query. Let's see the uses of views in SQL.

1. Speed: Views can be used to increase the performance.
   
   
2. Security: If suppose we have an Order Detail Table and it has a total of items sold, a Total price and so on. Since those fields can be viewed only by the administrator or manager and for the user they just need to only see the itemname and ItemPrice. Then in that case we can create a view for the user to display only the itemName and Price.
   
   
3. If we need to join more than one table and need to display that in several places then in that case we can use a View.

13. Pivot

Why do we use a pivot in our SQL? So many people ask how to display the data from a row to a column. Yes we can do that using a Pivot. Using a Pivot we can display the rows as columns and display the average or sum of items of that. For example I want to display all the items with the price as a column and for that we can use a pivot. Now let's see an sample.

Here we can see an example that displays all the records of tblItemMaster with ItemName as a column and display the price in each row of items.


Here we can see one more important example that will display all the Item Names as columns. To use this first we need to add all the item names to a variable. Then we can use this variable to display as a pivot. See the following example.


14. Stored Procedure (SP)

I saw many times in Code Project many people asking questions about how to write more than one query in SQL Server and use them in our C# program. For example many people ask how to run a Select, Insert and Update at the same time in SQL. Well we actually cannot run all the queries at the same time but we can use Store Procedures to execute them all one by one. A SP is nothing but a function we can write once and use many times and also perform various tasks with different parameter values.

In our example now I want to insert a new item into a table Item Master but before inserting I want to generate my itemCode. To do that for example as a simple method I will use the count of records and add one value to it and generate my item Code.

Syntax
Example Select Sp with no parameter


Note to alter the SP we use an Alter procedure procedureName.

Select and insert SP


15. Functions

In this article we have already seen a few pre-defined system functions like MAX(), SUM(), GetDate() and and so on. Now let’s see how to create a user defined function.

If someone were to ask me what a function is and what is the use of a function then in a simple way I will say that if I want to execute or perform some action several times with a different meaning then I will create a function and call it whenever I need it. The following is the syntax to create a function.
Here we will see a simple function that will return the max row count of tblItemMaster.


Here we can see another function that will return the last date of a month from a given date.
16. SQL Server Coding Standards

Here are a few SQL Coding Standards sites. Kindly refer to these links and follow the SQL Standards in your queries.

• SQL Server Database Coding Standards and Guidelines.
• SQL Server – Database Coding Standards and Guidelines – Part 1

17. Few Working Examples

Example 1: To Display Every Week Sunday.

In some case we need to display all the days that should be Sunday, Monday and so on. For example to list all the days of work that start on Sunday.

Here now let's see our following example. I have the From Date and the To Date. Now I want to display all the Sundays alone between this range.

For this we can use our preceding CTE, the same example. Now using the CTE we get all the dates between 2 dates. Now what we need to do is to select only the Sunday dates. I have used the temp table to store all the Sundays and display the result.
Points of Interest

If I have missed any SQL query that you want to understand then kindly leave me a comment, I will be happy to add those with examples to this article. I have attached a Zip file that has this article as a Word file for your easy access.