Рисунок 36 – Детали с ценой не ниже средней

1. 
   SELECT "Detail".id, "Detail".Name, "Detail"."Price", "Detail"."Weight",
   
2. 
   "Detail"."Color", "Detail"."High cost", SUM("Amount" * "Price") AS allprice FROM "Detail", "Delivery"
   
3. 
   WHERE "Detail".id = "Delivery"."Detail_ID" AND
   
4. 
   "Price" = (SELECT MAX("Price") FROM "Detail")
   
5. 
   GROUP BY "Detail".id;
   

Рисунок 37 – Детали с максимальной ценой

1. 
   SELECT "Detail".id, "Detail".Name, "Detail"."Price", "Detail"."Weight",
   
2. 
   "Detail"."Color", "Detail"."High cost", SUM("Amount" * "Price") AS allprice FROM "Detail", "Delivery"
   
3. 
   WHERE "Detail".id = "Delivery"."Detail_ID" AND
   
4. 
   "Price" = (SELECT MIN("Price") FROM "Detail")
   
5. 
   GROUP BY "Detail".id;
   

Рисунок 38 – Детали с минимальной ценой
3. Список всех проектов, список проектов с бюджетом не ниже/не выше среднего, список проектов с максимальным/минимальным бюджетом – с указанием общей суммы поставок по данному проекту (непременное условие для всех списков):

1. 
   SELECT "Project".id, "Project".name, "Project"."City",
   
2. 
   "Project"."Address", "Project"."Budget", SUM("Amount" * "Price") AS "All Price"
   
3. 
   FROM "Project", "Delivery", "Detail"
   
4. 
   WHERE "Delivery"."Project_ID" = "Project".id
   
5. 
   AND "Delivery"."Detail_ID" = "Detail".id
   
6. 
   GROUP BY "Project".id
   
7. 
   ORDER BY id;
   

Рисунок 39 – Все проекты

1. 
   SELECT "Project".id, "Project".name, "Project"."City",
   
2. 
   "Project"."Address", "Project"."Budget", SUM("Amount" * "Price") AS "All Price"
   
3. 
   FROM "Project","Delivery","Detail"
   
4. 
   WHERE "Delivery"."Project_ID" = "Project".id
   
5. 
   AND "Delivery"."Detail_ID" = "Detail".id
   
6. 
   AND "Budget" <= (SELECT MAX("Budget") FROM "Project")/2
   
7. 
   GROUP BY "Project".id
   
8. 
   ORDER BY "Project".id;
   

Рисунок 40 – Проекты с бюджетом ниже среднего

1. 
   SELECT "Project".id, "Project".name, "Project"."City",
   
2. 
   "Project"."Address", "Project"."Budget", SUM("Amount" * "Price") AS "All Price"
   
3. 
   FROM "Project","Delivery","Detail"
   
4. 
   WHERE "Delivery"."Project_ID" = "Project".id
   
5. 
   AND "Delivery"."Detail_ID" = "Detail".id
   
6. 
   AND "Budget" >= (SELECT MAX("Budget") FROM "Project")/2
   
7. 
   GROUP BY "Project".id
   
8. 
   ORDER BY "Project".id;
   

---

Рисунок 41 – Проекты с бюджетом выше среднего

1. 
   SELECT "Project".id, "Project".name, "Project"."City",
   
2. 
   "Project"."Address", "Project"."Budget", SUM("Amount" * "Price") AS "All Price"
   
3. 
   FROM "Project","Delivery","Detail"
   
4. 
   WHERE "Delivery"."Project_ID" = "Project".id
   
5. 
   AND "Delivery"."Detail_ID" = "Detail".id
   
6. 
   AND "Budget" = (SELECT MAX("Budget") FROM "Project")
   
7. 
   GROUP BY "Project".id;
   

Рисунок 42 – Проекты с максимальным бюджетом

1. 
   SELECT "Project".id, "Project".name, "Project"."City",
   
2. 
   "Project"."Address", "Project"."Budget", SUM("Amount" * "Price") AS "All Price"
   
3. 
   FROM "Project","Delivery","Detail"
   
4. 
   WHERE "Delivery"."Project_ID" = "Project".id
   
5. 
   AND "Delivery"."Detail_ID" = "Detail".id
   
6. 
   AND "Budget" = (SELECT MIN("Budget") FROM "Project")
   
7. 
   GROUP BY "Project".id;
   

Рисунок 43 – Проекты с минимальным бюджетом
4. Сумма всех поставок, сумма поставок дорогих деталей, сумма поставок дешевых деталей, сумма поставок от надежных поставщиков, сумма поставок от ненадежных поставщиков, сумма поставок от заданного поставщика:

1. 
   SELECT COUNT(*) FROM "Delivery";
   

Рисунок 44 – Сумма всех поставок

1. 
   SELECT "Detail".Name,"Detail".id,COUNT(*), SUM("Delivery"."Amount" * "Detail"."Price") AS "All Price"
   
2. 
   FROM "Delivery" INNER JOIN "Detail"
   
3. 
   ON "Delivery"."Detail_ID" = "Detail".id
   
4. 
   AND "Detail"."High cost" = 'Дорогая'
   
5. 
   GROUP BY "Detail".id;
   

Рисунок 45 – Поставки дорогих деталей

1. 
   SELECT "Detail".Name,"Detail".id,COUNT(*), SUM("Delivery"."Amount" * "Detail"."Price") AS "All Price"
   
2. 
   FROM "Delivery" INNER JOIN "Detail"
   
3. 
   ON "Delivery"."Detail_ID" = "Detail".id
   
4. 
   AND "Detail"."High cost" = 'Дешёвая'
   
5. 
   GROUP BY "Detail".id;
   

Рисунок 46 – Поставки дешёвых деталей

1. 
   SELECT COUNT(*),"Contractor".Name, "Contractor".id, SUM("Delivery"."Amount" * "Detail"."Price") AS "All Price"
   
2. 
   FROM "Delivery", "Detail", "Contractor"
   
3. 
   WHERE "Delivery"."Detail_ID" = "Detail".id AND "Delivery"."Contractor_ID" = "Contractor".id
   
4. 
   AND "Contractor"."Reliability" = 'Надёжный'
   
5. 
   GROUP BY "Contractor".id;
   

---

Рисунок 47 – Поставки от надежных поставщиков

1. 
   SELECT COUNT(*),"Contractor".Name, "Contractor".id, SUM("Delivery"."Amount" * "Detail"."Price") AS "All Price"
   
2. 
   FROM "Delivery", "Detail", "Contractor"
   
3. 
   WHERE "Delivery"."Detail_ID" = "Detail".id AND "Delivery"."Contractor_ID" = "Contractor".id
   
4. 
   AND "Contractor"."Reliability" = 'Ненадёжный'
   
5. 
   GROUP BY "Contractor".id;
   

Рисунок 48 – Поставки от ненадежных поставщиков

1. 
   SELECT COUNT(*),"Contractor".Name, "Contractor".id, SUM("Delivery"."Amount" * "Detail"."Price") AS "All Price"
   
2. 
   FROM "Delivery", "Detail", "Contractor"
   
3. 
   WHERE "Delivery"."Detail_ID" = "Detail".id AND "Delivery"."Contractor_ID" = "Contractor".id
   
4. 
   AND "Contractor".name = 'Company 6'
   
5. 
   GROUP BY "Contractor".id;
   

Рисунок 49 – Поставки от заданного поставщика

---

Смотрите также файлы

• Выделите грамматические основы, расставьте недостающие знаки препинания. Сделайте разбор предложений 19, 20.docx

• Техническое задание на проведение исследования запросов жителей Екатеринбурга на недвижимость.docx

• Кузеванова Наталья Вениаминовна гр бж 1931z психологопедагогическая безопасность образовательных систем.docx

• Проблемы формирования мотивации здорового образа жизни в Российской Федерации.pdf

• Сценарий Гимн молодежи Кубани.docx