Наш вариант теста на знание SQL

    У нас, как и во многих других организациях, проводится тестирование соискателей при поступлении их на работу. Основу тестирования составляет устное собеседование, но в некоторых случаях, даются также практические задания. Несколько дней назад, Руководство попросило меня подготовить набор задач на знание SQL.

    Разумеется, я постарался сделать задания не слишком сложными. Уровень соискателей различен и задачи, на мой взгляд, должны быть составлены таким образом, чтобы по результатам их решения можно было судить о том, насколько хорошо испытуемый знает предмет.

    Также, не имело смысла давать задания на знание каких-либо особенностей тех или иных СУБД. Мы в работе используем Oracle, но это не должно создавать трудностей для соискателей знающих, например, только MS SQL или PostgreSQL. Таким-образом, использование платформо-зависимых решений не возбраняется, но и не является ожидаемым при решении задач.

    Для проведения тестирования, в Oracle 11g была развернута схема, содержащая следующие таблицы:



    Требовалось составить SQL-запросы, для решения следующих пяти заданий:

    Задание 1
    Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителя

    Вариант ответа
    select a.*
    from   employee a, employee b
    where  b.id = a.chief_id
    and    a.salary > b.salary
    



    Задание 2
    Вывести список сотрудников, получающих максимальную заработную плату в своем отделе

    Вариант ответа
    select a.*
    from   employee a
    where  a.salary = ( select max(salary) from employee b
                        where  b.department_id = a.department_id )
    



    Задание 3
    Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек

    Вариант ответа
    select department_id
    from   employee
    group  by department_id
    having count(*) <= 3
    



    Задание 4
    Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделе

    Вариант ответа
    select a.*
    from   employee a
    left   join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
    where  b.id is null
    



    Задание 5
    Найти список ID отделов с максимальной суммарной зарплатой сотрудников

    Вариант ответа
    with sum_salary as
      ( select department_id, sum(salary) salary
        from   employee
        group  by department_id )
    select department_id
    from   sum_salary a       
    where  a.salary = ( select max(salary) from sum_salary ) 
    



    Не требовалось искать в каком-либо смысле оптимальное решение. Единственное требование: запрос должен возвращать правильный ответ на любых входных данных. Задания разрешалось решать в любом порядке, без ограничения времени. При правильном решении всех заданий, предлагалось следующее задание повышенной сложности:

    Дополнительное задание
    Составить SQL-запрос, вычисляющий произведение вещественных значений, содержащихся в некотором столбце таблицы

    Вариант ответа
    select
      exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary))))
     *decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1)
     *sign(min(abs(salary)))
    from employee
    



    Разумеется, опубликованные здесь ответы не являются единственно верными. В случае, если запрос соискателя не содержит явных ошибок, результаты его выполнения (для различных наборов исходных данных) сравниваются с результатами выполнения соответствующего эталонного запроса.
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 125
    • +23
      Это на джуниора? ;)
      • 0
        а здесь большая сложность? меня только 1е и дополнительное задания заставили призадуматься не долго.
        • +9
          Наоборот — слишком просто ;)
          • +5
            Вы оптимист однако. Когда, в разгар кризиса, мы искали себе на джуниора, мне пришлось обработать около тридцати человек, при чем задачи были менее сложные. Из них удалось наковырять только двоих. При чем, у всех, у кого в программе ВУЗа были БД, оценка за этот курс была ОТЛ.
            • 0
              Вы отбирали на должность джуниора на sql позицию и 28 из 30 не могли справиться с этими задачами?
              А что _вообще_ эти джуниоры умели в таком случае и как им в голову пришло в принципе позиционироваться на должность sql инженера?
              Ведь перечисленные 5 вопросов это не то что мидл уровень, это же практически то, с чего начинают изучать _реляционные_ базы данных. Их чуть ли не в туториале приводят.
              Реально очень интересен этот вопрос. То есть понятно что от секретарши знание экселя не всегда можно требовать, но если человек позиционируется на должность sql инженера и не знает содержимого первого урока…
              • +2
                Не с этими, а более простыми задачами.
                Типа поговорили на тему JOIN-ов, простенькая задачка на JOIN, поговорили про группировку — простенький запрос, потом на JOIN + GROUP BY (даже без HAVING).
                В общем, все то, что можно узнать, полистав на выходных «SQL для чайников».
                Про нормальные формы человек 5 были минимально в курсе (не дальше 3НФ, про ДКНФ вообще никто не знал).
                Про транзакции и уровни изоляции — 1 человек.
                При этом, хотелки зарплаты весьма такие приличные. Забавно, самая толковая девочка хотела меньше всех.
                Особенно доставил бывший ИТ-директор, который все знает, но все забыл, а освежить знания за три дня так и не удосужился.

                • +4
                  Всегда было интересно, зачем SQL-девелоперу знать, что такое 3НФ и тем более ДКНФ? Где он это применять будет?
            • 0
              У нас в вузе тоже была Теория БД. А вот семинары велись на Clipper :(
            • 0
              Задачи местами с подковыркой, но не сложные, конечно
              • +3
                К сожалению сейчас даже такие задачи многих соискателей ставят в тупик. 50% кандидатов как правило можно завалить вообще простым заданием на применение обычного LEFT JOIN.
                • 0
                  Я когда набирал 2-х джуниоров на позицию SQL-программист, таких проблем не было. Были конечно индивидуумы, но в целом достаточно адекватные.
                  • +1
                    А я 2 раза искал себе программиста. Что первый раз, что второй приходило с десяток человек и из них только по одному написали простой запрос из двух джойнов, причем можно было пользоваться интернетом.

                    К слову те, кто написали, интернетом не пользовались, и в целом оказались весьма хорошими кандидатами, в результате чего и были приняты.

                    PS Хабаровск.
                  • 0
                    Обратите внимание на участников IT-Планета, там есть конкурс по Oracle SQL.
                    Знаю очень многих участников этого конкурса. Начиная от региональных победителей (если в регионе был конкурс, а не собралось случайное количество людей, что в последние годы становится сложнее) очень толковые ребята, для которых эти запросы — это запросы на 3-4 бала из 10 максимум, если не на 2-3.
                    • 0
                      RIGHT JOIN может неслабо поставить в тупик, остальные JOIN'ы, особенно с группировкой, лучше не спрашивать.
                • 0
                  да
                • –1
                  1. SELECT * AS e1 FROM employee WHERE salary>(SELECT salary FROM employee WHERE id=e1.chief_id)
                  2. SELECT MAX(salary) FROM employee GROUP BY department_id
                  3. SELECT id, department_id AS dep FROM employee WHERE (SELECT COUNT(*) FROM employee WHERE department_id=dep)<=3
                  4. SELECT * AS e FROM employee JOIN employee AS e2 ON e.chief_id=e2.id AND e2.department_id<>e.department_id
                  5. SELECT department_id, SUM(salary) FROM employee GROUP BY department_id
                  Возможны какие-то неточности, лень запускать тестить.
                  • +3
                    Второе неправильно: надо вывести имена сотрудников, а не самые большие зарплаты в отделе
                    • –1
                      [...]
                      • 0
                        Ответ на второй пункт:
                        select name from employee e  
                        join (select department_id, max(salary) as max_salary 
                                    from employee e2 
                                 group by department_id) d
                          on d.department_id=e.department_id
                        where e.salary = d.max_salary
                        

                        Но можно и аналитикой
                        • +2
                          Понятно, что оптимизитору разницы никакой, но почему одно условие вы написали в ON, а другое в WHERE? Почему не оба в ON?
                          • +4
                            Так, для нагллядности… Люблю джойнить по идентификаторам
                            • 0
                              Аналогично: join обычно применяю к связям по внешним ключам, where для уточнения значений остальных полей.
                          • 0
                            можно проще
                            • +1
                              Да ладно, куда ещё проще?
                              • +5
                                Согласен, это субъективно
                                select a.*
                                from   employee a
                                where  a.salary = ( select max(salary) from employee b
                                                    where  b.department_id = a.department_id )
                                


                                Напоминаю, не требуется чтобы запрос был оптимален по производительности
                          • +8
                            Третий пункт можно было бы одним запросом:
                            select department_id from employee group by department_id having count(id)<=3
                            
                          • +2
                            1. можно без подзапроса, но ответ принимается
                            2. неправильно, требуется вывести список сотрудников, а не максимальную зарплату по отделам
                            3. есть значительно более простое решение
                            4. неправильно, не будут выведены сотрудники не имеющие назначенного руководителя вообще
                            5. неправильно, требуется вывести ID только тех отделов, суммарная зарплата в которых максимальна
                            • 0
                              4 пункт
                              select id from employee e1 where not exists(select null from employee e2 where e1.chief_id = e2.id and e1.department_id=e2.department_id)
                              
                              • 0
                                принимается, но можно проще
                              • 0
                                Вариант на пятый пункт, но не совсем верно. Он применим, когда известно, что элемент с максимальным параметром один.
                                SELECT department_id, SUM(salary) as sum_salary 
                                    FROM employee 
                                GROUP BY department_id 
                                ORDER BY sum_salary DESC LIMIT 1
                                
                                • +2
                                  Ну это, по сути, хак, да и в Oracle работать не будет.
                                  Кроме того, такой отдел может быть не один.
                                  • –3
                                    Для Оракла достаточно заменить LIMIT на where ROWNUM=1

                                    Хотя, я похоже не прав…
                                    • 0
                                      С rownum тоже не все просто. Да, аналогичный запрос в Oracle написать можно, но таких отделов может быть и два и больше.
                                      • 0
                                        Да, я специально оговорку сделал перед запросом
                                • +1
                                  Более правильный, на мой взгляд, вариант на 5 пункт:
                                  SELECT department_id 
                                  FROM employee 
                                  GROUP BY department_id 
                                  HAVING SUM(salary)=(SELECT max(sum_salary) 
                                                        FROM (SELECT department_id, SUM(salary) as sum_salary 
                                                                          FROM employee 
                                                                       GROUP BY department_id
                                                                     )q1
                                                      )
                                  

                                  но наверняка не самый оптимальный
                                  • 0
                                    Хороший ответ. Принимается
                                    • 0
                                      ИМХО с оператором ALL красивей будет:
                                      SELECT e1.DEPARTMENT_ID
                                      FROM EMPLOYEE e1
                                      GROUP BY e1.DEPARTMENT_ID
                                      HAVING sum(e1.salary) >= ALL
                                      (SELECT sum(e2.salary)
                                      FROM EMPLOYEE e2
                                      GROUP BY e2.DEPARTMENT_ID)
                                      
                                    • –1
                                      Думаю, с подзапросом количество перебранных БД вариантов будет: (размер_таблицы)*2, а в варианте с объединением — (размер_таблицы)^2
                                      В теории — более оптимально. Как будет на практике — не скажу…
                                  • +6
                                    Бонус: посчитать сумму логарифмов по произвольному основанию и возвести выбранное основание логарифма в степень получившейся суммы :-)
                                    • 0
                                      в целом, да, но там есть нюансы
                                      • 0
                                        Неужели нет решения проще с аналитическими функциями? Я ораклом давно не занимаюсь, уже забыл как там и что.
                                        • 0
                                          Ожидается, что соискатель может быть не знаком с аналитическими функциями
                                      • +1
                                        Круто, но тут будут проблемы с точностью. А если в условии числа целые, то и ответ, наверняка, ожидается точный.
                                        По сабжу, правильное решение — это самописная агрегатная функция.
                                        Но авторы вероятно хотели увидеть решенние с иерархическим запросом(я угадал?)
                                        • +1
                                          А с иерархическим запросом как вы будете агрегировать данные?

                                          Вот рекурсивный CTE — простое решение, мог (я) и раньше догадаться.
                                          • 0
                                            Нет, CTE и тем более самописные агрегатные функции — сильно зависят от СУБД. Ожидалось решение с логарифмами (числа не целые, например какие-то коэффициенты), но корректно обрабатывающее null-ы нули и отрицательные значения. Без специфичных для СУБД функций, скорее всего не обойтись, но принимаются решения, работающие в других СУБД.
                                            • 0
                                              Ну если речь про oracle/sql server/postgresql — тогда CTE самое простое решение — читабельное и поддерживаемое.
                                              • 0
                                                напишите, думаю всем будет интересно
                                                • 0
                                                  Я ошибался, оказывается в оракле рекурсивные CTE не могут быть реализованы с джоином, только с UNION ALL
                                                  • 0
                                                    Есть такое, но если Вы по этому заданию напишите работающее решение для MS SQL или PostgreSQL, оно тоже будет принято (правда MS SQL проверять не на чем)
                                              • 0
                                                Ну, честно говоря, тогда в условие стоит добавить, что числа вещественные. Иначе, по дефолту кажеться, что они целые(ну хотя бы потому что до этого речь шла про salary), и решение с логарифмами отсеивается
                                                • +1
                                                  salary не целое (да и его перемножение большого смысла не имеет, тут речь скорее о коэффициентах), но да, согласен, вношу дополнение
                                        • –2
                                          Можно узнать, а на какую должность то этот тест?
                                          Вряд ли junior уборщица пройдет.
                                          • 0
                                            Брали человека в тех.поддержку
                                          • +2
                                            Скажите, вы хоть эти задания даёте в sqlfiddle.com или ответы должны быть на бумажке и сразу правильные? Лично я мало имею дела каждый день с SQL, но вот с помощью sqlfiddle.com приходилось решать задачи сложнее, чем в тестовых у Вас, хотя все Ваши сходу я правильно и не сделаю.
                                            • +4
                                              Доступ к подготовленной схеме Oracle предоставляется.
                                            • 0
                                              1. select id from employee a1 where salary>(select salary from employee where id=a1.chief_id);
                                              2. select id from employee a1 where salary>(select max(salary) from employee where department_id=a1.department_id);
                                              3. select id from department where (select count(*) from employee where department_id=department.id)<=3;
                                              4. select id from employee a1 where chief_id is null and (select department_id from employee where id=a1.chief_id)=department_id;
                                              5. select department_id from (select department_id, sum(salary) from employee group by department_id order by 2 desc) as a1 limit 1;

                                              на быструю руку с тестированием на постгре.
                                              Эти все пишутся и на бумажке. А вот доп несколько задумал — его бы да, уже с предоставлением работающей схемы.
                                              • –1
                                                по 5му посмотрела коммент выше, если таковой отдел не один, то можно такую странную конструяклю использовать:

                                                select department_id from employee a1 where (select max(salary) from employee where department_id=a1.department_id)=(select max(salary) from employee group by department_id order by 1 desc limit 1);
                                                • 0
                                                  Надо еще зарплаты просуммировать.
                                                • 0
                                                  2. всегда будет возвращать пустую выборку
                                                  4. тоже перемудрили, не работает
                                                  5. в Oracle не работаю оба варианта. корректное решение есть, оно немного лобовое и не очень красивое
                                                  • 0
                                                    извиняюсь за опечатку, кончился лимит исправлений
                                                    • 0
                                                      в 2 там равно, да. очепятало.
                                                      ...where salary=(select…

                                                      для 4 как вариант
                                                      select id from employee a1 where a1.chief_id is null and exists (select id from employee where department_id=a1.department_id and chief_id is not null);

                                                      или я не совсем понимаю задание. Как без признака рук-ва в таком случае отличить рук-ля от безруководительного подчиненного? У них будет один отдел и у обоих же NULLовый chief_id. Недостаточно информативная схема? )
                                                      • 0
                                                        Не надо отличать. Нужно найти всех у кого нет непосредственного руководителя вообще, либо руководитель числится в другом отделе. Схема учебная, конечно.
                                                        • +1
                                                          А, ну тогда так:
                                                          select id from employee a1
                                                          where a1.chief_id is null
                                                          or a1.chief_id not in (select id from employee where department_id=a1.department_id);
                                                • 0
                                                  Формулировка четвёртой задачи какая-то корявая. У доп.задания та же беда.
                                                  • 0
                                                    Предложите свой вариант? На мой взгляд, все понятно
                                                    • –3
                                                      , не имеющих назначенного руководителя
                                                      т.е. руководителя нету
                                                      работающего в том-же отделе
                                                      но он всё-таки есть
                                                      Это что-то из серии — У меня есть классный ответ, я придумал под него задачу, а теперь угадай мой ответ.

                                                      Сформулируйте, например, так — Найти сотрудников, по ошибке у которых отдел не совпадает с отделом их руководителя
                                                      • 0
                                                        1. Не обязательно по ошибке
                                                        2. Имеется в виду, что в том-же отделе непосредственного руководителя нет, то есть руководителя нет ИЛИ имеется руководитель приписанный к другому отделу
                                                        • 0
                                                          Вот я за раскрытие всех умолчаний которые имеются в виду. И за точность формулировки чего от тебя хотят.
                                                          • 0
                                                            Я понимаю Вашу точку зрения, но не согласен с Вашей формулировкой задачи.
                                                            К сожалению лучшей формулировки я не нашел.
                                                    • 0
                                                      • 0
                                                        Ну не все так плохо. Не понял — постараемся объяснить, задать наводящие вопросы…
                                                    • +1
                                                      Первое мб так:
                                                      SELECT a.*, b.salary as boss_salary FROM employee a LEFT JOIN employee b ON a.chief_id=b.id WHERE a.salary > boss_salary

                                                      Только не знаю у всех ли есть босс, там надо тестить. )
                                                      • 0
                                                        Принимается. Кстати, в этом запросе можно использовать inner join, разницы не будет.
                                                        Именно так, как Вы написали в Oracle работать не будет, но это мелочь.
                                                      • НЛО прилетело и опубликовало эту надпись здесь
                                                        • 0
                                                          наводящий вопрос — как быть с отрицательными числами?
                                                          • НЛО прилетело и опубликовало эту надпись здесь
                                                            • 0
                                                              1. да, кроме того есть еще нули и null-ы
                                                              2. да
                                                              3. я не заставляю
                                                              • НЛО прилетело и опубликовало эту надпись здесь
                                                                • 0
                                                                  В этом суть теста — понять насколько глубоко испытуемый понимает задачу
                                                                  • НЛО прилетело и опубликовало эту надпись здесь
                                                                    • 0
                                                                      1. 0
                                                                      2. null-значения игнорируются, если нет не null-значений — результат null (поведение аналогичное sum)

                                                                      Если кандидат задает такие вопросы без наводок — берем без разговоров
                                                                      • НЛО прилетело и опубликовало эту надпись здесь
                                                                        • 0
                                                                          Это для какой СУБД? На Oracle такое точно не работает, в PostgreSQL 9.2 тоже дает ошибку.
                                                                          • НЛО прилетело и опубликовало эту надпись здесь
                                                                            • 0
                                                                              Понятно. Ну проверить не на чем, но, вроде-бы все что надо присутствует. Знаки и нули обрабатываются.
                                                        • 0
                                                          Не хватет дампа данных и самых правильных по мнению автора ответов.
                                                          • +1
                                                            Я обещал вечером опубликовать ответы.

                                                            Данные могут быть такими
                                                            Insert into DEPARTMENT
                                                            (ID, NAME)
                                                            Values
                                                            (1, 'Руководство');
                                                            Insert into DEPARTMENT
                                                            (ID, NAME)
                                                            Values
                                                            (2, 'Секритариат');
                                                            Insert into DEPARTMENT
                                                            (ID, NAME)
                                                            Values
                                                            (3, 'IT');
                                                            Insert into DEPARTMENT
                                                            (ID, NAME)
                                                            Values
                                                            (4, 'Хоз.Отдел');
                                                            COMMIT;

                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (1, 1, 'Иванов И.И.', NULL, 100000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (2, 1, 'Петров П.И.', 1, 90000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (3, 1, 'Сидоров А.Л.', 1, 95000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (4, 2, 'Смирнова О.П.', 1, 20000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (5, 3, 'Константинов Л.И.', 2, 80000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (6, 3, 'Федоров С.И.', 5, 100000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (7, 3, 'Самойлов Н.И.', 5, 100000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (8, 3, 'Никифоров С.П.', 7, 20000);
                                                            Insert into EMPLOYEE
                                                            (ID, DEPARTMENT_ID, NAME, CHIEF_ID, SALARY)
                                                            Values
                                                            (9, 4, 'Митрофанова М.С.', 3, 10000);
                                                            COMMIT;
                                                          • +2
                                                            Может, кому пригодится: отличный портал для изучения SQL: sql-ex.ru. Множество интерактивных упражнений с возможностью в веб-интерфесе проверить результат выполнения своего запроса на нескольких БД, содержимое одной из которых известно. Если сделать даже небольшую часть упражнений, приведённые задачи будут решаться на раз-два.
                                                            Например, автор статьи может вместе с решением добавить рекомендацию: как улучшить свои знания.
                                                            • 0
                                                              Замечательный портал, но не посылать же туда кандидата на собеседовании.
                                                              • 0
                                                                А почему бы и нет? Был случай, когда человек так тушевался на собеседовании что не смог решить ни одной задачи, потом пришёл через неделю и сходу решил штук 50.
                                                                • 0
                                                                  Для подготовки — да, но не как сам тест на собеседовании
                                                                • 0
                                                                  Обычно я туда посылал после неудачного собеседования:)
                                                              • 0
                                                                При такой структуре задача со звездочкой: получить топ 3 сотрудника с максимальной зарплатой по каждому отделу
                                                                • 0
                                                                  Не хочется давать задачи подразумевающие использование аналитических функций (без использования же аналитики, будет очень громоздко). Кроме того, сформулировать корректно такую задачу напросто. Что делать, например, если в топе 4 сотрудника и у всех одинаковая зарплата?
                                                                • 0
                                                                  Основные легкие, даже писать лень. Дополнительное заставило подумать. exp(sum(log(column)))?
                                                                  • 0
                                                                    В целом да, но что делать со значениями, которые нельзя передавать под логарифм?
                                                                    • 0
                                                                      select case when (select count(*) from t where value=0)>0  then 0
                                                                      else (select case when (select count(*) from t where value<0)%2=0 then 1 else -1 end ) * (select exp(sum(log(abs(value)))) from t) end 
                                                                      


                                                                      Null-ы обработаются автоматически — log(null) = null, и sum() его проигнорирует.
                                                                      • 0
                                                                        Да, в PostgreSQL работает
                                                                        • НЛО прилетело и опубликовало эту надпись здесь
                                                                    • –1
                                                                      Разработчик Oracle. Стаж 2 года.
                                                                      Такой вопрос: Я не использую операторы JOIN и за 2 года мне ниразу не приходилось это делать, всегда использую оператор (+), при этом в вопросниках на собеседования, либо в таких вот статьях, частенько слышу и вижу join'ы. Я конечно понимаю, что такие продукты как BI и DWH используют JION, и я вкурсе что это и как его кушать, но настолько ли принципиально джуниору (да и не только ему) писать join'ами? Как отреагирует работодатель увидев (+) в решении тестовой задачи?
                                                                      • 0
                                                                        Примерчик забыл:

                                                                        Задание 4

                                                                        select a.*
                                                                        from employee a,
                                                                        employee b
                                                                        where
                                                                        a.chief_id = b.id(+)
                                                                        and a.department_id = b.department_id(+)
                                                                        and b.id is null
                                                                        • +2
                                                                          1. Ответ принимается
                                                                          2. Увидев (+) Работодатель задаст вопрос, почему не используется ANSI Join. Далее все зависит от Вашего ответа

                                                                          Мы стараемся минимально использовать конструкции связанные с конкретной СУБД. В настоящее время, я не вижу причин по которым не стоило бы использовать в Oracle ANSI Join-ы (если Вы не работаете с совсем старыми версиями)
                                                                          • 0
                                                                            from employee a,
                                                                            employee b


                                                                            что это, как не implicit join?
                                                                        • 0
                                                                          Немного не по теме. Не смотря на то что с базами работаю постоянно, у меня как-то реальная необходимость написать более менее сложный запрос (вроде тех что в посте) появляется крайне редко. При этом я считаю что допольно неплохо умею писать достаточно сложные запросы, но надобности почему-то не возникает. Исключение — отладка: понять что не так в базе, например найти какого-нибудь юзера у которого что-то не совпадает в связных таблицах по сложным правилам. Поэтому мне всегда было интересно, имеет ли вообще смысл требовать от разработчиков среднего уровня знания SQL дальше JOIN и GROUP BY. Если конечно набор не на DBA или DB Developer.
                                                                          • 0
                                                                            Все меняется когда человек приходит в тех.поддержку. Для того, чтобы продраться через мегабайты унаследованного кода, требуется очень хорошо разбираться в SQL.
                                                                            • 0
                                                                              Вот мы как раз переписывали с нуля проект, «продираясь через мегабайты унаследованного кода», потому что бизнес-логику требовалось воспроизвести на 100%. Знания SQL дальше вышеупомянутых JOINов не потребовались. Так что все зависит от проекта.
                                                                              • 0
                                                                                Согласен. Но можете мне поверить, в нашем случае, знание SQL будет совсем не лишним.

                                                                                Возможно потому, что мы не часто можем себе позволить переписывать все «с нуля».
                                                                          • 0
                                                                            Базовые вопросы простенькие, а вот дополнительное задание я даже понять сходу не смог :(

                                                                            Кроме того, ответ противоречит требованию платформонезависимости, ибо содержит функцию DECODE, которая отсутствует в SQL Server'е (для платформонезависимости, если я правильно понял, нужно переписать на CASE).

                                                                            • 0
                                                                              Я сделал соответствующую оговорку. В этой задаче не требуется платформонезависимое решение.
                                                                              • 0
                                                                                Я поясню, почему я не понял ваше задание.Я часто сталкиваюсь с SQL, но вот не помню, что такое «Вещественные значения». Это аналог вещественного числа? Правильно ли я понимаю, что из определенной колонки нужно взять только вещественные числа (целые не трогать) и посчитать их произведение?
                                                                                • 0
                                                                                  Ну про вещественные значения я добавил уже в процессе обсуждения здесь, просто чтобы было понятно, что числа могут быть не целыми. Надо просто найти произведение всех чисел, ничего не пропуская, аналогично тому, как sum находит сумму.

                                                                                  Эта задача действительно возникла в одном старом проекте, когда нужно было перемножить коэффициенты льгот. В том случае, значения лежали в диапазоне от 0 до 1.
                                                                                  • 0
                                                                                    Ок.
                                                                                    Не могу сейчас посмотреть правильность решения на Oracle, но на MS SQL это реализуемо только с помощью UDF. Задание на базе опыта — это здорово, но в данном случае вы либо принимаете ответ с UDF, либо декларируете, что отдаете предпочтение кандидатам, знающим Oracle :)

                                                                                    Хотелось бы предложить вам другой доп. вопрос, на который возможен платформонезависимый ответ, но, к сожалению, в голову сейчас ничего подходящего не приходит.
                                                                                    • 0
                                                                                      Принимается любой правильно-работающий ответ для любой РСУБД. Просто для Oracle и PostgreSQL я легко могу проверить ответ, а MySql или MS SQL под рукой нет, соответственно, соискателю придется больше объяснять. Но если беседа дошла до этого вопроса, человек нам заведомо интересен.
                                                                                      • 0
                                                                                        Переписал ответ на CASE для совместимости с SQL Server'ом. Конструкция CASE должна быть платформонезависимой Также пришлось заменить mod на % и ln на log, так что полной независимости достичь не удалось:

                                                                                        select
                                                                                          exp(sum(log(case sign(salary) when 0 then 1 when -1 then -salary else salary end)))
                                                                                         *case sum(case sign(salary) when -1 then 1 else 0 end)%2 when 1 then -1 else 1 end
                                                                                         *sign(min(abs(salary)))
                                                                                        from employee
                                                                                        
                                                                            • 0
                                                                              Прошу простить меня, если об этом написано ранее (сам аналогичного разбора в комментариях к данному посту не нашел).
                                                                              Разбор дополнительного задания
                                                                              select
                                                                                exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary))))
                                                                               *decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1)
                                                                               *sign(min(abs(salary)))
                                                                              from employee
                                                                              

                                                                              1ая строка:

                                                                              2ая строка:
                                                                              определяет знак всего произведения, анализируя кол-во минусов
                                                                              3ья строка:
                                                                              если в произведении есть хотя бы один 0, то произведение *0
                                                                              • 0
                                                                                Зачем sign в третьей строчке?
                                                                                • 0
                                                                                  Чтобы значение отличное от нуля дало единицу, не изменяющую значение произведения.
                                                                              • 0
                                                                                Задания, конечно, слишком простые, лучше бы кто-нибудь выложил интересные и сложные задания для топовых вакансий…
                                                                                А так для баловства вот парочка:
                                                                                5-я задача:
                                                                                select * 
                                                                                from (
                                                                                  select e.department_id,dense_rank()over(order by sum(salary) desc) dr
                                                                                  from hr.employees e
                                                                                  group by e.department_id
                                                                                ) 
                                                                                where dr=1
                                                                                

                                                                                Дополнительная:
                                                                                with t as (
                                                                                    select *
                                                                                    from hr.employees e
                                                                                    model
                                                                                       dimension by (row_number()over(order by 1) rn)
                                                                                       measures(1d multiply,salary salary)
                                                                                       rules(
                                                                                          multiply[any] order by rn desc= nvl(multiply[cv()+1],1)*nvl(salary[cv()],1)
                                                                                       )
                                                                                )
                                                                                select multiply from t where rn=1
                                                                                
                                                                                Или с ограничением на кол-во:
                                                                                select multiply
                                                                                from hr.employees e
                                                                                model return updated rows
                                                                                   dimension by (row_number()over(order by 1) rn)
                                                                                   measures(1d multiply,salary)
                                                                                   rules
                                                                                   iterate(6e6) until( presentv(salary[iteration_number+1],1,0)=0)
                                                                                   (
                                                                                      multiply[0] = nvl(multiply[0],1) * nvl( salary[iteration_number+1] ,1)
                                                                                   )
                                                                                
                                                                                Рекурсивным subquery factoring точно также, поэтому не стал писать
                                                                                • 0
                                                                                  Задания и задумывались как простые. Мы ведь ищем человека не для того, чтобы олимпиадные задачки решать. Кстати, Ваш ответ подтверждает то, что сотрудники тех.поддержки должны хорошо знать SQL. В противном случае, они никогда не разберутся в подобных запросах написанных разработчиками (Ваши ответы, разумеется, верные).
                                                                                • 0

                                                                                  У вас ошибка в реализации 1-го задания (выводятся руководители)


                                                                                  SELECT e.*
                                                                                  FROM EMPLOYEE AS e
                                                                                  WHERE 
                                                                                      e.SALARY > 
                                                                                          (
                                                                                              SELECT SALARY 
                                                                                              FROM EMPLOYEE 
                                                                                              WHERE 
                                                                                                  DEPARTMENT_ID = e.DEPARTMENT_ID 
                                                                                                  AND CHIEF_ID IS NOT NULL
                                                                                          )
                                                                                  • 0
                                                                                    Если вы об этом варианте:
                                                                                    select a.*
                                                                                    from   employee a, employee b
                                                                                    where  b.id = a.chief_id
                                                                                    and    a.salary > b.salary
                                                                                    

                                                                                    то в нём ошибки нет. Ещё раз повторю задание:
                                                                                    Вывести список сотрудников, получающих заработную плату большую чем у непосредственного руководителя
                                                                                    Запрос практически дословно его повторяет. Выводятся сотрудники имеющие непосредственных руководителей (inner join), при условии того, что они получают больше этих самых руководителей. Ваш вариант запроса ищет что-то совсем другое.
                                                                                    • +1
                                                                                      У вас в подзапросе больше 1 строки, так что результата вы не дождётесь.

                                                                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.