Blogger Sandbox

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  

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

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

The COUNT() function returns the number of rows in the specified table. There are two ways you can use COUNT(), which are the following: COUNT(*) count all the rows in the table including 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

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,"...

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

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 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 (Cate...

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

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'

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

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

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

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

As you may have guessed the SQL mathematical operators are equivalent to their regular mathematical counter parts.  + Addtion - Subtraction  * Multiplication  / Division Eamples: 1. Addition SELECT UnitPrice, (UnitPrice + 20) AS RipOffPrice FROM Products 2. Subraction SELECT UnitPrice,(UnitPrice - 5) AS OutOfBusinessPrice FROM Products WHERE UnitPrice BETWEEN 5 AND 10 3. Muliplication SELECT UnitPrice,(UnitPrice*UnitsInStock) AS InventoryPrice FROM Products 4. Division SELECT UnitPrice,(UnitPrice/2) AS HalfPrice FROM Products

Concatenation means combining the value of two or more columns together to form a single value. The most common usage for this operation is to combine the first name and last name field. Like the query below. SELECT FirstName + ' ' + LastName AS Name FROM Employees The query above concatenates the FirstName and LastName column from the Employees table in the Northwind database and assign the alias Name to combined value.

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

The INSERT SELECT is an INSERT statement that inserts value using a SELECT statement. The following query will insert a new customer using existing record. INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName] ,[ContactName] ,[ContactTitle] ,[Address] ,[City] ,[Region] ,[PostalCode] ,[Country] ,[Phone] ,[Fax]) SELECT 'OPDS', CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE CustomerID = 'ALFKI' Since the CustomerID field cannot be null we have to assign the value 'OPDS' as the new CustomerID for the new customer. The rest of the column values are selected from the Customer with customer id 'ALFKI' Here is the new record as well as the 'ALFKI' customer for comparison.

The UNION operator combines two or more SELECT statements into one result set. The SELECT list must be the same in all queries, same columns, expressions, functions, and data types. All UNION queries can be replaced with a join query. The query below combines the Customers select statement with with the Employees statement SELECT City,Address FROM Customers UNION SELECT City,Address FROM Employees Here is the result:

FULL OUTER JOIN is a join that returns all the results from the left hand side of the = sign and all the results of the right hand side. For example this query returns all the customers and all the orders in one result SELECT c.ContactName, o.OrderID FROM Customers c FULL OUTER JOIN Orders o ON c.CustomerID=o.CustomerID ORDER BY c.ContactName