Basic SQL Queries For Beginners

Table of Contents

1. Create and Insert Query
2. Simple SQL Select Query
3. Aggregate and Scalar Function
4. Date Function
5. Other Select Function
6. Where
7. Group BY and Having Clause
8. Sub Query
9. Joins
10. UNION and UNION ALL.
11. Common Table Expressions(CTE) - With
12. View
13. Pivot
14. Stored Procedure (SP)
15. Functions
16. SQL Server Coding Standards
17. Few Worked Examples

1. Create and Insert Query

Before we start we will first create a table and insert some sample data into it so we can use these tables in our select class. I want to explain the table design with actual data since that will help the reader to understand this in detail.

In database design the important phase is to create a table with proper normalization with primary and foreign key relationships.

Now in this example we will assume we need to create a restaurant Order Management tables with relationships.

For this we need an Order Master, an Order Detail and an Item Master as the major tables. We will use these 3 tables in this article. First let's start by creating the tables. As I have said, we will use the 3 tables here in this article so we start by creating the Item Master as the first table. As the name suggests this table will be used to store the items.

Create Table

Item Master: Here we have created an ItemMaster with the necessary fields. As I already said, we need to make a plan about our project. List all the necessary tables we need to create for the project and describe and list all the fields to be created for each table. Here I used Item_Code as a primary key field that will be used in another table for the reference to this main table.

Insert Item Master
Order Master: Since this is a master table we will have one main record and all the subsequent related records will be stored in the Order Detail Table.

Note: First please understand what Master and Detail means. If you don't understand what a Master and a Detail are then I will explain that first. Master means there is one main record and in the details we have all the details of the main record.

Say for example we have a restaurant and an order for one Coke, one Burger and one Potato Fries. Which means I have make an order from the waiter with 3 Items. So the Order_No for the example can be “Ord0001” and this Order_No will have the 3 items so first we create the Order Master.
Insert Order Master
Order Detail: As table name suggests this will have the details of an order so for example we consider my preceding example order with the 3 items. First we create an Order Detail table.


Here we can see the same ordre_No for 3 different details with different items. Refer to the Item Master for the Item Name details. We will see in detail the select query uses the following in this article. Now we insert another Order Master detail into the Detail tables.
2. Simple SQL Select Query

Can someone tell me what a Select Query in SQL is? A Select is the one basic and most important DML statement of SQL. So what is DML in SQL? DML stands for Data Manipulation Language. That means these statements are used to manipulate the data that already exists. As the name suggests the SELECT statement selects one or more columns with one or more data to display from our DB with our optional filters.

For example now I want to display my Name in the SQL Server. So as a result I will use the select statement here as an example.


Select Statement from Table


3. Simple Aggregate and Scalar Function

Aggregate and Scalar Functions are builtin functions of SQL Server that we can be use in our select statements. For example a few Aggregate and Scalar functions are Count, Max, Sum, Upper, lower, Round and and so on. Here in comments I have explained each of its uses.

We can use our tblItemMaster with all these builtin functions.


4. Date Function

In all our projects and tables we use the date column. The Date function plays a very important role in all our projects. So we should be very careful with date functions since sometimes these functions will be trickier. We need to select appropriate date functions and formats in our projects. Let's see a few examples of those here.

• DatePart: We use the datepart to display the selected Year, Month and Day.
  
  
• DateADD: We use dateadd to display or add or subrtract days from the selected date.
  
  
• DateDIff: We use dateDiff to calculate the difference between 2 dates.

5. Other Select Function

Top: To display the Top First or Last selected records first we see how to select the first or Top records and last to the Top records from the Select statement.

Order By: This is used in a SQL Select statement to display the records in ascending or descending order by the columns.


Distinct: The distinct keyword is used in select statements to avoid the duplicate records.


6. Where Clause

The where clause is very important in SQL Select statements. Why we use a where clause and what is use of where clause. Where clause is nothing but a filter of the records using some condition.

Now for example we consider a table has 10,000 records. If we use a select query to display the records then the load time might be long to display all the data. In that case we can use a condition and display some specific data.

For example we can use the Where Clause to display the records for the past 1 week or 1 month.

We can consider our restaurant Order Detail table, for example we consider an Order Detail table to have 10,000 records but for sure for one Order_No there will not be more than 100 records in the Order Detail. Now let's see a few where clause uses.


Where In Clause


Where Between keyword


To display the records within a range we use the between keyword in the where clause.

7. Group BY Clause

A Group by Clause will display records as a result of a combined set or result of the same related records.


Group By and Having Clause

The Having and Group By clauses don't support aggregate functions. To use aggregate functions we use a having clause in a Select statement.


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.