Tech Junkie Blog: February 2015

Friday, February 27, 2015

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.








Thursday, February 19, 2015

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 + "");
 }

Wednesday, February 18, 2015

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.

Tuesday, February 17, 2015

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:

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:

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.

Monday, February 16, 2015

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.

Sql Parameter

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:

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:



Sunday, February 15, 2015

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




Saturday, February 14, 2015

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:

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.


Friday, February 13, 2015

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:

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:

Thursday, February 12, 2015

This is part three of our series on Entity Framework. In the last blog we went over how to create an Entity Framework model with the Northwind database. Now we are going to use that model in our ASP.NET by binding the Entity objects that have created to a GridView in our "Northwind" ASP.NET project. Usually we would put the Entity Framework model in a class library project and use it as our data access layer, but for simplicity I've decided to put in the same project as the ASP.NET pages.

Below are the directions on how to use the Entity objects in our web pages.

1. Create "Default.aspx" page in the "Northwind" web project.



To create the delete procedure type in the following code in the SQL editor window in "Microsoft SQL Server Management Studio"

USE Northwind
GO
CREATE PROCEDURE dbo.delProduct @ProductID int
AS
DELETE FROM Products
WHERE Products.ProductID = @ProductID
GO

The stored procedure only takes in one input parameter which is the ProductID, the DELETE statement needs a ProductID because if there is no WHERE clause, every record in the product in the Products table will be deleted. Make sure you backup the table before you work with a DELETE stored procedure.
Here is how would execute the stored procedure
EXEC dbo.delProduct 78


Blogs In the T-SQL Series:



The INNER JOIN functions like the WHERE clause by relating two or more tables using matching data. The difference is that the INNER JOIN is used in the FROM clause. So to the the employee's territory like the one we wrote in this blog. We would change the query into the query below to use the INNER JOIN instead of the WHERE clause.

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

With the result:



To the employees who belongs to the Boston territory you would add the "AND" clause to the query, like the query below:

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
AND t.TerritoryID = 02116

Wednesday, February 11, 2015

This is part two of our series on Entity Framework, if you would like to catch up with what we did on on part one, feel free to go over the lesson so that you can follow along.

In the last part we installed Entity Framework 6.1.1 with NuGet package management tool in Visual Studio.  In this lesson we will learn to create an Entity Model using the Northwind database.  Follow the steps below.

  1. Add a new folder call "Models" in the "Northwind" database
  2.  
     
     
     
     
     

Today we will be calling a stored procedure in SQL Server that we've created earlier in this blog call addProduct.  The stored procedure takes the following input parameters.


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 = "addProduct";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
  
            SqlParameter productName = new SqlParameter("@ProductName", "Teh");
            productName.SqlDbType = SqlDbType.NVarChar;
            productName.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(productName);

            SqlParameter supplerID = new SqlParameter("@SupplierID", 1);
            supplerID.SqlDbType = SqlDbType.Int;
            supplerID.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(supplerID);

            SqlParameter categoryID = new SqlParameter("@CategoryID", 1);
            categoryID.SqlDbType = SqlDbType.Int;
            categoryID.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(categoryID);

            SqlParameter quantityPerUnit = new SqlParameter("@QuantityPerUnit", "20 boxes of 12 oz.");
            quantityPerUnit.SqlDbType = SqlDbType.NVarChar;
            quantityPerUnit.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(quantityPerUnit);

            SqlParameter unitPrice = new SqlParameter("@UnitPrice", 12.99);
            unitPrice.SqlDbType = SqlDbType.Money;
            unitPrice.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(unitPrice);

            SqlParameter unitsInStock = new SqlParameter("@UnitsInStock", 6);
            unitsInStock.SqlDbType = SqlDbType.SmallInt;
            unitsInStock.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(unitsInStock);

            SqlParameter reorderLevel = new SqlParameter("@ReorderLevel", 2);
            reorderLevel.SqlDbType = SqlDbType.SmallInt;
            reorderLevel.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(reorderLevel);

            SqlParameter discontinued = new SqlParameter("@Discontinued", false);
            discontinued.SqlDbType = SqlDbType.Bit;
            discontinued.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(discontinued);

            int rowsAffected = cmd.ExecuteNonQuery();

            Response.Write(rowsAffected);
    }
In the above code you add the parameters required by the addProduct stored procedure. You specify the name, type, and value. Then add it to command object's parameters list. Then you execute the ExecuteNonQuery() method because you are not get a resultset back or a scalar value. The ExecuteNonQuery() method returns an int value, usually the rows that were affected value.

Blogs In the T-SQL Series:

In SQL the WHERE clause is the most common join you will see, it relates one or more tables together. For example you want to get the employeeis territory information in the Northwind database but you there are all in different tables.

As you can see from the above diagram the employee information is in the Employees table, while a linking table is used to link the employee to the territory in the EmployeeTerritories, and then there's the Territories table which contains the actual name of the territory in the Territory. How do we proceed to retrieve this information? With a WHERE clause of course. The WHERE clause allows us to retrieve information from all these tables and combine them into one result set. Here is the query that you would write with the WHERE clause:

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e,Territories t, EmployeeTerritories et
WHERE e.EmployeeID = et.EmployeeID
  AND et.TerritoryID = t.TerritoryID

The query above joins the Employees table to the EmployeesTerritories table matching the two tables by the column EmployeeID, then after we get the records with matching records between those two tables, we and the word "AND" to add additional joins based on our first join. This time we want to match the TerritoryID in the EmployeesTerritories table with the TerritoryID column in the Territories which contains the TerritoryDescription field that we wanted. So with the WHERE clause we were able to work with three tables at once in one query.



You can filter the results even more by looking adding more filtering conditions in the WHERE clause. Let's say you want to get only employees who belongs to the Boston territory, from the first query you know that Boston has a TerritoryID of 02116. So to get the employees who belongs to the Boston territory you would write the query like the one below:

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e,Territories t, EmployeeTerritories et
WHERE e.EmployeeID = et.EmployeeID
      AND et.TerritoryID = t.TerritoryID
      AND t.TerritoryID = 02116

Here are the results








Tuesday, February 10, 2015

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

USE Northwind
GO
CREATE PROCEDURE dbo.addProduct(
 @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
INSERT INTO Products(ProductName,
 SupplierID,
 CategoryID,
 QuantityPerUnit,
 UnitPrice,
 UnitsInStock,
 UnitsOnOrder,
 ReorderLevel,
 Discontinued)
VALUES(@ProductName,
 @SupplierID,
 @CategoryID,
 @QuantityPerUnit,
 @UnitPrice,
 @UnitsInStock,
 @UnitsOnOrder,
 @ReorderLevel,
 @Discontinued)
GO

When you see a parameter with the = null, it means the field can have a null value. Since the ProductID is auto incremented you don't include it. The data types must match the fields in the database.
Here is how you would execute the stored procedure
EXEC dbo.addProduct @ProductName ='Teh',
 @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)

Blogs In the T-SQL Series:

The easiest and simplest way to explain what a subquery is to say that it's a query within a query. For example if you want to get the employee that belongs to specific territory in the Northwind database without a join, you would have to use a subquery. Like the following subquery.

SELECT EmployeeID, (FirstName + ' ' + LastName) AS Name
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID 
       FROM EmployeeTerritories 
       WHERE TerritoryID=01581) 



Things You Should Know About Subqueries:
  • They are not the most efficient performance wise
  • You can only retrieve a single column in the subquery, retrieving multiple columns will throw an error
Another way to use subqueries is to use it with Aggregate functions like the query below, which gets the average price for the category with ID value of 1:

SELECT CategoryName,
 (SELECT AVG(UnitPrice) 
 FROM Products WHERE CategoryID = 1) AS AvgPrice
FROM Categories
WHERE CategoryID = 1



Monday, February 9, 2015

SQL GROUPING allows you to segregate data into groups so that you can work on it separately from the rest of the table records. Let's say you want to get the number of products in a category in the Northwind database Products table. You would write the following query:

 SELECT COUNT(*) NumberOfProductsByCategory
FROM Products
GROUP BY CategoryID

The query above gives you the following results:



The query gives you the number of products in each category, however it's not very useful. You don't really know what category the count is for in each record. You might want to try to change the query into something like this:

SELECT CategoryID,COUNT(*) NumberOfProductsByCategory
FROM Products
GROUP BY CategoryID


The above query is more useful the preceding one, however it only gives you the CategoryID number not the CategoryName in the Categories table. Being the perfectionist that you are you say to yourself, I can do better. "Yes, I can". I think that was a campaign slogan. So you try a join like this:

SELECT c.CategoryName,c.CategoryID
RIGHT JOIN Categories AS c ON c.CategoryID = p.CategoryID

The above query joins the Categories table with the Products table to be able to select from both tables therefore giving the ability to select the CategoryName field.



 You probably think to yourself, hey if I can get the CategoryName with the RIGHT JOIN I can just add the GROUP BY filter and be done with it. Yah, my Shaolin Master from the SQL Wing will be proud. So you write the following query:

SELECT c.CategoryName,p.CategoryID,COUNT(*) AS NumberOfProductsByCategory
FROM Products p
RIGHT JOIN Categories AS c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryID

But behold you get an error! OMG! !@@##$%##$% Luckily your Shaolin Master walked into your room and saw that you were distraught. Your master ask you why you so distraught? You tell the Master, I don't understand, the join was working by itself but when I add the GROUP BY it throws me this stupid SQL error:

Column 'Categories.CategoryName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Your master says, if the expression is used in the SELECT statement then the same expression must be used in the GROUP BY part of the statement. You think ah ha! I can just add the columns in the SELECT list of columns to the GROUP BY filtering in the query. So you write:

SELECT c.CategoryID,c.CategoryName,COUNT(*) AS NumberOfProductsByCategory 
FROM Products p
RIGHT JOIN Categories AS c ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryID,c.CategoryName

And like magic the result came to your screen. Tears of joy were streaming down your face because you've gotten the results that you were looking for.



Option 2: You can select what you want from the Products table and then use the GROUP BY in another select in a RIGHT JOIN to the get the COUNT() for each category. You will get the correct results with this query as well.

SELECT c.CategoryID,c.CategoryName,NumberOfProductsByCategory
FROM Categories AS c
RIGHT JOIN (SELECT CategoryID, COUNT(*) AS NumberOfProductsByCategory 
   FROM Products GROUP BY CategoryID) AS p ON p.CategoryID = c.CategoryID


As you have noticed the GROUP BY queries are missing the WHERE clause. What happened to the powerful WHERE clause? The reason we don't have a where clause is because WHERE does not work with GROUP BY results. That's where HAVING comes in. HAVING takes care of the filtering of the GROUP BY, while the WHERE clause takes care of the non GROUP BY results. For example let's say you want to get the categories with the most expensive products you can write a query that filters the product price with the WHERE clause and then filter the group data with the HAVING clause. You can write the following query to get the expensive products and their categories:

SELECT c.CategoryID,c.CategoryName,COUNT(*) AS NumberOfProductsByCategory 
FROM Products p
RIGHT JOIN Categories AS c ON c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10
GROUP BY c.CategoryID,c.CategoryName
HAVING COUNT(*) >=5
ORDER BY NumberOfProductsByCategory DESC

The query above gets products that are more than $10, and gets the categories that has more than 9 products. The two filters are separate but they work together nicely with the WHERE and HAVING


clause.

Sunday, February 8, 2015

Equality searches are great and efficient when you want exact matches or range of values. However, there will be times when you need to search a text field for not so perfect matches, perhaps a partial match is needed. Certain scenarios requires to search for patterns, such as an email address. That's when the LIKE operator is useful in SQL. The only caveat is that LIKE operators can only work with text fields. Examples: 1. A word/text with a % at the end, searches for all the records that begins with the letters before the percent sign

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE 'Chef%'

The query above returns all the records in the Products table that begins with the word "Chef"



 2. A word with % sign on both ends, means that the result will be any records that contains the enclosed word/text within the % sign

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Hot%'

The query above searches for any records that contains the word "Hot" in the ProductName field. It brings back all the records that contains the word "Hot" regardless of the position that it resides in.



3. A word/text with a % at the beginning, searches for all the records that ends with the word/text after the percent sign. It's works in kind of the reverse of what you think will happen

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Sauce'

The above query searches for all the records that ends with the word/text "Sauce" in the ProductName field in the Products table



 4. Let's try something a little bit tricky. Let's say your boss wants you to search for a spread that he likes, but does not know the exact spelling for. He would tell you it's call something like a "boys" n "berry" spread. To get that .00001% raise that you've always wanted you told your boss, I can do it!. So how will you search for such a spread?

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Boy%y%'



The above query searches for a word that contains the text "Boy" and ends with the letter "y", and the result is, ta da! "Grandma's Boysenberry Spread" with that result you were able to get your .00001% raise and is finally able to afford half a Popsicle that you've been eyeing all week. All is well in the IT land once again.

Conclusion: The LIKE operator comes in handy when you need to match a text pattern in a text field. However, it takes longer to execute than an equality match. So use it sparingly, only when needed.

Saturday, February 7, 2015

As a developer we always forget how to query for records with NULL values, no matter how many times we do it. It's just weird. Our first instinct is to write the query as such

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region = NULL

But that will not return any results. The funny thing is there's no SQL error so you think that there's no results. However if you change the query to this

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region IS NULL

You see there's plenty of records with Region IS NULL



The reverse is true if you want records that are not NULL you would not write the query like this

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region != NULL

But you want to write the query like this instead

   SELECT CompanyName, ContactName, ContactTitle,Region
   FROM Customers
   WHERE Region IS NOT NULL


Friday, February 6, 2015


SELECT UnitPrice, ProductName
FROM Products
ORDER BY UnitPrice DESC, ProductName


The query above sorts the results based on the most expensive products, and then the product name. Useful if you want a secondary sort criteria. For example if there are multiple products that are $14.00 then those products will be sorted by their names after the price has been sorted.