Database basic

Create table space

create tablespace tbs_yasir

datafile 'C:\oraclexe\oradata\XE\yasir_01.dbf'

size 50m;

Create Temp table space

CREATE TEMPORARY TABLESPACE tbs_temp_yasir

TEMPFILE  'C:\oraclexe\oradata\XE\tbs_temp_yasir.dbf' 

SIZE 5M AUTOEXTEND ON;

CREATE A NEW USER IN ORACLE

CREATE USER yasir

  IDENTIFIED BY yasir123

  DEFAULT TABLESPACE tbs_yasir

  TEMPORARY TABLESPACE   tbs_temp_yasir  QUOTA 20M on tbs_yasir;

ASSIGN SYSTEM PRIVILEGES TO NEW USER IN ORACLE

GRANT create session TO yasir;

GRANT create table TO yasir;

GRANT create view TO yasir;

GRANT create any trigger TO yasir;

GRANT create any procedure TO yasir;

GRANT create sequence TO yasir;

GRANT create synonym TO yasir;

CREATE OBJECTS IN THE SCHEMA

The following is a CREATE SCHEMA statement (creating one table within the schema):

CREATE SCHEMA AUTHORIZATION yasir

    CREATE TABLE suppliers

( supplier_id number(10) not null,

  supplier_name varchar2(50) not null,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10)

);

CREATE SYNONYMS FOR OBJECTS

You may want to create synonyms so that other schemas can access the new database objects 

(ie: tables) without having to prefix the object names with the schema name.

For example, if you were another user named yasir and wanted to select from the suppliers 

table in new_schema, you would have to run the following SELECT statement (before any 

synonyms are created):

SELECT * 

FROM new_schema.suppliers;

If you then created a synonym for the suppliers table as follows:

CREATE PUBLIC SYNONYM suppliers_yasir

FOR new_schema.suppliers;

You could run the SELECT statement as follows:

SELECT * 

FROM suppliers;

No longer needing to prefix the table name with the schema name.

SOLUTION FOR PRACTICE EXERCISE #1:

CREATE TABLE SUPPLIERS

( SUPPLIER_ID NUMBER(10) NOT NULL,

  SUPPLIER_NAME VARCHAR2(50) NOT NULL,

  ADDRESS VARCHAR2(50),

  CITY VARCHAR2(50),

  STATE VARCHAR2(25),

  ZIP_CODE VARCHAR2(10)

);

PRACTICE EXERCISE #2:

Create an Oracle table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

SOLUTION FOR PRACTICE EXERCISE #2:

The Oracle CREATE TABLE statement for the customers table is:

CREATE TABLE customers

( customer_id number(10) not null,

  customer_name varchar2(50) not null,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10),

  CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

SOLUTION FOR PRACTICE EXERCISE #3:

CREATE TABLE DEPARTMENTS(
DEPARTMENT_ID NUMBER(10));

The Oracle CREATE TABLE statement for the employees table is:

CREATE TABLE employees

( employee_number number(10) not null,

  employee_name varchar2(50) not null,

  department_id number(10),

  salary number(6),

  CONSTRAINT employees_pk PRIMARY KEY (employee_number),

  CONSTRAINT fk_departments

    FOREIGN KEY (department_id)

    REFERENCES departments(department_id)

);