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(<date>, '<format>')
where the <format> 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)
Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is
RR
considered 2006 instead of 1906, for example.
AM (or
Meridian indicator
PM)
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
