Using Multiple Databases in SQL

In SQL Server, you can have multiple databases on the same instance of SQL Server.  In SQL Server 2012, you can have over 32,000 (go HERE to read up more maximum capacity specifications for SQL Server) databases in one SQL Server instance.  So, the question is, how do you use/reference all these databases in the same query.

There are two main ways to accomplish this:

1. The use of USE
2. Reference the database in your FROM statement

First, we can use the word USE.  The general syntax for this is:

USE database

Where database is the database you are referencing.  If our Customer table was in the Billing database, it would look something like this:

USE Billing

SELECT *

FROM Customer

Using USE leaves you rather limited, though, because only the one database can be referenced at a time using USE.

Our other option is to specify the database in our FROM statement.  Let's say, for example, that we have our Customer table in the Billing database, while the Orders table is in the Shipping database.  If we wanted to use both tables in the same query, it would look like this

SELECT *

FROM Billing.dbo.Customer

LEFT OUTER JOIN Shipping.dbo.Orders

on Customer.CustomerNumber = Orders.CustomerNumber

In the above, we put the database, a dot (.), the user (in this case dbo, which means database owner), another dot (.) and then the table name.  Using this method, we can query multiple databases at the same time within the same query.

To read more on USE, go here: USE

To read more on selecting your database, go here: Select a Database

Wildcards while Searching Text in SQL Continued

One part of using wildcards that wasn't covered yesterday was the ability to use the keyword NOT in conjunction with LIKE.  Using an example from yesterday, this returns all customers that start with the letter L:

SELECT *
FROM Customer
WHERE CompanyName LIKE 'L%'

If we put the word NOT before LIKE, we will get all customers that do not start with an L:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE 'L%'

Likewise, we can exclude any customer that has an L in their name:

SELECT *
FROM Customer
WHERE CompanyName NOT LIKE '%L%'

The same works with another one of our examples:

SELECT *
FROM Customer

WHERE FirstName LIKE '_ean'

The above returns all customers that have a name that is four digits and ends in "ean".  The following returns any customer not with four digits AND does not end with "ean".  It could still be four digits, but cannot end with "ean".

SELECT *
FROM Customer

WHERE FirstName NOT LIKE '_ean'

Wildcards while Searching Text in SQL

Let's say you are looking for some text in SQL, but not exactly sure what the text is.  For example, you are looking for any customer where their last name starts with an L.  How would you do that in SQL?

There are two ways you can accomplish this.  The first is by using substring like the following:

SELECT *
FROM Customer
WHERE Substring(CompanyName, 1, 1) = 'L'

This would certainly work, but makes your search NonSARGable (to read up more on SARGable queries, go to this post Non-SARGable Queries)

Luckily, SQL has a logical operator called LIKE (which keeps your query SARGable), that allows you to search for text that matches a specific pattern, as follows:

SELECT *
FROM Customer
WHERE CompanyName like 'L%'

In this instance, the percent sign (%) allows for any characters before or after it, depending on where the % is placed.  The above would look for customers that started with an L.  The following would look for all customers that ended with an L:

SELECT *
FROM Customer
WHERE CompanyName like '%L'

This would look for all customers that had an L anywhere in its name:

SELECT *
FROM Customer
WHERE CompanyName like '%L%'

The % is a wild card option in SQL.  There are other wild card options to consider:

• _ (underscore)
• The underscore represents a single value.  For instance, if you are looking for a four letter name that ends in "ean", you could run the following:

SELECT *
FROM Customer

WHERE FirstName like '_ean'

• [ ]
• A single character within a range or given set.  For instance:

--Range of letters

SELECT *
FROM Customer

WHERE FirstName like '[D-S]ean'

--Set of letters

SELECT *
FROM Customer

WHERE FirstName like '[DEFGHS]ean'

• [^]
• A single character not within a range or give set

--Not in this range of letters

SELECT *
FROM Customer

WHERE FirstName like '[^D-S]ean'

--Not in this set of letters

SELECT *
FROM Customer

WHERE FirstName like '[^DEFGHS]ean'

To read more on LIKE and different wild card options, go here: LIKE

WHILE Statements in SQL

Previously I've spoken about control of flow statements in SQL (see the following: IF...ELSE, GOTO).  Today I will talk about another control of flow statement: the WHILE statement.

A WHILE statement allows you to specify a statement to check and then SQL statement(s) to run while that expression is still true.

The general outline for a WHILE statement is as follows:

WHILE Boolean_expression
{ sql_statement }

• A boolean expression is one that is either true or false
• The SQL statement(s) are the statements to run while the WHILE statement is still true
• If your SQL statement is more than one statement, you will have to begin and end your WHILE statement with BEGIN and END, as seen below.

WHILE Boolean_expression

BEGIN

SQL Statement Number 1

SQL Statement Number 2

END

Following is an example of when a WHILE statement may be useful:

WHILE (SELECT count(*) FROM Customers Where Balance < 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance < 50

END

The above will update the balance of a customer to their balance times 2 until that balance is above $50.  For example, if we have a customer whose balance is $5, it will go through the WHILE statement 4 times until the balance is $80.

Keep in mind that when WHILE statements are used, it is possible to run into an infinite loop (a loop that never stops) if you aren't incrementing your values correctly.  Using the same query, if I change it a little bit, I can get an infinite loop:

WHILE (SELECT count(*) FROM Customers Where Balance > 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance > 50

END

The above will never stop if I have one customer that has a balance over $50.  Because they are over $50, our boolean expression is and will always be true.

To read up more on WHILE statements, go here: WHILE

Creating a Child Form in C#

I spent some time this weekend working in C# on a personal project.  One of the subjects I came across was creating a child form.  A child form opens a new form within your current window.

During my research, I came across the following help document:

How to: Create MDI Child Forms

I have a program I created at work that has different tabs for specific tasks that need to be run.  I will be working on migrating from the tabbed format to the child form format, as this seems to be less confusing and the layout looks better.

A few key points for creating a child form:

1. Create a new windows form in your project for each new window.
2. Create an event handler for when you click on the button/menu item that will open the new form (Create Event Handler)
3. Paste the following in your event handler:

protected void MDIChildNew_Click(object sender, System.EventArgs e)
{

   Form2 newMDIChild = new Form2();
   // Set the Parent Form of the Child window.
   newMDIChild.MdiParent = this;
   // Display the new form.
   newMDIChild.Show();
}

Subqueries in SQL

A very helpful feature of SQL is the use of subqueries.  These subqueries allow you to nest one query inside of another query, thus giving you more flexibility with your data.  There are two areas in which a subquery can be used.  The first of these areas is in the SELECT statement.  For example, if we wanted to see how many purchases a customer has made, we can run a query like this:

SELECT CustomerNumber, CustomerName,
(SELECT SUM(Quantity)
FROM Orders
WHERE Orders.CustomerNumber = Customer.CustomerNumber) as Quantity
FROM Customers

In the above example, we used a subquery on the Orders table to get the total quantity (number of purchases) and then the Customers table to get our customer information.

The second instance where a subquery can be used is in the WHERE clause.  This gives us the ability to look for a particular set of data.  As another example, let's say that we want to look for customers that have an order greater than $50:

SELECT *
FROM Customers
WHERE CustomerNumber in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

The subquery returns all CustomerNumbers that have an order of $50 or greater.  We then tell SQL to look for all those (in) CustomerNumbers in our Customers table.  We could also tell SQL to give us CustomerNumbers that are not in the list by putting the word "not" in front of "in, like so:

SELECT *
FROM Customers
WHERE CustomerNumber not in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

This would return all customers that did not have an order of $50 or more.  The word "in" in this situation is called a "logical operator", which we will discuss in my next blog.

To read more on subqueries, go here: Subquery Fundamentals