18 Mod 4: Basic SQL
The SQL data manipulation language (DML) is used to query and modify database data. In this chapter, we will describe how to use the SELECT.
- SELECT – to query data in the database
In the SQL DML statement:
- Each clause in a statement should begin on a new line.
- The beginning of each clause should line up with the beginning of other clauses.
- If a clause has several parts, they should appear on separate lines and be indented under the start of the clause to show the relationship.
- Upper case letters are used to represent reserved words.
- Lower case letters are used to represent user-defined words.
SELECT Statement
The SELECT statement, or command, allows the user to extract data from tables, based on specific criteria. It is processed according to the following sequence:
SELECT DISTINCT item(s)
FROM table(s)
WHERE predicate
ORDER BY fields
We can use the SELECT statement to generate an employee phone list from the Employees table as follows:
FROM Employees
ORDER BY LastName
This action will display employee’s last name, first name, and phone number from the Employees table, seen in Table 16.1.
| Last Name | First Name | Phone Number |
| Hagans | Jim | 604-232-3232 |
| Wong | Bruce | 604-244-2322 |
Table 16.1. Employees table.
In this next example, we will use a Publishers table (Table 16.2). (You will notice that Canada is mispelled in the Publisher Country field for Example Publishing and ABC Publishing. To correct mispelling, use the UPDATE statement to standardize the country field to Canada – see UPDATE statement later in this chapter.)
| Publisher Name | Publisher City | Publisher Province | Publisher Country |
| Acme Publishing | Vancouver | BC | Canada |
| Example Publishing | Edmonton | AB | Cnada |
| ABC Publishing | Toronto | ON | Canda |
Table 16.2. Publishers table.
If you add the publisher’s name and city, you would use the SELECT statement followed by the fields name separated by a comma:
FROM Publishers
This action will display the publisher’s name and city from the Publishers table.
If you just want the publisher’s name under the display name city, you would use the SELECT statement with no comma separating pub_name and city:
FROM Publishers
Performing this action will display only the pub_name from the Publishers table with a “city” heading. If you do not include the comma, SQL Server assumes you want a new column name for pub_name.
SELECT statement with WHERE criteria
Sometimes you might want to focus on a portion of the Publishers table, such as only publishers that are in Vancouver. In this situation, you would use the SELECT statement with the WHERE criterion, i.e., WHERE city = ‘Vancouver’.
These first two examples illustrate how to limit record selection with the WHERE criterion using BETWEEN. Each of these examples give the same results for store items with between 20 and 50 items in stock.
Example #1 uses the quantity, qty BETWEEN 20 and 50.
FROM Sales
WHERE qty BETWEEN 20 and 50 (includes the 20 and 50)
Example #2, on the other hand, uses qty >=20 and qty <=50 .
FROM Sales
WHERE qty >= 20 and qty <= 50
Example #3 illustrates how to limit record selection with the WHERE criterion using NOT BETWEEN.
FROM Sales
WHERE qty NOT BETWEEN 20 and 50
The next two examples show two different ways to limit record selection with the WHERE criterion using IN, with each yielding the same results.
Example #4 shows how to select records using province= as part of the WHERE statement.
FROM Publishers
WHERE province = ‘BC’ OR province = ‘AB’ OR province = ‘ON’
Example #5 select records using province IN as part of the WHERE statement.
FROM Publishers
WHERE province IN (‘BC’, ‘AB’, ‘ON’)
The final two examples illustrate how NULL and NOT NULL can be used to select records. For these examples, a Books table (not shown) would be used that contains fields called Title, Quantity, and Price (of book). Each publisher has a Books table that lists all of its books.
Example #6 uses NULL.
FROM Books
WHERE price IS NULL
Example #7 uses NOT NULL.
FROM Books
WHERE price IS NOT NULL
Using wildcards in the LIKE clause
The LIKE keyword selects rows containing fields that match specified portions of character strings. LIKE is used with char, varchar, text, datetime and smalldatetime data. A wildcard allows the user to match fields that contain certain letters. For example, the wildcard province = ‘N%’ would give all provinces that start with the letter ‘N’. Table 16.3 shows four ways to specify wildcards in the SELECT statement in regular express format.
|
% |
Any string of zero or more characters |
|
_ |
Any single character |
|
[ ] |
Any single character within the specified range (e.g., [a-f]) or set (e.g., [abcdef]) |
|
[^] |
Any single character not within the specified range (e.g., [^a – f]) or set (e.g., [^abcdef]) |
Table 16.3. How to specify wildcards in the SELECT statement.
In example #1, LIKE ‘Mc%’ searches for all last names that begin with the letters “Mc” (e.g., McBadden).
FROM Employees
WHERE LastName LIKE ‘Mc%’
For example #2: LIKE ‘%inger’ searches for all last names that end with the letters “inger” (e.g., Ringer, Stringer).
FROM Employees
WHERE LastName LIKE ‘%inger’
In, example #3: LIKE ‘%en%’ searches for all last names that have the letters “en” (e.g., Bennett, Green, McBadden).
FROM Employees
WHERE LastName LIKE ‘%en%’
SELECT statement with ORDER BY clause
You use the ORDER BY clause to sort the records in the resulting list. Use ASC to sort the results in ascending order and DESC to sort the results in descending order.
For example, with ASC:
FROM Employees
ORDER BY HireDate ASC
And with DESC:
FROM Books
ORDER BY type, price DESC
