Главная страница
Навигация по странице:

  • CREATE VIEW products_by_month AS SELECT month(order.order_date) AS month, GROUP_CONCAT(DISTINCT product.Name) AS Assortment

  • AND lr.order-product.Product_ID = lr.product.ID_prod GROUP BY 1;

  • CREATE VIEW products_by_clients AS SELECT client.ID_client,client.name AS Client,order-product.Product_ID, product.Name AS Product

  • WHERE lr.order-product.Order_ID = lr.order.ID_order AND client.ID_client = order.ID_client AND lr.order-product.Product_ID = lr.product.ID_prod

  • CREATE VIEW clients_orders AS SELECT Client,min(order_date) AS First_order,max(order_date) AS Last_order

  • WHERE A.ID_client = B.ID_client GROUP BY B.ID_client;

  • CREATE VIEW orders AS SELECT order_id, order.exec_date AS ex_date, order.ID_point AS point

  • AND order-product.Product_ID = product.ID_prod;

    		•

    бд. бд БАНК_ЗАДАНИЙ. Удаленный доступ к базе с любого хоста


    Скачать 0.91 Mb.
    НазваниеУдаленный доступ к базе с любого хоста
    Дата08.10.2021
    Размер0.91 Mb.
    Формат файлаdocx
    Имя файлабд БАНК_ЗАДАНИЙ.docx
    ТипДокументы
    #243833
    страница2 из 8
    1   2   3   4   5   6   7   8

    WHERE lr.`order-product`.Order_ID = lr.`order`.ID_order

    AND lr.`order-product`.Product_ID = lr.product.ID_prod

    GROUP BY name;



    1. Создать представление, содержащее вычислимые поля и поля с форматированием данных.

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

    В названии столбцов также используем алиасы. В функции group_counat мы использовали команду dictinct, чтобы выводить каждый вид продукции только один раз, а не столько раз, сколько он был заказан.

    CREATE VIEW products_by_month

    AS

    SELECT month(order.order_date) AS month,

    GROUP_CONCAT(DISTINCT product.Name) AS Assortment,

    sum(product.Price * order-product.Amount) AS Amount

    FROM order-product, order, product

    WHERE lr.order-product.Order_ID = lr.order.ID_order

    AND lr.order-product.Product_ID = lr.product.ID_prod

    GROUP BY 1;

    1. Создать представление, согласно требованиям, представленным в задании.

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

    2. Для каждого заказчика вести историю заказов в следующем виде: имя клиента, дата первого заказа, дата последнего заказа, виды заказанной продукции и общее их количество через запятую в формате: «продукция1 – количество, продукция2 – количество…», общая сумма заказов.

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

    CREATE VIEW products_by_clients

    AS

    SELECT client.ID_client,client.name AS Client,order-product.Product_ID,

    product.Name AS Product,

    sum(`order-product.Amount) AS Amt,

    sum(product.Price * order-product.Amount) AS Total

    FROM client,order,order-product,product

    WHERE lr.order-product.Order_ID = lr.order.ID_order

    AND client.ID_client = order.ID_client

    AND lr.order-product.Product_ID = lr.product.ID_prod

    GROUP BY client.ID_client, order-product.Product_ID;

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

    CREATE VIEW clients_orders

    AS

    SELECT Client,min(order_date) AS First_order,max(order_date) AS Last_order,

    GROUP_CONCAT(DISTINCT Product, " - ", B.Amt) AS Assortment,

    sum(Total) AS Total

    FROM order A, products_by_clients B

    WHERE A.ID_client = B.ID_client

    GROUP BY B.ID_client;

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

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

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

    CREATE VIEW orders

    AS

    SELECT order_id,

    order.exec_date AS ex_date,

    order.ID_point AS point,

    product.Price * order-product.Amount AS summa

    FROM order-product, order, product

    WHERE order-product.Order_ID = order.ID_order

    AND order-product.Product_ID = product.ID_prod;

    Задание 22.1.О

    Написать представления в соответствии со своим вариантом.

    Вариант
    Описание задания

    1

    1. Создать представление, в котором вывести данные о договорах имеют сумму страхования менее 55000 и тарифную ставку более 600.

    2. Вывести данные о филиалах, в названии которых неизвестны вторая и четвертая и пятая буквы, а телефон начинается с номера 416.

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

    4. Создать вычисляемое поле Процент от вида страхования. Если код вида страхования не менее 4, то начисляется 6% от Платежа, иначе – 8%.

    5. Вывести дату заключения договора, страховую сумму, которая не менее 200000 руб., название вида страхования. Сортировать по дате.

    2

    1. Создать представление, в которомвывести цену номера более 5000 и комфортность полулюкс.

    2. Вывести данные о номерах, в которых количество человек в номере от 1 до 3, а цена менее 3500 руб.

    3. Вывести количество человек, которые освободили номера в каждом году. Показать количество, не превышающее 5 человек, а код номера 2 или 4.

    4. Создать вычисляемое поле, которое определит клиентов старшее 44 лет.

    5. Вывести номер, количество человек в номере, комфортность «1 категории» и «2 категории», в которых цена от 3000 руб. до 5000 руб.

    3

    1. Создать представление, в котором вывести данные о категории товаров, комиссионные за которые составляет 300, а дата возврата 01.02.2019.

    2. Вывести ФИО клиентов, у которых серия паспорта «4578», или «4682», или «4408», а дата выдачи паспорта в пределах от 14.07.1998 до 16.12.2003.

    3. Вывести сумму комиссионных, полученных с каждого клиента, если сумма превышает 400 руб., а дата сдачи от 16.02.2018 по 30.06.2018.

    4. Создать вычисляемое поле, по которое определяет пора ли менять паспорт клиенту в связи с достижением 25 лет или 45 лет.

    5. Вывести название категории товаров, ФИО клиента и комиссионные, составляющие 200 руб., 400 руб., или 600 руб.

    4

    1. Создать представление, в котором вывести код товара, описание товара, дату сдачи товаров, у которых сумма более 10000.

    2. Вывести данные о товарах: «холодильник LG», или «Холодильник Bosch», или «Холодильник Samsung», с оптовой ценой, не находящейся в пределах от 27000 руб. до 48000 руб.

    3. Вывести среднее значение количества товаров по сделке, которое не превышает 200, по каждому покупателю.

    4. Создать вычисляемое поле, определяющее разницу между оптовой ценой и розничной ценой товара.

    5. Вывести название товара с розничной ценой не более 2000 руб. и ФИО покупателя.

    5

    1. Создать представление, в котором вывести наименование товаров, оптовую цену менее 2000 и розничную цену более 3500 и отсортировать по наименованию товаров по возрастанию.

    2. Вывести название товаров, не начинающихся на букву «А» или «Т» и ценой не более 6000 руб.

    3. Вывести общее количество товара по заказам по каждому виду товара, если количество более 4, а дата заключения договора от 05.06.2018 до 07.09.2018.

    4. Создать вычисляемое поле. Вывести ФИО и возраст покупателей пенсионного возраста. (Для женщин 55 лет, для мужчин – 60 лет).

    5. Вывести название товара и вид доставки, стоимость которой не превышает 1000 руб.

    6

    1. Создать представление, в котором вывести коды сделок, заключенные в марте 2018 г., сумма которых превышает 30000.

    2. Вывести данные о соискателях с фамилиями, не начинающихся на буквы «А» или «Д», а в имени неизвестны вторая и четвертая буквы.

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

    4. Создать вычисляемое поле Премия, которое будет выдавать данные о премии, которая рассчитываются, в размере 6% от комиссионных.

    5. Вывести ФИО соискателя, его квалификацию «преподаватель высшей категории», «инженер-программист 1 категории» или «Оператор станков с ЧПУ 5 разряда».

    7

    1. Создать представление, в котором вывести название только тех услуг, названия которых не «Мытье окон» и не «Уборка квартиры».

    2. Вывести название услуг, в которых в середине встречаются сочетание букв «вер», а стоимость услуг не находится в пределах от 12000 руб. до 72000 руб.

    3. Вывести сумму комиссионных по каждому коду клиента, если эта сумма превышает 2000 руб., а дата сделки август или октябрь 2018 г.

    4. Создать вычисляемое поле Дополнительная_скидка. Размер дополнительной скидки составляет 10% от скидки, если код вида скидки более 3.

    5. Вывести названия клиентов с видами деятельности «Строительство» «Парфюмерия», или «Машиностроение».

    8

    1. Создать представление, в котором вывести данные о деталях с количеством не 32, не 45, не 56, ценой от 25000.

    2. Вывести данные о деталях, в которых артикул начинается с символов «WAUBH54» или с «BW154» и ценой в пределах от 14000 руб. до 25000 руб.

    3. Вывести среднюю цену по поставленным деталям по каждой детали, которая превышает 455 руб., и поставка была в феврале или октябре 2018 г.

    4. Создать вычисляемое поле Новая_цена, которая вычисляется как Цена детали + 10% от цены детали.

    5. Вывести название поставщика, название детали, цену, которая имеет величину в пределах от 300 руб. до 1500 руб.

    9

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

    2. Вывести данные о нагрузке с количеством часов менее 50 и оплатой от 415 руб. в час. до 460 руб. в час.

    3. Вывести среднее количество часов по каждому виду занятий, если оно не менее 70 часов и не включает группу 5703.

    4. Создать вычисляемое поле с полным именем преподавателей (Фамилия Имя Отчество) и показать дату его рождения.

    5. Вывести преподавателя, номер группы, предмет и количество часов, значение которых от 60 до 100.

    10

    1. Создать представление, в котором вывести название предмета, семестр с объемом практик, не менее 200. Отсортировать по возрастанию.

    2. Вывести данные о предметах математика, или физика, или сопротивление материалов, у которых объем лекций от 90 до 150 за 3 семестр.

    3. Вывести средний объем лабораторных работ по каждому предмету, который не более 65 час., за 1 и 2 семестры.

    4. Создать поле Изменение, которое составляет 1,1 от объема практик.

    5. Вывести ФИО студента, название предмета и оценку, которая более 2.

    11

    1. Создать представление, в котором вывести ФИО преподавателей, со стажем работы более 15.

    2. Вывести сведения о сотрудниках с именами Ирина, или Татьяна, или Наталья, не имеющие оклад в пределах от 35000 руб. до 47000 руб.

    3. Вывести общую сумму лет стажа работы преподавателей по каждой ученой степени, если эта сумма не более 25, и должность «профессор», или «аспирант».

    4. Создать поле Вариативные_часы, которое составляет 10% от количества часов по предметам.

    5. Вывести название предмета, вид занятия количество часов, которые находятся в пределах от 60 час. до 110 час.

    12

    1. Создать представление, в котором вывести сведения о сотрудниках с именами Ирина, или Татьяна.

    2. Вывести сведения о сотрудниках с именами Ирина, или Татьяна, или Наталья, не имеющие оклад в пределах от 35000 руб. до 47000 руб.

    3. Вывести количество работ, выполненных каждым сотрудником, если это количество не превышает 4, а дата начала работ с февраля по май 2018 г.

    4. Создать вычисляемое поле Надбавка, размер которой составляет 10% от оплаты за день по определенному виду работ.

    5. Вывести ФИО сотрудников, имеющих оклад не менее 40000 руб. и начавших работу в мае 2018.

    13

    1. Создать представление, в котором вывести Название ремонта с продолжительностью 72 часа и стоимостью более 6000.

    2. Вывести название вида ремонта, продолжительность и стоимость, в которых название вида ремонта: «диагностика подвески», или «замена сцепления», или «замена рычагов»; продолжительность ремонта от 2 до 3 дней, а стоимость менее 6500 руб.

    3. Вывести количество станков по каждой стране выпуска, если оно не превышает 3 и год выпуска с 1999 по 2016 г.

    4. Создать поле Новая_Стоимость, которое выводит стоимость вида ремонта, увеличенную на 15% тем видам работ, продолжительность ремонта которых более 7 дней.

    5. Вывести марку станка и год выпуска станков, которые были сделаны в США, или в КНДР, или в Германии.

    14

    1. Создать представление, в котором вывести Код путевки и код клиента, где скидка в пределах от 500 до 1000.

    2. Вывести ФИО клиентов, у которых третья буква фамилии «а», а четвертая «с», имя начинается с буквы «П». Вывести ФИО клиентов, которые имеют фамилии, начинающиеся на буквы «А» или «С», отчество «Николаевич» или «Игоревна», или «Петрович», или «Петровна.

    3. Вывести общую скидку по путевке в каждую страну, если эта скидка более 4560 руб. и дата отправления с мая 2018 г. по октябрь 2018 г.

    4. Создать вычисляемое поле Дети. Вывести ФИО клиентов, возраст которых менее 18 лет.

    5. Вывести название страны, климат страны при стоимости не дороже 30000 руб.

    15

    1. Создать представление, в котором вывести название маршрута, дальность более 500 км, количество дней в пути 3, оплата не менее 18000 руб.

    2. Вывести ФИО водителей, имеющих стаж работы, не находящиеся в пределах от 5 до 15 лет; фамилии, начинающиеся на буквы «А» или «Т», а отчества «Федорович», или «Егорович», или «Игоревич».

    3. Вывести средний стаж работы по каждому году рождения водителей с фамилиями Коршунов, Вишнякова, Кириллов.

    4. Создать вычисляемое поле. Вывести ФИО и стаж преподавателей пенсионного возраста. (Для женщин 55 лет, для мужчин – 60 лет).

    5. Вывести название маршрута, оплата за которые от 5000 до 8000 руб. и ФИО водителя.

    16

    1. Вывести записи с названием города и дневным тарифом, который не менее 1,5 руб. Отсортировать по городу по возрастанию.

    2. Вывести название юридического лица «ООО Аврора» или «ОАО Заря», или «ИП Стрела», в которых номер телефона имеет последние цифры 281.

    3. Вывести среднее значение минут переговоров по каждому абоненту, если это значение равно 26 мин., а время суток дневное.

    4. Создать вычисляемое поле Новый_ночной_тариф, которой рассчитывается так: Ночной тариф – 0,3 руб.

    5. Вывести название абонентов, имеющих номер телефона, начинающийся с цифр 4836, которые вели переговоры в вечернее время.

    17

    1. Вывести две записи с названием видов расходов, в которых предельная норма менее 20000 руб.

    2. Вывести названия расходов и предельную норму вида расходов. Названия расходов должны быть «Канцелярские», или «Транспортные», или «Аренда», в которых предельная норма расходов, не находящихся в пределах от 1700 руб. до 100000 руб.

    3. Вывести среднюю сумму расходов по каждому сотруднику, если она не более 12000 руб. и дата расходов с марта по май 2018 г.

    4. Создать вычисляемое поле Временные_сотрудники. Временные сотрудники составляют 10% от количествапостоянных сотрудников.

    5. Вывести название вида расходов, ФИО сотрудника и сумму расходов, которая не более 10000 руб.

    18

    1. Вывести книги с залоговой стоимостью 300 или 600 и стоимостью проката 100 или 200 руб.

    2. Вывести названия расходов и предельную норму вида расходов. Названия расходов должны быть «Канцелярские», или «Транспортные», или «Аренда», в которых предельная норма расходов, не находящихся в пределах от 1700 руб. до 100000 руб.

    3. Вывести количество выданных книг по каждому читателю с января по сентябрь 2018 г., если оно превышает 7.

    4. Создать вычисляемое поле Новая стоимость проката, которое равно Стоимость проката + 15% от Стоимости проката.

    5. Вывести название книг, ФИО автора с годом издания книг с 2006 г. по 2017 г.

    19

    1. Вывести данные о выданных автомобилях, у которых, дата выдачи не 21.02.2018 г. или не 01.04.2018 г.

    2. Вывести данные об автомобилях, у которых код марки не менее 3, год выпуска, начиная с 2010, стоимость от 150000 руб. до 350000 руб., или от 550000 руб. до 700000 руб., а стоимость проката не более 60000 руб.

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

    4. Создать вычисляемое поле Возраст_автомобиля. (Текущий год – Год выпуска).

    5. Вывести марку автомобиля, тип автомобиля, год выпуска, со стоимостью не более 500000 руб.

    20

    1. Вывести две записи по погашению кредита, в которых сумма погашения менее 100000 руб. или равна 200000 руб., а штраф отсутствует.

    2. Вывести клиентов с названиями «ООО Прогресс», «ЗАО Цветы», «ОАО Сатурн», ставкой от 6% до 10%, сроком от 2 до 7 лет.

    3. Вывести общую сумму штрафов по каждому клиенту, если она менее 3000 руб. а сумма погашения кредита менее 45500 руб.

    4. Создать вычисляемое поле, определяющее количество дней выплачиваемого кредита. (Текущая дата – Дата выдачи).

    5. Вывести название вида кредитов, условия получения кредита со ставкой от 50000 руб. до 200000 руб.

    21

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

    2. Вывести данные о ценных бумагах с названиями «облигация», или «акция», или «вексель», у которых минимальная сумма сделки от 255000 руб. до 450000 руб. или 1000000 руб.

    3. Вывести среднюю котировку по каждому коду клиента, если она более 45000 руб. и дата продажи с 2015 по 2018 г.

    4. Создать вычисляемое поле Максимальная сумма сделки, которая рассчитывается так: Минимальная сумма*3.

    22

    1. Вывести названия спектаклей, год постановки которых 2015 или 2016, а бюджет 300000 руб. или 350000 руб.

    2. Вывести данные о занятости артистов, в которых роль Гамлет, или Маленький принц, или Чичиков, а стоимость годового контракта более 330000 руб.

    3. Вывести общую премию, получаемую актером за все спектакли, если она более 154000 руб. и стоимость годового контракта менее 55500 руб.

    4. Создать вычисляемое поле Дополнительный бюджет, которое составляет 5% от бюджета.

    23

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

    2. Вывести ФИО сотрудников с именами «Антон», «Александр» или «Виталий», которые имеют в ИНН третью цифру 5, восьмую цифру 9 и одиннадцатую цифру 0.

    3. Вывести среднюю стоимость лечения, если она не превышает 4500 руб. для каждого пациента, родившегося с 1981 г. по 1997 г.

    4. Создать вычисляемое поле Новая стоимость лечения, которое равно 110% от стоимости лечения.

    24

    1. Вывести данные о показателях предприятия, в которых важность показателей до 70, а единицы измерения не доллары.

    2. Вывести данные о предприятиях, у которых номер расчетного счета не заканчивается цифрами 184, а название банка «Банк Санкт-Петербург», или «Банк ВТБ», или «Газпромбанк».

    3. Вывести общую сумму значений динамики показателей по каждому предприятию, если эта сумма более 1450 за период с 2012 по 2018 г.

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

    5. Вывести общую сумму значений динамики показателей по каждому предприятию, если эта сумма более 1450 за период с 2012 по 2018 г.

    25

    1. Вывести названия передач, в которых рейтинг находится в пределах от 50, а стоимость минуты более 15000 руб.

    2. Вывести данные о передачах, в которых название в середине имеет буквы «оро», рейтинг передач от 42 до 90, или от 220 до 270; стоимость минуты менее 40000 руб.

    3. Вывести общую длительность рекламы по каждой передаче, если она более 3 мин., а дата проведения – октябрь 2018 г.

    4. Создать вычисляемое поле Новая длительность рекламы, которая составляет 90% от длительности рекламы.

    5. Вывести общую длительность рекламы по каждой передаче, если она более 3 мин., а дата проведения – октябрь 2018 г.

    26

    1. Вывести две записи с названием материала, который имеет цену 300 руб., или 320 руб.

    2. Вывести данные о продажах, в которых фамилия покупателя начинается на букву от «А» до «С»; имя Ольга, или Егор, или Светлана, а отчество не начинается с буквы «Д» или «П».

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

    4. Создать вычисляемое поле Новая цена за грамм, которое равно 115% от Цены за грамм.

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

    27

    1. Вывести названия товаров, у которых цена не 100 руб.

    2. Вывести данные о клиентах, в которых фамилия клиентов не Иванов или не Соколов, или не Кудряшов; имя начинается на буквы от «А» до «С», а отчество имеет в середине буквы «екс».

    3. Вывести общее количество проданных товаров по каждому клиенту, если оно превышает 10 и дата продажи сентябрь 2018 г.

    4. Создать вычисляемое поле, которое выводит Фамилии клиентов-пенсионеров (мужчины – 60 лет, женщины – 55 лет).

    5. Вывести общее количество проданных товаров по каждому клиенту, если оно превышает 10 и дата продажи сентябрь 2018 г.

    28

    1. Вывести название стрижки, в которой указан и пол, и стоимостьстрижки.

    2. Вывести данные о стрижках, в которых названия начинаются с буквы «К» или «С», пол женский или мужской, а стоимость не находится в пределах от 570 руб. до 750 руб.

    3. Вывести среднюю скидку за работу по каждой стрижке, если она ниже 67 руб. и дата стрижки 16.07.2018 г. или 30.08.2018.

    4. Создать вычисляемое поле Стоимость за 10 стрижек, равное стоимости за одну стрижку*10.

    5. Вывести среднюю скидку за работу по каждой стрижке, если она ниже 67 руб. и дата стрижки 16.07.2018 г. или 30.08.2018.

    29

    1. Вывести ФИО клиентов, у которых не известен признак клиента. Отсортировать по убыванию фамилии и по возрастаниюимени.

    2. Вывести данные о видах услуг, которые не включают названия «Зашить», или «Чистка верхней одежды», или «Чистка ковров», а стоимость от 340 руб. до 550 руб., или от 1300 руб. до 1500 руб.

    3. Вывести количество услуг по каждому клиенту, если оно превышает 3, а дата приема – май 2018 г.

    4. Создать вычисляемое поле скидка, равное 12% от стоимости.

    5. Вывести количество услуг по каждому клиенту, если оно превышает 3, а дата приема – май 2018 г.

    30

    1. Вывести название торговых точек, находящихся на 1, или 2этажах, с площадью более 40 кв. м и стоимостью аренды в день 3000 руб.

    2. Вывести данные о клиентах, в которых расчетный счет начинается с цифр «925» или с «391», телефон не заканчивается на цифры «135» или «873», а название «ООО Спортинвест», или «ОАО Грузопревозки», или «ЗАО Росстрой».

    3. Вывести количество арендованных торговых точек по каждому клиенту, если оно не более 2, а дата начала аренды сентябрь 2018 г.

    4. Создать вычисляемое поле определяющее количество дней между датой окончания и датой начала аренды.

    5. Вывести количество арендованных торговых точек по каждому клиенту, если оно не более 2, а дата начала аренды сентябрь 2018 г.
    1   2   3   4   5   6   7   8

    написать администратору сайта