Table of Contents
|
Introduction
The SELECT statement is constructed of clauses that let you look at data in a relational database.
Selection is implemented through the WHERE clause of a SELECT statement.
SELECT * FROM customer WHERE state = 'NJ'
Projection
Projection is defined as taking a vertical subset from the columns of a single table that retains the unique rows.
SELECT UNIQUE city, state, zipcode FROM customer
The most common kind of SELECT statement uses both selection and projection.
SELECT UNIQUE city, state, zipcode FROM customer WHERE state = 'NJ'
Join
A join occurs when two or more tables are connected by one or more columns in common, creating a new table of results.
SELECT UNIQUE city, state, zipcode, sname FROM customer, state WHERE customer.state = state.code
Select all columns
Using the Asterisk Symbol (*). The following query specifies all the columns in the manufact table in a select list. A select list is a list of the column names or expressions that you want to project from a table.
SELECT * FROM manufact
Reordering the Columns
SELECT manu_name, manu_code, lead_time FROM manufact
Sorting the Rows
You can add an ORDER BY clause to your SELECT statement to direct the system to sort the data in a specific order.You must include the columns that you want to use in the ORDER BY clause in the select list.
SELECT manu_code, manu_name, lead_time FROM manufact ORDER BY lead_time [ASCE | DESC]
You can also ORDER BY two or more columns, creating a nested sort. The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence.
SELECT * FROM stock ORDER BY manu_code, unit_price
Using substrings
To select part of the value of a CHARACTER column, include a substring in the select list. The following query selects the first three characters of the zipcode column
SELECT zipcode[1,3], customer_num FROM customer ORDER BY zipcode
Using the WHERE clause.
SELECT customer_num, call_code, call_dtime, res_dtime FROM cust_calls WHERE user_id = 'maryj'
Specify rows in a WHERE clause.
SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog WHERE catalog_num BETWEEN 10005 AND 10008
the same as
SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog WHERE catalog_num >= 10005 AND catalog_num <= 10008
The following query uses the keywords NOT BETWEEN to exclude specific rows.
SELECT fname, lname, company, city, state FROM customer WHERE zipcode NOT BETWEEN '94000' AND '94999' ORDER BY state
Null Values
Use the IS NULL or IS NOT NULL option to check for null values. A null value represents either the absence of data or an unknown value.
SELECT order_num, customer_num, po_num, ship_date FROM orders WHERE paid_date IS NULL ORDER BY customer_num
Using Boolean expressions.
SELECT order_num, customer_num, po_num, ship_date FROM orders WHERE paid_date IS NULL AND ship_date IS NOT NULL ORDER BY customer_num
Using Variable-Text Searches
LIKE for variable-text queries
SELECT * FROM orders WHERE ship_instruct LIKE('express')
SELECT * FROM orders WHERE ship_instruct LIKE '%xp%'
MATCHES is an Informix extension.
SELECT * FROM orders WHERE ship_instruct MATCHES('express')
Wildcards Table
Symbol | Meaning |
---|---|
LIKE | |
% | Evaluates to zero or more characters |
_ | Evaluates to a single character |
\ | Escapes special significance of next character |
MATCHES | |
* | Evaluates to zero or more characters |
? | Evaluates to a single character (except null) |
[] | Evaluates to a single character or range of values |
\ | Escapes special significance of next character |
Comparing for Special Characters
The following query uses the keyword ESCAPE with LIKE or MATCHES so you can protect a special character from misinterpretation as a wildcard symbol.
SELECT * FROM cust_calls WHERE res_descr LIKE '%!%%' ESCAPE '!'
The following query specifies a range of characters in a column.
SELECT catalog_num, stock_num, manu_code, cat_advert, cat_descr FROM catalog WHERE cat_advert[1,4] = 'High'
You can use the SELECT clause of a SELECT statement to perform computations on column data and to display information derived from the contents of one or more columns.
SELECT stock_num, description, unit, unit_descr, unit_price, unit_price * 1.07 TAXES FROM stock WHERE unit_price >= 400
Functions
COUNT: The following query counts and displays the total number of rows in the stock table.
SELECT COUNT(*) FROM stock
By including the keyword DISTINCT (or its synonym UNIQUE) and a column name , you can tally the number of different manufacturer codes in the stock table.
SELECT COUNT(DISTINCT manu_code) FROM stock
AVG: The following query computes the average unit_price of all rows in the stock table.
SELECT AVG (unit_price) FROM stock
Multiple-Table SELECT Statements
A Cartesian product consists of every possible combination of rows from the tables.
SELECT * FROM customer, state
Equi-Join: An equi-join is a join based on equality or matching values.
SELECT * FROM manufact, stock WHERE manufact.manu_code = stock.manu_code
The following query joins tables that contain columns with the same name, precede each column name with a period and its table name.
SELECT order_num, order_date, ship_date, cust_calls.* FROM orders, cust_calls WHERE call_dtime >= ship_date AND cust_calls.customer_num = orders.customer_num ORDER BY customer_num
GROUP BY
The GROUP BY clause divides a table into sets.
The following query retrieves the number of items and the total price of all items for each order. The GROUP BY clause causes the rows of the items table to be collected into groups, each group composed of rows that have identical order_num values (that is, the items of each order are grouped together). After you form the groups, the aggregate functions COUNT and SUM are applied within each group.
SELECT order_num, COUNT (*) number, SUM (total_price) price FROM items GROUP BY order_num
HAVING Clause
The HAVING clause usually complements a GROUP BY clause by applying one or more qualifying conditions to groups after they are formed.
The following query returns the average total price per item on all orders that have more than two items.
SELECT order_num, COUNT(*) number, AVG (total_price) average FROM items GROUP BY order_num HAVING COUNT(*) > 2
Self-Joins
Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
The following query finds pairs of orders where the ship_weight differs by a factor of five or more and the ship_date is not null. The query then orders the data by ship_date.
SELECT x.order_num, x.ship_weight, x.ship_date, y.order_num, y.ship_weight, y.ship_date FROM orders x, orders y WHERE x.ship_weight >= 5 * y.ship_weight AND x.ship_date IS NOT NULL AND y.ship_date IS NOT NULL ORDER BY x.ship_date
Outer Joins
In an outer join, the result contains the combination of rows from the tables that satisfy the join conditions. Rows from the dominant table that would otherwise be discarded are preserved, even though no matching row was found in the subservient table. The dominant-table rows that do not have a matching subservient-table row receive a row of nulls before the selected columns are projected.
Simple Join
The following query returns only those rows in which the customer has made a call to customer service.
SELECT c.customer_num, c.lname, c.company, c.phone, u.call_dtime, u.call_descr FROM customer c, cust_calls u WHERE c.customer_num = u.customer_num
Simple Outer Join on Two Tables
The following query uses the same select list, tables, and comparison condition as the preceding example, but this time it creates a simple outer join. The addition of the keyword OUTER in front of the cust_calls table makes it the subservient table. An outer join causes the query to return information on all customers, whether or not they have made calls to customer service. All rows from the dominant customer table are retrieved, and null values are assigned to corresponding rows from the subservient cust_calls table.
SELECT c.customer_num, c.lname, c.company, c.phone, u.call_dtime, u.call_descr FROM customer c, OUTER cust_calls u WHERE c.customer_num = u.customer_num
Outer Join for a Simple Join to a Third Table
The following query shows an outer join that is the result of a simple join to a third table. This second type of outer join is known as a nested simple join.
SELECT c.customer_num, c.lname, o.order_num, i.stock_num, i.manu_code, i.quantity FROM customer c, OUTER (orders o, items i) WHERE c.customer_num = o.customer_num AND o.order_num = i.order_num AND manu_code IN ('KAR', 'SHM') ORDER BY lname
Outer Join for an Outer Join to a Third Table
The following query creates an outer join that is the result of an outer join to a third table. This third type is known as a nested outer join.
SELECT c.customer_num, lname, o.order_num, stock_num, manu_code, quantity FROM customer c, OUTER (orders o, OUTER items i) WHERE c.customer_num = o.customer_num AND o.order_num = i.order_num AND manu_code IN ('KAR', 'SHM') ORDER BY lname
Outer Join of Two Tables to a Third Table
The following query shows an outer join that is the result of an outer join of each of two tables to a third table. In this fourth type of outer join, join relationships are possible only between the dominant table and the subservient tables.
SELECT c.customer_num, lname, o.order_num, order_date, call_dtime FROM customer c, OUTER orders o, OUTER cust_calls x WHERE c.customer_num = o.customer_num AND c.customer_num = x.customer_num ORDER BY lname INTO TEMP service
Subqueries in SELECT Statements
Using ALL
Use the keyword ALL preceding a subquery to determine whether a comparison is true for every value returned. If the subquery returns no values, the search condition is true. (If it returns no values, the condition is true of all the zero values.)
The following query lists the following information for all orders that contain an item for which the total price is less than the total price on every item in order number 1023.
SELECT order_num, stock_num, manu_code, total_price FROM items WHERE total_price < ALL (SELECT total_price FROM items WHERE order_num = 1023)
Using ANY
Use the keyword ANY (or its synonym SOME) preceding a subquery to determine whether a comparison is true for at least one of the values returned. If the subquery returns no values, the search condition is false. (Because no values exist, the condition cannot be true for one of them.)
The following query finds the order number of all orders that contain an item for which the total price is greater than the total price of any one of the items in order number 1005.
SELECT DISTINCT order_num FROM items WHERE total_price > ANY (SELECT total_price FROM items WHERE order_num = 1005)
Single-Valued Subqueries
You do not need to include the keyword ALL or ANY if you know the subquery can return exactly one value to the outer-level query. A subquery that returns exactly one value can be treated like a function. This kind of subquery often uses an aggregate function because aggregate functions always return single values.
The following query uses the aggregate function MAX in a subquery to find the order_num for orders that include the maximum number of volleyball nets.
SELECT order_num FROM items WHERE stock_num = 9 AND quantity = (SELECT MAX (quantity) FROM items WHERE stock_num = 9)
Correlated Subqueries
The following query is an example of a correlated subquery, which returns a list of the 10 earliest shipping dates in the orders table. It includes an ORDER BY clause after the subquery to order the results because you cannot include ORDER BY within a subquery.
SELECT po_num, ship_date FROM orders main WHERE 10 > (SELECT COUNT (DISTINCT ship_date) FROM orders sub WHERE sub.ship_date > main.ship_date) AND ship_date IS NOT NULL ORDER BY ship_date, po_num
Using EXISTS
The keyword EXISTS is known as an existential qualifier because the subquery is true only if the outer SELECT finds at least one row.
SELECT UNIQUE manu_name, lead_time FROM manufact WHERE EXISTS (SELECT * FROM stock WHERE description MATCHES '*shoe*' AND manufact.manu_code = stock.manu_code)
Set Operations
Union
The union operation uses the UNION keyword, or operator, to combine two queries into a single compound query. You can use the UNION keyword between two or more SELECT statements to unite them and produce a temporary table that contains rows that exist in any or all of the original tables.
The following query performs a union on the stock_num and manu_code columns in the stock and items tables.
SELECT DISTINCT stock_num, manu_code FROM stock WHERE unit_price < 25.00 UNION SELECT stock_num, manu_code FROM items WHERE quantity > 3
By default, the UNION keyword excludes duplicate rows. Add the optional keyword ALL, to retain the duplicate values.
SELECT stock_num, manu_code FROM stock WHERE unit_price < 25.00 UNION ALL SELECT stock_num, manu_code FROM items WHERE quantity > 3 ORDER BY 2 INTO TEMP stockitem
Intersection - Keyword EXISTS or IN
The intersection of two sets of rows produces a table containing rows that exist in both the original tables. Use the keyword EXISTS or IN to introduce subqueries that show the intersection of two sets.
The following query is an example of a nested SELECT statement that shows the intersection of the stock and items tables.
SELECT stock_num, manu_code, unit_price FROM stock WHERE stock_num IN (SELECT stock_num FROM items) ORDER BY stock_num
Difference - Keyword NOT EXISTS or NOT IN
The difference between two sets of rows produces a table containing rows in the first set that are not also in the second set. Use the keywords NOT EXISTS or NOT IN to introduce subqueries that show the difference between two sets.
SELECT stock_num, manu_code, unit_price FROM stock WHERE stock_num NOT IN (SELECT stock_num FROM items) ORDER BY stock_num