Tuesday, February 17, 2015

Learning SQL - Part 1 - Fetching Data

What is SQL? It is basically just a standard language for interacting with databases, and that is what we will be doing. SQL stands for Structured Query Language. Manipulating data through SQL is similar throughout all Database Management Systems (DBMS) so we should not worry much about differences in language syntax. Basic syntax and operations are exactly the same among these DBMS. We use SQL mainly for Relational Databases, but we will not discuss that here. For this article it is expected the reader knows what databases are including tables, columns, restrictions, and views.

SQL allows us to create, fetch, modify and delete all elements of a database including tables, records, stored procedures, and views. Tables, simply, represent an entities in databases, much like how classes are in a software program, while records stored in the table follow the rules and columns that are set for that table. A table consists of finite number of columns, exactly how a class in a program contains attributes, but that is not the focus of this article.

SELECT


Fetching data is the easiest, a simple SELECT query can get you the data from a whole table.

SELECT * FROM USERS;

The above query will fetch you all the data inside the table named U. See? Easiest thing ever. Let's move on to something less simpler. Let's say I require data from only two columns of the database. The following query demonstrates how the * can be replaced with specific and valid column names of the table.

SELECT NAME, AGE FROM USERS;

The above query accesses data from only two columns of the table, the NAME and AGE columns. It should be noted that queries are not case sensitive, but all the table and column names must be existent.

When fetching data from selected columns, there can sometimes be multiple rows with similar data in one column, for e.g. while fetching only ages of all employees, two employees could have same age. In that case the list of ages of employees has no need to one item mentioned twice, and for that we can use a DISTINCT keyword in the query.

SELECT DISTINCT AGE FROM USERS;

WHERE


Fetching data gets more interesting when data is required of a particular type or subset from the table. WHERE clause is used to get only what we need. Let's see how we can fetch names of users who are 20 years of age.

SELECT NAME FROM USERS WHERE AGE = 20;

Sometimes there are more than one criteria of fetching, or a range that is required. We use AND and OR keywords in such cases. AND keyword is used when data is needed following both the conditions, and OR keyword is used when data is needed that match either of the two conditions. The query below shows how user names of age 20 to 25 can be obtained from the table, and we need users that fit both conditions.

SELECT NAME FROM USERS WHERE AGE >= 20 AND AGE <= 25;

Since this condition was a numeric range, it can also be achieved with the BETWEEN keyword, which makes it simpler.

SELECT NAME FROM USERS WHERE AGE BETWEEN 20 AND 25;

If I were to only need users that either have age 20 or 25 then OR keyword would come into play like the following query.

SELECT NAME FROM USERS WHERE AGE = 20 OR AGE = 25;

Moving on.... What if it is not a range but finding the age of a user through his name? The following query let's us achieve that.

SELECT AGE FROM USERS WHERE NAME = 'DAVID';

This being simple sometimes is not the solution, specially if the text is long or only part of the text is known. In those cases we use the keyword LIKE so that SQL fetches data using the information we gave as a partially known factor.

SELECT AGE FROM USERS WHERE NAME LIKE 'DAVID%';

The LIKE keyword let's the query fetch any data that matches the criteria. The above query will fetch ages for all Davids in the table irrespective of the last name or if David is just half part to some other name like Davidson. The % sign in SQL tells the query that this is the end of the text we are not sure about. Write know we do not know the end the text so we placed it after the known part, but if begining is not known or both ends are not known then % sign is used as follows.

SELECT AGE FROM USERS WHERE NAME LIKE '%ADAMS';

SELECT AGE FROM USERS WHERE NAME LIKE '%VID%';

Next let's check out the IN keyword. This one is used when we have a collection of conditions against a column. For e.g. the following query returns us all the users who have either age 20, 22, or 24 and no one else.

SELECT NAME FROM USERS WHERE AGE IN (20, 22, 24);

Multiple columns and conditions can be mixed and matched to fetch data that is needed. For instance if we require all user names that have last names as Morris and are older than 30 years, we will use the following query.

SELECT NAME FROM USERS WHERE NAME LIKE '% MORRIS' AND AGE > 30;

The above query will fetch data based on our requirement. Note that Morris has a space before it. That is to ensure that Morris is preceded by a space otherwise our result set could contain names that have Morris as a substring of other last names as well.

ORDER BY


We almost every time require data to be sorted. This is achieved using the ORDER BY keyword followed by column that we wish to order against and either ASC for ascending or DESC for descending as the direction.

SELECT NAME, AGE FROM USERS ORDER BY AGE DESC;

The above query returns us data of all the users ordered such that oldest user is at the top of the result list. Multiple orders can also be set, such conditions come when data needs to be ordered against multiple columns. The following query demonstrates how that can be done.

SELECT NAME, AGE FROM USERS ORDER BY NAME ASC, AGE DESC;

The query above returns us all the users in alphabetical order with oldest of same names displayed before the younger ones.

We have completed all the basic fetching techniques through SQL, although joins remain, that will be handled later on. Stay connected for adding, modifying and removing data from tables.

No comments :

Post a Comment

enter comment