SQL Tips, Tricks, and Training - SQL Training Online

June 15, 2012 by Joey Blue Leave a Comment

SQL Functions

Today I want to talk about SQL Functions and how they are used in the SQL language.

Let’s start by looking at a simple employee table.

select * from employee;

This employee table has 14 rows.

Now, there are some built-in SQL Functions that allow you to average, sum, get the max and minimum and count. (There are others too, but I am only mentioning a few.)

These functions will look at a group of rows and perform the required action.

So, what if I want to get the average salary of all 14 of my employees?

Then I would do the following:

select AVG(salary) from employee;

The average function is actually just AVG and you put the column in between the parenthesis.

Here are the results:

So the average salary is 2073.21…

That was pretty easy. But, what if you want the column to have a name?

select AVG(salary) as average_salary from employee;

You do what is called a column alias. After you put the column in (in this case AVG(salary) ) you put in the “as” keyword, then you put whatever you want to name the column. (In this case I called the column average_salary)

And here is the result:

And now your column has a name.

So now let me go ahead and use the rest of the functions I mentioned.

select 
AVG(salary) as average_salary 
,sum(salary) as sum_salary 
,min(salary) as min_salary 
,max(salary) as max_salary 
,count(salary) as count_salary 
from employee;

And here are the results:

Not too difficult? But, I want to try one more thing.

I want to use the SUM function and the COUNT function to create my own average function.

If you remember from Algebra, the average is calculated by summing all of the values and dividing by the number of values.

So let’s give it a try:

select sum(salary)/count(salary) as my_new_average 
from employee;

Notice how I took the Sum/Count.

Here is the result:

Notice that the value is the same as when we used the built in average function. That is what we would expect.

So that is an introduction into SQL Functions.

Leave any question, comments, or topic suggestions bellow.

June 14, 2012 by Joey Blue Leave a Comment

SQL Order By

The SQL Order By allows you to sort your results when you run a SQL Query.

Let’s look at an example:

select * from employee order by employee_name;

This will sort by the employee_name column.

Here are the results:

If you want to change the sort from Ascending (which is the default) to Descending, you would write your query as follows.

select * from employee order by employee_name desc;

With the following results:

You can also sort by more than one column.

select * from employee order by job asc,employee_name desc;

Notice how job and employee_name columns are both sorted.

And the last trick is that you can use the column number. In this case I will sort by the 3rd column (job) then the 2nd column (employee_name).

select * from employee order by 3 asc,2 asc;

Notice that I changed the employee_name column to sort ascending.

And that’s it for the SQL Order By clause.

Leave any comments or questions below.

June 13, 2012 by Joey Blue Leave a Comment

SQL Jobs in Oracle and Microsoft SQL Server

I was browsing around Yahoo Answers and I started to answer a question about Jobs in SQL and Oracle.

It was interesting to me, so I decided to repost the answer here and elaborate a bit.

Here is the question:

What is difference between oracle and SQL I want to know about . . . ?

which type of jobs are available on oracle course and Sql
Oracle and Sql are same or different ? And tell me about types of jobs with oracle like oracle DBA or oracle developer like this . . . .

I had to interpret quite a bit about what they were wanting to know, so here is my attempt.

Oracle is a relational database. There are other relational databases out there, like Microsoft SQL Server, MySQL, DB2, etc.

All of these relational databases use SQL to put data into and take data out of the relational databases.

Then, they have their own “programming” languages.

Oracle has their PL/SQL language that you use to write stored procedures and functions.

Microsoft SQL Server has their TSQL language that you use to write stored procedures and functions.

So now that we have defined some of the terms, what jobs are available?

An Oracle DBA (Database Administrator) would take care of database instances and make sure they keep running, they don’t run out of space, they are secure, they have redundancy where needed, and stuff along that line. They have traditionally been paid a bit more than a Microsoft SQL Server DBA. But, Oracle has many different “levers” you can pull to tune it, so their is quite a bit to learn to be a real good Oracle DBA.

An Oracle Developer would most likely need to learn PL/SQL. PL/SQL allows you to write functions and stored procedures that end up as objects on the Oracle server. You would also need to know basic SQL in order to be effective. Basic SQL would include SELECT, INSERT, UPDATE, DELETE statements that allow you to put data into the database and get data out.

So the jobs would be Oracle DBA (takes a bit of experience unless you work as a Junior DBA under a Senior DBA.), Oracle Developer (this is the PL/SQL side), and then a regular developer that would work in .Net or Java.

The regular developer position would write programs that interface with the Oracle database, but you would use the .Net (C#/VB.Net) or Java along with SQL to interact with the database.

There are so many different positions I have seen throughout my years in IT.

Once you start a position, the lines end up getting blurred quite a bit. You have your main responsibility, but you will end up crossing over and doing some scripting or programming.

Take a DBA for example:

They have the responsibility to make sure the database stays up and running and secure. But, in order to do their job efficiently, they will most likely write some scripts that recompile indexes on some interval, or the script with defragment the disk, or check for errors in the logs. They definitely need to know SQL in order to create and recompile objects.

DBA’s will also need to do some performance tuning of queries. But, this job is also a developer’s job, so it crosses over and many times will need to be a collaborative effort.

So you can see that a DBA’s job has already crossed over to a Developer’s a little bit.

I’m going to cut it off there, but I could keep going on all day.

Let me know what you have experienced working in (or with) the IT industry but posting to the comments below. Or, if you have some more questions please post them too.

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.