SQL Data Manipulation

Updating data

Insert Command

This command is used to add rows to a table. The format:

INSERT INTO table
Values (list of values separated by commas).

INSERT INTO table
Select statement that retrieves values from another table

INSERT INTO Patron
Values (100, 'Marsha', 'F')

Note that the default format for dates is day-month-year, where month is a three digit character abbreviation. The date should be between single quotes (e.g., 20-JUL-95).

Delete Command

This command removes rows from a table. The format:

DELETE FROM tablename
Where condition

DELETE FROM Patron
Where PID = 100

Update Command

This command changes data in one or more rows

UPDATE tablename
SET attributename = some expression (can be an equation)
Where condition

Note that you can have more than one set statement per UPDATE command.

UPDATE Checkout
SET DueDate = '10-Dec-95'
Where PID = 100

The select query used in Oracle is standard SQL so you should look in your text book for syntax and examples. The following sections illustrate some capabilities or syntax variations in Oracle that are not in the text.

Outer Joins

Unlike Outer Joins in the text, outer joins in Oracle are declared in the Where clause through the use of the (+) symbol. An example follows:

Select b.title, c.pid
    From Book b, Checkout c
    Where b.callno = (+) c.callno

The =(+) says to join the book callno with the checkout callno. But if there is no checkout callno to match book callno, use a null in the checkout attributes. Notice that the above example would be considered a left outer join in our book even though the (+) is to the right of the equal sign.

Special Set Operators

Oracle supports union, intersect and minus set operations on two select statements.

The System Catalog

Oracle keeps the names of your tables and other information in a meta table called user_tables. To see this information, use a simple select statement. The following statement list the tablenames:

Select TABLE_NAME from USER_TABLES;

To find out all the information on your tables, type:

Select * from USER_TABLES;

Group Functions

Oracle supports the following group functions in a SQL query:

AVG -- average
COUNT
MAX -- Returns the maximum value
MIN -- Returns the minimum value
STDDEV -- Standard Deviation
SUM -- The total of some column
VARIANCE

All of the above can be used with the DISTINCT option.

Other Useful Functions

Mathematical Functions

ABS(n)--Absolute value of n
CEIL(n)--Smallest integer greater than or equal to n
EXP(n)--Returns e raised to the nth power
LN(n)--The natural log of n
LOG(m,n)--Returns the logarithm base m of n
MOD(m,n)--The remainder of m divided by n
POWER(m,n)--m raised to the nth power
ROUND(n[,m])--n rounded to m places right of the decimal. m omitted equals 0 places
SIGN(n)-- Returns -1 if n<0, 0 if n=0 and 1 if n>0
SQRT(n)--Square root
TRUNC(n[,m])--n truncated to m decimal places

String Functions

CHR(n)--Returns the character with the binary equivalent to n
CONCAT(char1, char2)--char1 concatenated with char2
INITCAP(char)--Returns char with first letter in uppercase
LOWER(char)--Returns char with all letters in lowercase
LTRIM(char[,set]--Removes characters from the left of char, with initial characters removed up to the first character not in set
NLS_INITCAP(char)--First letter of each word in char is in uppercase
NLS_LOWER(char)--All letters in lower case for every word
NLS_UPPER(char)--All letters in lower case
REPLACE(char,search_string,replacement_string)
RTRIM(char [,set]--Similar to LTRIM, except trims on right
SUBSTR(char,m [,n])--Portion of char, beginning at character m, n characters long. m and n are integers. If n left off it returns all characters to end of char.
UPPER(char)--char with all letters in upper case

String to Number Functions

ASCII(char)--decimal representatin of char
INSTR(char1,char2, n, m)--The position of the mth char2 in char1 beggining from nth position.
LENGTH(char)--The length of char

Date Functions

ADD_MONTHS(d,n)--The date d plus n months
MONTHS_BETWEEN(d1,d2)
SYSDATE--Returns the current date and time

Last modified: September 1, 1998
Dirk Baldwin, MIS, UW-Parkside, dirk.baldwin@uwp.edu