This lesson helps the reader the fundamentals of the SQL SELECT statement, which is used to query the database for useful information. The database user has many options with the SELECT statement, proving that the query is the most robust feature of SQL.
The highlights of this hour include
- What a database query is
- How to use the SELECT statement
- Adding conditions to queries using the WHERE clause
- Using column aliases
- Selecting data from another user's table
What Is a Query?
A query is an inquiry into the database using the SELECT statement. A query is used to extract data from the database in a readable format according to the user's request. For instance, if you have an employee table, you might issue a SQL statement that returns the employee who is paid the most. This request to the database for usable employee information is a typical query that can be performed in a relational database.
The SELECT Statement
The SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable format. The SELECT part of the query is for selecting the data you want to see according to the columns in which they are stored in a table.
The syntax for a simple SELECT statement is as follows:
The asterisk (*) is used to denote that all columns in a table should be displayed as part of the output. Check your particular implementation for its usage. The ALL option is used to display all values for a column, including duplicates. The DISTINCT option is used to suppress duplicate rows from being displayed in the output. The default between DISTINCT and ALL is ALL, which does not have to be specified. Notice that the columns following the SELECT are separated by commas, as is the table list following the FROM.
Explore the basic capabilities of the SELECT statement by studying the following examples. First, perform a simple query from the PRODUCT_TBL table:
SELECT * FROM PRODUCT_TBL;
PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 222 PLASTIC PUMPKIN 18 INCH 7.75 13 FALSE PARAFFIN TEETH 1.1 90 LIGHTED LANTERNS 14.5 15 ASSORTED COSTUMES 10 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 1234 KEY CHAIN 5.95 2345 OAK BOOKSHELF 59.99 11 rows selected.
Product_TBL tale has the three columns PROD_ID, PROD_DESC and COST.. When we select all columns using (*) then we get 11 rows.
Now select data from another table, CAND_TBL. Create this table in the image of the PRODUCT_TBL table for the following examples. List the column name after the SELECT keyword to display only one column in the table:
SELECT PROD_DESC FROM CANDY_TBL;
PROD_DESC ------------------ CANDY CORN CANDY CORN HERSHEYS KISS SMARTIES 4 rows selected.
The DISTINCT option is used in the following statement to get the unique recods. Notice that the value CANDY CORN is only printed once in this example.
SELECT DISTINCT PROD_DESC FROM CANDY_TBL;
PROD_DESC ------------------ CANDY CORN HERSHEYS KISS SMARTIES 3 rows selected.
DISTINCT can also be used with parentheses enclosing the associated column. The use of parentheses is often used in SQL—as well as many other languages—to improve readability.
SELECT DISTINCT(PROD_DESC) FROM CAND_TBL;
PROD_DESC ------------------ CANDY CORN HERSHEYS KISS SMARTIES 3 rows selected.
The FROM Clause
The FROM clause must be used in conjunction with the SELECT statement. It is a required element for any query. The FROM clause's purpose is to tell the database what table(s) to access to retrieve the desired data for the query. The FROM clause may contain one or more tables. The FROM clause must always list at least one table.
The syntax for the FROM clause is as follows:
from table1 [ , table2 ]
Using Conditions to Distinguish Data
A condition is part of a query that is used to display selective information as specified by the user. The value of a condition is either TRUE or FALSE, thereby limiting the data received from the query. The WHERE clause is used to place conditions on a query by eliminating rows that would normally be returned by a query without conditions.
There can be more than one condition in the WHERE clause. If there is more than one condition, they are connected by the AND and OR operators, which are discussed during Hour 8, "Using Operators to Categorize Data." As you also learn during the next hour, there are several conditional operators that can be used to specify conditions in a query. This hour only deals with a single condition for each query.
An operator is a character or keyword in SQL that is used to combine elements in a SQL statement.
The syntax for the WHERE clause is as follows:
select [ all | * | distinct column1, column2 ] from table1 [ , table2 ] where [ condition1 | expression1 ] [ and condition2 | expression2 ]
The following is a simple SELECT without conditions specified by the WHERE clause:
SELECT * FROM PRODUCT_TBL;
PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 222 PLASTIC PUMPKIN 18 INCH 7.75 13 FALSE PARAFFIN TEETH 1.1 90 LIGHTED LANTERNS 14.5 15 ASSORTED COSTUMES 10 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 1234 KEY CHAIN 5.95 2345 OAK BOOKSHELF 59.99 11 rows selected.
Now add a condition for the same query.
SELECT * FROM PRODUCTS_TBL WHERE COST < 5;
PROD_ID PROD_DESC COST ---------- ------------------------------- ----- 13 FALSE PARAFFIN TEETH 1.1 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 5 rows selected.
The only records displayed are those that cost less than 5.
In the following query, you want to display the product description and cost that matches the product identification 119.
SELECT PROD_DESC, COST FROM PRODUCTS_TBL WHERE PROD_ID = '119';
PROD_DESC COST ------------------------------- ----- ASSORTED MASKS 4.95 1 row selected.
Sorting The Output
Data can be sorted by using the ORDER BYclause. The ORDER BY clause arranges the results of a query in a listing format you specify. The default ordering of the ORDER BY clause is an ascending order; the sort displays in the order A–Z if it's sorting output names alphabetically. A descending order for alphabetical output would be displayed in the order Z–A. Ascending order for output for numeric values between 1 and 9 would be displayed 1–9; descending order is displayed as 9–1.
The syntax for the ORDER BY is as follows:
select [ all | * | distinct column1, column2 ] from table1 [ , table2 ] where [ condition1 | expression1 ] [ and condition2 | expression2 ] ORDER BY column1|integer [ ASC|DESC ]
Order by the product description in ascending order or alphabetical order. use ASC can be specified after any column in the ORDER BY clause.
SELECT PROD_DESC, PROD_ID, COST FROM PRODUCT_TBL WHERE COST < 20 ORDER BY PROD_DESC ASC;
PROD_DESC PROD_ID COST ------------------------- --------------- ------ ASSORTED COSTUMES 15 10 ASSORTED MASKS 119 4.95 CANDY CORN 9 1.35 FALSE PARAFFIN TEETH 13 1.1 LIGHTED LANTERNS 90 14.5 PLASTIC PUMPKIN 18 INCH 222 7.75 PLASTIC SPIDERS 87 1.05 PUMPKIN CANDY 6 1.45 8 rows selected.
Order by the product description in descending order or alphabetical order. Use DESC can be specified after any column in the ORDER BY clause.
SELECT PROD_DESC, PROD_ID, COST FROM PRODUCT_TBL WHERE COST < 20 ORDER BY PROD_DESC DESC;
PROD_DESC PROD_ID COST ------------------------- --------------- ------ PUMPKIN CANDY 6 1.45 PLASTIC SPIDERS 87 1.05 PLASTIC PUMPKIN 18 INCH 222 7.75 LIGHTED LANTERNS 90 14.5 FALSE PARAFFIN TEETH 13 1.1 CANDY CORN 9 1.35 ASSORTED MASKS 119 4.95 ASSORTED COSTUMES 15 10
No comments:
Post a Comment