Упражнение 9

Създаване на заявки, които манипулират данни и обновяват базата

Видове заявки :

1. Заявки за избор (SELECT Query)

Избират и показват данни от различни таблици с подходящо филтриране или сортиране. Могат да изискват въвеждане на стойности от потребителя и тогава ги наричаме параметрични. Ако използват обобщаващи функции (сума, максимум, минимум, средно и др., то те са от тип Total)

2. Заявки за създаване на нова таблица (Make Table Query)

Подобни са на тези от тип SELECT, но при изпълнението си не само показват данните, но ги записват в нова таблица в базата. Създават се като първо се създаде аналогична заявка от тип SELECT , после се избере команда Make Table Query от менюто Query (Фиг.2) и в появилия се прозорец се въвежда името на новата таблица, като тя би могла да бъде в същата или в друга база и да е наистина нова или да е някоя от таблиците в базата. Заявката се записва при затваряне на прозореца и, а новата таблица се създава всеки път когато тя бъде изпълнена.

Пример 1 : заявка, която избира данни от таблицата Alphabetical List of Products при зададено условие [Alphabetical List of Products].UnitPrice)>40 и създава таблица t1, в която ги записва.

SELECT DISTINCTROW [Alphabetical List of Products].ProductID, [Alphabetical List of Products].ProductName, [Alphabetical List of Products].UnitPrice INTO t1

FROM [Alphabetical List of Products]

WHERE ((([Alphabetical List of Products].UnitPrice)>40));

3. Заявки за изтриване на данни (Delete Query)

Подобни са на тези от тип SELECT, но при изпълнението си не показват, а изтриват данните, отговарящи на съответните условия в базата. Създават се като първо се създаде аналогична заявка от тип SELECT , после се избере команда Delete Query от менюто Query (Фиг.1). Заявката се записва при затваряне на прозореца и, а изтриването става когато тя бъде изпълнена.

Пример 2 : заявка, която избира данни от таблицата Alphabetical List of Products при зададено условие [Alphabetical List of Products].UnitPrice)>40 и ги изтрива.

DELETE DISTINCTROW [Alphabetical List of Products].ProductID, [Alphabetical List of Products].ProductName, [Alphabetical List of Products].UnitPrice

FROM [Alphabetical List of Products]

WHERE ((([Alphabetical List of Products].UnitPrice)>40));

4. Заявки за обновяване на данни (Update Query)

Подобни са на тези от тип SELECT, но при изпълнението си не показват, а редактират данните, отговарящи на съответните условия в базата.

Пример: Всички цени, които са >40 се променят на 6

UPDATE DISTINCTROW [Alphabetical List of Products] SET [Alphabetical List of Products].UnitPrice = 6

WHERE ((([Alphabetical List of Products].UnitPrice)>40));

5. Заявки за добавяне на данни (Append Query) – добавят данни в таблица.

Пример: Заявка Append за добавяне на данни в таблица t1:

INSERT INTO t1

SELECT DISTINCTROW

FROM [Alphabetical List of Products]

WHERE ((([Alphabetical List of Products].UnitPrice)>40));

6. Параметрични заявки

При изпълнението си изискват потребителят да въведе данни от клавиатурата и показват само информацията, отговаряща на тези данни. Автоматичното им създаване е лесно – в израз или критерий включваме име, което не е поле от никоя от показаните в изгледа на заявката таблици или заявки. Показаната по-долу заявка е с два параметъра - Forms![Sales by Year Dialog]!BeginningDate DateTime и Forms![Sales by Year Dialog]!EndingDate DateTime Те се изписват един след друг като подсещащи съобщения в прозорците, в които потребителят трябва да въведе нужните данни.

PARAMETERS Forms![Sales by Year Dialog]!BeginningDate DateTime, Forms![Sales by Year Dialog]!EndingDate DateTime;

SELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, Format([ShippedDate],"yyyy") AS [Year]

FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID

WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between [Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year Dialog]![EndingDate]));

Задача 1. Направете по две параметрични заявки от всеки от горните видове по ваш избор. Използвайте базата Northwind.

На Фиг.4 са показани още три типа специфични заявки. Помощниците, които правят заявките от тип Crosstab Query и Duplicates Query обработват винаги само една таблица, докато Unmatched Query създава заявка на основа две таблици, като включва в нея избрани полета от първата, чиито съответни записи имат стойност в избрано поле, която не се среща като стойност на поле от втората таблица, което сме посочили, че искаме да свържем с полето от първата таблица. Полетата, които искаме да свържем трябва да са от един и същи тип.

7. Заявки от тип Crosstab Query

Основното им предназначение е да анализират данните и да изготвят заявка – справка в табличен вид, която осигурява определена статистическа обработка на данните. На Фиг.5 отдясно е показана Crosstab заявка на таблицата отляво с функция средно аритметично.

Създават се чрез помощник по следния алгоритъм:

1. стартиране на помощника за създаване на заявки чрез бутона New на базата когато сме в меню Query

2. избор на типа на заявката (фиг. 4)

3. избор на таблицата (Фиг. 6)

4. избор на данните, които ще се поставят в редовете на новата заявка

5. избор на данните, които ще се поставят в колоните на новата заявка

6. избор на данните, които ще се поставят в самата таблица и на обобщаващата функция, която ще ги обработва. (Фиг. 7)

7. задаване на име на заявката и отварянето и в изглед Design за дообработка или в Datasheet за преглеждане на данните

8. Във всички случаи после бихме могли да отворим заявсата в Design или SQL изглед и да я редактираме.

На Фиг. 8 е показана Crosstab заявка в Design View. За разлика от предишната тя използва две таблици. Втората таблица е добавена след като сме направили заявката с помощника и от нея е добавено поле. Когато редактираме Crosstab заявка, трябва да внимаваме какво избираме в реда Crosstab – Column Heading, Row Heading илиValue, защото в зависимост от тази стойност ще бъде определено местоположението на новото поле, а от там и структурата на заявката.

Задача 2. В нова база данни направете две таблици: Uchenici с полета Name, EGN (ключ), family, Address и Uspeh с полета EGN, predmet, ocenki. Направете връзка между тях чрез полето EGN. Попълнете таблиците с данни за 3 ученика с по нясолко изпитвания по 4 предмета. Направете Crosstab заявка, която показва средното аритметично на оценките на всеки от учениците по всеки от предметите и средния успех на всеки от учениците.

Има заявки, които не могат да се правят чрез графични прозорци и помощници, а само чрез директно въвеждане на SQL кода в текстов редактор. Той се отваря чрез командата SQL Specific от менюто Query (Фиг. 9) или при отваряне на SQL изгледа ( команда SQL View от менюто View, достъпна от изглед Design на заявката)

Задача 3. Разгледайте фиг. 8 и си припомнете типовете заявки. Опитайте да направите по една от тях в току що създадената от вас база в задача 2.

Направете следващите 9 заявки като използвате базата Northwind

Задача 4. Да се направи заявка, която показва доставчиците и имената на продукти с въведена от потребителя цена.

Задача 5. Да се направи заявка, която показва доставчиците , цените и имената на продукти с въведена от потребителя максимална цена.

Задача 6. Да се създаде заявка, която изтрива всички клиенти от Лондон от таблицата Customers.

Задача 7. Да се създаде заявка, която изтрива данните за всички стоки, по.скъпи от 50 $

Задача 8. Да се създаде заявка, която променя цените на всички напитки на 2 $.

Задача 9. Да се създаде заявка, която променя Australia на Austr в таблицата Suppliers.

Задача 10. Да се напише заявка, която създава таблица с полета CustomerID, ContactName, adres (съдържащ полетата CompanyName, Address,City, Region, разделени със запетаи), PostalCode, phone, fax , всичси от таблица Customers.

Задача 11. Горната заявка променете така, че да показва само данни за клиентите, чийто CustomerID започва с “СА” или “GO”.

Задача 12. Горната заявка променете така, че да показва само данни за клиентите, чийто

PostalCode e не по-дълъг от 6 символа.

Задача 13. Да се създаде нова база с 2 таблици – ученици с полета номер, име, презиме, адрес, телефон с ключ номер и таблица успех с полета номер, предмет, оценка, дата, като номер се избира от първата таблица. Да се свържат таблицине с връзка 1 към n , да се установи проверката за цялостност и да се попълнят всяка с поне 10 записа. Да се създадат следните заявки, да се прегледат без да се изпълняват : 1 заявка за създаване на таблица със същата структура като ученици и име make, с критерий за избор по номер, която да има данни; 2 заявки за избор на данни от таблиците ученици и make, с един и същи критерий за избор по номер, които да имт данни и да са сортирани по номер; 2 заявки за изтриване на данни от таблиците ученици и make, с един и същи критерий за избор по номер, които да имт данни и да са сортирани по номер; 2 заявки за обновяване на данни в таблиците ученици и make, с един и същи критерий за избор по номер, които да имт данни и да са сортирани по номер; 2 заявки за добавяне на данни към таблиците ученици и make, с един и същи критерий за избор по номер, които да имт данни и да са сортирани по номер; Да се изпълнят всички заявки при забранени каскадно триене и обновяване. Какви са резултатите в таблиците? Разрешете каскадното триене, изпълнете ги огново и обърнете внимание на разликата. Допълнете таблиците ако е необходимо и повторете всичко с разрешено каскадно обновяване. Прегледайте отново таблиците. Каква е промяната?

Задача 14. Направете заявка, включваща полета номер, име, презиме от ученици и предмет и оценка от успех със връзка INNER JOIN между таблиците в заявката. Прегледайте данните в заявката, запомнете колко записа са и сменете типа на връзката с LEFT JOIN. Промени ли се броят на записите в заявката? Сменете типа на връзката с RIGHT JOIN и потърсете промяна. Въведете няколко нови записа в ученици и повторете още веднъж преглеждането на заявката с различните връзки.