SQL: MAX() And MIN() Aggregate Functions

The MAX() function gets the highest value in the specified column, and the MIN() function gets the lowest value in the specified column

SELECT MAX(UnitPrice) AS HighestPrice, MIN(UnitPrice) AS LowestPrice
FROM Products

The query above gets the highest and lowest prices for the Products table in the Northwind database


 

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.

SQL: DATEPART Function

The DATEPART function extracts the date part of a date, for example using the 'yyyy' expression allows you to extract the year from a given date. The query below queries all the employees who were hired in the year 1994 in the Northwind Employees table.

SELECT FirstName + ' ' + LastName  AS Employee, HireDate
FROM Employees
WHERE DATEPART(yyyy,HireDate) = 1994

SQL: AVG() Aggregate Function

The AVG() function gets the average of a column, the following query gets the average of the UnitPrice column in the Northwind Products table.

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products

SQL: COUNT() Aggregate Function

The COUNT() function returns the number of rows in the specified table. There are two ways you can use COUNT(), which are the following:

1. COUNT(*) count all the rows in the table including
2. COUNT(column) return all the rows that contains value for the column, excluding the columns with null value

SELECT COUNT(*) AS NumberOfRows
FROM Customers

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"



 

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:

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

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:

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.

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: 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 Server 2014 : Creating a Sysadmin Using a Windows 8.1 Account

The one of thing that Windows 8 forces you to do is to sign in with an e-mail account.  I am not here to debate if it's a good thing or a bad thing.  But I just wanted to say, Microsoft why do you make our lives so complicated.  I just wanted Windows 7 with a touchscreen.  I digress :(   Anyways, if you install SQL Server on Windows 8 there is a little quirk that you have to deal with.  When you search for an account to add to your dba user login, you have to search on the entire username including the stuff after the @ sign.  Once again, I digress :(

Anyways here is how you add a dba to SQL Server 2014 on a Windows 8 machine.

1.  Connect to your instance of SQL Server, then expand the "Logins" node

SQL Server : Installing AdventureWorks Sample Databases from Microsoft

1. Type in the following URL into your browser's address bar

     AdventureWorks Database Download

2.  Click on the "Download" button on page

Hour 15 AspNet.Core Use Repository Pattern To Show Poducts

Now that we have our data in the database it is time for us to show the data to our users and we going to use the repository pattern to show our data.  A repository pattern is basically a conduit between the database and our business objects that has built in CRUD operations.  It is ideal for LOB applications.  There are many websites and books that explains the repository pattern better than I do.  Feel free to explore them.

Here are the steps to create a product repository for the products in the database:

1. Create a folder in the NorthwindCafe.Web solution call "Data"
2. Create an interface call IProductRepository in the "Data" folder, this will be the interface that we implement our repository from.

The code of the IProductRepository should look like this

using NorthwindCafe.Web.Models;
using System.Collections.Generic;

namespace NorthwindCafe.Web.Data
{
    public interface IProductRepository
    {
        IEnumerable<Product> GetProducts();
    }
}

Hour 13 ASP.NET Core : Add NorthwindContext To Startup Class And Create Database

In this post will are going to finally create the database that we have been preparing for in the last previous blog posts.  It's a two step process, first you have to add the NorthwindContext to the application in the Startup class, then you have to run the Entity Framework migration tool.

Here are the steps to create your NorthwindCafe database:

1.  Open the Startup.cs file, then type the following lines in the ConfigureServices method

 
            var connectionString = Configuration["Data:NorthwindContextConnection"];

            services.AddDbContext<NorthwindContext>(options => options.UseSqlServer(connectionString));

The line above gets the connection string from the appSettings.json file that we've created earlier. Then use the AddDbContext method in the services instance.  Dependency injection will take care of the plumbing for you.  Using lamba expression we tell the Entity Framework to use the Sql Sever provider for Entity Framework core.

Make sure you have the following namespaces in your Startup class

using NorthwindCafe.Web.Models;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;

Hour 12 ASP.NET Core : Create The NothwindContext ( EntityFrameworkCore )

In our previous post we created the models for our Northwind Cafe application.  In this blog we will create the DbContext class which is the conduit between your entity classes and the database.  Think of it as a bridge that the database and the entity framework has to cross to get to each other.

Follow the steps below to create the NorthwindContext:

1. Create a class in Models folder call NorthwindContext

Hour 11 ASP.NET Core : Entity Framework Core Models For Northwind Cafe

In the previous post we added a configuration file call appSettings.json file to store our connection string to the database that we are going to create through Entity Framework.  Even though Microsoft provides us with the Northwind database, we don't really want to use it because it's outdated.  We are going to modernize the database by rebuilding it from scratch with the code first approach with Entity Framework Core.  If you look at the existing Northwind database you will see that there's a lot of redundant data and tables.  For example there are tables for Customers, Employees, Suppliers and Shippers.  Those are basically roles, and we will take care of those roles later on in the series using the Identity framework.  What we are going to do is start out simple with just the Products, Categories, Orders, OrderDetails table and add on to those tables as we progress in building the application.

ASP.NET Core: Create The Category Repository

In this post we will create the Category Repository to retrieve information from the database about the the different categories in the NorthwindCafe database.

Here are the steps to create the Category repository class in the NorthwindCafe application:

1.  Create the ICategoryRepository interface for dependency injection, create a file call ICategoryRepository.cs in the "Models" folder with the following code

using System.Collections.Generic;

namespace NorthwindCafe.Web.Models
{
    public interface ICategoryRepository
    {
        IEnumerable GetAllCategories();
        Category GetCategory(int Id);

    }
}