Query

A query is a programming statement intended to retrieve data that meets our specifications. The query retrieves the matching records and places them in a result set. A single full instruction like this is called an SQL statement. Example:

Syntax Error

There are some structural rules to follow to while building an SQL statement. It's like grammatical rules in human language. We will encounter a syntax error if we do not follow that structural rules, and the statement cannot be executed. These rules may vary slightly depending on the implementation.

Logic Error

If the syntax is correct but the logic is not, then we will also get error. The statement can be executed, but we will get incorrect result. For instance, In the above query if we put <= instead of >= we will get result but not the one we were intended.

Debugging

Keep in mind that even though a statement may give the correct results with one set of data, it may fail if a different set of data is being used. Debugging is a way to deal with that by thorough and well-planned testing of the results to ensure that the statement will perform correctly under any circumstances.

Breaking down a complex statement piece-by-piece will give you good visual of the statement to work on it and find the error.

Clauses

An SQL statement consists of several clauses. The most common are::

SELECT: A list of column (fields) to retrieve.
FROM: The table(s) to access data from.
WHERE: The criteria indicating which rows (records) to retrieve, using Comparison Operators like "=", ">" etc.
ORDER BY: The order in which to sort the results.
We have some other clauses which we will leave for another article (UPDATE, INSERT INTO, DELETE, COUNT, LIKE, different JOINs, etc)

Individual ingredients within each clause

There are many ingredients a clause can have. I will focus on clause in another article. Let’s just know the basic here.

Keywords/Reserved words: Words that are part of SQL: SELECT, FROM, WHERE etc.
Identifiers: Names of tables and column (field) are know as identifier.
Literal: Value with which we compare with Such as 'SL' in the above example
Operators: Which calculate with and compare values ('=', 'AND', '<' etc)

Case-sensitivity

Keywords, Operators and functions are case-insensitive. So "WHERE" and "where" would be same. But it's always better to use WHERE to follow the convention.

In MySQL names of fields and tables are also case-insensitive. Therefore, "Salary", "salary" or "SALARY" would be same in above statement.

The literal values are also case-insensitive (For MySQL but not for all SQL): "SL" and "sl" will get you same result.

Building SQL expressions

An expression is a combination of field names, operators, functions and literal values that evaluates to a single value. For example:

Expressions that compare values result in a TRUE or FALSE or boolean value.

Salary>40000

Data types and literals

Data values can be divided into several distinct categories:

Numeric data includes numbers with which we can perform arithmetic.
Whole numbers are called integers (100, 99, -9, 0)
Numbers with decimal places are called floating point numbers (77.5, -77.59).

Text, or character data, is also known as alphanumeric data ('Jhon', '77' etc).
Groups of characters treated as a unit are known as character strings.
Character strings may contain numbers ('100'), but they cannot be used for arithmetic purposes. There are function available to CAST (convert) value from one data type to another.

SQL also treats dates and times as distinct category of data. Their treatment varies according to implementation.

Boolean (True/false) values are stored as integer values 1 and 0.

Operators

Operators are (usually) symbols that perform an operation on one or more values, such as "+" or "-". The main categories of operators are Arithmetic, Character, Relational and Logical.

An operator which performs on only one value (Such as negative sign) is known as a unary operator. An operator that performs on two values ("-" sign for subtraction) is known as binary operator. The values they work on are called Operands.

Each Operator is designed to work with one or more specific data type. If used with an inappropriate data type either an error will occur or (depending upon the implementation) the value(s) will be automatically converted to an appropriate data type.

The SQL arithmetic operators (by Precedence level) are:

Although the SELECT statement normally retrieves and displays data from tables, you can use it without the FROM clause to evaluate and display the result of a single expression. For example:

The arithmetic operators are not executed strictly in left-to-right order. Instead, they follow the rules of precedence which are common to mathematics and computer languages. The chart above shows the operators in order by precedence grouping (also see the Operator Precedence Chart handout). Therefore:

To alter the precedence, you must use parentheses:

When one set of parentheses is used inside another, they are said to be nested:

When two operators have the same precedence level, they are executed from left to right.

Arithmetic operators are most often used to create Calculated (or Virtual) fields. However, they are also often seen as part of the criteria in a WHERE clause. For instance, to find employees whose Bonus is more than one-tenth of their Salary:

Calculations (e.g., addition or multiplication) performed with a NULL value result in another NULL value, e.g.

MySQL is very flexible about mixing different data types in operations. However, you must be aware of its internal rules to divine the outcome. In the expression "747" + 5, MySQL sees the arithmetic addition (+) operator, and converts the string "747" to an integer, yielding a numeric result of 752 (which is probably the intention). Similarly, in the expression "2008-12-31" + 5, MySQL first attempts to convert the Date literal into a numeric value. Since a hyphen is not a numeric character, it stops at the first hyphen, yielding the integer 2008, and therefore the result 2013 (obviously not the intended result). Since rules for conversion vary according to implementation, it is best to always use conversion functions to ensure the correct result is obtained.

Relational operation

The SQL Relational (or Comparison) operators perform comparisons:

All the relational operators have the same precedence, so they are performed from left to right. They have lower precedence than the arithmetic operators, so those calculations are performed first. Most implementations allow both versions of the Not Equal To operator. Relational operations result in a TRUE or FALSE value, forming conditions:

Character strings are compared by their sorting order. The sorting order is based on the collating sequence used by the implementation (usually the ASCII chart), and whether or not comparisons are case-sensitive on the implementation.

In case-insensitive implementations like MySQL , character strings follow regular dictionary sort order (upper-case and lower-case are considered equivalent). You can use functions to overcome these inconsistencies. MySQL offers a BINARY keyword to effect case-sensitive comparisons

However, in case-sensitive implementations, all upper-case letters are considered "less than" all lower-case letters (due to their position on the ASCII chart):

When numbers are stored as character strings instead of numeric values, they may not sort as you would expect (since they are just a string of characters with no intrinsic numeric value):

Therefore, you can base filtering criteria on string comparisons as follows: (Find everyone whose first name precedes "Carl" in alphabetical order)

Most implementations, including MySQL, allow Date and Time values to be compared using the relational operators. They are compared by chronological order:

'2008-12-25' < '2009-01-01'

To find employees who joined the company before June 1, 2001:

Functions can also be used to compare and manipulate date/time values, and are more portable. Because the SQL standard specifies YYYY-MM-DD format, comparing date values has the same result as comparing them as if they were strings.

When comparing with a NULL value, SQL standards specify that the result should be "Unknown". Accordingly, MySQL gives NULL as the result (however some other implementations may give FALSE).