Oracle Essentials

Courtesy of Rahul Tikekar

1. Find location of and something about compiler errors:

show errors;

2. Datatypes:

1. char (n)
2. varchar2(n)
3. date: format DD-MMM-YYYY (e.g., '12-Jan-1995')
4. number
5. number(n,m): e.g., number(6,2): total 6 digits, 2 decimal places

3. Creating/deleting/updating tables

1. create table e.g., Emp (EID, EName, EDOB, EDept)

create table Emp

(EID number not null,

EName varchar2(20),

EDOB date,

EDept varchar2(10));

2. drop table Emp: will drop the table
3. deleting rows

delete Emp where EID=1;

4. truncate table Emp: will delete all rows
5. inserting values into tables

insert into Emp values (1, 'Dave Letterman', ’12-Apr-1949’, 'Late Night');

6. updating table values

update Emp set EDOB=’12-Apr-1942’ where EID=1;

4. Adding/dropping/enabling/disabling constraints: use alter table

1. primary key

alter table Emp add constraint Emp_PK primary key (EID);

2. foreign key

alter table Emp add constraint Emp_FK1

foreign key (EDept) references Dept (DeptName) [on delete cascade];

3. check

alter table Emp add constraint Emp_Check1 check (Eage > 15 AND EAge < 85);

4. alter table Emp enable/disable constraint Emp_Check1;
5. alter table Emp drop constraint Emp_Check1;

5. Sequences

1. Create sequence EmpSeq

Increment by 1

Start with 101

Nocycle;

2. Insert into Emp values (EmpSeq.nextval, 'Dave Letterman', 49, 'Late Night');

6. Changing column titles

Select EID "Employee ID" from Emp;

7. Wildcards: % and _

Select EAge from Emp where EName like ‘%L_’;

8. Objects: Define type, then define methods

Create type Emp as object ( …)

9. Looking to see what tables you have created and the constraints on them

Select table_name from user_tables;

Select constraint_name, table_name, constraint_type, status from user_constraints;

10. Listing the specification for a table

Describe Emp;