Лабораторная работа 1213 Преобразование вывода и встроенные функции
 Скачать 233.31 Kb.
|
|
ЛАБОРАТОРНАЯ РАБОТА 12-13 Преобразование вывода и встроенные функции В SQL реализованы операторы преобразования данных и встроенные функции, предназначенные для работы со значениями столбцов и/или константами в выражениях. Использование этих операторов допустимо в запросах везде, где можно использовать выражения. Числовые, символьные и строковые константы. Несмотря на то, что SQL работает с данными в понятиях строк и столбцов таблиц, имеется возможность применения значений выражений, построенных с использованием встроенных функций, констант, имен столбцов, которые определяются как своего рода виртуальные столбцы. Они помещаются в списке столбцов и могут сопровождаться псевдонимами, Если в запросе вместо спецификации столбца SQL обнаруживает числа, то оно интерпретируется как числовая константа. Символьные константы должны указываться в одинарных кавычках. Если одинарная кавычка должна выводиться как часть строковой константы, то ее нужно предварить другой одинарной кавычкой. Пример 1. SELECT 'Фамилия', SURNAME, 'Имя', NAME, 100 FROM STUDENT; является таблица следующего вида:
Арифметические операции для преобразования числовых данных. • Унарный (одиночный) оператор - (знак минус) изменяет знак числового значения, перед которым он стоит, на противоположный. • Бинарные операторы +, -, * и / предоставляют возможность выполнения арифметических операций сложения, вычитания, умножения и деления. Пример 2. результат запроса SELECT SURNAME, NAME, STIPEND, - ( STIPEND*KURS)/2 FROM STUDENT WHERE KURS = 4 AND STIPEND >0; Будет выглядеть следующим образом.
Символьная операция конкатенации строк. Операция конкатенации, обозначаемая символом "||", позволяет соединять ("склеивать") значения двух или более столбцов символьного типа или символьных констант в одну строку. Эта операция имеет синтаксис <значимое символьное выражение> || <значимое символьное выражение>. Пример 3. SELECT SURNAME || '_' || NAME, STIPEND FROM STUDENT WHERE KURS = 4 AND STIPEND > О; Результат запроса будет выглядеть следующим образом:
Символьные функции преобразования букв различных слов в строке. LOWER — перевод в строчные символы (нижний регистр) LOWER (<строка>) • UPPER — перевод в прописные символы (верхний. Регистр) UPPER (<строка >) INITCAP — перевод первой буквы каждого слова строки в заглавную (прописную) INITCAP (<строка> Пример 4. SELECT LOWER(SURNAME), UPPER(NAME) FROM STUDENT WHERE KURS = 4 AND STIPEND > 0; Результат запроса будет выглядеть следующим образом
Символьные. строковые функции. LPAD — дополнение строки слева. LPAD (<строка>, <длина> [,<подстрока.>] <строка> дополняется слева указанной в <подстроке> последовательностью символов до указанной <длины>.(возможно, с повторением последовательности); если <подстрока> не указана, то по умолчанию <строка> дополняется пробелами; если <длина> меньше длины <строки>, то исходная <строка> усекается слева до заданной <длины>. RPAD — дополнение строки cnpава RPAD(<строка>,<длина>[, <подстрока>]) <строка> дополняется справа указанной в <подстроке> последовательностью символов до указанной <длины> (возможно, с повторением последовательности); — если <подстрока> не указана, то по умолчанию <строка> дополняется пробелами; — если <длина> меньше длины <строки>, то исходная <строка> усекается справа до заданной <длины>. LTRIM — удаление левых граничных символов LTRIM (<строка> [, <подстрока>]) — из <строки> удаляются слева символы, указанные в <подстроке>; — если <подстрока> не указана; то по умолчанию удаляются пробелы; — в <строку> справа добавляется столько пробелов, сколько символов слева было удалено, т.е. длина <строки> остается неизменной. RTRIM — удаление правых граничных символов RTRIN(<строка>[,<подстрока>]) —из <строки> удаляются справа символы, указанные в <подстроке>; - если <подстрока> не указана, то по умолчанию удаляются пробелы; - в <строку> слева добавляется, столько пробелов, сколько символов справа было удалено, т.е. длина <строки> остается неизменной. Функции LTRIM и RTRIM рекомендуется использовать при написании условных выражений, в которых сравниваются текстовые строки. Дело в том, что наличие начальных или конечных пробелов в сравниваемых операндах может исказить результат сравнения. Например, константы ' ААА' и ' ААА ' не равны друг другу. • SUBSTR — выделение подстроки SUBSTR(<строка>, <начало>[, <количесиио>]) — из <строки> выбирается заданное <количество> символов, начиная с указанной позиции в строке <начало>; — если <количество> не задано, символы выбираются с <начала> и до конца <строки>; — возвращается подстрока, содержащая число символов, заданное параметром <количество>, либо число символов от позиции, заданной параметром <начало>, до конца <строки>; — если указанное <начало> превосходит длину <строки>, то возвращается строка, состоящая из пробелов. Длина этой строки будет равна заданному <количеству> или исходной длине <строки> (при не заданном <количестве>), INSTR — поиск подстроки INSTR(<строка>, <подстрока> [, <начало поиска>'[,<номер вхождения>]]) — <начало поиска> задает начальную позицию в строке для поиска <подстроки>; если не задано, то по умолчанию принимается значение 1; — <номер вхождения> задает порядковый номер искомой подстроки; если не задан, то по умолчанию принимается значение 1; —. значимые выражения в, <начале поиска> или в <номере вхождения> должны иметь беззнаковый целый тип или приводиться к этому типу. LENGTH — определение длины строки LENGTH( — длина <строки>, тип возвращаемого значения — INT; — функция возвращает NULL, если <строка> имеет NULL-значение. Пример 5. Результат запроса SELECT LPAD(SURNAME, 10, '@' ), RPAD(NAME,10, '$'.) FROM STUDENT WHERE KURS = 3 AND STIPEND > 0; будет выглядеть следующим образом
Пример 6. Запрос SELECT SUBSTR(NAME, 1,1) ||'.' || SURNAME, CITY, LENGTH(CITY) FROM STUDENT WHERE KURS IN(2, З, 4) AND STIPEND > 0; выдаст результат
Функции работы с числами. • ABS — абсолютное значение. ABS (<значимое числовое выражение>) • FLOOR — наибольшее целое, не превосходящее заданное число с плавающей точкой FLOOR(<значимое числовое выражение>) • CEIL — наименьшее целое, которое равно или больше заданного числа CEIL(<значимое числовое выражениё>) •ROUND — Функция округления ROUND (<значимое числовое выражение>, <точность>) аргумент <точность> задает точность округления (см. пример ниже). • TRUNC- Функция усечения TRUNC (<значимое числовое выражение>, <точность>) • СОS, SIN, TAN - Тригонометрические функции СОS(<значимое числовое выражение>) SIN(<значимое числовое выражение>) TAN(значимое числовое выражение>) • COSH, SINH, TANH - Гиперболические функции СОSH(<значимое числовое выражение>) SINH(<значимое числовое выражение>) TANH(<значимое числовое выражение>) • EXP - Экспоненциальная функция ЕХР(<значимое числовое выражение>) • LN, LOG - Логарифмические функции LN(<значимое числовое выражение>) LOG(<значимое числовое выражение>) • POWER - Функция возведения в степень POWER (<значимое числовое выражение>,<показатель степени>) • SIGN - Определение знака числа SIGN(<значимое числовое выражение>) • SQRT - Вычисление квадратного корня SQRT (<значимое числовое выражение>) Пример 7. Запрос SELECT UNIV_NAME, RATING, ROUND(RATING, -1), TRUNC(RATING, -1) FROM UNIVERSITY; вернет результат
Функции преобразования значений. • Преобразование в символьную строку — TO_CHAR TO_CHAR (<значимое выражение> [, <символьный формат>]) — <значимое выражение> должно представлять числовое значение или значение типа дата-время; — для числовых значений <символьный формат> должен иметь синтаксис [S]9[9…][.9[9…]], где S – представление знака числа (при отсутствии предполагается без отображения знака), 9 — представление цифр-знаков числового значения (для каждого знакоместа). Символьный формат определяет вид отображения чисел. По умолчанию для числовых значений используется формат '999999.99'; для значений типа ДАТА-ВРЕМЯ <символьный формат> имеет вид (т. е. вид отображения значений даты и времени) — в части даты: 'DD-Mon-YY' 'DD-Mon-YYYY' 'MM/DD/YY' 'MM/DD/YYYY' 'MM.DD.YY' 'MM.DD.YYYY' В части времени: 'НН24' 'НН24:Ml' 'НН24:MI.SS’ 'НН24:Ml:SS.FF’ где: - НН24 — часы в диапазоне от 0 до 24; Ml — минуты; SS — секунды; FF — тики (сотые доли секунды) При выводе времени в качестве разделителя по умолчанию используется двоеточие (":"), но при желании можно использовать любой другой символ. Возвращаемое значение — символьное представление <значимого выражения> в соответствии с заданным <символьным форматом> преобразования. • Преобразование из символьного значения в числовое – TO_NUMBER TO_NUMBER(<значимое символьное выражение>) При этом <значимое символьное выражение> должно задавать символьное значение числового типа. • Преобразование символьной строки в дату — ТО_DATE ТО_DATE (<значимое символьное выражение> [, <символьный формат>]) - <значимое символьное выражение> должно задавать символьное значение типа ДАТА-ВРЕМЯ; — <символьный формат> должен описывать представление значения типа ДАТА-ВРЕМЯ в <значимом символьном выражении>. Допустимые форматы (в том числе и формат по умолчанию) приведены выше. Возвращаемое значение — <значимое символьное выражение> во внутреннем представлении. Тип возвращаемого значения — DАТЕ. Над значениями типа DATE разрешены следующие операции: • к значению типа DATE можно прибавлять значения типа INTERVAL, в результате чего получается значение типа DATE; • при вычитании двух значений типа DATE получается значение типа INTERVAL; • при вычитании из значения типа DATE значения типа INTERVAL получается значение типа DATE. В бинарных операциях один из операндов должен иметь значение отдельного элемента даты: только год, или только месяц, или только день. Пример 8. Запрос SELECT SURNAME, МАМЕ, BIRTHDAY, TO_CHAR(BIRTHDAY, 'DD-Mon-YYYY' ), TO_CHAR(BIRTHDAY, 'DD.ММ.YY' ) FROM STUDENT; вернет результат
Функция CAST является средством явного преобразования данных из одного типа в другой. Синтаксис этой команды имеет вид CAST <значимое выражение> AS <тип данных> • <значимое выражение> должно иметь числовой или символьный тип языка SQL (возможно, с указанием длины, точности и масштаба) или быть NULL-значением. • Любое числовое выражение может быть явно преобразовано в любой другой числовой тип. • Символьное выражение может быть преобразовано в любой числовой тип. При этом в результате такого преобразования отсекаются начальные и конечные пробелы, а остальные символы преобразуются в числовое значение по правилам языка SQL. • Если заданная явным образом длина символьного типа недостаточна и преобразованное значение не размещается в нем, то результативное значение усекается справа. • Возможно явное преобразование символьного типа в символьный с другой длиной. Если длина результата больше длины аргумента, то значение дополняется пробелами; если меньше, то усекается. • NULL преобразуется в NULL. • Числовое выражение может быть преобразовано в символьный тип. Пример 8. SELECT CAST STUDENT_ID AS CHAR(10) FROM STUDENT; УПРАЖНЕНИЯ к ЛАБ 12 1. Составьте запрос для таблицы STUDENT таким образом, чтобы выходная таблица содержала один столбец, содержащий последовательность разделенных символом ";" (точка с запятой) значений всех столбцов этой таблицы; при этом текстовые значения должны отображаться прописными символами (верхний регистр), т. е. быть представленными в следующем виде: 10; КУЗНЕЦОВ; БОРИС; 0; БРЯНСК; 8.12.1987; 10. 2. Составьте запрос для таблицы STUDENT таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: Б. КУЗНЕЦОВ; место жительства — БРЯНСК; родился — 8.12.87. 3. Составьте запрос для таблицы STUDENT таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: б. кузнецов; место жительства — брянск; родился: 8-дек-1987. 4. Составьте запрос для таблицы STUDENT таким образом, чтобы выходная таблица содержала всего один столбец в следующем виде: Борис Кузнецов родился в 1987 году. 5. Составьте запрос, выводящий фамилии, имена студентов и величину получаемых ими стипендий, при этом значения стипендий должны быть увеличены в 100 раз. 6. То же, что и в упр. 4, но только для студентов 1, 2 и 4 курсов и таким образом, чтобы фамилии и имена были выведены прописными буквами. 7 Составьте запрос для таблицы UNIVERSITY таким образом, что - бы выходная таблица содержала всего один столбец в следующем виде: Код-10; ВГУ-r. ВОРОНЕЖ; Рейтинг=296. 8. То же; что и в упр. 7, но значения рейтинга требуется округлить до первого знака (например, значение -382 округляется-до 400). Агрегирование и групповые функции Агрегирующие функции позволяют получать из таблицы сводную (агрегированную) информацию, выполняя операции над группой строк таблицы. Для задания в SELECT-запросе агрегирующих операций используются следующие ключевые слова: COUNT определяет количество строк или значений поля, выбранных посредством запроса и не являющихся NULL-значениями; SUM вычисляет арифметическую сумму всех выбранных значений данного поля; AVG вычисляете среднее значение :для всех выбранных: значений данного поля. MAX-вычисляет-наибольшее из всех выбранных значении данного поля; MIN вычисляет наименьшее из всех выбранных значений данного поля. В SELECT-запросе агрегирующие функции используются аналогично именам полей, при этом последние (имена полей) используются в качестве аргументов этих функций. Функция AVG предназначена для подсчета среднего значения поля на множестве записей таблицы. Пример 9. Для определения среднего значения поля MARK (оценки) по веем записям таблицы EXAM_MARKS можно использовать запрос с функцией AVG следующего вида: SELECT AVG(MARK) FROM EXAM_MARKS; Пример 10. Для подсчета общего количества строк в таблице следует использовать функцию COUNT со звездочкой: SELECT COUNT (*) FROM XAM_MARKS; Пример 11. При подсчете значений конкретных атрибутов аргументы DISTINCT и ALL позволяют соответственно исключать и включать дубликаты обрабатываемых функцией COUNT значений. При этом необходимо учитывать, что при использовании опции ALL неопределенные значения атрибута (NULL) все равно не войдут в число подсчитываемых значений. SELECT COUNT(DISTINCT SUBJ_ID) FROM SUBJECT; Предложение GROUP BY (ГРУППИРОВАТЬ ПО) позволяет группировать записи в подмножества, определяемые значениями какого либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе. Пример 12. Предположим, требуется найти максимальное значение оценки, полученной каждым студентом. Запрос будет выглядеть следующим образом: SELECT STUDENT_ID, MAX(MARK) FROM EXAM_МАRKS GROUP BY STUDENT_ID; Выбираемые из таблицы EXAM_MARKS записи группируются по значениям поля STUDENT_ID указанного в предложении GROUP BY, и для каждой группы находится максимальное значение поля MARK. Предложение GROUP BY позволяет применять агрегирующие функции к каждой группе, определяемой общим значением поля или полей, указанных в этом предложении. В приведенном запросе рассматриваются группы записей, сгруппированные по идентификаторам студентов. В конструкции GROUP BY для группирования может быть использовано более одного столбца. Пример 13. SELECT STUDENT_ID, SUBJ_ID, MAX(MARK) FROM EXAM_MARKS GROUP BY STUDENT_ID, SUBJ_ID; В этом случае строки вначале группируются по значениям первого столбца, а внутри этих групп — в подгруппы по значениям второго столбца. Таким образом, GROUP ВY не только устанавливает столбцы по которым осуществляется группировка, но и указывает. порядок разбиения столбцов на группы. Следует иметь в виду, что после ключевого слова SELECT должны быть использованы. только те имена столбцов, которые указаны в предложении GROUP ВY. При необходимости часть сформированных с помощью GROUP BY групп может быть исключена с помощью предложения HAVING. Пример 14. Предложение HAVING определяет критерий, по которому группы следует включать в выходные-данные (по аналогии с предложением WHERE, которое осуществляет это для отдельных строк). SELECT SUBJ_NAME, MAX(HOUR) FROM SUBJECT GROUP BY SUBJ_NAME HAVING MAX(HOUR) > 72; В условии, задаваемом предложением HAVING, должны быть указаны только поля или выражения, которые на выходе имеют единственное значение для каждой выводимой группы. УПРАЖНЕНИЯ 1. Напишите запрос для подсчета количества студентов, сдававших экзамен по предмету обучения с идентификатором 20. 2. Напишите запрос, который позволяет подсчитать в таблице EXAM_MARKS количество различных предметов обучения. 3. Напишите запрос, который для каждого студента выполняет выборку его идентификатора и минимальной из полученных им оценок. 4. Напишите запрос, который для каждого студента выполняёт выборку его идентификатора и максимальной из полученных им оценок. 5. Напишите запрос, выполняющий вывод первой по алфавиту фамилии студента, начинающейся на букву 'И'. 6. Напишите запрос, который для каждого предмета обучения выводит наименование предмета и максимальное значение номера семестра, в котором этот предмет преподается. 7. Напишите запрос, который для каждого конкретного дня сдачи экзамена выводит данные о количестве студентов, сдававших экзамен в этот день. 8. Напишите запрос, выдающий средний балл для Каждого студента. 9. Напишите запрос, выдающий средний балл для каждого экзамена. 10. Напишите запрос, определяющий количество сдававших студентов для каждого экзамена. 11. Напишите запрос для определения количества предметов, изучаемых на каждом курсе. 12. Для каждого университета напишите запрос выводящий суммарную стипендию обучающихся в нем студентов, с последующей сортировкой списка по этому значению. 13. Для каждого семестра напишите запрос, выводящий общее количество часов, отводимое на изучение соответствующих предметов. 14. Для каждого студента напишите запрос выводящий среднее значение оценок, полученных им на всех экзаменах. 15. Для каждого студента напишите запрос, выводящий среднее значение оценок полученных им по каждому предмету. 16. Напишите запрос, выводящий количество студентов, проживающих в каждом городе. Список отсортировать в порядке убывания количества студентов. количества студентов 17. Для каждого университета напишите запрос, выводящий количество обучающихся в нем студентов, с последующей сортировкой списка по этому количеству. 18. Для каждого университета напишите запрос, выводящий количество работающих в нем преподавателей, с последующей сортировкой списка по этому количеству. 19. Для каждого университета напишите запрос, выводящий сумму стипендии, выплачиваемой студентам каждого курса. 20. Для каждого города напишите запрос, выводящий максимальный рейтинг университетов, в нем расположенных, с последующей сортировкой списка по значениям рейтингов. 21. Для каждого дня сдачи экзаменов напишите запрос, выводящий среднее значение всех экзаменационных оценок. 22. Для каждого дня сдачи экзаменов напишите запрос, выводящий максимальные оценки, полученные по каждому предмету. 23. Для каждого дня сдачи экзаменов напишите запрос, выводящий общее количество студентов, сдававших экзамены. 24. Для каждого дня сдачи экзаменов напишите запрос, выводящий общее количество экзаменов, сдававшихся каждым студентом. 25. Для каждого преподавателя напишите запрос, выводящий количество преподаваемых им предметов. 26. Для каждого предмета напишите запрос, выводящий количество преподавателей, ведущих по нему занятия 27. Напишите запрос, выполняющий вывод количества студентов, имеющих только отличные оценки. 28. Напишите запрос, выполняющий вывод количества экзаменов; сданных (с положительной оценкой) студентом с идентификатор ром 32. |