Details on Creating Queries
|
Details on Creating Queries |
|
Details on Column Selection |
|
Hiding a Column |
|
Consider the following table in a database:
|
|
Imagine that you want to create a list of employees using their names and you want to show their work department. You can create the following query:
This would produce:
Based on this list, imagine that you want the list to include only the employees who work at the corporate office, that is, employees whose Department value is Corporate. From what we learned in data filtering, in the Criteria corresponding to the Department column in the Select Query window, you can simply type "Corporate"
The corresponding SQL statement is: SELECT Employees.DateHired, Employees.FirstName,
Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.Department)="Corporate"));
Notice that the Department column is included as part of the SELECT statement. This would produce:
Notice that all filtered employees display Corporate. Since we are creating a list of employees who work at the corporate office and we know that this is what the query would produce, it becomes redundant, quite useless, to include the Department column in our list. Therefore, we can hide it. The problem is that we need it to pose the condition. To do this, we can add the column in the query to specify the condition but we would hide it from the result. The Select Query window provides a means of doing this visually. To do it, you can clear the check box of the Show row corresponding to the column. Here is an example:
The corresponding SQL statement is: SELECT Employees.DateHired, Employees.FirstName, Employees.LastName FROM Employees WHERE (((Employees.Department)="Corporate")); Notice that the Department column is not included as part of the SELECT statement. This would produce:
In your SELECT statement, after specifying the column(s) as we have done so far, when you execute the query, the name of each column would appear as the column header. When creating the table, if you had explicitly specified a caption for the column, the caption would display also when the query shows its result. On a query, instead of the default name used as the caption, if you want, you can display any string of your choice for a column header. To specify a column header other than the name of the column, if you are visually creating the SQL statement in the Select Query window, in the box that receives the name of the selected column, type the desired string, followed by a colon ":", followed by the actual name of the column from the table. Here are two examples:
This would produce:
If you are manually writing the SQL statement, type the actual name of the column, followed by the AS keyword, followed by the desired string. If the desired column header is in one word, you can simply type it. Here is an example: SELECT SocSecNbr AS EmployeeNumber, LastName, HourlySalary FROM Employees; If the string is in more than one word or contains a symbol that could be confused with an operator, you should include the whole string between an opening square bracket and a closing square bracket. In fact, you should always include the string between square brackets. Here are two examples: SELECT SocSecNbr AS [EmployeeNumber], LastName, HourlySalary AS [Pay Rate] FROM Employees; You can also include the string in single-quotes. Here are two examples: SELECT SocSecNbr AS [EmployeeNumber], LastName, HourlySalary AS 'Pay Rate' FROM Employees;
When creating a query, instead of having separate columns, you can combine two or more columns 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. To create this type of expression, you can use the + operator to concatenate the string as in FirstName + " " + LastName. After creating the expression, because the result is not part of the table (or the query) from which the query is based, you must give an alias name to the result. Here is an example:
Instead of the addition operator, you can use the ampersand & operator to perform the same operator. Instead of the single-quotes used to add a string in the expression, you can use double-quotes. Besides string, you can create a type of expression that uses a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.
Imagine that, instead of getting the list of employees who work at the corporate office, you are interested in the employees who don't work at the corporate office. To get this list, you can negate the "Corporate" condition that was stated earlier. This is done by preceding the condition with NOT. Here is an example:
This would produce:
The corresponding SQL statement is: SELECT Employees.DateHired, Employees.FirstName,
Employees.LastName, Employees.Department
FROM Employees
WHERE ((Not (Employees.Department)="Corporate"));
To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. To get a list of records where the student's email address is not specified, you can use a statement as follows: SELECT LastName, FirstName, Gender, EmailAddress FROM Students WHERE EmailAddress IS NULL In the same way, to validate that something is not null, you can use the expression IS NOT NULL. To see a list of only the students whose records have an email address, you can use a statement as follows: SELECT LastName, FirstName, State, EmrgName, EmrgPhone FROM Students WHERE EmrgPhone IS NOT NULL
Most or all of the criteria we have specified with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact desired value of records but you want to specify some type of approximation. To do this, you use the LIKE operator. If you are visually creating the statement, in the Select Query window, click the Criteria box corresponding to the column on which the condition would be applied and type. In a SQL statement, the LIKE operator is used in a formula as follows: Expression LIKE pattern The Expression factor is the expression that will be evaluated. This must be a clear and valid expression. The pattern factor can be a value to be found in Expression. For example, it can be the same type of value used in a WHERE statement. In this case, the equal operator would be the same as LIKE. For example SELECT Employees.DateHired, Employees.FirstName,
Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName)="Scott"));
is equivalent to SELECT Employees.DateHired, Employees.FirstName,
Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "Scott"));
This would produce:
The idea of using a LIKE operator is to give an approximation of the type of result you want. The available wildcards to se with the LIKE operator are:
If you want to match any character, in any combination, for any length, use the * wildcard. If you precede it with a letter, as in S*, the condition would consist of finding any string that starts with S. Imagine that you want to create a list of employees whose last names start with S. You would type the condition as LIKE "S*" (or LIKE 'S*'). To do this visually, in the Criteria field of the column, type the condition. Here is an example:
The SQL statement is this query is: SELECT Employees.DateHired, Employees.FirstName,
Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "S*"));
This would produce:
You can negate this condition by preceding it with NOT. Here is an example:
The SQL statement of this query is: SELECT Employees.DateHired, Employees.FirstName
Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Not Like "S*"));
This would produce:
As you can see, this results in the list of employees whose last names don't start with S. When you precede the * character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters that would precede the * symbol. For example, if you have some first names that start with Paul in a list but you don't remember the end of the name you are looking for, to create the list, you can specify that the first name would start with Paul and end with whatever. In this case, you would use Paul* as follows:
The corresponding SQL statement is: SELECT Employees.DateHired, Employees.FirstName, Employees.LastName, Employees.Department FROM Employees WHERE (((Employees.FirstName) Like "Paul*")); This would produce:
Instead of ending a letter or a group of letters with *, you can begin the LIKE statement with *. An example would be LIKE "*son". In this case, all strings that end with son, such as Johnson or Colson, would be considered. If you remember neither the beginning nor the end of a string you want to search for but you know a sub-string that is probably included in the type of string you are looking for, you can precede it with * and end it with *. An example would be LIKE "*er*". In this case, all strings that include er anywhere inside, such as Berg or Merck, would be considered. Like the other SQL statements, you can also negate this one.
The * wildcard is used to make it precede or succeed a specific character or a group of characters, that is, any character. If you want to consider only a range of characters from the alphabet, you can include the range in square brackets. To do this, type [, followed by the lowest character of the range, followed by -, followed by the highest character of the range, followed by ]. For example, to get a list of employees whose last names start with letters between D and M, you would specify the criterion as LIKE "[D-M]*". Here is an example:
The SQL statement of this query is: SELECT Employees.DateHired, Employees.FirstName, Employees.LastName, Employees.Department FROM Employees WHERE (((Employees.FirstName) Like "[D-M]*")); This would produce:
Once again, remember that you can negate this expression by preceding it with NOT.
Imagine that you know a certain pattern in the string but you know you are forgetting only one letter. For example, if you know that the employee's first name sounds like Saul, Haul, or Paul. In other words, you don't know for sure what the first letter is but you are sure about the other characters. To match a character in a string, you can use the ? symbol as the wild card. Here is an example:
The corresponding SQL statement is: SELECT Employees.DateHired, Employees.FirstName, Employees.LastName, Employees.Department FROM Employees WHERE (((Employees.FirstName) Like "?aul")); This would produce:
Notice that this produces the employees whose first name ends with aul regardless of the first character.
|
|
|
||
| Previous | Copyright © Yevol, 2007 | Next |
|
|
||
Practical Learning: Hiding a Column in a Query
