1 Reply Latest reply on Jan 16, 2015 7:21 AM by Marwim

    sql

    2836880

      Hi All,

       

      I need help with the below. Kindly give it a shot. Thanks a lot

       

       

      create Customer_cars

      (custid  number

      ,car varchar2(10)

      );

       

       

      insert into customer_cars values ( 1,'LEXUS');

      insert into customer_cars values ( 1,'Toyota');

      insert into customer_cars values ( 2,'LEXUS');

      insert into customer_cars values (3,'MERC');

      insert into customer_cars values (4,'LEXUS');

      insert into customer_cars values (5,'BMW);

      insert into customer_cars values ( 6,'LEXUS');

       

      Table One : Customer_cars

      CustId

      Car

       

      1

      Toyota

       

      1

      Lexus

       

      2

      Lexus

       

      3

      Merc

       

      4

      Lexus

       

      5

      BMW

       

      6

      Lexus

       

       

       

       

       

      Table Two

      create Customer_Advisors

      (custid  number

      ,Advisor varchar2(10)

      Account varchar(10)

      );

       

      insert into customer_Advisors values ( 1,'Rick’, ‘abc’);

      insert into customer_ Advisors values ( 1,'Max',ggg);

      insert into customer_ Advisors values ( 1,'Rick’,’hhh’);

      insert into customer_ Advisors values (2,'Johny',’xxx’);

      insert into customer_ Advisors values (2,''Johny,’yyy’);

      insert into customer_ Advisors values (3, ‘Bob’,’cc’);

      insert into customer_ Advisors values ( 3,'Jack', ‘qq’);

      insert into customer_ Advisors values ( 4,'Amy', ‘zzz’);

      insert into customer_ Advisors values ( 4,'Susan', ‘jj’);

       

      Table Two (check if more than one unique Advisor for  Lexus Customers

       

      CustID

      Advisor

      Account

      1

      Rick

      abc

      1

      MAx

      ggg

      1

      Rick

      hhh

      2

      Johny

      xxx

      2

      Johny

      yyy

      3

      Bob

      cc

      3

      Jack

      qq

       

      4

      Amy

      zzz

      4

      Susan

      jjj

       

       

       

       

       

       

      Get all the Id’s of Customers that have Lexus Car from table One.

      Then  check table two to see if those Id’s have more than one unique Advisor

      If so then display all the CustomerIds along with their Advisors and accounts.

      We don’t need to display customers with only one Advisor (ex  Empid 2)

       

       

       

      Final Output:

      CustID

      Advisor

      Account

      1

      Rick

      abc

      1

      Rick

      xxx

      1

      MAX

      hhh

      4

      Amy

      zzz

      4

      Susan

      jjj

       

       

       

       

       

       

       

      Get all the Id’s of Customers that have Lexus Car from table One.

      Then  check table two to see if those Id’s have more than one unique Advisor

      If so then display all the CustomerIds along with their Advisors and accounts.

      We don’t need to display customers with only one Advisor (ex  Empid 2)

       

       

        • 1. Re: sql
          Marwim

          Hello,

           

          this is the forum space for the tool SQL Developer. For question about SQL please post in SQL and PL/SQL

          And when you want solutions for your homework then you should at least show us what you tried yourself.

           

          Regards

          Marcus