1 / 23

ORACLE SQL and SQL_PLUS

Learn Oracle SQL fundamentals at University of California, Berkeley. Get started with data manipulation and modification using SQL and SQLPlus. Understand syntax, SELECT queries, conditions, functions, and table creation. Practice running commands, formatting output, and handling system information in Oracle.

perrye
Download Presentation

ORACLE SQL and SQL_PLUS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ORACLE SQL and SQL_PLUS University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management

  2. Getting started with ORACLE in SQL-- see assignment on website More on SQL and SQLPlus for data manipulation and modification Getting Started with ORACLE. Today

  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]

  4. CREATE SYNONYM • CREATE SYNONYM newname FOR oldname; • CREATE SYNONYM BIOLIFE for ray.BIOLIFE;

  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’)

  6. Aggregate Functions • COUNT(dataitem) • AVG(numbercolumn) • SUM(numbercolumn) • MAX(numbercolumn) • MIN(numbercolumn) • STDDEV(numbercolumn) • VARIANCE(numbercolumn)

  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)

  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

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

  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

  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

  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.

  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

  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.

  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.

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

  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).

  18. DROP Table • DROP TABLE tablename; • Removes a table from the database.

  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

  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;

  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

  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”

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

More Related