Using the SELECT statement to retrieve data
What regions, districts, and markets are defined in the Aroma database?
The first step is to identify the table that contains the rows and columns that can answer this question. In Part 1, there is a diagram of the tables in the AROMADB. Looking at the lower left-hand section of the diagram, there is a table called Market, which contains columns titled mktkey, hq_city, hq_state, district, and region. Listing the contents of this table will answer our question and can be done with an SQL SELECT statement.
SELECT * FROM aroma.market;
|3||New Orleans||LA||New Orleans||South|
|5||New York||NY||New York||North|
|14||San Jose||CA||San Francisco||West|
|15||San Francisco||CA||San Francisco||West|
|17||Los Angeles||CA||Los Angeles||West|
You use SELECT statements to retrieve columns and rows of data from database tables; to perform arithmetic operations on the data; and to group, order, or group and order the data. In most cases, a SELECT statement contains a simple query expression that begins with the SELECT keyword and is followed by one or more clauses or subclauses.
The most basic SELECT statement contains two keywords, SELECT and FROM:
SELECT column name(s) FROM table name(s)
|column name(s)||Column names or SQL expressions are separated by commas. An asterisk (*) can also be used to list all column names that occur in the list of table name(s)|
|table name(s)||Table names can be a list of tables or a SELECT statement. The table names are separated by commas. Referenced table(s) must contain the column name(s) that are referenced after the SELECT keyword. In this example, the FROM statement refers to AROMA.MARKET. More will be discussed about this naming convention later in Part 3, Table Names and Schemas. In this case, the FROM clause refers to the qualified table name. The first part is the schema and the second part is the table name. Schemas are useful to group like tables and other DB2 objects. In the AROMADB database, all the tables were set up with AROMA as the schema.|
SELECT mktkey, hq_city, hq_state, district, region FROM aroma.market
The above query produces the same result as
SELECT * FROM aroma.market; however, rather than using the asterisk (*) to list all columns, the column
names are identified.
|Important: SELECT and FROM (and all other words shown in uppercase) are reserved SQL keywords. These words must be used exactly as defined by the SQL standard. We use the uppercase format in this document to make the keywords easy to see; SQL is not case sensitive, so keywords can be written in uppercase or lowercase.|
The semicolon (;) at the end of each example in this guide is not a mandatory part of SQL syntax. By convention, the SQL language ignores extra spaces, tabs, and end-of-line indicators. The semicolon, then, is an end-of-statement marker and not necessary if you execute only a single statement. For situations where you create a scripted series of SQL statements, the semicolon provides a definite end to each statement. Depending on the interactive SQL tool you use to enter queries, you may or may not need to specify such a marker. When using the Command Line Processor to execute multiple SQL statements, a semicolon is required to indicated the end of each statement.