SQL Training

June 13, 2012 by Joey Blue Leave a Comment

SQL Like

The SQL Like is the clause that allows you to do wildcard searches in the SQL language.

The Like clause can only be used on string data types (char and varchar in Microsoft SQL Server).

You can use it in a SQL statement anywhere you would normally use the “=” (equal sign).

There are two wildcard characters you can use:

% – Is a multi-character wildcard. Which means it will match more than one character.
_ – The underscore is a single character wildcard. It only matches one character.

Let’s take a look at a few examples.

I want to look at a student table:

select * from student;

Here are the results:

So the student table has 7 rows and 8 columns.

Now, lets find all of the addresses that are designated “Rd”.

I have to do this by searching with the SQL Like clause.

Here is the query to do that:

select * from student where S_ADDRESS LIKE '%Rd%';

The where clause says, “I want to look at the S_ADDRESS column and find everything that has any number of characters, then an Rd, then any number of characters.

Here are the results:

We get 3 records that contain the “RD”.

No what if I wanted to find all of the zip codes that had a third character of 1?

The query would look like this:

select * from student where S_ZIPCODE LIKE '__1%';

Let’s take a look at that LIKE string: ‘__1%’

The first part of that string is actually 2 underscores. That is because we want exactly 2 characters, then we want to see the number 1. (I say number, but it is actually a varchar in the database.)

Here are the results:

I have also used it in a CASE statement inside of the SELECT clause, but I will leave that for another post.

As always, if you have any suggestions for topics you would like for me to blog, or if you have any general comments, leave them below.

June 12, 2012 by Joey Blue Leave a Comment

Troubleshooting a SQL Join

I shot a training video today while I was answering a SQL Join question on Yahoo Answers.

During the video, I construct the database, tables, and example data.

Then I tried to run the current query to see what error I get. Finally, I work through the issue and fix the join.

Here is the video:

If you couldn’t see the question well in the video, here is a reprint.

Database SQL query issues?
I have developed the database with the following tables:
STUDENT
S_ID PRIMARY KEY
S_FNAME
S_LNAME
S_ADDRESS
S_CITY
S_STATE
S_ZIPCODE
S_PHONE

SCHOLARSHIPS
S_ID PRIMARY KEY FOREIGN KEY REFERENCES STUDENT
SC_MAJOR
SC_ACADEMIC
SC_ATHLETIC
GPA
S_ID PRIMARY KEY FOREIGN KEY REFERENCES STUDENT
G_GPA
G_CLASS /*(4 is for Seniors)*/

I then inputted data and did the following SQL query:
SELECT S_ID, S_LNAME, S_FNAME, S_ADDRESS, S_CITY, S_STATE, S_ZIPCODE, S_PHONE
FROM STUDENT
WHERE GPA.G_CLASS = 4
ORDER BY S_LNAME, S_FNAME;
I get the following error:
The data content could not be loaded. Column not found: GPA.G_CLASS.
Please assist with any tutoring or assisting. Thank you.
http://answers.yahoo.com/question/

My answer is to put in the inner join condition.

Here is the final query:

SELECT STUDENT.S_ID, S_LNAME, S_FNAME, S_ADDRESS, S_CITY, S_STATE, S_ZIPCODE, S_PHONE 
FROM STUDENT inner join GPA on STUDENT.S_ID=GPA.S_ID 
WHERE GPA.G_CLASS = 4
ORDER BY S_LNAME, S_FNAME;

I am attaching the script to create and populate the tables and the Microsoft Excel file so you can see how I create my INSERT statements.

SqlTrainingOnlineUniversityDB.zip

Let me know if you have any questions or comments by posting them below.

June 12, 2012 by Joey Blue Leave a Comment

SQL Wildcard

SQL Wildcard filters allow you to search within a string to do partial matches.

So you can use them to search for all names that begin with the letter ‘A’.

Or you could search for all addresses that contain the name ‘OAK’.

Let’s take a look at an example. I will start with a basic employee table and run the following SQL query:

select * from employee;

The results look like this:

I want to find all of the employee’s with a name starting with ‘A’.

In order to do this, I need to use a SQL Where clause along with a LIKE wildcard filter.

The SQL will look like this:

select * from employee where employee_name like 'A%';

And here are the results:

I filtered out the 2 rows that have an employee_name starting with ‘A’. (ALLEN and ADAMS).

Let’s look at the where clause of that statement in more detail:

where employee_name like 'A%';

Where – Starts the filter section of SQL

employee_name – Is the column we want to look at.

like – The comparison operator. This is how we want to compare. Some other options would be =, <= ,>=, between, in, etc..

‘A%’ – This is the value we are looking for. We have to enclose it in ‘ ‘ (called ticks). Since the first letter inside of the ticks is an A, it will match the first character. Then we have the %. This means we don’t care how many characters come after the A. The name could contain just the A with no letters after it. It could contain the A with 1 letter after it, or it could contain the A with 100 letters after it. The % doesn’t care.

Now, there is one other wildcard that is often used. It is the ‘_’ (underscore). This is a single character wildcard.

Let’s look at an example. I want to find all names where the second letter is ‘L’:

select * from employee where employee_name like '_L%';

Here are the results of the query:

In each of the 3 results, the second letter of the employee_name is an L.

That is how you use the wildcard filter in the SQL language.

Please leave any questions and comments below.

June 11, 2012 by Joey Blue Leave a Comment

SQL Between

The SQL Between is a way of looking for a range of values when you are trying to filter in a SQL Select statement.

So let’s take at an Employee table with the following query:

select * from employee;

Here are the results:

This employee table has 14 rows. I want to filter this table to find all of the salaries that are greater than or equal to 1100.00 and less than or equal to 1600.00.

I will use the following query to do that:

select * from employee where salary >= 1100 and salary <= 1600;

Here are the results:

Notice how we get the salary of 1100 and the salary of 1600. This is because I used the greater than or equal to and the less than or equal to.

I could reword what I want with the following sentence. I want to filter this table to find all of the salaries that are between 1100.00 and 1600.00.

Then I can write the query like this:

select * from employee where salary between 1100 and 1600;

This query will give you the same results as before, but it uses the BETWEEN clause.

Some things to note about the BETWEEN clause.

First, you only put the column name in there 1 time vs. 2 times with the first query.

Also notice that the BETWEEN clause includes the Endpoints. So, if you look at the query above, we are including in the results a Salary of 1100 and a Salary of 1600.

Now, if you are wondering if one way is better than the other? Whether you should use the BETWEEN clause or the ‘>=’ and ‘<=’?

I would have to say that it is really just a preference. I prefer to use the BETWEEN clause when I can because it helps with the readability of the query.

As you get into more complex queries, you will start to find ways to keep your query as simple and readable as possible so that you don’t loose your mind.

(Trust me, queries can become really complex fast!)

That’s it for the BETWEEN clause.

Drop me a note in the comments section if you have any questions, comments, or suggestions.

June 10, 2012 by Joey Blue Leave a Comment

SQL IN

The SQL IN clause is used when you are filtering data and you want to look for more than one value.

Let’s take a look at the following query:

select * 
from customer 
where customer_company = 'Dell' 
 or customer_company='HP' 
 or customer_company='Walmart';

This query says that we want all of the columns, from the company table, where the customer_company is either Dell, or HP, or Walmart.

Here are the results:

This query works great, but it means you have to type a lot more than you need to.

That is where the “IN” clause comes into play. Instead of typing “customer_company” 3 times like we did in the previous SQL Query, we can write the query as follows:

select * 
from customer 
where customer_company IN ('Dell','HP','Walmart');

The results of this query are the exact same as the results above.

Notice how much less typing I had to do. And, notice the syntax.

It goes column name (in this case customer_company), then you use the “IN” word, then you list all of the values in side of parenthesis and separated by commas.

That’s it. I often use the IN clause instead of a bunch of OR’s, because it is neater and easier for me to read.

As you start putting more complex queries together, it will be a lot easier to keep track of an IN clause instead of a bunch of OR statements.

Remember to put any question, comments, or other topics you would like for me to explain into the comment section below.