Looking for oracle/sql tutorials and advice

#1

Hi

I'm teaching myself oracle and I am looking for some easy to follow
step by step tutorials on oracle and sql. I already found a few good
introductions which cover the basics (and possibly more) but the ones
I have found so far seem to leave a lot of questions. Two things I
would like to learn how to do are:

1. How to 'unnormalise' so data. For example, I have followed a
tutorial which shows how to normalise the contents of a column. So now
the data has been put in a 'lookup' table and replaced with an ID
(corresponding to the ID in the lookup table). How do I get the data
back into the table it originally came from?

2. Working with foreign keys. So far my studied have given me the
impression that this is the key to how relational databases work -
what makes them powerful/useful etc. But the only way I have seen to
access this power is by creating queries, to make a report. But what
about when one wants to add new data? e.g. in the Human Resources
example (which comes with Oracle 10g Express) there is a table for
employees, and also for departments. The department_id column on the
employees table is constrained to the department_id column in the
departments table. Now I want to create an application that displays
the employees table, and also can display a form for adding new
employees. On this form I would like there to be a button, next to
each employee, which brings up the list of departments. From this I
can select the department from this list. Is it possible to do
something like this?

Thanks for any advice.

Michael

#2

On Apr 27, 12:05 am, travel2light <everything2li... (AT) yahoo (DOT) co.uk>
wrote:

Quote:

Hi

I'm teaching myself oracle and I am looking for some easy to follow
step by step tutorials on oracle and sql. I already found a few good
introductions which cover the basics (and possibly more) but the ones
I have found so far seem to leave a lot of questions. Two things I
would like to learn how to do are:

1. How to 'unnormalise' so data. For example, I have followed a
tutorial which shows how to normalise the contents of a column. So now
the data has been put in a 'lookup' table and replaced with an ID
(corresponding to the ID in the lookup table). How do I get the data
back into the table it originally came from?

2. Working with foreign keys. So far my studied have given me the
impression that this is the key to how relational databases work -
what makes them powerful/useful etc. But the only way I have seen to
access this power is by creating queries, to make a report. But what
about when one wants to add new data? e.g. in the Human Resources
example (which comes with Oracle 10g Express) there is a table for
employees, and also for departments. The department_id column on the
employees table is constrained to the department_id column in the
departments table. Now I want to create an application that displays
the employees table, and also can display a form for adding new
employees. On this form I would like there to be a button, next to
each employee, which brings up the list of departments. From this I
can select the department from this list. Is it possible to do
something like this?

Thanks for any advice.

Michael

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

#3

On Apr 27, 12:05 am, travel2light <everything2li... (AT) yahoo (DOT) co.uk>
wrote:

Quote:

Hi

I'm teaching myself oracle and I am looking for some easy to follow
step by step tutorials on oracle and sql. I already found a few good
introductions which cover the basics (and possibly more) but the ones
I have found so far seem to leave a lot of questions. Two things I
would like to learn how to do are:

1. How to 'unnormalise' so data. For example, I have followed a
tutorial which shows how to normalise the contents of a column. So now
the data has been put in a 'lookup' table and replaced with an ID
(corresponding to the ID in the lookup table). How do I get the data
back into the table it originally came from?

2. Working with foreign keys. So far my studied have given me the
impression that this is the key to how relational databases work -
what makes them powerful/useful etc. But the only way I have seen to
access this power is by creating queries, to make a report. But what
about when one wants to add new data? e.g. in the Human Resources
example (which comes with Oracle 10g Express) there is a table for
employees, and also for departments. The department_id column on the
employees table is constrained to the department_id column in the
departments table. Now I want to create an application that displays
the employees table, and also can display a form for adding new
employees. On this form I would like there to be a button, next to
each employee, which brings up the list of departments. From this I
can select the department from this list. Is it possible to do
something like this?

Thanks for any advice.

Michael

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

#4

On Apr 27, 12:05 am, travel2light <everything2li... (AT) yahoo (DOT) co.uk>
wrote:

Quote:

Hi

I'm teaching myself oracle and I am looking for some easy to follow
step by step tutorials on oracle and sql. I already found a few good
introductions which cover the basics (and possibly more) but the ones
I have found so far seem to leave a lot of questions. Two things I
would like to learn how to do are:

1. How to 'unnormalise' so data. For example, I have followed a
tutorial which shows how to normalise the contents of a column. So now
the data has been put in a 'lookup' table and replaced with an ID
(corresponding to the ID in the lookup table). How do I get the data
back into the table it originally came from?

2. Working with foreign keys. So far my studied have given me the
impression that this is the key to how relational databases work -
what makes them powerful/useful etc. But the only way I have seen to
access this power is by creating queries, to make a report. But what
about when one wants to add new data? e.g. in the Human Resources
example (which comes with Oracle 10g Express) there is a table for
employees, and also for departments. The department_id column on the
employees table is constrained to the department_id column in the
departments table. Now I want to create an application that displays
the employees table, and also can display a form for adding new
employees. On this form I would like there to be a button, next to
each employee, which brings up the list of departments. From this I
can select the department from this list. Is it possible to do
something like this?

Thanks for any advice.

Michael

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

#5

On Apr 27, 12:05 am, travel2light <everything2li... (AT) yahoo (DOT) co.uk>
wrote:

Quote:

Hi

I'm teaching myself oracle and I am looking for some easy to follow
step by step tutorials on oracle and sql. I already found a few good
introductions which cover the basics (and possibly more) but the ones
I have found so far seem to leave a lot of questions. Two things I
would like to learn how to do are:

1. How to 'unnormalise' so data. For example, I have followed a
tutorial which shows how to normalise the contents of a column. So now
the data has been put in a 'lookup' table and replaced with an ID
(corresponding to the ID in the lookup table). How do I get the data
back into the table it originally came from?

2. Working with foreign keys. So far my studied have given me the
impression that this is the key to how relational databases work -
what makes them powerful/useful etc. But the only way I have seen to
access this power is by creating queries, to make a report. But what
about when one wants to add new data? e.g. in the Human Resources
example (which comes with Oracle 10g Express) there is a table for
employees, and also for departments. The department_id column on the
employees table is constrained to the department_id column in the
departments table. Now I want to create an application that displays
the employees table, and also can display a form for adding new
employees. On this form I would like there to be a button, next to
each employee, which brings up the list of departments. From this I
can select the department from this list. Is it possible to do
something like this?

Thanks for any advice.

Michael

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

#6

On 27 Apr, 08:28, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:

Quote:

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

I didn't delete the original data, but it was replaced with an ID
corresponding to the data in the lookup table. So for example the
employees table had a column called 'JOB_ID'. What has happened is
that all the JOB_ID's have been automatically moved to a separate
table with a Unique Key - and the JOB_ID column (in the employees
table) was replaced with a new column 'JOB_ID_ID' which has values
corresponding the the Unique Key on the new lookup table. I did this
by mistake (it is a redundancy because there is already a table called
'jobs' which lists all of the Job_ID's and their definitions).

I haven't learnt how to do restorations or reorganising schema design
or flashbacks. Maybe I am jumping ahead of myself.

Quote:

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

I haven't studied Application Express yet so I don't know if that is
what I am looking for. I noticed that oracle will display a button (on
a form) for entering dates. This is the kind of thing I am looking
for.

I'm beginning to realise that I am probably jumping too far ahead of
myself. I think what I really need is a road map of progressive topics
I need to study. Are there any books or tutorials that explain the key
concepts of oracle without going into too much detail?

Michael

#7

On 27 Apr, 08:28, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:

Quote:

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

I didn't delete the original data, but it was replaced with an ID
corresponding to the data in the lookup table. So for example the
employees table had a column called 'JOB_ID'. What has happened is
that all the JOB_ID's have been automatically moved to a separate
table with a Unique Key - and the JOB_ID column (in the employees
table) was replaced with a new column 'JOB_ID_ID' which has values
corresponding the the Unique Key on the new lookup table. I did this
by mistake (it is a redundancy because there is already a table called
'jobs' which lists all of the Job_ID's and their definitions).

I haven't learnt how to do restorations or reorganising schema design
or flashbacks. Maybe I am jumping ahead of myself.

Quote:

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

I haven't studied Application Express yet so I don't know if that is
what I am looking for. I noticed that oracle will display a button (on
a form) for entering dates. This is the kind of thing I am looking
for.

I'm beginning to realise that I am probably jumping too far ahead of
myself. I think what I really need is a road map of progressive topics
I need to study. Are there any books or tutorials that explain the key
concepts of oracle without going into too much detail?

Michael

#8

On 27 Apr, 08:28, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:

Quote:

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

I didn't delete the original data, but it was replaced with an ID
corresponding to the data in the lookup table. So for example the
employees table had a column called 'JOB_ID'. What has happened is
that all the JOB_ID's have been automatically moved to a separate
table with a Unique Key - and the JOB_ID column (in the employees
table) was replaced with a new column 'JOB_ID_ID' which has values
corresponding the the Unique Key on the new lookup table. I did this
by mistake (it is a redundancy because there is already a table called
'jobs' which lists all of the Job_ID's and their definitions).

I haven't learnt how to do restorations or reorganising schema design
or flashbacks. Maybe I am jumping ahead of myself.

Quote:

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

I haven't studied Application Express yet so I don't know if that is
what I am looking for. I noticed that oracle will display a button (on
a form) for entering dates. This is the kind of thing I am looking
for.

I'm beginning to realise that I am probably jumping too far ahead of
myself. I think what I really need is a road map of progressive topics
I need to study. Are there any books or tutorials that explain the key
concepts of oracle without going into too much detail?

Michael

#9

On 27 Apr, 08:28, William Robertson <williamr2... (AT) googlemail (DOT) com>
wrote:

Quote:

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

I didn't delete the original data, but it was replaced with an ID
corresponding to the data in the lookup table. So for example the
employees table had a column called 'JOB_ID'. What has happened is
that all the JOB_ID's have been automatically moved to a separate
table with a Unique Key - and the JOB_ID column (in the employees
table) was replaced with a new column 'JOB_ID_ID' which has values
corresponding the the Unique Key on the new lookup table. I did this
by mistake (it is a redundancy because there is already a table called
'jobs' which lists all of the Job_ID's and their definitions).

I haven't learnt how to do restorations or reorganising schema design
or flashbacks. Maybe I am jumping ahead of myself.

Quote:

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

I haven't studied Application Express yet so I don't know if that is
what I am looking for. I noticed that oracle will display a button (on
a form) for entering dates. This is the kind of thing I am looking
for.

I'm beginning to realise that I am probably jumping too far ahead of
myself. I think what I really need is a road map of progressive topics
I need to study. Are there any books or tutorials that explain the key
concepts of oracle without going into too much detail?

Michael

#10

"travel2light" <everything2light (AT) yahoo (DOT) co.uk> wrote

Quote:

On 27 Apr, 08:28, William Robertson <williamr2... (AT) googlemail (DOT) com
wrote:

1. I'm not sure if you want to reorganise your schema design, move
data between tables (insert ... select) or restore to a point in time
(select ... as of timestamp ..., or flashback table/database though I
don't think XE provides that). Did you delete the original data?

I didn't delete the original data, but it was replaced with an ID
corresponding to the data in the lookup table. So for example the
employees table had a column called 'JOB_ID'. What has happened is
that all the JOB_ID's have been automatically moved to a separate
table with a Unique Key - and the JOB_ID column (in the employees
table) was replaced with a new column 'JOB_ID_ID' which has values
corresponding the the Unique Key on the new lookup table. I did this
by mistake (it is a redundancy because there is already a table called
'jobs' which lists all of the Job_ID's and their definitions).

I haven't learnt how to do restorations or reorganising schema design
or flashbacks. Maybe I am jumping ahead of myself.

2. I'm not sure this has much to do with foreign keys. It sounds like
an Application Express question about how to define a button that pops
up a selection list populated from a query. I'm sure there there's a
way but I'm afraid I don't know Apex.

I haven't studied Application Express yet so I don't know if that is
what I am looking for. I noticed that oracle will display a button (on
a form) for entering dates. This is the kind of thing I am looking
for.

I'm beginning to realise that I am probably jumping too far ahead of
myself. I think what I really need is a road map of progressive topics
I need to study. Are there any books or tutorials that explain the key
concepts of oracle without going into too much detail?

Michael

You can go to otn.oracle.com and they have what are called 2 day dba
tutorials. They are online and self paced and free. Start there.
Jim