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.

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.

SQL: WHERE Clause

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

SQL: Subqueries

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

SQL: GROUP BY And HAVING

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.

SQL: Wildcard Search With The LIKE Operator

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.

SQL: SUM() Aggregate Function

The SUM() function is used to sum up all the values in the specified column.

SELECT SUM(UnitsInStock) AS TotalInventory
FROM Products

The above query gets the total number of units in stock for all products

SQL: INNER JOIN

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:

AngularJS SPA: Show Bank Accounts With $scope and Controller

In our last post we created our business entities, in this post we are going to use our business objects to display information to the users.  I this post we are going to show the accounts to the users.  In the previous posts we have been setting up the environment the plumbing sort of speak.  Starting now we are going to build the application.  The first thing we want to do is counter-intuitive, we are going to take the database out of the equation and start using mock data to get so that we can see how our application will run.

In our previous post we created some user stories.  In this post we are going to work on one of the stories:

• As a user I should be able to view my accounts. (2 pts)

It's a simple process, but we know exactly what we have to do with user stories.  It's a different mindset than the traditional requirements.  The typical requirement that you are used to seeing is

1.1.1 System shall allow the user to view his/her accounts

It's basically saying the same thing, but the first one the user stories is in plain English and it's a lot easier to comprehend.  You can take it to the business user, the web designer, or the web developer and it's easily understood.  That's the benefit of Agile development. 

First let's take the Iffe out of the account.js file so that it's easier to work with and then we want to make changes to the bankController.js file

Here is how the account.js file should now look like.

    function Account(balance, type) {
        this.balance = balance;
        this.type = type;
    }

    Account.prototype.deposit = function (d) {

        this.balance = this.balance + d;
        return this.balance;
    }

    Account.prototype.withdrawal = function (w) {
        this.balance = this.balance - w;
        return this.balance;
    }


    function inherits(baseClass, childClass) {
        childClass.prototype = Object.create(baseClass.prototype);
    }

    function Premium(balance, type) {
        this.balance = balance + (balance * .05);
        this.type = type;
    }


    inherits(Account, Premium);

    Premium.prototype.transfer = function (from, to, amount) {
        from.balance -= amount;
        to.balance += amount;
        from.rebalance();
        to.rebalance();
    }

    Premium.prototype.deposit = function (d) {
        this.balance = this.balance + d;
        this.balance = this.balance + (this.balance * .05);
        return this.balance;

    }
    Premium.prototype.rebalance = function () {
        this.balance += this.balance * .05;
    }

Here is the code we change in the bankController.js file

(function () {
    'use strict';

    angular.module('bankController', [])
        .controller('bankController', ["$scope", function ($scope) {
            $scope.accounts = [new Account(1000, 'Checking'), new Account(10000, 'Saving'), new Premium(100000, 'Premium Checking')];
            console.log($scope.accounts);
        }]);

})();

CSS: Combinators

Combinators in CSS is term used to define a style that combines more than one selectors together.

For example let's say you have the following markup

        <div id="combinator-div"><h1>I'll be back.</h1></div>
        <h1>Combinator me</h1>

Let's say you only want to apply the styles for the <h1> tag that's inside the combinator-div, you can use a combinator style which is a combination of the div id and the h1 tag as combination to style a very specific element on the page.  The style would look like the following

#combinator-div h1 {font-family: sans-serif; font-weight: bold; color: green;}

If you run the page with the code then you would get the following output











As you can see only the I'll be back <h1> is styled, the other <h1> is just not cool enough to get styled.