Oracle

This web page contains survival notes on Oracle.  As I get a chance I will place my survival notes here. 

Resources

Download & Install Oracle

Example: Oracle 12c for Windows 64bit - as of 4/22/2014
Note: I didn't see an XE version for 12c.

Windows Services:

My Notes

Terms

Oracle specific SQL Notes

Topic Notes
Oracle SQL Plus Connect to a DB:

connect <username>/<pswd> @<DBName or Connect Identifier>
 
TAB Table TAB Table
  • This table contains a list of all of the tables in a database.
List Tables in a Database List Tables in Database
  • select * from TAB; 
List Structure List the Structure of a Table
  • desc [table name];  //List the schema (structure) of a table.
  • ex: desc TAB;

For Oracle:  (desc TAB;)

TNAME not null varchar2 (30)
TABTYPE not nul varchar2(7)
CLUSTERID number

 

Operators Operators
  • % - wildcard for any number of characters
  • _ - wildcard for one character.
  • like "%CAT%"  //like = having matching characters.
Comment
  • rem - comment
Tables Tables
  • create table mytable1 (myvarchar1 varchar(10) NOT NULL, mydate date NULL)
  • insert into mytable1 (myvarchar1, mydate ) values ('Hello Test', '01-Jan-04')
  • insert into mytable1 (myvarchar1, mydate ) values ('Hello Test 2', sysdate)
  • drop table mytable1;
Temporary Tables Temp Tables
  • create global temporary table tmptable (myvarchar1 varchar(10) NOT NULL) - suppose to work in Oracle 8.1 or greater.  Got this from a friend.
Date Dates
  • sysdate - the current system date.
  • select to_char (mydate, 'yyyymmdd') from mytable1
    Converts a date to 'yyyymmdd' format
Functions Functions
  • substr
Oracle DB Tools - Oracle 12c Start
All Programs
Find Directory: Oracle - OraDB12Home1
Application Development
* Oracle ODBC Help
* Oracle Provider for OLE DB Readme
* SQL Developer
* SQL Plus
Configuration and Migration Tools
*Administration Assistant for Windows
* Database Configuration Assistant
* Database Migration Assistant for Unicode
* Database Upgrade Assistant
* Locale Builder
* Microsoft ODBC Administrator
* Net Configuration Assistant
* Net Manager
* Update Password for Oracle Home User
Integrated Management Tools
* Wallet Manager
Oracle Installation Products
* Universal Installer
 
How to Create A DB - Oracle 12c Launch the "Database Configuration Assistant"
Start, search for: Database Configuration Assistant
Launch
Select: Create Database
Click Next
Screen: Creation Mode
Select the default: Create a database with default configuration
Keep the defaults and add:
* Global Database Name: demo
* Administrative Password: <enter your pswd>
* "<Oracle Home User>": <enter your Oracle home user pswd>
* Check - Create as Container Database
* Pluggable Database Name: pdbdemo
Click Next
Click Finish
Screen: Progress Page
WARNING - wait for up to 5 min for progress bar to start and the screen to be populated so be patient and do not click the button "Close". Also, it may take up to 30 min to create the DB.
Click "Password Management" button to set the passwords for the users:
sys
system
Take note of the values on the screen.
Once the completion bar is 100% then you are finished.
Click "exit". You are finished.

 
Containers & Pluggable DBs - Oracle 12c Terms:
CDB - Container DB
PDB - Pluggable DB - multi-tenancy

alter session set container = <con_name - container name>
SQL> show con_id;
SQL> show con_name
SQL> select name from v$active_services;
 

YouTube resources

* http://www.youtube.com/watch?v=KKi37EIQ5-8

 

dual - testing selects with dual. select sysdate from dual;
results: 26-MAY-14

 

group by group by - see SQL examples

-- ******************************************
-- Display all items in an order for all customers
-- ******************************************
select
cust.custnum,
cust.custname,
ord.ordernum,
to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime"
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM" "CUSTNAME" "ORDERNUM" "orderdatetime"
"CMP001" "AAA Company" "ORD101" "05/26/2014"
"CMP001" "AAA Company" "ORD101" "05/26/2014"
"CMP001" "AAA Company" "ORD101" "05/26/2014"
"CMP001" "AAA Company" "ORD102" "05/27/2014"
"CMP001" "AAA Company" "ORD103" "05/27/2014"
"CMP002" "ABC Company" "ORD201" "05/27/2014"
"CMP003" "XYZ Company" "ORD301" "05/27/2014"
*/

-- ******************************************
-- Business Spec:
-- Display the # of items in an order.
-- ******************************************

-- group by & count added.
select
cust.custnum,
cust.custname,
ord.ordernum,
to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime",
--ord.orderdatetime,
count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM" "CUSTNAME" "ORDERNUM" "orderdatetime" "itemsinorder"
"CMP001" "AAA Company" "ORD101" "05/26/2014" 3
"CMP001" "AAA Company" "ORD102" "05/27/2014" 1
"CMP001" "AAA Company" "ORD103" "05/27/2014" 1
"CMP002" "ABC Company" "ORD201" "05/27/2014" 1
"CMP003" "XYZ Company" "ORD301" "05/27/2014" 1
*/
/*
Results if using ord.orderdatetime vs to_char()
"CUSTNUM" "CUSTNAME" "ORDERNUM" "ORDERDATETIME" "itemsinorder"
"CMP001" "AAA Company" "ORD101" 26-MAY-14 11.21.03.000000000 PM 3
"CMP001" "AAA Company" "ORD102" 27-MAY-14 11.21.03.000000000 PM 1
"CMP001" "AAA Company" "ORD103" 27-MAY-14 11.21.03.000000000 PM 1
"CMP002" "ABC Company" "ORD201" 27-MAY-14 11.21.03.000000000 PM 1
"CMP003" "XYZ Company" "ORD301" 27-MAY-14 11.21.03.000000000 PM 1
*/

-- ******************************************
-- Business Spec:
-- Display the # of items in an order ONLY if the customer has more than 1 order.
-- ******************************************
-- added having clause
select
cust.custnum,
cust.custname,
ord.ordernum,
to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime",
--ord.orderdatetime,
count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
having count(orditm.itemnum) > 1
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM" "CUSTNAME" "ORDERNUM" "orderdatetime" "itemsinorder"
"CMP001" "AAA Company" "ORD101" "05/26/2014" 3
*/