SQL1

     Смотрите www.spetsmedpribor.net гониоскоп -Краснов -Гольдман. |   

РАБОТА СО SQL


1. Какое поле таблицы Заказчиков является первичным ключом?

2. Что является столбцом 4 из таблицы Заказчиков?

3. Как по другому называется строка? Столбец?

4. Почему вы не можете запрашивать для просмотра первые пять строк таблицы?

(См. ответы в Приложении A.)


  1. Какое наибольшее основное различие между типами данных в SQL?

  • Распознает ANSI тип данных DATA?
  • Какой подраздел SQL используется, чтобы помещать значения в таблицы?
  • Что такое - ключевое слово?
  • (См. ответы в Приложении A.)






    1. Напишите команду SELECT, которая вывела бы номер заказа, сумму и дату для всех строк из таблицы Заказов.

  • Напишите запрос, который вывел бы все строки из таблицы Заказчиков, для которых номер продавца = 1001.
  • Напишите запрос, который вывел бы таблицу со столбцами в следующем порядке: city, sname, snum, comm.
  • Напишите команду SELECT, которая вывела бы оценку (rating), сопровождаемую именем каждого заказчика в San Jose.
  • Напишите запрос, который вывел бы значения snum всех продавцов в текущем заказе из таблицы Заказов без каких бы то ни было повторений.
  • (См. ответы в Приложении A.)




    1. Напишите запрос, который может выдать вам все заказы со значениями суммы выше $1,000.

  • Напишите запрос, который может выдать вам поля sname и city для всех продавцов в Лондоне с комиссионными выше .10.
  • Напишите запрос к таблице Заказчиков, чей вывод включит всех заказчиков с оценкой =< 100, если они не находятся в Риме.
  • Что может быть выведено в результате следующего запроса?
  • SELECT * FROM Orders WHERE (amt < 1000 OR NOT (odate = 10/03/1990 AND cnum > 2003));

  • Что может быть выведено в результате следующего запроса?
  • SELECT * FROM Orders WHERE NOT ((odate = 10/03/1990 OR snum > 1006) AND amt > = 1500 );

  • Как можно проще переписать такой запрос?
  • SELECT snum, sname, city, comm FROM Salespeople WHERE (comm > + .12 OR comm < .14);

    (См. ответы в Приложении A.)




    1. Напишите два запроса, которые могли бы вывести все заказы на 3 или 4 октября 1990.

    2. Напишите запрос, который выберет всех заказчиков, обслуживаемых продавцами Peel или Motika. (Подсказка: из наших типовых таблиц поле snum связывает вторую таблицу с первой.)

    3. Напишите запрос, который может вывести всех заказчиков, чьи имена начинаются с буквы, попадающей в диапазон от A до G.

    4. Напишите запрос, который выберет всех пользователей, чьи имена начинаются с буквы C.

    5. Напишите запрос, который выберет все заказы, имеющие нулевые значения или NULL в поле amt (сумма).

      (См. ответы в Приложении A.)




      1. Напишите запрос, который сосчитал бы все суммы продаж на 3 октября.

    6. Напишите запрос, который сосчитал бы число различных не-NULL-значений поля city в таблице Заказчиков.
    7. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
    8. Напишите запрос, который выбирал бы в алфавитном порядке заказчиков, чьи имена начинаются с буквы G.
    9. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.
    10. Напишите запрос, который сосчитал бы число заказчиков, регистрирующих каждый день свои заказы. (Если продавец имел более одного заказа в данный день, он должен учитываться только один раз.)
    11. (См. ответы в Приложении A.)




      1. Предположим, что каждый продавец имеет 12% комиссионных. Напишите запрос к таблице Заказов, который выведет номер заказа, номер продавца и сумму комиссионных продавца по этому заказу.

    12. Напишите запрос к таблице Заказчиков, который мог бы найти высший рейтинг в каждом городе. Вывод должен быть в такой форме:
    13. For the city (city), the highest rating is: (rating).

    14. Напишите запрос, который выводил бы список заказчиков в нисходящем порядке. Вывод поля оценки/рейтинга (rating) должен сопровождаться именем заказчика и его номером.
    15. Напишите запрос, который выводил бы общие заказы на каждый день и помещал результаты в нисходящем порядке.
    16. (См. ответы в Приложении A.)




      1. Напишите запрос, который вывел бы список номеров заказов сопровождающихся именем заказчика, который создавал эти заказы.

    17. Напишите запрос, который выдавал бы имена продавца и заказчика для каждого заказа после номера заказа.
    18. Напишите запрос, который выводил бы всех заказчиков, обслуживаемых продавцом с комиссионными выше 12%. Выведите имя заказчика, имя продавца и ставку комиссионных продавца.
    19. Напишите запрос, который вычислил бы сумму комиссионных продавца для каждого заказа заказчика с оценкой выше 100.
    20. (См. ответы в Приложении A.)




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

    21. Напишите запрос, который выведет все пары заказов по данным заказчикам, имена этих заказчиков и исключит дубликаты из вывода, как в предыдущем вопросе.
    22. Напишите запрос, который вывел бы имена (cname) и города (city) всех заказчиков.
    23. (Ответы см. в Приложении А.)




      1. Напишите запрос, который использовал бы подзапрос для получения всех заказов для заказчика с именем Cisneros. Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum.

      2. Напишите запрос, который вывел бы имена и оценки всех заказчиков, имеющих усреднённые заказы.

      3. Напишите запрос, который выбрал бы общую сумму всех приобретений в заказах для каждого продавца, у которого эта общая сумма больше, чем сумма наибольшего заказа в таблице.

      (См. ответы в Приложении A.)




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

      2. Напишите два запроса, которые выберут всех продавцов (по их имени и номеру), имеющих в своих городах заказчиков, которых они не обслуживают. Один запрос - с использованием объединения, а другой - с соотнесённым подзапросом. Которое из решений будет более изящным?

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

      (См. ответы в Приложении A.)




      1. Напишите запрос, который использовал бы оператор EXISTS для извлечения всех продавцов, имеющих заказчиков с оценкой 300.

      2. Как бы вы решили предыдущую проблему, используя объединение?

      3. Напишите запрос, использующий оператор EXISTS, который выберет всех продавцов с заказчиками, размещёнными в их городах, которые ими не обслуживаются.

      4. Напишите запрос, который извлекал бы из таблицы Заказчиков каждого заказчика, назначенного продавцу, который в данный момент имеет по крайней мере ещё одного заказчика (кроме заказчика, которого вы выберете) с заказами в таблице Заказов (подсказка: это может быть похоже на структуру в примере с нашим трехуровневым подзапросом).

      (См. ответы в Приложении A.)




      1. Напишите запрос, который выбирал бы всех заказчиков, чьи оценки равны или больше, чем любая (ANY) оценка заказчика Serres.

      2. Что будет выведено вышеупомянутой командой?

      3. Напишите запрос, использующий ANY или ALL, который находил бы всех продавцов, которые не имеют никаких заказчиков, живущих в их городе.

      4. Напишите запрос, который выбирал бы все заказы с суммой, больше, чем любая (в обычном смысле) для заказчиков в Лондоне.

      5. Напишите предыдущий запрос с использованием MAX.

      (См. ответы в Приложении A.)




      1. Создайте объединение из двух запросов, которое показало бы имена, города и оценки всех заказчиков. Те из них, которые имеют поле rating=200 и более, должны, кроме того, иметь слова - "Высокий Рейтинг", а остальные должны иметь слова "Низкий Рейтинг".

      2. Напишите команду, которая вывела бы имена и номера каждого продавца и каждого заказчика, которые имеют больше чем один текущий заказ. Результат представьте в алфавитном порядке.

      3. Сформируйте объединение из трех запросов. Первый выбирает поля snum всех продавцов в San Jose; второй, поля cnum всех заказчиков в San Jose; и третий - поля onum всех заказов на 3 октября. Сохраните дубликаты между последними двумя запросами, но устраните любую избыточность вывода между каждым из них и самым первым.

      (Примечание: в данных типовых таблицах не содержится никакой избыточности. Это только пример.)

      (См. ответы в Приложении A.)




      1. Напишите команду, которая поместила бы следующие значения в указанном заказе в таблицу Продавцов:

      city - San Jose, name - Bianco, comm - NULL, cnum - 1100.

      2. Напишите команду, которая удалила бы все заказы заказчика Clemens из таблицы Заказов.

      3. Напишите команду, которая увеличила бы оценку всех заказчиков в Риме на 100.

      4. Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

      (См. ответы в Приложении A.)




      1. Предположите, что имеется таблица Multicust с такими же именами столбцов, что и в таблице Продавцов. Напишите команду, которая вставила бы всех продавцов (из таблицы Продавцов), имеющих более чем одного заказчика, в эту таблицу.

      2. Напишите команду, которая удаляла бы всех заказчиков не имеющих текущих заказов.

      3. Напишите команду, которая увеличила бы на двадцать процентов комиссионные всех продавцов, имеющих сумму текущих заказов выше $3,000.

      (См. ответы в Приложении A.)




      1. Напишите предложение CREATE TABLE, которое вывело бы нашу таблицу Заказчиков.

      2. Напишите команду, которая давала бы возможность пользователю быстро извлекать заказы, сгруппированные по датам, из таблицы Заказов.

      3. Если таблица Заказов уже создана, как вы можете заставить поле onum быть уникальным (если допустить что все текущие значения уникальны)?

      4. Создайте индекс, который разрешал бы каждому продавцу быстро отыскивать его заказы, сгруппированные по датам.

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

      (См. ответы в Приложении A.)




      1. Создайте таблицу Заказов так, чтобы все значения полей onum, а также все комбинации полей cnum и snum, отличались друг от друга и чтобы значения NULL исключались из поля даты.

      2. Создайте таблицу Продавцов так, чтобы комиссионные по умолчанию составляли 10%, не разрешались значения NULL, поле snum являлось первичным ключом и чтобы все имена были в алфавитном порядке между A и M включительно (учитывая, что все имена будут напечатаны в верхнем регистре).

      3. Создайте таблицу Заказов учётом того, что поле onum больше, чем поле cnum, а cnum больше, чем snum. Запрещены значения NULL в любом из этих трех полей.

      (См. ответы в Приложении A.)




      1. Создайте таблицу с именем Cityorders. Она должна содержать такие же поля onum, amt и snum, что и таблица Заказов, и такие же поля cnum и city, что и таблица Заказчиков, так что заказ каждого заказчика будет вводиться в эту таблицу вместе с его городом. Поле оnum будет первичным ключом Cityorders. Все поля в Cityorders должны иметь ограничения при сравнении с таблицами Заказчиков и Заказов. Допускается, что родительские ключи в этих таблицах уже имеют соответствующие ограничения.

      2. Усложним проблему. Переопределите таблицу Заказов следующим образом: добавьте новый столбец с именем prev, который будет идентифицирован для каждого заказа, поле onum предыдущего заказа для этого текущего заказчика. Выполните это с использованием внешнего ключа, ссылающегося на саму таблицу Заказов. Внешний ключ должен ссылаться также на поле cnum заказчика, обеспечивающее определенную предписанную связь между текущим порядком и ссылаемым.

      (См. ответы в Приложении A.)




      1. Создайте представление, которое показывало бы всех заказчиков, имеющих самые высокие рейтинги.

      2. Создайте представление, которое показывало бы номер продавца в каждом городе.

      3. Создайте представление, которое показывало бы усреднённый и общий заказы для каждого продавца после его имени. Предполагается, что все имена уникальны.

      4. Создайте представление, которое показывало бы каждого продавца с несколькими заказчиками.

      (См. ответы в Приложении A.)




      1. Какое из этих представлений - модифицируемое?

      #1 CREATE VIEW Dailyorders AS SELECT DISTINCT cnum, snum, onum, odate FROM Orders;

      #2 CREATE VIEW Custotals AS SELECT cname, SUM (amt) FROM Orders, Customers WHERE Orders.cnum = customer.cnum GROUP BY cname;

      #3 CREATE VIEW Thirdorders AS SELECT * FROM Dailyorders WHERE odate = 10/03/1990;

      #4 CREATE VIEW Nullcities AS SELECT snum, sname, city FROM Salespeople WHERE city IS NULL OR sname BETWEEN 'A' AND 'MZ';

      2. Создайте представление таблицы Продавцов с именем Commissions (Комиссионные). Это представление должно включать только поля comm и snum. С помощью этого представления можно будет вводить или изменять комиссионные, но только для значений между .10 и .20.

      3. Некоторые SQL-реализации имеют встроенную константу, представляющую текущую дату, иногда называемую " CURDATE ". Слово CURDATE может, следовательно, использоваться в операторе SQL и заменяться текущей датой, когда его значение станет доступным, с помощью таких команд как SELECT или INSERT. Мы будем использовать представление таблицы Заказов с именем Entryorders для вставки строк в таблицу Заказов. Создайте таблицу заказов так, чтобы CURDATE автоматически вставлялась в поле odate, если не указано другое значение. Затем создайте представление Entryorders так, чтобы значения не могли быть указаны.

      (См. ответы в Приложении A.)




      1. Передайте Janet право на изменение оценки заказчика.

      2. Передайте Stephan право передавать другим пользователям право делать запросы в таблице Заказов.

      3. Отнимите привилегию INSERT (ВСТАВКА) в таблице Продавцов у Claire и у всех пользователей, которым она была предоставлена.

      4. Передайте Jerry право вставлять в или модифицировать таблицу Заказчиков с сохранением его возможности оценивать значения в диапазоне от 100 до 500.

      5. Разрешите Janet делать запросы в таблице Заказчиков, но запретите ей уменьшать оценки в той же таблице Заказчиков.

      (См. ответы в Приложении A.)




      1. Создайте область базы данных с именем Myspace, которая выделяет 15 процентов своей области для индексов и 40 процентов на расширение строк.

      2. Вы получили право SELECT в таблице Заказов продавца Diane. Введите команду так, чтобы вы могли ссылаться к этой таблице как к "Orders", не используя имя "Diane" в качестве префикса.

      3. Если произойдёт сбой питания, что случится со всеми изменениями, сделанными во время текущей транзакции?

      4. Если вы не можете видеть строку из-за её блокировки, какой это тип блокировки?

      5. Если вы хотите получить общее, максимальное, и усреднённое значения сумм приобретений для всех заказов и не хотите при этом запрещать другим пользоваться таблицей, какой уровень изоляции будет этому соответствовать?

      (См. ответы в Приложении A.)




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

      2. Сделайте запрос каталога, чтобы выяснить, сколько синонимов существует для каждой таблицы в базе данных. Не забудьте, что один и тот же синоним, принадлежащий двум различным пользователям - это фактически два разных синонима.

      3. Выясните, сколько таблиц имеют индексы в более чем пятидесяти процентах их столбцов.

      (См. ответы в Приложении A.)




      Обратите внимание: ответы для этих упражнений написаны в псевдокодах, являющихся английским языком описания логики, которой должна следовать программа. Это сделано для того, чтобы помочь читателям, которые могут быть не знакомы с ПАСКАЛем (или любым другим языком).

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

      Мы опустим из программ всё, что не относится напрямую к рассматриваемым вопросам, например, определение устройств ввода-вывода, подключение к базе данных и так далее. Конечно, имеется много способов выполнения таких упражнений; и совсем не обязательно, что представленные варианты решений являются самыми удачными.

      1. Разработайте простую программу, которая выберет все комбинации полей snum и cnum из таблиц Заказов и Заказчиков, и выясните, всегда ли предыдущая комбинация - такая же, как последующая. Если комбинация из таблицы Заказов не найдена в таблице Заказчиков, значение поля snum для этой строки будет изменено на удовлетворяющее условию совпадения. Вы должны помнить, что курсор с подзапросом модифицируем (ANSI-ограничение также применимо к просмотрам) и что базисная целостность базы данных это не тоже самое, что проверка на ошибку (т.е. первичные ключи уникальны, все поля cnums в таблице Заказов правильны, и так далее). Проверьте раздел объявлений и убедитесь, что там объявлены все используемые курсоры.

      2. Предположим, что ваша программа предписывает ANSI запрещение курсоров или просмотров, использующих модифицируемые подзапросы. Как вы должны изменить вышеупомянутую программу?

      3. Разработайте программу, которая подсказывает пользователям изменить значения поля city продавца, автоматически увеличивает комиссионные на .01 для продавца, переводимого в Барселону, и уменьшает их на .01 для продавца, переводимого в Сан-Хосе. Кроме того, продавец находящийся в Лондоне, должен потерять .02 из своих комиссионных, независимо от того, меняет он город, или нет, в то время как продавец, не находящийся в Лондоне должен иметь увеличение комиссионных на .02. Изменение в комиссионных, основывающееся на нахождении продавца в Лондоне, может применяться независимо от того, куда тот переводится. Выясните, может ли поле city или поле comm содержать NULL-значения, и обработайте их, как это делается в SQL.

      Предупреждение! Эта программа имеет некоторые сокращения.

      (См. ответы в Приложении A.)



      Содержание раздела