Hierarchical SQL

This post reviews several ways to write Oracle SQL to reflect a hierarchical structure in an organization. The caveat here is that the table you are querying must be mapped with hierarchical data.

In the example below, the SQL statement is querying a table with Employee ID's and their respective Managers. The CONNECT BY clause denotes the parent rows and the child rows of the hierarchy.

SELECT emp_id, l_name, mgr_id
FROM employees
CONNECT BY PRIOR emp_id = mgr_id;

This query produces the result set that defines the relationship between employees and managers.

EMP_ID	L_NAME	MGR_ID
101	Brown	100
108	Greenberg	101
109	Jones	108

In this next example, the LEVEL psuedocolumn shows the parent and child rows.

SELECT emp_id, l_name, mgr_id, LEVEL
FROM employees
CONNECT BY PRIOR emp_id = mgr_id;

The result set looks like this:

EMP_ID	L_NAME	MGR_ID	LEVEL
101	Koch	100	1
108	Greenberg	101	2
109	Franks	108	3

In this last example, the query includes the START WITH clause to specify a root row to start the hierarchy. In addition, the Oracle keyword, SIBLINGS, used in the ORDER BY clause preserves the order within the hierarchy.

SELECT l_name, emp_id, mgr_id, LEVEL
FROM employees
START WITH emp_id = 100
CONNECT BY PRIOR emp_id = mgr_id
ORDER SIBLINGS BY l_name;

SQL Subqueries

There are many different ways to solve a SQL problem. As requests for database information become more complex, the need for added programming tools has become more important. One of the biggest enhancements to SQL has been the SQL subquery. As the name implies, this is a query inside a main query.

There are different names for a subquery: inner query, nested SELECT, sub-SELECT. The main thing to note is that a subquery can be embedded in different places inside the main SQL statement. For example, a subquery can be used as a:
--As a column expression
--As a filter inside a WHERE or HAVING clause
--As a datasource inside the FROM clause

The syntax is straightforward: enclose the subquery in parentheses and do not use a semicolon at the end of the subquery statement. The innermost query is processed first, then the result is used in the main query.

The SQL standard defines three types of subqueries:
Row subquery:
A subquery that returns more than one column and one row.
Table subquery:
A subquery that returns one or more columns and zero to one or more rows.
Scalar subquery:
A subquery that returns only one column and one row, or in other words a single distinct value.

Row subqueries are used in the WHERE clause of the main query. The SQL standard defines a Row Value Constructor for use in the WHERE, HAVING, or ON clause. However, not many vendors have implemented it.  An example of a Row Value Constructor used as a subquery would be something like this:

SELECT student_id, last_name
FROM students
WHERE (instructor_id, department_id) = (100, 150);

Here, the SELECT is on columns in students table where other columns have certain values.  Although the Row Value Constructor is not implemented, Oracle created a compound WHERE clause that works like this:

SELECT student_id, instructor_id, department_id
FROM students
WHERE (instructor_id, department_id) IN (SELECT instructor_id, department_id FROM students WHERE student_id  IN (20, 30));

Here, the inner subquery is structured with two clauses in the WHERE statement. 

A table subquery is used primarily in the FROM clause of the main query, or as an insert into an existing table. As this subquery returns many columns and rows, it is used in the FROM clause of an existing query like so:

SELECT S.student_id, S.last_name, S.score, D.score_avg
FROM students,
(SELECT instructor_id, avg(score) as "score_avg" FROM students GROUP BY department_id) D
WHERE S.department_id=D.department_id AND S.score >  D.score_avg;

A scalar subquery returns only one value, and it is used widely. You can use a scalar subquery as a substitute for a value expression. In addition, it can be used as a column subquery or in the WHERE clause of the main query. Here is an example:

SELECT student_id, last_name, score
FROM students
WHERE score > (SELECT avg(score) FROM students);

Here, the subquery in the WHERE clause selects the average score for all students. The result is the WHERE clause sets up a dynamic query as the average score for all students changes as additional tests are given.

The SQL Union Join

The Union Join statement is a powerful join in SQL. The Union Join is not to be confused with the Union set operator. The Union Join enables you to write a SQL statement to retrieve records that do not match on both sides of the join. This is the exact opposite of a left and right outer join, or for that matter, a full outer join. This is part of the SQL standard. However, not many database vendors have implemented it.

To run a Union Join, you can use the Full Outer join syntax and construct a condition that is always false. For example table 1 Full Outer Join table 2 on 3=6.  Here is an example using a department table(department_id PK) and employee table(department_id FK), with the cardinality of one department may contain many employees

SELECT E.last_name,E.first_name,E.department_id,D.department_id
FROM employees E FULL OUTER JOIN departments D on 3=6;

With this SQL statement, you will see all the records from departments with no matching employees and all employees with no matching department.

The SQL ROLLUP Function

As a SQL programmer, if you need to calculate subtotals and a grand total in a query, the SLQ ROLLUP function fits the bill. The ROLLUP function is OLAP(Online Analytical Processing) SQL often used in data warehousing transactions.

Lets say you have an employee table with dept_id and job_id foreign keys to department and job tables. To determine the number of employees in each department and in each job position with subtotals and a grand total, you could write the following query:

SELECT department_id,job_id,Count(*) FROM employees
GROUP BY ROLLUP(department_id,job_id);

This query will produce the following outcome:

DEPT_ID    JOB_ID               COUNT(*)
---------------------- ---------- ---------

50	ST_MAN	5
50	SH_CLERK	20
50	ST_CLERK	20
50		45	Note: department subtotal
110	AC_MGR	1
110	AC_ACCOUNT	1
110		2
		107	Note: grand total

As you can see, the ROLLUP function calculates subtotals by job and department and a grand total all in one statement.

The INTERSECT And MINUS Operators In SQL

This post shows INTERSECT and MINUS operators that make nice additions to your SQL toolkit. First let's establish the tables we'll be querying. Say we have 2 tables: Employee and Job History defined as such:

Employees: employee_id,first_name,last_name,email,phone_number,hire_date,job_id,department_id
employee_id is primary key(PK).
And
Job_History:
employee_id,start_date,end_date,job_id,department_id
The combination of employee_id and start_date is the primary key and employee_id, alone, is the foreign key(FK) back to the Employees table.

Now, the INTERSECT operator returns all rows that are common to multiple queries as shown in the example below:

SELECT employee_id,job_id FROM employees
INTERSECT
SELECT employee_id,job_id FROM job_history;

The rules for using INTERSECT with multiple table queries are:
--The number of columns and datatypes being used in the SELECT statement must be the same. The names of the columns do not have to be identical.
--Reversing the order of the queries does not change the outcome.
--The intersect operation does not ignore NULL values.

On a single table you can achieve intersection results by putting the AND operator in the WHERE clause as follows:

SELECT last_name, first_name
FROM employees
WHERE job_id ='CLERK' AND Salary = 25000;

Here, the intersection is obtained by using specific column values.

The MINUS operator looks at the difference between multiple queries. With MINUS, the order of the two sets is important as it will yield different results when the order of the two query sets is reversed. Like the INTERSECT operator, the number of columns and their respective data types being used in the SELECT must be the same. And the names of the columns do not have to be identical. Here is an example of how the MINUS operator works:

SET 1: 3,5,7,9,12      SET 2: 2,3,5,7,9,11
The difference of set 1 and set 2: (3,5,7,9,12) - (2,3,5,6,9,11) = 12
The difference of set 2 and set 1: (2,3,5,7,9,11) - (3,5,7,9,12) = 2,11

Here the order of the sets was reversed and yielded different results. Now look at this query using the MINUS operator:

SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history;

Now based on the tables as previously defined, this query will yield employees who have never changed jobs. That's pretty handy....

Connect to MySQL using PHP PDO

The PHP Data Objects(PDO) extension provides a consistent object-oriented interface for using PHP to access databases and execute SQL queries. PDO provides a data access layer abstraction layer, which means that, regardless of what database you are using, you can use the same functions to issue queries and fetch data. You cannot use the PDO extension to perform any database operations, but use the database specific PDO driver to access the database server.

The PDO class constructor takes the following parameters of the data source name(DSN), database name, username, and password. In the example below, a MySQL database connection object($dbc) is instantiated using a localhost server. The $dbc object establishes a connection to the test database using the applicable logon credentials of $dbuser and $pwd. Here is a simple example.

<?php
$dbc = new PDO(' mysl: = localhost; dbname= test ', $dbuser, $pwd');
?>

If there are any connection problems, a PDO exception is thrown. If the exception is not caught and handled, the default action is to terminate the script and a back trace is displayed showing the database connection details including the username and password. To mitigate this security risk, it is best to handle the exception as it occurs. Depicted below is a script to handle connection errors.

<?php
query('SELECT * FROM testCase') as $row )
{
print_r($row);
}
$dbc = 'null ';
} catch (PDOException $e)
{
echo 'Error!:' . $e->getMessage() . ;
die();
}
?>

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of the PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

<?php
$dbc = new PDO(' mysql:=localhost; dbname = test '; $dbuser, $dbpwd);
//use the connection here

//and now close the connection
$dbc = 'null ';
?>

Restarting MySQL from XAMPP

The Apache Foundation provides an open source tool called XAMPP with modules that include the Apache webserver,PHP, MySQL, a file server called FileZilla and an email client named Mercury Mail. XAMPP comes with an all-in-one installer which can be downloaded either as an executible or zip file. Upon download and installation, all of these components can be started as services. For web and database development purposes, it is really nice to have a local webserver as opposed to numerous ftp's to a web host. It save alot of time.

However, I sometimes find that MySQL does not start through the XAMPP control panel GUI. One solution is to find the Windows batch files for uninstalling and installing MySQL as a service. These batch files are found in the directory C:\xampp\mysq. First right click on the uninstall batch file. A shell window appears and follow the prompts to uninstall MySQL as a service. Then right click on the install batch file and click through the prompts.

Go back to the XAMPP control service, click to start MySQL as a service and then click the Start button. That's all there is to it.

SQL Rand Function

The RAND() function is used for returning random numbers. For example, the statement SELECT rand() will return a random number between 0 and 1 as such:

mysql> SELECT rand();
+-------------------+
| rand() |
+-------------------+
| 0.138089752884665 |
+-------------------+
1 row in set (0.00 sec)

This function comes in handy when you are selecting customers to receive a special offer or perhaps raffling off a prize in a sweepskate contest. To show how this function would work, I have designed a sample table called Pie from a MySQL database called Dessert. The rand function will be used to generate a random someValue integer.

SELECT * FROM pie;

pie_id	name	someValue
1	apple	1
2	cherry	4
3	peach	2
4	berry	3
5	cream	5
6	pumpkin	6
7	mince	7
8	rubarb	10
9	pecan	8
10	mud	9

If you write the SQL query: SELECT someValue FROM pie ORDER BY rand() limit 1; you will generate a random someValue integer. When I ran this query 5 times, I got the following values: 2,4,9,4,8.

The following works for MS SQL and Oracle:
MS SQL:
SELECT TOP someValue FROM pie
ORDER BY someValue;

Oracle:
SELECT someValue FROM (
SELECT someValue from pie ORDER BY dbms_random.someValue) WHERE rownum=1;