ORACLE SQLPlus Updating and Modifying Data In SQL

1. ORACLE SQLPlus Updating and Modifying DataIn SQL University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management Database Management -- R. Larson

2. Getting started with ORACLE in SQL-- see assignment on website Migrating databases from ACCESS to ORACLE -- see handouts More on SQL and SQLPlus for data manipulation and modification Today Database Management -- R. Larson

3. SELECT • Syntax: • SELECT [DISTINCT] attr1, attr2,…, attr3 as label, function(xxx), calculation, attr5, attr6 FROM relname1 r1, relname2 r2,… rel3 r3 WHERE condition1 {AND | OR} condition2 ORDER BY attr1 [DESC], attr3 [DESC] Database Management -- R. Larson

4. CREATE SYNONYM • CREATE SYNONYM newname FOR oldname; • CREATE SYNONYM BIOLIFE for ray.BIOLIFE; Database Management -- R. Larson

5. SELECT Conditions • = equal to a particular value • >= greater than or equal to a particular value • > greater than a particular value • <= less than or equal to a particular value • <> not equal to a particular value • LIKE‘%wom_n%’ (Note different wild card) • IN (‘opt1’, ‘opt2’,…,’optn’) Database Management -- R. Larson

6. Aggregate Functions • COUNT(dataitem) • AVG(numbercolumn) • SUM(numbercolumn) • MAX(numbercolumn) • MIN(numbercolumn) • STDDEV(numbercolumn) • VARIANCE(numbercolumn) Database Management -- R. Larson

7. ABS(n) ACOS(n) ASIN(n) ATAN(n) ATAN2(n, m) CEIL(n) COS(n) COSH(n) ROUND(n) SIGN(n) SIN(n) SINH(n) SQRT(n) TAN(n) TANH(n) TRUNC(n[,m]) Numeric Functions • EXP(n) • EXP(n) • FLOOR(n) • LN(n) • LOG(m,n) • MOD(n) • POWER(m,n) Database Management -- R. Larson

8. CHR(n) CONCAT(char1,char2) INITCAP(char) LOWER(char) LPAD(char, n,char2), RPAD(char, n,char2) LTRIM(char, n, cset), RTRIM(char, n, cset) REPLACE(char, srch, repl) SOUNDEX(char) SUBSTR(char, m, n) SUBSTRB(char, m, n) TRANSLATE(char, from, to) UPPER(char) Character Functions returning character values Database Management -- R. Larson

9. ASCII(char) INSTR(char1, char2[,m, n]) INSTRB(char1, char2[,m, n]) LENGTH(char) LENGTHB(char) Character Function returning numeric values Database Management -- R. Larson

10. ADD_MONTHS(dt, n) LAST_DAY(d) MONTHS_BETWEEN(d1, d2) NEW_TIME(d, z1, z2) -- PST, AST, etc. NEXT_DAY(d, dayname) ROUND(d, fmt) -- century, year etc. SYSDATE TRUNC(d, fmt) -- century, year, etc. Date functions Database Management -- R. Larson

11. CHARTOROWID(char) CONVERT(char, dchar, schar) HEXTORAW(char) RAWTOHEX(raw) ROWIDTOCHAR(rowid) TO_CHAR (date, fmt) TO_DATE(char, fmt) TO_NUMBER(char,fmt) TO_MULTIBYTE(char) TO_SINGLE_BYTE(char) Conversion Functions Database Management -- R. Larson

12. Create Table • CREATE TABLE table-name (attr1 attr-type CONSTRAINT constr1 PRIMARY KEY, attr2 attr-type CONSTRAINT constr2 NOT NULL,…, attrM attr-type CONSTRAINT constref REFERENCES owner.tablename(attrname), attrN attr-type CONSTRAINT constrN CHECK (attrN = UPPER(attrN)); • Adds a new table with the specified attributes (and types) to the database. Database Management -- R. Larson

13. Types • VARCHAR2(size) • NUMBER(p, s) • LONG -- long char data • DATE -- from 4712BC to 4714 AD • RAW(size) -- binary • LONG RAW -- large binary • ROWID -- row reference • CHAR(size) -- fixed length characters Database Management -- R. Larson

14. Alter Table • ALTER TABLE table-name ADD COLUMN attr1 attr-type; • ALTER TABLE table-name ADD COLUMN attr1 CONSTRAINT xxx constrainvalue; • ALTER TABLE table-name MODIFY COLUMN attr1 optiontochange; • … DROP COLUMN attr1; • Adds a new column to an existing database table. Database Management -- R. Larson

15. INSERT • INSERT INTO table-name (attr1, attr4, attr5,…, attrK) VALUES (“val1”, val4, val5,…, “valK”); • OR • INSERT INTO table-name SELECT col1, col2, col3as newcol2, col4 FROM xx, yy WHERE where-clause; • Adds a new row(s) to a table. Database Management -- R. Larson

16. DELETE • DELETE FROM table-name WHERE<where clause>; • Removes rows from a table. Database Management -- R. Larson

17. UPDATE • UPDATE tablename SET attr1=newval, attr2 = newval2 WHERE<where clause>; • changes values in existing rows in a table (those that match the WHERE clause). Database Management -- R. Larson

18. DROP Table • DROP TABLE tablename; • Removes a table from the database. Database Management -- R. Larson

19. CREATE INDEX • CREATE [ UNIQUE ] INDEX indexname ON tablename (attr1 [ASC|DESC][, attr2 [ASC|DESC], ...]) • Adds an index on the specified attributes to a table Database Management -- R. Larson

20. System Information In ORACLE • Find all of the tables for a user • SELECT * FROM ALL_CATALOG WHERE OWNER = ‘userid’; • SELECT * FROM USER_CATALOG; (or CAT) • Show the attributes and types of data for a particular table • DESCRIBE tablename; Database Management -- R. Larson

21. Running commands • Create file with SQL and SQLPlus commands in it. • Use a plain text editor and NOT a word processor (or save as text only) • Give the file the extension .sql • From inside SQLPlus type • START filename Database Management -- R. Larson

22. Simple formatting in SQLPlus • SET PAGESIZE 500 • SET LINESIZE 79 • PROMPT stuff to put out to screen • TTITLE “title to put at top of results” • COLUMN col_name HEADING “New Name” Database Management -- R. Larson

23. Outputting results as a file… • SPOOL filename • Commands • SPOOL STOP • File will be created with everything between the SPOOL commands Database Management -- R. Larson