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 toLIKE
: 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
- From the
items_ordered
table, select a list of all items purchased forcustomerid
10449. Display thecustomerid
,item
, andprice
for this customer. - Select all columns from the
items_ordered
table for whoever purchased a Tent. - Select the
customerid
,order_date
, anditem
values from theitems_ordered
table for any items in theitem
column that start with the letter "S". - Select the distinct items in the
items_ordered
table. In other words, display a listing of each of the unique items from theitems_ordered
table. - 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;