-1
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**

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Abed Al Rahman Hussien Balhawa
  • 5
  • 4
  • 2
    (1) Where is the definition of table `has`? (2) Have you attempted anything at your homework yet? – GMB Feb 22 '20 at 22:08
  • create table has (cust_id integer not null, account_number integer not null, constraint pk_has primary key(cust_id, account_number) ); – Abed Al Rahman Hussien Balhawa Feb 22 '20 at 22:21
  • Please don't bury additional information about your question in a comment - it will often be overlooked. Instead, use the `edit` button (above, below the question text) to edit your question, adding whatever additional information is needed. I've copied the definition of the `has` table to the question this time, so please remember this for use in future. Thanks. – Bob Jarvis - Слава Україні Feb 23 '20 at 00:59
  • To practice your use of the editor, please **edit your question** and include the definition of the table which contains employee names. Thanks. – Bob Jarvis - Слава Україні Feb 23 '20 at 01:06

1 Answers1

0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Q1 select distinct cust_name       from customer c,has h       where c.cust_id = h.cust_id; does this works too ? – Abed Al Rahman Hussien Balhawa Feb 22 '20 at 22:30
  • @AbedAlRahmanHussienBalhawa: I think it would work but (1) it uses old-school joins (with a comma in the `from` clause) - this is an old syntax that is not state-of-the-art anymore, and should generally be avoided (use the `join ... on ...` syntax instead) (2) it uses aggregation (`select distinct`), which will be less efficient that the solution I provided here. – GMB Feb 22 '20 at 22:32
  • create table employee (emp_id integer not null, manager_id integer not null, emp_name char(20) not null, emp_tel char(10), emp_salary number not null, hire_date date, constraint pk_employee primary key(emp_id) ); 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) ); – Abed Al Rahman Hussien Balhawa Feb 22 '20 at 22:33
  • alter table employee add constraint fk_employee_manager foreign key(manager_id) references employee(emp_id); alter table customer add constraint fk_employee_customer foreign key(emp_id) references employee(emp_id); – Abed Al Rahman Hussien Balhawa Feb 22 '20 at 22:36