Access DBMS koristi dvije vrste upita: QBE – upit po uzorku i SQL(Structured Query Language) - strukturirani upitni jezik. Uzorak zahtjeva generira se ispunjavanjem posebnog obrasca zahtjeva u prozoru "Query Builder". SQL – upite kreiraju programeri iz niza SQL-a – upute. SQL generiraju, u pravilu, programeri na obrascu upita, koji se otvara naredbom “Query Builder” na kartici “Creation” i odabirom “SQL Mode” iz izbornika View. SQL jezik je dizajniran za rad s podacima, tj. za kreiranje, modificiranje i upravljanje podacima u relacijskim bazama podataka.
Treba napomenuti da postoji nekoliko načina SQL upita (ANSI-89 SQL i ANSI-92 SQL upiti načina) koji su u skladu sa standardima ANSI-89 SQL i ANSI-92 SQL.
Upute sadrže opis skupa podataka u SQL-u. SQL izjave sastoje se od rečenica (SELECT, FROM, WHERE, itd.). Ponude u SQL jeziku sastoje se od pojmova (operatora ili naredbi, identifikatora, konstanti itd.). Naredba počinje naredbom (jednom od naredbi SELECT, CREATE, INSERT, UPDATE, DELETE itd.) i završava točkom i zarezom. Osnovne SQL naredbe: SELECT, FROM i WHERE.
Na primjer, SQL izjava:
SELECT Studenti.StudentCode
OD Studenti;
sastoji se od klauzule "SELECT Students.StudentCode" i klauzule "FROM Students".
SELECT klauzula sadrži operater SELECT i identifikator"Studenti. Kod studenta". Ovdje punom nazivu polja "StudentCode" prethodi naziv tablice "Students" baze podataka. SELECT - određuje polje koje sadrži potrebne podatke. Klauzula FROM sastoji se od klauzule FROM i identifikatora "Studenti". FROM - definira tablicu koja sadrži polja navedena u klauzuli SELECT.
Treba napomenuti da prilikom izrade upita u SQL-u morate uzeti u obzir njegovu sintaksu. Unatoč činjenici da se sintaksa SQL jezika temelji na sintaksi engleskog jezika, sintaksa verzija SQL jezika može se razlikovati za različite DBMS-ove.
Postoji nekoliko vrsta upita: odabir, ažuriranje, dodavanje i brisanje zapisa, unakrsni upit, kreiranje i brisanje tablica, spajanje tablica itd. Najčešći je zahtjev za uzorkom. Upiti za odabir koriste se za odabir informacija sadržanih u tablicama koje su korisniku potrebne. Izrađuju se samo za povezane tablice.
Za pregled SQL upita u Access 2003 ili 2007 DBMS-u, trebate izvršiti naredbu SQL View/Mode u aktivnom prozoru za dizajniranje uzorka upita (slika 1).
Riža. 1.
Uzmimo SQL naredbu (SELECT) za uzorkovanje podataka iz baze podataka Access 2003 prema kriteriju uspješnosti učenika "Ocjena=5" (Sl. 2).
Riža. 2.
Kao što slijedi iz naredbe SELECT (slika 1), ona opisuje skup podataka u SQL jeziku: SELECT - definira nazive polja kojima prethode nazivi tablica koje sadrže podatke; FROM - definira tablice i njihove odnose kroz ključna polja tablica (za to se koristi konstrukcija INNER JOIN ... ON), na temelju kojih se biraju podaci; WHREME - definira uvjete za odabir polja; ORDER BY - određuje kako sortirati uzlaznim redoslijedom (prema zadanim postavkama izvodi se uzlazni redoslijed) vrijednosti polja "Prezime" tablice "Studenti".
Kako proizlazi iz uputa za dohvaćanje podataka iz baze, SQL jezik opisuje što je potrebno dobiti iz baze, dok je izvršenje povjereno DBMS-u, budući da SQL jezik nema vlastita sredstva za kontrolu izvršavanja programa.
Prethodni članci raspravljali su o tim problemima. Razmatra se tehnologija za kreiranje strukture tablica baze podataka “sql_training_st.mdb” na temelju SQL upita. Osim toga, pomoću SQL upita popunjene su ACCESS DBMS tablice "sql_training_st.mdb".
Poznato je da je u relacijskim bazama podataka jezik SQL dizajniran za manipuliranje podacima, definiranje strukture baze podataka i njezinih komponenti, kontrolu korisničkog pristupa bazi podataka te upravljanje transakcijama ili upravljanje promjenama u bazi podataka.
SQL jezik se sastoji od četiri grupe:
- jezik za manipulaciju podacima DML;
- DDL Data Definition Language;
- jezik za upravljanje podacima DCL;
- TCL jezik kontrole transakcija.
Grupa DML uključuje četiri glavne vrste SQL upita:
- INSERT - dizajniran za dodavanje jednog ili više zapisa na kraj tablice;
- AŽURIRANJE - dizajnirano za promjenu postojećih zapisa u stupcima tablice ili izmjenu podataka u tablici;
- DELETE - dizajniran za brisanje zapisa iz tablice;
- SELECT - dizajniran za odabir podataka iz tablica.
O prva tri tipa SQL upita (INSERT, UPDATE, DELETE), koji se odnose na korektivne upite prema bazi, govorilo se na stranici
U ovom ćemo članku pogledati upite za dohvaćanje podataka iz tablica baze podataka programa Access.
Da biste dohvatili informacije pohranjene u bazi podataka programa Access 2003 ili 2007, možete upotrijebiti upit SELECT za odabir podataka iz tablica.
Sastavimo sljedeći SQL upit (SQL naredbu) za uzorkovanje; da to učinimo, odaberite SQL način izvršavanjem naredbe View/SQL Mode. Unesite sljedeću SQL naredbu s tipkovnice:
IZABERI *
OD Studenti;
Ova se izjava sastoji od dvije klauzule "SELECT *" i "FROM Students". Prva klauzula sadrži naredbu SELECT i identifikator * ("identifikator *" znači popis svih stupaca tablice). Druga klauzula sadrži naredbu FROM i identifikator "Studenti".
FROM - Definira tablicu Studenti koja sadrži polja navedena u klauzuli SELECT. Treba imati na umu da izborni upit uvijek sadrži dvije naredbe: SELECT i FROM. Ovisno o uvjetima odabira, drugi operateri mogu biti prisutni u zahtjevu za odabir. Slika 1 prikazuje snimku zaslona zahtjeva za dohvaćanje podataka.
Riža. 1. SQL SELECT upit za odabir podataka
U ovom primjeru, uzorak podataka generiran je iz svih stupaca tablice Studenti.
Spremite zahtjev pod nazivom "Studenti-upit1". Kao rezultat izvršenja naredbe “Spremi”, objekt “Zahtjevi: Studenti-zahtjev1” pojavit će se u “Navigacijskom području”.
Nakon spremanja zahtjeva za odabir, morate ga izvršiti klikom na ikonu "Pokreni". Rezultati naredbe "Pokreni" prikazani su na sl. 2.
Riža. 2. Odabir podataka iz svih stupaca tablice Učenici
Laboratorijski rad br.1
SQL: DATA EXTRACT - naredbaIZABERI
Cilj rada:
- upoznati se sa SQL naredbama;
- naučiti kako izraditi jednostavne SQL upite u Accessu pomoću naredbe SELECT;
· upotreba operatora IN, BETWEEN, LIKE, JE NULL.
Vježbajte№1. Napravite upit za odabir u SQL modu svih vrijednosti polja IME i PREZIME iz tablice STUDENTI.
ODABERITE IME, PREZIME
OD STUDENATA;
Vježbajte№2 . Napravite upit za odabir u SQL načinu rada svih stupaca tablice STUDENTI.
IZABERI *
OD STUDENATA;
Zadatak br. 3. Napravite upit za odabir u SQL modu imena gradova u kojima studenti žive, a podaci o tome nalaze se u tablici OSOBNI PODACI.
ODABERITE DISTINKTNI GRAD
IZ [OSOBNI PODACI];
Zadatak br. 4. Napravite izborni upit u SQL načinu rada koji dohvaća imena svih studenata s prezimenom Ivanov, a podaci o kojima se nalaze u tablici STUDENTI.
ODABERITE PREZIME, IME
OD STUDENATA
WHERE PREZIME="Ivanov";
Zadatak br. 5. Napravite izborni upit u SQL načinu rada za dobivanje imena i prezimena studenata koji studiraju u skupini UIT-22 na proračunskom obliku obrazovanja.
ODABERITE PREZIME, IME
OD STUDENATA
WHERE GROUP="UIT-22" AND BUDGET=true;
Zadatak br. 6. Napravite upit u SQL modu. za uzorak iz ISPITNE tablice podaci o učenicima koji imaju samo 4 i 5 ocjene.
IZABERI *
OD [PROMIJENITIISPITI]
GDJERAZREDIN(4,5);
Zadatak br. 7. Napravite zanpoc i SQL mod za odabir informacija o studentima koji imaju ispitnu ocjenu 3 iz predmeta IOSU.
IZABERI *
OD [PROMIJENITIISPITI]
GDJEARTIKAL=" IOSU"IRAZREDNije u (4,5);
Zadatak br. 8. Napravite upit u SQL načinu rada za odabir zapisa za stavke čiji su sati između 100 i 130.
IZABERI *
IZSTAVKE
GDJEGLEDATIIZMEĐU 100 I 130;
Zadatak br. 9. Napravite upit u SQL modu kako biste iz tablice STUDENTI odabrali informacije o studentima čija prezimena počinju npr. slovom “C”.
IZABERI *
IZSTUDENTI
GDJEPREZIMEKAO"S*";
Zaključak: Tijekom laboratorijskog rada upoznali smo se sa SQL uputama, naučili kako kreirati jednostavne SQL upite u Accessu pomoću naredbe SELECT koristeći operatore IN, BETWEEN, LIKE.
Opis obrazovnog projekta "Trgovina"Dijagram veze tablice
Opis tablica
m_category - kategorije proizvoda
m_income - primitak robe
m_outcome - potrošnja dobara
m_product - imenik, opisi proizvoda
m_dobavljač - imenik; informacije o dobavljaču
m_jedinica - imenik; jedinice
Da biste praktično testirali primjere dane u ovom materijalu za obuku, morate imati sljedeći softver:
Microsoft Access 2003 ili noviji.
SQL upit u MS Accessu. Početak
Da biste vidjeli sadržaj tablice, dvaput kliknite na naziv tablice na lijevoj ploči:
Da biste se prebacili na način rada za uređivanje polja tablice, odaberite način rada Dizajn na gornjoj ploči:
Za prikaz rezultata SQL upita dvaput kliknite naziv upita u lijevom oknu:
Za prebacivanje u način uređivanja SQL upita odaberite SQL način na gornjoj ploči:
SQL upit. Primjeri u MS Accessu. ODABIR: 1-10
U SQL upitu, izjava SELECT koristi se za odabir iz tablica baze podataka.SQL upit Q001. Primjer SQL upita za dobivanje samo potrebnih polja u željenom nizu:
SELECT dt, product_id, iznos
OD m_prihoda;
SQL upit Q002. U ovom primjeru SQL upita, znak zvjezdice (*) koristi se za popis svih stupaca tablice m_product, drugim riječima, za dobivanje svih polja relacije m_product:
IZABERI *
OD m_proizvoda;
ZahtjevSQL Q003. Izjava DISTINCT koristi se za uklanjanje dvostrukih unosa i dobivanje višestrukih jedinstvenih unosa:
ODABERITE DISTINCT product_id
OD m_prihoda;
SQL upit Q004. Naredba ORDER BY koristi se za sortiranje (redoslijed) zapisa prema vrijednostima određenog polja. Naziv polja naveden je nakon naredbe ORDER BY:
IZABERI *
OD m_prihoda
REDAJ PO cijeni;
SQL upit Q005. Naredba ASC koristi se kao dopuna naredbi ORDER BY i služi za specificiranje uzlaznog sortiranja. Naredba DESC koristi se uz naredbu ORDER BY i koristi se za određivanje silaznog sortiranja. U slučaju kada nisu navedeni ni ASC ni DESC, pretpostavlja se prisutnost ASC (zadano):
IZABERI *
OD m_prihoda
ORDER BY dt DESC , cijena;
SQL upit Q006. Za odabir potrebnih zapisa iz tablice koriste se različiti logički izrazi koji izražavaju uvjet odabira. Boolean izraz pojavljuje se nakon naredbe WHERE. Primjer dohvaćanja svih zapisa iz tablice m_income za koje je vrijednost iznosa veća od 200:
IZABERI *
OD m_prihoda
WHERE iznos>200;
SQL upit Q007. Za izražavanje složenih uvjeta koriste se logički operatori AND (konjunkcija), OR (disjunkcija) i NOT (logička negacija). Primjer dobivanja iz tablice m_outcome svih zapisa za koje je vrijednost iznosa 20, a vrijednost cijene veća ili jednaka 10:
Cijena
IZ m_ishod
WHERE iznos=20 I cijena>=10;
SQL upit Q008. Za spajanje podataka iz dvije ili više tablica koristite upute INNER JOIN, LEFT JOIN, RIGHT JOIN. Sljedeći primjer dohvaća polja dt, product_id, iznos, cijenu iz tablice m_income i polje naslova iz tablice m_product. Zapis tablice m_income pridružuje se zapisu tablice m_product kada je vrijednost m_income.product_id jednaka vrijednosti m_product.id:
NA m_income.product_id=m_product.id;
SQL upit Q009. U ovom SQL upitu treba obratiti pozornost na dvije stvari: 1) tekst za pretraživanje nalazi se u jednostrukim navodnicima ("); 2) datum je u formatu #Mjesec/Dan/Godina#, što vrijedi za MS Access. U drugim slučajevima sustavima, format upisa datuma može biti drugačiji. Primjer prikaza podataka o primitku mlijeka 12. lipnja 2011. Obratite pozornost na format datuma #6/12/2011#:
ODABERITE dt, ID_proizvoda, naslov, iznos, cijena
FROM m_income INNER JOIN m_product
WHERE title="Mlijeko" And dt=#6/12/2011#; !}
SQL upit Q010. Instrukcija BETWEEN koristi se za provjeru pripada li vrijednost određenom rasponu. Primjer SQL upita koji prikazuje informacije o proizvodima primljenim između 1. lipnja i 30. lipnja 2011.:
IZABERI *
FROM m_income INNER JOIN m_product
NA m_income.product_id=m_product.id
WHERE dt IZMEĐU #6/1/2011# i #6/30/2011#;
SQL upit. Primjeri u MS Accessu. ODABERI: 11-20
Jedan SQL upit može biti ugniježđen unutar drugog. Podupit nije ništa više od upita unutar upita. Tipično, podupit se koristi u WHERE klauzuli. Ali postoje i drugi načini korištenja podupita.Upit Q011. Prikazuju se podaci o proizvodima iz tablice m_product čije se šifre također nalaze u tablici m_income:
IZABERI *
IZ m_proizvoda
WHERE id IN (SELECT product_id FROM m_income);
Zahtjev Q012. Prikazuje se popis proizvoda iz tablice m_product čiji se kodovi ne nalaze u tablici m_outcome:
IZABERI *
IZ m_proizvoda
WHERE id NOT IN (SELECT product_id FROM m_outcome);
Zahtjev Q013. Ovaj SQL upit prikazuje jedinstveni popis kodova i naziva proizvoda koji se nalaze u tablici m_income, ali ne i u tablici m_outcome:
SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
NA m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);
Upit Q014. Iz tablice m_category prikazuje se jedinstveni popis kategorija čija imena počinju slovom M:
ODABERITE DISTINCT naslov
IZ m_proizvoda
WHERE naslov KAO "M*";
Upit Q015. Primjer izvođenja aritmetičkih operacija nad poljima u upitu i preimenovanja polja u upitu (alias). Ovaj primjer izračunava trošak = količina*cijena i dobit za svaki unos troška stavke, uz pretpostavku da je dobit 7 posto prodaje:
iznos*cijena/100*7 AS profit
OD m_ishod;
Upit Q016. Analizom i pojednostavljenjem aritmetičkih operacija možete povećati brzinu izvršavanja upita:
ODABERITE dt, ID_proizvoda, iznos, cijena, iznos*cijena KAO ishod_zbroj,
outcome_sum*0,07 AS dobit
OD m_ishod;
Upit Q017. Možete koristiti naredbu INNER JOIN za spajanje podataka iz više tablica. U sljedećem primjeru, ovisno o vrijednosti ctgry_id, svaki unos u tablici m_income odgovara nazivu kategorije iz tablice m_category kojoj proizvod pripada:
SELECT c.title, b.title, dt, iznos, cijena, iznos*cijena KAO suma_prihoda
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
ORDER BY c.naslov, b.naslov;
Zahtjev Q018. Funkcije kao što su SUM - zbroj, COUNT - količina, AVG - aritmetički prosjek, MAX - maksimalna vrijednost, MIN - minimalna vrijednost nazivaju se agregatnim funkcijama. Oni prihvaćaju mnoge vrijednosti i nakon obrade vraćaju jednu vrijednost. Primjer izračuna zbroja umnoška polja iznosa i cijene pomoću agregatne funkcije SUM:
SELECT SUM(iznos*cijena) AS Total_Sum
OD m_prihoda;
Upit Q019. Primjer korištenja nekoliko agregatnih funkcija:
SELECT Suma(iznos) AS Amount_Sum, ASOR(amount) AS Amount_AVG,
MAX(iznos) AS Amount_Max, Min(iznos) AS Amount_Min,
Broj(*) KAO Ukupan_Broj
OD m_prihoda;
Zahtjev Q020. U ovom primjeru izračunava se iznos svih dobara s šifrom 1, kapitaliziranih u lipnju 2011.:
SELECT Suma(iznos*cijena) KAO suma_prihoda
OD m_prihoda
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.
Upit Q021. Sljedeći SQL upit izračunava količinu prodaje artikala s kodom 4 ili 6:
ODABERITE Zbroj(iznos*cijena) kao zbroj_ishoda
IZ m_ishod
WHERE product_id=4 OR product_id=6;
Upit Q022. Izračunava se koliko je robe sa šifrom 4 ili 6 prodano na dan 12.06.2011.
SELECT Zbroj(iznos*cijena) KAO zbroj_ishoda
IZ m_ishod
WHERE (product_id=4 ILI product_id=6) AND dt=#6/12/2011#;
Upit Q023. Zadatak je ovo. Izračunajte ukupnu količinu robe u kategoriji “Pekarski proizvodi” koja je kapitalizirana.
Da biste riješili ovaj problem, morate raditi s tri tablice: m_income, m_product i m_category, jer:
- količina i cijena kapitaliziranih dobara pohranjene su u tablici m_income;
- šifra kategorije svakog proizvoda pohranjena je u tablici m_product;
- naziv kategorije naslova pohranjuje se u tablici m_category.
Za rješavanje ovog problema koristit ćemo sljedeći algoritam:
- određivanje šifre kategorije "Pekarski proizvodi" iz tablice m_category pomoću podupita;
- povezivanjem tablica m_income i m_product za određivanje kategorije svakog kupljenog proizvoda;
- izračun iznosa primitka (= količina*cijena) za robu čija je šifra kategorije jednaka šifri definiranoj gornjim podupitom.
IZABERI
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Pekarski proizvodi"); !}
Upit Q024. Problem izračuna ukupnog iznosa kapitaliziranih dobara u kategoriji “Pekarski proizvodi” riješit ćemo pomoću sljedećeg algoritma:
- za svaki unos u tablici m_income, ovisno o vrijednosti njegovog product_id-a, iz tablice m_category, odgovara nazivu kategorije;
- odabrati zapise za koje je kategorija “Pekarski proizvodi”;
- izračunati iznos primitka = količina * cijena.
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Pekarski proizvodi";
!}
Upit Q025. Ovaj primjer izračunava koliko je artikala robe potrošeno:
SELECT COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;
Upit Q026. Naredba GROUP BY koristi se za grupiranje zapisa. Tipično, zapisi su grupirani prema vrijednosti jednog ili više polja, a na svaku skupinu primjenjuje se neka skupna operacija. Na primjer, sljedeći upit generira izvješće o prodaji robe. Odnosno, generira se tablica koja sadrži nazive robe i iznos za koji je prodana:
SELECT title, SUM(iznos*cijena) AS ishod_zbroj
FROM m_product AS a INNER JOIN m_outcome AS b
NA a.id=b.product_id
GRUPIRAJ PO naslovu;
Zahtjev Q027. Izvješće o prodaji po kategorijama. Odnosno, generira se tablica koja sadrži nazive kategorija proizvoda, ukupni iznos za koji su prodani proizvodi tih kategorija te prosječni iznos prodaje. Funkcija ROUND koristi se za zaokruživanje prosječne vrijednosti na najbližu stotinku (druga znamenka nakon decimalnog razdjelnika):
SELECT c.title, SUM(iznos*cijena) AS outcome_sum,
ROUND(PROSJEC(iznos*cijena),2) AS ishod_zbroj_prosj
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.title;
Upit Q028. Ukupan i prosječan broj njegovih računa izračunava se za svaki proizvod i prikazuje podatke o proizvodima čiji je ukupan broj računa najmanje 500:
SELECT product_id, SUM(iznos) AS suma_iznosa,
Zaokruži(prosj.(iznos),2) KAO iznos_prosj
OD m_prihoda
GROUP BY product_id
HAVING Zbroj(iznos)>=500;
Upit Q029. Ovaj upit izračunava za svaki proizvod iznos i prosjek njegovih primitaka ostvarenih u drugom kvartalu 2011. Ako je ukupni iznos računa proizvoda najmanje 1000, tada se prikazuju informacije o ovom proizvodu:
SELECT title, SUM(iznos*cijena) AS suma_prihoda
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
GDJE dt IZMEĐU #4/1/2011# I #6/30/2011#
GRUPIRAJ PO naslovu
HAVING SUM(iznos*cijena)>=1000;
Upit Q030. U nekim slučajevima morate uskladiti svaki zapis neke tablice sa svakim zapisom druge tablice; koji se naziva kartezijevim produktom. Tablica koja proizlazi iz takve veze naziva se Descartesovom tablicom. Na primjer, ako neka tablica A ima 100 zapisa, a tablica B ima 15 zapisa, tada će se njihova Descartesova tablica sastojati od 100*15=150 zapisa. Sljedeći upit spaja svaki zapis u tablici m_income sa svakim zapisom u tablici m_outcome:
OD m_prihoda, m_ishoda;
Upit Q031. Primjer grupiranja zapisa po dva polja. Sljedeći SQL upit izračunava za svakog dobavljača iznos i količinu robe primljene od njega:
SUM(iznos*cijena) AS zbroj_prihoda
Upit Q032. Primjer grupiranja zapisa po dva polja. Sljedeći upit za svakog dobavljača izračunava količinu i količinu njihovih proizvoda koje mi prodajemo:
ODABERITE id_dobavljača, id_proizvoda, SUM(iznos) KAO iznos_zbroja,
GROUP BY dobavljač_id, product_id;
Upit Q033. U ovom primjeru, dva gornja upita (q031 i q032) koriste se kao podupiti. Rezultati ovih upita korištenjem metode LEFT JOIN kombiniraju se u jedno izvješće. Sljedeći upit prikazuje izvješće o količini i količini primljenih i prodanih proizvoda za svakog dobavljača. Imajte na umu da ako je neki proizvod već primljen, ali još nije prodan, tada će ćelija outcome_sum za ovaj unos biti prazna. da je ovaj upit samo primjer korištenja relativno složenih upita kao podupita. Izvedba ovog SQL upita s velikom količinom podataka je upitna:
IZABERI *
IZ
SUM(iznos*cijena) AS zbroj_prihoda
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LIJEVO PRIDRUŽI
(SELECT dobavljač_id, product_id, SUM(iznos) KAO iznos_sum,
SUM(iznos*cijena) AS zbroj_ishoda
FROM m_outcome AS a INNER JOIN 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) AND (a.supplier_id=b.supplier_id);
Upit Q034. U ovom primjeru, dva gornja upita (q031 i q032) koriste se kao podupiti. Rezultati ovih upita korištenjem metode RIGTH JOIN kombiniraju se u jedno izvješće. Sljedeći upit prikazuje izvješće o visini uplata svakog klijenta prema sustavima plaćanja koje je koristio i iznosu ulaganja koje je napravio. Sljedeći upit prikazuje izvješće o količini i količini primljenih i prodanih proizvoda za svakog dobavljača. Imajte na umu da ako je neki proizvod već prodan, ali još nije stigao, tada će ćelija prihod_zbroj za ovaj unos biti prazna. Prisutnost takvih praznih polja pokazatelj je greške u knjigovodstvu prodaje, jer je prije prodaje potrebno da odgovarajući proizvod stigne:
IZABERI *
IZ
(SELECT dobavljač_id, product_id, SUM(iznos) KAO iznos_sum,
SUM(iznos*cijena) AS zbroj_prihoda
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GRUPIRAJ PO dobavljač_id, product_id) KAO a
PRAVO SE PRIDRUŽI
(SELECT dobavljač_id, product_id, SUM(iznos) KAO iznos_sum,
SUM(iznos*cijena) AS zbroj_ishoda
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GRUPIRAJ PO id_dobavljača, id_proizvoda) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);
Upit Q035. Prikazuje se izvješće s prikazom iznosa prihoda i rashoda po proizvodima. Da biste to učinili, kreira se popis proizvoda prema tablicama m_income i m_outcome, zatim se za svaki proizvod s tog popisa izračunava zbroj njegovih prihoda prema tablici m_income, a iznos njegovih rashoda prema tablici m_outcome:
SELECT product_id, SUM(u_iznosu) KAO prihod_iznos,
SUM(out_amount) KAO ishod_iznos
IZ
(SELECT product_id, iznos AS in_amount, 0 AS out_amount
OD m_prihoda
UNIJA SVE
SELECT product_id, 0 AS in_amount, iznos AS out_amount
IZ m_ishod) KAO t
GROUP BY product_id;
Upit Q036. Funkcija EXISTS vraća TRUE ako skup koji joj je proslijeđen sadrži elemente. Funkcija EXISTS vraća FALSE ako je skup koji joj je proslijeđen prazan, odnosno ne sadrži elemente. Sljedeći upit prikazuje kodove proizvoda koji su sadržani u tablicama m_income i m_outcome:
ODABERITE DISTINCT product_id
OD m_dohodak KAO a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
Upit Q037. Prikazuju se šifre proizvoda koje se nalaze u tablicama m_income i m_outcome:
ODABERITE DISTINCT product_id
OD m_dohodak KAO a
WHERE product_id IN (SELECT product_id FROM m_outcome)
Upit Q038. Prikazuju se šifre proizvoda koje se nalaze u tablici m_income, ali nisu sadržane u tablici m_outcome:
ODABERITE DISTINCT product_id
OD m_dohodak KAO a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);
Upit Q039. Prikazuje se popis proizvoda s maksimalnim iznosom prodaje. Algoritam je sljedeći. Za svaki proizvod izračunava se iznos njegove prodaje. Zatim se određuje najveći od tih iznosa. Zatim se za svaki proizvod ponovno izračunava zbroj njegove prodaje te se ispisuje šifra i zbroj prodaje robe čiji je zbroj prodaje jednak maksimalnom:
SELECT product_id, SUM(iznos*cijena) KAO iznos_zbroj
IZ m_ishod
GROUP BY product_id
HAVING SUM(iznos*cijena) = (SELECT MAX(s_iznos)
FROM (SELECT SUM(iznos*cijena) AS s_iznos FROM m_outcome GROUP BY product_id));
Upit Q040. Rezervirana riječ IIF (uvjetni operator) koristi se za procjenu logičkog izraza i izvođenje radnje ovisno o rezultatu (TRUE ili FALSE). U sljedećem primjeru, isporuka artikla smatra se "malom" ako je količina manja od 500. U suprotnom, to jest, količina primitka je veća ili jednaka 500, isporuka se smatra "velikom":
SELECT dt, product_id, iznos,
IIF(iznos OD m_prihoda;
SQL upit Q041. U slučaju da se IIF operator koristi nekoliko puta, praktičnije ga je zamijeniti s SWITCH operatorom. Operator SWITCH (operator višestrukog odabira) koristi se za procjenu logičkog izraza i izvođenje radnje ovisno o rezultatu. U sljedećem primjeru, isporučena partija se smatra "malom" ako je količina robe u seriji manja od 500. U suprotnom, to jest, ako je količina robe veća ili jednaka 500, partija se smatra "velikom ":
SELECT dt, product_id, iznos,
SWITCH(iznos =500,"veliki") AS oznaka
OD m_prihoda;
Upit Q042. U sljedećem zahtjevu, ako je količina robe u primljenoj seriji manja od 300, tada se serija smatra "malom". U suprotnom, to jest, ako je uvjet iznos SELECT dt, product_id, iznos,
IIF(iznos IIF(iznos OD m_prihoda;
SQL upit Q043. U sljedećem zahtjevu, ako je količina robe u primljenoj seriji manja od 300, tada se serija smatra "malom". U suprotnom, to jest, ako je uvjet iznos SELECT dt, product_id, iznos,
SWITCH(iznos iznos iznos>=1000,"veliki") AS oznaka
OD m_prihoda;
SQL upit Q044. U sljedećem upitu prodaja je podijeljena u tri skupine: mala (do 150), srednja (od 150 do 300), velika (300 ili više). Zatim se izračunava ukupan iznos za svaku grupu:
SELECT Kategorija, SUM(zbroj_ishoda) AS Ctgry_Total
FROM (SELECT iznos*cijena KAO ishod_zbroj,
IIf(iznos*cijena IIf(iznos*cijena OD m_ishod) AS t
GRUPIRAJ PO kategoriji;
SQL upit Q045. Funkcija DateAdd koristi se za dodavanje dana, mjeseci ili godina određenom datumu i dobivanje novog datuma. Sljedeći zahtjev:
1) dodaje 30 dana datumu iz polja dt i prikazuje novi datum u polju dt_plus_30d;
2) dodaje 1 mjesec datumu iz polja dt i prikazuje novi datum u polju dt_plus_1m:
SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
OD m_prihoda;
SQL upit Q046. Funkcija DateDiff dizajnirana je za izračunavanje razlike između dva datuma u različitim jedinicama (dani, mjeseci ili godine). Sljedeći upit izračunava razliku između datuma u polju dt i trenutnog datuma u danima, mjesecima i godinama:
SELECT dt, DateDiff("d",dt,Date()) AS last_day,
DateDiff("m",dt,Date()) KAO zadnji_mjeseci,
DateDiff("yyyy",dt,Date()) KAO last_years
OD m_prihoda;
SQL upit Q047. Broj dana od datuma primitka robe (tablica m_income) do trenutnog datuma izračunava se pomoću funkcije DateDiff i uspoređuje se datum isteka (tablica m_product):
DateDiff("d",dt,Date()) KAO zadnji_dani
OD m_dohodak KAO UNUTARNJI SPOJ m_proizvod KAO b
NA a.product_id=b.id;
SQL upit Q048. Izračunava se broj dana od datuma prijema robe do tekućeg datuma, zatim se provjerava da li ta količina prelazi rok trajanja:
ODABERITE a.id, product_id, dt, lifedays,
DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
FROM m_prihod a INNER JOIN m_proizvod b
NA a.product_id=b.id;
SQL upit Q049. Izračunava se broj mjeseci od datuma prijema robe do tekućeg datuma. Stupac month_last1 izračunava apsolutni broj mjeseci, stupac month_last2 izračunava broj punih mjeseci:
SELECT dt, DateDiff("m",dt,Date()) AS month_last1,
DateDiff("m",dt,Date())-iif(dan(dt)>dan(datum()),1,0) AS month_last2
OD m_prihoda;
SQL upit Q050. Iskazuje se tromjesečno izvješće o količini i količini kupljene robe za 2011. godinu:
SELECT kvartal, SUM(outcome_sum) AS Ukupno
FROM (ODABERITE iznos*cijenu KAO ishod_zbroj, mjesec(dt) KAO m,
PREKIDAČ(m =10,4) AS kvartal
FROM m_dohodak WHERE godina(dt)=2011) AS t
GROUP BY kvartal;
Upit Q051. Sljedeći upit pomaže saznati jesu li korisnici mogli unijeti u sustav podatke o potrošnji robe u iznosu većem od količine primljene robe:
SELECT product_id, SUM(in_sum) KAO prihod_sum, SUM(out_sum) AS outcome_sum
FROM (SELECT product_id, iznos*cijena kao in_sum, 0 kao out_sum
od m_prihoda
UNIJA SVE
SELECT product_id, 0 kao in_sum, iznos*cijena kao out_sum
od m_ishod) KAO t
GROUP BY product_id
IMA SUM(u_zbroju)
Upit Q052. Numeriranje redaka koje vraća upit implementirano je na različite načine. Na primjer, možete prenumerirati retke izvješća pripremljenog u MS Accessu koristeći sam MS Access. Također možete promijeniti broj pomoću programskih jezika, na primjer, VBA ili PHP. Međutim, ponekad to treba učiniti u samom SQL upitu. Dakle, sljedeći će upit numerirati retke tablice m_income prema uzlaznom redoslijedu vrijednosti ID polja:
SELECT COUNT(*) kao N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;
Upit Q053. Prikazuje se prvih pet proizvoda među proizvodima prema količini prodaje. Prvih pet zapisa ispisuje se pomoću TOP upute:
SELECT TOP 5, product_id, sum(amount*price) AS summa
IZ m_ishod
GROUP BY product_id
ORDER BY suma(iznos*cijena) DESC;
Upit Q054. Prikazuje se prvih pet proizvoda među proizvodima prema iznosu prodaje, a reci su numerirani kao rezultat:
SELECT COUNT(*) AS N, b.product_id, b.summa
IZ
FROM m_outcome GROUP BY product_id) KAO a
UNUTARNJI SPOJ
(SELECT product_id, sum(amount*price) AS summa,
suma*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
NA a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)ORDER BY COUNT(*);
Upit Q055. Sljedeći SQL upit prikazuje korištenje matematičkih funkcija COS, SIN, TAN, SQRT, ^ i ABS u MS Access SQL-u:
SELECT (odaberite broj(*) iz m_income) kao N, 3,1415926 kao pi, k,
2*pi*(k-1)/N kao x, COS(x) kao COS_, SIN(x) kao SIN_, TAN(x) kao TAN_,
SQR(x) kao SQRT_, x^3 kao "x^3", ABS(x) kao ABS_
OD (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;
SQL upit. Primjeri u MS Accessu. AŽURIRANJE: 1-10
Zahtjev U001. Sljedeći SQL upit za promjenu povećava cijene robe s kodom 3 u tablici m_income za 10%:AŽURIRANJE m_income SET cijena = cijena*1.1
WHERE product_id=3;
Zahtjev U002. Sljedeći SQL upit za ažuriranje povećava količinu svih proizvoda u tablici m_income za 22 jedinice čiji nazivi počinju riječju "Ulje":
AŽURIRAJ m_income SET iznos = iznos+22
WHERE product_id IN (SELECT id FROM m_product WHERE naslov LIKE "Oil*");
Zahtjev U003. Sljedeći SQL upit za promjenu u tablici m_outcome smanjuje cijene svih proizvoda koje proizvodi Sladkoe LLC za 2 posto:
AŽURIRANJE m_outcome SET cijena = cijena*0,98
WHERE product_id IN
(SELECT a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="LLC"Сладкое"");. !}
Upiti u Accessu glavni su alat za odabir, ažuriranje i obradu podataka u tablicama baze podataka. Access, u skladu s konceptom relacijskih baza podataka, koristi SQL (Structured Query Language) za izvršavanje upita. Korištenjem SQL jezičnih uputa implementira se svaki upit u Accessu.
Glavna vrsta zahtjeva je zahtjev za odabirom. Rezultat ovog upita je nova tablica koja postoji dok se upit ne zatvori. Zapisi se formiraju kombiniranjem zapisa tablica na kojima se temelji upit. Metoda za kombiniranje zapisa tablice navedena je prilikom definiranja njihovog odnosa u podatkovnoj shemi ili prilikom kreiranja upita. Uvjeti odabira formulirani u upitu omogućuju vam filtriranje zapisa koji čine rezultat spajanja tablica.
U Accessu se može stvoriti nekoliko vrsta upita:
- zahtjev za uzorak- bira podatke iz jedne tablice ili upita ili više međusobno povezanih tablica i drugih upita. Rezultat je tablica koja postoji dok se upit ne zatvori. Zapisi tablice rezultata generiraju se u skladu sa zadanim uvjetima odabira i pri korištenju više tablica kombiniranjem njihovih zapisa;
- zahtjev za izradu tablice- odabire podatke iz međusobno povezanih tablica i drugih upita, ali, za razliku od upita odabira, pohranjuje rezultat u novu trajnu tablicu;
- zahtjevi za ažuriranje, dodavanje, brisanje- su zahtjevi za radnjom, uslijed kojih se mijenjaju podaci u tablicama.
Upiti u Accessu u modu dizajna sadrže podatkovnu shemu koja prikazuje korištene tablice i obrazac upita u kojem se dizajnira struktura tablice upita i uvjeti za odabir zapisa (Sl. 4.1).
Pomoću upita možete izvršiti sljedeće vrste obrade podataka:
- uključite korisnička odabrana polja tablice u tablicu upita;
- izvršiti izračune u svakom od primljenih zapisa;
- odabrati zapise koji zadovoljavaju uvjete odabira;
- stvoriti novu virtualnu tablicu na temelju kombiniranja zapisa međusobno povezanih tablica;
- grupni zapisi koji imaju iste vrijednosti u jednom ili više polja, istovremeno obavljaju statističke funkcije na drugim poljima grupe i, kao rezultat toga, uključuju jedan zapis za svaku grupu;
- stvoriti novu tablicu baze podataka koristeći podatke iz postojećih tablica;
- ažurirati polja u odabranom podskupu zapisa;
- izbrisati odabrani podskup zapisa iz tablice baze podataka;
- dodati odabrani podskup zapisa u drugu tablicu.
Upiti u Accessu služe kao izvori zapisa za druge upite, obrasce i izvješća. Pomoću upita možete prikupiti potpune podatke za generiranje dokumenta određenog predmetnog područja iz nekoliko tablica, a zatim ih koristiti za izradu obrasca - elektroničkog prikaza tog dokumenta. Ako obrazac ili izvješće kreira čarobnjak na temelju nekoliko međusobno povezanih tablica, tada se za njih automatski generira upit kao izvor zapisa.
Kako biste to potvrdili, pogledajte video vodič.