Oracle SQL interview

SBolgov
Уже с Приветом
Posts: 14006
Joined: 17 Jun 2003 04:41

Post by SBolgov »

Siberian Cableman wrote:Я имел в виду отличие вышеприведенного графа от другого, когда у одного работника может быть куча начальников, а то и ситуация когда я Ваш босс, а Ваш подчинненый мой босс.

Дык деревом и называется.

Согласно определению, дерево - это связный граф без циклов. :umnik1:

А корневое дерево - это дерево, у которого одна вершина специально выделена и обозвана "корнем". (AKA Самый Главный Начальник.)

Кстати, в описанном Вами случае в таблице может содержаться не одно дерево, а сразу несколько. (Есть несколько самых главных начальников с непересекающимися наборами подчинённых.) На такой случай ;) тоже определение имеется:

Лес - это совокупность деревьев. :umnik1:
Не гоните, и не гонимы будете...
User avatar
Sabina
Уже с Приветом
Posts: 5669
Joined: 13 Oct 2000 09:01
Location: East Bay, CA

Post by Sabina »

Siberian Cableman wrote:Вот что я спрашиваю на интервью для QA-ев:

Дано 3-и таблицы:
CREATE TABLE emp (id int, name varchar(32));
CREATE TABLE sal (id int, salary int);
CREATE TABLE org (id int, manid int);

Ну а дальше конкретные вопросы:
1. Вывести табличку Имя, Зарплата для народа с зарплатой больше определенной суммы.


Code: Select all

select name, salary from emp e, sal s where e.id=s.id and salary > 30;


Siberian Cableman wrote:2. Вывести Имена и Зарплату всех подчинненых начальника с именем 'Том' которые получают зарплату больше определенной суммы.


Code: Select all

select name, salary from emp e, sal s, org o where e.id=s.id and salary > 30 and o.id=e.id and o.manid = (select id from emp where name='Tom');


Siberian Cableman wrote:3. Вывести Имена и Зарплату всех подчинненых начальника с именем 'Том' которые получают зарплату меньше определенной суммы.


Code: Select all

select name, salary from emp e, sal s, org o where e.id=s.id and salary < 30 and o.id=e.id and o.manid = (select id from emp where name='Tom');


Siberian Cableman wrote:При этом вывести имена/зарплату 5-и самых низкооплачиваемых, т.е. от нуля и выше.


Code: Select all

select * from (select name, salary from emp e, sal s, org o where e.id=s.id and salary < 30 and o.id=e.id and o.manid = (select id from emp where name='Tom')) where rownum < 6;


Siberian Cableman wrote:5. Написать Хранимую Процедуру в качестве ответа на 2 или 3 вопрос с Именем начальника/Суммой зарплаты в качестве входных параметров.


Code: Select all

CREATE OR REPLACE PROCEDURE stat_sp
        (p_name IN emp.name%TYPE,
         p_salary IN sal.salary%TYPE)
 IS
 CURSOR cur_emp IS
   select name, salary from emp e, sal s, org o
   where e.id=s.id
   and salary > p_salary
   and o.id=e.id
   and o.manid = (select id from emp where name=p_name);
BEGIN
   FOR rec_emp IN cur_emp LOOP
     DBMS_OUTPUT.PUT_LINE('Name:' || rec_emp.name ||', Salary:'||rec_emp.salary);
   END LOOP;
 COMMIT;
END;
/
*********
SQL> SET SERVEROUTPUT ON
SQL>  EXECUTE stat_sp('Tom',30);
Last edited by Sabina on 04 Aug 2004 05:22, edited 1 time in total.
User avatar
Vik_NJ
Уже с Приветом
Posts: 1995
Joined: 29 Dec 2001 10:01
Location: Kiev->...->NYC

Re: Oracle SQL interview

Post by Vik_NJ »

Sabina wrote:
А что тут еще можно сказать, кроме того, что это ограничение использования BEFORE UPDATE триггера, когда одна и та же таблица изменяется DML action и на ней же trigger is fired. И нужно использовать way around, типа использовать два триггера вместо одного, с referencing to package variables и пр. ?

Мне вообще-то надо именно заковыристые select-ы, руку набить хоть немного.

Sabina, да Вы всё и так знаете! :D
Вот Вам задачка,
дана таблица
people ( id int,
firstname varchar2(30),
lastname varchar2(30),
...
)
Вам нужно не используя rownum вывести пронумерованный список (seqnumber, firstname, lastname, id) всех, у кого first name = "Sabina" :D seqnumber должен = 1 для первой записи, 2 для второй и т.д..
I may grow old, but I refuse to grow up!
User avatar
Sabina
Уже с Приветом
Posts: 5669
Joined: 13 Oct 2000 09:01
Location: East Bay, CA

Re: Oracle SQL interview

Post by Sabina »

Vik_NJ wrote:Вот Вам задачка,
дана таблица
people ( id int,
firstname varchar2(30),
lastname varchar2(30),
...
)
Вам нужно не используя rownum вывести пронумерованный список (seqnumber, firstname, lastname, id) всех, у кого first name = "Sabina" :D seqnumber должен = 1 для первой записи, 2 для второй и т.д..


Так?

Code: Select all

CREATE SEQUENCE people_seq
    MINVALUE 1
    MAXVALUE 1000
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

    SELECT people_seq.nextval, id, firstname, secondname
    FROM people
    WHERE firstname = 'Sabina';
User avatar
Vik_NJ
Уже с Приветом
Posts: 1995
Joined: 29 Dec 2001 10:01
Location: Kiev->...->NYC

Re: Oracle SQL interview

Post by Vik_NJ »

Sabina wrote:
Так?

Code: Select all

CREATE SEQUENCE people_seq
    MINVALUE 1
    MAXVALUE 1000
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

    SELECT people_seq.nextval, id, firstname, secondname
    FROM people
    WHERE firstname = 'Sabina';

Не-а, не так. Без sequences :nono#: Постарайтесь ограничиться select-ом , независимым от базы данных, т. е. чтобы работал и под MSSql и под Sybase. :)
I may grow old, but I refuse to grow up!
User avatar
vlad12345
Уже с Приветом
Posts: 605
Joined: 14 Feb 2002 10:01
Location: Russia

Post by vlad12345 »

Кстати, неплохая формулировка 3-й нф для легкого запоминания:
Attributes depend on the key, the whole key and nothing but the key, so help me Codd.
vc
Уже с Приветом
Posts: 664
Joined: 05 Jun 2002 01:11

Re: Oracle SQL interview

Post by vc »

Vik_NJ wrote:....
Вот Вам задачка,
дана таблица
people ( id int,
firstname varchar2(30),
lastname varchar2(30),
...
)
Вам нужно не используя rownum вывести пронумерованный список (seqnumber, firstname, lastname, id) всех, у кого first name = "Sabina" :D seqnumber должен = 1 для первой записи, 2 для второй и т.д..


The problem, as stated, does not have a solution because the order is not specified.

Assuming the order is defined as ascending by 'id' and 'id' is unique, one can do :

Code: Select all


either

select seqnumber,
          fistname,
          secondname
from people a,
(select a.id, a.fistname, count(*) seqnumber
from people a, people b
where a.id>=b.id
    and a.firstname=b.firstname
group by a.id, a.fistname) b
where a.id=b.id
   and firstname=<whatever>

... or

select
  (select count(*) from t1 where a.id>=id and a.firstname=firstname) seqnumber,
  firstname,
  secondname
from people a where firstname=<whatever>


For sufficiently large tables, neither solution is very useful due to performance implications. One should use 'rownum' in Oracle and temp tables elsewhere.

VC
Rain_Man
Posts: 9
Joined: 13 May 2004 14:52
Location: Ukraine,Kiev

Re: Oracle SQL interview

Post by Rain_Man »

Vik_NJ wrote:Вам нужно не используя rownum вывести пронумерованный список (seqnumber, firstname, lastname, id) всех, у кого first name = "Sabina" :D seqnumber должен = 1 для первой записи, 2 для второй и т.д..

В Oracle можно так:

Code: Select all

select row_number() over(partition by firstname order by firstname) seqnumber, firstname, lastname, id from people
RainMan
lozzy
Уже с Приветом
Posts: 2435
Joined: 12 Jun 2001 09:01

Re: Oracle SQL interview

Post by lozzy »

Ну вот задачки (пытался написать в стиле вопросов MS-овских тестов на MCAD ;) ):

Вы работаете на фирму D-B-Telecom, которая предоставляет услуги IP-телефонии для частных и корпоративных пользователей. До некоторого времени прирост клиентской базы был стабилен и фирма планомерно расширялась. Но, с недавних пор, прирост клиентской базы остановился, и, в некоторые моменты был отрицательным. Руководство компании обеспокоенное данным положением вещей дало указание отделу маркетинга проинвестигировать ситуацию и выдать условия для ряда маркетинговых акций, которые, однако же, не должны существенно повлиять на уровень прибыли компании. Разумеется, отдел маркетинга обратился к Вам, как ведущему разработчику (и DBA одновременно ;) lol ) с просьбой о помощи.

Итак, исходные данные: Все звонки, осуществляемые вышеозначенными пользователями аккуратно логгируются в корпоративной базе данных. Для упрощения задачи примем, что таблица с логом звонков имеет следующую структуру:

create table Calls (
CustomerID int,
CallDate datetime,
CallDuration int)

а таблица кастомеров создается с помощью следующего DDL-скрипта:

create table Customers (
CustomerID int,
ContractStarted datetime,
ContractFinished datetime,
IsCorporate bit,
Name varchar(150)
)

пояснения: IsCorporate = 1, если это корпоративный клиент, ContractFinished является NULL до тех пор, пока кастомер не расторгает договор.

Для того, что бы понять, какие маркетиновые акции будут иметь наименьший impact на profit компании, отделу маркетинга требуется знать:
1) гистограмму распределения кол-ва звонков за последние три месяца по дням недели (в какие дни недели меньше всего звонков)
2) гистограмму распределения кол-ва звонков по часам (в какие часы меньше всего звонят, so, делая скидки на такие часы компания потеряет меньше денег) за последние 6 месяцев для корпоративных и частных пользователей отдельно (желательно в один запрос ;) )

Компания заинтересована в возврате ушедших клиентов, поэтому наши маркетологи предлагают создать льготные условия возвращающимся клиентам и совершенно особенные условия для "дорогих" возвращающихся клиентов, соответственно, им нужно:
3) вывести список названий _компаний_ (не частных пользователей), которые разорвали контракт с нашей фирмой в течении предыдущих 3-х месяцев и которые наговорили не менее 10000 минут (нам не интересны малоговорящие компании для данного предложения) в течении всего срока действия договора, что бы послать им специальные предложения для "возвращающихся клиентов"

Кроме того, компания намерена провести снижение цен для наиболее ценных текущих клиентов, поэтому маркетологам необходимы следующие данные:
4) вывести название всех компаний-клиентов, а так же кол-во выговоренных минут, которые за последние пол-года наговорили не менее 10000, 5000, 1000 минут в месяц (вероятно здесь требуется создать сторед просиджер, что бы не делать 3 разных запроса).
5) ... ну устал я, народ может чего еще придумает. это же так просто ;)
Steel helmet protects your teeth from the morning to the evening.
vc
Уже с Приветом
Posts: 664
Joined: 05 Jun 2002 01:11

Re: Oracle SQL interview

Post by vc »

Rain_Man wrote:
Vik_NJ wrote:Вам нужно не используя rownum вывести пронумерованный список (seqnumber, firstname, lastname, id) всех, у кого first name = "Sabina" :D seqnumber должен = 1 для первой записи, 2 для второй и т.д..

В Oracle можно так:

Code: Select all

select row_number() over(partition by firstname order by firstname) seqnumber, firstname, lastname, id from people


This won't work in Sybase.

VC
NNemo
Уже с Приветом
Posts: 1935
Joined: 15 Sep 2003 17:49
Location: Ukraine, Mariupol -> USA everywhere :-)

Post by NNemo »

Siberian Cableman wrote:Вот что я спрашиваю на интервью для QA-ев:

Дано 3-и таблицы:
CREATE TABLE emp (id int, name varchar(32));
CREATE TABLE sal (id int, salary int);
CREATE TABLE org (id int, manid int);

id - в данных таблицах уникальное и однозначно определяет работника. В одной таблице - зарплату, в другой eго имя. Третья таблица определяет связи начальник - подчинненый. Граф, непомню как это называется в математике (если кто знает скажите :-)) такой, что у одного работника может быть только один начальник.

Ну а дальше конкретные вопросы:
1. Вывести табличку Имя, Зарплата для народа с зарплатой больше определенной суммы.
2. Вывести Имена и Зарплату всех подчинненых начальника с именем 'Том' которые получают зарплату больше определенной суммы.
3. Вывести Имена и Зарплату всех подчинненых начальника с именем 'Том' которые получают зарплату меньше определенной суммы. При этом вывести имена/зарплату 5-и самых низкооплачиваемых, т.е. от нуля и выше.

Немного вопросов потрепатся:
4. Ваши предложения по оптимизации исходных таблиц, и возможной оптимизации ответов на вопрос 2 и 3.
5. Написать Хранимую Процедуру в качестве ответа на 2 или 3 вопрос с Именем начальника/Суммой зарплаты в качестве входных параметров.

Ну и тестерский вопрос:
6. Как будeте тестировать SP из вопроса 5.

Можете использовать хоть PL хоть T- SQL. Мне все равно, я не того ни другого ни не знаю :-). Пока никто дальше 1-ого вопроса и немного потрепаться на эту тему ничего не написал.


I guess it must be one table only. That will be the biggest optimization you can do here :-)
User avatar
FatCat
Уже с Приветом
Posts: 3153
Joined: 05 Sep 2000 09:01
Location: RU -> NJ -> MA -> NY

Post by FatCat »

Sabina wrote:

Code: Select all

select name, salary from emp e, sal s, org o where e.id=s.id and salary > 30 and o.id=e.id and o.manid = (select id from emp where name='Tom');



Я бы заменил на

Code: Select all

... and o.manid in (select id from emp where name='Tom');


Начальников Томов может быть больше одного.

А вообще это не три, а одна таблица :)
Мы сеем по всей земле радость, а какие-то сволочи срывают ее и курят!
Rain_Man
Posts: 9
Joined: 13 May 2004 14:52
Location: Ukraine,Kiev

Post by Rain_Man »

Sabina wrote:

Code: Select all

select * from (select name, salary from emp e, sal s, org o where e.id=s.id and salary < 30 and o.id=e.id and o.manid = (select id from emp where name='Tom')) where rownum < 6;



Там еще в вопросе по-моему было самых низких зарплат:

Code: Select all

select * from (select name, salary from emp e, sal s, org o where e.id=s.id and salary < 30 and o.id=e.id and o.manid in (select id from emp where name='Tom') [b]order by salary[/b]) where rownum < 6;
RainMan
Бродяга
Уже с Приветом
Posts: 16086
Joined: 22 Apr 2003 17:57
Location: Колыбель

Post by Бродяга »

Sabina wrote:SQL> SET SERVEROUTPUT ON
SQL> EXECUTE stat_sp('Tom',30);

Sabina, немножко не в тему, но у меня тут интервью было с BoFa, задали интересный вопрос. А если вы сделали в sql+ SET SERVEROUTPUT ON, а процедура не выводит ничего на экран, то что еще можно сделать что-бы результат печатлся. Сказал что скорее всего execute dbms_output.enable(1000);
Мужик сказал ага, правильно:-) Ну и заместо 1000 можно поставить любой буффер.
Бог создал людей разными, Линкольн дал людям свободу, а Кольт всех уравнял.
User avatar
Sabina
Уже с Приветом
Posts: 5669
Joined: 13 Oct 2000 09:01
Location: East Bay, CA

Post by Sabina »

Бродяга wrote:Sabina, немножко не в тему, но у меня тут интервью было с BoFa, задали интересный вопрос.


Cпасибо за подсказку. До такого я бы точно сама не догадалась, тут опыт нужен

Return to “Вопросы и новости IT”