БД. ИТМО. Практикум санктПетербург 2020
 Скачать 0.94 Mb.
|
|
Задания для самостоятельной работы 1 Найти долю продаж каждого продукта (цена продукта * количество продукта), на каждый чек, в денежном выражении. 2 Вывести на экран список продуктов, их стоимость, а также разницу между стоимостью этого продукта и стоимостью самого дешевого продукта в той же подкатегории, к которой относится продукт. 53 3 Вывести три колонки: номер покупателя, номер чека покупателя (отсортированный по возрастанию даты чека) и искусственно введенный порядковый номер текущего чека, начиная с 1, для каждого покупателя. 4 Вывести номера продуктов, таких что их цена выше средней цены продукта в подкатегории, к которой относится продукт. Запрос реализовать двумя способами. В одном из решений допускается использование обобщенного табличного выражения. 5 Вывести на экран номер продукта, название продукта, а также информацию о среднем количестве этого продукта, приходящихся на три последних по дате чека, в которых был этот продукт. 54 Приложения Ниже приведена таблица со всеми логическими операторами, их использование будет иллюстрировано на практических примерах. Оператор Синтаксис Комментарий ALL скаляр { = | <> | != | > | >= | !> | < | <= | !< } ALL (подзапрос) Сравнивает скалярное значение с набором значений, находящиеся в столбце, который вернул подзапрос. Скаляр должен удовлетворять условию для всех значений. AND выражение AND выражение Стандартная конъюнкция SOME, ANY скаляр { = | <> | != | > | >= | !> | < | <= | !< } { SOME | ANY } (подзапрос) Сравнивает скалярное значение с набором значений, находящиеся в столбце, который вернул подзапрос. Скаляр должен удовлетворять условию хотя бы одного из значений. SOME и ANY – эквиваленты. BETWEEN выражение [ NOT ] BETWEEN выражение1 AND выражение2 Определяет диапазон для проверки, выражение должно находиться в диапазоне от выражения1 до выражения2 включительно. Выражения должны иметь один формат. EXISTS EXISTS(подзапрос ) Возвращает истину, если подзапрос возвращает хотя бы одно значение, в противном случае возвращает ложь. Особенность данного оператора в том, что подзапрос прекращается после нахождения первого значения, что существенно экономит ресурсы. IN выражение [ NOT ] IN (подзапрос | список[,…n]) Определяет, совпадает ли указанное значение выражения с одним из значений возвращаемым подзапросом или содержащимся в списке. LIKE выражение [ NOT ] LIKE шаблон Определяет, совпадает ли символьное выражение с указанным шаблоном. Для уточнения параметров шаблона см. пояснения после таблицы. NOT NOT логическое выражение Стандартное логическое отрицание. OR выражение OR выражение Стандартная дизъюнкция В строковых шаблонах допускаются следующие символы: 55 % – символ-шаблон, заменяющий любую последовательность символов; _ (подчеркивание) – символ-шаблон, заменяющий любой одиночный символ; [] – заменяет одиночный символ, указанный в угловых скобках, можно перечислить символы или диапазон (через дефис) символов; [^] – заменяет одиночный символ, не указанный в угловых скобках, можно перечислить символы или диапазон (через дефис) символов. Допускается использование ESCAPE последовательностей: WHERE СТРОКА LIKE ‘%[a-f][^xyz]_30!%%’ ESCAPE ‘!’ Данный пример является частью запроса, который, в частности, проверяет, совпадают ли значения в столбце СТРОКА со следующим шаблоном: любое количество символов, далее один из символов в диапазоне от ‘a’ до ‘f’ включительно, далее любой символ, кроме символов ‘x’, ‘y’ или ‘z’, далее еще один любой символ, далее 30%, и опять последовательность любых символов. Символ ‘!’ является эскейп-символом и говорит о том, что следующий за ним символ, в данном случае ‘%’, не надо рассматривать как управляющий, таким образом, в пример включен символ %, который в общем случае является служебным. Строковые функции Функция Синтаксис Комментарий ASCII ASCII(строковое выражение) Функция возвращает код ASCII первого символа строкового выражения. CHAR CHAR(числовое выражение) Преобразует код ASCII символа, числовое выражение, в символ. CHARINDEX CHARINDEX(строка для поиска, строка поиска [, номер начала поиска]) Функция выполняет поиск строки, строка для поиска, в строке, строка поиска. Можно указать номер символа, номер начала поиска, с которого начать поиск в строке, строке поиска. Функция возвращает номер позиции, строки для поиска, если таковая найдена. CONCAT CONCAT(строка1, строка2[ ,строкаN]) Возвращает строку, результат объединения строки1, строки2 … строкиN. CONCAT_WS CONCAT_WS(разделител ь, строка1, строка2[ ,строкаN]) Возвращает строку, результат объединения строки1, строки2 56 … строкиN, разделенные разделителем. DIFFERENCE DIFFERENCE(строка1, строка2) Возвращает число, разницу между значениями SOUNDEX строки1 и строки2. FORMAT FORMAT(значение, формат [, язык]) Возвращает значение в указанном формате, возможно указание языкового / регионального параметра, языка. LEFT LEFT(строка, число) Возвращает указанное число символов строки слева. LEN LEN(строка) Возвращает длину строки. LOWER LOWER(строка) Возвращает строку, все символы которой преобразованы в те же символы нижнего регистра LTRIM LTRIM(строка) Возвращает строку, у которой удалены начальные пробелы. NCHAR NCHAR(число) Возвращает символ Юникода с указанным номером. PATINDEX PATINDEX(шаблон, выражение) Возвращает начальную позицию первого вхождения шаблона в выражение или ноль, если такого нет. Выражение – это строка или столбец. QUOTENAME QUOTENAME(строка [, разделитель]) Возвращает строку с разделителями в виде правильного идентификатора SQL Server. REPLACE REPLACE(строка, шаблон, замена) Заменяет в строке все последовательности символов по шаблону на строку замены. REVERSE REVERSE(строка) Возвращает строку, где символы переставлены в обратном порядке относительно строки параметра. RIGHT RIGHT(строка, число) Возвращает указанное число символов строки справа. RTRIM RTRIM(строка) Возвращает строку, в которой удалены все завершающие пробелы. SOUNDEX SOUNDEX(строка) Возвращает четырехсимвольный код строки 57 SPACE SPACE(число) Возвращает строку из пробелов. Количество пробелов определяется числом. STR STR(число[, длинна[, количество]]) Возвращает символьные денные преобразованные из числа заданной длины, с заданным количеством знаков после запятой. STRING_AGG STRING_AGG(выражение , разделитель) Сцепляет строковые выражения, используя разделитель. Допускается использование с GROUP BY. STRING_ESCAP E STRING_ESCAPE (строка, тип) Возвращает строку с экранированными по типу символами. STRING_SPLIT STRING_SPLIT(строка, разделитель) Возвращает таблицу, созданную из подстрок строки по разделителю. STUFF STUFF(строка, начало, длинна, выражение) Вставляет одно выражение в строку. Начало определяет, с какого символа начнется вставка, и какова будет длина удаленной подстроки. SUBSTRING SUBSTRING(строка, начало, длинна) Возвращает подстроку из строки, указанной длины с позиции начала. TRANSLATE TRANSLATE(аргумент1, аргумент2, аргумент3) Возвращает строку, представленную в качестве аргумента1, после преобразования символов, аргумент2, в конечный набор символов, аргумент3. TRIM TRIM([символы FROM] строка) Удаляет указанные символы из начала и конца строки. UNICODE UNICODE(строка) Возвращает целочисленное значение, соответствующее стандарту Юникод для первого символа строки. UPPER UPPER(строка) Возвращает строку, преобразованную в строку, где все символы переведены в верхний регистр. Функции работы с датой и временем 58 Функция Синтаксис Комментарий SYSDATETIME SYSDATETIME () Возвращает системное время компьютера, на котором запущен экземпляр СУБД. Возвращаемый тип данных – datetime2(7). SYSDATETIMEO FFSET SYSDATETIMEOFFSET ( ) Возвращает системное время компьютера, на котором запущен экземпляр СУБД. Возвращаемый тип данных – datetimeoffset(7). SYSUTCDATETI ME SYSUTCDATETIME ( ) Возвращает системное время компьютера, на котором запущен экземпляр СУБД. Возвращаемый тип данных – datetime2(7) в формате UTC. CURRENT_TIME STAMP CURRENT_TIMESTAMP Возвращает системное время компьютера, на котором запущен экземпляр СУБД. Возвращаемый тип данных – datetime. Данная конструкция не является функцией, это эквивалент функции GETDATE ( ). GETDATE GETDATE ( ) Возвращает системное время компьютера, на котором запущен экземпляр СУБД. Возвращаемый тип данных – datetime. GETUTCDATE GETUTCDATE ( ) Возвращает системное время компьютера, на котором запущен экземпляр СУБД. Возвращаемый тип данных – datetime2 в формате UTC. DATENAME DATENAME ( часть , дата ) Возвращает строку символов, которая является частью даты. Формат части даты: • year или yy, yyyy • quarter или qq, q • mONth или mm, m • dayofyear или dy, y • day или dd, d • week или wk, ww • weekday или dw, w 59 • hour или hh • mINute или mi, n • secONd или ss, s • millisecONd или ms • microsecONd или mcs • nanosecONd или ns • TZoffset или tz • ISO_WEEK или ISOWK, ISOWW DATEPART DATEPART ( часть , дата ) Возвращает целое число, являющееся частью даты. Формат части такой же, как и у функции DATENAME DAY DAY ( дата ) Возвращает целое число, являющееся частью даты. MONTH MONTH ( дата ) Возвращает целое число, являющееся частью даты. YEAR YEAR ( дата ) Возвращает целое число, являющееся частью даты. DATEFROMPART S DATEFROMPARTS ( год , месяц , день ) Возвращает дату в формате date, соответствующую году, месяцу и дню DATETIME2FRO MPARTS DATETIME2FROMPARTS ( год, месяц, день, час, минуты, секунды, доли_секунд, точность) Возвращает дату в формате datetime2, соответствующую году, месяцу, дню, часу, минуте, секунде, доли секунды с заданной точностью. DATETIMEFROM PARTS DATETIMEFROMPARTS ( год, месяц, день, час, минуты, секунды, миллисекунды) Возвращает дату в формате datetime, соответствующую году, месяцу, дню, часу, минуте, секунде, миллисекунде. DATETIMEOFFS ETFROMPARTS DATETIMEOFFSETFROMPA RTS ( год, месяц, день, час, минуты, секунды, доли_секунд, смещение_в_часах, смещение_в_минутах, точность) Возвращает дату в формате datetimeoffset, соответствующую году, месяцу, дню, часу, минуте, секунде, доли секунды с заданной точностью, с учетом смещения в часах и минутах. SMALLDATETIM EFROMPARTS SMALLDATETIMEFROMPAR TS ( год, месяц, день, час, минуты ) Возвращает дату в формате smalldatetime, соответствующую году, месяцу, дню, часу, минуте. TIMEFROMPART S TIMEFROMPARTS (час, минуты, секунды, доли_секунд, точность) Возвращает дату в формате time, соответствующую часу, минуте, секунде, доли секунды с заданной точностью. 60 DATEDIFF DATEDIFF ( часть , начальная_дата , конечная_дата ) Возвращает целое число, INt, разницу между начальной датой и конечной датой в частях. Формат части аналогичен формату части для функции DATENAME. DATEDIFF_BIG DATEDIFF_BIG ( часть , начальная_дата , конечная_дата ) Возвращает целое число, bigINt, разницу между начальной датой и конечной датой в частях. Формат части аналогичен формату части для функции DATENAME. DATEADD DATEADD (часть , число , дата ) Возвращает новую дату, типа datetime, соответствующую дате увеличенной на то количество частей, которое определено числом. EOMONTH EOMONTH ( дата [, число ] ) Возвращает дату последнего дня того месяца, который содержит указанную дату. К указанной дате можно добавить число, определяющее, на сколько месяцев сместить дату. SWITCHOFFSET SWITCHOFFSET ( дата , зона) Возвращает дату, типа datetimeoffset, соответствующую введенной дате, типа datetimeoffset, со смещением на часовую зону. TODATETIMEOF FSET TODATETIMEOFFSET (дата , зона) Возвращает дату, типа datetimeoffset, соответствующую введенной дате, типа datetime2, со смещением на часовую зону. ISDATE ISDATE ( выражение ) Возвращает 1, если выражение соответствует формату datetime или smalldatetime, и 0 во всех других случаях. 61 Модель данных Модель данных 62 Повышев Владислав Вячеславович Практикум Базы данных. Практикум В авторской редакции Редакционно-издательский отдел Университета ИТМО Зав. РИО Н.Ф. Гусарова Подписано к печати Заказ № Тираж Отпечатано на ризографе 63 Редакционно-издательский отдел Университета ИТМО 197101, Санкт-Петербург, Кронверский пр., 49 |