Message:
: Test. : : Hi, : : you need to create three tables ACCOUNTS, DEVICES and SERVICES : : : CREATE TABLE ACCOUNT : : (acc_id NUMBER NOT NULL, : : column1 NUMBER, : : column2 VARCHAR2(255) : : ) : : / : : CREATE TABLE DEVICES : : (device_id NUMBER NOT NULL, : : acc_id NUMBER NOT NULL, : : column2 VARCHAR2(255) : : ) : : / : : CREATE TABLE SERVICES : : (service_id NUMBER NOT NULL, : : device_id NUMBER NOT NULL, : : column2 VARCHAR2(255) : : ) : : / : : ALTER TABLE ACCOUNTS add (CONSTRAINT acc_pk PRIMARY KEY (acc_id)) : : / : : ALTER TABLE DEVICES add : : ( : : CONSTRAINT device_pk PRIMARY KEY (device_id) : : ,CONSTRAINT device_fk FOREIGN KEY (acc_id) REFERENCES accounts (acc_id) : : ) : : / : : ALTER TABLE SERVICES add : : ( : : CONSTRAINT service_pk PRIMARY KEY (service_id) : : ,CONSTRAINT service_fk FOREIGN KEY (device_id) REFERENCES devices (device_id) : : ) : : / : : Now to get all the devices for all account use this query : : : select device_id : : from accounts a, devices b : : where a.acc_id = b.acc_id : : order by acc_id, device_id : : / : : To get all the devices for a given account use this query : : : select device_id : : from devices : : where b.acc_id = 123 : : / : : To get all the services for each account, use this query : : : select a.acc_id, service_id : : from accounts a, devices b, services c : : where a.acc_id = b.acc_id : : and b.device_id = c.device_id : : order by a.acc_id, service_id : : / : : To get all the services for a given account, use this query : : : select service_id : : from services : : where device_id IN : : ( : : select a.device_id : : from devices a, services b : : where a.acc_id = 123 : : ) : : / : : : : Same way you can try for all serivces for a device and account for the device. Thats ur homework :) : : Thanks : : Amit : : : : : Hi folks! : : : This is my first experience with oracle. So i need a ton lot of assistance. I have three parameters account, devices and subscription services. I have to have a table of these 3 parameters. One account can have lots of devices attached to it and each device can subsribe for the same or different services. As for the queries, given an account name, i should be able to list the services associated with the account and the devices associated with the account. Given a device, i should be able to report the account in which it exists and the service it subscribes to. What all tables should i have and how i should create them. Please let me know. : : : Thanks in advance