SQL Commands You Can Use in Oracle

By Chris Zeis, Chris Ruel, Michael Wessler

Part of Oracle 11g For Dummies Cheat Sheet

SQL is an American National Standards Institute (ANSI) standard for communicating with relational database systems. You can use SQL to communicate with Oracle. Consider SQL to be the language that Oracle will use to communicate what you need. Here are some commands to get you started.

Create a tablespace

CREATE TABLESPACE users 
DATAFILE ‘/u01/oracle/oradata/dev11g/users01.dbf’ 
   size 100M
autoextend on next 10M maxsize 4G;

Set up a user

CREATE USER ldehoff IDENTIFIED BY ‘welcome1’
DEFAULT TABLESPACE users
QUOTA 10m ON users
PASSWORD EXPIRE;
CREATE ROLE developer_role;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE,
   CREATE VIEW to developer_role;
GRANT developer_role TO ldehoff;

Create a table

CREATE TABLE clients (
client_id    NUMBER(4) CONSTRAINT pk_client_id PRIMARY KEY,
last_name    VARCHAR2(128) NOT NULL,
first_name   VARCHAR2(64) NOT NULL,
acct_number  NUMBER(8) CONSTRAINT uk_acct_number UNIQUE,
advisor_id   NUMBER(4) CONSTRAINT fk_advisor_id
   REFERENCES advisors(advisor_id) 
) TABLESPACE users; 

Create a view

CREATE OR REPLACE VIEW client_vw AS
SELECT last_name||’,’||first_name “LAST, FIRST”, client_id
FROM clients;

Use a sequence

INSERT INTO clients
VALUES (client_id.nextval, ‘Schumacher’, ‘Michael’, 1000,
   1234); 

Create an index

CREATE INDEX idx_last_name ON
clients(last_name)
TABLESPACE indexes; 

Create a sequence

CREATE SEQUENCE client_id_seq
INCREMENT BY 10
START WITH 10;