Home » SQL & PL/SQL » SQL & PL/SQL » SQL Division
icon9.gif  SQL Division [message #228500] Tue, 03 April 2007 04:16 Go to next message
sql_difficulties
Messages: 2
Registered: April 2007
Junior Member
Hi,

I have created a 'HR system' database with 7 tables (Employees, Jobs, Job History, Departments, Locations, Countries and Regions) can anyone tell me how I could perform a Division operation on any of these.

I can provide details of each table, and am using ORACLE XE that is web based.

Any help you can give would be much appreciated.

Thanks!

[Updated on: Tue, 03 April 2007 04:22]

Report message to a moderator

Re: SQL Division [message #228504 is a reply to message #228500] Tue, 03 April 2007 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean a division on a table? Question
Oracle divide numbers not tables, / is the operator:
SQL> select 1/2 from dual;
       1/2
----------
        .5

1 row selected.

See SQL Reference.

Regards
Michel
Re: SQL Division [message #228511 is a reply to message #228500] Tue, 03 April 2007 04:38 Go to previous messageGo to next message
sql_difficulties
Messages: 2
Registered: April 2007
Junior Member
Hi Michel,

The project requires a number of operations to be performed on attributes held within the tables, including Selection, Projection, Cartesian Product, various Joins and I believe you can perform a Division on several tables where...

R / S
Defines a relation over the attributes C that consists of a set of tuples from R that match the combination of every tuple in S.


An example we have been given (fpr a Property scenario) is to identify all CLIENTS who have viewed ALL properties with THREE rooms.

This involves taking attributes from 2 tables.

Not sure if this clears things up, but I am having difficulty in finding a compatible example for my HR system.

Any ideas?
Re: SQL Division [message #228692 is a reply to message #228511] Tue, 03 April 2007 14:13 Go to previous message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

sql_difficulties wrote on Tue, 03 April 2007 05:38
Hi Michel,

The project requires a number of operations to be performed on attributes held within the tables, including Selection, Projection, Cartesian Product, various Joins and I believe you can perform a Division on several tables where...

R / S
Defines a relation over the attributes C that consists of a set of tuples from R that match the combination of every tuple in S.


An example we have been given (fpr a Property scenario) is to identify all CLIENTS who have viewed ALL properties with THREE rooms.

This involves taking attributes from 2 tables.

Not sure if this clears things up, but I am having difficulty in finding a compatible example for my HR system.

Any ideas?


In your example, you're saying you would make use of 2 tables to find the solution. I'm assuming that there is a table containing all client information (client ID, name, address, etc.) and a table containing property data that includes which clients have viewed which rooms and the number of rooms for each property. We'll call the tables CLIENTS and PROPERTIES and we'll assume the link between the two is a column called client_id, which is the primary key in the CLIENTS table and a foreign key in the PROPERTIES table. With that understanding, this is code that would show you the clients that have seen all properties with 3 rooms:

select c.client_name
from   clients c, properties p
where  c.client_id = p.client_id
and    p.number_of_rooms = 3
group by c.client_name
having count(distinct p.property_id) = (select count(distinct property_id)
                                        from   properties
                                        where  number_of_rooms = 3)  



I don't know if this is the most efficient solution, but it is a solution. So, as far as your HR system is concerned, you could use this kind of code to determine which employees have worked in all locations or something like that. Let me know if this helps you or not and we'll go from there.

[Updated on: Tue, 03 April 2007 14:49]

Report message to a moderator

Previous Topic: insert in plsql table
Next Topic: Insert using DBLINK; Trigger not getting executed.
Goto Forum:
  


Current Time: Sat Dec 03 05:44:25 CST 2016

Total time taken to generate the page: 0.07045 seconds