Базы данных. Задание 1 в4. Создать таблицу "db2 products"
 Скачать 17.6 Kb.
|
|
1.Создать и заполнить (не менее 10 записей в каждой таблице) базу данных согласно схеме. Тип и размер полей задать самостоятельно. -- -- Создать таблицу "db2_products" -- CREATE TABLE "db2_products" ( "id" NUMBER, "name" NVARCHAR2(150) NOT NULL, "manufacturer" NVARCHAR2(150) NOT NULL, "creation_date" DATE NOT NULL, "price" NUMBER(19, 2) NOT NULL, CONSTRAINT PK_DB1_GOODS_ID PRIMARY KEY ("id") USING INDEX TABLESPACE SYSTEM STORAGE (INITIAL 64 K MAXEXTENTS UNLIMITED) ) TABLESPACE SYSTEM STORAGE (INITIAL 64 K MAXEXTENTS UNLIMITED) LOGGING; -- -- Создать таблицу "db2_customers" -- CREATE TABLE "db2_customers" ( "id" NUMBER, "surname" NVARCHAR2(50), "firstname" NVARCHAR2(50), "address" NVARCHAR2(250), "telephone" NVARCHAR2(50), "discount" NUMBER(19, 2), CONSTRAINT PK_DB2_CUSTOMERS_ID PRIMARY KEY ("id") USING INDEX TABLESPACE SYSTEM STORAGE (INITIAL 64 K MAXEXTENTS UNLIMITED) ) TABLESPACE SYSTEM STORAGE (INITIAL 64 K MAXEXTENTS UNLIMITED) LOGGING; -- -- Создать таблицу "db2_purchases" -- CREATE TABLE "db2_purchases" ( "id" NUMBER, "product_id" NUMBER NOT NULL, "customer_id" NUMBER NOT NULL, "cnt" NUMBER NOT NULL, "purchase_date" DATE NOT NULL, CONSTRAINT PK_DB2_PURCHASES_ID PRIMARY KEY ("id") USING INDEX TABLESPACE SYSTEM STORAGE (INITIAL 64 K MAXEXTENTS UNLIMITED) ) TABLESPACE SYSTEM STORAGE (INITIAL 64 K MAXEXTENTS UNLIMITED) LOGGING; -- -- Создать внешний ключ "FK_DB2_PURCHASES_CUSTOMER_ID" для объекта типа таблица "db2_purchases" -- ALTER TABLE "db2_purchases" ADD CONSTRAINT FK_DB2_PURCHASES_CUSTOMER_ID FOREIGN KEY ("customer_id") REFERENCES "db2_customers" ("id"); -- -- Создать внешний ключ "FK_DB2_PURCHASES_PRODUCT_ID" для объекта типа таблица "db2_purchases" -- ALTER TABLE "db2_purchases" ADD CONSTRAINT FK_DB2_PURCHASES_PRODUCT_ID FOREIGN KEY ("product_id") REFERENCES "db2_products" ("id"); -- -- Вывод данных для таблицы "db2_products" -- INSERT INTO "db2_products" VALUES (3, N'Йогурт питьевой', N'Ополье', '12/30/2006 00:00:00', 60); INSERT INTO "db2_products" VALUES (1, N'Молоко', N'Ополье', '01/25/2007 00:00:00', 50); INSERT INTO "db2_products" VALUES (2, N'Масло', N'ООО "Ивановское"', '01/20/2007 00:00:00', 95); INSERT INTO "db2_products" VALUES (4, N'Сельдь консерв.', N'Доброфлот', '11/16/2006 00:00:00', 150); INSERT INTO "db2_products" VALUES (5, N'Свинина тушеная', N'Микоян', '01/10/2007 00:00:00', 210); INSERT INTO "db2_products" VALUES (6, N'Печенье', N'Кондитерская фабрика "Ромашка"', '12/12/2006 00:00:00', 35); INSERT INTO "db2_products" VALUES (7, N'Порошок стиральный', N'Тайд', '08/21/2006 00:00:00', 186); INSERT INTO "db2_products" VALUES (8, N'Шампунь', N'Clear', '01/15/2007 00:00:00', 290); INSERT INTO "db2_products" VALUES (9, N'Мороженое в ваф. ст.', N'Коровка из кореновки', '12/11/2006 00:00:00', 68); INSERT INTO "db2_products" VALUES (10, N'Мороженое Пломбир', N'ООО "Холод"', '11/30/2006 00:00:00', 120); -- -- Вывод данных для таблицы "db2_customers" -- INSERT INTO "db2_customers" VALUES (1, N'Алексеев', N'Алексей', N'1', N'111111', 0); INSERT INTO "db2_customers" VALUES (2, N'Александров', N'Александр', N'2', N'222222', 0.03); INSERT INTO "db2_customers" VALUES (3, N'Борисов', N'Борис', N'3', N'333333', 0.01); INSERT INTO "db2_customers" VALUES (4, N'Васин', N'Иван', N'4', N'444444', 0); INSERT INTO "db2_customers" VALUES (5, N'Глебов', N'Глеб', N'5', N'555555', 0.02); INSERT INTO "db2_customers" VALUES (6, N'Дмитриев', N'Дмитрий', N'6', N'666666', 0); INSERT INTO "db2_customers" VALUES (7, N'Иванов', N'Иван', N'7', N'777777', 0); INSERT INTO "db2_customers" VALUES (8, N'Петров', N'Петр', N'8', N'888888', 0.05); INSERT INTO "db2_customers" VALUES (9, N'Сергеев', N'Сергей', N'9', N'999999', 0); INSERT INTO "db2_customers" VALUES (10, N'Яковлев', N'Яков', N'10', N'000000', 0); -- -- Вывод данных для таблицы "db2_purchases" -- INSERT INTO "db2_purchases" VALUES (1, 1, 1, 2, '01/03/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (2, 2, 1, 1, '01/03/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (3, 5, 1, 1, '01/03/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (4, 6, 1, 3, '01/03/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (5, 8, 2, 1, '01/05/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (6, 3, 3, 2, '01/10/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (7, 7, 3, 1, '01/10/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (8, 9, 4, 3, '01/11/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (9, 4, 5, 1, '01/12/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (10, 6, 5, 3, '01/12/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (11, 1, 6, 1, '01/13/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (12, 2, 6, 1, '01/13/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (13, 6, 6, 2, '01/13/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (14, 7, 7, 1, '01/15/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (15, 5, 8, 1, '01/16/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (16, 9, 9, 1, '01/17/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (17, 4, 10, 2, '01/19/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (18, 8, 10, 1, '01/19/2007 00:00:00'); INSERT INTO "db2_purchases" VALUES (19, 10, 10, 1, '01/19/2007 00:00:00'); COMMIT; 2. Написать запросы: 2.1. Вывести название товара, его стоимость и производителя, для товаров купленных наибольшее количество раз. SELECT "dp"."name", "dp"."price", "dp"."manufacturer" FROM "db2_products" "dp" JOIN "db2_purchases" "dp1" ON "dp"."id" = "dp1"."product_id" GROUP BY "dp"."name", "dp"."price", "dp"."manufacturer" HAVING SUM("dp1"."cnt") = (SELECT MAX(SUM("p"."cnt")) FROM "db2_purchases" "p" GROUP BY "p"."product_id") 2.2. Вывести название товара и его стоимость для товаров, произведенных в период с января по март 2007 года. SELECT "dp"."name", "dp"."price" FROM "db2_products" "dp" WHERE EXTRACT(YEAR FROM "dp"."creation_date") = 2007 AND EXTRACT(MONTH FROM "dp"."creation_date") >= 1 AND EXTRACT(MONTH FROM "dp"."creation_date") <= 3 2.3. Вывести ФИО покупателей, купивших товаров на наименьшую сумму. SELECT "dc"."surname", "dc"."firstname" FROM "db2_customers" "dc" JOIN "db2_purchases" "dp" ON "dc"."id" = "dp"."customer_id" JOIN "db2_products" "dp1" ON "dp"."product_id" = "dp1"."id" GROUP BY "dc"."surname", "dc"."firstname" HAVING SUM("dp1"."price"*"dp"."cnt"*(1-"dc"."discount")) = (SELECT MIN(SUM("dp1"."price"*"dp"."cnt"*(1-"dc"."discount"))) FROM "db2_customers" "dc" JOIN "db2_purchases" "dp" ON "dc"."id" = "dp"."customer_id" JOIN "db2_products" "dp1" ON "dp"."product_id" = "dp1"."id" GROUP BY "dc"."surname", "dc"."firstname") 2.4. Вывести фамилию и телефон покупателя (покупателей), сделавшего первую покупку в текущем месяце. SELECT DISTINCT "dc"."surname", "dc"."telephone" FROM "db2_customers" "dc" JOIN "db2_purchases" "dp" ON "dc"."id" = "dp"."customer_id" WHERE EXTRACT(YEAR FROM "dp"."purchase_date") = 2007 AND EXTRACT(MONTH FROM "dp"."purchase_date") = 1 AND EXTRACT(DAY FROM "dp"."purchase_date") = (SELECT MIN(EXTRACT(DAY FROM "dp"."purchase_date")) FROM "db2_purchases" "dp" WHERE EXTRACT(YEAR FROM "dp"."purchase_date") = 2007 AND EXTRACT(MONTH FROM "dp"."purchase_date") = 1) 2.5. Вывести название, производителя и стоимость товара, пользующегося наименьшим спросом у конкретного покупателя. SELECT "dp"."name", "dp"."manufacturer", "dp"."price" FROM "db2_products" "dp" JOIN "db2_purchases" "dp1" ON "dp"."id" = "dp1"."product_id" WHERE "dp1"."customer_id" = 1 GROUP BY "dp"."name", "dp"."manufacturer", "dp"."price" HAVING SUM("dp1"."cnt") = (SELECT MIN(SUM("dp1"."cnt")) FROM "db2_purchases" "dp1" WHERE "dp1"."customer_id" = 1 GROUP BY "dp1"."product_id") 2.6. Вывести ФИО покупателей, суммарную стоимость всех купленных каждым из них товаров без учета скидки и со скидкой. SELECT "dc"."surname", "dc"."firstname", SUM("dp1"."price"*"dp"."cnt") AS "Без скидки", SUM("dp1"."price"*"dp"."cnt"*(1-"dc"."discount")) AS "Со скидкой" FROM "db2_customers" "dc" JOIN "db2_purchases" "dp" ON "dc"."id" = "dp"."customer_id" JOIN "db2_products" "dp1" ON "dp"."product_id" = "dp1"."id" GROUP BY "dc"."surname", "dc"."firstname" 3. Создать представление, содержащее список товаров, список покупателей, дату покупки и количество купленного товара. CREATE VIEW SYS."db2_purchases_history" ( "Товар", "Фамилия покуп.", "Имя покупателя", "Дата покупки", "Количество" ) AS SELECT "dp"."name", "dc"."surname", "dc"."firstname", "dp1"."purchase_date", "dp1"."cnt" FROM "db2_products" "dp" JOIN "db2_purchases" "dp1" ON "dp"."id" = "dp1"."product_id" JOIN "db2_customers" "dc" ON "dp1"."customer_id" = "dc"."id" ORDER BY "dp1"."purchase_date", "dp"."id"; 4. Создать представление каталог товаров, произведенных в определенный временной интервал (например, за последний месяц). CREATE VIEW SYS."db2_products_list" ( "Код", "Наименование", "Производитель", "Дата пр-ва", "Цена" ) AS SELECT "id", "name", "manufacturer", "creation_date", "price" FROM "db2_products" "dp" WHERE EXTRACT(YEAR FROM "dp"."creation_date") = 2007 AND EXTRACT(MONTH FROM "dp"."creation_date") = 1; |