Selecting Data

This section is designed to help you become familiar with the construction and design of basic queries. Most topics focus on basic single-table SELECT statements and include sample statements that you can use to practice writing queries.

All of the examples in this section use the Sybase IQ demo database (iqdemo.db) as a data source. To run the queries in this section, start the demo database, open Interactive SQL, and connect to iqdemo.

• Viewing Table Information
  Queries retrieve information from the database. All queries include a SELECT statement and can contain additional clauses that determine what data the query returns. Query results are stored in a result table called the result set.
• Ordering Query Results
  Sybase IQ does not return the rows in a result set in any particular order. Adding an ORDER BY clause to a SELECT statement returns the results in alphabetical or numerical order.
• Selecting Columns and Rows
  Often, you are interested in only columns in a table. For example, to make up birthday cards for employees you might want to see the Surname, DepartmentID and BirthDate columns.
• Using Search Conditions
  Adding a WHERE clause to a SELECT statement retrieves only those records that meet a specific condition. .
• Comparing Dates in Queries
  Use a comparison in the WHERE clause to select a set of rows that satisfy the search condition.
• Compound Search Conditions in the WHERE Clause
  Using comparison operators such as greater than (>), greater than or equal (>=), less than or equal (<=), and not equal (<>) with AND and OR creates more complex search conditions.
• Pattern Matching in Search Conditions
  Use the LIKE operator in a WHERE clause to search for patterns in a column.
• Matching Rows by Sound
  With the SOUNDEX function, you can match rows by sound, as well as by spelling.
• Using Shortcuts for Search Conditions
  Use the BETWEEN and IN operators in a WHERE clause to retrieve multiple values.
• Obtaining Aggregate Data
  Aggregate functions perform calculations on a set of values rather than on a single value.
• Improving Subtotal Calculation
  If you have data that varies across dimensions such as date or place, you may need to determine how the data varies in each dimension. ROLLUP and CUBE are extension to the GROUP BY operator that allow you to use one query to compute data using multiple levels of grouping, instead of a separate query for each level.
• Obtaining Analytical Data
  Analytical functions manipulate data within result sets. Rank analytical functions rank items in a group. Inverse distribution analytical functions return a k-th percentile value.
• Eliminating Duplicate Rows
  Use the DISTINCT keyword to eliminate the duplicate rows.