Ta lekcija se osredotoča na SQL poizvedbe v bazo podatkov na VBA dostop... Pogledali bomo, kako se v VBA izvajajo poizvedbe INSERT, UPDATE, DELETE do baze podatkov, in se naučili tudi, kako pridobiti določeno vrednost iz poizvedbe SELECT.

Tisti, ki programirajo naprej VBA dostop pri delu z bazo podatki SQL strežniki se pogosto soočajo s tako preprosto in potrebno nalogo, kot je pošiljanje poizvedbe SQL v bazo podatkov, naj bo to INSERT, UPDATE ali preprosta poizvedba SQL SELECT. In ker smo programerji začetniki, moramo biti sposobni tudi to, zato bomo danes naredili prav to.

Teme prejemanja podatkov iz strežnika SQL, kjer smo za pridobivanje teh podatkov napisali kodo v VBA, smo se že dotaknili, na primer v članku o razkladanju podatkov v besedilno datoteko iz MSSql 2008 ali pa smo se je tudi nekoliko dotaknili v članku Razlaganje podatkov iz Accessa v predlogo Word in Excel. a tako ali drugače smo to obravnavali površno, danes pa predlagam, da o tem spregovorimo nekoliko podrobneje.

Opomba! Vsi spodnji primeri so obravnavani z uporabo projekta Access 2003 ADP in baze podatkov MSSql 2008. Če na splošno ne veste, kaj je projekt ADP, smo to upoštevali v članku Kako ustvariti in konfigurirati projekt Access ADP

Začetni podatki za primere

Recimo, da imamo test_table, ki bo vsebovala številke in imena mesecev v letu (poizvedbe so narejene z Management Studio)

USTVARI TABELE. (NI NULL, (50) NULL) NA GOJU

Kot sem rekel, bomo uporabili projekt ADP, konfiguriran za delo z MS SQL 2008, v katerem sem ustvaril testni obrazec in dodal startni gumb s podpisom "teči" ki ga moramo testirati našo kodo, tj. vso kodo bomo napisali v upravljalnik dogodkov " Pritisk na gumb».

Poizvedbe VBA INSERT, UPDATE, DELETE

Da ne bi dolgo odlašali, začnimo takoj, recimo, da moramo v našo testno tabelo dodati vrstico ( koda je komentirana)/

Private Sub start_Click () "Razglasite spremenljivko za shranjevanje niza poizvedbe Dim sql_query kot niz" Napišite poizvedbo, ki jo potrebujemo sql_query = "INSERT INTO test_table (id, name_mon) VALUES (" 6 "," June ")" "Izvedite jo DoCmd Zaženite SQL sql_query End Sub

V tem primeru se poizvedba izvede z uporabo trenutnih parametrov povezave z bazo podatkov. Lahko preverimo, ali so bili podatki dodani ali ne.

Kot lahko vidite, so bili podatki vstavljeni.

Če želite izbrisati eno vrstico, napišite naslednjo kodo.

Private Sub start_Click () "Razglasite spremenljivko za shranjevanje niza poizvedbe Dim sql_query kot niz" Napišite poizvedbo za brisanje vanj sql_query = "IZBRIŠI test_table WHERE id = 6" "Izvedite jo DoCmd.RunSQL sql_query End Sub

Če preverimo, bomo videli, da je bila zahtevana vrstica izbrisana.

Če želite posodobiti podatke, napišite poizvedbo za posodobitev v spremenljivko sql_query, upam, da je pomen jasen.

Poizvedba SELECT glede na osnovo VBA

Tu so stvari nekoliko bolj zanimive kot pri ostalih konstrukcijah SQL.

Najprej recimo, da moramo vse podatke dobiti iz tabele in jih bomo na primer obdelali in prikazali v sporočilu, vi pa jih seveda lahko uporabite za druge namene, za to zapišemo naslednje Koda

Private Sub start_Click () "Razglasi spremenljivke" Za niz zapisov iz baze podatkov Dim RS As ADODB.Recordset "Niz poizvedbe Dim sql_query As String" Niz za prikaz zbirnih podatkov v sporočilu Dim str As String "Ustvari nov objekt za niz zapisov RS = New ADODB .Recordset "String poizvedbe sql_query =" SELECT id, name_mon FROM test_table "" Izvedite poizvedbo z uporabo trenutnih nastavitev povezave projekta RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Ni zanka skozi zapis (RS.EOF)" Izpolnite spremenljivko, da prikažete sporočilo str = str & RS.Fields ("id") & "-" & RS.Fields ("name_mon") & vbnewline "pojdite na naslednji zapis RS.MoveNext Wend" Prikaži sporočilo msgbox str End Sub

Tukaj že uporabljamo zanke VBA Access za ponavljanje vseh vrednosti v našem naboru zapisov.

Toda pogosto je treba iz nabora zapisov dobiti ne vseh vrednosti, ampak samo eno, na primer ime meseca po njegovi kodi. In za to je uporaba zanke nekako draga, zato lahko preprosto napišemo poizvedbo, ki bo vrnila samo eno vrednost in se sklicala nanjo, na primer, ime meseca bomo dobili s kodo 5

Private Sub start_Click () "Razglasi spremenljivke" Za niz zapisov iz baze podatkov Dim RS As ADODB.Recordset "Niz poizvedbe Dim sql_query As String" Niz za prikaz skupne vrednosti Dim str As String "Ustvari nov objekt za niz zapisov RS = Nov ADODB.Recordset "String poizvedbe sql_query =" SELECT name_mon FROM test_table WHERE id = 5 "" Izvedite poizvedbo z uporabo trenutnih nastavitev povezave projekta RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic = RS. Pridobite našo vrednost Polja (0) msgbox str Konec pod

Za univerzalnost se tukaj že obrnemo ne po imenu celice, temveč po njenem indeksu, t.j. 0 in to je prva vrednost v Nabor zapisov, na koncu smo dobili vrednost "maj".

Kot lahko vidite, je vse precej preprosto. Če morate pogosto pridobiti določeno vrednost iz osnove ( kot v zadnjem primeru), potem priporočam, da vso kodo izpišete v ločeno funkcijo (Kako napisati funkcijo v VBA Access 2003) z enim vhodnim parametrom, na primer kodo meseca ( če upoštevamo naš primer) in preprosto, kjer je treba izpisati to vrednost, pokličemo funkcijo, ki jo potrebujemo, z zahtevanim parametrom in to je to, to bo znatno zmanjšalo kodo VBA in izboljšalo zaznavanje našega programa.

To je vse za danes. Vso srečo!

Primere poizvedb SQL lahko uporabite za učenje in vadbo pisanja poizvedb SQL v MS Access.

Eno poizvedbo SQL je mogoče ugnezditi v drugo. Podpoizvedba ni nič drugega kot poizvedba znotraj poizvedbe. Običajno se podpoizvedba uporablja v stavku WHERE. Obstajajo pa tudi drugi načini uporabe podpoizvedb.

Zahteva Q011. Prikazane so informacije o izdelkih iz tabele m_product, katerih kode so tudi v tabeli m_income:

IZBERI *
IZ m_product
WHERE id IN (IZberite ID izdelka IZ m_income);

Zahteva Q012. Prikaže se seznam izdelkov iz tabele m_product, katerih kod ni v tabeli m_outcome:

IZBERI *
IZ m_product
WHERE id NOT IN (IZBERITE product_id FROM m_outcome);

Zahteva Q013. Ta poizvedba SQL prikaže edinstven seznam kod in imen izdelkov, ki imajo kode v tabeli m_income, vendar ne v tabeli m_outcome:

IZBERI DISTINCT ID_izdelka, naslov
IZ m_income INNER JOIN m_product
ON m_income.product_id = m_product.id
WHERE product_id NOT IN (IZBERITE product_id FROM m_outcome);

Zahteva Q014. Iz tabele m_category se prikaže edinstven seznam kategorij, katerih imena se začnejo s črko M:

IZBERI DISTINCT naslov
IZ m_product
KJE naslov LIKE "М *";

Zahteva Q015. Primer izvajanja aritmetičnih operacij nad polji v zahtevi in ​​preimenovanja polj v zahtevi (vzdevek). Ta primer izračuna znesek odhodka = količina * cena in stopnja dobička za vsak zapis o stroških artikla, ob predpostavki, da je dobiček 7 odstotkov zneska prodaje:

znesek * cena / 100 * 7 AS dobiček
IZ m_outcome;

Zahteva Q016. Z analizo in poenostavitvijo aritmetičnih operacij lahko povečate hitrost izvajanja poizvedbe:

IZBERI dt, product_id, znesek, cena, znesek * cena AS outcome_sum,
outcome_sum * 0,07 AS dobiček
IZ m_outcome;

Zahteva Q017. Z uporabo stavka INNER JOIN lahko združite podatke iz več tabel. V naslednjem primeru se glede na vrednost ctgry_id vsak zapis tabele m_income ujema z imenom kategorije iz tabele m_category, ki ji izdelek pripada:

IZBERI c.naslov, b.naslov, dt, znesek, cena, znesek * cena KOT dohodek_vsota
FROM (m_income AS A INNER JOIN m_product AS b ON a.product_id = b.id)
INNER JOIN m_category AS c ON b.ctgry_id = c.id
VRSTI PO c.naslovu, b.naslovu;

Zahteva Q018. Funkcije, kot so SUM – vsota, COUNT – količina, AVG – aritmetična sredina, MAX – največja vrednost, MIN – najmanjša vrednost, se imenujejo agregatne funkcije. Prevzamejo več vrednosti in po obdelavi vrnejo eno samo vrednost. Primer izračuna vsote zmnožka uporabljenih polj zneska in cene agregatna funkcija SUM.

Laboratorijsko delo št. 1

SQL: IZVLEČEK PODATKOV - ukazIZBERI

Cilj:

• seznaniti se z stavki SQL;
• naučite se ustvariti najpreprostejše poizvedbe SQL v Accessu z ukazom SELECT;

· Uporaba operatorjev IN, BETWEEN, LIKE, JE NULL.

Vaja№1. Ustvarite poizvedbo, da v načinu SQL izberete vse vrednosti polj IME in PRIIMEK iz tabele STUDENTS.

IZBERI IME, PRIIMEK

OD ŠTUDENTOV;

Vaja№2 ... Ustvarite izbirno poizvedbo v načinu SQL za vse stolpce tabele STUDENTS.

IZBERI *

OD ŠTUDENTOV;

Naloga številka 3. Ustvarite poizvedbo, da v načinu SQL izberete imena mest, kjer študenti živijo, informacije o katerih so v tabeli OSEBNI PODATKI.

IZBERITE DISINCT CITY

IZ [OSEBNIH PODATKOV];

Naloga številka 4. Ustvarite poizvedbo za izbiro načina SQL, ki izbere imena vseh študentov s priimkom Ivanov, navedenimi v tabeli STUDENTS.

IZBERI PRIIMEK, IME

OD ŠTUDENTOV

KJE PRIIMEK = "Ivanov";

Naloga številka 5... Ustvarite poizvedbo za izbor v načinu SQL za pridobitev imen in priimkov študentov, vpisanih v skupino UIT-22 na proračunski obliki izobraževanja.

IZBERI PRIIMEK, IME

OD ŠTUDENTOV

KJE SKUPINA = "WHIT-22" IN PRORAČUN = res;

Naloga številka 6... Ustvarite poizvedbo v načinu SQL. na vzorcu iz tabele IZPITI IZPIT podatek o študentih s samo 4. in 5. oceno.

IZBERI *

IZ [SPREMEMBAIZPITI]

KJEOCENAIN (4,5);

Naloga številka 7. Ustvarite način zanpoc in SQL za izbor informacij o študentih, ki imajo izpitno oceno 3 pri predmetu IOSU.

IZBERI *

IZ [SPREMEMBAIZPITI]

KJESTVAR=" IOSU"InOCENANe v (4,5);

Naloga številka 8. Ustvarite poizvedbo v načinu SQL, da izberete zapise za subjekte, katerih ure so med 100 in 130.

IZBERI *

IZPREDMETI

KJEURAMED 100 IN 130;

Naloga številka 9. Ustvarite poizvedbo v načinu SQL, da iz tabele STUDENTS izberete informacije o študentih, katerih priimki se začnejo na primer s črko "C".

IZBERI *

IZŠTUDENTI

KJEPRIIMEKLIKE "Z*";

zaključek: Med laboratorijskim delom smo se seznanili s stavki SQL, se naučili izdelati najpreprostejše SQL poizvedbe v Accessu z ukazom SELECT z uporabo stavkov IN, BETWEEN, LIKE.

SQL - Lekcija 4. Pridobivanje podatkov - stavek SELECT

IZBERI, kaj_ izbrati IZ, kje izbrati;

Najprej si oglejmo vse stolpce iz tabele uporabnikov:

IZBERI * IZ uporabnikov;

To so vsi naši podatki, ki smo jih vnesli v to tabelo. Toda recimo, da si želimo ogledati samo stolpec id_user (na primer, v zadnji lekciji smo morali vedeti, kaj je id_user v tabeli uporabnikov, da zapolnimo tabelo s temami). Če želite to narediti, bomo v zahtevi podali ime tega stolpca:

IZBERI id_user IZ uporabnikov;

No, če želimo videti na primer imena in e-poštne naslove naših uporabnikov, potem bomo stolpce zanimanja navedli, ločene z vejicami:

IZBERITE ime, e-pošto OD uporabnikov;

Podobno lahko vidite, katere podatke vsebujejo naše druge tabele. Poglejmo, katere teme imamo:

IZBERI * IZ tem;

Zdaj imamo samo 4 teme, kaj pa če jih je 100? Rad bi, da so prikazani na primer po abecedi. Za to obstaja ključna beseda v SQL NAROČI PO po katerem ime stolpca, po katerem bo potekalo razvrščanje. Sintaksa je naslednja:

SELECT ime_stolpca FROM ime_tabele ORDER BY ime_stolpca;

Privzeto je razvrščanje v naraščajočem vrstnem redu, vendar je to mogoče spremeniti z dodajanjem ključne besede DESC

Zdaj so naši podatki razvrščeni v padajočem vrstnem redu.

Razvrščanje se lahko izvede po več stolpcih hkrati. Na primer, naslednja poizvedba bo razvrstila podatke po stolpcu topic_name, in če ta stolpec vsebuje več enakih vrstic, bo stolpec id_author razvrščen v padajočem vrstnem redu:

Primerjajte rezultat z rezultatom prejšnje poizvedbe.

Zelo pogosto ne potrebujemo vseh podatkov iz tabele. Na primer, želimo vedeti, katere teme je ustvaril uporabnik sveta (id = 4). Za to obstaja ključna beseda v SQL KJE, je sintaksa za takšno zahtevo naslednja:

Za naš primer je pogoj ID uporabnika, tj. potrebujemo samo vrstice s 4 v stolpcu id_author (ID uporabnika sveta):

Ali pa želimo vedeti, kdo je ustvaril temo koles:

Seveda bi bilo bolj priročno prikazati ime avtorja namesto id avtorja, vendar so imena shranjena v drugi tabeli. V naslednjih lekcijah se bomo naučili, kako izbrati podatke iz več tabel. Za zdaj poglejmo, katere pogoje je mogoče nastaviti s ključno besedo WHERE.

LIKE Operator metaznaki

Najpogostejši metaznak je % ... Pomeni kakršne koli simbole. Na primer, če moramo najti besede, ki se začnejo s črkami "led", potem pišemo LIKE "led%", in če želimo najti besede, ki vsebujejo znake "club", potem pišemo LIKE "% club%" . Na primer:

Drug pogosto uporabljen metaznak je _ ... Za razliko od %, ki označuje več znakov ali nič, podčrtaji označujejo natanko en znak. Na primer:

Bodite pozorni na presledek med metaznakom in "ribo", če ga preskočite, zahteva ne bo delovala, ker metaznak _ pomeni točno en znak, presledek pa je tudi znak.

Za danes je dovolj. V naslednji lekciji se bomo naučili poizvedovati po dveh ali več tabelah. Do takrat pa poskusite sami poizvedovati po tabeli objav.

Eno poizvedbo SQL je mogoče ugnezditi v drugo. Podpoizvedba ni nič drugega kot poizvedba znotraj poizvedbe. Običajno se podpoizvedba uporablja v stavku WHERE. Obstajajo pa tudi drugi načini uporabe podpoizvedb.

Zahteva Q011. Prikazane so informacije o izdelkih iz tabele m_product, katerih kode so tudi v tabeli m_income:

IZBERI *
IZ m_product
WHERE id IN (IZberite ID izdelka IZ m_income);

Zahteva Q012. Prikaže se seznam izdelkov iz tabele m_product, katerih kod ni v tabeli m_outcome:

IZBERI *
IZ m_product
WHERE id NOT IN (IZBERITE product_id FROM m_outcome);

Zahteva Q013. Ta poizvedba SQL prikaže edinstven seznam kod in imen izdelkov, ki imajo kode v tabeli m_income, vendar ne v tabeli m_outcome:

IZBERI DISTINCT ID_izdelka, naslov
IZ m_income INNER JOIN m_product
ON m_income.product_id = m_product.id
WHERE product_id NOT IN (IZBERITE product_id FROM m_outcome);

Zahteva Q014. Iz tabele m_category se prikaže edinstven seznam kategorij, katerih imena se začnejo s črko M:

IZBERI DISTINCT naslov
IZ m_product
KJE naslov LIKE "М *";

Zahteva Q015. Primer izvajanja aritmetičnih operacij nad polji v zahtevi in ​​preimenovanja polj v zahtevi (vzdevek). Ta primer izračuna znesek odhodka = količina * cena in stopnja dobička za vsak zapis o stroških artikla, ob predpostavki, da je dobiček 7 odstotkov zneska prodaje:

Cena, znesek * cena AS outcome_sum,
znesek * cena / 100 * 7 AS dobiček
IZ m_outcome;

Zahteva Q016. Z analizo in poenostavitvijo aritmetičnih operacij lahko povečate hitrost izvajanja poizvedbe:

outcome_sum * 0,07 AS dobiček
IZ m_outcome;

Zahteva Q017. Z uporabo stavka INNER JOIN lahko združite podatke iz več tabel. V naslednjem primeru se glede na vrednost ctgry_id vsak zapis tabele m_income ujema z imenom kategorije iz tabele m_category, ki ji izdelek pripada:

IZBERI c.naslov, b.naslov, dt, znesek, cena, znesek * cena KOT dohodek_vsota
FROM (m_income AS A INNER JOIN m_product AS b ON a.product_id = b.id)
INNER JOIN m_category AS c ON b.ctgry_id = c.id
VRSTI PO c.naslovu, b.naslovu;

Zahteva Q018. Funkcije, kot so SUM – vsota, COUNT – količina, AVG – aritmetična sredina, MAX – največja vrednost, MIN – najmanjša vrednost, se imenujejo agregatne funkcije. Prevzamejo več vrednosti in po obdelavi vrnejo eno samo vrednost. Primer izračuna vsote produkta polj zneska in cene z uporabo agregatne funkcije SUM:

SELECT SUM (znesek * cena) AS Total_Sum
IZ m_dohodka;

Zahteva Q019. Primer uporabe več agregatnih funkcij:

IZBERI vsota (znesek) AS Znesek_Sum, AVG (znesek) AS Znesek_AVG,
MAX (količina) AS Znesek_Max, Min (količina) AS Znesek_Min,
Število (*) AS skupno_število
IZ m_dohodka;

Zahtevaj Q020. Ta primer izračuna vsoto vseh postavk s kodo 1, napisano z velikimi črkami junija 2011:

IZBERI
IZ m_dohodka
KJE product_id = 1 IN dt MED # 6/1/2011 # IN # 6/30/2011 # ;.

Zahteva Q021. Naslednja poizvedba SQL izračuna količino prodanih izdelkov s kodo 4 ali 6:

IZBERI
IZ m_outcome
WHERE product_id = 4 OR product_id = 6;

Zahteva Q022. Izračuna se za kolikšen znesek je bilo prodano 12. junija 2011 blaga s kodo 4 ali 6:

IZBERI Vsota (znesek * cena) AS outcome_sum
IZ m_outcome
KJE (id_izdelka = 4 ALI ID_izdelka = 6) IN dt = # 12. 6. 2011 #;

Zahteva Q023. Naloga je naslednja. Izračunajte skupno količino blaga v kategoriji »Pekarna«, ki je bilo kapitalizirano.

Če želite rešiti to težavo, morate delovati s tremi tabelami: m_income, m_product in m_category, ker:
- količina in cena kapitaliziranega blaga sta shranjeni v tabeli m_dohodkov;
- koda kategorije vsakega izdelka je shranjena v tabeli m_product;
- ime naslova kategorije je shranjeno v tabeli m_category.

Za rešitev tega problema bomo uporabili naslednji algoritem:
- določitev kode kategorije "Pekovski izdelki" iz tabele m_category s podpoizvedbo;
- združitev tabel m_income in m_product za določitev kategorije vsakega kapitaliziranega produkta;
- izračun zneska prevzema (= količina * cena) za blago, katerega oznaka kategorije je enaka kodi, določeni z zgornjo podpoizvedbo.

IZ m_product AS A INNER JOIN m_income AS b ON a.id = b.product_id
KJE ctgry_id = (IZBERITE ID IZ m_category WHERE title = "(! LANG: Pekarna"); !}

Zahteva Q024. Problem izračuna skupne količine kapitaliziranega blaga v kategoriji "Pekovski izdelki" bomo rešili z naslednjim algoritmom:
- za vsak zapis tabele m_income, odvisno od vrednosti njegovega product_id, iz tabele m_category, se ujemajo z imenom kategorije;
- izberite zapise, za katere je kategorija enaka »Pekovski izdelki«;
- izračunajte znesek računa = količina * cena.

IZBERI Vsota (znesek * cena) AS dohodek_vsota
FROM (m_product AS A INNER JOIN m_income AS b ON a.id = b.product_id)
KJE c.title = "(! LANG: Pekarna"; !}

Zahteva Q025. Ta primer izračuna, koliko predmetov je bilo porabljenih:

SELECT COUNT (product_id) AS product_cnt
FROM (IZBERITE DISTINCT product_id FROM m_outcome) AS t;

Zahteva Q026. Klavzula GROUP BY se uporablja za združevanje zapisov. Običajno so zapisi združeni po vrednosti enega ali več polj, za vsako skupino pa se uporabi neka združena operacija. Na primer, naslednja poizvedba ustvari poročilo o prodaji blaga. To pomeni, da se ustvari tabela, ki vsebuje imena blaga in znesek, za katerega je bilo prodano:

IZBERI naslov, SUM (znesek * cena) AS outcome_sum
IZ m_izdelka KOT NOTRANJE PRIKLJUČEK m_outcome AS b
ON a.id = b.product_id
GROUP BY naslov;

Zahteva Q027. Poročilo o prodaji po kategorijah. To pomeni, da se ustvari tabela, ki vsebuje imena kategorij izdelkov, skupni znesek, za katerega so bili izdelki teh kategorij prodani, in povprečni znesek prodaje. Funkcija ROUND se uporablja za zaokrožitev povprečne vrednosti na stotino (druga številka za decimalnim ločilom):

SELECT c.title, SUM (znesek * cena) AS outcome_sum,
ROUND (AVG (znesek * cena), 2) AS outcome_sum_avg
FROM (m_product AS A INNER JOIN m_outcome AS b ON a.id = b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id = c.id
GROUP BY c.naslov;

Zahteva Q028. Za vsak izdelek se izračuna skupno in povprečno število njegovih prejemov in prikaže informacije o blagu, katerega skupno število prejemov ni manjše od 500:

SELECT product_id, SUM (količina) AS znesek_sum,
Okrogla (Povpr. (količina), 2) AS znesek_avg
IZ m_dohodka
GROUP BY product_id
IMITE Vsota (znesek)> = 500;

Zahteva Q029. Ta poizvedba izračuna za vsako postavko vsoto in povprečje njenih prejemkov v drugem četrtletju 2011. Če skupna količina prejetega blaga ni manjša od 1000, se prikažejo informacije o tem izdelku:

IZBERI naslov, SUM (znesek * cena) KOT vsota_dohodka
IZ m_income a INNER JOIN m_product b ON a.product_id = b.id
KJE dt MED # 1. 4. 2011 # IN # 30. 6. 2011 #
GROUP BY naslov
OB VSOTO (znesek * cena)> = 1000;

Zahtevaj Q030. V nekaterih primerih morate preslikati vsak zapis določene tabele v vsak zapis druge tabele; kar se imenuje kartezijanski produkt. Tabela, ki izhaja iz takšne povezave, se imenuje Descartesova tabela. Na primer, če ima neka tabela A 100 zapisov in tabela B 15 zapisov, bo njihova Descartesova tabela sestavljena iz 100 * 15 = 150 zapisov. Naslednja poizvedba združuje vsak zapis v tabeli m_income z vsakim zapisom v tabeli m_outcome:

SELECT * FROM m_income, m_outcome;

Zahteva Q031. Primer razvrščanja zapisov po dveh poljih. Naslednja poizvedba SQL izračuna količino in količino blaga, prejetega od vsakega dobavitelja za vsakega dobavitelja:

SUM (znesek * cena) AS dohodek_vsota

Zahteva Q032. Primer razvrščanja zapisov po dveh poljih. Naslednja poizvedba izračuna za vsakega dobavitelja količino in količino njegovih izdelkov, ki jih prodamo:

SELECT Supplier_id, product_id, SUM (količina) AS znesek_sum,
SUM (znesek * cena) AS outcome_sum
GROUP BY Supplier_id, product_id;

Zahteva Q033. V tem primeru sta dve zgornji poizvedbi (q031 in q032) uporabljeni kot podpoizvedbe. Rezultati teh poizvedb z metodo LEFT JOIN so združeni v eno poročilo. Naslednja poizvedba poroča o količini in količini prejetih in prodanih izdelkov za vsakega dobavitelja. Upoštevajte, da če je nek izdelek že prispel, vendar še ni prodan, bo celica outcome_sum za ta zapis prazna. Opozoriti je treba tudi, da je ta zahteva le primer uporabe glede zapletene poizvedbe kot podpoizvedba. Izvedba te zahteve SQL z veliko količino podatkov je vprašljiv:

IZBERI *
IZ
SUM (znesek * cena) AS dohodek_vsota
ON a.product_id = b.id GROUP BY supplier_id, product_id) AS a
LEVO PRIDRUŽI
(IZBERITE Supplier_id, product_id, SUM (količina) AS znesek_sum,
SUM (znesek * cena) AS outcome_sum
IZ m_outcome KOT NOTRANJI SPOJ m_product AS b
ON a.product_id = b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id = b.product_id) IN (a.supplier_id = b.supplier_id);

Zahteva Q034. V tem primeru sta dve zgornji poizvedbi (q031 in q032) uporabljeni kot podpoizvedbe. Rezultati teh poizvedb z metodo RIGTH JOIN so združeni v eno poročilo. Naslednja poizvedba prikaže poročilo o znesku plačil posamezne stranke za plačilne sisteme, ki jih je uporabljal, in znesku njihovih naložb. Naslednja poizvedba poroča o količini in količini prejetih in prodanih izdelkov za vsakega dobavitelja. Upoštevati je treba, da če je bil nek izdelek že prodan, vendar še ni prispel, bo celica vsota prihodkov za ta zapis prazna. Prisotnost takšnih praznih celic je pokazatelj napake v obračunu prodaje, saj je pred prodajo najprej treba prispeti ustrezen izdelek:

IZBERI *
IZ
(IZBERITE Supplier_id, product_id, SUM (količina) AS znesek_sum,
SUM (znesek * cena) AS dohodek_vsota
FROM m_income AS A INNER JOIN m_product AS b ON a.product_id = b.id
GROUP BY Supplier_id, product_id) AS a
PRAVI SE PRIKLJUČITE
(IZBERITE Supplier_id, product_id, SUM (količina) AS znesek_sum,
SUM (znesek * cena) AS outcome_sum
IZ m_outcome KOT NOTRANJE PRIKLJUČEK m_product AS b NA a.product_id = b.id
GROUP BY Supplier_id, product_id) AS b
ON (a.supplier_id = b.supplier_id) IN (a.product_id = b.product_id);

Zahteva Q035. Prikaže poročilo o višini prihodkov in odhodkov po izdelkih. Za to se ustvari seznam izdelkov v skladu s tabelama m_income in m_outcome, nato pa se za vsak izdelek s tega seznama izračuna vsota njegovih prejemkov po tabeli m_income in vsota njegovih stroškov po tabeli m_outcome:

SELECT product_id, SUM (in_amount) AS dohodek_amount,
SUM (out_amount) AS znesek_izid
IZ
(IZBERITE ID_izdelka, znesek AS in_količina, 0 AS out_amount
IZ m_dohodka
UNION VSE
SELECT product_id, 0 AS in_amount, znesek AS out_amount
IZ m_izid) AS t
GROUP BY product_id;

Zahteva Q036. EXISTS vrne TRUE, če nabor, ki mu je posredovan, vsebuje elemente. Funkcija EXISTS vrne FALSE, če je niz, ki ji je bil posredovan, prazen, torej ne vsebuje elementov. Naslednja poizvedba prikaže kode izdelkov, ki so vsebovane v tabelah m_income in m_outcome:

IZBERI DISTINCT ID izdelka
IZ m_income AS a
KJE OBSTAJA (IZBERITE ID izdelka IZ m_outcome AS b

Zahteva Q037. Prikazane so kode izdelkov, ki so vsebovane tako v tabeli m_income kot v tabeli m_outcome:

IZBERI DISTINCT ID izdelka
IZ m_income AS a
WHERE product_id IN (IZBERITE product_id FROM m_outcome)

Zahteva Q038. Prikazane so kode izdelkov, ki so vsebovane kot v tabeli m_income, vendar niso v tabeli m_outcome:

IZBERI DISTINCT ID izdelka
IZ m_income AS a
KJE NE OBSTAJA (IZBERITE ID izdelka IZ m_outcome AS b
KJE b.product_id = a.product_id);

Zahteva Q039. Prikaže se seznam izdelkov z največjo prodajo. Algoritem je naslednji. Za vsak izdelek se izračuna znesek njegove prodaje. Nato se določi najvišji od teh zneskov. Nato se za vsak izdelek ponovno izračuna skupna prodaja ter se prikažeta koda in skupna prodaja izdelkov, katerih seštevek je enak največjemu:

SELECT product_id, SUM (količina * cena) AS znesek_sum
IZ m_outcome
GROUP BY product_id
HAVING SUM (znesek * cena) = (IZBERITE MAX (s_amount)
FROM (IZBERITE VSOTE (znesek * cena) AS s_amount FROM m_outcome GROUP BY product_id));

Zahtevaj Q040. Rezervirana beseda IIF ( pogojni operater) se uporablja za oceno boolov izraz in izvajanje enega ali drugega dejanja, odvisno od rezultata (TRU ali NE). V naslednjem primeru se dostava artikla šteje za "majhno", če je količina manjša od 500. V nasprotnem primeru, to je, da je količina na prejemu večja ali enaka 500, se dostava šteje za "veliko":

IZBERI dt, product_id, znesek,
IIF (znesek<500,"малая","большая") AS mark
IZ m_dohodka;

Poizvedba SQL Q041. V primeru, ko se operater IIF uporablja večkrat, ga je bolj priročno zamenjati z operaterjem SWITCH. Operator SWITCH (operater več izbir) se uporablja za vrednotenje logičnega izraza in izvedbo dejanja na podlagi rezultata. V naslednjem primeru se dostavljena serija šteje za "majhno", če je količina blaga v seriji manjša od 500. V nasprotnem primeru, če je količina blaga večja ali enaka 500, se serija šteje za "veliko ":

IZBERI dt, product_id, znesek,
STIKALO (količina<500,"малая",amount>= 500, "veliko") oznaka AS
IZ m_dohodka;

Zahteva Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

IZBERI dt, product_id, znesek,
IIF (znesek<300,"малая",
IIF (znesek<1000,"средняя","большая")) AS mark
IZ m_dohodka;

Poizvedba SQL Q043. V naslednjem zahtevku, če je količina blaga v prejeti seriji manjša od 300, se serija šteje za "majhno". V nasprotnem primeru, če je znesek pogoj<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

IZBERI dt, product_id, znesek,
STIKALO (količina<300,"малая",
znesek<1000,"средняя",
znesek> = 1000, "veliko") oznaka AS
IZ m_dohodka;

Poizvedba SQL Q044. V naslednji poizvedbi je prodaja razdeljena v tri skupine: majhna (do 150), srednja (od 150 do 300), velika (300 ali več). Nato se za vsako skupino izračuna vsota:

IZBERITE kategorijo, SUM (outcome_sum) AS Ctgry_Total
FROM (IZBERITE znesek * cena AS outcome_sum,
IIf (znesek * cena<150,"малая",
IIf (znesek * cena<300,"средняя","большая")) AS Category
IZ m_izid) AS t
GROUP BY Kategorija;

Poizvedba SQL Q045. Funkcija DateAdd se uporablja za dodajanje dni, mesecev ali let določenemu datumu in pridobivanje novega datuma. Naslednja poizvedba:
1) datumu iz polja dt doda 30 dni in prikaže nov datum v polju dt_plus_30d;
2) doda 1 mesec datumu iz polja dt in prikaže nov datum v polju dt_plus_1m:

IZBERI dt, dateadd ("d", 30, dt) AS dt_plus_30d, dateadd ("m", 1, dt) AS dt_plus_1m
IZ m_dohodka;

Poizvedba SQL Q046. Funkcija DateDiff izračuna razliko med dvema datumoma v različnih enotah (dnevi, meseci ali leta). Naslednja poizvedba izračuna razliko med datumom v polju dt in trenutnim datumom v dnevih, mesecih in letih:

IZBERI dt, DateDiff ("d", dt, Datum ()) AS zadnji_dan,
DateDiff ("m", dt, Datum ()) AS zadnji_meseci,
DateDiff ("llll", dt, Datum ()) AS zadnja_leta
IZ m_dohodka;

Poizvedba SQL Q047.Število dni od datuma prejema blaga (tabela m_income) do tekočega datuma se izračuna s funkcijo DateDiff in se primerja datum poteka (tabela m_product):

DateDiff ("d", dt, Datum ()) AS zadnji_dnevi
IZ m_income KOT NOTRANJE PRIKLJUČEK m_product AS b
ON a.product_id = b.id;

Poizvedba SQL Q048. Izračuna se število dni od datuma prejema blaga do tekočega datuma, nato se preveri, ali to število presega rok uporabnosti:

IZBERITE a.id, product_id, dt, lifedays,
DateDiff ("d", dt, Datum ()) AS zadnji_dnevi, IIf (zadnji_dnevi> življenjski dnevi, "Da", "Ne") AS date_expire
IZ m_income a NOTRANJE PRIDRUŽITEV m_product b
ON a.product_id = b.id;

Poizvedba SQL Q049. Izračuna se število mesecev od dneva prejema blaga do tekočega datuma. Stolpec month_last1 izračuna absolutno število mesecev, stolpec month_last2 izračuna število polnih mesecev:

IZBERI dt, DateDiff ("m", dt, Datum ()) AS mesec_zadnji1,
DateDiff ("m", dt, datum ()) - iif (dan (dt)> dan (datum ()), 1,0) AS mesec_zadnji2
IZ m_dohodka;

Poizvedba SQL Q050. Prikaže se četrtletno poročilo o količini in količini usredstvenega blaga za leto 2011:

SELECT kvartal, SUM (outcome_sum) AS Skupaj
OD (IZBERITE znesek * cena AS outcome_sum, mesec (dt) AS m,
STIKALO (m<4,1,m<7,2,m<10,3,m>= 10,4) AS kvartal
IZ m_dohodka KJE leto (dt) = 2011) AS t
SKUPINA PO kvartalih;

Zahteva Q051. Naslednja poizvedba pomaga ugotoviti, ali so uporabniki lahko v sistem vnesli podatke o porabi artikla v količini, ki je večja od zneska prejema artikla:

SELECT product_id, SUM (in_sum) AS dohodek_sum, SUM (out_sum) AS outcome_sum
FROM (IZBERITE ID_izdelka, znesek * cena kot vsota_v, 0 kot vsota_izhoda
od m_dohodka
UNION VSE
IZBERI ID_izdelka, 0 kot vsota, znesek * cena kot vsota
iz m_outcome) AS t
GROUP BY product_id
OB VSOTE (v_vsoti)

Zahteva Q052. Oštevilčenje vrstic, ki jih vrne poizvedba, se izvaja na različne načine. Z uporabo samega MS Accessa lahko na primer preštevilčite vrstice poročila, pripravljenega v MS Access. Prav tako lahko preštevilčite z uporabo programskih jezikov, kot sta VBA ali PHP. Vendar je včasih to treba storiti v sami poizvedbi SQL. Torej bo naslednja poizvedba oštevilčila vrstice tabele m_income v naraščajočem vrstnem redu vrednosti polj ID:

IZBERITE ŠTEVILO (*) kot N, b.id, b.product_id, b.amount, b.price
IZ m_income a INNER JOIN m_income b NA a.id<= b.id
GROUP BY b.id, b.product_id, b.amount, b.price;

Zahteva Q053. Prvih pet med izdelki je prikazanih po količini prodaje. Prvih pet zapisov je prikazanih s stavkom TOP:

SELECT TOP 5, product_id, sum (količina * cena) AS vsota
IZ m_outcome
GROUP BY product_id
NAROČI PO vsoti (znesek * cena) DESC;

Zahteva Q054. Prikazanih je pet vodilnih med izdelki po količini prodaje, vrstice pa so oštevilčene:

SELECT COUNT (*) AS N, b.product_id, b.summa
IZ

IZ m_outcome GROUP BY product_id) AS a
NOTRANJI SPOJ
(IZBERITE ID_izdelka, vsota (znesek * cena) AS vsota,
summa * 10000000 + product_id AS id
IZ m_outcome GROUP BY product_id) AS b
ON a.id> = b.id
GROUP BY b.product_id, b.summa
ŠTEVO (*)<=5
VRSTI PO ŠTEVI (*);

Zahteva Q055. Naslednja poizvedba SQL prikazuje uporabo matematičnih funkcij COS, SIN, TAN, SQRT, ^ in ABS v MS Access SQL:

IZBERI (izberi število (*) iz m_income) kot N, 3,1415926 kot pi, k,
2 * pi * (k-1) / N kot x, COS (x) kot COS_, SIN (x) kot SIN_, TAN (x) kot TAN_,
SQR (x) kot SQRT_, x ^ 3 kot "x ^ 3", ABS (x) kot ABS_
OD (IZBERITE ŠTEVILO (*) AS k
FROM m_income AS A INNER JOIN m_income AS b ON a.id<=b.id
GROUP BY b.id) t;