Basic Queries
Using the select command for basic queries and for retrieving data
The select command is the crux of all DML and is used extensively to query and retrieve data from the database. The select command has the following basic syntax; optional clauses are in square brackets:
select [distinct] <comma_separated_column(s)> from <table> [ where <condition1> [and/or] <condition2> ...] [ order by <comma_separated_column(s) [asc/desc]> ]
A simple SELECT query for the books database and results follow:
SQL> select subjectdesc, categoryid from subject; SUBJECTDESC CATEGORYID ------------------------------ ---------- American 1 English 1 French 1 Original Creation 2 Reinterpretation 2 Retelling 2 Adventure 3 Children and Young Adult 3 Crime and Mystery 3 Historical 3 Horror 3 Religious 3 Western 3 Autobiography and Biography 4 Science 4
As you can see, the query just returned the columns subjectdesc and categoryid from the subject table. Columns are returned in the exact order specified.
Now that we have the basic SELECT query under our belts, let us try something new. When we want all the columns in a table to be in the same order they appear within it, there is really no need to specify each column's name in the SQL command. We may use the asterisk '*' symbol instead. This symbol stands for 'all columns' when used in this context.
SQL> select * from category; CATEGORYID CATEGORYDESC ---------- ------------------------------ 1 Drama and Plays 2 Fairy Tales and Folklore 3 Fiction 4 Non Fiction
If we want a list of customers and their addresses in alphabetical order, we could use the ORDER BY clause after the SELECT statement:
SQL> SELECT lastname, firstname, address 2 from customer ORDER BY lastname, firstname; LASTNAME FIRSTNAME ADDRESS -------------------- -------------------- ------------------------------ Ajoob Alea 95, Terrance Place Alan Rita 789, Timm Anshuman Andrea 120, Winter Oak Carreras Bridget 56, West end Chalmers Winston 552, Veritas Way Ferrero Marina 1050, Midland Fraci Agoubi 66A, Filarts Drive Frieze Willard 54, Connaught Road Gerard Chloe 59, Rodgers Av Gomez Monalisa 122, Wentworth Gracias George 122, Ziontrain Harris Mildred 125, Western Road Harris Sid 530, Waters Lane Jeffries Thomas 765, Winola . .
Specifying the letters 'ASC' or nothing at all next to the column name sorts the column in ascending order; and specifying 'DESC' sorts the column in descending order. Sometimes, there is a need to leave out duplicates when we get an answer to a question. For example, let us say that we need to know which subjects are represented in the list of books. Obviously, the book table has as many entries for a subject as there are books on the subject. Ten books may be classified under subject 1, five under subject 2 and so on, in which case 1 will appear ten times and 2 will appear five times. We only want a list of the subjects represented; we don't want to see duplicates.
SQL> select distinct subjectid from book 2 order by subjectid; SUBJECTID ---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 15 rows selected.
We can clearly see that all 15 subjects are represented. Run the same query without using the distinct keyword and see what the results will be.
Say you have a special promotion on books that cost under $30 and you want to send flyers containing just these books. How would you get a list of books that cost under $30? SQL offers the 'WHERE' clause to help you do this.
SQL> SELECT title, price from book WHERE price <= 30 2 order by title; TITLE PRICE ----------------------------------------- ---------- Carrie 21.75 Changeling Sea, The 20.75 Dreamcatcher 27.75 Famous People in History 23.75 Happy Prince, The 22.5 Harry Potter and the Order of the Phoenix 21.75 Heartbreak House 27.5 Henry VIII 27.5 Insect Societies, The 26.25 Life of Huien Tsang 26.99 Lion, The Witch and the Wardrobe, The 26.99 Red Shoes, The 25.75 Respectful Prostitute, The 29.75 Shining, The 27.99 Silver Hoof 26.25 Sphere 21.5 Tempest, The 23.25 Wrinkle in Time, A 25.99 18 rows selected.
Basically, the where clause returns a command to the database to grab all the rows of information where the column price contained a value less than or equal to 30. The select clause may also contain arithmetic expressions or function results and so on. The following command returns the price of discounted order items after application of discount against the orderid. The 'AS' keyword allows us to specify an alias for the computed column. The result of the computation is displayed under the header that appears after the AS keyword.
SQL> select orderid, unitprice*(100-discount)/100 AS FINALPRICE 2 from orderdetails 3 where discount is not null; ORDERID FINALPRICE ---------- ---------- 3 24.75 5 46.575 8 64.5915 9 107.775 12 62.325 14 71.825 15 42.925 19 71.1875 19 69.291 19 42.925 19 100.791 20 23.175 23 33.7875 24 63.225 27 41.8625 15 rows selected.
Several operators and functions may be used for the appropriate datatypes while performing arithmetic operations after the select clause, the where clause and so on. Following are the main Oracle functions as of version 10. You may get more information on these functions by typing the command 'help [topic]' at the SQL> prompt
|
Function |
Description |
|
ABS(n) |
Absolute value of number |
|
ACOS(n) |
arc cosine of n |
|
ADD_MONTHS(date,num_months) |
Returns date + num_months |
|
ALL, ANY |
used if check all rows returned by subquery or at least one satisfies a condition |
|
ASCII(char) |
Converts char into a decimal ascii code |
|
ASIN(n) |
arc sine of n. |
|
ATAN(n) |
arc tangent of n. |
|
ATAN2(n.m) |
arc tangent of n and m. |
|
AVG([DISTINCT]n) |
Average value of 'n' ignoring NULLs. DISTINCT uses duplicate values just once |
|
BETWEEN y AND z |
Where 'x' lies between y AND z |
|
CEIL(n) |
Round n up to next whole number |
|
CHARTOROWID(char) |
Converts a character into a rowid value. |
|
CHR(n) |
Character with value n |
|
CONCAT(s1,s2) |
Concatenate string1 and string2 |
|
CONVERT(char_to_convert, new_char_set, old_char_set) |
Convert character sets |
|
COS(n) |
Cosine of number |
|
COSH(n) |
Hyperbolic Cosine of number |
|
COUNT(*) |
Count the no of rows returned |
|
COUNT([DISTINCT] expr) |
Count the no of rows returned by expr |
|
DECODE( expression, x ,y , x1, y1, x2, y2, z ) |
IF expression equals x THEN return y, if x1 return y1 etc. default: return z |
|
DENSE_RANK |
Calculate the rank of a value in a group |
|
EMPTY_BLOB |
Return an empty LOB locator (use to empty a column or variable) |
|
EMPTY_CLOB |
Return an empty LOB locator (use to empty a column or variable) |
|
EXISTS |
Return TRUE if a subquery returns at least one row |
|
EXP(n) |
Exponential (e to 'n'th power) |
|
FLOOR(n) |
Round n down to the next whole number. |
|
GREATEST(expression, expression...) |
Returns the largest in a list of expressions. |
|
IN(list of comma separated values) |
Effectively a shorthand for ['x' = y OR 'x' = z...] i.e. Where 'x' IN ('horror','adventure','mystery') |
|
INITCAP(char) |
String with Initial Capitals |
|
INSTR(str,chars[,s[,n]]) |
Find the 'n'th occurrence of 'chars' in 'str' Starting at position 's' n and s default to 1 |
|
INSTRB(str,chars[,s[,n]]) |
|
|
IS [NOT] NULL |
Check for NULL (empty) values - Where 'x' IS/IS NOT NULL; |
|
LAST_DAY(date) |
Returns the last day of month in Date |
|
LEAST(expression, expression...) |
Returns the smallest in a list of expressions |
|
LENGTH(char) |
Returns the number of characters in char |
|
LENGTHB(char) |
Returns the number of bytes in char (use for double-byte char sets) |
|
LIKE wildcard/value |
Wildcards - % matches any character any no of times, _ matches any single character; Where col LIKE 'Will%' will find 'William' and 'Williamson; Where col LIKE 'Will_' will find 'Wills' |
|
LN(n) |
Natural Log of n, where n>0 |
|
LOG(b,n) |
log of n, base b |
|
LOWER(char) |
Returns Chars in lowercase |
|
LPAD(char,n[,PadChar]) |
Left Pad char with n spaces [or PadChars] |
|
LTRIM(char[,set]) |
Left Trim char - remove leading spaces [or char set] |
|
MAX([DISTINCT]expr) |
Maximum value returned by expr |
|
MIN([DISTINCT]expr) |
Minimum value returned by expr |
|
MOD(x,y) |
Remainder of x divided by y |
|
MONTHS_BETWEEN(end_date, start_date) |
Number of months between the 2 dates (integer) NEW_TIME(date, zone1, zone2) Convert between GMT and US time zones |
|
NEXT_DAY(date,day_of_week) |
'12-OCT-01','Monday' will return the next Mon after 12 Oct |
|
NVL(expression, value_if_null) |
If expression is null, returns value_if_null ; if expression is not null, returns expression . The arguments can have any datatype (Oracle will perform implicit conversion where needed). |
|
PERCENT_RANK |
Calculate the percent rank of a value in a group. |
|
POWER(m,n) |
m raised to the nth power |
|
RANK |
Calculate the rank of a value in a group |
|
RAWTOHEX(raw) |
Convert raw to a character value containing its hex equivalent |
|
REFTOHEX(ref) |
Convert ref (object type) to a char value containing its hex equivalent. |
|
REPLACE(char,search_str[, replace_str]) |
Replace every occurrence of search_str with replace_str, replace_str defaults to null. |
|
ROUND(n,d) |
n rounded to d decimal places (d defaults to 0) |
|
ROUND(date,fmt) |
date rounded to fmt |
|
ROWIDTOCHAR(rowid) |
Convert a rowid value to VARCHAR2 |
|
ROW_NUMBER |
Assign a unique number to each row of results. |
|
RPAD(char,n[,PadChar]) |
Right Pad char with n spaces [or PadChars] |
|
RTRIM(char[,set]) |
Right Trim char - remove trailing spaces [or char set] |
|
SIGN(n) |
returns 1 if n is +ive, zero if n=0, -1 if n is -ive |
|
SIN(n) |
Sine of n in Radians |
|
SINH(n) |
Hyperbolic Sine of n in Radians |
|
SOUNDEX(char) |
Returns a char value representing the sound of the words 'SOUNDS LIKE'. Use if you expect spelling variations in column values. |
|
SQRT(n) |
Square Root (returns NULL for negative no's) |
|
STDDEV([DISTINCT] n) |
Standard deviation of n |
|
SUBSTR(char,s[,l]) |
A substring of char, starting at character s, length l |
|
SUBSTRB(char,s[,l]) |
A substring of char, starting at character s, length l. The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes, use for double-byte char sets |
|
SUM([DISTINCT] n) |
Sum of values of n, ignoring NULLs |
|
SYS_GUID() |
Returns a globally unique identifier (16 byte RAW value) |
|
SYSDATE |
The current system date & time |
|
TAN(n) |
Tangent of n in Radians |
|
TANH(n) |
Hyperbolic tangent of n in Radians |
|
TO_CHAR |
Convert to character String |
|
TO_DATE |
Convert to date value |
|
TO_MULTI_BYTE(char) |
Convert single-byte char to multi-byte char. |
|
TO_NUMBER |
Convert to numeric format |
|
TO_SINGLE_BYTE(char) |
Convert multi-byte char to single-byte char. |
|
TRANSLATE('char', 'search_str', 'replace_str') |
Replace every occurrence of search_str with replace_str unlike REPLACE() if replace_str is NULL the function returns NULL |
|
TRANSLATE(text USING charset) |
Convert text into a specific character set. Use this instead of CONVERT() if either the input or output datatype is NCHAR or NVARCHAR2. |
|
TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source) |
Returns trim_source as a VARCHAR2 with leading / trailing items removed trim_char defaults to a space ' ' but may be numeric or char 'A' |
|
TRUNC(n,d) |
n truncated to d decimal places (d defaults to 0) |
|
TRUNC(date,fmt) |
date truncated to nearest fmt |
|
UID |
User id - unique number |
|
UPPER(char) |
Returns Chars in uppercase |
|
USER |
Returns the current Username |
|
USERENV('option') |
Can return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO |
|
VALUE(correlation_variable) |
Return the object instance for a row of an object table as associated with the correlation_variable (table alias) |
|
VARIANCE([DISTINCT] n) |
Variance of n, ignoring NULLs |
|
VSIZE(expr) |
Value Size - returns the number of bytes used by each row of expr. |
Following are some important examples that illustrate function use:
SELECT subjectid, nvl(keywords, 'none') AS KEYWORDS from subject;
Returns 'none' if the keywords column contains a null; other wise returns keywords
select replace(title,',',';') as semititle from books;
Returns the books titles with ';' substituted in places where a ',' occurs.
SELECT bookid, unitprice*decode(discount, null, 100, 100 - discount)/100 from orderdetails;
Returns the bookid and price after discount of ordered books. If discount value contains null, regular price is returned; otherwise discounted price is returned
select title, price from book where trunc(price) between 25 and 30;
Returns all the books with cost in range 25.00 - 30.99
The best way to execute built in Oracle functions is to call them through SQL queries. For the sake of uniformity, users may obtain data from Oracle only through select statements even when the returned data is not from a database. The pseudo 'DUAL' table is an Oracle table that contains just one row and column; this is initialized to contain values computed on the fly. The dual table may be used to return function or expression results.
SQL> select abs(sinh(12.5)) from dual; ABS(SINH(12.5)) --------------- 134168.643 SQL> select current_date from dual; CURRENT_D --------- 11-Dec-06 USER ------------------------------ BOOKSDB SQL> select current_date from dual;
The following is a list of operators used in ‘where’ clauses and descriptions:
|
Operator |
Description |
Example |
|
= |
All data that Equals value |
SELECT * from book WHERE authorid=119; |
|
<> |
All data that does not equal value |
SELECT * from category WHERE categoryid != 4; |
|
> |
All data that is Greater than the value |
SELECT * from book WHERE pubdate > '12-Nov-2000'; |
|
< |
All data that is less than the value |
SELECT * from custorder WHERE orderdate < '1-Jan-2006'; |
|
>= |
All Data that is greater than or Equal to the value |
SELECT * from book WHERE pubdate >= '12-Nov-2000'; |
|
<= |
All data that is Less than or equal to the value |
SELECT * from custorder WHERE orderdate <= '1-Jan-2006'; |
|
AND, OR |
Used to Separate values being compared |
SELECT * from book WHERE subjectid = 5 OR subjectid = 10; |
|
BETWEEN |
Between two values including those two values |
SELECT * FROM book WHERE pubdate Between '31-Dec-1980' And '31-Dec-1990'; |
|
LIKE |
Used to search for a string of alphabets, numbers, or other characters |
SELECT * from book WHERE lastname LIKE 'Shakespeare' OR firstname LIKE 'William'; |
|
IN |
Used to search for a set of values |
SELECT * from books WHERE subjectid IN (1,5,10,15); |
The WHERE clause is one of the most useful features of the language. Try out all the examples in the above table in the SQL plus command window and see the results. The equals, less than and greater than signs, and between keywords are used mainly for numbers and dates while the LIKE clause is used for comparing words and other character strings. We can use the '=' operator for comparing short strings but using LIKE makes more sense. Quotes should only be used for strings and dates and never for numbers.
Most of the time, we only know part of the string we are searching for. Can SQL get us information about such strings? The '%' keyword is used as a wild card in matching strings in SQL. How would we search for all books that begin with an 'A'?
Select * from book where title LIKE 'A%'; Select * from book where title like 'Harry Potter%';
Another wildcard is the '_' underscore. While the % sign matches any number of characters, the '_' matches just one. The following command would return all books that start with two letter words with a leading 'A'. We match the 'A' followed by any character followed by a space and any sequence.
Select * from book where title LIKE 'A_ %';
Starting with version 10g, Oracle supports a powerful regular expressions package that allows users to search for intricate text patterns.
