Home » SQL & PL/SQL » SQL & PL/SQL » need urgent help !!!
need urgent help !!! [message #214480] Tue, 16 January 2007 12:41 Go to next message
Messages: 1
Registered: January 2007
Junior Member
can somebody solve this problem ?

there are six tables with the next structure:

-mark, number(4), prkey
-name, char(20), not null
-job, char(20)
-salary, number(9,2), not null, >0
-bonus_income, number(9,2)
-emp_mark, number(4)
-hiredate, date
-deptno, number(4), not null

these 6 tables have names like: emp1, ... emp6.
we know that tables have data into them, and have diffrent owners.

i have to:

a) create 6 users: USER1,...USER6 with the right to create and maintain the info in the tables, having one single role, and

conisdering that the user we are using is there admin.
b) verify the role and the users in data dictionary

a) create, using each user's rights an associated table to each one of them, using the structure and the constraints

specified earlier:
so: user1 creates table emp1,...user6 creates table emp6
b) supply each table with 10 records, automaticaly using for "deptno" of the first table the value 1000, "deptno" for table 2

would be 2000,...
also, those 10 records in each table based on emp_mark (employee mark) implements an employee hierarhy in each department.

3) verify the structure and the contents of the objects created 'till now;

a) the user having andmin rights has to unify the 6 tables into a new one, called "employee" without loosing data or

deteriorating employees hierarhy.
table "employee" must keep the constraints specified in each one of those 6 tables.
b) modify "mark" column, considering we ommited "mark" column's sequiential controll witch must be "applied" sequentialy,

ordering by date (calendaristic date) without modifying the employee's hierarhy
c) show the content of "employee"

a) make the sequence needed to controll supplying data for "employee" table cosidering start value = 61 and step = 1;
b) verify the structure and the proprieties of the sequence in the data dictionary .

a) create table department with the structure:
-dept_code, number(4), prkey, having one of the values: 1000,2000,3000,4000,5000,6000
-name, char(20), not null
-loc, char(20)
b) supply the table with data covering all 6 departments.
c) verify the data dictionary for the new created objects and show department's table content.

7) modify "employee"'s structure, in sense of making "deptno" a foreign key.

a) using the previous created sequence, supply the "employee" table with 10 records.
b) show (select) only the new values that appear now in the table

a) create a structure of synonyms for all objects created at points 4, 5 and 6.
the following modifications are going to affect the synonyms.
b) verify the data dictionary if the synonyms exist.

a) create a table "incomes" with the structure:
-deptno, number(4), prkey
-income_summ, number(9,2)
this table should contain , for each department, employees income.
b) show "incomes"'s table content;

11) we know that all employee's -in a department- bonus income represents 10% * departments income.
also, in every department, exists a N level hierarhy. For each level of hierarhy, all employee's bonus income into a

department is equaly distributed. For example, having a K-level hierarhy, for those on the M level we have M/K from all bonus

income of all department's employees.
update bonus income for each employee and write a query to show the new status of each employee.

12) create views containg:
-mgr_name, min_income_employee(sal+bonus_income), max_income_employee
-emp_name, number of worked weeks in the year he was hired
-emp_name,sal, bonus_income, for those having same sal and bonus income with one of the employees in the department with a

max number of hiered employees.

a) check the data dictionary to see if views were created
b) show view's structure and content

14) make raports that contain:
a) all_mgr_income payed in department 1000, ...department 6000
b) dept_name, department mgr's name, number_of_mgr_employees
c) emp_name, emp_bonus_income, dept_name, dept_income, percent of emp_income from dept_income
- each raport must have a title.

15) update initial tables emp1,...emp6 using data from employee table. data from employee table supplyed only in this table

will be inserted in tables corresponding to department's number where the new employees where inserted.
show these tables content;

16) using data from tables: emp1,...emp6 create a raport containing:
Re: need urgent help !!! [message #214482 is a reply to message #214480] Tue, 16 January 2007 12:48 Go to previous message
Messages: 25149
Registered: January 2009
Location: SoCal
Senior Member
>can somebody solve this problem ?
Somebody like you?
I don't know. Can you?
It appears to be a decent homework assignment.
I hope you took good notes in class & are willing to read and comprehend the text book or SQL Reference manual found at http://tahiti.oracle.com

Why is it urgent to me to solve this problem for you?
With free advice you can have it good, fast or cheap. Pick any 2.

[Updated on: Tue, 16 January 2007 12:50] by Moderator

Report message to a moderator

Previous Topic: Extracting a variable value
Next Topic: Java TABLE function in Oracle
Goto Forum:

Current Time: Sat Jan 21 22:21:35 CST 2017

Total time taken to generate the page: 0.51546 seconds