mSQL Tutorial 5

The "Select" clause

The select clause allows you to retrieve data from the data base. What good would a database be if all you could do it was insert and delete data?

The syntax for the select clause is:

SELECT [table.]column [ , [table.]column ]**
FROM table [ , table]**
[ WHERE [table.] column OPERATOR VALUE
[ AND | OR [table.]column OPERATOR VALUE]** ]
[ ORDER BY [table.]column [DESC] [, [table.]column [DESC] ]

OPERATOR can be <,>, =, <=,>=, , or like
VALUE can be a literal value or a column name

A simple select might be:

SELECT first_name, last_name FROM emp_details
WHERE dept = 'finance'

In this example we are getting the first and last names of all the people in the finance department.

To sort the returned data in ascending order by last_name and descending order by first_name the query would look like this:

SELECT first_name, last_name FROM emp_details
WHERE dept = 'finance'
ORDER BY last_name, first_name DESC

In this example we are pulling all the employees in the finance department and listing them in ascending order by last name and then descending order by first name. A sample session would look something like:

Welcome to the mSQL monitor.  Type \h for help.

mSQL> SELECT first_name, last_name FROM emp_details
   -> WHERE dept = 'Tech'
   -> ORDER BY last_name, first_name DESC
   -> \g

Query OK.

1 rows matched.

 +-----------------+-----------------+
 | first_name      | last_name       |
 +-----------------+-----------------+
 | Darrell         | Brogdon         |
 +-----------------+-----------------+


mSQL>

And to remove and duplicate rows, the DISTINCT operator could be used:

SELECT DISTINCT first_name, last_name FROM emp_details
WHERE dept = 'finance'
ORDER BY last_name, first_name DESC

You can also use a LIKE clause. The syntax is as follows:

• '_' matches any single character
• '%' matches 0 or more characters of any value
• '\' escapes special characters (e.g. '\%' matches % and '\\' matches \)
• all other characters match themselves

So to search for anyone in finance who's last name consists of a letter followed by 'ughes', such as Hughes, the query would look like this:

Welcome to the mSQL Monitor.  Type \h for help.

mSQL> SELECT first_name, last_name FROM emp_details
   -> WHERE dept = 'I.T.S.' and last_name like '_ughes'
   -> \g

Query OK.

1 Rows matched.

 +-----------------+-----------------+
 | first_name      | last_name       |
 +-----------------+-----------------+
 | David           | Hughes          |
 +-----------------+-----------------+


mSQL>

The power of a relational query language starts to become apparent when you start joining tables together during a select. Lets say you had two tables defined, one containing staff details and another listing the projects being worked on by each staff member, and each staff member has been assigned an employee number that is unique to that person. You could generate a sorted list of who was working on what project with a query like:

SELECT emp_details.first_name, emp_details.last_name, project_details.project
FROM emp_details, project_details
WHERE emp_details.emp id = project_details.emp_id
ORDER BY emp_details.last_name, emp_details.first_name

mSQL places no restriction on the number of tables "joined" during a query so if there were 15 tables all containing information related to an employee ID in some manner, data from each of those tables could be extracted, albeit slowly, by a single query. One key point to note regarding joins is that you must qualify all column names with a table name. mSQL does not support the concept of uniquely named columns spanning multiple tables so you are forced to qualify every column name as soon as you access more than one table in a single select.

Last but not least, The "Update" clause.