Introduction to SQL

The Structured Query Language

Introduction

In previous lessons, we used Microsoft Visual Basic as our primary language to perform the necessary operations using code. That language is mostly used for its ability to handle computer applications for the Microsoft Windows family of operating systems. To provide a common approach to database development, a universal language was developed to apply it to any programming environment or any operating system. The Structured Query Language, or SQL, is a language used to create and maintain databases.

SQL can be pronounced Sequel or S. Q. L. On this site, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, on this site, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

The SQL is used by various database environments such as Microsoft Access, MySQL, Microsoft SQL Server, Oracle, Paradox, etc. As it usually happens, each environment adds its flavor to the language to adapt it to whatever needs to be done. Based on this, the SQL we will learn on this site has some differences with the way it is interpreted in other programming environments.

As it is common with other computer languages, the SQL comes with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it. On this site, we will apply the SQL as it is implemented in Microsoft Access.

When using SQL, you write short to long sections of code and view its result. Code based on SQL is referred to as a SQL statement. In Microsoft Access, there are four main ways you can create or use a SQL statement:

• To write a SQL statement:
  • In the Database window, you can click the Queries button and double-click Create Query in Design View
  • On the main menu of Microsoft Access, you can click Insert -> Query and double-click Design View in the New Query dialog box
  • On the Database toolbar, you can also click the arrow of the New Object button, click Query, and double-click Design View in the New Query dialog box
  Any of these actions would display the Show Table dialog box where you can click Close. In future lessons, we will learn more about that Show Table dialog box.
  To open the window that allows you to write code, you can right-click the top section of the Select Query window and click SQL View. This would display a window with a default line of code. You can either edit it or delete the default and replace it with your own code.
  After writing your SQL expression, you can execute it to see the result. To do this, on the Query Design toolbar, you can click the Run button . After viewing the result, to return to the Select Query window, on the main menu, you can click View -> SQL Code. Alternatively, on the Query Design toolbar, you can click the arrow of the View button and select SQL Code.
• You can use a wizard to assist you with formulating a SQL statement without manually writing code. Still, you will be able to modify the code produced by the wizard.
• You can write a SQL statement as a string and, to apply it, you can submit it to the RunSQL() method of the DoCmd object.
• You can write a SQL statement and pass it to a method of one of the DAO, ADO, or ADOX objects we will review

When writing an expression, SQL is not case-sensitive. This means that Case, case, and CASE represent the same word. This applies to keywords of the SQL or words that you will add in your expressions.

The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to display a value to the user. In this simple case, it uses the following formula:

SELECT Value;

The value on the right side of SELECT must be appropriate and we will see examples in the next few sections.

When you execute a SQL statement in the Select Query window, its results are displayed in a spreadsheet. To be able to recognize a value, the top section of the a column of the spreadsheet displays a label, called a caption. If you create a simple SELECT Value; expression, Microsoft Access assigns a default caption to the value. In reality, the SQL allows you to specify a caption that would be used for the value. This is done using the following basic formula:

SELECT Value As Caption;

The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice.

The value to select can be a number. Here is an example:

SELECT 48;

The caption can be made of a word but the word cannot be one of the SQL's keywords. Here is an example:

SELECT 48 AS Age;

This would produce:

You can also use non-literal characters or digits in the caption. If the caption is made of a combination of words, you can concatenate them to create one word. Here is an example:

SELECT 24.85 AS HourlySalary;

If you want the caption to display different words, you can include them between an opening square bracket "[" and a closing bracket "]". Here is an example:

SELECT 25.05 AS [Hourly Salary];

This would produce:

Instead of displaying one column, you may want to display more than one. To do this, you can separate them with commas. Here is an example:

SELECT 42.50 AS [Weekly Hours], 25.05 AS [Hourly Salary];

This would produce:

Besides a number, the value of a SELECT expression can be a string. If it is, you can include it between single-quotes. Here is an example:

SELECT N'Martens, Laurent' AS [Employee Name];

In the same way, you can mix number-based and string-based columns.

Practical Learning: Introducing SELECT

Unary Operators

The Positive Operator +

Algebra uses a type of ruler to classify numbers. This ruler has a middle position of zero. The numbers on the left side of the 0 are referred to as negative while the numbers on the right side of the rulers are considered positive:

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand.

The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side.

As a mathematical convention, when a value is positive, you do not need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned as we learned in the previous lesson.

To express a variable as positive or unsigned, you can just type it. here is an example:

The Negative Operator -

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left.

Here is an example that uses two variables. One has a positive value while the other has a negative value:

SELECT -1250

Binary Operators

String Concatenation

We have seen that the SELECT keyword could be used to create a list of values. These values are separate of each other. You can also combine values to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name. An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name.

String concatenation consists of adding one string to another to get a new string. This is done using the & operator. The formula of the expression is:

String1 & String2

String1 and String2 must be recognizable strings. When this statement executes, String2 would added to the end of String1, resulting in String1String2. In the same way, you can add as many strings as you want by separating them with the & operator.

Practical Learning: Concatenating Strings

The Addition +

The addition can be used to add one value to another. This is done using the + operator. Here is an example:

SELECT 412.48 + 66.84 AS Total;

This would produce:

The order you use to add two or more values doesn't matter. This means Value1 + Value2 is the same as Value2 + Value1. In the same way a + b + c is the same as a + c + b the same as b + a + c and the same as c + b + a. This means that the addition is associative.

The Subtraction

The subtraction is used to take out or subtract one value from another value. It is essentially the opposite of the addition. The subtraction is performed with the - sign. Here is an example:

SELECT 1240 - 608

Unlike the addition, the subtraction operation is not associative. This means that a - b - c is not necessarily equal to c - b - a.

The Multiplication *

The multiplication can be used to multiply one value by another. This is done using the * operator. For example, to get the weekly salary of an employee, you can multiply the weekly hours by the hourly salary and get the result. As mentioned for the addition, the order of the operands is not important.

Practical Learning: Using the Multiplication

The Division 

The division is similar to cutting an item in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. Here is an example:

SELECT 128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0). Make sure that you know the relationship(s) between the numbers involved in the operation.

The Modulo

In the above division, 128/42, the result is 3. When you multiply 42 by 3, as in 42*3, you get 126. In some cases, you may be interested in knowing the amount that was left out after the operation. The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the MOD operator. Here is an example:

SELECT 128 Mod 42 AS [128 mod 42];

This would produce:

Parentheses

Like most computer languages, SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, parentheses allow a procedure to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction was not associative and could lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:

SELECT (154 - 12) + 8 AS First, 154 - (12 + 8) AS Second;

This would produce:

As you can see, using the parentheses controls how the whole operation would proceed. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands.