SQL SERVER – SQL Basics: SQL Code Generators – Day 6 of 10

This is the 6th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Almost everyone these days feels comfortable with using a “point and click” process to get things done with a computer. Only a small percentage of us have ventured into writing code or working with command line utilities to create the programs that perform these tasks. Whether we write code or use clicks, what really matters is getting the job done. Computers will respond to their instructions, regardless of how they receive them. Whether we copy a file with a drag and drop operation, or use the copy command in a console window, the end result is the same; the file has been copied from one place to another.

With the SQL Server Management Studio User Interface (SSMS UI), we can create a table by writing code in a query window, or use the “point and click” method. Point and click allows us to create a table without writing any code ourselves. It is much easier to send someone the code script to accomplish a task with SQL Server than it is to send step-by-step instructions on how to point and click their way to accomplish the same task. For this reason, code script is the most common method used to consistently and reliably deploy new databases and objects the exact same way on many systems.

It is much faster to run code that has already been written and tested by someone else than it is to accurately and consistently navigate the user interface. There are times when creating long constructions of code is faster with the SSMS UI than it is to write out each line of code by hand. In addition, some people simply prefer to point and click their way to creating objects in SQL Server.

In either case, it is possible to instruct SQL Server to convert all the point and click steps into T-SQL code scripts. There is a handy feature within SQL Server known as a “Code Generator” which automatically generates T-SQL scripts to perform specific actions for objects that already exist in the database, including the database itself.

Automatic Scripting

To demonstrate how code generators work in SQL Server Management Studio let’s start by expanding the dbBasics database folder in the Object Explorer and looking at some of the tables it contains.

There are two tables in the dbBasics database which appear to be duplicates.

There is a [Shopping List] table (with a space between the words), as well as a [ShoppingList] table (without a space between the words). When we look at the two queries above our suspicions are confirmed. In addition to having similar names, these two tables share the exact same data and we don’t need both of them in the database at the same time.

One way to solve this dilemma is by using the point and click method. We can right-click on the [Shopping List] table, and then choose the Delete option from the pop up menu. This will open a Delete Object dialog box that gives us several choices. We are NOT going to click OK yet but want to go over the buttons first. We can click the ‘OK’ button to confirm we want the table deleted, or we can press the ‘Cancel’ button to exit out of the operation. What many SQL users are not aware of is that when we press the OK button, SQL Server will actually execute T-SQL code in the background, relieving us from having to write the code to delete this table ourselves.

What if we are asked to help delete this table on a server that someone else owns, and that we are not allowed to access directly? This is a common situation. When a SQL Server is being used in a production environment, very few people have access for security purposes. Since our job is to provide the code that will be run on this production SQL Server, it will be up to someone else to perform the action to delete the [Shopping List] table either with code or a point and click solution.

Years of personal experience tells us that if we try to talk someone through all of the point and click steps necessary to accomplish this task there is a chance for a misunderstanding. A simple misunderstanding when deleting a table could have an immediate negative effect on mission critical readiness, which is definitely not a desirable outcome. It will be far easier and safer to create this action as a script that we could send to the company’s SQL Operations team. This would allow another person to run the scripted action on their machine with the exact same results as the code we have already safely tested. This is also a better result as the other person can complete their work with just one execution step.

A task like the one just described is a great use of the code generator feature. The SSMS UI, as the T-SQL code generated for these types of actions will always be consistent and safe. It is the exact same code that SQL Server runs when we perform a task via the point and click method.

We can discover how to use the code generator by going through each of the point and click steps (plus one to generate code). Instead of pressing OK as the last step to delete the table, we will press ‘Cancel’ to prevent the table from being deleted until we test the code that has been generated for us by SQL Server.

We have been asked to write a script that can be used to delete the Members table at some point in the future. Of course, we are tasked to write this script when we are already in the middle of an important test. The quickest and safest way to write this new script is to use the SSMS UI to generate the code for us.

The first step is to right-click the Members table of the dbBasics database then choose the Delete option from the popup menu. This will open a Delete Object dialog box giving us several options to choose from.

Warning: DO NOT left-click the OK button during any of these steps as it will instruct SQL Server to execute the code which will delete the Members table, preventing this exercise to work correctly.

The next step is to find the Script dropdown menu button located at the top of the right-hand pane in the Delete Object dialog box (figure below). Simply left-click on the dropdown menu arrow and then choose the Script Action to New Query Window option. Finish the process by left-clicking on the Cancel button to exit the dialog box and view the code generated in the new query window.

A new query window will open in SSMS containing the code generated to delete the Members table of the dbBasics database. An example of what this code will look like is shown here:

USE [dbBasics]
GO
/****** Object: Table [dbo].[Members] Script Date: 8/29/2013 2:15:19 PM ******/
DROP TABLE [dbo].[Members]
GO

We can verify that this code works properly by clicking on the ‘! Execute’ button to run the code. When this is complete it will be necessary to refresh the Tables folder inside the dbBasics database (right-click the Tables folder, then select the Refresh option in the popup menu). Once this folder is refreshed we can visibly see that the Members table no longer exists in the dbBasics database.

The SSMS code generator is very useful in these types of situations, as it will quickly produce reliable code to perform basic database maintenance actions. It also comes in handy for those times when it is difficult to remember exactly what syntax is necessary to write certain T-SQL code. It can even serve as an excellent learning tool to show what code will accomplish certain actions that we have yet to work with.

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – SQL Basics: Using Management Studio – Day 5 of 10

This is the 5th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

By now you have written several queries. This means you have opened SQL Server Management Studio and then opened a query window to write your code. Once it came time to execute your code you can do so by pressing F5 or clicking the Execute button. Management Studio connects to your server and provides you handy tools to manage your databases. You even have the Object Explorer to browse all the parts of your server to see what is present. But what if one of your windows is gone? Or what if you are doing a presentation and the fonts are too small to be seen in the back of the room? You can customize the look and feel of Management Studio to your liking. This blog post will show you how to navigate the common windows in Management Studio.

Management Studio Windows

Today is a normal day at the office. We are working in Management Studio and have a query window open and we are ready to write a query. We may want to explore the server so we also have the Object Explorer open on the left.

 

It is possible that you need to look at something in the Object Explorer and cannot find it. It is possible to move and resize windows in Management Studio and Object Explorer is the most commonly used window. If we were to click the pushpin icon in Object Explorer as shown in the 1st figure below the entire window will collapse to the left  as seen in the 2nd figure below.

 

Collapsing Object Explorer can be useful if we need to increase the size of the query window to view a long line of code. To open Object Explorer simply click on it against the left margin and it will open over the query window. The pushpin icon will be lying on its side. To pin it beside the query window simply click the pushpin icon again and Management Studio will return to the configuration. If we need to quickly look at Object Explorer but not open it back up completely we can hover the mouse over the Object Explorer on the left margin and the window will appear. As soon as we remove the mouse the Object Explorer will collapse again. This automatic opening and closing of the window is called auto hide.

We can also cause Object Explorer to float with a click and hold on the title bar, dragging it, and letting it go in a new location. This can even be outside the constraints of the Management Studio desk top.

 

It is possible to snap the Object Explorer in each of the four compass headings in the editing area of Object Explorer. When we click and hold the title bar a compass appears. If the mouse pointer is hovered over one of the nine points on the compass and released, the Object Explorer will snap into the corresponding position. In the figure below the nine positions are numbered and we will choose position 2.

 

On the compass, position 9 will fill the entire editing area, position 6 will snap the Object Explorer to the right half of the editing area, and position 2 will snap it to the right quarter of the editing area.

 

Go ahead and play with this tool by snapping Object Explorer to different positions on the compass. When you are done, we can set the screen back to the default settings by selecting Window > Reset Window Layout. A popup window will appear asking if we are sure we want the default settings, select Yes.

 

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Code Comments – Day 4 of 10

This is the 4th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Code Comments

Old classic movies utter this famous phrase “Gentlemen, this is off the record”. In the movies this is used when talking to the press and letting them know a certain comment or two will be said, however; it is not meant for publication in the media. Sometimes, we want to use words or phrases within a query window that we want SQL Server to ignore when executing the code.

Fortunately, SQL Server allows us to write words or phrases that are “off the record”, with a coding technique called commenting. When writing a query in the SQL Server query window, we can use special symbols to instruct the query engine to ignore these portions of the code when the query is run.

These special symbols also allow comments to visually look different than the rest of our code, which makes it much easier for the human eye to distinguish from the rest of the code as being something unique, and indicates this portion has a special purpose meant specifically for a human to read and for the computer to ignore.

Thus the main purpose of commenting techniques is to allow us to write words or phrases that are often descriptive notes or warnings of what the code is doing, when to run it and why it is necessary. This is a big benefit when other people, such as our team members, supervisors, or software testers are reviewing the code for their own purposes. In other words, comments are commonly used as a mini informational ‘readme’ file allowing other people reading it to learn more about the code being used.

Another great use of commenting techniques is to troubleshoot or test smaller segments of code without needing to modify or delete surrounding blocks of code that are around the segment we want to run by itself. This is a more specialized use of comments that every good SQL Developer needs to know.

Single-Line Comments

How can we make just one line in a query window not run, while the other lines of code still run as expected? Typing two hyphen signs ‘–’, one after the other without a space, will instruct SQL Server to ignore every character that follows it on the same line. This coding technique is known as a single-line comment.

In the figure below we see a query with a single-line comment written as the first line, describing the purpose of the code below it. The comment in this example shows other developers and testers what action the code is expected to accomplish. Indeed, when we run this code block, the result set contains all the fields from the Employee table and the Location table based on the matching LocationID field using the INNER JOIN keyword.

--This query joins the Employee and Location tables together.
SELECT *
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

 

However; the description is overly simple, as it does not explain why the two tables are being joined together, and what service or department might need the results from this query.

It is important to use comments wisely, by considering who, what, when, where, and why for describing the purpose of the code. The challenge is to write notes that balance being descriptive and brief, so anyone else that may read it days, weeks, months or even years from now can understand why it exists and what it is meant to achieve.

A better example of how comments can be used to describe code in a work environment is shown in the figure below. While adding in more detail by indicating who the code is written for (HR), the specific purpose of why the code exists (Employee Report), and also draws attention to which fields from each table need to be included in the results (all Employee fields and the Address, City, and State Location fields).

Since the comments in this example take up two lines in the query window, it is necessary to start each new line of comments with a new set of double hyphen signs, so SQL Server will know to ignore the first two lines of the query.

--This gets all fields from Employee table
--plus Address, City, & State from the Location table
SELECT em.*, lo.Street, lo.City, lo.[State]
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

It is also possible to use single-line comments for testing specific segments of code. This commenting technique gives us the ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code we want to inspect closer is able to run.

Let’s say that while building the code for the HR Employee Report, something was not working right. We suspect there is a problem with the join to the Location table and believe that the code for the Employee table segment is correct. We want to test our theory by only running the segment of code with the SELECT statement for the Employee table without having to rewrite it as a separate query or deleting any of the code in the INNER JOIN segment.

This can be accomplished by commenting out the last two lines of the code, which will instruct the SQL Server query engine to ignore these two lines of code and only run the first two lines of code. The following code will not execute the INNER JOIN for the Location table and will only return the records from the Employee table, as shown in the figure below.

SELECT *
FROM Employee AS em
--INNER JOIN Location as lo
--ON em.LocationID = lo.LocationID

Multi-Line Comments

There are times when we want to run part of a query or just a few lines for a quick test. Once the test is over we want all of the code back in its original state. For these purposes many SQL developers choose to highlight (select) only the segment of code they are interested in executing and ignore the remaining code block.

This next code example demonstrates how it is possible to run a segment of code that is part of a larger code block without using any commenting techniques.

Notice that the SELECT and FROM lines of the query are highlighted in blue for the Employee table. While the INNER JOIN is part of our query, since it is not highlighted (selected), this segment of code will not run when the segment of code that is highlighted in blue gets executed. When we run the highlighted segment of the query, we see that only the fields from the Employee table are returned. The segment of code with the INNER JOIN is ignored by the query engine.

--This query joins the Employee & Location tables together
SELECT *
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

When we want to comment out many consecutive lines of code, we have two different commenting techniques we can use to accomplish this goal. Use single-line comments for each and every line to be prevented from running, or use the more efficient multi-line commenting technique to disable small or large segments of code with an easy to use beginning and ending sign.

The first option is to use single-line comments by placing the double hyphen sign at the beginning of each line we want to prevent running when the query is executed. We used this technique in the previous section, so the following code should look familiar.

SELECT *
FROM Employee AS em
--INNER JOIN Location as lo
--ON em.LocationID = lo.LocationID

The second option is to use multi-line comments by placing a ‘/*’ (forward slash, asterisk with no spaces) sign at the start of the segment to be commented out and then close the comment with a ‘*/’ (asterisk, forward slash with no spaces). Let’s see how this works by using the same query we have been practicing with. The results of using this multi-line commenting technique are the same as those returned by using single-line comments.

SELECT *
FROM Employee AS em
/*INNER JOIN Location as lo
ON em.LocationID = lo.LocationID*/

In this example of commenting out only two lines of code, typing two sets of ‘–‘ signs, is about the same amount of work as typing a matching set of ‘/*’ and ‘*/’ signs. If this is the case, why choose one technique over the other?

The amount of effort to type the ‘–‘ signs for a single-line comment increases dramatically as the number of continuous lines to be commented out grows. What if we wanted to disable the last 300 lines of code? Typing ‘/*’ and ‘*/’ signs one time each, is definitely easier than typing the ‘–‘ sign 300 times to achieve the exact same result. Unlike the double hyphen, which can only instruct SQL Server to ignore one line of code at a time, the ‘/*’ ‘*/’ signs (delimiters) are more efficient for multi-line commenting as there can be an infinite number of lines of code between the opening and closing delimiters.

Imagine if we had needed to comment out these 300 lines of code for testing purposes and as soon as we completed our test, it was necessary to uncomment the same 300 lines of code for the next member of our team to review. Removing one set of opening and closing ‘/*’ ‘*/’ delimiters is much simpler and faster than removing 300 sets of double hyphens!

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Joining Tables – Day 3 of 10

This is the 3rd post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Relational Data

When was the last time we received a vague answer to a question? For most of us, it happens every day. Let’s say we asked someone where they worked. We are anticipating a response that may include a city name or address, except the answer we actually get is, “I work at headquarters”. While this is an accurate answer, it is not the detailed answer we wanted to know.

After detecting a slight New England accent from James Newton, we decide to look him up in the Employee table and discover that he works at LocationID 2. In what city or state is this mysterious LocationID 2 located? A quick query of the Location table shows us the following data.

SELECT *
FROM Location

Now each time we see an employee listed for LocationID 2 we know the street, city and state information for where they work. Why not just store this information in the Employee table? In fact, why not put all of our data in one giant table so we only have one place to go to get our information? That is a common interview question about database design so let’s review a few database design best practices to start building our interviewing arsenal.

One reason is a lot of space in memory is saved by not replicating all three data items for each employee in a table. Another reason for having location fields only in the Location table is that it saves us time as well. For example: What would happen if the office at LocationID 2 physically moved from Boston to a new building in nearby Cambridge? If all the data were in a giant table, then we would have to update every street, city and state for each employee individually. This is a very tedious task that leaves a great deal of room for errors and inconsistency in how the data is entered. Placing the LocationID field only in the Location table means all employees with LocationID 2 map to an update we can make just once.

So, how do we find an employee’s address if the information is spread between two tables? Each table has a LocationID field inside it. We can then use a two-part identifier, to distinguish them as the Employee.LocationID field corresponds to the Location.LocationID field.

Look at Alex Adams and Barry Brown in the following figure. These employees both work at LocationID 1. If we were new to the company and only had access to the Employee table, we would not have enough detailed information to send a parcel to Alex Adams. What if we put two tables next to one another on our screen? By physically drawing a line from the Employee.LocationID field to the Location.LocationID field we can get more location details for each employee. LocationID 1 is located at 111 First ST in Seattle, WA.

What about a global company with locations in all 50 states and over 100 different countries? We will have many records in our Location table and probably will not be able to look at both tables very efficiently on one screen.

How can we effectively see information in two different tables at the same time? Our ultimate goal is to show the Employee and Location information in one result set. Since we have not learned the code on how to do this yet, it is not shown in this figure. The results shown are the goal of the upcoming example.

Inner Joins

So far, we have learned that each query can have only one result set and will only allow a single FROM clause. How can we place two tables in one FROM clause? We can include many tables in one FROM clause by using a JOIN clause. The most common type of a join is called the INNER JOIN.

An INNER JOIN clause allows us to join multiple tables in a single query, although it requires a specific condition in order for it to work correctly. We must ensure that the INNER JOIN statement has two tables with at least one common or overlapping field. We already know the Employee and Location tables share a common field (LocationID). The relationship is between Employee.LocationID and Location.LocationID, so we instruct SQL Server that the INNER JOIN is on this field and voila! We have combined two tables into one result set.

Every time a value is found in Employee.LocationID, the inner join will searche for the matching record in the Location.LocationID field. If a match is found, data from both tables are displayed as a single record. Both tables will show all their fields if we type SELECT * at the beginning of our query.

SELECT *
FROM Employee INNER JOIN Location
ON Employee.LocationID = Location.LocationID

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Running SQL Code – Day 2 of 10

This is the 2nd post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Running SQL Code

When we run SQL code, it is often a series of SQL statements created by someone else. Still we are often tasked with adding to the code to customize it for our system or testing the code and making suggestions. For this reason the SQL Admin must know basic SQL coding skills. This section will focus on the most common types of queries. If being great at all types of queries is important to you, then we recommend a deeper study with the Joes 2 Pros SQL Queries series starting with Volume 1 for beginners and work through to Volume 5.

Single Table Queries

When we want information from a database, we write a query, such as SELECT filename(s) FROM TableName
. In English we might say, “Show us the information!”

Let’s write a simple query. We will use the dbBasics database context and request all records and fields from the ShoppingList table as part of the result set. The keyword SELECT requests information to be displayed and the asterisk (*) sign is a shortcut for all the field names. The keyword FROM chooses a specific table.

We are able to run this query to retrieve the information we want by either clicking on the ! Execute button in the toolbar above the query window, or by pressing the F5 button on our keyboard (some keyboard configurations require pressing the fn (function) + F5 key simultaneously).

Basic Query Syntax

A group of students in one of my weekend classes helped to write a neatly summarized guide listing the meaning of words in a basic SQL query. Here is the code sample:

USE dbBasics         --choose DATABASE Context
GO                   --complete USE statement
SELECT *             --choose Field(s) to display
FROM ShoppingList    --choose the Table

Now is a good time to point out that words can be placed in a query window that have nothing to do with the SQL code itself. We can write non-SQL words and notes like “I wrote this query today” above the SELECT statement. In order to do this we must tell SQL Server to ignore this text since it is not intended to be code. In fact, it should be non-executing code known as comments. To make comments, we need to begin the line with two hyphen signs, one after the other with no spaces between them. Change the database context back to JProCo with the drop-down window in the toolbar.

Use this example of code:

-- I wrote this query today
SELECT * FROM Location

The preceding code runs fine in JProCo. SQL Server ignores the first line because of the double hyphens. The comment is there only for the benefit of the human reading it. This is a useful way to make notes that later provide us, or our team with key hints or details explaining what the code is attempting to accomplish.

Table names can optionally have square brackets around them and it will not change the result set. Changing back to the dbBasics context, we can run the following two queries. Although one query uses square brackets and the other does not use brackets, they will both operate identically by returning a result set.

SELECT * FROM [ShoppingList]
SELECT * FROM ShoppingList

When coding, we rarely use square brackets because it requires extra typing. The only time it is helpful is when we can’t tell when a table name starts or stops. For this demo, the dbBasics database has two identical tables named ‘ShoppingList’ and ‘Shopping List’. The latter contains a space in its name, which is generally a bad naming practice for any database object.

For situations like this we must use square brackets as a delimiter. Without these delimiters, SQL Server will think the table is named ‘Shopping’ and there is a command named ‘List’, which it does not recognize and will result in an error message.

Of course, as shown in the two earlier queries, we can put square brackets around any table. In fact, code generated automatically by SQL Server always creates these delimiters for every object in the database. The only time we must use the square bracket delimiters is when table names are separated by a space, have the same name as a SQL Server keyword (bad idea), or are otherwise named in a way that is not obvious or easily interpreted by SQL Server.

We can easily correct this error message by placing square bracket delimiters around the Shopping List table name. Once this is complete, the query will run without an error  and we will get the same seven records in our result set as the first query using the ShoppingList table (not the [Shopping List] table).

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Basics: Data and Information in Businesses – Day 1 of 10

This is the 1st post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Databases in Business

Let’s take the movie industry for our first example. I was a young boy back in early 70-80s when the movie Star Wars hit the silver screen. Within a week the news agencies were talking about what a hit this movie appears to be. There were no numbers yet but evidence was mounting that things were good for Star Wars. Mostly the news cameras would drive by the theaters and see the long lines of people waiting to see Star Wars.

One news agency called the 30 theaters in the USA that showed Star Wars on its opening night to ask about the sales. The theater owners in 29 of the 30 theaters said it was a record weekend for them. OK we are starting to get some numbers now but how about the weekend box office total nationwide or even worldwide? Well after months of accounting the first week and first month’s totals were tallied and this movie would become the biggest hit of all time. It took a team of accountants with ledgers to eventually turn out these totals.

Based on the hit of the movie (and the memorable characters) a decision was made to start making toys and action figures based on the movie which were sure to be the hottest Christmas toys to hit the market. The only problem was the months it would take to design, approve, and test these toys meant they would be out in March of 1978. Had that decision to make these toys been made earlier there would have been millions of dollars’ worth of toy sales in 1977.

Contrast this example with the release of “Harry Potter and the Sorcerer’s Stone” back in 2001. On the Monday morning after the weekend release the news agencies said this movie made 90 million in the opening weekend. When they announced this, the weekend had just ended a few hours ago. So how did this total come so fast? Did a team of accountants work in the early hours of the morning to make this happen? All these sales were rolled up into a database and with one single query a grand total was instantly available.

For centuries we have been collecting data. In 1977 and in 2001 we were collecting data. What has changed now is how fast we can get back our information. This means the reaction of the market to our products and marketing can be instantly known. These capabilities allow businesses to make decisions with that information.

Database Management Systems (DBMS)

Databases have been around since cavemen were drawing stick figures of their family’s on the rock walls. A database is a collection of related information. In the last 20 years what has improved greatly is we can get the information we need instantly from databases. For example if we ran a test promotion in Florida that we were thinking about running around the world we would want to know how well the promotion affected sales. In this case we want to compare the sales gains in Florida to all other areas. There may be millions of sales in Florida and billions of sales everywhere else. That is far too much data for a human ledger. We need a system that can both collect and pull out this information for us. SQL Server is a Database Management System (DBMS) that (if we know how to talk to it) will be our best business friend.

Data vs. Information

Remember that example of Harry Potter making 90 million in the opening weekend? Was that 90 million stored in the database? Actually there were many records of people paying 5-7 dollars but nowhere is there a record of 90 million. If we added the numbers together then we would see this 90 million tally. So, the 90 million was not really the stored data but the answer to a question about the stored data. This gets us to the definitions of data and information. Data is what is being stored in a database, such as raw numbers, text, or images. Information is what we look at and often build into business reports. In other words, information is data that is processed or structured in such a way as to have true value to the user. It’s useful for businesses to have the right information quickly, and SQL Server is a true master at turning data into information.

The power in the DBMS is when it is necessary to tabulate the weekend gross receipts for the latest weekend movie across the nation. We want to do this easily. Do we really want to ship all the ticket stubs from every box office across the country to a central accounting desk and then count the piles of receipts? No. Fortunately, the movie industry uses a DBMS so that by Monday morning it instantly knows the amount for the weekend gross receipts. This is the benefit of being able to turn data into information.

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards

Note: Read at the end of the blog post how you can get five Joes 2 Pros Book #1 and a surprise gift.

I have been blogging for almost 7 years and every other day I receive questions about Querying Pattern Ranges. The most common way to solve the problem is to use Wild Cards. However, not everyone knows how to use wild card properly.

SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners Book On Amazon | Book On Flipkart

Learn SQL Server get all the five parts combo kit Kit on Amazon | Kit on Flipkart

Many people know wildcards are great for finding patterns in character data. There are also some special sequences with wildcards that can give you even more power. This series from SQL Queries 2012 Joes 2 Pros® Volume 1 will show you some of these cool tricks.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter2.2Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

Querying Pattern Ranges

The % wildcard character represents any number of characters of any length. Let’s find all first names that end in the letter ‘A’. By using the percentage ‘%’ sign with the letter ‘A’, we achieve this goal using the code sample below:

SELECT *
FROM Employee
WHERE FirstName LIKE '%A'

To find all FirstName values beginning with the letters ‘A’ or ‘B’ we can use two predicates in our WHERE clause, by separating them with the OR statement.

Finding names beginning with an ‘A’ or ‘B’ is easy and this works fine until we want a larger range of letters as in the example below for ‘A’ thru ‘K’:

SELECT *
FROM Employee
WHERE FirstName LIKE 'A%'
OR FirstName LIKE 'B%'
OR FirstName LIKE 'C%'
OR FirstName LIKE 'D%'
OR FirstName LIKE 'E%'
OR FirstName LIKE 'F%'
OR FirstName LIKE 'G%'
OR FirstName LIKE 'H%'
OR FirstName LIKE 'I%'
OR FirstName LIKE 'J%'
OR FirstName LIKE 'K%'

The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when a query requires a large range of letters, the LIKE operator has an even better option. Since the first letter of the FirstName field can be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘J’ or ‘K’, simply list all these choices inside a set of square brackets followed by the ‘%’ wildcard, as in the example below:

SELECT *
FROM Employee
WHERE FirstName LIKE '[ABCDEFGHIJK]%'

A more elegant example of this technique recognizes that all these letters are in a continuous range, so we really only need to list the first and last letter of the range inside the square brackets, followed by the ‘%’ wildcard allowing for any number of characters after the first letter in the range.

Note: A predicate that uses a range will not work with the ‘=’ operator (equals sign). It will neither raise an error, nor produce a result set.

--Bad query (will not error or return any records)
 SELECT *
 FROM Employee
 WHERE FirstName = '[A-K]%'

Question: You want to find all first names that start with the letters A-M in your Customer table and end with the letter Z. Which SQL code would you use?

a. SELECT * FROM Customer
WHERE FirstName LIKE 'm%z'

b. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'

c. SELECT * FROM Customer
WHERE FirstName LIKE 'a-m%z'

d. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'

e. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'

f. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]%z'

g. SELECT * FROM Customer
WHERE FirstName LIKE '[a-m]z%'

Contest

  • Leave a valid answer before June 18, 2013 in the comment section.
  • 5 winners will be selected from all the valid answers and will receive Joes 2 Pros Book #1.
  • 1 Lucky person will get a surprise gift from Joes 2 Pros.
  • The contest is open for all the countries where Amazon ships the book (USA, UK, Canada, India and many others).

Special Note: Read all the options before you provide valid answer as there is a small trick hidden in answers.

Reference: Pinal Dave (http://blog.sqlauthority.com)