Retrieving Data

1.

Retrieving Data
Aitbekova M.B.

2.

The SELECT Statement
”SQLstatements are made up of plain English terms. These terms are
called keywords, and every SQLstatement is made up of one or more
keywords. The SQLstatement that you’ll probably use most frequently
is the SELECT statement. Its purpose is to retrieve information from
one or more tables.
To use SELECT to retrieve
table data, you must, at a
minimum, specify two pieces
of information—what you
want to select and from where
you want to select it.

3.

Understanding the Sample Tables
The tables used are part of an order entry system used by an imaginary
distributor of toys. The tables are used to perform several tasks:
Manage vendors
Manage product catalogs
Manage customer lists
Enter customer orders
Making this all work requires five tables (that are closely interconnected
as part of a relational database design). A description of each of the
tables appears in the following sections.

4.

Table Descriptions
What follows is a description of
each of the five tables, along with
the name of the columns within
each table and their descriptions.
The Vendors Table
The Vendors table stores the
vendors whose products are sold.
Every vendor has a record in this
table, and that vendor ID (the
vend_id) column is used to match
products with vendors.

5.

The Products Table
The Products table contains the
product catalog, one product per
row. Each product has a unique ID
(the prod_id column) and is
related to its vendor by vend_id
(the vendor’s unique ID).

6.

The Customers Table
The Customers table stores all
customer information. Each
customer has a unique ID (the
cust_id column).

7.

The Orders Table
The Orders table stores customer orders (but not order details). Each order is
uniquely numbered (the order_num column). Orders are associated with the
appropriate customers by the cust_id column (which relates to the customer’s
unique ID in the Customers table).

8.

The OrderItems Table
The OrderItems table stores the actual items
in each order, one row per item per order. For
every row in Orders there are one or more
rows in OrderItems. Each order item is
uniquely identified by the order number plus
the order item (first item in order, second item
in order, and so on). Order items are
associated with their appropriate order by the
order_num column (which relates to the
order’s unique ID in Orders). In addition,
each order item contains the product ID of the
item orders (which relates the item back to the
Products table).

9.

Relationship Diagram
Database administrators often
use relationship diagrams to
help demonstrate how
database tables are connected.
Remember, it is foreign keys
that define those
relationships as noted in the
table descriptions. This is a
relationship diagram for the
five tables described before.

10.

Retrieving Individual Columns
We’ll start with a simple SQLSELECT statement, as follows:
The previous statement uses the SELECT statement to retrieve a single column
called prod_name from the Products table. The desired column name is specified
right after the SELECT keyword, and the FROM keyword specifies the name of the
table from which to retrieve the data. The output from this statement is shown in
the following:

11.

Depending on the DBMS and client you are
using, you may also see a message
telling you how many rows were retrieved
and the processing time. For example,
the MySQLcommand line would display
something like this:
9 rows in set (0.01 sec)

12.

13.

Retrieving Multiple Columns
To retrieve multiple columns from a table, the same SELECT statement
is used. The only difference is that multiple column names must be
specified after the SELECT keyword, and each column must be
separated by a comma.

14.

Just as in the prior example, this statement uses the SELECT statement to
retrieve data from the Products table. In this example, three column names
are specified, each separated by a comma. The output from this statement is
shown below:

15.

16.

Retrieving All Columns
In addition to being able to specify desired columns (one or more, as seen above),
SELECT statements can also request all columns without having to list them
individually. This is done using the asterisk (*) wildcard character in lieu of actual
column names, as follows:
When a wildcard (*) is specified, all the columns in the table are returned. The column order will
typically, but not always, be the physical order in which the columns appear in the table
definition. However, SQLdata is seldom displayed as is. (Usually, it is returned to an application
that formats or presents the data as needed). As such, this should not pose a problem.

17.

Wildcards

18.

Retrieving Distinct Rows
As you have seen, SELECT returns all matched rows. But what if you do not want
every occurrence of every value? For example, suppose you want the vendor ID of
all vendors with products in your Products table:
The SELECT statement returned nine
rows (even though there are only three
unique
vendors in that list) because there are
nine products listed in the Products
table.
So how could you retrieve a list of
distinct values?

19.

The solution is to use the DISTINCT keyword, which, as its name implies, instructs
the database to only return distinct values.

20.

21.

Limiting Results
• SELECT statements return all matched rows, possibly every row in the specified
table. What if you want to return just the first row or a set number of rows? This is
doable, but unfortunately, this is one of those situations where all SQL
implementations are not created equal.
• In Microsoft SQLServer you can use the TOP keyword to limit the top number of
entries, as seen here:

22.

23.

24.

25.

26.

Using Comments

27.

28.

Summary
In this lesson, you learned how to use the SQLSELECT statement to retrieve a
single table column, multiple table columns, and all table columns. You alson
learned how to return distinct values and how to comment your code. And
unfortunately, you were also introduced to the fact that more complex SQLtends to
be less portable SQL. Next, you’ll learn how to sort the retrieved data.

29.

Thank you for your attention!