create table customer (cust_id integer not null, cust_name char(20) not null , cust_address varchar2(200) , emp_id integer not null, constraint pk_customer primary key(cust_id) ); create table account (account_number integer not null, account_balance number(8,2) not null, constraint pk_acount primary key(account_number) ); create table has (cust_id integer not null, account_number integer not null, constraint pk_has primary key(cust_id, account_number) ) alter table has add constraint fk_account_has foreign key(account_number) references account(account_number); alter table has add constraint fk_customer_has foreign key(cust_id) references customer(cust_id);
Q1 Show the names of customers that have accounts
Q2 Show the customer names with the names of the employees they deal with**
Advertisement
Answer
Q1 is a simple lookup of the cust_id
in junction table has
:
select c.cust_name from customer c where exists (select 1 from has h where h.cust_id = c.cust_id)
This phrases as: select the customers that have at least one entry in the has
table.
When it comes to Q2: your data structures show no sign of employees (all we have is customers and accounts), so this cannot be answered based on the information that you provided. You might want to ask a new question for this, providing sample data for the involved tables, along with desired results and you current attempt at solving the problem.