There is a table with collection (nested table): COMPANIES(C_NUMB, C_NAME, C_TYP

Question

There is a table with collection (nested table): COMPANIES(C_NUMB, C_NAME, C_TYPE, CLERKS (C_NUM, C_NAME, GENDER, SALARY). Please create the object view to achieve companies names and sum of clerks salaries in every company. Write a corresponding SQL, to extract data from object view. Create table STUDENTS with complex row type objects. Row type object STUDENT has attributes: NUM, NAME, MARKS. Attribute MARKS is sub-object with attributes: MARK_1, MARK_2, MARK_3. Insert two row data in the table STUDENTS. There is relational table DATABASE_SYSTEMS(S_NUM, S_NAME, S_TYPE). Write a PL/SQL program, what extracts all object-relational (S_TYPE) database systems numbers and names in PL/SQL table RESULT. There are two object types: COMPUTER (C_NUM, C_NAME, C_TYPE) and PRINTER (P_NUM, P_NAME, P_TYPE). Please: create appropriate tables; insert 2 rows in both tables; connect tables with object reference 1 1; write object SQL query to achieve computer names and appropriate printer names. There is a collection of rings. Every ring has number (NUM) and radius (RAD). Please create object table RINGS(NUM, RAD) with row type objects and object method COMPARE, which compare rings by squares. Please write object SOL query, using created object method, to achieve a sorted list of rings. In database we have table STUDENTS (S_NUM, S_NAME, GENDER, GROUP). Please write Java program and comment the commands to extract all womens from table. What types of XML data storage in database you use and what characteristic features they have?

Explanation / Answer

2.Creating a table with complex row types in PostgreSQl

CREATE TYPE M AS (

marks1 integer,

marks2 integer,

marks3 integer );

CREATE TABLE STUDENT(

NUM integer,

NAME text,

MARKS M

);

INSERT INTO STUDENT VALUES( 22,'abc',ROW(78,97,98));

INSERT INTO STUDENT VALUES(23,'def',ROW(56,67,78));

3.PL/SQL PRogram

CREATE PROCEDURE P1(

S_TYPE_IN IN DATABASE_SYSTEMS .S_TYPE%TYPE)

IS

1_S_NUM DATABASE_SYSTEMS.S_NUM%TYPE;

1_S_NAME DATABASE_SYSTEMS.S_NAME%TYPE;

BEGIN

SELECT S_NUM,S_NAME INTO 1_S_NUM,1_S_NAME

FROM DATABASE_SYSTEMS

WHERE

S_TYPE='OBJECT RELATIONAL';

END;

7.XML storage types

1.Structured storage:data is stored in relations.Data can be retrieval and updation can be performed effectively with the help of queries.

2.Binary XML storage:data is stored in postparsed binary format.It is flexible and can be used for schemaless documents.

3.Unstructed storage:data is stored in CLOB(character large object )instances.CLOB storage provided fast insertion and retrieval.

4.Hybrid storage:one model for one part of xml document and other for another part,it is a mixture of structured and unstructured storage.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.