Oracle Database Learning

 

1) field method: varchar2 (50), number, date, integer, char (10)
2) Default Time: oprtime date default sysdate not null; call_time datetime default getdate () not null,
3) create an index: Create index idx_accountchange on account_changehistory (email ASC );
Create unique index idx_voxemail_account on voxemail (voxaccount ASC );
Create index idx_caller_called on DBO. platform_x_info (x_caller, x_called)
4) create a table's primary key: Create Table areatable (
Areacode varchar2 (20) not null,
Areaname varchar2 (20) not null,
Constraint pk_areatable primary key (areacode ))
5) Add a foreign key:
Alter table fax_accountdata
Add constraint fk_fax_acco_reference_faxuser _ foreign key (userclass)
References faxuser_type (userclass)

Create Table DBO. sms_retrec_log
(
Msgid varchar (30) not null,
Smsid numeric (15,0) not null,
Primary Key clustered (msgid ),
Constraint fk_sms_retr_reference_sms_send
Foreign key (smsid)
References DBO. sms_send_record (smsid)
)
6) Automatically increasing table fields: rowid numeric () identity,
7) create a trigger: -- create a trigger
Create or replace trigger insert_corphb
Before insert on corphb for each row
Begin
Select seq_corphb.nextval into: New. userid from dual;
End;

8. Create Table wf_schedule
(
Scheduleid integer not null,
Schedulename varchar2 (100) not null,
Createdate date default sysdate not null,
Constraint pk_wf_schedule primary key (scheduleid)
);

Create sequence wf_schedule_tb_seq
Minvalue 1
Max value 99999999
Start with 281
Increment by 1
Cache 20;

Create or replace trigger insert_wf_schedule
Before insert on wf_schedule for each row
Begin
Select wf_schedule_tb_seq.nextval into: New. scheduleid from dual;
End;