Simple SELECTs
- About the database we'll be using in class.
- To comment your SQL code.
- To understand SQL syntax.
- To select all rows from a table.
- To sort record sets.
- To filter records.
The SELECT statement is used to retrieve data from tables. SELECT statements can be used to perform simple tasks such as retrieving records from a single table or complicated tasks such as retrieving data from multiple tables with record grouping and sorting. In this lesson, we will look at several of the more basic ways to retrieve data from a single table.
Introduction to the Northwind Database
The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server and Microsoft Access. The database contains the sales data for Northwind Traders, a fictitious specialty foods export-import company.
Although the code taught in this class is not specific to Microsoft products, we use the Northwind database for many of our examples because many people are already familiar with it and because there are many resources for related learning that make use of the same database.
The diagram below shows the table structure of the Northwind database.
The Northwind database has additional tables, but we will only be using the ones shown above. In this lesson, we will explore some of these tables.
Some Basics
Comments
The standard SQL comment is two hyphens (--). However, some databases use other forms of comments as shown in the table below.
| -- | # | /* */ | |
|---|---|---|---|
| Example | -- Comment | # Comment | /* Comment */ |
| ANSI | YES | NO | NO |
| SQL Server | YES | NO | YES |
| Oracle | YES | NO | YES |
| MySQL | YES | YES | YES |
The code sample below shows some sample comments.
Code Sample: Simple Selects/Demos/Comments.sql
-- Single-line comment /* Multi-line comment used in: -SQL Server -Oracle -MySQL */
Whitespace and Semi-colons
Whitespace is ignored in SQL statements. Multiple statements are separated with semi-colons. The two statements in the sample below are equally valid.
Code Sample: Simple Selects/Demos/White Space.sql
SELECT * FROM Employees; SELECT * FROM Employees;
Case Sensitivity
SQL is not case sensitive. It is common practice to write reserved words in all capital letters. User-defined names, such as table names and column names may or may not be case sensitive depending on the operating system used.
SELECTing All Columns in All Rows
The following syntax is used to retrieve all columns in all rows of a table.
SELECT table.* FROM table; -- OR SELECT * FROM table;
Code Sample: Simple Selects/Demos/SelectAll.sql
--Retrieve all columns in the Region table SELECT * FROM Region;
The above SELECT statement will return the following results:
As you can see, the Region table has only two columns, RegionID and RegionDescription, and four rows.
Exercise: Exploring the Tables
In this exercise, you will explore all the data in the Northwind database by selecting all the rows of each of the tables.
- Select all columns of all rows from the tables below.
- The number of records that should be returned is indicated in parentheses next to the table name.
- Categories (8)
- Customers (91)
- Employees (9)
- Orders (830)
- Products (77)
- Shippers (3)
- Suppliers (29)
SELECTing Specific Columns
The following syntax is used to retrieve specific columns in all rows of a table.
SELECT table_name.column_name, table_name.column_name FROM table; -- OR SELECT column, column FROM table;
Code Sample: Simple Selects/Demos/SelectCols.sql
/* Select the FirstName and LastName columns from the Employees table. */ SELECT FirstName, LastName FROM Employees;
The above SELECT statement will return the following results:
Exercise: SELECTing Specific Columns
In this exercise, you will practice selecting specific columns from tables in the Northwind database.
- Select CategoryName and Description from the Categories table.
- Select ContactName, CompanyName, ContactTitle and Phone from the Customers table.
- Select EmployeeID, Title, FirstName, LastName, and Region from the Employees table.
- Select RegionID and RegionDescription from the Region table.
- Select CompanyName, Fax, Phone and HomePage from the Suppliers table.
Sorting Records
The ORDER BY clause of the SELECT statement is used to sort records.
Sorting By a Single Column
To sort by a single column, simply name that column in the ORDER BY clause.
SELECT column, column FROM table ORDER BY column;
Note that columns in the ORDER BY clause do not have to appear in the SELECT clause.
Code Sample: Simple Selects/Demos/OrderBy1.sql
/* Select the FirstName and LastName columns from the Employees table. Sort by LastName. */ SELECT FirstName, LastName FROM Employees ORDER BY LastName;
The above SELECT statement will return the following results:
Sorting By Multiple Columns
To sort by multiple columns, comma-delimit the column names in the ORDER BY clause.
SELECT column, column FROM table ORDER BY column, column;
Code Sample: Simple Selects/Demos/OrderBy2.sql
/* Select the Title, FirstName and LastName columns from the Employees table. Sort first by Title and then by LastName. */ SELECT Title, FirstName, LastName FROM Employees ORDER BY Title, LastName;
The above SELECT statement will return the following results:
Sorting By Column Position
It is also possible to sort tables by the position of a column in the SELECT list. To do so, specify the column numbers in the ORDER BY clause.
SELECT column, column FROM table ORDER BY column_position, column_position;
Code Sample: Simple Selects/Demos/OrderBy3.sql
/* Select the Title, FirstName and LastName columns from the Employees table. Sort first by Title (position 1) and then by LastName (position 3). */ SELECT Title, FirstName, LastName FROM Employees ORDER BY 1,3;
The above SELECT statement will return the same results as the previous query:
Ascending and Descending Sorts
By default, when an ORDER BY clause is used, records are sorted in ascending order. This can be explicitly specified with the ASC keyword. To sort records in descending order, use the DESC keyword.
SELECT column, column FROM table ORDER BY column_position DESC, column_position ASC;
Code Sample: Simple Selects/Demos/OrderBy4.sql
/* Select the Title, FirstName and LastName columns from the Employees table. Sort first by Title in ascending order and then by LastName in descending order. */ SELECT Title, FirstName, LastName FROM Employees ORDER BY Title ASC, LastName DESC;
The above SELECT statement will return the following results:
Exercise: Sorting Results
In this exercise, you will practice sorting results in SELECT statements.
- Select CategoryName and Description from the Categories table sorted by CategoryName.
- Select ContactName, CompanyName, ContactTitle, and Phone from the Customers table sorted by Phone.
- Create a report showing employees' first and last names and hire dates sorted from newest to oldest employee.
- Create a report showing Northwind's orders sorted by Freight from most expensive to cheapest. Show OrderID, OrderDate, ShippedDate, CustomerID, and Freight.
- Select CompanyName, Fax, Phone, HomePage and Country from the Suppliers table sorted by Country in descending order and then by CompanyName in ascending order.
The WHERE Clause and Operator Symbols
The WHERE clause is used to retrieve specific rows from tables. The WHERE clause can contain one or more conditions that specify which rows should be returned.
SELECT column, column FROM table WHERE conditions;
The following table shows the symbolic operators used in WHERE conditions.
| Operator | Description |
|---|---|
| = | Equals |
| <> | Not Equal |
| > | Greater Than |
| < | Less Than |
| >= | Greater Than or Equal To |
| <= | Less Than or Equal To |
Note that non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in single quotes. Examples are shown below.
Checking for Equality
Code Sample: Simple Selects/Demos/Where-Equal.sql
/* Create a report showing the title and the first and last name of all sales representatives. */ SELECT Title, FirstName, LastName FROM Employees WHERE Title = 'Sales Representative';
The above SELECT statement will return the following results:
Checking for Inequality
Code Sample: Simple Selects/Demos/Where-NotEqual.sql
/* Create a report showing the first and last name of all employees excluding sales representatives. */ SELECT FirstName, LastName FROM Employees WHERE Title <> 'Sales Representative';
The above SELECT statement will return the following results:
Exercise: Using the WHERE clause to check for equality or inequality
In this exercise, you will practice using the WHERE clause to check for equality and inequality.
- Create a report showing all the company names and contact names of Northwind's customers in Buenos Aires.
- Create a report showing the product name, unit price and quantity per unit of all products that are out of stock.
- Create a report showing the order date, shipped date, customer id, and freight of all orders placed on May 19, 1997.
- Oracle users will have to use following date format: 'dd-mmm-yyyy' (e.g, '19-May-1997').
- MySQL users will have to use following date format: 'yyyy-mm-dd' (e.g, '1997-05-19').
- Create a report showing the first name, last name, and country of all employees not in the United States.
Checking for Greater or Less Than
The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings.
Code Sample: Simple Selects/Demos/Where-GreaterThanOrEqual.sql
/* Create a report showing the first and last name of all employees whose last names start with a letter in the last half of the alphabet. */ SELECT FirstName, LastName FROM Employees WHERE LastName >= 'N';
The above SELECT statement will return the following results:
Exercise: Using the WHERE clause to check for greater or less than
In this exercise, you will practice using the WHERE clause to check for values greater than or less than a specified value.
- Create a report that shows the employee id, order id, customer id, required date, and shipped date of all orders that were shipped later than they were required.
- Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B."
- Create a report that shows all orders that have a freight cost of more than $500.00.
- Create a report that shows the product name, units in stock, units on order, and reorder level of all products that are up for reorder.
Checking for NULL
When a field in a row has no value, it is said to be NULL. This is not the same as having an empty string. Rather, it means that the field contains no value at all. When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.
Code Sample: Simple Selects/Demos/Where-Null.sql
/* Create a report showing the first and last names of all employees whose region is unspecified. */ SELECT FirstName, LastName FROM Employees WHERE Region IS NULL;
The above SELECT statement will return the following results:
Code Sample: Simple Selects/Demos/Where-NotNull.sql
/* Create a report showing the first and last names of all employees who have a region specified. */ SELECT FirstName, LastName FROM Employees WHERE Region IS NOT NULL;
The above SELECT statement will return the following results:
Exercise: Checking for NULL
In this exercise, you will practice selecting records with fields that have NULL values.
- Create a report that shows the company name, contact name and fax number of all customers that have a fax number.
- Create a report that shows the first and last name of all employees who do not report to anybody.
WHERE and ORDER BY
When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.
Code Sample: Simple Selects/Demos/Where-OrderBy.sql
/* Create a report showing the first and last name of all employees whose last names start with a letter in the last half of the alphabet. Sort by LastName in descending order. */ SELECT FirstName, LastName FROM Employees WHERE LastName >= 'N' ORDER BY LastName DESC;
The above SELECT statement will return the following results:
Exercise: Using WHERE and ORDER BY Together
In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY.
- Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name.
- Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with "A" or "B." Sort by contact name in descending order.
The WHERE Clause and Operator Words
The following table shows the word operators used in WHERE conditions.
| Operator | Description |
|---|---|
| BETWEEN | Returns values in an inclusive range |
| IN | Returns values in a specified subset |
| LIKE | Returns values that match a simple pattern |
| NOT | Negates an operation |
The BETWEEN Operator
The BETWEEN operator is used to check if field values are within a specified inclusive range.
Code Sample: Simple Selects/Demos/Where-Between.sql
/* Create a report showing the first and last name of all employees whose last names start with a letter between "J" and "M". */ SELECT FirstName, LastName FROM Employees WHERE LastName BETWEEN 'J' AND 'M'; -- The above SELECT statement is the same as the one below. SELECT FirstName, LastName FROM Employees WHERE LastName >= 'J' AND LastName <= 'M';
The above SELECT statements will both return the following results:
Note that a person with the last name "M" would be included in this report.
The IN Operator
The IN operator is used to check if field values are included in a specified comma-delimited list.
Code Sample: Simple Selects/Demos/Where-In.sql
/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy is "Mrs." or "Ms.".
*/
SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');
-- The above SELECT statement is the same as the one below
SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';
The above SELECT statements will both return the following results:
The LIKE Operator
The LIKE operator is used to check if field values match a specified pattern.
The Percent Sign (%)
The percent sign (%) is used to match any zero or more characters.
Code Sample: Simple Selects/Demos/Where-Like1.sql
/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy begins with "M". */ SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE TitleOfCourtesy LIKE 'M%';
The above SELECT statement will return the following results:
The Underscore (_)
The underscore (_) is used to match any single character.
Code Sample: Simple Selects/Demos/Where-Like2.sql
/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy begins with "M" and is followed by any character and a period (.). */ SELECT TitleOfCourtesy, FirstName, LastName FROM Employees WHERE TitleOfCourtesy LIKE 'M_.';
The above SELECT statement will return the following results:
Wildcards and Performance
Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.
The NOT Operator
The NOT operator is used to negate an operation.
Code Sample: Simple Selects/Demos/Where-Not.sql
/*
Create a report showing the title of courtesy and the first and last name
of all employees whose title of courtesy is not "Ms." or "Mrs.".
*/
SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.');
The above SELECT statement will return the following results:
Exercise: More SELECTs with WHERE
In this exercise, you will practice writing SELECT statements that use WHERE with word operators.
- Create a report that shows the first and last names and birth date of all employees born in the 1950s.
- Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders. Hint: you will need to first do a separate SELECT on the Suppliers table to find the supplier ids of these three companies.
- Create a report that shows the shipping postal code, order id, and order date for all orders with a ship postal code beginning with "02389".
- Create a report that shows the contact name and title and the company name for all customers whose contact title does not contain the word "Sales".
Checking Multiple Conditions
AND
AND can be used in a WHERE clause to find records that match more than one condition.
Code Sample: Simple Selects/Demos/Where-And.sql
/* Create a report showing the first and last name of all sales representatives whose title of courtesy is "Mr.". */ SELECT FirstName, LastName FROM Employees WHERE Title = 'Sales Representative' AND TitleOfCourtesy = 'Mr.';
The above SELECT statement will return the following results:
OR
OR can be used in a WHERE clause to find records that match at least one of several conditions.
Code Sample: Simple Selects/Demos/Where-Or.sql
/* Create a report showing the first and last name and the city of all employees who are from Seattle or Redmond. */ SELECT FirstName, LastName, City FROM Employees WHERE City = 'Seattle' OR City = 'Redmond';
The above SELECT statement will return the following results:
Order of Evaluation
By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.
Code Sample: Simple Selects/Demos/Where-AndOrPrecedence.sql
/* Create a report showing the first and last name of all sales representatives who are from Seattle or Redmond. */ SELECT FirstName, LastName, City, Title FROM Employees WHERE City = 'Seattle' OR City = 'Redmond' AND Title = 'Sales Representative';
The above SELECT statement will return the following results:
Notice that Laura Callahan is returned by the query even though she is not a sales representative. This is because this query is looking for employees from Seattle OR sales representatives from Redmond.
This can be fixed by putting the OR portion of the clause in parentheses.
Code Sample: Simple Selects/Demos/Where-AndOrPrecedence2.sql
/* Create a report showing the first and last name of all sales representatives who are from Seattle or Redmond. */ SELECT FirstName, LastName, City, Title FROM Employees WHERE (City = 'Seattle' OR City = 'Redmond') AND Title = 'Sales Representative';
The parentheses specify that the OR portion of the clause should be evaluated first, so the above SELECT statement will return the same results minus Laura Callahan.
If only to make the code more readable, it's a good idea to use parentheses whenever the order of precedence might appear ambiguous.
Exercise: Writing SELECTs with Multiple Conditions
In this exercise, you will practice writing SELECT statements that filter records based on multiple conditions.
- Create a report that shows the first and last names and cities of employees from cities other than Seattle in the state of Washington.
- Create a report that shows the company name, contact title, city and country of all customers in Mexico or in any city in Spain except Madrid.
Simple SELECTs Conclusion
In this lesson of the SQL tutorial, you have learned a lot about creating reports with SELECT. However, this is just the tip of the iceberg. SELECT statements can get a lot more powerful and, of course, a lot more complicated.
0 Comments