My name is Alan and I'm a third year student studying Computer Science at Boston College. My interests lie in databases and popular web languages such as PHP and JavaScript, and I plan on putting my skills to good use after graduation - joining a Startup, for instance. SQL Operator Precedence
Unlike normal code statements in a programming language, SQL code is not executed from left to right. SQL statements are a bit weird and are processed in a non-sequitur manner that may seem counter intuitive to beginners. SQL is read by the database based on the order of precedence- and the operator with the highest priority in any statement is the FROM clause, which shouldn’t be a surprise since you can’t manipulate data without having first specified where the data resides. But trying to figure out why GROUP BY occurs after the WHERE has been evaluated is less clear, so understanding operator precedence before reading or writing SQL queries is vital to writing cleaner queries , and can save hours of head-scratching.
The order of precedence from highest to lowest :
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Lets take a look at an sample problem and take a look at the right and wrong way of writing a query for it.
Lets say we want to know the classes where the sum of the students ages greater than 30 in a table that contains the student name, age, and the class he/she belongs to.
Beginners Fail Attempt:
SELECT class FROM table WHERE sum(age) > 30 GROUP BY class
Why this is incorrect: Which comes first, WHERE or GROUP BY? According to operator precedence, WHERE is evaluated first. So the database returns the sum of ALL the ages, which is not what we want. What we want is the sum of EACH class, and only the classes where the sum is greater than 30. It then goes on to GROUP BY the class- and instead of giving us the answer we want, it assigns the total sum to each class.
Correction
SELECT class FROM table GROUP BY class HAVING sum(age) > 30
Why this works: HAVING is the group equivalent of WHERE. It works with aggregate data. How? Lets walk through this query using us new understanding of operator precedence. First, the data is pulled using FROM. Now, which holds the higher priority, GROUP BY or HAVING? GROUP BY does, and so it groups the data based on our specified column. Finally, it evaluates the aggregated data with HAVING, and the SELECT returns the correct answer.
Closing
The general rule of thumb when writing SQL statements is to keep the order of execution in mind. What do I want? I want the total # of orders for each customer. But instead of jumping directly to the columns for the select, think top down in terms of operator precedence. What table? Should I set a predicate? Do I need to group? Now that they are grouped, what do I want? The problem then becomes dead simple.
Here’s a question for you:
Does the following statement comply with operator precedence? Why or why not?
SELECT a.name FROM table AS a
JavaScript: HTML DOM Flow
The view from the top
I had a lot of difficulty understanding how all the different methods and properties in the DOM API worked together when I first began learning JavaScript. For example, how are methods such as similar to methods such as setAttributes? And how are they related to property specifications like nodeType? It was all very confusing. The details are very important, and key to building a solid understanding of both JavaScript and DOM fundamentals, but I’ve found that it’s equally important to understand how each piece in the DOM work in concert to make DHTML possible; I put together a simple flow diagram using google docs that abstracts that process. If you want more information about the actual DOM API, check out Mozilla or W3C schools.
Traversing the DOM
Below is a script that I wrote in JavaScript to find all the tags used within a page recursively. This might come in handy when building a dynamically generated table of contents…
function get_tags(n){
var tag_string = "";
if(n.nodeType == 1){
tag_string += n.nodeName + "\n";
}
var children = n.childNodes;
var total_children = children.length;
for(var i = 0; i < total_children; i++){
tag_string += get_tags(children[i]);
}
return tag_string;
}
Learning JavaScript: Understand the DOM!
First, a quick review about the JavaScript Definitive Guide aka “Rhino Book”.
I purchased the definitive guide a year ago and just wiped the dust off the cover last week. It’s a great reference book for developers who already have a basic understanding of the DOM, but it doesn’t contain many exercises for a total novice to apply the lessons. So if you’re looking for a how-to book that not only provides the conceptual explanations of javascript, but also includes enough exercises for you to apply the in-book lessons, this is not it. Also, W3c schools is still the best resource for gaining a solid basic understanding of any language before trying to reap the benefits of an actual book.
I’m beginning to feel much more comfortable with element manipulation now, and there are several exercises that I think were key to helping me understand client side javascript. One of them-and I recommend this to anyone hoping to gaining a complete understanding about javascript- was researching the DOM thoroughly. Understanding the concept of the model is fairly straight forward, but manipulating it using DOM methods and properties is a different story- In the past I tried to do the latter before the former, only to be frustrated and confused before turning to the more intuitive structure of jQuery and the abundance of free copy-and-paste scripts available on the web to mask my ignorance. So now I’m back to step 1.
Trying to program with client side javascript without having an intimate understanding about the DOM API is worst than trying to build a house without a blueprint. It’s really like trying to build a house without a single clue about what construction tools and materials you actually need. Just like any other API, you need to read the documentation. And client side javascript is no different. Once you can explain the difference between properties like innerHTML and nodeValue as well as how they fit into the DOM tree, it’s just a matter of picking up the syntax and learning the quirks particular to the language. Oh, and practice.
Some sources on the DOM that I highly recommend!