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: 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

SQL: The IN Operator

The IN operator in SQL means that you are retrieving records in the database that matches the values in a comma separated list. 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 IN (1,2)

In the above example all the products with the SupplierID of 1 or 2 are retrieved.

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.

ASP.NET: Getting The Inserted ID Back With Scope_Identity()

When you need to do an insert into multiple database table you need to the get the ID of the insert so that you could use that ID for the next insert. Here is how you would do that with the Scope_Identity()which gets the last inserted ID back to you if you execute your query with the ExecuteScalar() method.

                SqlCommand cmd = new SqlCommand("INSERT INTO Users (" +
                    "LoginName," +
                    "FirstName," +
                     "LastName," +
                     "Password," +
                     "Email," +
                     "DOB," +
                     "Sex" +
                     ") VALUES (" +
                    "@Email," +
                    "@FirstName," +
                     "@LastName," +
                     "@Password," +
                     "@Email," +
                     "@DOB," +
                     "@Sex)" + 
                     " Select Scope_Identity();",conn);

SQL: Using Parentheses To Get Expected Result

SQL Server as well as other DBMS has an order of evaluation that can throw you off. Especially when you have more than one comparison in the WHERE clause. In this example I will show you the difference between using a parentheses and not using one, and how by using parentheses can give the results that you want.  Suppose you want to get the products with CategoryID 1 and 2 that are priced less than 15 dollars in the Products table in the Northwind database. Here is the query without the parentheses:

SELECT CategoryID,ProductName,UnitPrice
FROM Products
WHERE CategoryID = 1 OR CategoryID =2 AND UnitPrice < 15

When you run the query above you would expect that all the records retrieved will have a unit price of less than $15 dollar but that is not the case. Below is the result from the query.

SQL: Checking ShippedDate Column For NULL

Retrieve records with NULL value in the ShippedDate column in the Orders table in Northwind

SELECT OrderID, ShippedDate
FROM Orders
WHERE ShippedDate IS NULL

Retrieve records that is does not have NULL value in the ShippedDate column in the Orders table in Northwind

SELECT OrderID, ShippedDate
FROM Orders
WHERE ShippedDate IS NOT NULL