Skip to content
Advertisement

Show the names of customers that have accounts SQL Query Oracle 10G

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.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement