Very challenging SQL interview (can't use stored procedure)

Very challenging SQL interview (can't use stored procedure)

You have a SQL table table with two columns: name and pen. Both columns are text strings.

name  | pen  ---------------      mike  | red  mike  | red  mike  | blue  mike  | green  steve | red  steve | yellow  anton | red  anton | blue  anton | green  anton | black  alex  | black  alex  | green  alex  | yellow  alex  | red  

Person's name is given as the input argument.

Please write a SQL statement (not a stored procedure) which returns names of persons having unique set of pens which is equivalent or wider/bigger than the set of pens of given person.

Examples:

• input: mike
• output: anton

Mike has (red, blue, green).
Anton has more gadgets (red, blue, green) + black.

• input: steve
• output: alex

Steve has (red, yellow).
Alex has (red, yellow) + green+ black.
Mike, Anton aren't printed - they do not have yellow.

• input: alex
• output:

Answer by Martin Smith for Very challenging SQL interview (can't use stored procedure)

Here's one way (Online Demo), assuming input name "steve".

This can be rephrased as "Looking for all users for which there does not exist a pen owned by steve that they do not own"

SELECT DISTINCT name  FROM   table t1  WHERE  NOT EXISTS (SELECT *                     FROM   table t2                     WHERE  name = 'steve'                            AND NOT EXISTS (SELECT *                                            FROM   table t3                                            WHERE  t2.pen = t3.pen                                                   AND t1.name = t3.name))    AND t1.name <> 'steve' /*Exclude input name from results*/  

See this article for other techniques

Answer by Nikhil Shinde for Very challenging SQL interview (can't use stored procedure)

Table Format:    E_NAME  E_PEN  ----------------  mike    green   mike    blue    mike    red     mike    red     steve   red     steve   yellow  anton   red     anton   blue    anton   green   anton   black   alex    black   alex    green   alex    yellow  alex    red         Query:         SELECT A.E_NAME, A.E_PEN    FROM V_NAME A, V_NAME B   WHERE TRIM(UPPER(A.E_NAME)) = TRIM(UPPER(B.E_NAME))     AND TRIM(UPPER(A.E_NAME)) != 'MIKE'     AND TRIM(UPPER(A.E_PEN)) = TRIM(UPPER(B.E_PEN(+)))    Procedure to take Input from user:     CREATE OR REPLACE PROCEDURE E_TEST(I_NAME VARCHAR2) AS  BEGIN    EXECUTE IMMEDIATE 'CREATE TABLE E_TABLE AS                     SELECT A.E_NAME,A.E_PEN FROM V_NAME A,V_NAME B                     WHERE TRIM(UPPER(A.E_NAME)) = TRIM(UPPER(B.E_NAME))                     AND TRIM(UPPER(A.E_NAME)) != ''' ||                      I_NAME || '''                     AND TRIM(UPPER(A.E_PEN))= TRIM(UPPER(B.E_PEN(+)))';  END;    Name: Nikhil Shinde  E-Mail: nikhilshinde3jun@gmail.com  

Answer by Rahul for Very challenging SQL interview (can't use stored procedure)

  with test1 as     (select a.name nm, count(distinct a.pen) ct        from table a, table b       where b.pen = a.pen         and b.name = 'anton'       group by a.name       order by 2 desc),    test2 as     (select  nm name1      from test1      where ct = (select max(ct) from test1))    select distinct c.name       from table c     where c.name in (select name1                        from test2                        where name1 not in (case                               when (select count(distinct name1) from test2) > 1 then                                'anton'                               else                                ' '                             end))  

Answer by Marcelo Vollbrecht for Very challenging SQL interview (can't use stored procedure)

declare @t table (name nvarchar(20), pen nvarchar(20))  insert into @t (name,pen)  values('mike','red'),  ('mike','red'),  ('mike','blue'),  ('mike','green'),  ('steve','red'),  ('steve','yellow'),  ('anton','red'),  ('anton','blue'),  ('anton','green'),  ('anton','black'),  ('alex','black'),  ('alex','green'),  ('alex','yellow'),  ('alex','red')    declare @input nvarchar(20) = 'mike';    with cte_input (name, pen)  as  (      select distinct name, pen      from @t       where name = @input  )  , cte_colors (name, matches)  as  (      select name, matches = count(distinct pen)      from @t       where name != @input              and pen in (select pen from cte_input)        group by name      having count(distinct pen) = (select count(1) from cte_input)  )  select t.name   from   @t t  join   cte_colors m on m.name = t.name  group by t.name, m.matches  having count(distinct t.pen) > m.matches  

Answer by Rajendra for Very challenging SQL interview (can't use stored procedure)

We can use LEAD analytical function to achieve this solution.

The query would be

select next_name from  (select name, count(pen) CNT, LEAD(name,1) over (order by count(pen)) next_name   from table  group by name order by CNT  )  where name=input_value;  

the sub query will give the results as below

name | CNT | next_name

mike | 3 | Steve

steve | 2 | anton

anton | 4 | alex

alex | 4 | (null)

Then the out query filter the row required and gives the next_name which is what we are looking for.

Answer by Raman for Very challenging SQL interview (can't use stored procedure)

create table practise ( name varchar (10),pens varchar (10)

) insert practise (name,pens) select 'mike','red' union select 'mike','red' union select 'mike','blue' union select 'mike','green' union select 'steve','red' union select 'steve','yellow' union select 'anton','blue' union select 'anton','green' union select 'anton','black' union select 'anton','red' union select 'alex','black' union select 'alex','green' union select 'alex','yellow' union select 'alex','red'

select * into #t from practise

 update #t   set Colourcode = 1 where pens = 'black'   go    update #t   set Colourcode = 2 where pens = 'blue'    go    update #t   set Colourcode = 3 where pens = 'green'    go    update #t   set Colourcode = 4 where pens = 'red'   go    update #t    set Colourcode = 5 where pens = 'yellow'  

select distinct colourcode from #t

/* IMP Code / update #t set Number = ( select count() from #t where name = 'steve' and colourcode = 1 ) where name = 'steve' and colourcode = 1

/* once the above update statement is exected change colourcode = 2 and again run the statement change colourcode = 3 and again run the statement change colourcode = 4 and again run the statement change colourcode = 5 and again run the statement*/

/* Follow the same process for name 'alex','anton' and 'mike' */

/* Below is the Solution*/

select name from #t where number = ( select distinct number from #t where colourcode = 3 and Number =1)

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72