Tag Archives: sql example

More SQL JOIN Statements

Posted on by
Reply

Lets look at another way of querying with JOIN statement. Just to recap from previous post that JOIN statement aka INNER JOIN return rows when there is at least one match in BOTH tables

select e.emp_name as "Employee Name" , d.dept_name "Department Name"
from employee e , department d
where e.emp_dept_id = d.dept_id and d.dept_budget > 500000;

So what is the difference compared to what we have looked at last time? The difference is slight , the main logic stays the same but how we name the names have changed. take a look at the second line

from employee e , department d

Here we are saying “employee” table from now on will be referenced by “e” for the lifetime of the SQL statement. If you come from traditional programming background , like me , you might find it odd that “e” can be used on the first line yet it is only defined in the second line. You definitely cannot use a variable that has not been defined!

Anyway , besides that weirdness , every other part remains the same. Of course they are now “e.emp_name” instead of “employee.emp_name”. This query will return ,

Employee Name Department Name
Michael Bay Asia
John Evan Finance
Christine May Sales
George Chan Asia

SQL JOIN statement tutorial with examples

Posted on by
Reply

Today , lets go into the exciting world of JOIN statements. Before we talk about complex topics such as INNER and OUTER JOINS , default JOIN is an INNER JOIN statement if you are interested , a few words on the concept of JOIN statements. We have , up to this stage , done some basic SQL operations , such as setting up the database and the tables and inserting information into them. We have also looked at basic queries using SELECT statement with WHERE clause. Till now , we are learning what can be done even easier in excel , not much more reason to learn SQL at all. What , then , is the big deal with the databases?

Short and sweet answer to that would be that a database , is a relational database. What this means is that by using common information , we can gather information from two tables at same time. Take a look at the table created , each table is specific to itself. There is no department budget data in employee table and there is no reason why it should be there. If we want to know what is our own department budget , we ask our manager , who has access to it. Same for the SQL database. Instead of adding every information to one table only , we split the tables according to the function or type , pretty much like OOP if you ask me.

Here is a sample JOIN statement , 

select employee.emp_name , department.dept_name , department.dept_budget
  from employee , department
  where department.dept_budget >500000;

Here , instead of querying from one table , we query from 2 tables , employee and department tables. If you read it carefully 2 or 3 times , you will find it is pretty simple and straightforward. In plain English , it is asking for

“Employee names , department names and department budget for those departments with budget more than 500000.”

There are a few ways of using JOIN statements but for our purpose , this will do. Till next time , have a nice day!

SQL SELECT statement (Order By keyword)

Posted on by
Reply

Lets continue with our discussion into the SQL query statement , SELECT , by adding one more option to it. It is called “Order By” and is used to sort the result-set by a specified column. By default , it will order in ascending order but it could be changed.

Lets see some examples of its use. The database we will be using is the sample database. Lets take a look at it again.



mysql> select * from anime;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

Now lets see it again with ORDER BY keyword. I do not generally use capital letters when I type SQL statements but its a personal preference.



mysql> select * from anime order by name;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

mysql> select * from anime order by sex;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

As you can see in both cases , the order of the rows have been rearranged by the specific column in ascending order. What if you want to see in opposite order?



mysql> select * from anime order by name desc
;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
+----+-----------------+--------------+----------------------+--------+
3 rows in set
mysql> select * from anime order by sex
desc;
+----+-----------------+--------------+----------------------+--------+
| id | name            | address      | email                | sex    |
+----+-----------------+--------------+----------------------+--------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male   |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male   |
|  3 | Rukia Kuchiki   | Soul Society | rukia@soul.society   | Female |
+----+-----------------+--------------+----------------------+--------+
3 rows in set

As you can see from above , the only change needed is the addition of DESC keyword at the end of the statement. It will then reverse the order in which the table appears. As simple as it sounds , this is one of the most useful KEYWORD as it allows the user to view the data in a orderly manner.

Hope that helps!

 

More of SELECT statement using sample database

Posted on by
Reply

For this tutorial , pls refer to the SQL Database Example Page for the sample database. We will be using it for our examples below. Lets recap what we had done so far.

In brief , we were introduced to the SELECT statement , which is used for querying data from the database tables. It can be used to return all the rows and columns , such as


SELECT * FROM TABLE_NAME;

or could be refined to return only the rows meeting specific criteria.


SELECT * FROM TABLE_NAME WHERE COLUMN_HEADER = 'SOME_STRING';

In our sample database , lets say we want to return only those male characters , meaning sex = ‘Male’ ( Note that for MySQL , a string is enclosed within either single quote or double quote. http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html )


mysql> select * from anime where sex = 'Male';
+----+-----------------+--------------+----------------------+------+
| id | name            | address      | email                | sex  |
+----+-----------------+--------------+----------------------+------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male |
+----+-----------------+--------------+----------------------+------+
2 rows in set (0.00 sec)

mysql> select * from anime where sex = "Male";
+----+-----------------+--------------+----------------------+------+
| id | name            | address      | email                | sex  |
+----+-----------------+--------------+----------------------+------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male |
+----+-----------------+--------------+----------------------+------+
2 rows in set (0.00 sec)

Both will return the same result. Now , suppose you don’t want the whole set of information , just the name of the male anime characters. Then instead of * , enter ‘name’.


mysql> select name from anime where sex = 'Male';
+-----------------+
| name            |
+-----------------+
| Monkey D. Luffy |
| Uzumaki Naruto  |
+-----------------+
2 rows in set (0.00 sec)

Ok but you want to return the name as “Anime Heroes” instead of just “name” as in the column header. In that case using “name as ‘something’”. 


mysql> select name as "Anime Heroes" from anime where sex = 'Male';
+-----------------+
| Anime Heroes    |
+-----------------+
| Monkey D. Luffy |
| Uzumaki Naruto  |
+-----------------+
2 rows in set (0.00 sec)

So we just seen a few ways to manipulate the queries. Those are simple yet effective. Pls know them and know them well. Till next time , see ya!