6. SELECT Statement

The SELECT statement is used to query the database and retrieve data that matches specified criteria. It has five main clauses, though only the FROM clause is required. Each clause offers various options and parameters. The clauses will be detailed later in the tutorial. The basic format of the SELECT statement is:

SELECT [ALL | DISTINCT] column1[, column2] 
FROM table1[, table2] 
[WHERE "conditions"] 
[GROUP BY "column-list"] 
[HAVING "conditions"] 
[ORDER BY "column-list" [ASC | DESC]];

6.1 FROM & WHERE Clause Quick Review

Example:

SELECT name, age, salary
FROM employee
WHERE age > 50;

This statement selects the name, age, and salary columns from the employee table where the age is greater than 50.

Note: Remember to put a semicolon at the end of your SQL statements. The semicolon (;) indicates that your SQL statement is complete and ready to be interpreted.

6.2 Comparison Operators

• =: Equal
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to
• <> or !=: Not equal to
• LIKE: String comparison test

Example using LIKE:

SELECT name, title, dept 
FROM employee 
WHERE title LIKE 'Pro%';

This statement selects the name, title, and dept columns from the employee table where the title starts with 'Pro', which may return job titles like Programmer or Pro-wrestler.

6.3 ALL and DISTINCT Keywords

ALL and DISTINCT are keywords used to select either all (default) or distinct (unique) records in your query results.

• ALL: Selects all specified columns, including duplicates.
• DISTINCT: Selects unique records in the specified columns.

Example:

SELECT DISTINCT age 
FROM employee_info;

This statement returns all unique ages from the employee_info table.

6.4 Review Exercises

1. From the items_ordered table, select a list of all items purchased for customerid 10449. Display the customerid, item, and price for this customer.
2. Select all columns from the items_ordered table for whoever purchased a Tent.
3. Select the customerid, order_date, and item values from the items_ordered table for any items in the item column that start with the letter "S".
4. Select the distinct items in the items_ordered table. In other words, display a listing of each of the unique items from the items_ordered table.
5. Make up your own select statements and submit them.

6.5 SELECT Exercise Answers

Exercise #1:

SELECT customerid, item, price
FROM items_ordered
WHERE customerid = 10449;

Exercise #2:

SELECT * 
FROM items_ordered 
WHERE item = 'Tent';

Exercise #3:

SELECT customerid, order_date, item
FROM items_ordered
WHERE item LIKE 'S%';

Exercise #4:

SELECT DISTINCT item
FROM items_ordered;