Splat | WiseTome.com

This is the second part of the series of SQL Basics Primer. As mentioned in the earlier post, we would now examine the data retrieval SQL requests from a table.

For sake of illustrations I would be using the following imaginary EMP_DATA table:

Now, as you can see, the data present in the table is of no particular order, and that is not of relevance to the requester. It could be stored anyway, but when a SQL command is given, it can be made to be presented in a certain way. (We will look into that at the end of this part).

To retrive data from the table, we use the SQL command SELECT. A SELECT command has several clauses that determine which data is fetched from the database, and how it is fetched. For example, let us consider the case where we require getting the names and employee ids of all the employees.

We would give a SQL command as follows:

SELECT empname, empid FROM emp_data;

Then it would give a result as follows:

The basic SQL statement can sytactically be written as follow:

SELECT * | (column list)| (expression list) FROM (table list);

That means that the first clause should contain ‘*’ or the column list or some expression list or a combination of them. We will not dwell into the importance of the (expression list) now. The second clause is the FROM clause where we tell the DBMS to get the data from a particular table. Again, we will not look into the importance of having more than one table there.

What will happen when we give the following command?

SELECT * FROM emp_data;

All the data from that table would be displayed.

The DBMS can also fetch data from a table selectively. This filter is specified in an additional WHERE clause. Say, we need to get the details of all the employees in the Programming department. The following SQL command would do the task:

SELECT * FROM emp_data WHERE dept = ‘Programming’;

It would give the following result:

One could add more than one set of filter logically in the WHERE clause.

SELECT * FROM emp_data WHERE dept = ‘Programming’ AND empid > ‘6002′;

There are more clauses that can be included in the SQL SELECT statement. They are the grouping clauses ‘GROUP BY’ and ‘HAVING’ clauses - again, I think this would be out of the purview of a ‘basic primer’. Another clause that is worth mentioning is the ORDER BY clause.

Again, let us now try to get all the programmers but sorted according to their employee numbers:

SELECT * FROM emp_data WHERE dept = ‘Programming’ ORDER BY empid;

A lot can be done using the SELECT statement, as I have mentioned in this post. The ones that I mentioned above are the GROUP BY and HAVING clauses, and also use of expressions within the clauses. Also, there are concepts of JOINS where one would retrieve data from more than one tables. That, I believe should stay out of this SQL Basics Primer, as our aim is to get a rough idea of what it is all about.

In the next part we would be looking into data modification tasks.

To be continued…