Create User

CREATE USER username IDENTIFIED BY password; ALTER USER username IDENTIFIED BY new_password; DROP USER username [CASCADE] Specify CASCADE to drop all objects in the user's schema. List all usernames SELECT NAME FROM SYS.USER$ GRANT privilege1, privilege2, ... [ON object_name] TO user1, user2, ...; REVOKE privilege1, privilege2, ... [ON object_name] FROM username; Privileges: •CREATE SESSION •CREATE TABLE •DROP TABLE •UNLIMITED TABLESPACE •CREATE USER •GRANT ANY PRIVILEGE •CREATE ANY TABLE •DROP ANY TABLE GRANT privilege TO user WITH ADMIN OPTION; (Allows user to grant privilege ) Managing Tablespace - You may/should create tablespace for each user so that they do not need to share the same file space. Example tablespace script. CREATE TABLESPACE lhoward DATAFILE 'C:\oracle\lhoward\lhoward.dbf' SIZE 30M; CREATE USER lhoward IDENTIFIED BY abcd DEFAULT TABLESPACE lhoward QUOTA UNLIMITED ON lhoward; GRANT CONNECT, RESOURCE TO lhoward; REVOKE UNLIMITED TABLESPACE FROM lhoward; GRANT CREATE SESSION, CREATE TABLE TO lhoward; Defining Data Data Types:

•CHAR •VARCHAR2 •NCHAR •NUMBER •DATE •BLOB •CLOB •BFILE •NCLOB Basic Column Definition column_definition -> field_name data_type_definition, Contraint Types •Primary Key •Foreign Key •Check Condition •Not Null •Unique Defining Primary Key Constraints. CONTRAINT contraint_name PRIMARY KEY [used within a column declaration] CONSTRAINT contraint_name PRIMARY KEY (fieldname) [used after column declarations] Defining Foreign Key Constaints CONSTRAINT foreign_key_id FOREIGN KEY (foreign_key_field) REFERENCES table_name(field_name) Table Management CREATE TABLE table_name column_definition1, column definition2, ... column_definitionN contraint_definitions; DESCRIBE table_name; List all tables and their owners SELECT owner, table_name FROM sys.all_tables; DROP TABLE tablename [CASCADE CONSTRAINTS];

Add a field to an existing table ALTER TABLE table_name ADD(fieldname_data_declaration constraint_definitions); Modify existing field definition ALTER TABLE table_name MODIFY(fieldname_data_declaration); Delete a field from a table ALTER TABLE table_name DROP COLUMN fieldname; Disable and Reenable Constraint ALTER table_name DISABLE CONSTRAINT constraint_name; ALTER table_name ENABLE CONSTRAINT constraint_name; Create a sequence CREATE SEQUENCE sequence_name [INCREMENT BY number] [START WITH start_value] [MAXVALUE max_value] [MINVALUE min_value] [CYCLE] [ORDER] Drop a Sequence DROP SEQUENCE sequence_name; Pseudocolumns CURRVAL

Most recent sequence number

NEXTVAL

Next available sequence number

SYSDATE

Current system date and time from DUAL table

USER

Current user from DUAL table

Managing Data Add a new record to a table (references all columns) INSERT INTO table_name VALUES(col1_value, col2_value, ...); Alternate syntax for adding new record (puts data only into names columns) INSERT INTO table_name (col-x_name, col-y_name, ...) VALUES(col-x_value, col-y_value, ...); Insert a record into a table utilyzing a sequence INSERT INTO table_name VALUES(sequence_name.NEXTVAL, col1_value, col2_value, ...); Change a field's value in one or more records UPDATE table_name SET column1 = new_value1, column2 = new_value2, ...

WHERE record_retrieval_conditions; Delete one or more records from a table DELETE FROM table_name WHERE record_retrieval_conditions; Remove all records from a table without saving rollback information TRUNCATE TABLE table_name; Retrieving Data Special Tables •Objects •Tables •Indexes •Views •Sequences •Users •Constraints •Cons_Columns •Ind_Columns •Tab_Columns •DUAL Basic Retrieval from single table SELECT [DISTINCT] display_fields FROM tables WHERE field_conditions ORDER BY field_name [ASC | DESC]; Basic Join SELECT display_fields FROM table1, table2 WHERE table1_foreign_key=table2_primary_key AND other_field_conditions; Basic Group Retrieval from single table SELECT group_field, group_functions FROM tables WHERE field_conditions ORDER BY field_name [ASC | DESC] GROUP BY group_field HAVING condition_on_group; Numeric Functions

Convert a date string into an internal date where date_format specifies format of string date. TO_DATE(string_date, date_format) Return the mod of number in the indicated base MOD(number, base) Raise number to the indicated exponent POWER(number, exponent) Round number to the indicated precision (number of decimal places) ROUND(number precision) Truncate number to the indicated precision (number of decimal places) TRUNC(number, precision) More numeric functions ABS

Absolute value

CEIL

Ceiling

FLOOR

Floor

SIGN

Sign of a number

SQRT

Square Root

Aggregate numeric functions AVG

Average value of field

COUNT

Number of records returned

MAX

Maximum value in field in returned records

MIN

Maximum value in field in returned records

SUM

Sum of values in field

String Functions CONCAT(string1, string2) LPAD(string, num_chars, pad_char) RPAD(string, num_chars, pad_char) LTRIM(string, search_string) RTRIM(string, search_string) REPLACE(string, search_string, replacement_string) SUBSTR(string, start_posn, length) More string functions INITCAP(string)

Capitalize first character

LENGTH(string)

Length of string

UPPER(string)

Convert all chars to uppercase

LOWER(string)

Convert all chars to lowercase

Date Functions ADD_Months(date, num_months_to_add) LAST_DAY(date) - Last day of month as date MONTHS_BETWEEN(date1, date2) - returns decimal difference in months

= > SELECT ID, Start_Date, LAST_DAY(Start_Date) AS FirstPayment FROM Employee; => CREATE TABLE copy_of_employee AS SELECT * FROM employee WHERE 3=5; = > INSERT INTO emp_department_1 SELECT * FROM temp; CREATE TABLE temp AS SELECT fname, minit, lname, ssn, bdate, address, sex, superssn, dno FROM employee; DROP TABLE employee; CREATE TABLE employee AS SELECT * FROM temp;

Retrieving special values from DUAL SELECT sequence_name.CURRVAL, sequence_name.NEXTVAL, SYSDATE, USER FROM DUAL; Special Search Keywords and Symbols LIKE

Wildcard match verb

_

single character wildcard

%

multiple character wildcard

NULL

matches null values

The general usage of TO_CHAR is: TO_CHAR(, '')

where the string can be formed from over 40 options. Some of the more popular ones include: MM

Numeric month (e.g., 07)

MON

Abbreviated month name (e.g., JUL)

MONTH

Full month name (e.g., JULY)

DD

Day of month (e.g., 24)

DY

Abbreviated name of day (e.g., FRI)

YYYY

4-digit year (e.g., 1998)

YY

Last 2 digits of the year (e.g., 98)

RR

Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is

considered 2006 instead of 1906, for example. AM (or PM)

Meridian indicator

HH

Hour of day (1-12)

HH24

Hour of day (0-23)

MI

Minute (0-59)

SS

Second (0-59)

select sysdate from dual select to_char(MONTHS_BETWEEN('01-mar-2009', '01-jan-2009') )from dual select to_char(sysdate,'MON/YYYY') from dual select to_char(sysdate,'MM') from dual select to_char(sysdate,'MONTH') from dual select to_char(sysdate,'DD') from dual select to_char(sysdate,'YYYY') from dual select to_char(sysdate,'YY') from dual select to_char(sysdate,'RR') from dual select to_char(sysdate,'PM') from dual select to_char(sysdate,'HH') from dual select to_char(sysdate,'HH24') from dual select to_char(sysdate,'MI') from dual select to_char(sysdate,'SS') from dual insert into x values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')); SELECT owner, table_name FROM sys.all_tables