The SELECT statement is the heart of SQL. It allows you to get the data out of the database and do things with it. When you perform a SELECT against a table or tables the result is compiled into a further temporary table which is displayed to you (or received by your program).
Get all the table data (the basic query)
SELECT * FROM JD11.BOOK
This will provide a result set exactly like the BOOK table. The keyword SELECT tells the parser which function is required. After the SELECT is the column identifier region where you tell the parser which columns you require in the result set, "*" is a special character and means (in this context) ALL COLUMNS. The FROM keyword is mandatory and tells the parser that the next region is the table identifier region. The table identifier region (JD11.BOOK) tells the parser both the schema that owns the table and the table name within that schema. The schema name is not required if the statement is issued when logged in to the JD11 schema or when you have the select right on the JD11.BOOK table granted to you (and no other schema to which you have select access has a table named BOOK). It is good practice to always use (schema name dot table name) notation as it ensures you don't access the wrong table.
Get 2 columns from a table and order them
SELECT ISBN, TITLE FROM JD11.BOOK
ORDER BY ISBN ;
This SELECT statement gets two named columns from a table and orders them.
Note that we use a comma separator to define multiple columns to the parser for the query, if we required only a single column then we would specify only that column name with no commas. The ORDER BY keywords tell the parser that the following region is the ordering list, this is where you specify the fields that will be sorted (by default the sort order is ascending). You do not have to order by a queried column, I could have ordered by Author even though I haven't asked for that column, the data would still be sorted by the Author column. You may sort by as many columns as you want up to the total columns within the queried table(s).
SELECT TITLE BOOK_TITLE, ISBN
FROM JD11.BOOK
ORDER BY ISBN DESC, BOOK_TITLE ;
This select statement demonstrates that the column list order has no special significance, query the columns in a convenient order for you. Note that I have introduced a column alias for the column title, I have renamed it book_title, the purpose of an alias is to give columns a more meaningful name. Once a column alias is declared you can use it elsewhere in the statement (I have used it in the order list). The alias does not rename the actual table column. The order list may have one or more ordering columns specified (comma separated). The order list column name order does have significance, the precedence of ordering runs from first to last named column. The first named column will be sorted and then the second named column will be sorted whilst preserving the sort order of the first named column and so on until the ordering is complete (in this case ordering the title column has no effect on the result set because the isbn column has no common values). The DESC keyword specifies that the ordering is to be descending.
Perform arithmetic on column values as they are queried
SELECT TITLE BOOK_TITLE, ISBN+1 NEW_ISBN
FROM JD11.BOOK
ORDER BY NEW_ISBN ;
In this SELECT statement we have decided to add one to the ISBN number (for some reason) in the result set, note that we have aliased the new_isbn column else it would be headed isbn+1 (not very readable). You should realise that the original isbn table values are unchanged (the new isbn's apply to the result set, not the table). You may add (+), subtract (-), multiply (*) and divide (/). You may use brackets () to force operator precedence otherwise BODMAS rules apply.
SELECT TITLE BOOK_TITLE, ISBN*TIMES_LENT NEW_ISBN
FROM JD11.BOOK
ORDER BY NEW_ISBN ;
This SELECT statement multiplies one column against another (for no good reason). The result set is displayed here even though we wouldn't in reality be able to do this, the SELECT statement would fail because we are trying to multiply using values in a column that are blank (known as NULL). To make this work we must deal with the NULL values.
Perform arithmetic on column values as they are queried (dealing with NULLs)
SELECT TITLE BOOK_TITLE, ISBN*NVL(TIMES_LENT, 1) NEW_ISBN
FROM JD11.BOOK
ORDER BY NEW_ISBN;
The SELECT statement multiplies one column against another (for no good reason) but now we substitute a value into any NULL valued field by using the NVL function. NVL is an Oracle NULL handling implementation - this may be different on other RDBMS platforms. The NVL function forces a value (the second parameter) where the field is NULL (in this case 1). The substituted value must be of the same type as the column. For example :-
NVL(NULLDATE,'21-JAN-90')
NVL(NULLSTR,'NEW STRING')
NVL(NULLNUM,99898)
The parser replaces any NULL value in times_lent with a 1 before doing the calculation and displaying the result set.
SELECT TIMES_LENT FROM JD11.BOOK ;
<><><><><><><>
SELECT NVL(TIMES_LENT,0)
FROM JD11.BOOK ;
<><><><><><><>
The above two SELECT statements show the difference in the result set after using NVL to replace a NULL with some value that adds to the readability of the result set.
Concatenation and Literal Insertion
SELECT ISBN||'-'||TITLE BOOK
FROM JD11.BOOK
ORDER BY ISBN ;
<><><><><><><>
In this SELECT statement we concatenate two fields and a literal together using concatenation symbols "||".
Preventing duplicate row selection
Unless you specify otherwise queries will return result sets without eliminating duplicates.
SELECT SECTION_ID
FROM JD11.BOOK
ORDER BY SECTION_ID ;
<><><><><><><>
To return non duplicate rows use the DISTINCT keyword.
SELECT DISTINCT SECTION_ID
FROM JD11.BOOK
ORDER BY SECTION_ID ;
<><><><><><><>
Slicing and Dicing with the WHERE clause
The WHERE clause enables you to selectively return rows from tables, it has many variations depending upon what you wish to do. The WHERE clause fits in between the table identifier region and any ORDER BY clause. A basic example is given below.
SELECT * FROM JD11.BOOK WHERE ISBN > 100 ORDER BY ISBN
<><><><><><><>
The basic format of a where clause is WHERE keyword, followed by the column (or alias) name for comparison (as with ORDER BY clauses the comparison column name need not be selected as part of the result set), followed by the operator followed by the condition.
The basic operators are "=" (equal to), ">" (greater than), ">=" (greater than or equal to), "<" (less than), "<=" (less than or equal to) or "<>" (not equal to).
The condition is the thing that you are checking against, it must be of the same type as the comparison column. For example :-
WHERE MYDATE = '17-FEB-82'
WHERE MYNUM = 9090
WHERE NAME = 'SMITH' - strings are case sensitive, 'smith' would not be found.
SQL operators in WHERE clause
There are four SQL operators, they work on all data types.
<><><><><>
Some examples are :-
SELECT * FROM
JD11.BOOK
WHERE ISBN BETWEEN 90 AND 120 ORDER BY ISBN
<><><><><>
SELECT * FROM
JD11.BOOK
WHERE SECTION_ID IN (9, 11) ORDER BY SECTION_ID
<><><><><>
The LIKE operator accepts any character string as a condition but also allows "%" wildcards to indicate many / no characters or numbers and "_" (underscore) to indicate a single undefined character at that point.
SELECT * FROM JD11.BOOK WHERE TITLE LIKE 'HELP'
<><><><><>
SELECT * FROM JD11.BOOK WHERE TITLE LIKE '%st%'
<><><><><>
SELECT * FROM JD11.BOOK WHERE TITLE LIKE '_e%'
<><><><><>
The IS NULL operator returns any row with a NULL value in the comparison column.
SELECT * FROM JD11.BOOK WHERE TIMES_LENT IS NULL
<><><><><>
All of these four operators can be negated by placing a NOT keyword in front of them. For example :-
SELECT * FROM JD11.BOOK WHERE ISBN NOT BETWEEN 90 AND 120
<><><><><>
SELECT * FROM JD11.BOOK WHERE SECTION_ID NOT IN (9, 11)
<><><><><>
SELECT * FROM JD11.BOOK WHERE TITLE NOT LIKE 'HELP'
<><><><><>
SELECT * FROM JD11.BOOK WHERE TIMES_LENT IS NOT NULL
<><><><><>
Multiple condition WHERE clause
Multiple condition WHERE clauses are in the format :- WHERE keyword, 1st comparison column, 1st operator, 1st condition, AND/OR operator, 2nd comparison column, 2nd operator, 2nd condition ad infinitum. The AND operator takes precedence unless you force priority to an OR operator with brackets. An example is given below :-
SELECT * FROM JD11.BOOK WHERE SECTION_ID IN (9, 11) AND TIMES_LENT > 10
<><><><><>
Slice the results set by row number
This is Oracle specific (you can use the TOP clause to achieve the same result in a query to MS Access). You may wish to sort a result set and then deal with the top or bottom entries. Oracle internally identifies table rows with a row number, because a result set is a table as well (albeit a temporary one) you can use the row number to limit the returned rows. The row numbers of a result set (unlike a real table) are sequential from 1-n where n is the number of the last row returned by a query. Use the ROWNUM keyword to slice the result set by specifying a fixed number of returned rows.
SELECT TITLE FROM BOOK WHERE ROWNUM <= 5 ORDER BY TITLE
<><><><><>
The above SELECT statement sorts the title column and returns the top five rows.
SELECT TITLE FROM BOOK WHERE ROWNUM <= 5 ORDER BY TITLE DESC
The above SELECT statement sorts the title column and returns the bottom five rows, this is because we have ordered the result set using DESC (descending). If you don’t specify any ordering you will get an arbitrary five rows returned.
Note :- for this to work correctly the column specified by the ORDER BY clause must be indexed. So if the TITLE column wasn't already indexed I would run the command below to create one :-
CREATE INDEX MYINDEX ON JD11.BOOK(TITLE)
Conversion Functions
Functions may be nested together. Nested functions are evaluated from the innermost out.
The To Char Conversion Function
TO_CHAR converts a number / date to a string. The function accepts 2 parameters, the first is the name of a column value or a literal value and the second is a format picture (which consists of one or many defined keywords). The format picture dictates what the result will look like. Some examples follow :-
SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') FROM SYS.DUAL
<><><><><>
SELECT TO_CHAR(SYSDATE, 'FMDay, ddth Month YYYY') FROM SYS.DUAL
<><><><><>
SYSDATE is a system variable always available from an Oracle database which contains the current internal database date / time (of the database server). SYS.DUAL is a dummy table that system variables must be queried from - mainly because this ensures the SELECT syntax is followed.
Both SELECT statements format the result to match the format picture, the second varies in that it uses the FM keyword to strip leading zeros and uses capitalisation correctly.
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM SYS.DUAL
<><><><><>
This statement selects the current database time, note that any characters in the format picture that aren't a defined keyword are treated as a literal and inserted into the result.
SELECT TO_CHAR(COST, 'L99.99') REPLACEMENT_COST FROM JD11.BOOK WHERE COST < 10
<><><><><>
This statement formats a numeric field and displays it as a currency string.
Date format picture keywords.
<><><><><>
Time format picture keywords.
<><><><><>
Other format picture keywords.
<><><><><>
Numeric format picture keywords.
<><><><><>
TO_DATE converts a string (representing a date) to a date. The function accepts 2 parameters, the first is the name of a column value or a literal value and the second is a format picture (which consists of one or many defined keywords). The format picture dictates what the inbound literal or column value looks like. An example follows :-
SELECT * FROM JD11.BOOK WHERE RETURNED_DATE >= TO_DATE('JAN 1 1997', 'MON DD YYYY') ORDER BY ISBN
<><><><><>
TO_NUMBER converts a character string containing digits to a numeric data type, it accepts one parameter which is a column value or a string literal. An example follows :-
SELECT * FROM JD11.BOOK WHERE ISBN = TO_NUMBER('101') ORDER BY ISBN
<><><><><>
The decode function allows you to perform selective substitution of values returned from the database, doing the work of an IF.....THEN.....ELSE structure. In our test table BOOK we have a foreign key that places each book within a particular library section (table SECTION). If we didn't have the SECTION table we might want to use the decode function to map the numeric values as they are queried into something more readable, we could do this with the decode function.
SELECT ISBN, TITLE, DECODE(SECTION_ID, 10, 'Fiction', 5, 'Romance', 6, 'Science Fiction', 7, 'Science', 9, 'Reference', 11, 'Law', 'Unknown') SECTION FROM JD11.BOOK
<><><><><>
Notice that DECODE has substituted the numeric keys with our section names. The parameters of the DECODE function follow this format :- the first parameter is a column name or literal, this is followed by pairs of search / result values. All the search values must be of the same type as the column / literal type. Optionally you may place a final parameter after the search / result pairs that becomes a default result for any value not matched by your search / result pairs.
Group Functions
When you want a result that takes into account a whole table or result set then you use grouping. By default using a group function will group across an entire result set and provide a single result, however, you can use the GROUP BY clause to break the result set into logical groupings and get a result for each group. Examples of full result set group functions follow, to perform these functions on a subset of rows rather than the whole table you simply add a WHERE clause.
SELECT COUNT(*) FROM JD11.BOOK
<><><><><>
The COUNT function tells you how many rows were in the result set.
SELECT AVG(COST) FROM JD11.BOOK
<><><><><>
The AVG function tells you the average value of a numeric column.
SELECT MAX(COST) FROM JD11.BOOK
<><><><><>
SELECT MIN(COST) FROM JD11.BOOK
<><><><><>
The MAX and MIN functions tell you the maximum and minimum value of a numeric column.
SELECT SUM(COST) FROM JD11.BOOK
<><><><><>
The SUM function tells you the sum value of a numeric column.
For the more statistically minded there are also STDDEV (standard deviation) and VARIANCE functions.
Breaking results sets into groups
Use the GROUP BY clause to break result sets into smaller groups. Remember that these functions work across the entire result set and provide a grouped result, you may not query groups and individual row values at the same time. You may group by multiple columns by using the comma separator. Instead of summing and counting the entire set of books we will now group the books by section.
SELECT SECTION_ID, COUNT(*), SUM(COST) FROM JD11.BOOK GROUP BY SECTION_ID ORDER BY SECTION_ID
<><><><><>
When grouping using the GROUP BY clause you cannot use a WHERE clause to restrict the groups that are returned, SQL provides another selection keyword, HAVING, to replace the WHERE clause. You may have multiple conditions on the HAVING clause by using the comma separator. An example of using the HAVING clause follows.
SELECT SECTION_ID, COUNT(*), SUM(COST) FROM JD11.BOOK GROUP BY SECTION_ID HAVING SUM(COST) > 20.00 ORDER BY SECTION_ID
<><><><><>
Using SELECT against more than one table
We can select values from more than one table by using a JOIN, joins come in several types but all are variations on the standard SELECT statement.
The equi join is normally used to join tables across a foreign key link. We specify the join firstly by identifying all the table names within the SELECT statement (rather than the single table we've used up to now) and then specifying the columns that link the two tables together.
SELECT A.TITLE, B.SECTION_NAME FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID = B.SECTION_ID ORDER BY SECTION_NAME, TITLE
<><><><><>
The SELECT statement has joined the BOOK and SECTION tables together before compiling the result set. Note firstly that I have specified the two tables after the FROM keyword and aliased them as A and B. Notice that I have appended the alias name to each column name, for the columns that I wish in the result set (TITLE and SECTION_NAME) this is not strictly required as there is no name clash between the two tables (but it is good practice to do so). The WHERE clause tells the SELECT statement how to join the tables together, in this case by using the foreign key relationship. Because the names of the two fields are the same in both tables you must specify which table you are referring to. You don't have to use aliasing, you can append the table name to each column name if you prefer.
The non-equi join is a join where the link condition is not a direct relationship but is inferred in some fashion, it is any join where the "=" symbol is not the joining condition.
If a row doesn't satisfy a SELECT join condition then it won't appear in the result set . The example query for the equi join demonstrates this. Note that the Science section isn't mentioned because no book is currently assigned to that section. You may want to see all the entries in a table regardless of whether they have linked entries in another table, to do this you use an outer join. Below is the same query again but with an outer join.
SELECT A.TITLE, B.SECTION_NAME FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID (+) = B.SECTION_ID ORDER BY SECTION_NAME, TITLE
<><><><><>
Note the outer join symbol "(+)" is placed on the side of the join that might be missing a corresponding value. A null value has been generated to place against the Science section so that it can be displayed in the result set.
Set Operators
Set operators are INTERSECT, MINUS and UNION. These operators join tables by column rather than by row (like equi, non-equi and outer joins). Each involves two or more SELECT statements which are joined together using a set operator. The UNION joins two result sets togther, the INTERSECT returns the rows that match from both result sets and MINUS returns the rows that don't match from both result sets. Note that all queried columns must be of the same data type(s), all queries must select the same number of columns, column names from the first query appear in the result, you can't use alias names except in the first query, you can only ORDER BY column number not column name and select statements are executed from first to last (not together).
The UNION join compiles all distinct rows into a result set.
SELECT SECTION_ID FROM JD11.BOOK UNION
SELECT SECTION_ID FROM JD11.SECTION
<><><><><>
Note that the returned values have been grouped because only rows distinct in both tables are returned. To return all values without grouping use the ALL qualifier.
SELECT SECTION_ID FROM JD11.BOOK UNION ALL
SELECT SECTION_ID FROM JD11.SECTION
<><><><><>
Now all the rows have been returned (including duplicates). If we want to see only the rows that that match in both queries than we use INTERSECT.
SELECT SECTION_ID FROM JD11.BOOK INTERSECT
SELECT SECTION_ID FROM JD11.SECTION
<><><><><>
Note as there is no section_id value 7 in the BOOK table that row has not been returned. If we want to see only the rows that don't match in both queries then we use MINUS.
SELECT SECTION_ID FROM JD11.SECTION MINUS
SELECT SECTION_ID FROM JD11.BOOK
<><><><><>
Section_id 7 is returned as the only row that doesn't match in both result sets.
Sub Quries
Subqueries are nested SELECT statements where the value of an inner select becomes a WHERE clause conditioning parameter in an outer select. They can replace joins and set operations but they are slower to execute and should be used sparingly (if at all). It is a good way of using grouping functions to return single row values. Below is an example of a single row subquery.
SELECT TITLE FROM JD11.BOOK WHERE COST = (SELECT MAX(COST) FROM JD11.BOOK)
<><><><><>
This query returns the most expensive book. The inner SELECT runs first and passes its result out to the outer SELECT which uses that value when it executes (you can use any permitted maths operator here - it doesn't have to be "="). If we now wanted to find the most expensive book in each section we will need a multi row subquery.
SELECT SECTION_ID, TITLE FROM JD11.BOOK WHERE COST IN (SELECT MAX(COST) FROM JD11.BOOK GROUP BY SECTION_ID)
<><><><><>
The inner SELECT is a simple grouping statement which finds the most expensive book in each library section. It will provide multiple rows to the outer SELECT statement. The outer statement handles this with the IN keyword which replaces the "=" symbol. This requires the outer select to execute for each value that the inner SELECT provides. You could also use the NOT keyword before the IN to only select rows not returned by the inner SELECT.
The above query may fail if the highest book cost in one section equals the cost of another book in another section, this is because a number is returned from the inner query and not the book ISBN. The outer query then matches this number to the book rows and returns the first it finds. A way round this is to return the "group by column" value to differentiate the returned rows.
SELECT SECTION_ID, TITLE FROM JD11.BOOK WHERE (COST, SECTION_ID) IN (SELECT MAX(COST), SECTION_ID FROM JD11.BOOK GROUP BY SECTION_ID)
Many thanks to Hrvoje Djurdjevich from Croatia for pointing out this flaw. This is his solution.
SELECT SECTION_ID, TITLE FROM JD11.BOOK WHERE TO_CHAR(COST,'999999.99')||TO_CHAR(SECTION_ID,'999') IN (SELECT TO_CHAR(MAX(COST),'999999.99')||TO_CHAR(SECTION_ID,'999') FROM JD11.BOOK GROUP BY SECTION_ID)
Queries on Queries
It's possible on Oracle databases (v7.3 and above) to query against the result set of another query. The query below replaces the table identifier section with a bracketed query. The inner query provides its result set via a temporary table and the outer table uses this to perform its query against.
SELECT TITLE, SECTION FROM (
SELECT ISBN, TITLE, DECODE(SECTION_ID, 10, 'Fiction', 5, 'Romance', 6,
'Science Fiction', 7, 'Science', 9, 'Reference', 11, 'Law', 'Unknown')
SECTION FROM BOOK
) WHERE ISBN > 100
<><><><><>
Get all the table data (the basic query)
SELECT * FROM JD11.BOOK
This will provide a result set exactly like the BOOK table. The keyword SELECT tells the parser which function is required. After the SELECT is the column identifier region where you tell the parser which columns you require in the result set, "*" is a special character and means (in this context) ALL COLUMNS. The FROM keyword is mandatory and tells the parser that the next region is the table identifier region. The table identifier region (JD11.BOOK) tells the parser both the schema that owns the table and the table name within that schema. The schema name is not required if the statement is issued when logged in to the JD11 schema or when you have the select right on the JD11.BOOK table granted to you (and no other schema to which you have select access has a table named BOOK). It is good practice to always use (schema name dot table name) notation as it ensures you don't access the wrong table.
Get 2 columns from a table and order them
SELECT ISBN, TITLE FROM JD11.BOOK
ORDER BY ISBN ;
This SELECT statement gets two named columns from a table and orders them.
Note that we use a comma separator to define multiple columns to the parser for the query, if we required only a single column then we would specify only that column name with no commas. The ORDER BY keywords tell the parser that the following region is the ordering list, this is where you specify the fields that will be sorted (by default the sort order is ascending). You do not have to order by a queried column, I could have ordered by Author even though I haven't asked for that column, the data would still be sorted by the Author column. You may sort by as many columns as you want up to the total columns within the queried table(s).
SELECT TITLE BOOK_TITLE, ISBN
FROM JD11.BOOK
ORDER BY ISBN DESC, BOOK_TITLE ;
This select statement demonstrates that the column list order has no special significance, query the columns in a convenient order for you. Note that I have introduced a column alias for the column title, I have renamed it book_title, the purpose of an alias is to give columns a more meaningful name. Once a column alias is declared you can use it elsewhere in the statement (I have used it in the order list). The alias does not rename the actual table column. The order list may have one or more ordering columns specified (comma separated). The order list column name order does have significance, the precedence of ordering runs from first to last named column. The first named column will be sorted and then the second named column will be sorted whilst preserving the sort order of the first named column and so on until the ordering is complete (in this case ordering the title column has no effect on the result set because the isbn column has no common values). The DESC keyword specifies that the ordering is to be descending.
Perform arithmetic on column values as they are queried
SELECT TITLE BOOK_TITLE, ISBN+1 NEW_ISBN
FROM JD11.BOOK
ORDER BY NEW_ISBN ;
In this SELECT statement we have decided to add one to the ISBN number (for some reason) in the result set, note that we have aliased the new_isbn column else it would be headed isbn+1 (not very readable). You should realise that the original isbn table values are unchanged (the new isbn's apply to the result set, not the table). You may add (+), subtract (-), multiply (*) and divide (/). You may use brackets () to force operator precedence otherwise BODMAS rules apply.
SELECT TITLE BOOK_TITLE, ISBN*TIMES_LENT NEW_ISBN
FROM JD11.BOOK
ORDER BY NEW_ISBN ;
This SELECT statement multiplies one column against another (for no good reason). The result set is displayed here even though we wouldn't in reality be able to do this, the SELECT statement would fail because we are trying to multiply using values in a column that are blank (known as NULL). To make this work we must deal with the NULL values.
Perform arithmetic on column values as they are queried (dealing with NULLs)
SELECT TITLE BOOK_TITLE, ISBN*NVL(TIMES_LENT, 1) NEW_ISBN
FROM JD11.BOOK
ORDER BY NEW_ISBN;
The SELECT statement multiplies one column against another (for no good reason) but now we substitute a value into any NULL valued field by using the NVL function. NVL is an Oracle NULL handling implementation - this may be different on other RDBMS platforms. The NVL function forces a value (the second parameter) where the field is NULL (in this case 1). The substituted value must be of the same type as the column. For example :-
NVL(NULLDATE,'21-JAN-90')
NVL(NULLSTR,'NEW STRING')
NVL(NULLNUM,99898)
The parser replaces any NULL value in times_lent with a 1 before doing the calculation and displaying the result set.
SELECT TIMES_LENT FROM JD11.BOOK ;
times_lent | <><><><><><><>
10 | <><><><><><><>
| <><><><><><><> |
1 | <><><><><><><>
19 | <><><><><><><>
98 | <><><><><><><>
12 | <><><><><><><>
| <><><><><><><> |
12 | <><><><><><><>
56 | <><><><><><><>
SELECT NVL(TIMES_LENT,0)
FROM JD11.BOOK ;
nvl(times_lent,0) | <><><><><><><>
10 | <><><><><><><>
0 | <><><><><><><>
1 | <><><><><><><>
19 | <><><><><><><>
98 | <><><><><><><>
12 | <><><><><><><>
0 | <><><><><><><>
12 | <><><><><><><>
56 | <><><><><><><>
The above two SELECT statements show the difference in the result set after using NVL to replace a NULL with some value that adds to the readability of the result set.
Concatenation and Literal Insertion
SELECT ISBN||'-'||TITLE BOOK
FROM JD11.BOOK
ORDER BY ISBN ;
book | <><><><><><><>
8-Over the past again | <><><><><><><>
21-HELP | <><><><><><><>
22-Seven seas | <><><><><><><>
79-Courses for horses | <><><><><><><>
87-Killer Bees | <><><><><><><>
90-Up the creek | <><><><><><><>
91-Dirty steam trains | <><><><><><><>
101-The story of trent | <><><><><><><>
989-Leaning on a tree | <><><><><><><>
In this SELECT statement we concatenate two fields and a literal together using concatenation symbols "||".
Preventing duplicate row selection
Unless you specify otherwise queries will return result sets without eliminating duplicates.
SELECT SECTION_ID
FROM JD11.BOOK
ORDER BY SECTION_ID ;
section_id | <><><><><><><>
5 | <><><><><><><>
6 | <><><><><><><>
9 | <><><><><><><>
9 | <><><><><><><>
9 | <><><><><><><>
9 | <><><><><><><>
10 | <><><><><><><>
10 | <><><><><><><>
11 | <><><><><><><>
To return non duplicate rows use the DISTINCT keyword.
SELECT DISTINCT SECTION_ID
FROM JD11.BOOK
ORDER BY SECTION_ID ;
section_id | <><><><><><><>
5 | <><><><><><><>
6 | <><><><><><><>
9 | <><><><><><><>
10 | <><><><><><><>
11 | <><><><><><><>
Slicing and Dicing with the WHERE clause
The WHERE clause enables you to selectively return rows from tables, it has many variations depending upon what you wish to do. The WHERE clause fits in between the table identifier region and any ORDER BY clause. A basic example is given below.
SELECT * FROM JD11.BOOK WHERE ISBN > 100 ORDER BY ISBN
Isbn | <><><><><><><> title | <><><><><><><> author | <><><><><><><> cost | <><><><><><><> lent_date | <><><><><><><> returned_date | <><><><><><><> times_lent | <><><><><><><> section_id | <><><><><><><>
101 | <><><><><><><> The story of trent | <><><><><><><> T.Wilbury | <><><><><><><> 17.89 | <><><><><><><> 10-JAN-98 | <><><><><><><> 16-JAN-98 | <><><><><><><> 12 | <><><><><><><> 6 | <><><><><><><>
989 | <><><><><><><> Leaning on a tree | <><><><><><><> M.Kilner | <><><><><><><> 19.41 | <><><><><><><> 12-NOV-97 | <><><><><><><> 22-NOV-97 | <><><><><><><> 56 | <><><><><><><> 11 | <><><><><><><>
The basic format of a where clause is WHERE keyword, followed by the column (or alias) name for comparison (as with ORDER BY clauses the comparison column name need not be selected as part of the result set), followed by the operator followed by the condition.
The basic operators are "=" (equal to), ">" (greater than), ">=" (greater than or equal to), "<" (less than), "<=" (less than or equal to) or "<>" (not equal to).
The condition is the thing that you are checking against, it must be of the same type as the comparison column. For example :-
WHERE MYDATE = '17-FEB-82'
WHERE MYNUM = 9090
WHERE NAME = 'SMITH' - strings are case sensitive, 'smith' would not be found.
SQL operators in WHERE clause
There are four SQL operators, they work on all data types.
Operator | <><><><><> Description | <><><><><>
BETWEEN ...AND ... | <><><><><> within two given values | <><><><><>
IN(condition list) | <><><><><> matches any value in the given list | <><><><><>
LIKE | <><><><><> match a string pattern | <><><><><>
IS NULL | is a NULL value | <><><><><>
Some examples are :-
SELECT * FROM
JD11.BOOK
WHERE ISBN BETWEEN 90 AND 120 ORDER BY ISBN
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
90 | <><><><><> Up the creek | <><><><><> K.Klydsy | <><><><><> 15.95 | <><><><><> 15-JAN-97 | <><><><><> 21-JAN-97 | <><><><><> 1 | <><><><><> 10 | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> J.SP.Smith | <><><><><> 8.25 | <><><><><> 14-JAN-98 | <><><><><> <><><><><> | 98 | <><><><><> 9 | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
SELECT * FROM
JD11.BOOK
WHERE SECTION_ID IN (9, 11) ORDER BY SECTION_ID
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
21 | <><><><><> HELP | <><><><><> B.Baker | <><><><><> 20.90 | <><><><><> 20-AUG-97 | <><><><><> <><><><><> | 10 | <><><><><> 9 | <><><><><>
87 | <><><><><> Killer Bees | <><><><><> E.F.Hammond | <><><><><> 29.90 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 9 | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> J.SP.Smith | <><><><><> 8.25 | <><><><><> 14-JAN-98 | <><><><><> <><><><><> | 98 | <><><><><> 9 | <><><><><>
79 | <><><><><> Courses for horses | <><><><><> H.Harriot | <><><><><> 10.34 | <><><><><> 17-JAN-98 | <><><><><> <><><><><> | 12 | <><><><><> 9 | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> M.Kilner | <><><><><> 19.41 | <><><><><> 12-NOV-97 | <><><><><> 22-NOV-97 | <><><><><> 56 | <><><><><> 11 | <><><><><>
The LIKE operator accepts any character string as a condition but also allows "%" wildcards to indicate many / no characters or numbers and "_" (underscore) to indicate a single undefined character at that point.
SELECT * FROM JD11.BOOK WHERE TITLE LIKE 'HELP'
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
21 | <><><><><> HELP | <><><><><> B.Baker | <><><><><> 20.90 | <><><><><> 20-AUG-97 | <><><><><> <><><><><> | 10 | <><><><><> 9 | <><><><><>
SELECT * FROM JD11.BOOK WHERE TITLE LIKE '%st%'
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> J.SP.Smith | <><><><><> 8.25 | <><><><><> 14-JAN-98 | <><><><><> <><><><><> | 98 | <><><><><> 9 | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
8 | <><><><><> Over the past again | <><><><><> K.Jenkins | <><><><><> 19.87 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 10 | <><><><><>
SELECT * FROM JD11.BOOK WHERE TITLE LIKE '_e%'
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
22 | <><><><><> Seven seas | <><><><><> J.J.Jacobs | <><><><><> 16.00 | <><><><><> 21-DEC-97 | <><><><><> <><><><><> | 19 | <><><><><> 5 | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> M.Kilner | <><><><><> 19.41 | <><><><><> 12-NOV-97 | <><><><><> 22-NOV-97 | <><><><><> 56 | <><><><><> 11 | <><><><><>
The IS NULL operator returns any row with a NULL value in the comparison column.
SELECT * FROM JD11.BOOK WHERE TIMES_LENT IS NULL
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
87 | <><><><><> Killer Bees | <><><><><> E.F.Hammond | <><><><><> 29.90 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 9 | <><><><><>
8 | <><><><><> Over the past again | <><><><><> K.Jenkins | <><><><><> 19.87 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 10 | <><><><><>
All of these four operators can be negated by placing a NOT keyword in front of them. For example :-
SELECT * FROM JD11.BOOK WHERE ISBN NOT BETWEEN 90 AND 120
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
21 | <><><><><> HELP | <><><><><> B.Baker | <><><><><> 20.90 | <><><><><> 20-AUG-97 | <><><><><> <><><><><> | 10 | <><><><><> 9 | <><><><><>
87 | <><><><><> Killer Bees | <><><><><> E.F.Hammond | <><><><><> 29.90 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 9 | <><><><><>
22 | <><><><><> Seven seas | <><><><><> J.J.Jacobs | <><><><><> 16.00 | <><><><><> 21-DEC-97 | <><><><><> <><><><><> | 19 | <><><><><> 5 | <><><><><>
8 | <><><><><> Over the past again | <><><><><> K.Jenkins | <><><><><> 19.87 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 10 | <><><><><>
79 | <><><><><> Courses for horses | <><><><><> H.Harriot | <><><><><> 10.34 | <><><><><> 17-JAN-98 | <><><><><> <><><><><> | 12 | <><><><><> 9 | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> M.Kilner | <><><><><> 19.41 | <><><><><> 12-NOV-97 | <><><><><> 22-NOV-97 | <><><><><> 56 | <><><><><> 11 | <><><><><>
SELECT * FROM JD11.BOOK WHERE SECTION_ID NOT IN (9, 11)
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
90 | <><><><><> Up the creek | <><><><><> K.Klydsy | <><><><><> 15.95 | <><><><><> 15-JAN-97 | <><><><><> 21-JAN-97 | <><><><><> 1 | <><><><><> 10 | <><><><><>
22 | <><><><><> Seven seas | <><><><><> J.J.Jacobs | <><><><><> 16.00 | <><><><><> 21-DEC-97 | <><><><><> <><><><><> | 19 | <><><><><> 5 | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
8 | <><><><><> Over the past again | <><><><><> K.Jenkins | <><><><><> 19.87 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 10 | <><><><><>
SELECT * FROM JD11.BOOK WHERE TITLE NOT LIKE 'HELP'
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
87 | <><><><><> Killer Bees | <><><><><> E.F.Hammond | <><><><><> 29.90 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 9 | <><><><><>
90 | <><><><><> Up the creek | <><><><><> K.Klydsy | <><><><><> 15.95 | <><><><><> 15-JAN-97 | <><><><><> 21-JAN-97 | <><><><><> 1 | <><><><><> 10 | <><><><><>
22 | <><><><><> Seven seas | <><><><><> J.J.Jacobs | <><><><><> 16.00 | <><><><><> 21-DEC-97 | <><><><><> <><><><><> | 19 | <><><><><> 5 | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> J.SP.Smith | <><><><><> 8.25 | <><><><><> 14-JAN-98 | <><><><><> <><><><><> | 98 | <><><><><> 9 | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
8 | <><><><><> Over the past again | <><><><><> K.Jenkins | <><><><><> 19.87 | <><><><><> <><><><><> | <><><><><> | <><><><><> | 10 | <><><><><>
79 | <><><><><> Courses for horses | <><><><><> H.Harriot | <><><><><> 10.34 | <><><><><> 17-JAN-98 | <><><><><> <><><><><> | 12 | <><><><><> 9 | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> M.Kilner | <><><><><> 19.41 | <><><><><> 12-NOV-97 | <><><><><> 22-NOV-97 | <><><><><> 56 | <><><><><> 11 | <><><><><>
SELECT * FROM JD11.BOOK WHERE TIMES_LENT IS NOT NULL
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
21 | <><><><><> HELP | <><><><><> B.Baker | <><><><><> 20.90 | <><><><><> 20-AUG-97 | <><><><><> <><><><><> | 10 | <><><><><> 9 | <><><><><>
90 | <><><><><> Up the creek | <><><><><> K.Klydsy | <><><><><> 15.95 | <><><><><> 15-JAN-97 | <><><><><> 21-JAN-97 | <><><><><> 1 | <><><><><> 10 | <><><><><>
22 | <><><><><> Seven seas | <><><><><> J.J.Jacobs | <><><><><> 16.00 | <><><><><> 21-DEC-97 | <><><><><> <><><><><> | 19 | <><><><><> 5 | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> J.SP.Smith | <><><><><> 8.25 | <><><><><> 14-JAN-98 | <><><><><> <><><><><> | 98 | <><><><><> 9 | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
79 | <><><><><> Courses for horses | <><><><><> H.Harriot | <><><><><> 10.34 | <><><><><> 17-JAN-98 | <><><><><> <><><><><> | 12 | <><><><><> 9 | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> M.Kilner | <><><><><> 19.41 | <><><><><> 12-NOV-97 | <><><><><> 22-NOV-97 | <><><><><> 56 | <><><><><> 11 | <><><><><>
Multiple condition WHERE clause
Multiple condition WHERE clauses are in the format :- WHERE keyword, 1st comparison column, 1st operator, 1st condition, AND/OR operator, 2nd comparison column, 2nd operator, 2nd condition ad infinitum. The AND operator takes precedence unless you force priority to an OR operator with brackets. An example is given below :-
SELECT * FROM JD11.BOOK WHERE SECTION_ID IN (9, 11) AND TIMES_LENT > 10
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> J.SP.Smith | <><><><><> 8.25 | <><><><><> 14-JAN-98 | <><><><><> <><><><><> | 98 | <><><><><> 9 | <><><><><>
79 | <><><><><> Courses for horses | <><><><><> H.Harriot | <><><><><> 10.34 | <><><><><> 17-JAN-98 | <><><><><> <><><><><> | 12 | <><><><><> 9 | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> M.Kilner | <><><><><> 19.41 | <><><><><> 12-NOV-97 | <><><><><> 22-NOV-97 | <><><><><> 56 | <><><><><> 11 | <><><><><>
Slice the results set by row number
This is Oracle specific (you can use the TOP clause to achieve the same result in a query to MS Access). You may wish to sort a result set and then deal with the top or bottom entries. Oracle internally identifies table rows with a row number, because a result set is a table as well (albeit a temporary one) you can use the row number to limit the returned rows. The row numbers of a result set (unlike a real table) are sequential from 1-n where n is the number of the last row returned by a query. Use the ROWNUM keyword to slice the result set by specifying a fixed number of returned rows.
SELECT TITLE FROM BOOK WHERE ROWNUM <= 5 ORDER BY TITLE
title | <><><><><>
Courses for horses | <><><><><>
Dirty steam trains | <><><><><>
HELP | <><><><><>
Killer Bees | <><><><><>
Leaning on a tree | <><><><><>
The above SELECT statement sorts the title column and returns the top five rows.
SELECT TITLE FROM BOOK WHERE ROWNUM <= 5 ORDER BY TITLE DESC
The above SELECT statement sorts the title column and returns the bottom five rows, this is because we have ordered the result set using DESC (descending). If you don’t specify any ordering you will get an arbitrary five rows returned.
Note :- for this to work correctly the column specified by the ORDER BY clause must be indexed. So if the TITLE column wasn't already indexed I would run the command below to create one :-
CREATE INDEX MYINDEX ON JD11.BOOK(TITLE)
Conversion Functions
Functions may be nested together. Nested functions are evaluated from the innermost out.
The To Char Conversion Function
TO_CHAR converts a number / date to a string. The function accepts 2 parameters, the first is the name of a column value or a literal value and the second is a format picture (which consists of one or many defined keywords). The format picture dictates what the result will look like. Some examples follow :-
SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') FROM SYS.DUAL
TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') | <><><><><>
TUESDAY, 03RD FEBRUARY 1998 | <><><><><>
SELECT TO_CHAR(SYSDATE, 'FMDay, ddth Month YYYY') FROM SYS.DUAL
TO_CHAR(SYSDATE, 'FMDay, ddth Month YYYY') | <><><><><>
Tuesday, 3rd February 1998 | <><><><><>
SYSDATE is a system variable always available from an Oracle database which contains the current internal database date / time (of the database server). SYS.DUAL is a dummy table that system variables must be queried from - mainly because this ensures the SELECT syntax is followed.
Both SELECT statements format the result to match the format picture, the second varies in that it uses the FM keyword to strip leading zeros and uses capitalisation correctly.
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM SYS.DUAL
TO_CHAR(SYSDATE, 'HH24:MI:SS') | <><><><><>
15:43:21 | <><><><><>
This statement selects the current database time, note that any characters in the format picture that aren't a defined keyword are treated as a literal and inserted into the result.
SELECT TO_CHAR(COST, 'L99.99') REPLACEMENT_COST FROM JD11.BOOK WHERE COST < 10
REPLACEMENT_COST | <><><><><>
£8.25 | <><><><><>
This statement formats a numeric field and displays it as a currency string.
Date format picture keywords.
Picture Keyword | <><><><><> Description | <><><><><>
J | <><><><><> Julian day | <><><><><>
DDD or DD or D | <><><><><> Day of year / month / week | <><><><><>
DAY | <><><><><> Name of day | <><><><><>
DY | <><><><><> Name of day (3 letter abbreviation) | <><><><><>
MM | <><><><><> Month (numeric 1-12) | <><><><><>
MON | <><><><><> Name of month (3 letter abbreviation) | <><><><><>
MONTH | <><><><><> Name of month | <><><><><>
RM | <><><><><> Roman numeral month | <><><><><>
WW or W | <><><><><> Week of year / month | <><><><><>
Q | <><><><><> Quarter of year | <><><><><>
BC or AD | <><><><><> BC / AD indicator | <><><><><>
B.C. or A.D. | <><><><><> BC / AD indicator (with periods) | <><><><><>
SYEAR or YEAR | <><><><><> Year (spelled out), S prefixes BC date with "-" | <><><><><>
SYYYY or YYYY | <><><><><> Year, S prefixes BC date with "-" | <><><><><>
SCC or CC | <><><><><> Century, S prefixes BC date with "-" | <><><><><>
YYY or YY or Y | <><><><><> Last 3, 2 or 1 digit(s) of year | <><><><><>
Y,YYY | <><><><><> Year with comma | <><><><><>
IYYY or IYY or IY or I | <><><><><> 4, 3, 2 or 1 digit year (ISO standard) | <><><><><>
RR | <><><><><> Return date of previous / last century (depends on current date and requested date) | <><><><><>
Time format picture keywords.
Picture Keyword | <><><><><> Description | <><><><><>
AM or PM | <><><><><> Meridian indicator | <><><><><>
A.M. or P.M. | <><><><><> Meridian indicator (with periods) | <><><><><>
HH or HH12 or HH24 | <><><><><> Hour of day, hour (1-12), hour (0-24) | <><><><><>
MI | <><><><><> Minute | <><><><><>
SS | <><><><><> Second | <><><><><>
SSSSS | <><><><><> Seconds past midnight (0-86399) | <><><><><>
Other format picture keywords.
Picture Keyword | <><><><><> Description | <><><><><>
TH | <><><><><> Ordinal numeric, "DDTH" would give 15TH | <><><><><>
SP | <><><><><> Spelled out number, "DDSP" would give FOUR | <><><><><>
SPTH | <><><><><> Spelled out ordinal, "DDSPTH" would give FOURTH | <><><><><>
Numeric format picture keywords.
Picture Keyword | <><><><><> Description | <><><><><>
9 | <><><><><> Numeric position (number of these defines display width) | <><><><><>
V | <><><><><> Multiply by 10 n where n = number of 9's after V | <><><><><>
0 | <><><><><> Display leading zero's | <><><><><>
$ | <><><><><> Floating dollar sign | <><><><><>
L | <><><><><> Floating local currency symbols | <><><><><>
B | <><><><><> Blank leading zeros | <><><><><>
. | <><><><><> Decimal point at this position | <><><><><>
, | <><><><><> Comma at this position | <><><><><>
MI | <><><><><> Minus sign to right of negative numbers | <><><><><>
PR | <><><><><> Parenthesise negative numbers | <><><><><>
EEEE | <><><><><> Scientific notation. | <><><><><>
The TO_DATE conversion function
TO_DATE converts a string (representing a date) to a date. The function accepts 2 parameters, the first is the name of a column value or a literal value and the second is a format picture (which consists of one or many defined keywords). The format picture dictates what the inbound literal or column value looks like. An example follows :-
SELECT * FROM JD11.BOOK WHERE RETURNED_DATE >= TO_DATE('JAN 1 1997', 'MON DD YYYY') ORDER BY ISBN
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
The TO_NUMBER conversion function
TO_NUMBER converts a character string containing digits to a numeric data type, it accepts one parameter which is a column value or a string literal. An example follows :-
SELECT * FROM JD11.BOOK WHERE ISBN = TO_NUMBER('101') ORDER BY ISBN
Isbn | <><><><><> title | <><><><><> author | <><><><><> cost | <><><><><> lent_date | <><><><><> returned_date | <><><><><> times_lent | <><><><><> section_id | <><><><><>
101 | <><><><><> The story of trent | <><><><><> T.Wilbury | <><><><><> 17.89 | <><><><><> 10-JAN-98 | <><><><><> 16-JAN-98 | <><><><><> 12 | <><><><><> 6 | <><><><><>
The Decode Function
The decode function allows you to perform selective substitution of values returned from the database, doing the work of an IF.....THEN.....ELSE structure. In our test table BOOK we have a foreign key that places each book within a particular library section (table SECTION). If we didn't have the SECTION table we might want to use the decode function to map the numeric values as they are queried into something more readable, we could do this with the decode function.
SELECT ISBN, TITLE, DECODE(SECTION_ID, 10, 'Fiction', 5, 'Romance', 6, 'Science Fiction', 7, 'Science', 9, 'Reference', 11, 'Law', 'Unknown') SECTION FROM JD11.BOOK
Isbn | <><><><><> title | <><><><><> section | <><><><><>
21 | <><><><><> HELP | <><><><><> Reference | <><><><><>
87 | <><><><><> Killer Bees | <><><><><> Reference | <><><><><>
90 | <><><><><> Up the creek | <><><><><> Fiction | <><><><><>
22 | <><><><><> Seven seas | <><><><><> Romance | <><><><><>
91 | <><><><><> Dirty steam trains | <><><><><> Reference | <><><><><>
101 | <><><><><> The story of trent | <><><><><> Science Fiction | <><><><><>
8 | <><><><><> Over the past again | <><><><><> Fiction | <><><><><>
79 | <><><><><> Courses for horses | <><><><><> Reference | <><><><><>
989 | <><><><><> Leaning on a tree | <><><><><> Law | <><><><><>
Notice that DECODE has substituted the numeric keys with our section names. The parameters of the DECODE function follow this format :- the first parameter is a column name or literal, this is followed by pairs of search / result values. All the search values must be of the same type as the column / literal type. Optionally you may place a final parameter after the search / result pairs that becomes a default result for any value not matched by your search / result pairs.
Group Functions
When you want a result that takes into account a whole table or result set then you use grouping. By default using a group function will group across an entire result set and provide a single result, however, you can use the GROUP BY clause to break the result set into logical groupings and get a result for each group. Examples of full result set group functions follow, to perform these functions on a subset of rows rather than the whole table you simply add a WHERE clause.
SELECT COUNT(*) FROM JD11.BOOK
COUNT(*) | <><><><><>
9 | <><><><><>
The COUNT function tells you how many rows were in the result set.
SELECT AVG(COST) FROM JD11.BOOK
AVG(COST) | <><><><><>
17.61 | <><><><><>
The AVG function tells you the average value of a numeric column.
SELECT MAX(COST) FROM JD11.BOOK
MAX(COST) | <><><><><>
29.90 | <><><><><>
SELECT MIN(COST) FROM JD11.BOOK
MIN(COST) | <><><><><>
8.25 | <><><><><>
The MAX and MIN functions tell you the maximum and minimum value of a numeric column.
SELECT SUM(COST) FROM JD11.BOOK
SUM(COST) | <><><><><>
158.51 | <><><><><>
The SUM function tells you the sum value of a numeric column.
For the more statistically minded there are also STDDEV (standard deviation) and VARIANCE functions.
Breaking results sets into groups
Use the GROUP BY clause to break result sets into smaller groups. Remember that these functions work across the entire result set and provide a grouped result, you may not query groups and individual row values at the same time. You may group by multiple columns by using the comma separator. Instead of summing and counting the entire set of books we will now group the books by section.
SELECT SECTION_ID, COUNT(*), SUM(COST) FROM JD11.BOOK GROUP BY SECTION_ID ORDER BY SECTION_ID
SECTION_ID | <><><><><> COUNT(*) | <><><><><> SUM(COST) | <><><><><>
5 | <><><><><> 1 | <><><><><> 16.00 | <><><><><>
6 | <><><><><> 1 | <><><><><> 17.89 | <><><><><>
9 | <><><><><> 4 | <><><><><> 69.39 | <><><><><>
10 | <><><><><> 2 | <><><><><> 35.82 | <><><><><>
11 | <><><><><> 1 | <><><><><> 19.41 | <><><><><>
When grouping using the GROUP BY clause you cannot use a WHERE clause to restrict the groups that are returned, SQL provides another selection keyword, HAVING, to replace the WHERE clause. You may have multiple conditions on the HAVING clause by using the comma separator. An example of using the HAVING clause follows.
SELECT SECTION_ID, COUNT(*), SUM(COST) FROM JD11.BOOK GROUP BY SECTION_ID HAVING SUM(COST) > 20.00 ORDER BY SECTION_ID
SECTION_ID | <><><><><> COUNT(*) | <><><><><> SUM(COST) | <><><><><>
9 | <><><><><> 4 | <><><><><> 69.39 | <><><><><>
10 | <><><><><> 2 | <><><><><> 35.82 | <><><><><>
Using SELECT against more than one table
We can select values from more than one table by using a JOIN, joins come in several types but all are variations on the standard SELECT statement.
Equi join
The equi join is normally used to join tables across a foreign key link. We specify the join firstly by identifying all the table names within the SELECT statement (rather than the single table we've used up to now) and then specifying the columns that link the two tables together.
SELECT A.TITLE, B.SECTION_NAME FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID = B.SECTION_ID ORDER BY SECTION_NAME, TITLE
a.title | <><><><><> b.section_name | <><><><><>
Over the past again | <><><><><> Fiction | <><><><><>
Up the creek | <><><><><> Fiction | <><><><><>
Leaning on a tree | <><><><><> Law | <><><><><>
Courses for horses | <><><><><> Reference | <><><><><>
Dirty steam trains | <><><><><> Reference | <><><><><>
HELP | <><><><><> Reference | <><><><><>
Killer Bees | <><><><><> Reference | <><><><><>
Seven seas | <><><><><> Romance | <><><><><>
The story of trent | <><><><><> Science Fiction | <><><><><>
The SELECT statement has joined the BOOK and SECTION tables together before compiling the result set. Note firstly that I have specified the two tables after the FROM keyword and aliased them as A and B. Notice that I have appended the alias name to each column name, for the columns that I wish in the result set (TITLE and SECTION_NAME) this is not strictly required as there is no name clash between the two tables (but it is good practice to do so). The WHERE clause tells the SELECT statement how to join the tables together, in this case by using the foreign key relationship. Because the names of the two fields are the same in both tables you must specify which table you are referring to. You don't have to use aliasing, you can append the table name to each column name if you prefer.
Non-equi join
The non-equi join is a join where the link condition is not a direct relationship but is inferred in some fashion, it is any join where the "=" symbol is not the joining condition.
Outer join
If a row doesn't satisfy a SELECT join condition then it won't appear in the result set . The example query for the equi join demonstrates this. Note that the Science section isn't mentioned because no book is currently assigned to that section. You may want to see all the entries in a table regardless of whether they have linked entries in another table, to do this you use an outer join. Below is the same query again but with an outer join.
SELECT A.TITLE, B.SECTION_NAME FROM JD11.BOOK A, JD11.SECTION B WHERE A.SECTION_ID (+) = B.SECTION_ID ORDER BY SECTION_NAME, TITLE
a.title | <><><><><> b.section_name | <><><><><>
Over the past again | <><><><><> Fiction | <><><><><>
Up the creek | <><><><><> Fiction | <><><><><>
Leaning on a tree | <><><><><> Law | <><><><><>
Courses for horses | <><><><><> Reference | <><><><><>
Dirty steam trains | <><><><><> Reference | <><><><><>
HELP | <><><><><> Reference | <><><><><>
Killer Bees | <><><><><> Reference | <><><><><>
Seven seas | <><><><><> Romance | <><><><><>
| <><><><><> | Science | <><><><><>
The story of trent | <><><><><> Science Fiction | <><><><><>
Note the outer join symbol "(+)" is placed on the side of the join that might be missing a corresponding value. A null value has been generated to place against the Science section so that it can be displayed in the result set.
Set Operators
Set operators are INTERSECT, MINUS and UNION. These operators join tables by column rather than by row (like equi, non-equi and outer joins). Each involves two or more SELECT statements which are joined together using a set operator. The UNION joins two result sets togther, the INTERSECT returns the rows that match from both result sets and MINUS returns the rows that don't match from both result sets. Note that all queried columns must be of the same data type(s), all queries must select the same number of columns, column names from the first query appear in the result, you can't use alias names except in the first query, you can only ORDER BY column number not column name and select statements are executed from first to last (not together).
The UNION join compiles all distinct rows into a result set.
SELECT SECTION_ID FROM JD11.BOOK UNION
SELECT SECTION_ID FROM JD11.SECTION
section_id | <><><><><>
10 | <><><><><>
5 | <><><><><>
6 | <><><><><>
7 | <><><><><>
9 | <><><><><>
11 | <><><><><>
Note that the returned values have been grouped because only rows distinct in both tables are returned. To return all values without grouping use the ALL qualifier.
SELECT SECTION_ID FROM JD11.BOOK UNION ALL
SELECT SECTION_ID FROM JD11.SECTION
section_id | <><><><><>
9 | <><><><><>
9 | <><><><><>
10 | <><><><><>
5 | <><><><><>
9 | <><><><><>
6 | <><><><><>
10 | <><><><><>
9 | <><><><><>
11 | <><><><><>
10 | <><><><><>
5 | <><><><><>
6 | <><><><><>
7 | <><><><><>
9 | <><><><><>
11 | <><><><><>
Now all the rows have been returned (including duplicates). If we want to see only the rows that that match in both queries than we use INTERSECT.
SELECT SECTION_ID FROM JD11.BOOK INTERSECT
SELECT SECTION_ID FROM JD11.SECTION
section_id | <><><><><>
10 | <><><><><>
5 | <><><><><>
6 | <><><><><>
9 | <><><><><>
11 | <><><><><>
Note as there is no section_id value 7 in the BOOK table that row has not been returned. If we want to see only the rows that don't match in both queries then we use MINUS.
SELECT SECTION_ID FROM JD11.SECTION MINUS
SELECT SECTION_ID FROM JD11.BOOK
section_id | <><><><><>
7 | <><><><><>
Section_id 7 is returned as the only row that doesn't match in both result sets.
Sub Quries
Subqueries are nested SELECT statements where the value of an inner select becomes a WHERE clause conditioning parameter in an outer select. They can replace joins and set operations but they are slower to execute and should be used sparingly (if at all). It is a good way of using grouping functions to return single row values. Below is an example of a single row subquery.
SELECT TITLE FROM JD11.BOOK WHERE COST = (SELECT MAX(COST) FROM JD11.BOOK)
title | <><><><><>
Killer Bees | <><><><><>
This query returns the most expensive book. The inner SELECT runs first and passes its result out to the outer SELECT which uses that value when it executes (you can use any permitted maths operator here - it doesn't have to be "="). If we now wanted to find the most expensive book in each section we will need a multi row subquery.
SELECT SECTION_ID, TITLE FROM JD11.BOOK WHERE COST IN (SELECT MAX(COST) FROM JD11.BOOK GROUP BY SECTION_ID)
section_id | <><><><><> title | <><><><><>
9 | <><><><><> Killer Bees | <><><><><>
10 | <><><><><> Over the past again | <><><><><>
5 | <><><><><> Seven seas | <><><><><>
6 | <><><><><> The story of trent | <><><><><>
11 | <><><><><> Leaning on a tree | <><><><><>
The inner SELECT is a simple grouping statement which finds the most expensive book in each library section. It will provide multiple rows to the outer SELECT statement. The outer statement handles this with the IN keyword which replaces the "=" symbol. This requires the outer select to execute for each value that the inner SELECT provides. You could also use the NOT keyword before the IN to only select rows not returned by the inner SELECT.
The above query may fail if the highest book cost in one section equals the cost of another book in another section, this is because a number is returned from the inner query and not the book ISBN. The outer query then matches this number to the book rows and returns the first it finds. A way round this is to return the "group by column" value to differentiate the returned rows.
SELECT SECTION_ID, TITLE FROM JD11.BOOK WHERE (COST, SECTION_ID) IN (SELECT MAX(COST), SECTION_ID FROM JD11.BOOK GROUP BY SECTION_ID)
Many thanks to Hrvoje Djurdjevich from Croatia for pointing out this flaw. This is his solution.
SELECT SECTION_ID, TITLE FROM JD11.BOOK WHERE TO_CHAR(COST,'999999.99')||TO_CHAR(SECTION_ID,'999') IN (SELECT TO_CHAR(MAX(COST),'999999.99')||TO_CHAR(SECTION_ID,'999') FROM JD11.BOOK GROUP BY SECTION_ID)
Queries on Queries
It's possible on Oracle databases (v7.3 and above) to query against the result set of another query. The query below replaces the table identifier section with a bracketed query. The inner query provides its result set via a temporary table and the outer table uses this to perform its query against.
SELECT TITLE, SECTION FROM (
SELECT ISBN, TITLE, DECODE(SECTION_ID, 10, 'Fiction', 5, 'Romance', 6,
'Science Fiction', 7, 'Science', 9, 'Reference', 11, 'Law', 'Unknown')
SECTION FROM BOOK
) WHERE ISBN > 100
title | <><><><><> section | <><><><><>
The story of trent | <><><><><> Science fiction | <><><><><>
Leaning on a tree | <><><><><> Law | <><><><><>
No comments:
Post a Comment