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

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

The query above returns the records of employees who were hired on October

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

The query above returns the number of rows in the Customers table

SELECT COUNT(Region) AS NumberOfRows
FROM Customers

The query above counts the number of rows for the column "Region" that are not NULL

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);

Here is how you would execute the query:

int UserId = (int)cmd.ExecuteScalar();

Most of the time you will need to use the Scope_Identity() when you have to deal with foreign key constraints, that's why a Users table is a good example.

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

As you can see several records have unit price that are greater than $15 dollars
Now let's run the query with parantheses

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

Below is the result from the query

Now you are getting result that you've always wanted in the first place. The parentheses tells SQL Server to ignore the order of evaluation and evaluate what is in the parentheses first then evaluate the second part of the WHERE clause.

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

SQL: SELECT Rows Between Certain Dates

Let's say you want to know the orders that takes place in the Northwind database table Orders tables that occurs during the Christmas Eve 1997-12-24 and the New Years Day the following year in 1998-01-01. Here is the SQL to query the OrderID between those date range:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '1997-12-24' AND '1998-01-01'

XML In SQL Server Part 1: Storing XML In SQL Server

There times when you have to store data as XML in a SQL Server database table.  In this blog we will go over how to store XML as data in SQL Server.  There's an xml data type in SQL Server that we can use to store XML data.

Example: Create a database table that contains a column to store XML data using the xml data type

CREATE TABLE Books
(
  Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Book XML NOT NULL
);

If you look at the "Book" column for the table "Books" you will see that it has a data type of XML



Now that we have our table set up, we can insert XML data to into the table

INSERT INTO Books(Book)
VALUES(
 CAST ( '<book>
  <author>Bill King</author>
  <title>ACME Consulting: An Inside Look</title>
  <publisher>ACME Publishing</publisher>
  <language>Swahili</language>
 </book>' AS XML));

In the example above we CAST the type to XML first before we insert the data into the Books column because we want to make sure that the data we are inserting into the column is a well-formed XML data. If you query the table now you will see that there's one record with XML data in the "Book" column

XML In SQL Server Part 1: Storing XML In SQL Server

There times when you have to store data as XML in a SQL Server database table.  In this blog we will go over how to store XML as data in SQL Server.  There's an xml data type in SQL Server that we can use to store XML data.

Example: Create a database table that contains a column to store XML data using the xml data type

CREATE TABLE Books
(
  Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  Book XML NOT NULL
);

If you look at the "Book" column for the table "Books" you will see that it has a data type of XML



Now that we have our table set up, we can insert XML data to into the table

INSERT INTO Books(Book)
VALUES(
 CAST ( '<book>
  <author>Bill King</author>
  <title>ACME Consulting: An Inside Look</title>
  <publisher>ACME Publishing</publisher>
  <language>Swahili</language>
 </book>' AS XML));

In the example above we CAST the type to XML first before we insert the data into the Books column because we want to make sure that the data we are inserting into the column is a well-formed XML data. If you query the table now you will see that there's one record with XML data in the "Book" column

SQL : TRANSACTION

Transaction processing is a concept in SQL that allows you to execute a query or rollback the changes if something goes wrong.  A way of enforcing the data integrity of the database.  As such, you can only rollback INSERT, UPDATE, and DELETE.  Not that there's any use in rolling back a SELECT statement because there's no change in data.

The following is how you would wrap a transaction around a DELETE statement:

BEGIN TRANSACTION
DELETE Products WHERE ProductID = 87
COMMIT TRANSACTION

The above query will only execute if there are no errors, if there's an error the transaction will be rolled back. That's it, that's the whole concept of what a transaction is, if there are no errors then you should get the following message.

(1 row(s) affected)

If you are dealing with multiple statements then you can use the SAVE TRANSACTION, SAVE TRANSACTION allows you to create a placeholder so that you can rollback a transaction at a checkpoint.

For example if you were to INSERT a new order you would need to insert a new record into the Customers table and then the Orders table and then the OrderDetails table. You wouldn't want to rollback the whole transaction if something goes wrong. You might want to record the customer who tries to order your product, but couldn't and then have your customer representative do a follow up to complete the transaction if something goes wrong.

Here is how you would do a partial rollback:

BEGIN TRANSACTION
INSERT INTO Customers(CustomerID,CompanyName)
VALUES ('ACME','ACME Company')
SAVE TRANSACTION StartOrder
INSERT INTO Orders(OrderID,CustomerID)
VALUES (10999,'ACME')
IF @@Error <> 0 ROLLBACK TRANSACTION StartOrder
INSERT INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (10999,14,23.25,1,0)
COMMIT TRANSACTION

The above query rolls back the query if there's an error. Notice the IF @@Error condition.

=

SQL : TRANSACTION

Transaction processing is a concept in SQL that allows you to execute a query or rollback the changes if something goes wrong.  A way of enforcing the data integrity of the database.  As such, you can only rollback INSERT, UPDATE, and DELETE.  Not that there's any use in rolling back a SELECT statement because there's no change in data.

The following is how you would wrap a transaction around a DELETE statement:

BEGIN TRANSACTION
DELETE Products WHERE ProductID = 87
COMMIT TRANSACTION

The above query will only execute if there are no errors, if there's an error the transaction will be rolled back. That's it, that's the whole concept of what a transaction is, if there are no errors then you should get the following message.

(1 row(s) affected)

If you are dealing with multiple statements then you can use the SAVE TRANSACTION, SAVE TRANSACTION allows you to create a placeholder so that you can rollback a transaction at a checkpoint.

For example if you were to INSERT a new order you would need to insert a new record into the Customers table and then the Orders table and then the OrderDetails table. You wouldn't want to rollback the whole transaction if something goes wrong. You might want to record the customer who tries to order your product, but couldn't and then have your customer representative do a follow up to complete the transaction if something goes wrong.

Here is how you would do a partial rollback:

BEGIN TRANSACTION
INSERT INTO Customers(CustomerID,CompanyName)
VALUES ('ACME','ACME Company')
SAVE TRANSACTION StartOrder
INSERT INTO Orders(OrderID,CustomerID)
VALUES (10999,'ACME')
IF @@Error <> 0 ROLLBACK TRANSACTION StartOrder
INSERT INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (10999,14,23.25,1,0)
COMMIT TRANSACTION

The above query rolls back the query if there's an error. Notice the IF @@Error condition.

=

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

The query above returns the number of rows in the Customers table

SELECT COUNT(Region) AS NumberOfRows
FROM Customers

The query above counts the number of rows for the column "Region" that are not NULL

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

The SOUNDEX function is a cool function that you can talk about at your next dinner party. It searches for the words that sounds the same but are not. Like the query below, which queries product names that sounds like the word "Chief" in the Northwind Products table.

SELECT ProductName
FROM Products
WHERE SOUNDEX(ProductName) = SOUNDEX('Chief')