Какая из этих строковых функций sql допустима

Эта функция является обратной самой себе (т.е. pseudo_encrypt(pseudo_encrypt(x)) = x ). Точное воспроизведение функции является своего рода безопасностью через неясность, и если кто-нибудь догадается, что вы использовали сеть Фейстеля из документации PostgreSQL, то ему будет легко получить исходную последовательность. Однако вместо (((1366 * r1 + 150889) % 714025) / 714025.0) можно использовать другую функцию с областью значений от 0 до 1, например, просто поэкспериментировать с числами в предыдущем выражении.

Вот, как использовать pseudo_encrypt:

CREATE SEQUENCE my_table_seq; CREATE TABLE my_table ( short_id int NOT NULL DEFAULT pseudo_encrypt( nextval('my_table_seq')::int ), -- другие колонки … UNIQUE (short_id) );

Такое решение сохраняет случайные значения в столбце short_id , если же важно поддерживать высокие скорости обработки данных, то можно хранить в таблице саму инкрементную последовательность и преобразовывать её при запросе отображения с помощью pseudo_encrypt . Как мы увидим позже, индексирование рандомизированных значений может привести к увеличению объёма записи.

В предыдущем примере для short_id использовались целые значения обычного размера, для bigint есть другие функции Фейстеля, например XTEA.

Ещё один способ запутать последовательность целых чисел заключается в преобразовании её в короткие строки. Попробуйте воспользоваться расширением pg_hashids:

CREATE EXTENSION pg_hashids; CREATE SEQUENCE my_table_seq; CREATE TABLE my_table ( short_id text NOT NULL DEFAULT id_encode( nextval('my_table_seq'), ' long string as table-specific salt ' ), -- другие колонки … UNIQUE (short_id) ); INSERT INTO my_table VALUES (DEFAULT), (DEFAULT), (DEFAULT); SELECT * FROM my_table; /* ┌──────────┐ │ short_id │ ├──────────┤ │ R4 │ │ ya │ │ Ll │ └──────────┘ */

Здесь снова будет быстрее хранить в таблице сами целые числа и преобразовывать их по запросу, но замерьте производительность и посмотрите, имеет ли это смысл на самом деле.

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

-- Редкий пример таблицы: нет потенциальных естественных ключей, -- которые можно объявить вместе с искусственным ключом "code" CREATE TABLE coupons ( code text NOT NULL, amount numeric(5,2) NOT NULL, redeemed boolean NOT NULL DEFAULT false, UNIQUE (code) );

Если у вас есть искусственный ключ и вы не объявляете естественные ключи, когда они существуют, то оставляете последние незащищёнными:

CREATE TABLE cars ( car_id bigserial NOT NULL, vin varchar(17) NOT NULL, year int NOT NULL, UNIQUE (car_id) -- нужно было добавить -- UNIQUE (vin) ); -- К сожалению, это успешно выполнится INSERT INTO cars (vin, year) VALUES ('1FTJW36F2TEA03179', 1996), ('1FTJW36F2TEA03179', 1997);

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

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

Суррогатные ключи

Как уже упоминалось, важный тип искусственного ключа называется «суррогатный ключ». Он не должен быть кратким и передаваемым, как другие искусственные ключи, а используется как внутренняя метка, всегда идентифицирующая строку. Он используется в SQL, но приложение не обращается к нему явным образом.

Если вам знакомы системные колонки (system columns) из PostgreSQL, то вы можете воспринимать суррогаты почти как параметр реализации базы данных (вроде ctid), который однако никогда не меняется. Значение суррогата выбирается один раз для каждой строки и потом никогда не изменяется.

Суррогатные ключи отлично подходят в качестве внешних ключей, при этом необходимо указать каскадные ограничения ON UPDATE RESTRICT , чтобы соответствовать неизменности суррогата.

С другой стороны, внешние ключи к публично передаваемым ключам должны быть помечены ON UPDATE CASCADE , чтобы обеспечить максимальную гибкость. (Каскадное обновление выполняется на том же уровне изоляции, что и окружающая его транзакция, поэтому не беспокойтесь о проблемах с параллельным доступом – база данных справится, если выбрать строгий уровень изоляции.)

Не делайте суррогатные ключи «естественными». Как только вы покажете значение суррогатного ключа конечным пользователям, или, что хуже, позволите им работать с этим значением (в частности через поиск), то фактически придадите ключу значимость. Потом показанный ключ из вашей базы данных может стать естественным ключом в чьей-то чужой БД.

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

Автоинкрементные bigint

Чаще всего для суррогатных ключей используют автоинкрементную колонку «bigserial», также известную как IDENTITY . (На самом деле, PostgreSQL 10 теперь, как и Oracle, поддерживает конструкцию IDENTITY, см. CREATE TABLE.)

Однако, я считаю, что автоинкрементное целое плохой выбор для суррогатных ключей. Такое мнение непопулярно, поэтому позвольте мне объясниться.

Недостатки последовательных ключей:

• Если все последовательности начинаются с 1 и постепенно увеличиваются, то у строк из разных таблиц будут одинаковые значения ключей. Такой вариант неидеален, предпочтительнее все же использовать непересекающиеся множества ключей в таблицах, чтобы, например, запросы не смогли бы случайно перепутать константы в JOIN и вернуть неожиданные результаты. (Как вариант для обеспечения отсутствия пересечений, можно составить каждую последовательность из чисел, кратных различным простым, но это будет довольно трудоёмко.)
• Вызов nextval() для генерации последовательности в современных распределённых SQL, приводит к тому, что вся система хуже масштабируется.
• Поглощение данных из базы данных, в которой тоже использовались последовательные ключи, приведет к конфликтам, потому что последовательные значения не будут уникальными в разных системах.
• С философской точки зрения последовательное увеличение чисел связано со старыми системами, в которых подразумевался порядок строк. Если же вы теперь хотите упорядочить строки, то делайте это явным образом, с помощью колонки меток времени или чего-то имеющего смысл в ваших данных. В противном случае нарушается первая нормальная форма.
• (Слабая причина, но) эти короткие идентификаторы так и тянет сообщить кому-нибудь.

UUID

Давайте рассмотрим другой вариант: использование больших целых чисел (128-битных), генерируемых в соответствии со случайным шаблоном. Алгоритмы генерации таких универсальных уникальных идентификаторов (universally unique identifier, UUID) имеют чрезвычайно малую вероятность выбора одного значения дважды, даже при одновременном выполнении на двух разных процессорах.

В таком случае, UUID кажутся естественным выбором для использования в качестве суррогатных ключей, не правда ли? Если вы хотите пометить строки уникальным образом, то ничто не сравнится с уникальной меткой!

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

Для начала, расскажу о надуманных причинах. Некоторые люди думают, что UUID — это строки, потому что они записываются в традиционном шестнадцатеричном виде с дефисом: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. Действительно, некоторые базы данных не имеют компактного (128-битного) типа uuid, но в PostgreSQL он есть и имеет размер двух bigint, т.е., по сравнению с объёмом прочей информации в базе данных, издержки незначительны.

Ещё UUID незаслуженно обвиняется в громоздкости, но кто будет их произносить, печатать или читать? Мы говорили, что это имеет смысл для показываемых искусственных ключей, но никто (по определению) не должен увидеть суррогатный UUID. Возможно, с UUID будет иметь дело разработчик, запускающий команды SQL в psql для отладки системы, но на этом всё. А разработчик может ссылаться на строки и с помощью более удобных ключей, если они заданы.

Реальная проблема с UUID в том, что сильно рандомизированные значения приводят к увеличению объёма записи (write amplification) из-за записей полных страниц в журнал с упреждающей записью (write-ahead log, WAL). Однако, на самом деле снижение производительности зависит от алгоритма генерации UUID.

Давайте измерим write amplification. По правде говоря, проблема в старых файловых системах. Когда PostgreSQL выполняет запись на диск, она изменяет «страницу» на диске. При отключении питания компьютера большинство файловых систем всё равно сообщит об успешной записи ещё до того, как данные безопасно сохранились на диске. Если PostgreSQL наивно воспримет такое действие завершённым, то при последующей загрузке системы база данных будет повреждена.

Раз PostgreSQL не может доверять большинству ОС/файловых систем/конфигураций дисков в вопросе обеспечения неразрывности, база данных сохраняет полное состояние изменённой дисковой страницы в журнал с упреждающей записью (write-ahead log), который можно будет использовать для восстановления после возможного сбоя. Индексирование сильно рандомизированных значений наподобие UUID обычно затрагивает кучу различных страниц диска и приводит к записи полного размера страницы (обычно 4 или 8 КБ) в WAL для каждой новой записи. Это так называемая полностраничная запись (full-page write, FPW).

Некоторые алгоритмы генерации UUID (такие, как «snowflake» от Twitter или uuid_generate_v1() в расширении uuid-ossp для PostgreSQL) создают на каждой машине монотонно увеличивающиеся значения. Такой подход консолидирует записи в меньшее количество страниц диска и снижает FPW.

Давайте измерим влияние FPW для различных алгоритмов генерации UUID, а также исследуем статистику WAL. Я использовал следующую конфигурацию для замера.

• Экземпляр EC2 с запущенным ami-aa2ea6d0
  • Ubuntu Server 16.04 LTS (HVM)
  • EBS General Purpose (SSD)
  • c3.xlarge
  • vCPU: 4
  • RAM GiB: 7.5
  • Disk GB: 2 x 40 (SSD)
  • ftp.postgresql.org/pub/source/v10.1/postgresql-10.1.tar.gz
  • ./configure --with-uuid=ossp CFLAGS="-O3"
  • max_wal_size=‘10GB’;
  • checkpoint_timeout=‘2h’;
  • synchronous_commit=‘off’;

  CREATE EXTENSION "uuid-ossp"; CREATE EXTENSION pgcrypto; CREATE TABLE u_v1 ( u uuid PRIMARY KEY ); CREATE TABLE u_crypto ( u uuid PRIMARY KEY );

  Перед тек, как добавить UUID в каждую таблицу, находим текущую позицию write-ahead log.

  SELECT pg_walfile_name(pg_current_wal_lsn()); /* Например, pg_walfile_name -------------------------- 000000010000000000000001 */

  Я использовал такую позицию, чтобы получить статистику об использовании WAL после проведения бенчмарка. Так мы получим статистику событий, выполняемых последовательно после начальной позиции:

  pg_waldump --stats 000000010000000000000001

  Я провёл тесты трёх сценариев:

  1. Добавление UUID, сгенерированных алгоритмом gen_random_uuid() (pgcrypto)
  2. Добавление из uuid_generate_v1() (предоставленного [uuid-ossp] (https://www.postgresql.org/docs/10/static/uuid-ossp.html)
  3. Снова добавление из gen_random_uuid() , но теперь с параметром full_page_writes='off' в конфигурации БД. Это покажет, насколько всё будет быстрее без увеличения FPW.

  -- например, я выполнял это в psql 16 раз с параметром \timing INSERT INTO u_crypto ( SELECT gen_random_uuid() FROM generate_series(1, 1024*1024) );

  И вот результаты замеров скорости:

  

  График скорости вставки UUID

  Вот статистика WAL для каждого из способов:

  gen_random_uuid() Тип N (%) Размер записи (%) Размер FPI (%) ---- - --- ----------- --- -------- --- XLOG 260 ( 0.15) 13139 ( 0.09) 484420 ( 30.94) Heap2 765 ( 0.45) 265926 ( 1.77) 376832 ( 24.07) Heap 79423 ( 46.55) 6657121 ( 44.20) 299776 ( 19.14) Btree 89354 ( 52.37) 7959710 ( 52.85) 404832 ( 25.85) uuid_generate_v1() Тип N (%) Размер записи (%) Размер FPI (%) ---- - --- ----------- --- -------- --- XLOG 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Heap2 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Heap 104326 ( 49.88) 7407146 ( 44.56) 0 ( 0.00) Btree 104816 ( 50.12) 9215394 ( 55.44) 0 ( 0.00) gen_random_uuid() with fpw=off Тип N (%) Размер записи (%) Размер FPI (%) ---- - --- ----------- --- -------- --- XLOG 4 ( 0.00) 291 ( 0.00) 64 ( 0.84) Heap2 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) Heap 107778 ( 49.88) 7654268 ( 46.08) 0 ( 0.00) Btree 108260 ( 50.11) 8956097 ( 53.91) 7556 ( 99.16)

  Результаты подтверждают, что gen_random_uuid создаёт существенную активность в WAL из-за полностраничных образов (full-page images, FPI), а другие способы этим не страдают. Конечно, в третьем методе я просто запретил базе данных делать это. Однако запрет FPW совсем не то, что стоило бы использовать в реальности, если только вы не полностью уверены в файловой системе и конфигурации дисков. В этой статье утверждается, что ZFS может быть безопасным для отключения FPW, но пользуйтесь им с осторожностью.

  Явным победителем в моём бенчмарке оказался uuid_generate_v1() – он быстр и не замедляется при накоплении строк. Расширение uuid-ossp по умолчанию установлено в таких облачных базах данных, как RDS и Citus Cloud, и будет доступно без дополнительных усилий.

  В документация есть предупреждение о uuid_generate_v1:

  В нём используется MAC-адрес компьютера и метка времени. Учитывайте, что UUID такого типа раскрывают информацию о компьютере, который создал идентификатор, и время его создания, что может быть неприемлемым, когда требуется высокая безопасность.

  Однако я не думаю, что настоящая проблема, потому что суррогатный ключ не передаётся. Если же это всё-таки важно для вас, в библиотеке есть uuid_generate_v1mc() , скрывающий mac-адрес компьютера.

  Итоги и рекомендации

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

  Для каждой таблицы:

  1. Определите и объявите все естественные ключи.
  2. Создайте суррогатный ключ _id типа uuid со значением по умолчанию в uuid_generate_v1() . Можете даже пометить его как первичный ключ. Если добавить в этот идентификатор название таблицы, это упростит JOIN, т.е. получите JOIN foo USING (bar_id) вместо JOIN foo ON (foo.bar_id = bar.id) . Не передавайте этот ключ клиентам и вообще не выводите за пределы базы данных.
  3. Для промежуточных таблиц, через которые происходит JOIN, объявляйте все колонки внешних ключей как единый составной первичный ключ.
  4. При необходимости добавьте искусственный ключ, который можно использовать в URL или других указаниях ссылки на строку. Используйте сетку Фейстеля или pg_hashids, чтобы замаскировать автоинкрементные целые.
  5. Указывайте каскадное ограничение ON UPDATE RESTRICT , используя суррогатные UUID в качестве внешних ключей, а для внешних искусственных ключей – ON UPDATE CASCADE . Выбирайте естественные ключи, исходя из собственной логики.

  Такой подход обеспечивает стабильность внутренних ключей, в то же время допуская и даже защищая естественные ключи. К тому же, видимые искусственные ключи не становятся к чему-либо привязанными. Правильно во всем разобравшись, можно не зацикливаться только на «первичных ключах» и пользоваться всеми возможностями применения ключей.

  Обсуждать подобные профессиональные вопросы мы предлагаем на наших конференциях. Если у вас за плечами большой опыт в ИТ-сфере, наболело, накипело и хочется высказаться, поделиться опытом или где-то попросить совета, то на майском фестивале конференций РИТ++ будут для этого все условия, 8 тематических направлений начиная от фронтенда и мобильной разработки, и заканчивая DevOps и управлением. Подать заявку на выступление можно здесь.

  • Блог компании Конференции Олега Бунина (Онтико)
  • MySQL
  • PostgreSQL
  • SQL
  • Администрирование баз данных

  Синтаксис SQL

  В этой главе описывается синтаксис SQL. Он формирует основу для понимания следующих глав, в которых подробно рассматриваются способы применения команд SQL для определения и изменения данных.

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

  • Лексическая структура
    • Идентификаторы и ключевые слова
    • Константы
      • Строковые константы
      • Строковые константы с экранированием в стиле C
      • Строковые константы с экранированием Unicode
      • Строковые константы в долларовых кавычках
      • Константы битовых строк
      • Числовые константы
      • Константы других типов
      • Ссылки на столбец
      • Позиционные параметры
      • Подзапросы
      • Выбор поля
      • Операторы вызова
      • Вызов функции
      • Агрегатные выражения
      • Вызовы оконных функций
      • Приведение типов
      • Сортировка выражений
      • Скалярные подзапросы
      • Конструкторы массивов
      • Конструкторы строк
      • Правила вычисления выражений
      • Использование позиционной нотации
      • Использование именованных обозначений
      • Использование смешанной нотации

      Лексическая структура

      Предложение на SQL состоит из последовательности команд. Команда состоит из последовательности токенов, оканчивающейся точкой с запятой («;»). Конец потока ввода также завершает команду. Какие токены являются действительными, зависит от синтаксиса конкретной команды.

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

      Например, следующее (синтаксически) допустимый ввод SQL:

      SELECT * FROM MY_TABLE; UPDATE MY_TABLE SET A = 5; INSERT INTO MY_TABLE VALUES (3, 'hi there'); 

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

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

      Синтаксис SQL не очень согласован относительно того, какие токены идентифицируют команды, а какие являются операндами или параметрами. Первые несколько токенов обычно являются именем команды, поэтому в приведенном выше примере мы обычно говорим о командах «SELECT», «UPDATE» и «INSERT». Но, например, команда UPDATE всегда требует, чтобы токен SET появлялся в определенной позиции, и этот конкретный вариант INSERT также требует VALUES для завершения. Точные правила синтаксиса для каждой команды описаны в главе Команды SQL.

      Идентификаторы и ключевые слова

      Токены, такие как SELECT, UPDATE или VALUES в приведенном выше примере, являются примерами ключевых слов, то есть слов, которые имеют фиксированное значение в языке SQL. MY_TABLE и A являются примерами идентификаторов. Они идентифицируют имена таблиц, столбцов или других объектов базы данных в зависимости от команды, в которой они используются. Поэтому их иногда просто называют «именами». Ключевые слова и идентификаторы имеют одинаковую лексическую структуру, что означает, что невозможно знать, является ли токен идентификатором или ключевым словом, не зная языка. Полный список ключевых слов, используемых стандарте SQL и в QHB можно найти в Ключевые слова SQL

      Идентификаторы SQL и ключевые слова должны начинаться с буквы (a - z, но также с букв с диакритическими знаками и нелатинскими буквами) или подчеркивания (_). Последующие символы в идентификаторе или ключевом слове могут быть буквами, подчеркиванием, цифрами (0 - 9) или знаками доллара ($). Обратите внимание, что знаки доллара не допускаются в идентификаторах в соответствии с буквой стандарта SQL, поэтому их использование может сделать приложения менее переносимыми. Стандарт SQL не будет определять ключевое слово, которое содержит цифры, начинается или заканчивается подчеркиванием, поэтому идентификаторы этой формы защищены от возможного конфликта с будущими расширениями стандарта.

      Система использует не более NAMEDATALEN-1 байтов идентификатора; более длинные имена могут быть записаны в командах, но они будут усечены. По умолчанию NAMEDATALEN равен 64, поэтому максимальная длина идентификатора составляет 63 байта.

      Ключевые слова и идентификаторы без кавычек не чувствительны к регистру. Следовательно:

      UPDATE MY_TABLE SET A = 5;

      можно эквивалентно записать как:

      uPDaTE my_TabLE SeT a = 5;

      Часто используемое соглашение заключается в написании ключевых слов в верхнем регистре и имен в нижнем регистре, например:

      UPDATE my_table SET a = 5;

      Существует второй тип идентификатора: идентификатор с разделителями (delimited identifier) или идентификатор в кавычках (quoted identifier). Он формируется путем включения произвольной последовательности символов в двойные кавычки ("). Идентификатор с разделителями всегда является идентификатором, а не ключевым словом. Таким образом, "select" может использоваться для ссылки на столбец или таблицу с именем "select", тогда как select без кавычек был бы взят как ключевое слово и поэтому вызвал бы ошибку синтаксического анализа при использовании там, где ожидается имя таблицы или столбца. Пример может быть написан с помощью заключенных в кавычки идентификаторов как это:

      UPDATE "my_table" SET "a" = 5;

      Идентификаторы в кавычках могут содержать любой символ, кроме символа с нулевым кодом. (Чтобы включить двойные кавычки, напишите две двойные кавычки). Это позволяет создавать имена таблиц или столбцов, которые в противном случае были бы невозможны, например, содержащие пробелы или амперсанды. Ограничение длины все еще применяется.

      Вариант заключенных в кавычки идентификаторов позволяет включать экранированные символы Юникода, идентифицируемые их кодовыми точками. Этот вариант рассмотрен в разделе Строковые константы с экранированием Unicode. Заключение в кавычки идентификатора также делает его чувствительным к регистру, тогда как имена без кавычек всегда свертываются в нижний регистр. Например, идентификаторы FOO, foo и "foo" в QHB считаются одинаковыми, но "Foo" и "FOO" отличаются друг от друга. (Свертывание имен без кавычек в нижний регистр в QHB несовместимо со стандартом SQL, который говорит, что имена без кавычек должны быть приведены в верхний регистр. Таким образом, foo должен быть эквивалентен "FOO", а не "foo" в соответствии со стандартом. Если Вы хотите писать переносимые приложения. Рекомендуется всегда указывать конкретное имя или никогда его не указывать).

      Константы

      В QHB есть три типа неявно типизированных констант: строки, битовые строки и числа. Константы также могут быть указаны с явными типами, которые могут обеспечить более точное представление и более эффективную обработку системой. Эти альтернативы обсуждаются в следующих подразделах.

      Строковые константы

      Строковая константа в SQL — это произвольная последовательность символов, ограниченная одинарными кавычками (’), например, ’This is a string’. Чтобы включить символ одинарной кавычки в строковую константу, напишите две соседние одинарные кавычки, например, ’Dianne”s horse’. Обратите внимание, что это не то же самое, что символ двойной кавычки (").

      Две строковые константы, которые разделены только пробелами хотя бы с одной новой строкой, объединяются и эффективно обрабатываются так, как если бы строка была записана как одна константа. Например:

      SELECT 'foo' 'bar'; 

      неверный синтаксис (Это немного странное поведение определяется SQL и QHB следует стандарту).

      Строковые константы с экранированием в стиле C

      QHB также принимает строковые константы с экранированием, которые являются расширением стандарта SQL. Константная строка с экранированием указывается путем написания буквы E (в верхнем или нижнем регистре) непосредственно перед открывающей одинарной кавычкой, например, E’foo’. (При продолжении строковые константы с экранированием через строки пишите E только перед первой открывающей кавычкой). Внутри строки с экранированием символ обратной косой черты (\) начинает C-подобную escape- последовательность с обратной косой чертой, в которой комбинация обратной косой черты и следующего(их) символа(ов) представляет специальное байтовое значение, как показано в таблице:

      Escape-последовательности с обратной косой чертой

      Escape-последовательность с обратной косой чертой	Описание
      \b	backspace
      \f	form feed
      \n	newline
      \r	carriage return
      \t	tab
      \o, \oo, \ooo (o = 0 - 7)	octal byte value
      \xh, \xhh (h = 0 - 9, A - F)	hexadecimal byte value
      \uxxxx, \Uxxxxxxxx (x = 0 - 9, A - F)	16 or 32-bit hexadecimal Unicode character value

      Любой другой символ после обратной косой черты воспринимается буквально. Таким образом, чтобы включить символ обратной косой черты, напишите две обратные косые черты (\\). Кроме того, одиночная кавычка может быть включена в escape-строку, с помощью \’, в дополнение к обычному способу ''.

      Вы несете ответственность за то, что создаваемые вами последовательности байтов, особенно при использовании восьмеричного или шестнадцатеричного экранирования, составляют допустимые символы в кодировке набора символов сервера. Если кодировкой сервера является UTF-8, вместо этого следует использовать кодировку Unicode или альтернативный синтаксис escape Unicode, описанный в разделе Строковые константы с экранированием Unicode. Альтернативой может быть ручное кодирование UTF-8 и запись байтов, что будет очень громоздким.

      Экранирующий синтаксис Unicode работает полностью, только если кодировка сервера UTF8. При использовании других серверных кодировок можно указывать только кодовые точки в диапазоне ASCII (до \u007F). Как 4-значная, так и 8-значная форма могут использоваться для указания суррогатных пар UTF-16 для составления символов с кодовыми точками, большими, чем U + FFFF, хотя наличие 8-значной формы технически делает это ненужным. (Если суррогатные пары используются, когда кодировкой сервера является UTF8, они сначала объединяются в одну кодовую точку, которая затем кодируется в UTF-8).

      Символ с нулевым кодом не может быть строковой константой.

      Строковые константы с экранированием Unicode

      QHB также поддерживает другой тип синтаксиса с экранированием для строк, который позволяет указывать произвольные символы Юникода по кодовой точке. Константа escape-строки Unicode начинается с U& (заглавная или строчная буква U, за которой следует амперсанд) непосредственно перед открывающей кавычкой, без пробелов между ними, например, U&’foo’. (Обратите внимание, что это создает неоднозначность с оператором &. Используйте пробелы вокруг оператора, чтобы избежать этой проблемы). Внутри кавычек символы Unicode могут быть указаны в экранированной форме путем написания обратной косой черты с последующим четырехзначным шестнадцатеричным номером кодовой точки или в качестве альтернативы обратный слеш, за которым следует знак плюс, за которым следует шестизначный шестнадцатеричный номер кода. Например, строка ’data’ может быть записана как

      U&'d\0061t\+000061' 

      Следующий менее тривиальный пример пишет русское слово «слон» кириллическими буквами:

      U&'\0441\043B\043E\043D' 

      Если требуется другой escape-символ, чем обратная косая черта, его можно указать с помощью оператора UESCAPE после строки, например:

      U&'d!0061t!+000061' UESCAPE '!' 

      Экранирующим символом может быть любой отдельный символ, кроме шестнадцатеричной цифры, знака плюс, одинарной кавычки, двойной кавычки или пробела. Обратите внимание, что escape-символ пишется в одинарных кавычках, а не в двойных.

      Экранирующий синтаксис Unicode работает только в том случае, если кодировка сервера UTF8. При использовании других серверных кодировок можно указывать только кодовые точки в диапазоне ASCII (до \007F). Как 4-значная, так и 6-значная форма могут использоваться для указания суррогатных пар UTF-16 для составления символов с кодовыми точками, большими, чем U + FFFF, хотя технически наличие 6-значной формы делает это ненужным. (Если суррогатные пары используются, когда кодировкой сервера является UTF8, они сначала объединяются в одну кодовую точку, которая затем кодируется в UTF-8).

      Кроме того, escape-синтаксис Unicode для строковых констант работает только при включенном параметре конфигурации standard_conforming_strings. Это связано с тем, что в противном случае этот синтаксис может запутать клиентов, которые анализируют операторы SQL, до такой степени, что это может привести к инъекциям SQL и аналогичным проблемам безопасности. Если параметр отключен, этот синтаксис будет отклонен с сообщением об ошибке.

      Чтобы буквально включить escape-символ в строку, напишите его дважды.

      Строковые константы в долларовых кавычках

      Хотя стандартный синтаксис для указания строковых констант обычно удобен, бывает трудно понять, когда нужная строка содержит много одинарных кавычек или обратной косой черты, поскольку каждая из них должна быть удвоена. Чтобы разрешить более удобочитаемые запросы в таких ситуациях, QHB предоставляет другой способ, называемый «цитирование в долларах», для записи строковых констант. Строковая константа, заключенная в кавычки, состоит из знака доллара ($), необязательного «тега», состоящего из нуля или более символов, другого знака доллара, произвольной последовательности символов, составляющих содержимое строки, знака доллара, того же тега, которым началась эта цитата доллара и знак доллара. Например, вот два разных способа указать строку «Dianne's horse» с использованием долларовых кавычек:

      $$Dianne's horse$$ $SomeTag$Dianne's horse$SomeTag$ 

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

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

      $function$ BEGIN RETURN ($1 ~ $q$[\t\r\n\v\\]$q$); END; $function$ 

      Здесь последовательность $q$[\t\r\n\v\\]$q$ представляет заключенную в доллары текстовую строку [\t\r\n\v\\] , которая будет распознаваться, когда тело функции будет выполняться QHB. Но так как последовательность не соответствует внешнему разделителю долларовых кавычек $function$, это просто еще несколько символов внутри константы, поскольку речь идет о внешней строке.

      Тэг строки в кавычках, если таковой имеется, следует тем же правилам, что и идентификатор без кавычек, за исключением того, что он не может содержать знак доллара. Теги чувствительны к регистру, поэтому $tag$String content$tag$ является правильным, но $TAG$String content$tag$ - нет.

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

      Кавычки в долларах не являются частью стандарта SQL, но они часто являются более удобным способом написания сложных строковых литералов, чем синтаксис с одинарными кавычками, соответствующий стандарту. Это особенно полезно при представлении строковых констант внутри других констант, что часто требуется в определениях процедурных функций. В синтаксисе с одинарными кавычками каждый обратный слеш в приведенном выше примере должен быть записан как четыре обратных слеша, которые будут уменьшены до двух обратных слешей при разборе исходной строковой константы, а затем до одного, когда внутренняя строковая константа будет повторно проанализирована во время выполнения функции.

      Константы битовых строк

      Константы битовых строк выглядят как обычные строковые константы с буквой B (в верхнем или нижнем регистре) непосредственно перед открывающей кавычкой (без пробелов), например, B'1001' . В битовых строковых константах допускаются только символы 0 и 1 .

      В качестве альтернативы константы битовой строки могут быть указаны в шестнадцатеричном формате с использованием начального X (верхний или нижний регистр), например, X'1FF' . Это обозначение эквивалентно константе битовой строки с четырьмя двоичными цифрами для каждой шестнадцатеричной цифры.

      Обе формы константы битовой строки могут быть продолжены между строками так же, как обычные строковые константы. «Цитирование в долларах» нельзя использовать в константе битовой строки.

      Числовые константы

      Числовые константы принимаются в следующих общих формах:

      digits digits.[digits][e[+-]digits] [digits].digits[e[+-]digits] digitse[+-]digits 

      где digits — это одна или несколько десятичных цифр (от 0 до 9). Как минимум одна цифра должна быть до или после десятичной точки, если она используется. По крайней мере одна цифра должна следовать за маркером экспоненты (e), если таковая имеется. В константе не должно быть пробелов или других символов. Обратите внимание, что любой ведущий знак плюс или минус на самом деле не считается частью константы; это оператор, применяемый к константе.

      Вот несколько примеров допустимых числовых констант:

      42 3.5 4. .001 5e2 1.925e-3 

      Числовая константа, которая не содержит ни десятичной точки, ни экспоненты, изначально считается типом integer если ее значение соответствует типу integer (32 бита); в противном случае предполагается, что это тип bigint если его значение соответствует типу bigint (64 бита); в противном случае он считается типом numeric. Константы, которые содержат десятичные точки и/или показатели, всегда изначально считаются numeric.

      Первоначально назначенный тип данных числовой константы является лишь отправной точкой для алгоритмов разрешения типов. В большинстве случаев константа будет автоматически приведена к наиболее подходящему типу в зависимости от контекста. При необходимости вы можете принудительно интерпретировать числовое значение как определенный тип данных путем его приведения. Например, вы можете заставить числовое значение обрабатываться как тип real (float4), написав:

      REAL '1.23' -- string style 1.23::REAL -- PostgreSQL (historical) style 

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

      Константы других типов

      Константа произвольного типа может быть введена с использованием любого из следующих обозначений:

      type 'string' 'string'::type CAST ( 'string' AS type ) 

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

      Строковая константа может быть записана с использованием обычной записи SQL или долларовых кавычек.

      Также можно указать приведение типа с использованием функционально-подобного синтаксиса:

      typename ( 'string' ) 

      но не все имена типов могут быть использованы таким образом; детальную информацию см. в разделе Приведение типов.

      CAST(), «::» и функционально-подобный синтаксис также можно использовать для указания преобразований типов во время выполнения произвольных выражений, как обсуждается в разделе Приведение типов. Чтобы избежать синтаксической неоднозначности, синтаксис type 'string' может использоваться только для указания типа простой литеральной константы. Другое ограничение синтаксиса type type 'string' заключается в том, что он не работает для типов массивов; используйте «::» или CAST() чтобы указать тип константы массива.

      Синтаксис CAST() соответствует SQL. Синтаксис type 'string' является обобщением стандарта: SQL определяет этот синтаксис только для нескольких типов данных, но QHB допускает его для всех типов.

      Операторы

      Имя оператора — это последовательность символов длиной до 63 символов из следующего списка:

      Однако есть несколько ограничений на имена операторов:

      • -- и /* нигде не могут появляться в имени оператора, так как они будут приняты в качестве начала комментария.
      • Имя оператора из нескольких символов не может заканчиваться на + или -, если только имя не содержит хотя бы один из следующих символов:

      Например, @- это разрешенное имя оператора, а *- нет. Это ограничение позволяет QHB анализировать SQL-совместимые запросы без пробелов между токенами.

      При работе с нестандартными именами операторов вам обычно нужно разделять соседние операторы пробелами, чтобы избежать двусмысленности. Например, если вы определили левый унарный оператор с именем @, вы не можете написать X*@Y; вы должны написать X* @Y чтобы QHB прочитал его как два оператора, а не как один.

      Специальные символы

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

      • Знак доллара ($), за которым следуют цифры, используется для представления позиционного параметра в теле определения функции или подготовленного оператора. В других контекстах знак доллара может быть частью идентификатора или строковой константы в кавычках.
      • Круглые скобки (()) имеют обычное значение для группирования выражений и обеспечения приоритета. В некоторых случаях скобки требуются как часть фиксированного синтаксиса конкретной команды SQL.
      • Скобки ([]) используются для выбора элементов массива. См. раздел Массивы для получения дополнительной информации о массивах.
      • Запятые (,) используются в некоторых синтаксических конструкциях для разделения элементов списка.
      • Точка с запятой (;) завершает команду SQL. Он не может появляться где-либо внутри команды, кроме как внутри строковой константы или идентификатора в кавычках.
      • Двоеточие (:) используется для выбора «кусочков» из массивов. (См. раздел Массивы. В некоторых диалектах SQL (таких как Embedded SQL) двоеточие используется для добавления префиксов к именам переменных.
      • Звездочка (*) используется в некоторых контекстах для обозначения всех полей строки таблицы или составного значения. Он также имеет особое значение при использовании в качестве аргумента агрегатной функции, а именно, что агрегат не требует какого-либо явного параметра.
      • Точка (.) Используется в числовых константах и для разделения имен схем, таблиц и столбцов.

      Комментарии

      Комментарий — это последовательность символов, начинающаяся с двойных черт и продолжающаяся до конца строки, например:

      -- This is a standard SQL comment 

      В качестве альтернативы можно использовать комментарии блока в стиле C:

      /* multiline comment * with nesting: /* nested block comment */ */ 

      где комментарий начинается с /* и распространяется на совпадение вхождения */. Эти блочные комментарии вложены, как указано в стандарте SQL, но в отличие от C, так что можно закомментировать большие блоки кода, которые могут содержать существующие блочные комментарии.

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

      Приоритет оператора

      Таблица "Приоритет оператора" показывает приоритет и ассоциативность операторов в QHB. Большинство операторов имеют одинаковый приоритет и являются левоассоциативными. Приоритет и ассоциативность операторов встроены в синтаксический анализатор.

      Иногда вам нужно будет добавить скобки при использовании комбинаций бинарных и унарных операторов. Например:

      будет проанализирован как:

      потому что синтаксический анализатор понятия не имеет - пока не стало слишком поздно – «!» определяется как постфиксный оператор, а не как инфиксный. Чтобы получить желаемое поведение в этом случае, вы должны написать:

      Приоритет оператора (от высшего к низшему)

      Оператор / Элемент	Ассоциативность	Описание
      .	left	разделитель имени таблицы/столбца
      ::	left	определение типа в стиле QHB
      [ ]	left	выбор элемента массива
      + -	right	унарный плюс, унарный минус
      ^	left	экспоненцирование
      * / %	left	умножение, деление, по модулю
      + -	left	сложение, вычитание
      (любой другой оператор)	left	все остальные собственные и пользовательские операторы
      BETWEEN IN LIKE ILIKE SIMILAR	ограничение диапазона, набор членов, сопоставление строк
      < >= = <>	операторы сравнения
      IS ISNULL NOT NULL	IS TRUE, IS FALSE, IS NULL, IS DISTINCT FROM т.д.
      NOT	right	логическое отрицание
      AND	left	логическое соединение
      OR	left	логическая дизъюнкция

      Обратите внимание, что правила приоритета операторов также применяются к пользовательским операторам, имена которых совпадают с именами встроенных операторов, упомянутых выше. Например, если вы определите оператор «+» для некоторого пользовательского типа данных, он будет иметь тот же приоритет, что и встроенный оператор «+», независимо от того, что делает ваш.

      Когда полное имя схемы используется в синтаксисе OPERATOR, как, например, в:

      SELECT 3 OPERATOR(pg_catalog.+) 4;

      конструкция OPERATOR имеет приоритет по умолчанию, показанный в таблице 9 для «любого другого оператора». Это верно независимо от того, какой конкретный оператор появляется внутри OPERATOR().

      Выражения значения

      Выражение значения используются в различных контекстах, таких как список целей команды SELECT, в качестве новых значений столбцов в INSERT или UPDATE или в условиях поиска в ряде команд. Результат вычисления выражения значений иногда называют скаляром, чтобы отличить его от результата табличного выражения (которое является таблицей). Поэтому выражения значений также называют скалярными выражениями (или даже просто выражениями). Синтаксис выражения позволяет вычислять значения из примитивных частей, используя арифметические, логические, множественные и другие операции.

      Выражением значения может быть:

      • Постоянное или буквальное значение;
      • Ссылка на столбец;
      • Ссылка на позиционный параметр в теле определения функции или подготовленного оператора;
      • Выражение подзапроса;
      • Выражение выбора поля;
      • Вызов оператора;
      • Вызов функции;
      • Агрегатное выражение;
      • Вызов оконной функции;
      • Приведение типа;
      • Сортировка выражения;
      • Скалярный подзапрос;
      • Конструктор массива;
      • Конструктор строк.
      • Другое выражение значения в скобках (используется для группировки подвыражений и переопределения приоритета).

      В дополнение к этому списку, существует ряд конструкций, которые могут быть классифицированы как выражения, но не следуют никаким общим правилам синтаксиса. Как правило, они имеют семантику функции или оператора и объясняются в соответствующем месте в главе Функции и операторы. Примером является предложение IS NULL.

      Константы уже обсуждались в разделе Константы. В следующих подразделах обсуждаются остальные варианты.

      Ссылки на столбец

      На столбец можно ссылаться в виде:

      correlation — это имя таблицы (возможно, дополненной именем схемы) или псевдонима для таблицы, определенного с помощью предложения FROM. Имя таблицы и разделяющая точка могут быть опущены, если имя столбца уникально во всех таблицах, используемых в текущем запросе. (См. также главу Запросы).

      Позиционные параметры

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

      Например, рассмотрим определение функции dept как:

      CREATE FUNCTION dept(text) RETURNS dept AS $$ SELECT * FROM dept WHERE name = $1 $$ LANGUAGE SQL; 

      Здесь $1 ссылка на значение первого аргумента функции при каждом её вызове.

      Подзапросы

      Если выражение дает значение типа массива, то конкретный элемент значения массива можно извлечь, написав

      или несколько смежных элементов («срез массива») можно извлечь, написав

      (Здесь скобки [ ] должны появляться буквально). Каждый подзапрос сам по себе является выражением, которое должно давать целочисленное значение.

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

      mytable.arraycolumn[4] mytable.two_d_column[17][34] $1[10:42] (arrayfunction(a,b))[42] 

      Скобки в последнем примере обязательны. См. раздел Массивы для получения дополнительной информации о массивах.

      Выбор поля

      Если выражение возвращает значение составного типа (тип строки), то конкретное поле строки можно извлечь, написав

      В общем случае expression строки должно быть заключено в скобки, но скобки можно опустить, если выбранное выражение является просто ссылкой на таблицу или позиционным параметром. Например:

      mytable.mycolumn $1.somecolumn (rowfunction(a,b)).col3 

      (Таким образом, квалифицированная ссылка на столбец на самом деле является просто частным случаем синтаксиса выбора поля). Важным частным случаем является извлечение поля из столбца таблицы составного типа:

      (compositecol).somefield (mytable.compositecol).somefield 

      Скобки здесь обязательны для того, чтобы показать, что compositecol — это имя столбца, а не имя таблицы, или что mytable — это имя таблицы, а не имя схемы во втором случае.

      Вы можете запросить все поля составного значения, написав .* :

      Это обозначение ведет себя по-разному в зависимости от контекста; см. раздел Использование составных типов в запросах для деталей.

      Операторы вызова

      Существует три возможных синтаксиса для вызова оператора:

      expression operator expression (binary infix operator) operator expression (unary prefix operator) expression operator (unary postfix operator) 

      где маркер operator следует синтаксическим правилам раздел Операторы, или является одним из ключевых слов AND, OR и NOT, или является квалифицированным именем оператора в форме:

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

      Вызов функции

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

      function_name ([expression [, expression . ]] )

      Например, вычисление квадратного корня из 2:

      Список всех встроенных функций см. в главе Функции и операторы. Другие необходимые функции могут быть добавлены пользователем.

      При отправке запросов в базу данных, когда некоторые пользователи не доверяют другим пользователям, соблюдайте меры безопасности при написании вызовов таких функций.

      К аргументам необязательно могут быть прикреплены имена. Детальную информацию см. в разделе Вызов функции.

      Функция, которая принимает один аргумент составного типа, может вызываться с использованием синтаксиса выбора поля, и наоборот, выбор поля может быть написан в функциональном стиле. То есть обозначения col(table) и table.col являются взаимозаменяемыми. Такое поведение не является стандартом SQL, но предоставляется в QHB, поскольку позволяет использовать функции для эмуляции «вычисляемых полей».

      Агрегатные выражения

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

      aggregate_name (expression [, . ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name (ALL expression [, . ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name (DISTINCT expression [, . ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ] aggregate_name ( [ expression [, . ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ] 

      где aggregate_name — это ранее определенный агрегат (возможно, с указанием имени схемы), а expression — это любое выражение значения, которое само не содержит выражения агрегации или вызова оконной функции. Необязательные order_by_clause и filter_clause описаны ниже.

      Первая форма агрегатного выражения вызывает агрегат один раз для каждой входной строки. Вторая форма такая же, как и первая, поскольку по умолчанию используется ALL. Третья форма вызывает агрегат один раз для каждого отдельного выражения значения (или отдельного набора значений для нескольких выражений), найденных во входных строках. Четвертая форма вызывает агрегат один раз для каждой входной строки; поскольку конкретное входное значение не указано, оно обычно используется только для агрегатной функции count(*) . Последняя форма используется с агрегатными функциями с упорядоченным набором, которые описаны ниже.

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

      Например, count(*) дает общее количество входных строк; count(f1) возвращает количество входных строк, в которых f1 не равно нулю, поскольку count игнорирует нули; и count(distinct f1) дает количество различных ненулевых значений f1.

      Обычно входные строки подаются в агрегатную функцию в неопределенном порядке. Во многих случаях это не имеет значения; например, min выдает один и тот же результат независимо от того, в каком порядке он принимает входные данные. Однако некоторые агрегатные функции (например, array_agg и string_agg) выдают результаты, которые зависят от упорядочения входных строк. При использовании такого агрегата необязательный order_by_clause может использоваться для указания желаемого порядка. У order_by_clause тот же синтаксис, что и для предложения ORDER BY уровня запроса, как описано в разделе Сортировка строк, за исключением того, что его выражения всегда являются просто выражениями и не могут быть именами или числами выходных столбцов. Например:

      SELECT array_agg(a ORDER BY b DESC) FROM table;

      При работе с агрегатными функциями с несколькими аргументами обратите внимание, что предложение ORDER BY идет после всех аргументов агрегации. Например, напишите это:

      SELECT string_agg(a, ',' ORDER BY a) FROM table;

      SELECT string_agg(a ORDER BY a, ',') FROM table; -- неверно

      Последний синтаксически допустим, но он представляет собой вызов однопараметрической агрегатной функции с двумя ключами ORDER BY (второй является довольно бесполезным, поскольку он является константой).

      Если DISTINCT указан в дополнение к order_by_clause, то все выражения ORDER BY должны соответствовать обычным аргументам агрегата; то есть вы не можете сортировать выражения, которые не включены в список DISTINCT.

      Помещение ORDER BY в обычный список аргументов агрегата, как описано выше, используется при упорядочении входных строк для универсальных и статистических агрегатов, для которых упорядочение необязательно. Существует подкласс агрегатных функций, называемых агрегатами упорядоченного набора, для которых требуется order_by_clause, обычно потому, что вычисление агрегата имеет смысл только с точки зрения конкретного порядка его входных строк. Типичными примерами агрегатов с упорядоченным набором являются вычисления ранга и процентиля. Для агрегата упорядоченного набора order_by_clause записывается внутри WITHIN GROUP (. ), как показано в финальной альтернативе синтаксиса выше. Выражения в order_by_clause вычисляются один раз для каждой входной строки, как обычные агрегатные аргументы, сортируются в соответствии с требованиями order_by_clause и передаются в агрегатную функцию в качестве входных аргументов. (Это непохоже на случай non-WITHIN GROUP order_by_clause, которой не рассматривается как аргумент(ы) для агрегатной функции). Выражения аргументов, предшествующие WITHIN GROUP, если таковые имеются, называются прямыми аргументами, чтобы отличать их от агрегатных аргументов, перечисленных в order_by_clause. В отличие от обычных агрегатных аргументов, прямые аргументы вычисляются только один раз за вызов агрегата, а не один раз на каждую входную строку. Это означает, что они могут содержать переменные, только если эти переменные сгруппированы по GROUP BY; это ограничение такое же, как если бы прямые аргументы вообще не были внутри агрегатного выражения. Прямые аргументы обычно используются для таких вещей, как процентильные дроби, которые имеют смысл только как одно значение для расчета агрегации. Список прямых аргументов может быть пустым; в этом случае просто напишите () не (*). (QHB фактически принимает любое написание, но только первый способ соответствует стандарту SQL).

      Пример агрегатного вызова с упорядоченным набором:

      SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households; percentile_cont ----------------- 50489 

      которая получает 50-й процентиль, или медиану, значения столбца income из таблицы households. Здесь 0.5 - прямой аргумент; не имеет смысла, чтобы процентная доля была значением, варьирующимся по строкам.

      Если указан FILTER, то в агрегатную функцию передаются только входные строки, для которых filter_clause оценивается как «истина»; другие строки отбрасываются. Например:

      SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i); unfiltered | filtered ------------+---------- 10 | 4 (1 row) 

      Предопределенные агрегатные функции описаны в разделе Агрегатные функции. Другие агрегатные функции могут быть добавлены пользователем.

      Агрегатное выражение может появляться только в списке результатов или в предложении HAVING команды SELECT. Это запрещено в других разделах, таких как WHERE, поскольку эти разделы логически оцениваются до формирования результатов агрегатов.

      Когда агрегатное выражение появляется в подзапросе (см.раздел Скалярные подзапросы и раздел Выражения подзапроса, агрегат обычно вычисляется по строкам подзапроса. Но возникает исключение, если аргументы агрегата (и filter_clause при наличии) содержат только переменные внешнего уровня: агрегат в этом случае принадлежит такому ближайшему внешнему уровню и вычисляется поверх строк этого запроса. Агрегатное выражение в целом является тогда внешней ссылкой для подзапроса, в котором оно появляется, и действует как константа поверх любого вычисления этого подзапроса. Ограничение относительно отображения только в списке результатов или предложении HAVING применяется к уровню запроса, к которому принадлежит агрегат.

      Вызовы оконных функций

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

      function_name ([expression [, expression . ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ([expression [, expression . ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) 

      где window_definition имеет синтаксис:

      [ existing_window_name ] [ PARTITION BY expression [, . ] ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS < FIRST | LAST >] [, . ] ] [ frame_clause ] 

      Необязательный frame_clause может быть одним из:

       < RANGE | ROWS | GROUPS >frame_start [ frame_exclusion ] < RANGE | ROWS | GROUPS >BETWEEN frame_start AND frame_end [ frame_exclusion ] 

      где frame_start и frame_end могут быть одним из:

      UNBOUNDED PRECEDING offset PRECEDING CURRENT ROW offset FOLLOWING UNBOUNDED FOLLOWING 

      и frame_exclusion может быть одним из:

      EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS 

      Здесь expression представляет любое выражение значения, которое само не содержит вызовов оконных функций.

      window_name является ссылкой на именованную спецификацию окна, определенную в предложении WINDOW запроса. В качестве альтернативы полное определение window_definition может быть указано в скобках, используя тот же синтаксис, что и для определения именованного окна в предложении WINDOW; см. страницу выбора SELECT для деталей. Стоит отметить, что OVER wname не совсем эквивалентно OVER (wname . ); последнее подразумевает копирование и изменение определения окна и будет отклонено, если спецификация окна, на которое ссылается ссылка, включает предложение frame.

      Предложение PARTITION BY группирует строки запроса в разделы, которые обрабатываются отдельно оконной функцией. PARTITION BY работает аналогично предложению GROUP BY уровня запроса, за исключением того, что его выражения всегда являются просто выражениями и не могут быть именами или числами выходных столбцов. Без PARTITION BY все строки, созданные запросом, обрабатываются как один раздел. Предложение ORDER BY определяет порядок, в котором строки раздела обрабатываются оконной функцией. Он работает аналогично предложению ORDER BY уровня запроса, но также не может использовать имена или номера выходных столбцов. Без ORDER BY строки обрабатываются в неопределенном порядке.

      frame_clause определяет набор строк, составляющих кадр окна, который является подмножеством текущего раздела, для тех оконных функций, которые действуют на кадр, а не на весь раздел. Набор строк в кадре может варьироваться в зависимости от того, какая строка является текущей. Кадр может быть указан в режиме RANGE, ROWS или GROUPS; в каждом случае он выполняется от frame_start до frame_end. Если frame_end опущен, конец по умолчанию равен CURRENT ROW.

      frame_start с UNBOUNDED PRECEDING означает, что кадр начинается с первой строки раздела, и аналогично frame_end с UNBOUNDED FOLLOWING означает, что кадр заканчивается последней строкой раздела.

      В режиме RANGE или GROUPS, frame_start с CURRENT ROW означает, что кадр начинается с первой строки однорангового соединения текущей строки (строка, которую предложение ORDER BY окна сортирует как эквивалентную текущей строке), в то время как frame_end с CURRENT ROW означает, что кадр заканчивается с последней равноправной строкой для текущей строки. В режиме ROWS с CURRENT ROW просто означает текущую строку.

      В опциях offset PRECEDING и offset FOLLOWING offset должно быть выражением, не содержащим никаких переменных, агрегатных функций или оконных функций. Значение offset зависит от режима кадра:

      • В режиме ROWS offset должно давать ненулевое, неотрицательное целое число, а параметр означает, что кадр начинается или заканчивается указанным числом строк до или после текущей строки.
      • В режиме GROUPS offset снова должно давать ненулевое, неотрицательное целое число, а параметр означает, что кадр начинается или заканчивается за указанное число одноранговых групп до или после группы текущей строки, где одноранговые группы представляют набор строк, которые эквивалентных в порядке ORDER BY. (Для использования режима GROUPS в определении окна должно быть предложение ORDER BY).
      • В режиме RANGE эти параметры требуют, чтобы предложение ORDER BY указывало ровно один столбец. offset определяет максимальную разницу между значением этого столбца в текущей строке и его значением в предыдущих или последующих строках кадра. Тип данных выражения offset варьируется в зависимости от типа данных столбца упорядочения. Для числовых упорядочивающих столбцов обычно это тот же тип, что и упорядочивающий столбец, но для столбцов с упорядочиванием даты и времени это интервал. Например, если столбец заказа имеет тип date или timestamp, можно написать RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING . offset по-прежнему должно быть ненулевым и неотрицательным, хотя значение «неотрицательный» зависит от его типа данных.

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

      Обратите внимание, что в режимах ROWS и GROUPS, 0 PRECEDING и 0 FOLLOWING эквивалентны CURRENT ROW. Это обычно выполняется и в режиме RANGE для соответствующего значения специфического типа данных “zero”.

      Опция frame_exclusion позволяет исключить строки вокруг текущей строки из кадра, даже если они будут включены в соответствии с параметрами начала и конца кадра. EXCLUDE CURRENT ROW исключает текущую строку из кадра. EXCLUDE GROUP исключает текущую строку и ее упорядоченные одноранговые элементы из кадра. EXCLUDE TIES исключает любые одноранговые элементы текущей строки из фрейма, но не саму текущую строку. EXCLUDE NO OTHERS просто явно указывает поведение по умолчанию - не исключать текущую строку или ее одноранговые узлы.

      Опция кадрирования по умолчанию - RANGE UNBOUNDED PRECEDING, которая совпадает с RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. С ORDER BY это устанавливает кадр, для всех строк от начала раздела до последнего однорангового узла текущей строки с учетом ORDER BY. Без ORDER BY это означает, что все строки раздела включены в кадр окна, так как все строки становятся равноправными в текущей строке.

      Ограничения состоят в том, что frame_start не может быть UNBOUNDED FOLLOWING, frame_end не может быть UNBOUNDED PRECEDING, а выбор frame_end не может появляться раньше в приведенном выше списке frame_start и frame_end, чем выбор frame_start - например, RANGE BETWEEN CURRENT ROW AND offset PRECEDING не допускается. Но, например, ROWS BETWEEN 7 PRECEDING AND 8 PRECEDING разрешены, даже если они никогда не будут выбирать какие-либо строки.

      Если указан параметр FILTER, то только входные строки, для которых filter_clause оценивается как «истина», передаются оконной функции; другие строки отбрасываются. Только оконные функции, которые являются агрегатами, принимают предложение FILTER.

      Встроенные оконные функции описаны в таблице Оконные функции. Другие оконные функции могут быть добавлены пользователем. Кроме того, любой встроенный или определяемый пользователем универсальный или статистический агрегат может использоваться в качестве оконной функции. (Агрегаты упорядоченного набора и гипотетического набора в настоящее время не могут использоваться в качестве оконных функций).

      Синтаксисы, использующие *, используются для вызова агрегатных функций без параметров в качестве оконных функций, например, count(*) OVER (PARTITION BY x ORDER BY y) . Звездочка (*) обычно не используется для оконных функций. Специфичные оконные функции не позволяют использовать DISTINCT или ORDER BY в списке аргументов функции.

      Вызовы оконных функций разрешены только в списке SELECT и предложении ORDER BY запроса.

      Более подробную информацию о оконных функциях можно найти в разделе Руководство по оконным функциям, разделе Оконные функции и разделе Обработка оконных функций.

      Приведение типов

      Приведение типов определяет преобразование из одного типа данных в другой. QHB принимает два эквивалентных синтаксиса для приведения типов:

      CAST ( expression AS type ) expression::type 

      Синтаксис CAST соответствует SQL; синтаксис с «::» является историческим наследием QHB.

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

      Явное приведение типов обычно может быть опущено, если нет двусмысленности относительно типа, который должно генерировать выражение значения (например, когда оно назначено столбцу таблицы); система автоматически применяет приведение типа в таких случаях. Однако автоматическое приведение выполняется только для приведений, помеченных как «OK to apply implicitly» в системных каталогах. Другие приведения должны вызываться с явным синтаксисом приведения. Это ограничение предназначено для того, чтобы предотвратить неожиданные преобразования, применяемые автоматически.

      Также возможно указать приведение типа с использованием функционально-подобного синтаксиса:

      Однако это работает только для типов, имена которых также допустимы в качестве имен функций. Например, double precision не может быть использована таким образом, но эквивалентный float8 может. Кроме того, имена interval, time и timestamp могут использоваться таким образом, только если они заключены в двойные кавычки, из-за синтаксических конфликтов. Следовательно, использование функционально-подобного синтаксиса приведений приводит к несоответствиям, и его, вероятно, следует избегать.

      Функционально-подобный синтаксис на самом деле является просто вызовом функции. Когда один из двух стандартных синтаксисов преобразования используется для выполнения преобразования во время выполнения, он будет внутренне вызывать зарегистрированную функцию для выполнения преобразования. По соглашению, эти функции преобразования имеют то же имя, что и их тип вывода, и, таким образом, «функционально-подобный синтаксис» является не чем иным, как прямым вызовом базовой функции преобразования. Очевидно, что это не то, на что портативное приложение должно положиться. Для получения дополнительной информации см. CREATE CAST.

      Сортировка выражений

      Предложение COLLATE переопределяет параметры сортировки выражения. Он добавляется к выражению, к которому он относится:

      expr COLLATE collation

      где collation — это идентификатор, который может быть определен схемой. Предложение COLLATE связывает крепче, чем операторы; при необходимости могут быть использованы круглые скобки.

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

      Два типовых варианта использования предложения COLLATE переопределяют порядок сортировки в предложении ORDER BY, например:

      SELECT a, b, c FROM tbl WHERE . ORDER BY a COLLATE "C";

      и переопределение сортировки вызова функции или оператора, который имеет чувствительные к настройкам локализации результаты, например:

      SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

      Обратите внимание, что в последнем случае предложение COLLATE присоединяется к входному аргументу оператора, на который необходимо повлиять. Неважно, к какому аргументу оператора или функции, вызывающей предложение COLLATE, присоединено, потому что сортировка, применяемая оператором или функцией, получается с учетом всех аргументов, и явное предложение COLLATE будет переопределять сортировку всех других аргументы. (Однако присоединение несоответствующих предложений COLLATE к более чем одному аргументу является ошибкой. Более подробную информацию см. в разделе Поддержка сортировки. Таким образом, это дает тот же результат, что и в предыдущем примере:

      SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

      Но этот вариант будет ошибкой:

      SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

      потому что он пытается применить параметры сортировки к результату оператора «>», который является логическим типом данных без сортировки.

      Скалярные подзапросы

      Скалярный подзапрос — это обычный запрос SELECT в скобках, который возвращает ровно одну строку с одним столбцом. (См. главу Запросы для получения информации о написании запросов). Запрос SELECT выполняется, и единственное возвращаемое значение используется в контексте его вызова. Ошибочно использовать запрос, который возвращает более одной строки или более одного столбца в качестве скалярного подзапроса. (Но, если во время конкретного выполнения подзапрос не возвращает строк, ошибки нет; скалярный результат принимается равным нулю). Подзапрос может ссылаться на переменные из окружающего запроса, которые будут действовать как константы во время любого разового вызова подзапроса.

      Например, следующий запрос находит наибольшее население города в каждом штате:

      SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states; 

      Конструкторы массивов

      Конструктор массива — это выражение, которое создает значение массива, используя значения для его элементов-членов. Простой конструктор массива состоит из ключевого слова «ARRAY», левой квадратной скобки [ , списка выражений (разделенных запятыми) для значений элементов массива и, наконец, правой квадратной скобки ] . Например:

      SELECT ARRAY[1,2,3+4]; array --------- (1 row) 

      По умолчанию тип элемента массива является общим типом выражений-членов, определяемых с использованием тех же правил, что и для конструкций UNION или CASE. Вы можете переопределить это, явно приведя конструктор массива к желаемому типу, например:

      SELECT ARRAY[1,2,22.7]::integer[]; array ---------- (1 row) 

      Это имеет тот же эффект, что и приведение каждого выражения к типу элемента массива по отдельности. Подробнее о приведении см. раздел Приведение типов.

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

      SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; array --------------- ,> (1 row) SELECT ARRAY[[1,2],[3,4]]; array --------------- ,> (1 row) 

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

      Элементы конструктора многомерного массива могут быть чем угодно, дающим массив правильного вида, а не только конструкцией sub-ARRAY. Например:

      CREATE TABLE arr(f1 int[], f2 int[]); INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); SELECT ARRAY[f1, f2, ',>'::int[]] FROM arr; array ------------------------------------------------ ,>,,>,,>> (1 row) 

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

      SELECT ARRAY[]::integer[]; array ------- <> (1 row) 

      Также возможно построить массив из результатов подзапроса. В этой форме конструктор массива записывается с ключевым словом ARRAY за которым следует подзапрос (не заключенный в скобки). Например:

      SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); array ----------------------------------------------------------------------- (1 row) SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); array ---------------------------------- ,,,,> (1 row) 

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

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

      Конструкторы строк

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

      SELECT ROW(1,2.5,'this is a test');

      Ключевое слово ROW является необязательным, если в списке более одного выражения.

      Конструктор строки может включать синтаксис rowvalue.* , который будет расширен до списка элементов значения строки, как это происходит, когда синтаксис .* используется на верхнем уровне списка SELECT (см. раздел Использование составных типов в запросах). Например, если таблица t имеет столбцы f1 и f2, следующие записи выдают идентичные результаты:

      SELECT ROW(t.*, 42) FROM t; SELECT ROW(t.f1, t.f2, 42) FROM t; 

      По умолчанию значение, созданное ROW-выражением, относится к анонимному типу записи. При необходимости его можно привести к названному составному типу - либо к типу строки таблицы, либо к составному типу, созданному с помощью CREATE TYPE AS. Чтобы избежать двусмысленности, может потребоваться явное приведение. Например:

      CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- No cast needed since only one getf1() exists SELECT getf1(ROW(1,2.5,'this is a test')); getf1 ------- 1 (1 row) CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Now we need a cast to indicate which function to call: SELECT getf1(ROW(1,2.5,'this is a test')); ERROR: function getf1(record) is not unique SELECT getf1(ROW(1,2.5,'this is a test')::mytable); getf1 ------- 1 (1 row) SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); getf1 ------- 11 (1 row) 

      Конструкторы строк можно использовать для построения составных значений, которые будут храниться в столбце таблицы составного типа или передаваться в функцию, которая принимает составной параметр. Кроме того, можно сравнить два значения строки или проверить строку с помощью IS NULL или IS NOT NULL, например:

      SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows 

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

      Правила вычисления выражений

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

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

      SELECT true OR somefunc();

      параметр somefunc() (вероятно) не будет вызван вообще. То же самое будет, если написать:

      SELECT somefunc() OR true;

      Обратите внимание, что это не то же самое, что «оптимизация» логических операторов слева направо, встречающееся в некоторых языках программирования.

      Как следствие, неразумно использовать функции с побочными эффектами как часть сложных выражений. Особенно опасно полагаться на побочные эффекты или порядок вычисления в предложениях WHERE и HAVING предложениях, так как эти пункты тщательно обрабатываются как часть построения плана выполнения. Булевы выражения (комбинации AND/OR/NOT ) в этих предложениях могут быть реорганизованы любым способом, разрешенным законами булевой алгебры.

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

      SELECT . WHERE x > 0 AND y/x > 1.5;

      Но это безопасно:

      SELECT . WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

      CASE-конструкция используемая таким образом, будет препятствовать попыткам оптимизации, так что это должно быть сделано только в случае необходимости. (В этом конкретном примере было бы лучше обойти проблему, написав y > 1.5*x ).

      Однако CASE, это не панацея от подобных проблем. Одним из ограничений метода, показанного выше, является то, что он не препятствует раннему вычислению постоянных подвыражений. Как описано в разделе Категории волатильности функций, функции и операторы, помеченные как IMMUTABLE могут быть вычислены при планировании запроса, а не при его выполнении. Так например:

      SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

      скорее всего, это приведет к ошибке деления на ноль из-за того, что планировщик пытается упростить постоянное подвыражение, даже если в каждой строке таблицы есть x > 0 так, что вариант с ELSE никогда не будет обработан во время выполнения.

      Хотя этот конкретный пример может показаться глупым, связанные запросы, которые явно не включают константы, могут возникать в запросах, выполняемых внутри функций, поскольку значения аргументов функции и локальных переменных могут быть вставлены в запросы в качестве констант для целей планирования. Например, в функциях PL/pgSQL использование оператора IF-THEN-ELSE для защиты рискованных вычислений намного безопаснее, чем просто вложить его в выражение CASE.

      Другое ограничение того же типа состоит в том, что CASE не может предотвратить вычисление содержащегося в нем агрегатного выражения, потому что агрегатные выражения вычисляются до того, как будут рассмотрены другие выражения в списке SELECT или предложении HAVING. Например, следующий запрос может вызвать ошибку деления на ноль, несмотря на кажущуюся защиту от нее:

      SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments; 

      Агрегаты min() и avg() вычисляются одновременно по всем входным строкам, так что, если любая строка имеет employees равное нулю, то ошибка деления на ноль будет происходить до того, как появится возможность проверить результат min(). Вместо этого используйте предложение WHERE или FILTER, чтобы предотвратить попадание проблемных входных строк в агрегатную функцию.

      Вызов функции

      QHB позволяет вызывать функции с именованными параметрами с использованием позиционной или именованной нотации. Именованная нотация особенно полезна для функций, которые имеют большое количество параметров, поскольку она делает ассоциации между параметрами и фактическими аргументами более явными и надежными. В позиционной нотации вызов функции записывается со значениями аргументов в том же порядке, в котором они определены в объявлении функции. В именованной нотации аргументы сопоставляются с параметрами функции по имени и могут быть записаны в любом порядке.

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

      QHB также поддерживает смешанную нотацию, которая объединяет позиционную и именованную нотацию. В этом случае позиционные параметры записываются первыми, а именованные параметры появляются после них.

      Следующие примеры иллюстрируют использование всех трех обозначений, используя следующее определение функции:

      CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT; 

      Функция concat_lower_or_upper имеет два обязательных параметра, a и b . Кроме того, есть один необязательный параметр uppercase который по умолчанию равен false. Входы a и b будут объединены и переведены в верхний или нижний регистр в зависимости от параметра uppercase. Остальные детали этого определения функции здесь не важны (см. главу Расширение SQL для получения дополнительной информации).

      Использование позиционной нотации

      Позиционная нотация — это традиционный механизм передачи аргументов функциям в QHB. Примером является:

      SELECT concat_lower_or_upper('Hello', 'World', true); concat_lower_or_upper ----------------------- HELLO WORLD (1 row) 

      Все аргументы указаны в порядке. Результат - верхний регистр, поскольку uppercase указан как true. Другой пример:

      SELECT concat_lower_or_upper('Hello', 'World'); concat_lower_or_upper ----------------------- hello world (1 row) 

      Здесь параметр uppercase пропущен, поэтому он получает значение по умолчанию false, что приводит к выводу в нижнем регистре. В позиционной нотации аргументы могут быть опущены справа налево, если они имеют значения по умолчанию.

      Использование именованных обозначений

      В именованной нотации имя каждого аргумента указывается с помощью => чтобы отделить его от выражения аргумента. Например:

      SELECT concat_lower_or_upper(a => 'Hello', b => 'World'); concat_lower_or_upper ----------------------- hello world (1 row) 

      Опять же, аргумент в uppercase был опущен, поэтому он неявно установлен в значение false. Одно из преимуществ использования именованных обозначений заключается в том, что аргументы могут быть указаны в любом порядке, например:

      SELECT concat_lower_or_upper(a => 'Hello', b => 'World', uppercase => true); concat_lower_or_upper ----------------------- HELLO WORLD (1 row) SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World'); concat_lower_or_upper ----------------------- HELLO WORLD (1 row) 

      Более старый синтаксис, основанный на ":=" , поддерживается для обратной совместимости:

      SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World'); concat_lower_or_upper ----------------------- HELLO WORLD (1 row) 

      Использование смешанной нотации

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

      SELECT concat_lower_or_upper('Hello', 'World', uppercase => true); concat_lower_or_upper ----------------------- HELLO WORLD (1 row) 

      В приведенном выше запросе аргументы a и b указываются позиционно, а uppercase - именем. В этом примере это мало что добавляет, кроме документации. С более сложной функцией, имеющей многочисленные параметры со значениями по умолчанию, именованные или смешанные обозначения могут сэкономить много времени и уменьшить вероятность ошибок.

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

      Изучаем PHP и MySQL. М.Дэвис, Д.Филлипс. 2008

      По договору между издательством «Символ-Плюс» и Интернет-магазином «Books. Ru – Книги России» единственный легальный способ получения данного файла с книгой ISBN 978-5-93286-115-8, название «Изучаем PHP и MySQL» – покупка в Интернет-магазине «Books. Ru –. Показать больше

      По договору между издательством «Символ-Плюс» и Интернет-магазином «Books. Ru – Книги России» единственный легальный способ получения данного файла с книгой ISBN 978-5-93286-115-8, название «Изучаем PHP и MySQL» – покупка в Интернет-магазине «Books. Ru – Книги России». Если Вы получили данный файл каким-либо другим образом, Вы нарушили международное законодательство и законодательство Российской Федерации об охране авторского права. Вам необходимо удалить данный файл, а также сообщить издательству «Символ-Плюс» (piracy@symbol. ru), где именно Вы получили данный файл. Спрятать

      • Похожие публикации
      • Поделиться
      • Код вставки
      • Добавить в избранное
      • Комментарии

      Функции SQL Oracle. Дополнительные фразы предложения SELECT

      Агрегатные функции без фраз WHERE и GROUP BY . Если фразы WHERE и GROUP BY отсутствуют, то областью действия агрегатной функции являются все строки таблицы. Пример . Сколько строк в таблице TEACHER: SELECT COUNT(*) AS Total_rows_in_TEACHER_table FROM TEACHER; Пример . Чему равна сумма фондов всех факультетов: SELECT SUM(Fund) FROM FACULTY; Пример . Какой средний фонд финансирования кафедр: SELECT AVG(Fund) FROM DEPARTMENT; Агрегатные функции с фразой WHERE . При наличии фразы WHERE областью действия агрегатной функции являются те строки, которые удовлетворяют условию фразы WHERE. Пример . Какова средняя ставка ассистентов, которые не имеют надбавки. SELECT AVG(Salary) FROM TEACHER WHERE UPPER(Post) = 'ASSISTANT' AND Commission IS NULL; Пример . Какой максимальный фонд среди кафедр факультета информатики? SELECT MAX(DEPARTMENT.Fund) FROM DEPARTMENT, FACULTY WHERE DEPARTMENT.FacNo = FACULTY.FacNo AND UPPER(FACULTY.Name) = 'INFORMATICS'; Выражения в агрегатной функции . Аргументы агрегатных функций могут содержать выражения над столбцами таблицы. Премер . Какова максимальная величина Salary+Commission? SELECT MAX(Salary + Commission) FROM TEACHER; Пример . Какова разница между максимальной и минимальной ставками? SELECT MAX(Salary) – MIN(Salary) FROM TEACHER; DISTINCT в агрегатных функциях . Ключевое слово DISTINCT означает, что в расчет следут брать только уникальные значения столбца (выражения) аргумента. Пример . Сколько номеров телеыонов в таблице TEACHER: SELECT COUNT(DISTINCT Tel) AS Number_of_telephones FROM TEACHER; Пример . Сколько должностей в таблице TEACHER: SELECT COUNT(DISTINCT Post) FROM TEACHER; Множество агрегатных функций в списке select. Список Select может содержать множество агрегатных функций. Example . Сколько профессоров в Вузе и какова их средняя ставка: SELECT COUNT(*), SUM( Salary ) FROM TEACHER WHERE UPPER(Post) = 'PROFESSOR'; Агрегатные функции с фразой GROUP BY . См. примеры фразы использования фразы GROUP BY.

      3.2.Фразы GROUP BY и HAVING

      GROUP BY и агрегатные функции в списке select . Пример. Сколько кафедр в каждом из корпусов: SELECT Building, COUNT(*) FROM DEPARTMENT GROUP BY Building; Example . Какова сумма зарлпты (Salary+Commission) по всем имеющимся должностям: SELECT Post, SUM(Salary + Commission) FROM TEACHER GROUP BY Post; Группирование и фраза WHERE . Если запрос содержит фразы WHERE и GROUP BY, то фразаWHERE обрабатывается первой, а затем применяется группирование. Пример. Для каждого корпуса подсчитать количество аудиторий вместимостью более 50: SELECT Building, COUNT(*) FROM ROOM WHERE Seats > 50 GROUP BY Building; Пример. Для корпусов 5, 7 и 12 подсчитать количество аудиторий вместимостью более 50: SELECT Building, COUNT(*) FROM ROOM WHERE UPPER(Building) IN ('5', '7', '12') AND Seats > 50 GROUP BY Building; Группирование по многим столбцам . Можно группировать по многим столбцам. Пример. Для каждой недели и дня недели подсчитать количество лекций типа “lab”: SELECT Day, Week, COUNT(*) FROM LECTURE WHERE UPPER(Type) = 'LAB' GROUP BY Week, Day; Группирование и соединение различных таблиц . Имеется возможность соединить две или более таблиці и зптем произвести группирование по соединенной таблице. Пример. По каждому факультету выдать количество кафедр: SELECT f.Name, COUNT(*) FROM FACULTY f, DEPARTMENT d WHERE f.FacNo = d.FacNo GROUP BY f.Name; Пример. По каждому факультету выдать количество профессоров: SELECT f.Name, COUNT(*) FROM FACULTY f, DEPARTMENT d, TEACHER t WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(t.Post) = 'PROFESSOR' GROUP BY f.Name; Пример . Для каждой кафедры каждого факультета подсчитать количество профессоров: SELECT f.Name, d.Name, COUNT(*) FROM FACULTY f, DEPARTMENT d, TEACHER t WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(t.Post) = 'PROFESSOR' GROUP BY f.Name, d.Name; Использование фразы HAVING . Она задает условие на группу. Фразы HAVING обычно содержат агрегатную функцию. Example. Вывести корпуса, в которых суммарное количество мест в аудиториях меньше 1000: SELECT Building FROM ROOM GROUP BY Building HAVING SUM(Seats) Упорядочение по столбцу из списка select . Пример. Выдать назвыание факультета и его декана, упорядочив результат по факультетам: SELECT Name, Dean FROM FACULTY ORDER BY Name; Упорядочение по столбцу таблицы. Можно упорядочивать по столбцу таблицы, даже если он не присутствует в списке select. Эта возможность не поддерживается стандартом ANSI, но допустима в SQL Oracle. Пример. Выдать имена преподавателей и их ставки, упорядочив результат по должностям: SELECT Name, Salary FROM TEACHER ORDER BY Post; Упорядочение по списку столбцов . Используя список столбцов можно произвести многоуровневое упорядочение. Пример. По каждой группе вывести ее номер, курс и количество студентов. Упорядочить результат по курсу и номеру группы: SELECT Course, Num, Quantity FROM SGROUP ORDER BY Course, Num; Упорядочение по выражению над столбцами . Можно упорядочивать по выражению над столбцами. Example . Вывести имя ставку и надбавку преподавателей. Упорядочить результат по выражению Salary+Commission: SELECT Name, Salary, Commission FROM TEACHER ORDER BY Salary + Commission ASC; Использование порядкового номера столбца в списке select. Можно использовать порядковый номер столбца в списке select для ссылки на него во фразе ORDER BY. Это может оказаться удобным, когда список select содержит выражения. Пример . Вывести имя преподавателя и его зарплату (Salary+Commission). Упорядочить результат по выражению Salary+Commission в порядке убывания: SELECT Name, Salary + Commission FROM TEACHER ORDER BY 2 DESC; Использование алиаса столбца из списка select. Вы можете воспользоваться алиасом столбца из списка select для ссылки на него во фразе ORDER BY. Пример . Вывести имена преподавателей и даты их поступления на работу. Если дата не определена, то вывести строку “not defined”. Упорядочить результат по дате в убывающем порядке: SELECT Name, NVL(TO_CHAR(hiredate,'yyyy-mm-dd'),'not defined') AS Teacher_hiredate FROM TEACHER ORDER BY Teacher_hiredate DESC; Соединение таблиц и упорядочение. Если вы соединяете таблицы, то можно упорядочивать по любому столбцу соединенной таблицы. Пример . Вывести имна преподавателей и их зарплаты факультета инфоматики. Упорядочить результат по зарплате в убывающем порядке. SELECT t.Name, Salary + Commission FROM FACULTY f, DEPARTMENT d, TEACHER t WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(f.Name) = 'INFORMATICS' ORDER BY 2 DESC; Группирование и упорядочение . Для упорядочение групп необходимо удовлетворить ограничения, описанные в разделе “2.3. Фраза ORDER BY”. Пример . Вывести количество студентов на каждом курсе. Расположить результат в порядке возрастания курса. SELECT Course, SUM( Quantity ) FROM SGROUP GROUP BY Course ORDER BY Course ASC;

      4.Варианты заданий

      1. Агрегатные функции на одной таблице
      2. Агрегатные функции с учетом нескольких таблиц
      3. NULL в агрегатных функциях, фраза DISTINCT, выражения в аргументе агрегатной ф-ции
      4. GROUP BY (группирование по одному или нескольким столбцам)
      5. Выражения в GROUP BY
      6. Фраза HAVING (без вложенных запросов)
      7. Фраза ORDER BY

      4.1.Вариант 1

      1) Сколько дисциплин приведено в таблице SUBJECT. Столбец результирующей таблицы должен иметь имя «К-во дисциплин» 2) Сколько дисциплин читается студентам на факультете 'компьютерные науки' 3) Вывести дважды среднее количество студентов в группах факультета 'компьютерные науки'. В первом случае при подсчете среднего значения NULL игнорируются. Во втором случае NULL учитываются как значение 0. Почему эти значения могут отличаться? 4) Для каждого факультета вывести его название и разницу между ее фондом и суммарными фондами финансирования всех его кафедр. 5) По каждой зарплате (salary+commission), выплачиваемой преподавателям факультета, деканом которого является Иванов, вывести эту зарплату, количество преподавателей с этой зарплатой и количество кафедр, на которых работают эти преподаватели. Соответствующие столбцы имеют названия: «Зарплата», «Кол-во преподавателей» и «Кол-во кафедр» 6) По каждому преподавателю факультета компьютерных наук вывести: - его имя, - должность и - количество дисциплин, которые он преподает, при условии, что: - он проводит занятия не более, чем в 3-х группах и - он проводит занятия в не более, чем 2-х аудиториях 7) По каждой аудитории корпуса 6 вывести ее номер и названия кафедр, на которых проволятся занятия в этой аудитории студентам 3курса. Результат упорядочить по номеру аудитории в возрастающем порядке и по названию кафедры в убывающем порядке

      4.2.Вариант 2

      1) Вывести значение следующего выражения: суммарную зарплату (salary+commission) по всем преподавателям умножить на количество должностей и разделить на количество преподавателей. Столбец результирующей таблицы должен иметь имя «Выражение» 2) Сколько студентов и сколько групп на факультете 'компьютерные науки' 3) Вывести дважды среднее количество зарплаты (salary+commission) преподавателей факультета 'компьютерные науки'. В первом случае при подсчете среднего значения возможные значения NULL для salary и commission игнорируются. Во втором случае NULL учитываются как значение 0. Почему эти значения могут отличаться? 4) Для каждого преподавателя факультета 'компьютерные науки' вывести следующую информацию под столбцами с соответствующими именами - имя - Преподаватель, - количество лекций, которые он имеет, и - Кол-во лекций - количество дисциплин, которые он читает. - Кол-во дисциплин 5) По каждой зарплате (salary+commission), выплачиваемой преподавателям кафедры ИПО, вывести эту зарплату, количество преподавателей с этой зарплатой и количество должностей, имеющих эту зарплату. Соответствующие столбцы имеют названия: «Зарплата», «Кол-во преподавателей» и «Кол-во должностей». Если ставка или надбавка не определены (имеют значение NULL) то считать, что они принимают значение 0. 6) По каждому преподавателю факультета компьютерных наук вывести: - его имя, - его зарплату (salary+commission), - количество групп, в которых он проводит занятия, и - количество аудиторий, в которых он проводит занятия, при условии, что: - он проводит занятия не более, чем по 2 дисциплинам и - у него не более, чем 4 занятий в расписании 7) Вывести названия дисциплин и номера аудиторий с их номерами корпусов, которые (дисциплины) читаются преподавателями-профессорами факультета 'компьютерные науки' студентам 3 курса. Результат упорядочить по названию дисциплине в возрастающем порядке и по номеру аудитории в убывающем порядке.

      4.3.Вариант 3

      1) Сколько дней в неделю проводятся занятия и каково максимальное количество пар указано в таблице LECTURE. Столбцы результирующей таблицы должны иметь имена «МАКС к-во пар» и «К-во дней» 2) Сколько кафедр и сколько преподавателей на факультете 'компьютерные науки' и какова средняя зарплата (salary+commission) преподавателей на этом факультете 3) Вывести дважды количество значений в столбце Tel таблицы TEACHER В первом случае при подсчете количества значений возможные значения NULL для Tel игнорируются. Во втором случае NULL учитываются как значение ' ' (пустая строка). Почему эти значения могут отличаться? 4) Для каждого кафедры факультета 'компьютерные науки', расположенной в корпусе 6, вывести название кафедры, количество преподавателей на кафедре и количество групп на кафедре. При этом результирующие столбцы имеют следующие имена: - Кафедра - Кол-во преподавателей - Кол-во групп 5) По каждой зарплате (salary+commission), выплачиваемой преподавателям кафедры, заведующей которой является Иванов, вывести следующую информацию в столбцах под соответствующими именами: - зарплата этой группы преподавателей. Если она не определена, то вывести 0 Зарплата - к-во различных должностей, занимаемых этими преподавателями К-во должностей - к-во кафедр, на которых работают эти преподаватели К-во кафедр - к-во телефонов, которыми пользуются эти преподаватели К-во телефонов 6) По каждой группе кафедры ИПО вывести: - номер группы, - его курс, - количество преподавателей, которые проводят в ней занятия, и - количество занятий, которые имеются у ней в расписании, при условии, что: - в этой группе проводятся занятия не более, чем по 6-ти дисциплинам, и что - в ней проводятся занятия в не более, чем в 5-ти аудиториях. 7) Вывести названия факультетов и дисциплин, которые читаются на этих факультетах преподавателями-доцентами, являющимися кураторами студентов 3-го курса. Упорядочить по названию факультета в убывающем порядк и по названию дисциплины в убывающем порядке

      4.4.Вариант 4

      1) Какой суммарный фонд финансирования всех факультетов. Столбец результирующей таблицы должен иметь имя «Фонд фак-тов» 2) Группам скольких кафедр проводит занятия преподаватель Иванов и сколько дисциплин этот преподаватель преподает (не зависимо от того, для студентов каких кафедр проводятся занятия) 3) Вывести дважды суммарную зарплату преподавателей факультета, деканом которого является Иванов В первом случае NULL игнорируется. Во втором случае NULL учитывается как значение 0. Почему эти значения могут отличаться? 4) Для каждого кафедры факультета 'компьютерные науки', имеющей фонд финансирования больше 125000, вывести название кафедры, количество групп на кафедре, количество дисциплин, которые читаются студентам кафедры, количество преподавателей, которые читают лекции студентам кафедры. При этом результирующие столбцы имеют следующие имена: - Кафедра - Кол-во групп - Кол-во дисциплин - Кол-во преподавателей 5) По каждому телефону, установленному у преподавателей, проводящих занятия в группах кафедры ИПО, вывести номер телефона и количество преподавателей, у которых этот телефон установлен. Если телефон принимает значение NULL, то вместо него использовать строку «Нет телефона». Столбцы результирующей таблицы принимают имена: «Телефон» и «К-во преподавателей». 6) По каждой группе, куратором которой является преподаватель кафедры ИПО, вывести: - номер группы, - имя куратора, - количество дисциплин, которые читаются этой группе, - количество аудиторий, в которых проводятся занятия в группе при условии, что: - в этой группе проводят занятия не более 4-х преподавателей, - в этой группе имеется менее 30 занятий в расписании 7) Вывести номера аудиторий с именами преподавателей, в которых (аудиториях) эти преподаватели проводят лабораторные занятия для студентов факультета 'компьютерные науки'. Результат упорядочить по названию факультетов по возрастанию и названию кафедр по убыванию.

      4.5.Вариант 5

      1) Чему равно следующее выражение: сложить суммарный фонд финансирования всех кафедр, разделенный на количество кафедр, с максимальным фондом, разделенным на минимальный фонд финансирования. Столбец результирующей таблицы должен иметь имя «Сложное выражение» 2) В скольких группах преподают преподаватели кафедры ИПО 3) Вывести дважды средний рейтинг всех групп факультета компьютерных наук. В первом случае NULL игнорируются. Во втором случае NULL учитывается как значение 0. Почему эти значения могут отличаться? 4) Для каждого кафедры факультета 'компьютерные науки', имеющей фонд финансирования в диапазоне 200000-500000 вывести название кафедры, количество групп на кафедре, общее количество студентов на кафедре, среднее количество студентов в группе, разницу между максимальным и минимальным рейтингом групп кафедры. При этом результирующие столбцы имеют следующие имена: - Кафедра - Кол-во групп - Кол-во студентов - Сред. к-во студентов в группе - МАКС(рейтинг)-МИН(рейтинг) 5) По каждой группе преподавателей кафедры ИПО с диапазоном даты поступления на работу 01.01.2000-31.12.2007, фамилии которых начинаются на одну букву, вывести эту букву, количество преподавателей и суммарную зарплату (salary+commission) этих преподавателей. Соответствующие столбцы имеют названия: «Б», «Кол-во преподавателей» и «Суммарная зарплата» 6) По каждой аудитории, в которой проводят занятия преподаватели факультета компьютерных наук, вывести - ее номер, - корпус и - количество групп, которым проводятся занятия в этой аудитории, при условии, что: - в этой аудитории проводят занятия не более, чем 3 преподавателя, и что - в ней проводятся занятия не более, чем по 4-м дисциплинам 7) Вывести названия кафедр, преподаватели которых преподают студентам кафедры ИПО. Результат упорядочить по иманам преплдавателей по убыванию.

      4.6.Вариант 6

      1) Каковы суммарные ставка, надбавка и зарплата (ставка+надбавка) всех преподавателей-доцентов. Столбцы результирующей таблицы должны иметь имена «Ставка доцентов», «Надбавка доцентов» и «Зарплата доцентов» 2) По всем преподавателям, являющимся кураторами групп кафедры ИПО, вывести указанные значения под соответствующими именами столбцов: - строковая константа 'Кураторы кафедры ИПО' - Что выводится? - количество этих преподавателей - К-во преподавателей; - их суммарную зарплату (Salary+Commission) - Суммарная зарплата - их суммарную зарплату, разделенную на их количество - Средняя зарплата - разницу между их максимальной и минимальной зарплатой - Разность зарплат 3) Вывести перечень дней недели, в которые имеются занятия в группах факультета компьютерныъ наук 4) Для каждой пары значений преподаватель-дисциплина, имеющих (преподавателей) зарплату (Salary+Commission) в диапазоне 1000-3000 и работающих на кафедре ИПО, вывести: - имя преподавателя, - Преподаватель - должность преподавателя - Должность - название дисциплины, которую преподает этот преподаватель - Дисциплина - количество лекций, которое имеет этот преподаватель по этой дисциплине - Кол-во лекций - количество групп, которым читаются лекции этим преподавателей по этой дисциплине - Кол-во групп - количество аудиторий, в которых проводятся занятия этим преподавателем по этой дисциплине - Кол-во аудиторий 5) По каждой группе преподавателей факультета компьютерных наук с диапазоном зарплат (salary+commission) 1000-3000 вывести следующую информацию под соответствующими названиями столбцов: - название месяца, в котором были приняты на работу преподаватели - Месяц - количество принятых на работу преподавателей в этом месяце - К-во преподавателей - средняя зарплата (salary+commission) всех преподавателей, - Средняя зарплата принятых на работу в этом месяце - количество кафедр, на которых работают эти преподаватели - К-во кафедр 6) По каждой аудитории из корпусов 1, 3, 4, 5, 6, в которых проводятся занятия групп кафедры ИПО, вывести: - номер аудитории, - количество мест в аудитории, - количество преподавателей, которые проводят занятия в этой аудитории, - количество дисциплин, которые читаются в этой аудитории, при условии, что: - в этой аудитории проводятся занятия не более, чем в 3-х группах, и - в этой аудитории проводятся не более 5 занятий 7) Вывести имена преподавателей факультета 'компьютерные науки'. Результат упорядочить по первой букве в именах преподавателей.

      4.7.Вариант 7

      1) Какое количество групп и суммарное количество студентов и во всех группах третьего курса. Столбцы результирующей таблицы должны иметь имена: «К-во групп» и «К-во студентов» 2) По всем преподавателям, занимающим должности профессоров или доцентов или ассистентов, факультета 'компьютерные науки' вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о фак-те компьютерных наук:' - Что выводится? - количество таких преподавателей - Кол-во преподавателей - Количество читаемых ими дисциплин - Кол-во дисциплин - Количество групп, в которых они проводят занятия - Кол-во групп - Количество аудиторий, в которых они проводят занятия - Кол-во аудиторий 3) Вывести перечень должностей, которые имеют преподаватели факультета, деканом которого является Иванов 4) Для каждой пары значений группа-дисциплина, имеющих (групп) один из следующих рейтингов: 10-30, 45, 55-70 или NULL, вывести следующую информацию под соответствующими именами столбцов: - номер группы -Группа, - курс группы -Курс, - рейтинг группы -Рейтинг, - дисциплина, которая читается группе -Дисциплина, - количество занятий, проводимых для этой группе по этой дисциплине -Кол-во лекций, - количество преподавателей, которые проводят занятия в этой группе по этой дисциплине – Кол-во преподаватаелей, - количество аудиторий - Кол-во аудиторий 5) По каждой группе преподавателей с диапазоном зарплат (salary+commission) 0-3000 вывести следующую информацию под соответствующими названиями столбцов: - название месяца, в котором были приняты на работу преподаватели Месяц - количество принятых на работу преподавателей в этом месяце К-во преподавателей - средняя зарплата (salary+commission) всех преподавателей, принятых Средн. зарплата на работу в этом месяце - разница максимальной и минимальной зарплатами среди всех МАКС(зарп.)-МИН(зарп.) преподавателей, принятых на работу в этом месяце. При использовании во фразе WHERE полей salary и commission заменять значения NULL на 0 6) По каждой дисциплине, читаемой студентам кафедры, у которой заведующим является Иванов, вывести: - название дисциплины, - количество групп, которым эта дисциплина читается, - количество преподавателей, которые эту дисциплину читают, при условии, что: - эта дисциплина читается не более, чем в 4-х аудиториях, и что - по этой дисциплине проводятся не более, чем 5 занятий 7) Вывести имена преподавателей кафедры ИПО и дату поступления их на работу. Результат упрядочить по месяцу поступления на работу в убывающем порядке

      4.8.Вариант 8

      1) Какова минимальная и максимальная зарплаты (с учетом ставки и надбавки) среди всех преподавателей-профессоров, принятых на работу в диапазоне дат 01.01.2000-31.12.2002. Столбцы результирующей таблицы должны иметь имена: «МИН зарплата» и «МАКС зарплата» 2) По дисциплине СУБД, которую читает преподаватель Иванов, вывести следующую информацию под соответствующими именами столбцов: - количество занятий, которые имеются в расписании Кол-во занятий - Количество групп, в которых проводятся эти занятия Кол-во групп - Количество аудиторий, в которых проводятся эти занятий Кол-во аудиторий - Количество дней недели, в которых проводятся эти занятий Кол-во дней 3) Вывести перечень дисциплин, читаемых студентам кафедры ИПО факультета компьютерных наук 4) Для каждой тройки значений преподаватель-дисциплина-группа, в которой группа принадлежит факультету 'компьютерные науки', вывести следующую информацию в столбцах под соответствующими именами: - имя преподавателя - Преподаватель, - должность преподавателя - Должность, - название дисциплины - Дисциплина, - номер группы - Группа, - количество занятий проводимых этим преподавателем в этой группе по этой дисциплине - Кол-во занятий - количество аудиторий, в которых проводятся занятия этим преподавателем в этой группе по этой дисциплине. - Кол-во аудиторий 5) По каждой группе преподавателей факультета, деканом которого является Иванов, с диапазоном дат поступления на работу 01.01.2000-31.12.2008 вывести следующую информацию под соответствующими названиями столбцов: - название дня недели, в котором были приняты на работу преподаватели - День - количество принятых на работу преподавателей в этот день недели - К-во преподавателей - разница между максимальной и минимальной зарплатой - МАКС(зарп.)-МИН(зарп.) (salary+commission) всех преподавателей, принятых на работу в этот день - количество кафедр, на которых работают эти преподаватели - К-во кафедр 6) По каждой дисциплине, которая читается преподавателем факультета, расположенном в одном из корпусов 1, 3, 4, 5, 6, 7, вывести - название дисциплины - количество аудиторий, в которых эта дисциплина читается, - количество занятий, которые проводятся по этой дисциплине при условии: - эта дисциплина читается не более 5 группам, - количество преподавателей, читающих эту дисциплину, находится в диапазоне 1-5 7) Вывести имена и зарплату (Salary+Commission) преподавателей факультета, деканом которого является Иванов. Результат упорядочить по зарплате в убывающем порядке

      4.9.Вариант 9

      1) Какова минимальная и максимальная даты поступления на работу преподавателей-доцентов. Столбцы результирующей таблицы должны иметь имена «МИН дата» и МАКС дата» 2) По группе 304 3-го курса кафедры ИПО вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о группе 304 кафедры ИПО:' - О чем сведения? - количество читаемых дисциплин - Дисциплины - количество преподавателей, проводящих занятий - Преподаватели - количество аудиторий, в которых проводятся занятия - Аудитории - количество занятий, которые имеются в расписании - Занятия - Количество дней недели, в которых проводятся эти занятий - Дни недели 3) Вывести перечень корпусов, в аудиториях которых проводят занятия преподаватели факультета, деканом которого является Иванов 4) По каждому преподавателю, принятому на работу в диапазоне дат 01.01.2000- 31.12.2007, вывести следующую информацию в столбцах под соответствующими именами: - его имя - Преподаватель - количество подчиненных ему преподавателей - Кол-во подчиненных - количество подчиненных подчиненных ему преподавателей - Кол-во подчиненных подчиненных - суммарная зарплата (salary+commission) подчиненных подчиненных ему преподавателей - Суммарная зарплата подч. подч. - разница между средней и минимальной зарплатами подчиненных подчиненных ему преподавателей - СРЕД(зарплата)-МИН(зарплата) 5) По каждой группе преподавателей кафедры, заведующим которой является Иванов, с диапазоном дат поступления на работу 15.10.2001-25.11.2009 вывести следующую информацию в столбцах с соответствующими именами: - год, в котором были приняты на работу преподаватели - Год - количество принятых на работу преподавателей в этот год - К-во преподавателей - разница между максимальной и средней зарплатой (salary+commission) всех преподавателей, принятых на работу в этом году, Если эта разница равна NULL, то вывести 0 - МАКС(зарплата)-МИН(зарплата) - количество должностей, на которых работают эти преподаватели - К-во должностей 6) По каждой паре значений преподаватель-дисциплина (преподаватель читает эту дисциплину), такой, что: - преподаватель работает на кафедре, заведующим которой является Иванов, вывести: - имя преподавателя, - его зарплату (salary+commission), - название дисциплины и - количество групп, которым этот преподаватель читает эту дисциплину, при условии, что: - у этого преподавателя имеется не более 4 занятий по этой дисциплине и - он читает эту дисциплину в более, чем одной аудитории 7) Вывести среднее арифметическое, минимальное и максимальное значения выражения Salary+Commission по строкам таблицы TEACHER, для которых значение столбца Hiredate находится в диапазоне 01.10.1998-30.07.2009. Результат упорядочить по убыванию значений первого столбца и по возрастанию значений третьего столбца. Для упорядочения использовать алиасы столбцов

      4.10.Вариант 10

      1) Вывести минимальную ставку, максимальную надбавку, количество различных должностей среди преподавателей-доцентов. Столбцы результирующей таблицы должны иметь имена: «МИН ставка», «Макс надбавка», «К-во должностей». 2) По кафедре, заведующим которой является Иванов, вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о кафедре Иванова:' - Инфо о кафедре - количество преподавателей - Количество преподавателей - количество групп - Количество групп 3) Вывести названия факультетов и имена их деканов, студентам которых читается дисциплина СУБД 4) По каждому преподавателю факультета компьютерных наук, имеющему зарплату (salary+commission) в диапазоне 800-3000, вывести следующую информацию под соответствующими именами столбцов: - его имя Преподаватель - его должность Должность - количество подчиненных ему преподавателей К-во подчиненных - суммарная зарплата (salary+commission) его подчиненных СУМ зарплата подчиненных - разница между макс. и миним. зарплатами его подчиненных МАКС(зарплата)-МИН(зарплата) 5) По каждому корпусу вывести следующую информацию в соответствующих столбцах: - номер корпуса. Если корпус NULL, то вывести константу “Нет корпуса” - Корпус - к-во кафедр в корпусе - Кол-во кафедр - к-во факультетов в корпусе - Кол-во факультетов - к-во преподавателей в корпусе - Кол-во преподавателей - к-во номеров телефонов в корпусе - Кол-во телефонов 6) По каждой паре значений группа-аудитория (группа имеет занятие в этой аудитории), такой, что: - группа принадлежит факультету компьютерных наук, - а аудитория находится в одном из корпусов: 1, 3, 4, 5, 6, вывести: - номер группы, - курс группы, - номер аудитории, - корпус аудитории, - количество преподавателей, которые проводят занятия в этой группе в этой аудитории, при условии, что: - эта группа в этой аудитории имеет менее 3-х занятий и - эта группа в этой аудитории имеет занятия не более, чем по 2-м дисциплинам 7) Сколько студентов, групп и кафедр на факультете 'компьютерные науки'. Результат упорядочить по первому столбу по убыванию, затем по второму столбцу по убывании и по третьему столбцу по возрастанию. Использовать для этого алиасы столбцов

      4.11.Вариант 11

      1) Вывести среднее арифметическое, минимальное и максимальное значения выражения Salary+Commission по строкам таблицы TEACHER, для которых значение столбца Hiredate находится в диапазоне 01.01.2000-31.12.2005. Столбцы результирующей таблицы должны иметь имена: «Средняя зарплата», «МИН зарплата», «МАКС зарплата» 2) По кафедре, заведующим которой является Иванов, вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о кафедре Иванова:' - Инфо о кафедре - количество преподавателей на кафедре - Количество преподавателей - количество дисциплин, читаемых преподавателями этой кафедры - Количество дисциплин - количество групп, которым приводят занятия преподаватели этой кафедры – Количество групп 3) Вывести суммарную зарплату всех преподавателей факультета, деканом которого является Иванов. При этом по каждому преподавателю произвести независимое округление ставки и надбавки с отбрасыванием дробной части. 4) По каждой кафедре факультета, деканом которого является Иванов, вывести следующую информацию под соответствующими именами столбцов: - название кафедры Кафедра - заведующий кафедры Заведующий - кол-во преподавателей на кафедре К-во преподавателей - кол-во групп на кафедре Кол-во групп 5) По каждой должности, которую имеют преподаватели факультета, деканом которого является Иванов, вывести следующую информацию в соответствующих столбцах: - название должности. Если должность NULL, то вывести константу “НЕТ” - Должность - кол-во преподавателей, имеющих эту должность - Кол-во преподавателей - суммарная зарплата (salary+commission) этих преподавателей - Суммарная зарплата - кол-во кафедр, на которых имеются преподаватели на этой должности - Кол-во кафедр 6) По каждому факультету, деканом которого является ИВАНОВ, вывести: - его название, - должность декана, - количество преподавателей на факультете и - суммарную зарплату (salary+commission) всех преподавателей факультета при условии, что: - разница между фондом финансирования факультета и суммарной зарплатой всех преподавателей факультета больше 10000 7) Сколько кафедр и сколько преподавателей на факультете 'компьютерные науки' и какова средняя зарплата (salary+commission) преподавателей на этом факультете. Результат упорядочить по первому столбцу по возрастанию, по второму – по убыванию и по третьему – по убыванию. Использовать для этого алиасы столбцов.

      4.12.Вариант 12

      1) Вывести среднее арифметическое количества студентов в группах, минимальное значения рейтинга, максимальное значение рейтинга, процент минимального значения рейтинга по отношению к сумме минимального и максимального значения рейтингов. Столбцы результирующей таблицы должны иметь имена: - Среднее к-во студентов - МИН рейтинг - МАКС рейтинг - Процент 2) По факультету, деканом которой является Иванов, вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о факультете Иванова:' - Инфо о факультете - количество кафедр на факультете - К-во кафедр - количество преподавателей - К-во преподавателей - суммарная зарплата (salary+commission) - Зарплата по фак-ту - средняя зарплата по факультету - Средняя зарплата - разница между максимальной и минимальной зарплатой - Разница в зарплате 3) Вывести суммарную зарплату всех преподавателей факультета, деканом которого является Иванов. При этом по каждому преподавателю произвести округление его зарплаты (Salary+Commission) с отбрасыванием дробной части. 4) Для каждого факультета вывести его название и разницу между ее фондом и суммарной зарплатой (salary+commission) всех преподавателей факультета. 5) По каждому курсу (имеющемуся в таблице SGROUP) факультета компьютерных наук вывести следующую информацию в соответствующих столбцах: - номер курса, если курс равен NULL, вывести константу “Отсутствует” - Курс - количество групп на курсе - К-во групп - количество студентов на курсе - К-во студентов - среднее количество студентов в группе на курсе - Сред. к-во студентов в группе - максимальный рейтинг группы на курсе - МАКС рейтинг - к-во кафедр, на которых имеются группы на данном курсе - К-во кафедр 6) По каждому факультету с фондом финансирования в диапазоне 20000-300000 и расположенном в одном из корпусов 1, 3, 4, 5, 6, 8 вывести: - название факультета, - имя декана, - суммарный фонд финансирования всех его кафедр, - разницу между фондом финансирования факультета и суммарным фондом финансирования всех его кафедр при условии: - средний фонд финансирования кафедр факультета меньше, чем половина максимального фонда финансирования среди всех кафедр этого факультета 7) Для каждого преподавателя факультета 'компьютерные науки' вывести его имя, количество лекций, которые он имеет, и количество дисциплин, которые он читает, и количество групп, которым он читает лекции.. Результат упорядочить по количеству групп по возрастанию, по количеству лекций по возрастанию и по фамилии преподавателя по убыванию

      4.13.Вариант 13

      1) Вывести среднее арифметическое, сумму зарплаты (salary+commission), количество преподавателей, сумму зарплаты, разделенную на количество преподавателей, относительно всех строк таблицы TEACHER. . Столбцы результирующей таблицы должны иметь имена: - Средняя зарплата - Сумма зарплаты - Количество строк - Выражение 2) По аудитории 313 корпуса 6 вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о аудитории 313 к. 6:' - Инфо о ауд. 313 к.6 - количество кафедр, преподаватели которых имеют в ней занятия - Кол-во кафедр-преподаватели - количество кафедр, группам которых проводят в ней занятия - Кол-во кафедр-группы - количество преподавателей, которые проводят в ней занятия - Кол-во преподавателей - количество групп, которым проводятся в ней занятия - Кол-во групп - количество дисциплин, которые читаются в ней - Кол-во дисциплин 3) Вывести суммарную зарплату всех преподавателей факультета, деканом которого является Иванов. При этом вычисленный результат округлить с отбрасыванием дробной части. 4) Для каждой пары возможных значений преподаватель-группа вывести: - имя преподавателя - номер группы - количество лекций, читаемых этим преподавателем в этой группе - количество предметов, которые читает этот преподаватель этой группе 5) По каждой паре значений группа-курс из таблицы SGROUP вывести следующую информацию в соответствующих столбцах: - номер группы. Если группа равна NULL, вывести константу “Отсутствует” - Группа - номер курса. Если курс равен NULL, вывести константу “Нет” - Курс - к-во преподавателей, преподающих в этой группе - К-во преподавателей - к-во дисциплин, преподающих в этой группе - К-во дисциплин - к-во занятий, имеющихся в этой группе на этом курсе - К-во занятий 6) По каждому преподавателю факультета, деканом которого является Иванов, вывести - имя преподавателя, - его должность, - количество подчиненных ему преподавателей, - количество должностей, которые имеют подчиненные преподаватели, при условии: - суммарное значение зарплат (salary+commission) всех подчиненных находится в диапазоне 700-10000, и - разница между максимальной и минимальной зарплатой среди подчиненных преподавателей меньше 2500. 7) Для каждого кафедры факультета, деканом которого является Иванов, вывести название кафедры, количество преподавателей на кафедре и количество групп на кафедре. Результат упорядочить по третьему столбцу по возрастанию, по первому столбцу по убыванию и по второму столбцу – по убыванию. Использовать для этого порядковые номера столбцов результирующей таблицы.

      4.14.Вариант 14

      1) Вывести среднее арифметическое ставки, среднее арифметическое надбавки, среднее арифметическое ставки+надбавки, процент отношения максимальной надбавки к максимальной ставке, разницу между максимальной датой приема на работу и минимальной датой приема на работу для преподавателей, принятых на работу в диапазоне 05.06.1998-31.12.2007. Столбцы результирующей таблицы должны иметь имена: - Средняя ставка - Средняя надбавка - Средняя зарплата - Процент надбавка/ставка - Разница дат 2) Относительно преподавателя Иванова вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Подчиненные Иванова' - Что выводится? - количество подчиненных Иванова - Кол-во подчиненных - количество подчиненных подчиненных Иванова - Кол-во подчиненных подчиненных 3) Вывести среднюю зарплату всех преподавателей факультета, деканом которого является Иванов. При этом по каждому преподавателю произвести независимое округление ставки и надбавки с отбрасыванием дробной части. 4) Для всех должностей, которые имеются у преподавателей факультета 'компьютерные науки' вывести следующую информацию под соответствующими именами столбцов: - название должности - Должность - количество преподавателей, которые имеют эту должность - Кол-во преподавателей - суммарная зарплата (salary+commission) всех преподавателей - Суммарная зарплата с этой должностью - разницу между максимальной и минимальной зарплатой среди - Разница в зарплате преподавателей с этой должностью 5) Для каждой пары значений корпус-этаж из таблицы ROOM вывести следующую информацию в соответствующих столбцах: - номер корпуса, Если он равен NULL, вывести константу “Нет” – Корпус - номер этажа. Если он NULL, вывести константу “Отсутствует” – Этаж - кол-во аудиторий на этаже корпуса - К-во аудитоорий - общее количество занятий, проводящихся на этом этаже корпуса - К-во занятий - к-во преподавателей, проводящих занятия на этом этаже корпуса - К-во преподавателей 6) По каждому преподавателю с зарплатой (salary+commission) в диапазоне 1000-3000, принятому на работу в диапазоне дат 01.01.2000-31.12.2008 вывести: - имя преподавателя, - дату поступления на работу, - количество подчиненных его подчиненных, - суммарную зарплату (salary+commission) всех подчиненных его подчиненных, при условии: - количество его подчиненных не превышает 3 - средняя зарплата всех подчиненных его подчиненных больше 500 7) По каждой дисциплине, которая читается преподавателем факультета, расположенном в одном из корпусов 1, 3, 4, 5, 6, 7, 8 вывести - название дисциплины - количество аудиторий, в которых эта дисциплина читается, - количество занятий, которые проводятся по этой дисциплине при условии, что количество преподавателей, читающих эту дисциплину, находится в диапазоне 1-5. Результат упорядочить по третьему столбцу по возрастанию, по второму столбцу по убыванию и первому столбцу по убыванию. Использовать для этого порядковые номера столбцов результирующей таблицы

      4.15.Вариант 15

      1) Вывести минимальное количество студентов в группе, максимальное количество студентов в группе, среднее количество студентов в группе и суммарное количество студентов во всех группах, разделенное на количество групп. Столбцы результирующей таблицы должны иметь имена: - МИН группа - МАКС группа - Средняя группа - Вычисленное среднее 2) Вывести количество подчиненных у преподавателя, у которого один из подчиненных является Петров 3) Вывести среднюю зарплату всех преподавателей факультета, деканом которого является Иванов, заменяя ставку или надбавку на 0, если ее значение равно NULL. Вычисленную среднюю зарплату округлить с отбрасыванием дробной части. 4) По каждой дисциплине, читаемой преподавателем-профессором, вывести следующую информацию под соответствующими именами столбцов: - название дисциплины -Дисциплина - количество преподавателей, которые читают эту дисциплину - К-во преподавателей - количество кафедр, преподаватели которых эту дисциплину читают - К-во кафедр - количество групп, которым эта дисциплина читается - К-во групп 5) Для каждой группы 3-го курса кафедры ИПО факультета компьютерных наук вывести следующую информацию в соответствующих столбцах: - номер группы. Если номер равен NULL, вывести константу “Нет номера” - Группа - день недели. Если день недели равен NULL, вывести константу “Отсутствет” - День недели - количество пар, которые имеет группа в этот день - К-во занятий - к-во преподавателей, проводящих занятия в этой группе в этот день - К-во преподавателей 6) Для каждой тройки значений преподаватель-группа-дисциплина таких, что указанный преподаватель преподает указанной группе указанную дисциплину, вывести - имя преподавателя, - номер группы, - название дисциплины, - количество занятий, которое имеет это этот преподаватель для этой группы по этой дисциплине, при условии, что: - указанный преподаватель преподает указанной группе указанную дисциплину не более, чем в 3-х аудиториях 7) По каждому факультету, деканом которого является ИВАНОВ, вывести: - его название, - должность декана, - количество преподавателей на факультете и - суммарную зарплату (salary+commission) всех преподавателей факультета при условии, что: - разница между фондом финансирования факультета и суммарной зарплатой всех преподавателей факультета меньше 100000. Результирующую таблицу упорядочить по третьему столбцу по возрастанию, по второму столбцу по убывнию и по четвертому столбцу по убыванию

      4.16.Вариант 16

      1) Вывести значение следующего выражения: суммарную зарплату (salary+commission) по всем преподавателям умножить на количество должностей и разделить на количество преподавателей. Столбец результирующей таблицы должен иметь имя «Выражение» 2) Сколько дисциплин читается студентам на факультете 'компьютерные науки' 3) Вывести дважды средний рейтинг всех групп факультета компьютерных наук. В первом случае NULL игнорируются. Во втором случае NULL учитывается как значение 0. Почему эти значения могут отличаться? 4) Для каждой пары значений группа-дисциплина, имеющих (групп) один из следующих рейтингов: 10-30, 45, 55-70 или NULL, вывести следующую информацию под соответствующими именами столбцов: - номер группы -Группа, - курс группы -Курс, - рейтинг группы -Рейтинг, - дисциплина, которая читается группе -Дисциплина, - количество занятий, проводимых для этой группе по этой дисциплине -Кол-во лекций, - количество преподавателей, которые проводят занятия в этой группе по этой дисциплине – Кол-во преподаватаелей, - количество аудиторий - Кол-во аудиторий 5) По каждой зарплате (salary+commission), выплачиваемой преподавателям факультета, деканом которого является Иванов, вывести эту зарплату, количество преподавателей с этой зарплатой и количество кафедр, на которых работают эти преподаватели. Соответствующие столбцы имеют названия: «Зарплата», «Кол-во преподавателей» и «Кол-во кафедр» 6) По каждому преподавателю факультета, деканом которого является Иванов, вывести - имя преподавателя, - его должность, - количество подчиненных ему преподавателей, - количество должностей, которые имеют подчиненные преподаватели, при условии: - суммарное значение зарплат (salary+commission) всех подчиненных находится в диапазоне 700-10000, и - разница между максимальной и минимальной зарплатой среди подчиненных преподавателей меньше 2500. 7) Для каждого преподавателя факультета 'компьютерные науки' вывести его имя, количество лекций, которые он имеет, и количество дисциплин, которые он читает, и количество групп, которым он читает лекции.. Результат упорядочить по количеству групп по возрастанию, по количеству лекций по возрастанию и по фамилии преподавателя по убыванию

      4.17.Вариант 17

      1) Чему равно следующее выражение: сложить суммарный фонд финансирования всех кафедр, разделенный на количество кафедр, с максимальным фондом, разделенным на минимальный фонд финансирования. Столбец результирующей таблицы должен иметь имя «Сложное выражение» 2) Сколько кафедр и сколько преподавателей на факультете 'компьютерные науки' и какова средняя зарплата (salary+commission) преподавателей на этом факультете 3) Вывести перечень корпусов, в аудиториях которых проводят занятия преподаватели факультета, деканом которого является Иванов 4) По каждой кафедре факультета, деканом которого является Иванов, вывести следующую информацию под соответствующими именами столбцов: - название кафедры Кафедра - заведующий кафедры Заведующий - кол-во преподавателей на кафедре К-во преподавателей - кол-во групп на кафедре Кол-во групп 5) По каждой группе преподавателей кафедры ИПО с диапазоном даты поступления на работу 01.01.2000-31.12.2007, фамилии которых начинаются на одну букву, вывести эту букву, количество преподавателей и суммарную зарплату (salary+commission) этих преподавателей. Соответствующие столбцы имеют названия: «Б», «Кол-во преподавателей» и «Суммарная зарплата» 6) По каждому преподавателю с зарплатой (salary+commission) в диапазоне 1000-3000, принятому на работу в диапазоне дат 01.01.2000-31.12.2008 вывести: - имя преподавателя, - дату поступления на работу, - количество подчиненных его подчиненных, - суммарную зарплату (salary+commission) всех подчиненных его подчиненных, при условии: - количество его подчиненных не превышает 3 7) Сколько студентов, групп и кафедр на факультете 'компьютерные науки'. Результат упорядочить по первому столбу по убыванию, затем по второму столбцу по убывании и по третьему столбцу по возрастанию. Использовать для этого алиасы столбцов

      4.18.Вариант 18

      1) Какова минимальная и максимальная зарплаты (с учетом ставки и надбавки) среди всех преподавателей-профессоров, принятых на работу в диапазоне дат 01.01.2000-31.12.2002. Столбцы результирующей таблицы должны иметь имена: «МИН зарплата» и «МАКС зарплата» 2) По всем преподавателям, занимающим должности профессоров или доцентов или ассистентов, факультета 'компьютерные науки' вывести следующую информацию под соответствующими именами столбцов: - строковая константа 'Инфо о фак-те компьютерных наук:' - Что выводится? - количество таких преподавателей - Кол-во преподавателей - Количество читаемых ими дисциплин - Кол-во дисциплин - Количество групп, в которых они проводят занятия - Кол-во групп 3) Вывести перечень должностей, которые имеют преподаватели факультета, деканом которого является Иванов 4) Для каждой пары возможных значений преподаватель-группа вывести: - имя преподавателя - номер группы - количество лекций, читаемых этим преподавателем в этой группе - количество предметов, которые читает этот преподаватель этой группе 5) По каждому корпусу вывести следующую информацию в соответствующих столбцах: - номер корпуса. Если корпус NULL, то вывести константу “Нет корпуса” - Корпус - к-во кафедр в корпусе - Кол-во кафедр - к-во факультетов в корпусе - Кол-во факультетов - к-во преподавателей в корпусе - Кол-во преподавателей - к-во номеров телефонов в корпусе - Кол-во телефонов 6) Для каждой тройки значений преподаватель-группа-дисциплина таких, что указанный преподаватель преподает указанной группе указанную дисциплину, вывести - имя преподавателя, - номер группы, - название дисциплины, - количество занятий, которое имеет это этот преподаватель для этой группы по этой дисциплине, при условии, что: - указанный преподаватель преподает указанной группе указанную дисциплину не более, чем в 3-х аудиториях 7) По каждой дисциплине, которая читается преподавателем факультета, расположенном в одном из корпусов 1, 3, 4, 5, 6, 7, 8 вывести - название дисциплины - количество аудиторий, в которых эта дисциплина читается, - количество занятий, которые проводятся по этой дисциплине при условии, что количество преподавателей, читающих эту дисциплину, находится в диапазоне 1-5. Результат упорядочить по третьему столбцу по возрастанию, по второму столбцу по убыванию и первому столбцу по убыванию. Использовать для этого порядковые номера столбцов результирующей таблицы

      5.Контрольные вопросы

      1. Какие типы фукций SQL вы знаете?
      2. Какая область действия агрегатных функций при наличии и отсутствии фразы GROUP BY?
      3. В каких фразах предложения SELECT могут использоваться агрегатные функции?
      4. Что означают фразы DISTINCT и ALL в агрегатных функциях?
      5. Как агрегатные функции оперируют со значениями null?
      6. Как SQL Oracle оперирует с датами?
      7. Какие выражения может содержать список select, если присутствует фраза GROUP BY?
      8. Каковы цели фраз GROUP BY и HAVING?
      9. Каковы ограничения на выражения столбцов во фразе ORDER BY, если присутствует фраза GROUP BY?
      10. Как Oracle упорядочивает значения null?

      Похожие публикации:

      1. Meetcouples что это
      2. Как изменить index html
      3. Какой ассемблер лучше fasm
      4. Почему не работает баш