Re: How to use SELECT to query this kind of table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 06 Jan 2000 12:16:15 -0500
Message-ID: <5ej97s8t0hrfp6u7ek037o620od1tvka6d@4ax.com>

A copy of this was sent to ppakorn_at_my-deja.com (if that email address didn't require changing) On Thu, 06 Jan 2000 16:30:20 GMT, you wrote:

>I have a table named student as below
>
>create type name_o as object (
>first varchar2(10),
>middle varchar2(10),
>last varchar2(15));
>/
>
>create type address_o as object (
>street varchar2(15),
>city varchar2(15),
>state char(2),
>zip char(5));
>/
>
>create table student(
>sid char(9) not null,
>name name_o,
>address address_o,
>telnum varchar2(11),
>primary key (sid));
>
>I know only how to insert information into this table, but have no idea
>how to query (SELECT statement) such as showing first name of student
>who live in NY. How can I do that? Actually I don't know how to do the
>updating (UPDATE TABLE statement)too. Please help me.
>
>

here are some examples using your model above:

tkyte_at_8.0> insert into student values
  2 ( '123456789', name_o( 'Thomas', 'J', 'Kyte' ),

  3                             address_o( '1910 Oracle', 'Reston', 'VA',
'19210' ),
  4                             '123-4567' );

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select *
  2 from student;

SID NAME(FIRST, MIDDLE, LAST)

--------- ------------------------------

ADDRESS(STREET, CITY, STATE, ZIP)

---

TELNUM

---

123456789 NAME_O('Thomas', 'J', 'Kyte') ADDRESS_O('1910 Oracle', 'Reston', 'VA', '19210') 123-4567

tkyte_at_8.0>
tkyte_at_8.0> select s.name.first from student s;

NAME.FIRST

---

Thomas

tkyte_at_8.0>
tkyte_at_8.0> update student s
  2 set s.name = name_o( 'Tom', '', 'Kyte' );

1 row updated.

tkyte_at_8.0>
tkyte_at_8.0> select s.name.first, s.name from student s;

NAME.FIRST NAME(FIRST, MIDDLE, LAST)

---------- ------------------------------
Tom        NAME_O('Tom', NULL, 'Kyte')

tkyte_at_8.0>
tkyte_at_8.0> update student s
  2 set s.name.first = 'Thomas';

1 row updated.

tkyte_at_8.0>
tkyte_at_8.0> select s.name.first, s.name from student s;

NAME.FIRST NAME(FIRST, MIDDLE, LAST)

---------- ------------------------------
Thomas     NAME_O('Thomas', NULL, 'Kyte')

>Thank you in advance
>Pak
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 06 2000 - 11:16:15 CST