1. creating a sequence
create sequence cust_no_seq
maxvalue 110
start with 100
increment by 1
;
2. creating a table
create table customer
(ac_no number(5),
ac_name varchar2(30),
ac_open_date date,
contact_no number(10),
area varchar2(20)
,constraint ac_no_pk primary key(ac_no)
);
3.creating another table
create table transaction
(
transact_id number(5),
transact_date date,
ac_no number(5) constraint ac_no_fk references customer (ac_no),
transact_type varchar2(1) default 'C',
amount number(7),
constraint transact_id_pk primary key(transact_id)
);
4.using the sequence created
insert into customer values(cust_no_seq.currval,'Kwasi Ofori',sysdate-2,0233333333,'Accra');
5.what to do to start using the sequence
select cust_no_seq.nextval from dual;
6.repeating query 4 above
insert into customer values(cust_no_seq.currval,'Kwasi Ofori',sysdate-2,0233333333,'Accra');
7.select everything from the table
select * from customer;
8.format the columns well
col ac_name format a12
9.run the following scripts
insert into customer values(cust_no_seq.nextval,'Atta Mills',sysdate-289,0236633333,'Tema');
insert into customer values(cust_no_seq.nextval,'K Kumi',sysdate-289,0200633333,'Tema');
insert into customer values(cust_no_seq.nextval,'Alhassan Musa',sysdate-89,0270633333,'Takoradi');
insert into customer values(cust_no_seq.nextval,'Love Listowell',sysdate-89,02698765432,'Accra');
insert into customer values(cust_no_seq.nextval,'Kafui Lands',sysdate-100,02698765444,'Nkawkaw');
insert into customer values(cust_no_seq.nextval,'Akoto Osei',sysdate-88,02444765444,'Kumasi');
insert into customer values(cust_no_seq.nextval,'Moses Anim',sysdate-8,02444700444,'Kumasi');
insert into customer values(cust_no_seq.nextval,'James Okai',sysdate-776,02444707767,'Kumasi');
insert into customer values(cust_no_seq.nextval,'Alhaji Ali',sysdate-776,02777364567,'Takoradi');
insert into customer values(cust_no_seq.nextval,'Mumuni Anim',sysdate-776,02000364567,'Accra');
insert into customer values(cust_no_seq.nextval,'Afua Asiedua',sysdate-776,02888364567,'Nkawkaw');
10.commit and check the information in the customer table
11.alter the sequence cust_no_seq
alter sequence cust_no_seq maxvalue 500;
12.continue with the insert and commit afterwards
insert into customer values(cust_no_seq.nextval,'Afua Asiedua',sysdate-776,02888364567,'Nkawkaw');
13.check the content of the customer table
select * from customer;
14.increase the pagesize to 50
15.check the structure of the customer table and insert into only the ac_no column
desc customer
insert into customer(ac_no) values(cust_no_seq.nextval);
16.check the content of the table
select * from customer;
17.delete the last row you inserted
delete from customer where ac_no=112;
18.let us enforce not null constraints on the rest of the columns
alter table customer modify (ac_name not null);
alter table customer modify (ac_open_date not null);
alter table customer modify (contact_no not null);
alter table customer modify (area not null);
18.let us check whether the newly created constraints is working
insert into customer(ac_no) values(cust_no_seq.nextval);
19.what is your observation and desc the table
20.create another sequence
create sequence transact_id_seq
maxvalue 110
start with 1
increment by 1;
21.what should we do before we can use the sequence
22.let us start inserting into the table transaction
insert into transaction values(transact_id_seq.currval,sysdate,101,'D',300);
insert into transaction values(transact_id_seq.nextval,sysdate-1,102,'C',1300);
insert into transaction values(transact_id_seq.nextval,sysdate-5,106,'X',700);
23.check the content of the table transaction, format the columns where necessary
select * from transaction;
24. adding a new column to the table transaction
alter table transaction add (del_stat varchar2(1));
25. format the columns well
26.how to rename column name
alter table transaction rename column transact_type to type;
27.ASSIGNMENT
I want you to change the data type of contact_no to varchar2(10).
send your result to elvisboateng@gmail.com
28.let us increase the length of the column contact_no
alter table customer modify (contact_no number(11));
29.how to drop a column
alter table customer add (land varchar2(2));
alter table customer drop column land;
30.how to set a column to unused
alter table customer add (land varchar2(10));
alter table customer set unused column land;
I WANT US TO CHANGE THE COLUMN AREA ON THE CUSTOMER TABLE TO AREA_CODE TO REFERENCE ANOTHER PARENT TABLE AREA
31. create the table area
create table area
(
area_code varchar2(10),
name varchar2(30)
);
32.insert into the table area
insert into area values('001','Accra');
insert into area values('002','Tema');
insert into area values('003','Takoradi');
insert into area values('004','Kumasi');
insert into area values('005','Nkawkaw');
commit;
select * from area;
33.change the values in the area column
update customer set area='001' where area='Accra';
update customer set area='002' where area='Tema';
update customer set area='003' where area='Takoradi';
update customer set area='004' where area='Kumasi';
update customer set area='005' where area='Nkawkaw';
select * from customer;
34.how to add contraint to a column already created
alter table area modify area_code primary key;
35 i want to know the name of the constraint
SQL> insert into area values('001','JJ');
36.how to drop a constraint
SQL> alter table area drop constraint SYS_C005468;
37.another way to add a contraint to a column
alter table area add constraint area_code_pk primary key (area_code);
38.let us verify the name again
insert into area values('001','JJ');
39.let us change the area column to area_code
alter table customer rename column area to area_code;
40.let us add another constraint
alter table customer add constraint area_code_fk foreign key(area_code) references area(area_code);
41.let us check whether it is working
update area set area_code='111' where area_code='001';
42.let us test ON DELETE CASCADE
alter table customer drop constraint area_code_fk;
alter table customer add constraint area_code_fk foreign key(area_code) references area on delete cascade;
delete from area where area_code='005';
select * from area;
select * from customer;
43.looking at deferable initially deferred
alter table customer drop constraints area_code_fk;
alter table customer add constraint area_code_fk foreign key(area_code) references area(area_code) deferrable initially deferred;
update area set area_code='111' where area_code='001';
commit;
update area set area_code='111' where area_code='001';
update customer set area_code='111' where area_code='001';
commit;
Commit complete.
44.drop the primary key cascade
alter table area drop constraint area_code_pk;
alter table area drop constraint area_code_pk cascade;
45.let us add the primary key again
alter table area add constraint area_code_pk primary key(area_code);
46.how to disable a constraint
insert into area values ('001','Nkawkaw');
insert into area values ('001','Post');
alter table area disable constraint area_code_pk;
insert into area values ('001','Post');
alter table area enable constraint area_code_pk;
update area set area_code='005' where name='Nkawkaw';
commit;
47.creating a synonym
create synonym cust for customer;
select * from cust;
48.creating a view
create view empvu10
as select employee_id,last_name
from employees where department_id=80;
create or replace view empvu10
as select employee_id,last_name,salary
from employees where department_id=80;
create or replace view empvu10(id,name,sal)
as select employee_id,last_name,salary
from employees where department_id=80;
49.creating complex view
50.Manipulating views
select * from empvu10;
select last_name from employees where employee_id=145;
update empvu10 set name='Elvis' where id=145;
select * from empvu10;
select last_name from employees where employee_id=145;
51.creating read-only views
create or replace view empvu10(id,name,sal)
as select employee_id,last_name,salary
from employees where department_id=80
with read only;
update empvu10 set name='Elvis' where id=145;
delete from empvu10;
52.WITH CHECK OPTION CONSTRAINTS
create or replace view kk10
as select last_name name,employee_id id,department_id d_id from employees
where department_id=20
with check option constraint kk10_ck;
update kk10 set name='Boat' where name='Fay';
update kk10 set id=1 where name='Boat';
alter table employees disable constraint EMP_EMP_ID_PK;
alter table employees disable constraint EMP_EMP_ID_PK cascade;
update kk10 set id=1 where name='Boat';
update kk10 set d_id=1 where name='Boat';
53. CREATING INDEX
Indexes can be created explicitly or automatically
Indexes are logically and physically independent of the table that they index
when you drop a table, corresponding indexes are also dropped
The name of the index is the name that is given to the constraint
You cannot modify indexes.
To change an index, you must dro it and then re-create it.
if you drop a table, indexes and constraints are automatically dropped but views and sequences remain
how to remove NOT NULL
create table index_test as select * from employees;
create index emp_id_idx on index_test(employee_id);
54.alter table customer add (close_status varchar2(1));
55.update customer set close_status='C' where ac_no >110;
56.update customer set close_status='O' where ac_no <111;
57.let us put restriction on the column close_status
alter table customer add constraint close_status_check check(close_status IN ('C','O'));
58.insert into customer values(cust_no_seq.nextval,'Okudzeto Ablakwa',sysdate-789,0288124567,'003','B');
59.create table cust_name_hist
(
ac_no number(5),
ac_name varchar2(30)
);
add constraints
60.create table cust_contact_hist
(
ac_name varchar2(30),
ac_open_date date,
contact_no number(10)
);
add constraints
-UNCONDITIONAL INSERT
61.
insert all
into cust_name_hist values (ac_no,ac_name)
into cust_contact_hist values (ac_name,ac_open_date,contact_no)
select ac_no,ac_name,ac_open_date,contact_no
from customer
where close_status='C';
62.check the contents of cust_name_hist and cust_contact_hist
63.truncate the tables cust_namr_hist anf cust_contact_hist
64.
insert all
into cust_name_hist values (ac_no,ac_name)
into cust_contact_hist values (ac_name,ac_open_date,contact_no)
select ac_no,ac_name,ac_open_date,contact_no
from customer
65.check the contents of cust_name_hist and cust_contact_hist
66.truncate the tables cust_namr_hist anf cust_contact_hist
67.
insert all
into cust_name_hist values (ac_no,ac_name)
into cust_contact_hist values (ac_name,ac_open_date,contact_no)
select *
from customer
68.create table cust_kumasi as select * from cust_name_hist;
69.alter table cust_kumasi add (area_code varchar2(20));
70.create table cust_accra as select * from cust_kumasi;
--CONDITIONAL INSERT ALL
71.
insert all
when area_code='001' then
into cust_accra values(ac_no,ac_name,area_code)
when area='003' then
into cust_kumasi values(ac_no,ac_name,area_code)
select * from customer ;
---PROJECT ON EXTERNAL TABLES
---MERGING
PIVOTING INSERT
WE WILL CONTINUE, I HOPE U ENJOYED THE SHOW
ANY COMMENT, GIVE ME A SHOUT AT elvisboateng@gmail.com
