SQL - Lekcija 4. Dohvaćanje podataka - naredba SELECT
Dakle, u našoj forumskoj bazi podataka postoje tri tablice: korisnici (korisnici), teme (teme) i postovi (poruke). I želimo vidjeti koje podatke sadrže. Za to postoji operator u SQL-u IZABERI. Sintaksa za njegovu upotrebu je sljedeća:SELECT what_select FROM where_select;
Umjesto “what_select” moramo navesti ili naziv stupca čije vrijednosti želimo vidjeti, ili imena nekoliko stupaca odvojenih zarezima, ili simbol zvjezdice (*), što znači odabir svih stupaca tablice. . Umjesto "from_select" trebali biste navesti naziv tablice.
Pogledajmo prvo sve stupce iz tablice korisnika:
SELECT * FROM korisnika;
To su svi naši podaci koje smo unijeli u ovu tablicu. Ali pretpostavimo da želimo pogledati samo stupac id_user (na primjer, u prošloj lekciji, da bismo popunili tablicu tema, morali smo znati koji su id_users u tablici korisnici). Da bismo to učinili, navest ćemo naziv ovog stupca u zahtjevu:
SELECT id_user FROM korisnika;
Pa, ako želimo vidjeti, na primjer, imena i e-mailove naših korisnika, tada ćemo navesti stupce od interesa odvojene zarezima:
ODABERITE ime, e-poštu OD korisnika;
Slično, možete vidjeti koje podatke sadrže naše druge tablice. Da vidimo koje teme imamo:
SELECT * FROM tema;
Sada imamo samo 4 teme, ali što ako ih ima 100? Htio bih da se prikazuju, na primjer, abecednim redom. Za to postoji ključna riječ u SQL-u NARUČI PO nakon čega slijedi naziv stupca po kojem će se sortirati. Sintaksa je sljedeća:
SELECT column_name FROM table_name ORDER BY sort_column_name;
Prema zadanim postavkama sortiranje je uzlaznim redoslijedom, ali to se može promijeniti dodavanjem ključne riječi OPIS
Sada su naši podaci poredani silaznim redoslijedom.
Razvrstavanje se može izvršiti po nekoliko stupaca odjednom. Na primjer, sljedeći upit će sortirati podatke prema stupcu topic_name, a ako postoji više identičnih redaka u ovom stupcu, tada će stupac id_author biti poredan silaznim redoslijedom:
Usporedite rezultat s rezultatom prethodnog upita.
Vrlo često nam nisu potrebni svi podaci iz tablice. Na primjer, želimo saznati koje je teme kreirao korisnik sveta (id=4). Za to postoji ključna riječ u SQL-u GDJE, sintaksa za takav zahtjev je sljedeća:
Za naš primjer, uvjet je ID korisnika, tj. Trebaju nam samo oni redovi čiji stupac id_author sadrži 4 (korisnički ID sveta):
Ili želimo znati tko je stvorio temu "bicikli":
Naravno, bilo bi zgodnije da se umjesto ID-a autora prikazuje njegovo ime, ali imena su pohranjena u drugoj tablici. U narednim lekcijama naučit ćemo kako odabrati podatke iz više tablica. U međuvremenu, saznajmo koji se uvjeti mogu postaviti pomoću ključne riječi WHERE.
| Operater | Opis |
| = (jednako) | Odabrane su vrijednosti jednake navedenoj vrijednosti Primjer: SELECT * FROM topics WHERE id_author=4; Proizlaziti:
|
| > (više) | Odabrane su vrijednosti veće od navedenih Primjer: SELECT * FROM topics WHERE id_author>2; Proizlaziti:
|
| < (меньше) | Odabrane su vrijednosti manje od navedenih Primjer: SELECT * FROM topics WHERE id_author
|
| >= (veće ili jednako) | Odabrane su vrijednosti veće i jednake navedenoj vrijednosti Primjer: SELECT * FROM topics WHERE id_author>=2; Proizlaziti:
|
| <= (меньше или равно) | Odabrane su vrijednosti manje i jednake navedenoj vrijednosti Primjer: SELECT * FROM topics WHERE id_author
|
| != (nije jednako) | Odabrane su vrijednosti koje nisu jednake navedenoj vrijednosti Primjer: SELECT * FROM topics WHERE id_author!=1; Proizlaziti:
|
| NIJE NULL | Odabire retke koji imaju vrijednosti u navedenom polju Primjer: SELECT * FROM topics WHERE id_author IS NOT NULL; Proizlaziti:
|
| JE NIŠTAVAN | Odabire retke koji nemaju vrijednost u navedenom polju Primjer: SELECT * FROM topics WHERE id_author IS NULL; Proizlaziti:
|
| IZMEĐU (između) | Odabrane su vrijednosti između navedenih vrijednosti Primjer: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Proizlaziti:
|
| IN (sadržana vrijednost) | Odabrane su vrijednosti koje odgovaraju navedenim vrijednostima Primjer: SELECT * FROM topics WHERE id_author IN (1, 4); Proizlaziti:
|
| NIJE U (vrijednost nije sadržana) | Odabrane su vrijednosti koje nisu navedene Primjer: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Proizlaziti:
|
| LIKE (podudaranje) | Odabrane su vrijednosti koje odgovaraju uzorku Primjer: SELECT * FROM topics WHERE naziv_teme LIKE "led%"; Proizlaziti:
|
| NE Sviđa mi se (ne odgovara) | Odabrane su vrijednosti koje ne odgovaraju uzorku Primjer: SELECT * FROM topics WHERE topic_name NOT LIKE "led%"; Proizlaziti:
|
Prazan set - nema takvih linija.
O mogućim metakarakterima za LIKE operator bit će riječi u nastavku.
LIKE metakarakteri operatora
Pretrage pomoću metaznakova mogu se izvoditi samo u tekstualnim poljima.Najčešći metakarakter je % . To znači bilo koje simbole. Na primjer, ako želimo pronaći riječi koje počinju slovima "vel", tada ćemo napisati KAO "vel%", a ako želimo pronaći riječi koje sadrže znakove "klub", tada ćemo napisati KAO "% klub%". Na primjer:
Još jedan često korišten metakarakter je _ . Za razliku od %, koji označava malo ili nimalo znakova, podvlaka označava točno jedan znak. Na primjer:
Obratite pozornost na razmak između metakaraktera i "ribice"; ako ga preskočite, zahtjev neće funkcionirati, jer metakarakter _ označava točno jedan znak, a razmak je također znak.
Za danas je dosta. U sljedećoj lekciji naučit ćemo kako napisati upite prema dvije ili više tablica. U međuvremenu pokušajte postavljati vlastite upite prema tablici postova.
Ova lekcija je posvećena SQL upiti u bazu podataka na VBA pristup. Pogledat ćemo kako se postavljaju upiti INSERT, UPDATE, DELETE prema bazi podataka u VBA, a također ćemo naučiti kako dobiti određenu vrijednost iz SELECT upita.
Oni koji programiraju VBA pristup a tijekom rada s bazom podataka SQL poslužitelja vrlo često se susreću s tako jednostavnim i potrebnim zadatkom kao što je slanje SQL upita bazi podataka, bilo da se radi o INSERT, UPDATE ili jednostavnom SQL SELECT upitu. A budući da smo programeri početnici, trebali bismo i ovo moći, pa ćemo danas učiniti upravo to.
Već smo se dotakli teme dobivanja podataka sa SQL poslužitelja, gdje smo napisali kod u VBA za dobivanje tih podataka, na primjer, u članku o Prijenos podataka u tekstualnu datoteku iz MSSql 2008, ili smo se također dotakli toga malo u materijalu Prijenos podataka iz programa Access u Word i Excel predložak, ali na ovaj ili onaj način, pogledali smo ovo površno, a danas predlažem da o tome razgovaramo malo detaljnije.
Bilješka! Svi primjeri u nastavku razmatraju se korištenjem projekta Access 2003 ADP i baze podataka MSSql 2008. Ako ne znate što je ADP projekt, pogledali smo to u materijalu Kako stvoriti i konfigurirati Access ADP projekt.
Izvorni podaci za primjere
Recimo da imamo tablicu test_table koja će sadržavati brojeve i nazive mjeseci u godini (upiti se izvršavaju pomoću Management Studio)
STVARAJ TABLICU .( NOT NULL, (50) NULL) U KRENU
Kao što sam već rekao, koristit ćemo ADP projekt konfiguriran za rad s MS SQL 2008, u kojem sam napravio testni obrazac i dodao gumb za pokretanje s potpisom "Trčanje", koji će nam trebati za testiranje našeg koda, tj. Napisat ćemo sav kod u rukovatelju događajima " Pritisak gumba».
Upiti prema bazi INSERT, UPDATE, DELETE u VBA
Kako ne bismo dugo odgađali, počnimo odmah, recimo da trebamo dodati red u našu testnu tablicu ( kod komentirao)/
Private Sub start_Click() "Deklarirajte varijablu za pohranjivanje niza upita Dim sql_query As String "U nju upišite upit koji nam je potreban sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Izvrši it DoCmd RunSQL sql_query End Sub
U ovom slučaju, upit se izvršava pomoću trenutnih parametara veze s bazom podataka. Možemo provjeriti jesu li podaci dodani ili ne.
Kao što vidite, podaci su ubačeni.
Da bismo izbrisali jednu liniju, napišemo sljedeći kod.
Private Sub start_Click() "Deklarirajte varijablu za pohranjivanje niza upita Dim sql_query As String "Upišite upit za brisanje u nju sql_query = "DELETE test_table WHERE id = 6" "Pokrenite DoCmd.RunSQL sql_query End Sub
Ako provjerimo, vidjet ćemo da je željena linija izbrisana.
Da biste ažurirali podatke, napišite zahtjev za ažuriranje u varijablu sql_query, nadam se da je značenje jasno.
SELECT upit prema bazi podataka u VBA
Ovdje su stvari malo zanimljivije nego s drugim SQL konstrukcijama.
Prvo, recimo, trebamo dobiti sve podatke iz tablice, i npr. mi ćemo ih obraditi i prikazati u poruci, a vi ih naravno možete koristiti u druge svrhe, za to pišemo sljedeće kodirati
Private Sub start_Click() "Declare varijable "Za skup zapisa iz baze podataka Dim RS As ADODB.Recordset "Query string Dim sql_query As String "String za prikaz sažetih podataka u poruci Dim str As String "Kreiraj novi objekt za zapise set RS = New ADODB .Recordset "Redak upita sql_query = "SELECT id, name_mon FROM test_table" "Pokreni upit korištenjem trenutnih postavki veze projekta RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Prolazi kroz zapise Dok ne ( RS.EOF) "Ispunite varijablu za prikaz poruke str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "idite na sljedeći zapis RS.MoveNext Wend " Ispišite poruku msgbox str End Sub
Ovdje već koristimo VBA Access petlje za ponavljanje kroz sve vrijednosti u našem skupu zapisa.
Ali vrlo često je potrebno dobiti ne sve vrijednosti iz skupa zapisa, već samo jednu, na primjer, naziv mjeseca prema njegovom kodu. A da bismo to učinili, nekako je skupo koristiti petlju, tako da možemo jednostavno napisati upit koji će vratiti samo jednu vrijednost i pristupiti joj, na primjer, dobit ćemo ime mjeseca pomoću koda 5
Private Sub start_Click() "Deklaraj varijable" Za skup zapisa iz baze podataka Dim RS As ADODB.Recordset "String upita Dim sql_query As String "String za prikaz konačne vrijednosti Dim str As String "Kreiraj novi objekt za skup zapisa RS = Novi ADODB.Recordset "Redak upita sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Pokreni upit korištenjem trenutnih postavki veze projekta RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Dohvati našu vrijednost str = RS. Polja(0) msgbox str End Sub
Radi univerzalnosti, ovdje smo se već pozabavili ne imenom ćelije, već njenim indeksom, tj. 0, a ovo je prva vrijednost u Skup zapisa, na kraju smo dobili vrijednost "Svibanj".
Kao što vidite, sve je vrlo jednostavno. Ako često trebate dobiti određenu vrijednost iz baze podataka ( kao u posljednjem primjeru), tada preporučujem ispisivanje cijelog koda u zasebnu funkciju (Kako napisati funkciju u VBA Access 2003) s jednim ulaznim parametrom, na primjer, šifrom mjeseca ( ako uzmemo u obzir naš primjer) i jednostavno tamo gdje je potrebno prikazati tu vrijednost pozovemo funkciju koja nam treba sa traženim parametrom i to je to, time ćemo značajno smanjiti VBA kod i poboljšati percepciju našeg programa.
To je sve za danas. Sretno!
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:
Za prebacivanje u 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:
ODABIR 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 koristi se 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. Postoje dvije stvari na koje treba obratiti pozornost u ovom SQL upitu: 1) tekst za pretraživanje je u jednostrukim navodnicima ("); 2) datum je u formatu #Mjesec/Dan/Godina#, što vrijedi za MS Access. U drugim sustavima format za ispisivanje 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);
Upit 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 profit
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 podudara se s nazivom 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čka srednja vrijednost, 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 pohranjeni 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. Obično se zapisi grupiraju 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;
Upit 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 kartezijanska 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 GRUPIRAJ PO dobavljač_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 koristi 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 zbroja prihoda 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 koje prikazuje iznos 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:
ODABIR 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:
ODABIR 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:
ODABIR 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, prikladnije ga je zamijeniti operatorom SWITCH. 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 "Ulje*");
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"Сладкое"");. !}
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 načinu rada 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, informacije o kojima se nalaze 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 ocjene samo 4 i 5.
IZABERI *
OD [PROMIJENITIISPITI]
GDJERAZREDIN(4,5);
Zadatak br. 7. Napravite zanpoc i SQL mod za odabir podataka 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.
Primjeri SQL upita mogu se koristiti za učenje i vježbanje pisanja SQL upita u MS Accessu.
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);
Upit 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 profit
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 podudara se s nazivom 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čka srednja vrijednost, 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.