Tag Archives: sql tutorial

SELECT Statement with AND & OR Operators

Posted on by
4

Lets take a breather from JOIN statements we been looking at and lets look at AND and OR operators to be used in conjunction with with the SELECT statement to query information from the database.

Here is a sample SELECT statement using WHERE clause


select emp_name as 'Name', emp_sex as 'Sex', emp_salary as 'Salary' from employee where emp_sex = 'Male';

This will return you with 3 rows , excluding the lone female we have in the sample database.

Name Sex Salary
Michael Bay Male 90000.00
John Evan Male 5000.00
George Chan Male 50000.00

Its all fine but what if you want to select those who are BOTH Male AND has salary of more than 10000.00? Of course , this mean only two out of three will qualify. SO how do we do this in SQL?


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' and emp_salary > 5000;

Here we are looking for employees who are both Male and must have salary of more than 5000. The important thing to note is that using “AND’ means both the conditions must be satisfied. The query result for this statement is of just 2 rows.

Name Sex Salary
Michael Bay Male 90000.00
George Chan Male 50000.00

So what about OR? Try it. Replace ‘and’ with ‘or’ in the previous statement , like this ..


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' or emp_salary > 5000;

Guess what is the result from this query? If you say all three rows are returned as the first query then you are right. Because using ‘OR’ operator , only one of the conditions must be right. Either the employee is Male or has salary more than 5000. Since John Evan is Male , it qualifies him even though his salary is not more than 5000.

Hope this helps if you are wondering how is it possible to apply two conditions in SQL Select statement. See ya!

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!

Original SQL database example

Posted on by
1

*This sample would no longer be in used anymore. Pls reference to new database.* If you would like to delete a database – the correct term is “drop” a database – here is how


drop database anime;

Simple heh?

——————————————————————————————————————

We are going to build a sample database with a table and some data to be a reference for all the SQL codes we will be using on the site. Lets begin!

The following has been tested on MySQL server version 5.5 and assumed to be correct on all other SQL Databases. If you are using another database and having some error pls leave a comment so we could update the code.


 CREATE DATABASE anime;
 Query OK, 1 row affected (0.00 sec) 

 mysql> GRANT ALL PRIVILEGES ON anime.* TO 'slamdunk'@'localhost' IDENTIFIED BY 'slamdunk';
 Query OK, 0 rows affected (0.05 sec) 

 mysql> FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec) mysql>

Lets go through it line by line shall we? Lets start with the MySQL user password when you start the commandline. Out of the package , it is blank , so just enter. It will do well for home users and developers but definitely for for production servers or any server that is facing the internet.

Next line is about creating the database which follows the syntax of

CREATE DATABASE DATABASE_NAME

ending with “;”. It is to signify that the SQL command has ended. If you comes from C/C++/JAVA background , it should be familiar to you. Also pls take note that some words are capitalized and some are not. Those that are capitalized are reserved words in SQL , same as include , int , printf in other programming languages. You could , if you want , type them in lower-case too , MySQL doesn’t care. Other SQL Servers might so pls check your respective SQL documentation.

Moving on , this line about privileges or permissions , basically who can do what. This follows the syntax of ,

 GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO 'USER_NAME'@'HOST_NAME' IDENTIFIED BY 'USER_PASSWORD';

It is not so straight forward as the first line but simple put , I am giving all privileges , such as create,read,update,delete and so on , on this database and all its tables to this user from this particular host , you can leave this out , authenticated by that password. Phew! Clear now?

Lastly , it is about flushing privileges. Here the quote from the MySQL website itself about this

“Reloads the privileges from the grant tables in the mysql database. On Unix, this also occurs if the server receives a SIGHUP signal.

The server caches information in memory as a result of GRANT and CREATE USER statements. This memory is not released by the corresponding REVOKE and DROP USER statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES. ”
http://dev.mysql.com/doc/refman/5.0/en/flush.html

Here is a sample table.

Sample Database Table:

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

So how do we enter these into the MySQL Database? First , we need to tell the database what are the datatype of the each column.

  • id = integer
  • name = varchar(30)
  • address = varchar(60)
  • email = varchar(30)
  • sex = varchar(6)

Note that the difference between char and varchar is that when you define a type as char(6) , it is fixed to 6 characters.

Before creating our first table , be sure to select which database to create the table first! This can be done by ,

USE DATABASE_NAME;

or if you have been following along the example , 

USE anime;

Here is the SQL code to create the table.

 CREATE TABLE anime (id INTEGER PRIMARY KEY ,name VARCHAR(30) ,address VARCHAR(60) ,email VARCHAR(30) ,sex VARCHAR(6) );

To enter the information into the table ,

 INSERT INTO anime (id, name, address , email,sex) VALUES (1,'Monkey D. Luffy' ,'Amazon Lily','monkey@eastblue','Male');
 INSERT INTO anime (id, name, address , email,sex) VALUES (2,'Uzumaki Naruto' ,'Konohagakure',' naruto@Konohagakure','Male');
 INSERT INTO anime (id, name, address , email,sex) VALUES (3,'Rukia Kuchiki' ,'Soul Society','rukia@soul.society','Female');

This should have the trick. If you are unable to do for some reason or another , pls kindly leave a comment.

When you are done , try these two commands and see what you get.


DESC anime;
SELECT * FROM anime;

That should give you some ideas of querying. Thanks for following us all the way and hope you had fun!

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!