VirtualBox : Installing Oracle VM VirtualBox

Oracle VM VirtualBox is a great virtualization software, and the best part about it is that it's free. If you have a Windows operating system and you want to explorer Linux and UNIX distributions for fun, then VirtualBox is the way to go.

 Here are the steps to install VirtualBox on your machine:

1.  Go to https://www.virtualbox.org/wiki/Downloads to download the latest version of VirtualBox for your operating system.

2.  Double click on the .exe file that you've just downloaded

3.  Click "Next" on the intro screen

4.  Click "Next" on the "Custom Setup" screen

5.  Click next on the second "Custom Setup" screen.

6.  Click "Yes" on the "Warning: Network Interfaces" screen, don't worry your network connection will only be interrupted briefly.

7.  Click "Install" in the "Ready to Install" screen

8,  Click "Yes" on the "User Access Control" prompt from Windows

9.  The installer will do the rest, a status screen will show you the progress of the install

10. After the installation has been completed a dialog screen will show up saying the installation has been completed,  Click "Finish"

11. After you click "Finish" the VirtualBox application will be launched.   That's it, now VirtualBox is installed on your PC.

VirtualBox : Installing Oracle VM VirtualBox

Oracl VM VirtualBox is a great virtualization software, and the best part about it is that it's free. If you have a Windows operating system and you want to explorer Linux and UNIX distributions for fun, then VirtualBox is the way to go.

 Here are the steps to install VirtualBox on your machine:

1.  Go to https://www.virtualbox.org/wiki/Downloads to download the latest version of VirtualBox for your operating system.

2.  Double click on the .exe file that you've just downloaded

3.  Click "Next" on the intro screen

4.  Click "Next" on the "Custom Setup" screen

5.  Click next on the second "Custom Setup" screen.

6.  Click "Yes" on the "Warning: Network Interfaces" screen, don't worry your network connection will only be interrupted briefly.

7.  Click "Install" in the "Ready to Install" screen

8,  Click "Yes" on the "User Access Control" prompt from Windows

9.  The installer will do the rest, a status screen will show you the progress of the install

10. After the installation has been completed a dialog screen will show up saying the installation has been completed,  Click "Finish"

11. After you click "Finish" the VirtualBox application will be launched.   That's it, now VirtualBox is installed on your PC.

C# : Arrays

Arrays are fixed size elements of a type, arrays are stored next to each other in the memory. Making them very fast and efficient.  However you must know the exact size of an array when you declare an array.

Declaring an array:

string[] names = new string[5];

There are two ways you can assign values to an array. The first is to assign the values individually by specify the index of the array inside a square bracket. The index is the position of element in the array. Index starts with 0.

names[0] = "George";
names[1] = "James";
names[2] = "Arthur";
names[3] = "Eric";
names[4] = "Jennifer";

Or you can initialize and populate the array at the same time, like the example below.

string[] names = new string[]{"George","James","Arthur","Eric","Jennifer"};

You don't have to specify the size of the array if you initialize during the declaration, C# is smart enough to figure out the array size from the initialization. You can loop through an array with a foreach loop

  foreach(string n in names)
  {
     Response.Write(n + "");
  }

Or a for loop

 for(int i=0; i < names.Length;i++)
{
    Response.Write(n + "");
 }

C# : Arrays

Arrays are fixed size elements of a type, arrays are stored next to each other in the memory. Making them very fast and efficient.  However you must know the exact size of an array when you declare an array.

Declaring an array:

string[] names = new string[5];

There are two ways you can assign values to an array. The first is to assign the values individually by specify the index of the array inside a square bracket. The index is the position of element in the array. Index starts with 0.

names[0] = "George";
names[1] = "James";
names[2] = "Arthur";
names[3] = "Eric";
names[4] = "Jennifer";

Or you can initialize and populate the array at the same time, like the example below.

string[] names = new string[]{"George","James","Arthur","Eric","Jennifer"};

You don't have to specify the size of the array if you initialize during the declaration, C# is smart enough to figure out the array size from the initialization. You can loop through an array with a foreach loop

  foreach(string n in names)
  {
     Response.Write(n + "
");
  }

Or a for loop

 for(int i=0; i < names.Length;i++)
{
    Response.Write(n + "
");
 }

SQL: Views

Views are virtual tables that you can create that others can use without knowing the complexity of the query, but can be used like a table. Also they can provided an added security by giving developers access to a view instead of the underlying table. Views does not contain data in itself the data stays at the tables that the views are created from. Complex views can degrade performance since they contain no data the query must be processed every time. Let's say a junior developer just came on board and he doesn't really know SQL that well. You can create a view of the more complex views to work with until he gets better with his SQL.

CREATE VIEW EmployeeTerritoriesDescriptions AS
SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e
INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID

The view above queries the employees territories using joins, by creating a view the person using the view does not have to know the underlying table structures that is in the database they can just use the view.
If you check in SQL Server you will see that a new view call EmployeeTerritoriesDescriptions has been created



To select a view you just select it like any other table, by using the SELECT statement. Here is an example of how you would select the EmployeeTerritoriesDescriptions view after it has been created.

SELECT Name,TerritoryDescription,TerritoryID
FROM EmployeeTerritoriesDescriptions

Here are the results:



As you can see a view is a great way to hide the complexity of a query. All you need to do is query the columns in the view and don't have to worry about the complex query any longer.

SQL: Views

Views are virtual tables that you can create that others can use without knowing the complexity of the query, but can be used like a table. Also they can provided an added security by giving developers access to a view instead of the underlying table. Views does not contain data in itself the data stays at the tables that the views are created from. Complex views can degrade performance since they contain no data the query must be processed every time. Let's say a junior developer just came on board and he doesn't really know SQL that well. You can create a view of the more complex views to work with until he gets better with his SQL.

CREATE VIEW EmployeeTerritoriesDescriptions AS
SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e
INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID

The view above queries the employees territories using joins, by creating a view the person using the view does not have to know the underlying table structures that is in the database they can just use the view.
If you check in SQL Server you will see that a new view call EmployeeTerritoriesDescriptions has been created

To select a view you just select it like any other table, by using the SELECT statement. Here is an example of how you would select the EmployeeTerritoriesDescriptions view after it has been created.

SELECT Name,TerritoryDescription,TerritoryID
FROM EmployeeTerritoriesDescriptions

Here are the results:

As you can see a view is a great way to hide the complexity of a query. All you need to do is query the columns in the view and don't have to worry about the complex query any longer.

ASP.NET: Get a Single Value From a Stored Procedure Part 7

Sometimes you need to call a stored procedure that only returns one value. It would be overkill to use the SqlDataReader to store just one value. You can use the SqlCommand.ExecuteScalar() method instead to retrieve just one value from the database.
Here is how you would call the "GetProductsAvgPrice" stored procedure in the Northwind database.

1.  First you need create a stored procedure in the SQL Server that will return just one value the average price of products in Products table in the Northwind database. Run this code in the SQL Server query editor window

USE Northwind;
GO
CREATE PROCEDURE GetProductsAvgPrice
AS
    SELECT AVG(UnitPrice)
    FROM Products;
GO

2.  In your C# code file you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure using the ExecuteScalar() method

  using (SqlConnection conn = new SqlConnection(connectString))
  {    
      conn.Open();
      SqlCommand cmd = new SqlCommand();
      cmd.CommandText = "GetProductsAvgPrice";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Connection = conn;
      decimal avgPrice = Convert.ToDecimal(cmd.ExecuteScalar());

    }

In the above code the way you call a stored procedure is very similar to the way you query the database. The only different is instead of specifying the SQL statement in the SqlCommand.CommandText property you specify the stored procedure name instead. Also you have set the SqlCommand.CommandType to CommandType.StoredProcedure type to let the reader know that it is executing a stored procedure.

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

ASP.NET: Get a Single Value From a Stored Procedure Part 7

Sometimes you need to call a stored procedure that only returns one value. It would be overkill to use the SqlDataReader to store just one value. You can use the SqlCommand.ExecuteScalar() method instead to retrieve just one value from the database.
Here is how you would call the "Top Ten Most Expensive Products" stored procedure in the Northwind database.

1.  First you need create a stored procedure in the SQL Server that will return just one value the average price of products in Products table in the Northwind database. Run this code in the SQL Server query editor window

USE Northwind;
GO
CREATE PROCEDURE GetProductsAvgPrice
AS
    SELECT AVG(UnitPrice)
    FROM Products;
GO

2.  In your C# code file you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure using the ExecuteScalar() method

  using (SqlConnection conn = new SqlConnection(connectString))
  {    
      conn.Open();
      SqlCommand cmd = new SqlCommand();
      cmd.CommandText = "GetProductsAvgPrice";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Connection = conn;
      SqlDataReader dataReader = cmd.ExecuteReader();

    }

In the above code the way you call a stored procedure is very similar to the way you query the database. The only different is instead of specifying the SQL statement in the SqlCommand.CommandText property you specify the stored procedure name instead. Also you have set the SqlCommand.CommandType to CommandType.StoredProcedure type to let the reader know that it is executing a stored procedure.

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL : INSERT SELECT

The INSERT SELECT is an INSERT statement that inserts value using a SELECT statement. The following query will insert a new customer using existing record.

INSERT INTO [dbo].[Customers]
           ([CustomerID],
     [CompanyName]
           ,[ContactName]
           ,[ContactTitle]
           ,[Address]
           ,[City]
           ,[Region]
           ,[PostalCode]
           ,[Country]
           ,[Phone]
           ,[Fax])
   SELECT   'OPDS',
      CompanyName,
      ContactName,
      ContactTitle,
      Address,
      City,
      Region,
      PostalCode,
      Country,
      Phone,
      Fax
FROM Customers
WHERE CustomerID = 'ALFKI'

Since the CustomerID field cannot be null we have to assign the value 'OPDS' as the new CustomerID for the new customer. The rest of the column values are selected from the Customer with customer id 'ALFKI'

Here is the new record as well as the 'ALFKI' customer for comparison.

SQL : INSERT SELECT

The INSERT SELECT is an INSERT statement that inserts value using a SELECT statement. The following query will insert a new customer using existing record.

INSERT INTO [dbo].[Customers]
           ([CustomerID],
     [CompanyName]
           ,[ContactName]
           ,[ContactTitle]
           ,[Address]
           ,[City]
           ,[Region]
           ,[PostalCode]
           ,[Country]
           ,[Phone]
           ,[Fax])
   SELECT   'OPDS',
      CompanyName,
      ContactName,
      ContactTitle,
      Address,
      City,
      Region,
      PostalCode,
      Country,
      Phone,
      Fax
FROM Customers
WHERE CustomerID = 'ALFKI'

Since the CustomerID field cannot be null we have to assign the value 'OPDS' as the new CustomerID for the new customer. The rest of the column values are selected from the Customer with customer id 'ALFKI'
Here is the new record as well as the 'ALFKI' customer for comparison.

SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

In SQL Server type in the following command in the Query Window:

USE Northwind;
Grant EXEC ON OBJECT::dbo.GetProducts TO "NT AUTHORITY\NETWORK SERVICE";
GO

The command above grants execution permission for user "NT AUTHORITY\NETWORK SERVICE" on the stored procedure dbo.GetProducts

dbo = owner schema
GetProducts = name of stored procedure
"NT AUTHORITY\NETWORK SERVICE" = user that IIS uses to access SQL Server

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: UNION Operator

The UNION operator combines two or more SELECT statements into one result set. The SELECT list must be the same in all queries, same columns, expressions, functions, and data types. All UNION queries can be replaced with a join query. The query below combines the Customers select statement with with the Employees statement

SELECT City,Address
FROM Customers
UNION
SELECT City,Address
FROM Employees

Here is the result:

ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call selProductsBySupplierID.  The stored procedure takes one input parameter call @SupplierID which takes an int.

Here is how you would do it in C#

1.  First you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure and output the result from the SqlDataReader

  using (SqlConnection conn = new SqlConnection(connectString))
  {    
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "selProductsBySupplierID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@SupplierID";
            parameter.SqlDbType = SqlDbType.Int;
            parameter.Value = 1;
            cmd.Parameters.Add(parameter);

            SqlDataReader dataReader = cmd.ExecuteReader();

            try
            {
                 while (dataReader.Read())
                 {
                     Response.Write("Products: " + dataReader[0] + " $" + 
                     dataReader[1] + "<br>");
                 }
             }
             finally
             {
                 dataReader.Close();
             }
    }

In the above code you add the parameter by using SqlParameter. You specify the name, type, and value. Then add it to command object's parameters list. When you execute the reader the parameter @SupplierID is passed into the stored procedure.

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: UNION Operator

The UNION operator combines two or more SELECT statements into one result set. The SELECT list must be the same in all queries, same columns, expressions, functions, and data types. All UNION queries can be replaced with a join query. The query below combines the Customers select statement with with the Employees statement

SELECT City,Address
FROM Customers
UNION
SELECT City,Address
FROM Employees

Here is the result:

ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call selProductsBySupplierID.  The stored procedure takes one input parameter call @SupplierID which takes an int.

Here is how you would do it in C#

1.  First you need the namespaces

using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

2. Then get the Northwind connection string value from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;

3. Now call the stored procedure and output the result from the SqlDataReader

  using (SqlConnection conn = new SqlConnection(connectString))
  {    
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "selProductsBySupplierID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@SupplierID";
            parameter.SqlDbType = SqlDbType.Int;
            parameter.Value = 1;
            cmd.Parameters.Add(parameter);

            SqlDataReader dataReader = cmd.ExecuteReader();

            try
            {
                 while (dataReader.Read())
                 {
                     Response.Write("Products: " + dataReader[0] + " $" + 
                     dataReader[1] + "<br>");
                 }
             }
             finally
             {
                 dataReader.Close();
             }
    }

In the above code you add the parameter by using SqlParameter. You specify the name, type, and value. Then add it to command object's parameters list. When you execute the reader the parameter @SupplierID is passed into the stored procedure.

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: FULL OUTER JOIN

FULL OUTER JOIN is a join that returns all the results from the left hand side of the = sign and all the results of the right hand side. For example this query returns all the customers and all the orders in one result

SELECT c.ContactName, o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID=o.CustomerID
ORDER BY c.ContactName

SQL: FULL OUTER JOIN

FULL OUTER JOIN is a join that returns all the results from the left hand side of the = sign and all the results of the right hand side. For example this query returns all the customers and all the orders in one result

SELECT c.ContactName, o.OrderID
FROM Customers c
FULL OUTER JOIN Orders o
ON c.CustomerID=o.CustomerID
ORDER BY c.ContactName

SQL: Self Joins, Unary Relationships, and Aliases

If you look at the Employees table in the Northwind database diagram you will see that there's a relationship that links to itself


And if you look the at the Employees create script you will see that the foreign key to is the ReportTo field referencing the Primary Key EmployeeID. This kind of self referencing is called a unary relationship.

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

So how do you query the employees who is manage by another employee? You can assign aliases to the same table so that you can query the same table as if it were two different tables.

SELECT e.EmployeeID,(e.FirstName + ' ' + e.LastName) AS Name, 
       (et.FirstName + ' ' + et.LastName) AS Supervisor
FROM Employees e, Employees et  
WHERE e.ReportsTo = et.EmployeeID

Here are the results:

As you can see from the select list, (e.FirstName + ' ' + e.LastName) AS Name displays the Employee's name, but (et.FirstName + ' ' + et.LastName) AS Supervisor displays the supervisor's name even though we are retrieving the same fields on the same table. This behavior is possible because we gave the same table two different aliases. As a general rule, you should replace subqueries with self joins, if you can because it performs better.

T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5

In most of your projects you will have to work with stored procedures.  As a developer most of the time you only have to concern yourself with the basic stored procedures such as the SELECT, INSERT, UPDATE, and DELETE stored procedures.  If there's a DBA then you will probably be handed a stored procedure written by the database god.  But if you are the only developer in the five mile radius you might have to get your hands dirty and roll your own stored procedure.  In this tutorial we will be creating a select stored procedure.

Here is how

1.  Right click on the "Northwind" database and then select "New Query" in "Microsoft SQL Server Management Studio"

2.  A new query window will be open type in the following into the query window to create the SELECT stored procedure.

USE Northwind
GO
CREATE PROCEDURE dbo.ProductsSuppliers
AS
SELECT p.ProductID,
p.ProductName,
p.UnitPrice,
s.CompanyName AS Supplier
FROM Products p
INNER JOIN Suppliers s ON
p.SupplierID = s.SupplierID
GO

We've just created a stored procedure that will get the supplier for each product. This involves getting information from two different tables, therefore you need to use a join.  The INNER JOIN is like a WHERE and AND clause.  We displayed the CompanyName as Supplier.  That's it!

3.  Type

  EXEC dbo.ProductsSuppliers

4. Your result should look something like this

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: Self Joins, Unary Relationships, and Aliases

If you look at the Employees database diagram you will see that there's a relationship that links to itself


And if you look the at the Employees create script you will see that the foreign key to is the ReportTo field referencing the Primary Key EmployeeID. This kind of self referencing is called a unary relationship.

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employees] ([EmployeeID])

So how do you query the employees who is manage by another employee? You can assign aliases to the same table so that you can query the same table as if it were two different tables.

SELECT e.EmployeeID,(e.FirstName + ' ' + e.LastName) AS Name, 
       (et.FirstName + ' ' + et.LastName) AS Supervisor
FROM Employees e, Employees et  
WHERE e.ReportsTo = et.EmployeeID

Here are the results:

As you can see from the select list, (e.FirstName + ' ' + e.LastName) AS Name displays the Employee's name, but (et.FirstName + ' ' + et.LastName) AS Supervisor displays the supervisor's name even though we are retrieving the same fields on the same table. This behavior is possible because we gave the same table two different aliases. As a general rule, you should replace subqueries with self joins, if you can because it performs better.

T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5

In most of your projects you will have to work with stored procedures.  As a developer most of the time you only have to concern yourself with the basic stored procedures such as the SELECT, INSERT, UPDATE, and DELETE stored procedures.  If there's a DBA then you will probably be handed a stored procedure written by the database god.  But if you are the only developer in the five mile radius you might have to get your hands dirty and roll your own stored procedure.  In this tutorial we will be creating a select stored procedure.

Here is how

1.  Right click on the "Northwind" database and then select "New Query" in "Microsoft SQL Server Management Studio"

2.  A new query window will be open type in the following into the query window to create the SELECT stored procedure.

USE Northwind
GO
CREATE PROCEDURE dbo.ProductsSuppliers
AS
SELECT p.ProductID,
p.ProductName,
p.UnitPrice,
s.CompanyName AS Supplier
FROM Products p
INNER JOIN Suppliers s ON
p.SupplierID = s.SupplierID
GO

We've just created a stored procedure that will get the supplier for each product. This involves getting information from two different tables, therefore you need to use a join.  The INNER JOIN is like a WHERE and AND clause.  We displayed the CompanyName as Supplier.  That's it!

3.  Type

  EXEC dbo.ProductsSuppliers

4. Your result should look something like this

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: RIGHT JOIN

RIGHT JOIN works like the INNER JOIN, it just returns all the records that are on the right side of the = sign on the RIGHT JOIN clause. For example let's say you want to get a record of all customers who orders a certain product.
You will use the RIGHT JOIN by query all the orders in the Orders table then linking it to the OrderDetails table and then eventually linking it to the Products table.

SELECT c.CompanyName,c.ContactName,c.ContactTitle,od.OrderID,p.ProductID,p.ProductName
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
RIGHT JOIN [Order Details] od ON o.OrderID = od.OrderID
RIGHT JOIN Products p ON p.ProductID = od.ProductID

Here are the results:

You can filter the results further by adding a WHERE claus for a specific product id.

SELECT c.CompanyName,c.ContactName,c.ContactTitle,od.OrderID,p.ProductID,p.ProductName
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
RIGHT JOIN [Order Details] od ON o.OrderID = od.OrderID
RIGHT JOIN Products p ON p.ProductID = od.ProductID 
WHERE p.ProductID = 33

Here are the results:

T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4

Here is how you would create a stored procedure to update an a new record into the Products table in the Northwind database.

USE Northwind
GO
CREATE PROCEDURE dbo.updProduct(
 @ProductID int,
 @ProductName nvarchar(40),
 @SupplierID int = null,  --default is null
 @CategoryID int = null,
 @QuantityPerUnit nvarchar(20) = null,
 @UnitPrice money = null,
 @UnitsInStock smallint = null,
 @UnitsOnOrder smallint = null,
 @ReorderLevel smallint = null,
 @Discontinued bit)
AS
UPDATE Products 
 SET ProductName = @ProductName,
  SupplierID = @SupplierID,
  CategoryID = @CategoryID,
  QuantityPerUnit = @QuantityPerUnit,
  UnitPrice = @UnitPrice,
  UnitsInStock = @UnitsInStock,
  UnitsOnOrder = @UnitsOnOrder,
  ReorderLevel = @ReorderLevel,
  Discontinued = @Discontinued
WHERE Products.ProductID = @ProductID
GO

When you see a parameter with the = null, it means the field can have a null value. Since we need ProductID is needed to update a specific record we need it in the input parameter list. The data types must match the fields in the database.
Now lets find an existing record in the database, let's use the product with the ProductID of 1

Here is how you would execute the stored procedure

EXEC dbo.updProduct 
 @ProductID = 1,
 @ProductName ='Teh Tarik',
 @SupplierID = DEFAULT,
 @CategoryID = DEFAULT,
 @QuantityPerUnit ='20 boxes x 12 oz.',
 @UnitPrice = 12.99,
 @UnitsInStock = 5,
 @UnitsOnOrder = 6,
 @ReorderLevel = DEFAULT,
 @Discontinued = 0

When you see a parameter with = DEFAULT it means to assign the DEFAULT value to the field, if the execution is completed successfully you should see the message.

(1 row(s) affected)
Here is how the updated row looks like now.

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8

SQL: RIGHT JOIN

RIGHT JOIN works like the INNER JOIN, it just returns all the records that are on the right side of the = sign on the RIGHT JOIN clause. For example let's say you want to get a record of all customers who orders a certain product.
You will use the RIGHT JOIN by query all the orders in the Orders table then linking it to the OrderDetails table and then eventually linking it to the Products table.

SELECT c.CompanyName,c.ContactName,c.ContactTitle,od.OrderID,p.ProductID,p.ProductName
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
RIGHT JOIN [Order Details] od ON o.OrderID = od.OrderID
RIGHT JOIN Products p ON p.ProductID = od.ProductID

Here are the results:

You can filter the results further by adding a WHERE claus for a specific product id.

SELECT c.CompanyName,c.ContactName,c.ContactTitle,od.OrderID,p.ProductID,p.ProductName
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerID = c.CustomerID
RIGHT JOIN [Order Details] od ON o.OrderID = od.OrderID
RIGHT JOIN Products p ON p.ProductID = od.ProductID 
WHERE p.ProductID = 33

Here are the results: