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.

Java EE: Setup WildFly for Java EE Development

If you are developing Java EE applications, then WildFly is an excellent web application that you can set up easily.  In this post we are going to setup WildFly on a Windows machine.  The instructions should be similar for a Linux machine, it's just that the files ends with the .sh extensions.

Here are the steps to setup WildFly on your machine:

1. Go the WildFly website and download the latest version, https://wildfly.org/downloads/

CSS: Using Google Fonts

In this post we are going to use Google Fonts in our HTML markup, using Google Fonts is really easy to do.  All you have to do is go to https://fonts.google.com/ then select the fonts that you would like to use.  Let's we want to use the Manjari, then All we have to do is click on the + sign next to the font.

AngularJS SPA: Agile Development With User Stories

In our previous post we created some mockups, now we are going to create some user stories so that we can started development with some idea of what should be included in the application.  This is just a quick planning session.

We'll pretend like we are running a Sprint, we can think of this as a very loose Sprint planning.

Sprint 1 User Stories;

• As a user I should be able to view my accounts. (2 pts)
• As a user I should be view my user profile. (1 pt)
• As a user I should be able to view my account details. (2 pts)
• As a user I should be able to view my past transactions. (1 pt)
• As a user I should be able to view my upcoming transactions ( 1 pt)
• As a user I should be able to search my transactions (2 pts)

AngularJS SPA: ACME Bank Mockups

In the previous post we created the JavaScript business objects in our AngularJS application.  While that's great and all we should we really take a step back create some rough mockups of our banking application so that we will know what we will be building.

First let's mock up the home page:























On our home page we have a profile section were we can access our account/profile. Then we have the ACME bank logo/branding, and finally the accounts that we have in the bank.

The next mock-up is the the Accounts Details page, when we click on one of the accounts:





























As you can see from the mock-up it's pretty straightforward.  You have an identification of the account, then a display of upcoming  transactions, and past transactions.  There's also a search box for searching of transactions.

Finally we have the Money Transfer screen which enables the users to transfer money into and out of the account.  It just has the basic information, where the money is coming from, where it's going to, the transfer date, the amount and the transfer button.




















As you can see the mock-up is pretty simple and easy to change.  Actually it's just pen and paper.  I am a big fan of low-fidelity design.  Meaning simple and low effort.  We can change it anytime.

Previous: AngularJS SPA: Building The Bank Application JavaScript Objects (Business Entities)

Hour 18 Asp.Net Core: Create A Web API For NorthwindCafe With Postman

In the last few posts we have been working with our models and retrieving the data natively in the application, but most of the project you will work with in real life will probably have you call some kind of api with an endpoint.  In this post we are going to substitute the products call what we made with an api.

Here are steps to create our first Api:

1. First we want to set up our application to use postman to test out our api. To do that right-click on the project and click on "Properties", in Properties screen click on "Debug" and uncheck "Launch browser" and make a note of the port number of the App URL.  We will make the api calls with postman initially to make sure that our api works.  Postman has become pretty popular with api development because it allows us to make api calls and see the results.  You can get postman here

https://www.getpostman.com/downloads/ I would get the desktop version because it's more robust.





















2. Now we are ready to create a ProductController to be our api controller, Asp.NET Core has the api control built-in so we don't have to do anything special for a controller to be a web api controller. So create a file call ProductController in the Controllers folder.  The ProductController should have the following code

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NorthwindCafe.Web.Data;
using System;

namespace NorthwindCafe.Web.Controllers
{
    [Route("api/[Controller]")]
    public class ProductController : Controller
    {
        private readonly  IProductRepository _repository;
        private readonly  ILogger _logger;

        public ProductController(IProductRepository repository, ILogger logger)
        {
            _repository = repository;
            _logger = logger;
        }

        [HttpGet]
        public IActionResult Get()
        {
            try
            {
                return Ok(_repository.GetProducts());
            }
            catch (Exception ex)
            {
                _logger.LogError($"Get products failed {ex}");
                return BadRequest("Get products failed");
            }
        }
    }
}

The code above is pretty straightforward.  You have the define private variables _repository and _logger for the repository to hold the injected objects in the constructor. Then you define a method call Get with the decorator [HttpGet] to handle get requests.  The Get() method returns the list of products and retruns Ok = 200 status code if everything is ok and returns a 400 error status code if there's an exception.  The other important thing is the [Route("api/[Controller]")] decorator.  This is what you typed into the browser.  So for this route you would type http://localhost:50051/api/product into Postman.

3. So now we are ready to test our code in Postman, first we need to run our code, press CRTL+F5
Then select "GET" method on Postman call, type in the URL localhost:50051/api/product and you will see the list of products returned in Json, usually the resource in this product should be pluralize, but I forgot the s.  So the URLs should be localhost:<port>/api/products but since it's just development we can let it slide.  However, if you work with other people you might want to pluralize it.

JavaEE: Create Dynamic Web Project With Tomcat And Eclipse

In order to host websites in Tomcat we need to create a project to host servlets.  In this post we are going to create Dynamic Web project in Eclipse.

Here are the steps to create a web project in Eclipse:

1. Right-click on "Servers" → "New" → "Other"

2. Expand the "Web" node then select "Dynamic Web Project", then click "Next"→

SQL: Sort By Multiple Columns

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.

JavaEE: Configure Tomcat As An Eclipse Server

In this post we are going to set Tomcat as a server in Eclipse so that we can interact with Tomcat through Eclipse.

Here are the steps to configure Tomcat to work with Eclipse:

You have to download the zip file version of the install to make Tomcat work with the Eclipse.  I had no luck with the Windows Installer

So go to https://tomcat.apache.org/download-90.cgi

Then click on the .zip version of the download, and then extract the files into the




















Now extract the content of the zip file into the folder C:\apache-tomcat-9.0.24

JavaEE: Install The Eclipse IDE On Windows

In this post we are going to install the Eclipse IDE for Java development.

Here are the steps to install Eclipse:

1. Go to https://eclipse.org/downloads click on the "Download 64-bit" button

JavaEE: Install Tomcat 9 On Windows

2. Under "Binary Distributions" select the last link

MongoDB In-Depth: Installing MongoDB on Windows

In the previous post we installed MongoDB on Linux on this post we are going to install MongoDB on Windows.

Here are the steps:

1. Go to https://www.mongodb.com/download-center/community, then click on the "Download" button

MongoDB In-Depth: The Difference Between RDBMS and MongoDB

Before we begin inserting data into mongoDB we need to understand the difference between Relational Database Management System (RDBMS) and mongoDB.  RDBMS is a database many of us worked with in the past.  You have tables and relationships between those tables.  We will use the ACME Bank that we were build our AngularJS SPA application as an example.

SQL Server : Add a Data Connection To A SQL Server Database In Visual Studio 2013

The "Server Explorer" tool in Visual Studio 2013 is a good tool at your disposal if want to interact with the database in GUI environment.  To create a new data connection to the database in the "Server Explorer" perform the following actions:

1. Click on "Server Explorer" tab in the left hand side, then click on "Add Connection"

SQL Server : Attaching A Database File to SQL Server 2012

There are times when you are handed over an .mdf file and are tasked to create a new database out of it.  There's no log just an .mdf file.  The easiest way to create a new database is to use the query editor in SQL Management Studio type the following into the Sql Query window

CREATE DATABASE Northwind
 ON (name= 'Northwind',
 filename = 'C:\Northwind.mdf')
FOR ATTACH_REBUILD_LOG;

The query above will create a new database for you call "Northwind", all you have to do is specify the location of the .mdf file and the "FOR ATTACH_REBUILD_LOG" will rebuild the log for you. You might run into a "Access denied" error but it's a permissions issue. Make sure the user that is running the query has permission to the file.

MongoDB In-Depth: Basic MongoDB Commands

In this post we are going to go over some basic commands that you can run on a machine that has MongoDB installed.

One of the first thing you want to do is to connect to MongoDB so that you can run more commands.
Here is the command to connect to MongoDB

mongo --host=localhost --port=27017

All you need is the name of the host and port number, 27017 is the default port number and since we running the command on the MongoDB host, the host is localhost

MongoDB In-Depth: Setting Up And Starting MongoDB For The First Time

In the previous post we installed MongoDB on a Linux orperating system.  On this post we are going to setup the data folder that MongoDB needs and connect to MongoDB in the command line. 

Here are the steps:

1.  Create a folder call /data/ then create another folder call db underneath it with the mkdir command, I've already created the folder so I can't do it on the command prompt again.

git & github : Merge Branch Into Master Branch

In a typical git scenario you would have a new branch for developers to work on, the when he or she is done. You do your code reviews and what not.  After you are satisfied with the results you would want to merge the new branch into the master branch.

In this post I am going to show you how to merge an existing branch into a master branch

Here are the steps:

1. First you want to checkout the master branch to work on it with the command git checkout master

git & github: Create A New Branch

In previous posts we created a new git repository locally and on github and then sync them up.  In this post we are going to create a new branch in the local git repository and push it to the remote repository.


First let's create a new branch with the following command in the local git repository

git checkout -b "Hour2"








Now following the tutorials in this post Hour 2: Enable ASP.NET Core to Serve Static Files

After you are done open the command line in the folder and type git add .

git & github: Sync Your Local Repository With A github Repository

In the previous post we created a local git repository, in this post we are going to create a remote repository in github and link it to the local repository that we created.

Here are the steps to create a new repository in github:

1.  Log into your github account and click on the "New" button next to the text "Repositories"

SQL: The NOT IN Operator

The NOT IN operator in SQL means that you are retrieving records in the database that does not match the values in a comma separated list. In other words it retrieves the inverse of the IN statement by itself. Here is an example of how you can use the IN operator in the products table in the Northwind database.

SELECT * 
FROM Products
WHERE SupplierID NOT IN (1,2)

The above example all the products will be retrieved except for products with SupplierID of 1 or 2, here are the results