 need help for oracle sql [message #635707] |
Tue, 07 April 2015 04:00  |
 |
hivwood
Messages: 7
Registered: April 2015
Location: CMB
|
Junior Member |
|
|
I am not good at for oracle sql. I need to fix my code. That is the problem. i given my answer. I want to check my code is correct or not. please help me.
Consider a type named student_type that has attributes, (sid: char(8), sname: varchar(15), phone: char(10)).
Let ug_type be a subtype of student_type with attributes, (gpa: real, deptid: char(6), course: varchar(10)).
(a) Write Oracle object SQL statements to create these two types.
(b) Assuming that a table named students of student_type has been created, insert ug_type tuple into it with attribute values of sid: 12354326, sname: Janet Paeres, phone: null, gpa: 3.2, deptid: CS01, and course:InfoTech.
(c) Assuming there may be data of ug_type and others in the table students of student_type, write an Oracleobject SQL statement to retrieve the sid and sname of only students with deptid of CS01.
My ANSWERS:
A)
CREATE TYPE student_type AS OBJECT (
sid char(8),
sname varchar(15),
phone char(10)) NOT FINAL;/
CREATE TABLE student_type_table of student_type(
sid PRIMARY KEY);
CREATE TYPE ug_type UNDER student_type
(gpa real,
deptid char(6),
course varchar(10));/
CREATE TABLE ug_type_table of ug_type;
B)
INSERT INTO student_type_table VALUES(
Student_type ('12354326', 'Janet Paeres', 'NULL'));
Insert into ug_type_table values(
Ug_type (3.2, 'CS01', 'InfoTech' ));
C)
How to do "C"?
|
|
|
|
| Re: need help for oracle sql [message #635708 is a reply to message #635707] |
Tue, 07 April 2015 04:34  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
FIRST, you have to know that you should NEVER use object type in a stored table. Oracle is a relational database with object extension not an object database and so objects are "hiddenly" and with very poor performances converted to relational objects.
A) testing your code:
SQL> CREATE TYPE student_type AS OBJECT (
2 sid char(8),
3 sname varchar(15),
4 phone char(10)) NOT FINAL;
5 /
Type created.
SQL> CREATE TYPE ug_type UNDER student_type
2 (gpa real,
3 deptid char(6),
4 course varchar(10));
5 /
Type created.
SQL> desc student_type
student_type is NOT FINAL
Name Null? Type
-------------------------------- -------- ----------------------
SID CHAR(8 CHAR)
SNAME VARCHAR2(15 CHAR)
PHONE CHAR(10 CHAR)
SQL> desc ug_type
ug_type extends MICHEL.STUDENT_TYPE
Name Null? Type
-------------------------------- -------- ----------------------
SID CHAR(8 CHAR)
SNAME VARCHAR2(15 CHAR)
PHONE CHAR(10 CHAR)
GPA FLOAT(63)
DEPTID CHAR(6 CHAR)
COURSE VARCHAR2(10 CHAR)
Is this what you want?
B) SQL> CREATE TABLE student_type_table of student_type(
2 sid PRIMARY KEY);
Table created.
SQL> desc student_type_table
Name Null? Type
-------------------------------- -------- ----------------------
SID NOT NULL CHAR(8 CHAR)
SNAME VARCHAR2(15 CHAR)
PHONE CHAR(10 CHAR)
Your answer is not correct because the input (from the text) is an ug_type not a student_type as your "INSERT INTO student_type_table VALUES(Student_type ('12354326', 'Janet Paeres', 'NULL'));" use it.
So the input is (also note there is no quote around NULL):
Ug_type ('12354326', 'Janet Paeres', NULL, 3.2, 'CS01', 'InfoTech')
So:
SQL> insert into student_type_table
2 values (Ug_type ('12354326', 'Janet Paeres', NULL, 3.2, 'CS01', 'InfoTech'));
1 row created.
SQL> select * from student_type_table;
SID SNAME PHONE
-------- --------------- ----------
12354326 Janet Paeres
1 row selected.
The other attributes of the input data are lost (as far as I know).
C) See my last sentence.
[Updated on: Tue, 07 April 2015 04:52] Report message to a moderator |
|
|
|
|
|
| Re: need help for oracle sql [message #635710 is a reply to message #635708] |
Tue, 07 April 2015 04:56 |
|
hivwood
Messages: 7
Registered: April 2015
Location: CMB
|
Junior Member |
|
|
About "C
(c) Assuming there may be data of ug_type and others in the table students of student_type, write an Oracleobject SQL statement to retrieve the sid and sname of only students with deptid of CS01.
Yes data are missed. But i need to get some idea about that. I can drive with sql. But oracle sql is difficult to me. Can help me to write this code. In this situation we need to remember we have lot of data. But only need to catch "only students with deptid of CS01"
Thank you.
[Updated on: Tue, 07 April 2015 05:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
