The SeQueL


Time to talk geek! As I said before I will explain what SQL is and how you can write a few basic commands.

SQL is something that, in time, will become very handy for you to know. Specially when you are doing an office job, you’ll be confronted with SQL sooner or later. Trust me, it’s always a good thing if you can amaze people with the fact that you can at least read SQL.
In case you are a woman, men will fall in front of your feet begging you to marry them (more or less at least) if you know SQL.

SQL stands for Structured Query Language. This is a language which can be interpreted by databases. There are a few basic commands that every database understands.
But take note of this, there are a lot of different databases (Oracle Database, MySQL, Access, SQL Server, …) and most of them also have their own specific set of database commands. In other words, the things I will explain now will work for each and every database.
It may, however, very well be so that their are other ways to do this, perhaps in a more performant way, depending on the database.

If you hear people talk about a query, it means that they are talking about an SQL statement. A query is a fancy word for saying “a question to the database”. You will request data from a database from different tables, views, … .

Now there is something very imporant you should know about this language. The basic principle, compared to programming languages like java and c#, is different. If you write a program in, let’s say java, you will tell the compiler what it should do. You actually hand him the code and tell him how to treat it.

SQL doesn’t work this way. You “ask” a question to the database in a specific form but you allow the database to figure out by itself how he should handle your request.
In other words… Bad question.. Bad Reply :-).

How does SQL look like. This is actually a very simple, understandable language.
If you want to “pose a question” you should write a select statement.
Logically such a request always starts with the syntax SELECT. After you written what you want to select, you should tell the database where it should come from. This syntax is (dummy proof) FROM.

A very basic SQL statement looks like this:

SELECT *
FROM EMPLOYEES;

It says: Select everything (the *) from table EMPLOYEES. the ‘;’ just makes it clear to the database “this is the end of my request”.

That was easy now was it? Next step… You do not always want to select everything from every field in a table. It could be that you need a few fields from a table where the data is constaint by a certain filter.
You can define filters by using the WHERE command.
Example:

SELECT FIRST_NAME, LAST_NAME, DEPT
FROM EMPLOYEES
WHERE SALARY > 1200;

This statement says: Give me the data from column First_name, Last_name, Dept from the table EMPLOYEES of everyone who has a salary below 1200.

Just for the record, yes the syntax should always be in this order, first SELECT then FROM then WHERE.

A database can, ofcourse, also process calculations.

SELECT DEPT, SUM(SALARY)
FROM EMPLOYEES
WHERE RETIRED = ‘NO’
GROUP BY DEPT;

This one is a bit harder to understand in my opinion.
The query says following: Select the column DEPT, the sum of all salaries from table EMPLOYEES where the value in the RETIRED column equals NO (this has to be between quotes to make clear that it’s text) and then something new.
GROUP BY… If you are using aggregate functions (like SUM, AVG, COUNT, …) you should define the level where the data should be aggregated to. The example says GROUP BY DEPT means, show me the sum of the salaries per DEPT.

Still not very hard to understand, isn’t it? :-)

You can also ORDER data.

SELECT DEPT, LAST_NAME, SUM(SALARY)
FROM EMPLOYEES
WHERE RETIRED = ‘NO’
GROUP BY DEPT,LAST_NAME
ORDER BY LAST_NAME DESC;

The order by line just says, order descending by LAST_NAME . So last names with a Z will be first.

There are more things you can add but let’s just keep it at this. Since it’s the most commonly used syntax.

About joining tables. This is also something that needs to be done in the SQL syntax.
Let’s say we have 2 tables EMPLOYEES and CUSTOMERS.

Then we can join them as followed (I’ll only show you an inner join for now)

SELECT E.LAST_NAME, E.FIRST_NAME, C.LOCATION
FROM EMPLOYEES E, CUSTOMERS C
WHERE E.ID = C.EMP_ID;

Notice the FROM line first, I put an E and a C after the tables. These are aliasses. Since tables can have columns with the same name it needs to be possible to select each column seperatly. This can be done by defining an alias to the tables.
In this case EMPLOYEES has the alias E and CUSTOMERS has alias C. In the entire query we can now use the aliasses as a reference.
So the query says: Show me from table EMPLOYEES the last name, first name and from table CUSTOMER location but only where the ID from table EMPLOYEES matches the EMP_ID column from CUSTOMERS.

That was about the hardest part to read and also to explain this chapter :-). Not too bad right?

Let me know if you need more info!

Advertisements

2 thoughts on “The SeQueL

  1. Pingback: Advanced webcoding: PHP – Part 1 « Tech-o-rama

  2. Pingback: Beginners guide to advanced webcoding « Tech-o-rama

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: