Beginning SQL the SEQUEL: Working with Advanced SQL Statements

So you made it through the first tutorial and now you are back for more. Well buckle up for the ride, because this episode teaches you the glories of advanced SQL statements. By the end of this article you’ll be able to sort data, join data, you name it.

That’s right, hordes of rows and columns will be at your mercy. You won’t only know SQL — you’ll be SQL. So slap on your cool shades and trench coat and get ready to dodge bullets in slow motion. And prepare to become the one.

Disclaimer: You won’t actually be able to dodge bullets after reading this article. You will however be able to avoid being shot at by your boss when you show him your l33t programming skillz.

Sorting Things Out

Before I became a big, illustrious writer with oodles of fans, I worked for a boss who liked to sort everything. Even if it couldn’t be sorted, he wanted me to sort it. Hours on end he would call me into his office imploring me to sort by name, date, value. And off I would scurry to do his bidding. Then one day he began to ask me to sort things around his office. Like his garbage can and the stapler. Last I heard he was in a small room with a white jacket. They say when the nurses come to bring him his pills, he whispers softly, “Can you sort those for me?”

To sort in SQL, you will need a handy little phrase called the ORDER BY statement.

SELECT FirstName, LastName from EMPLOYEES

ORDER BY LastName;

In the above code, we are telling the database to choose and display the columns FirstName and LastName from our table, Employees, and to then sort them alphabetically by the last name.

If you wanted to sort the same two columns in reverse alphabetical order, then you would do so by adding DESC (descending) to the code.

SELECT FirstName, LastName from EMPLOYEES

ORDER BY LastName DESC;

You can, of course, also sort by more than one column. Say you wanted the first name, last name, and salary of every employee, sorted first by last name, then by salary.

SELECT FirstName, LastName, SALARY from EMPLOYEES

ORDER BY LastName, SALARY;

And finally, for a last bit of sorting madness, if you wanted to sort by LastName in Ascending order and SALARY in Descending order, you would grab a safety helmet and type in the following code:

SELECT FirstName, LastName, SALARY from EMPLOYEES

ORDER BY LastName ASC, SALARY DESC;

{mospagebreak title=AND and OR…Or OR and And}

If it’s any comfort, that title hurt my head typing it as much as it did yours reading it. In the previous tutorial we spoke about the WHERE CLAUSE and its uses. Here, we will go a little more in-depth and add some power to it using the AND and OR operators.

AND

Say you wanted to list all the people in our employee database that had the first name Homer and the last name Sampson (if you remember in the first tutorial I had you add Homer’s twin brother, Homer, to the table). One way we could do this is by joining the WHERE clause with the AND operator (think Voltron with words).

SELECT * from EMPLOYEES

WHERE FirstName=’Homer’

AND LastName=’Sampson';

The above sample would return the following dataset:

First Name

Last Name

Position

Salary

Homer

Sampson

CEO

$75,000

Homer

Sampson

CFO

$75,000

So let’s say that Homer Sampson has a daughter, we’ll call her, I don’t know…Liza (to avoid a lawsuit from Matt Groening and Sam Simon). She isn’t married yet because she is a high-powered business woman, devoted to her job, so her last name is still Sampson. Her salary is a little more than her father’s, because she is the CTO and daddy’s little baby. So let’s give her $85,000 a year. Go ahead and add her to the employee table.

OR

Now that we have added Liza Sampson to our employee table, let’s do some more coding. In this example, we want to list all people with either the first name Liza, or the last name of Sampson.

SELECT * from EMPLOYEES

WHERE FirstName=”Liza’

OR LastName=’Sampson”;

Here’s how that should look in your table:

First Name

Last Name

Position

Salary

Homer

Sampson

CEO

$75,000

Homer

Sampson

CFO

$75,000

Liza

Sampson

CTO

$85,000

Lastly, you can combine the might of the AND and OR statements. I know — how can one man wield that much power? Well you can — and will — with the following code.

SELECT * from EMPLOYEES WHERE

(FirstName=’Liza’ OR FirstName=’Homer’)

AND LastName=’Sampson';

This code will return the same results as above.

First Name

Last Name

Position

Salary

Homer

Sampson

CEO

$75,000

Homer

Sampson

CFO

$75,000

Liza

Sampson

CTO

$85,000

{mospagebreak title=The IN-credible Statement}

By now you may have noticed that there are multiple ways to return the same results in SQL. This is true for all languages really. With the IN statement, you can retrieve a specific set of data you are seeking.

SELECT * from EMPLOYEES

WHERE LastName IN (‘Sampson’,’Lee’);

With this line of coding, we are asking the database to return all rows that contain the last names of Sampson and Lee. I know what you are thinking; we could have done that with the OR operator or even the AND operator. Right you are. So why use IN? It’s just a matter of preference.

BETWEEN AND

You have probably been between a rock and a hard place before, but that’s not really what the BETWEEN AND statement is about. If you think about being asked to choose a number between 1 and 100, you’re much closer to understanding this bit of SQL. The basic function of the BETWEEN AND statement is to allow you to choose a range of values.

Let’s say you wanted to list all of the employees who made between $75,000 and $100,000. To do so, you would use the following code:

SELECT * from EMPLOYEES WHERE SALARY

BETWEEN $75,000 AND $100,000;

That should bring up the following table:

First Name

Last Name

Position

Salary

Homer

Sampson

CEO

$75,000

Homer

Sampson

CFO

$75,000

Liza

Sampson

CTO

$85,000

Larry

Smith

President

$100,000

Note that in some databases, the BETWEEN AND Statement is taken more literally and would not find $75,000 or $100,000, but instead any number between them. In that event you would change the parameter to between $74,999 and $100,001.

{mospagebreak title=NOT BETWEEN}

Likewise, if you wanted to see a list of employees whose salary was NOT between $75,000 and $100,000, you would use the NOT BETWEEN statement. This would show you any value lower than $75,000 and higher than $100,000.

SELECT * FROM EMPLOYEES WHERE SALARY

NOT BETWEEN $75,000 AND $100,000;

This would show us the following table:

 

First Name

Last Name

Position

Salary

Bruce

Lee

Security

$50,000

Your

Name

Whipping Boy

$15,000

ALIAS

I know what you are thinking. Hot girls in tight leather pants using high-tech weaponry and kicks to the face to spoil the plots of evil-doers worldwide, and still somehow managing to form meaningful relationships. And all within one hour no less.

Unfortunately, SQL doesn’t have any of those. If it did, then every program would be written in SQL. Imagine the database version of World of Warcraft. (Hmmm, now where did I put my medication?)

The ALIAS statement in SQL comes in handy if you want your column names or even your table itself to show a different name than you originally gave it, without having to change the name of anything. A lot of times you will have other programs that refer to your tables, and if you change the table name or the columns, you have to change all that code. Not good. This is where ALIAS comes in handy.

SELECT FirstName AS Ninja_Warrior, SALARY AS Meager_Earnings

FROM EMPLOYEES;

This would take the first name and salary columns from your employees table and display them with the names Ninja_Warrior and Meager_Earnings, respectively (without actually changing them and causing you many hours of headaches).

Ninja_Warrior

Meager_Earnings

Larry

$100,000

Homer

$75,000

Homer

$75,000

Liza

$85,000

Bruce

$50,000

You

$15,000

Again, you can do the same with your table.

SELECT FirstName, SALARY

FROM EMPLOYEES AS EX_EMPLOYEES

This would return the first name and salary of your employees and show them in a table called EX_EMPLOYEES.

That covers the first part of the more advanced commands contained within SQL. In the next and final tutorial we will delve into the rest of the advanced statements and transform you into a true SQL Master.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan