Упражнение 8

Създаване на заявки, които сортират, филтрират, изчисляват, използват формули и функции

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

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

1. Видове заявки за избор

a) Сортиращи заявки – показват данните, подредени във възходящ или низходящ ред по една или няколко колони

b) Филтриращи заявки – показват само данните, които удовлетворяват определени критерии, приложени в една или няколко колони

c) Заявки, използващи изчисляеми полета

d) Обобщаващи заявки (Totals)

2. Сортиращи заявки (фиг. 1) – използват възходящо (Ascending) или низходящо (Descending) сортиране. При избор на повече от една колона за сортиране то се извършва най-напред по най-лявата и след това тези записи, които имат едни и същи стойности в нея се сортират по следващите по същото правило.

3. Филтриращи заявки (фиг. 1) – използват реда Criteria: за въвеждане на всички критерии, които трябва да бъдат изпълнени едновременно (свързват се с логическата операция And ) и реда or: за записване на алтернативните критерии (свързани с «или»).

Заявките за търсене по зададен критерии се реализират като след подбора на полетата в Design View в нужните полета на реда Criteria се въведат логически изрази, осигуряващи желаното подмножество от данни в полето. При въвеждане на критерия могат да се използват както други полета, включително от други таблици, така и да се пишат по-сложни изрази, в които участват функции. Изразите могат да се въвеждат с Expression Builder (Фиг. 2) В зависимост от вида на логическия израз и вида на полето заявката може да формира:

§ търсене на точно съответствие ако в реда Criteria на съответното поле се въвде = и конкретната търсена стойност от съответния на полето тип (числов, текст, логически, дата/час или брояч

§ търсене по шаблон в който при формирането на критериите за търсене се използуват глобалните символи:

- * , заместващ всички възможни символи на всички възможни места;

- ?, заместващ всички възможни символи на съответното място;

- # , заместващ всички възможни символи цифри на съответното място;

§ търсене по зададена област от стойности ако в реда Criteria на съответното поле се въведе логическо условие, което започва с един от операторите за сравнение <, <=, >, >=, <>, =, between, like, след който се въвеждат стойностите, които трябва да са от съответствуващия на полето и на оператора за сравнение тип.

Задача 1. Отворете базата Northwind и направете заявка, която показва информация за продуктите от таблица Products, подредени по азбучен ред. Отменете подреждането по имена и сортирайте в низходящ ред по цена. Без да отменяте сортирането, подредете продуктите отново по азбучен ред.

Задача 2. Отворете базата Northwind и направете заявка, която показва информация за продуктите от таблица Products, които имат цена между 10 и 50. сортирайте ги във възходящ ред по цена. Без да отменяте сортирането, подредете продуктите отново по азбучен ред.

4. Използване на формули и функции в заявките

Изчислими полета се използуват за получаване на данни, които не се съхраняват в таблиците на базата данни но могат да се изчислят посредством съществуващи данни. Това може да стане във всяка заявка за търсене с изчислими полета. За използване на вградени функции и писане на формули Access предлага помощник – Expression Builder (Фиг. 2). Той се стартира чрез бутона Build от лентата или от контекстното меню на поле от заявката (фиг. 3).

Създаването на поле, съдържащо функции или формули става по следния начин:

1. избира се празно поле в Design изгледа на заявката, където ще се пише формулата.

2. извиква се Expression Builder чрез бутона Build (фиг. 3).

3. в прозореца на Expression Builder се изписва нужната формула, като се търсят таблици и групи функции в лявата част на прозореца от Фиг. 3, избират се техните полета или функции от другите две части чрез двукратно кликване, свързват се с нужните операции от лентата с операциите. При затваряне на прозореца на Expression Builder с ОК въведената формула се изписва в полето на заявката.

Разрешените операции зависят от типа на полето и можете да ги видите в Expression Builder (Фиг. 3): за събиране +, изваждане -, умножение *, деление / , целочислено деление \, степенуване ^, остатък от целочислено деление Mod. При изчисление на изразите се ползува правилото за изчисление от ляво на дясно при спазване на приоритета на аритметичните операции.

4. чрез командата Property от контекстното меню на полето с формулата се отваря прозореца Field Property, където можем да зададем разбираемо име, формат и описание наполето с формулата.

Задача 3. Отворете базата Northwind и направете заявка, използваща таблици Suppliers и products и съдържаща име на компания, име на продукт и поле с име total price което показва общата стойност на продуктите за всеки продукт за всяка компания. ( за всеки продукт на дадена компания цената на опаковката се умножава по броя на опаковките )

Задача 4. В базата Northwind направете заявка, използваща таблици Suppliers и products и съдържаща име на компания, име на продукт и поле с име total_Q_price което показва общата стойност на заявките за всеки продукт за всяка компания. ( за всеки продукт на дадена компания цената на опаковката се умножава по броя на опаковките и по заявеното количество )

По – важни функции, достъпни чрез билдера.

1. Обобщаващи функции – прилагат се върху съдържанието на избран стълб в заявката, като се извършва групиране по всички полета в заявката, в които не се използват обобщаващи функции. Самите обобщаващи функции се прилагат не върху цялата колона, а поотделно върху всяка от групите и за всяка група резултатът се записва в нейния ред. Използват се следните агрегатни функции:

o Count – връща броя на редовете (във всяка група) отговарящи на указано условие, което не е задължително. В случай че има условие първо се изпълнява агрегатната функция, а после се проверява условието.

o Sum - връща сумата на стойностите (във всяка група) отговарящи на указано условие

o Avg – средно аритметично

o Max – намира максималния елемент (във всяка група)

o Min - намира минималния елемент (във всяка група)

Обобщаващите функции се избират от групата SQL Aggregate, но най-често се избират от реда Total на заявките от тип Total

2. Функции за дата и време

o Date() – връща днешна дата

o Time() - връща часа в момента

o Now() - връща днешна дата + часа в момента

o Weekday(num) – връща номера на деня, съответстващ на числото (остатък при деление на 7)

o Isdate(expr) – проверява дали изразът в скобите е дата За истина връща -1, за лъжа - 0

3. Математически функции

o Round(expression [,numdecimalplaces]) - закръглява

o Sqr(number)

o Log(number) – при основа е

o Int(number) – връща цялата част

o Sgn(number) – връща 1 ако числото е положително, 0 ако е 0 и -1 ако е отрицателно

o Exp(number)

4. Функции за работа с низове

o Trim(string) – връща същия низ, но без водещите или завършващите празни интервали

o String(number, character) - повтаря character number пъти

o StrComp(string1, string2) – сравнява двата низа връща 1 ако първия е по-голям, 0 ако са еднакви и -1 втория е по-голям

o Len(string) – връща броя символи в низа

o Left(string, length) – връща левите length на брой символа на низа string

o Right(string, length) – връща десните length на брой символа на низа string

o Mid(string, start[, length])- връща length на брой символа на низа string като започва от позиция start

o InStr(string1, string2 )- връща номера на позицията, от която първият низ започва във втория

o Replace(expression, find, replace[, start[, count[, compare]]]) – заменя подниз find на низ expression с друг низ replace.

5. Обобщаващи заявки (от тип Total)

Чрез тях се прилагат обобщаавщите функции. Те се създават като във вече направена заявка в Design View се добави редът Total: от който се избират обобщаващите функции. Вмъкването на реда става като се кликне върху бутона Total (Фиг. 5). Във всяко от полетата на реда Total: трябва да бъде избрана някаква функция. В случай че никъде в реда не е избрано “Group By” заявката просто прилага избраните в реда Total: функции върху съответните колони и връща един ред, в който записва резултатите от всяка колона.

Функцията Group By предизвиква обединяване на всички записи, които имат една и съща стойност в съответната колонка и представянето им с един ред. Тогава приложените в други колони обобщаващи функции се прилагат поотделно за всяка група, формирана с “Group By”

Задача 5. Да се направи заявка, която показва имената, адресите, градовете и областите за компании от таблицата Suppliers, и онези техни продукти от Products, чиито заявено количество Quantity от Order Details e по-малко от 5 или по-голямо от 100.

Задача 6. Да се направи заявка, която показва имената, адресите, градовете и областите за компании от таблицата Suppliers, заявените продукти от Products и общото им количество Quantity от Order Details. Сортираите заявката по име на компания.

Задача 7. Аа се направи заявка,съдържаща име на компания и име на продукт (таблици Suppliers и products) която показва:

a) Най- скъпия продукт във всяка компания , като покажете и после скриете цената.

b) Най- евтиния продукт във всяка компания, като покажете и после скриете цената.

c) Продуктът, за който има най-голяма поръчка.

d) Продуктът, за който има най-малка поръчка.

Задача 8. Да се направи заявка, съдържаща

a) име на компания , име на продукт и total price (таблици Suppliers и products) която показва общата стойност на заявките за всеки продукт за всяка компания. ( за всеки продукт на дадена компания цената на опаковката се умножава по броя на опаковките )

b) име на компания и total price (таблици Suppliers и products) която показва общата стойност на заявките за всяка компания. ( за всеки продукт на дадена компания цената на опаковката се умножава по броя на опаковките и така получените произведения се събират)

Задача 9. Да се направи заявка, съдържаща име на продукт, цена и трето поле, в което е записано името, града, окръга и адреса на компанията доставчик . Последното поле да се нарече Supplier