The SELECT statement is used
to query the database and retrieve selected data that match the
criteria that you specify.
The SELECT statement has five main clauses to choose from,
although, FROM is the only required clause. Each of the clauses have
a vast selection of options, parameters, etc. The clauses will be
listed below, but each of them will be covered in more detail later
in the tutorial.
Here is the format of the SELECT statement:
SELECT [ALL | DISTINCT] column1[,column2] FROM
table1[,table2] [WHERE "conditions"] [GROUP BY
"column-list"] [HAVING "conditions] [ORDER BY "column-list"
[ASC | DESC] ]
FROM &
WHERE clause quick review
Example:
SELECT name, age, salary FROM employee WHERE age > 50;
The above statement will select all of the values in the name,
age, and salary columns from the employee table whose age is greater
than 50.
Note: Remember to put a semicolon at the end of your SQL
statements. The ; indicates that your SQL statment is
complete and is ready to be interpreted.
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 |
*Note about LIKE
Example:
SELECT name, title, dept FROM employee WHERE title LIKE
'Pro%';
The above statement will select all of the rows/values in the
name, title, and dept columns from the employee table whose title
starts with 'Pro'. This may return job titles including Programmer
or Pro-wrestler.
ALL and DISTINCT are keywords used to select either
ALL (default) or the "distinct" or unique records in your query
results. If you would like to retrieve just the unique records in
specified columns, you can use the "DISTINCT" keyword. DISTNCT will
discard the duplicate records for the columns you specified after
the "SELECT" statement: For example:
SELECT DISTINCT age FROM employee_info;
This statement will return all of the unique ages in the
employee_info table.
ALL will display "all" of the specified columns including all of
the duplicates. The ALL keyword is the default if nothing is
specified.
Note: The following two tables will be used throughout
this course. It is recommended to have them open in another window
or print them out.
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.
Answers to
these Exercises
Enter SQL Statement
here:
|