33. SQLite 2 - MantsSk/CA_PTUA14 GitHub Wiki

Parsisiųskite duombazės failiuką - https://github.com/robotautas/kursas/blob/master/DB/db2/cars.db

Atsidarykite duombazę programoje DB Browser for SQLite ir skiltyje "Execute SQL" galite sekti paskaitos teoriją:

Platesnis WHERE panaudojimas

BETWEEN ...AND

Nurodo žemiausios ir aukščiausios reikšmių intervalą, išrenkant duomenis iš lentelės, t.y. rezultate pateikia duomenis tam tikram reikšmių intervalui:

SELECT * FROM cars WHERE year BETWEEN 1990 AND 1993;

BETWEEN ... AND operatorius apima ir kraštutines reikšmes.

IN

Išrenka rezultatus pagal pateiktą sąrašą:

SELECT * FROM cars WHERE year IN (1995, 2000, 2010);

LIKE

Duomenų išrinkimui iš lentelės naudoja šabloną:

  • % - reiškia nulį arba keletą simbolių
  • _ - reiškia vieną simbolį
SELECT * FROM cars WHERE make LIKE'V%';

kitas pvz. - išrenka eilutes, kuriose modelis iš 2-jų simbolių:

SELECT * FROM cars WHERE model LIKE'__';

arba, tarkim:

SELECT * FROM cars WHERE make LIKE'__n%';

išrinktos eilutės, kuriose gamintojo trečia raidė n:

IS NULL

nurodo, jog vartotoją domina tik eilutės lentelėje su neapibrėžta lauko reikšme:

SELECT * FROM cars WHERE color IS NULL;

AND, OR, NOT

naudojami sąlygų kombinavimui, pvz:

SELECT * FROM cars WHERE make = "Ford" AND price > 40000;


SELECT * FROM cars WHERE make = "Ford" OR year > 2012;

prieš kiekvieną sąlygą galima naudoti NOT:

SELECT * FROM cars WHERE color NOT in ("Violet", "Turquoise", "Orange", "Crimson", "Puce");

sudėtingesnis pavyzdys:

SELECT * FROM cars WHERE (make = "Volvo" OR make = "Ford") AND price NOT BETWEEN 10000 AND 50000;

ORDER BY, DESC

nustato, kaip turi būti išrūšiuoti duomenys užklausos rezultate. Pvz.:

SELECT * FROM cars ORDER BY price;

Jeigu norime atvirkštinio rūšiavimo, naudojame DESC:

SELECT * FROM cars ORDER BY price DESC;

analogiškai tekstiniai įrašai rūšiuojasi pagal abėcelę:

SELECT * FROM cars ORDER BY make DESC;

Case insensitive paieška

SELECT * FROM cars WHERE make = "toyota" COLLATE NOCASE;

||

Tai yra sujungimo operatorius (concatenate operator), naudojamas string reikšmių apjungimui paieškos rezultate:

SELECT "GAMINTOJAS: " || make, model FROM cars;


SELECT make||" "||model AS "full_name", year FROM cars;

šiuo atveju nurodėme, kaip vadinsime stulpelį t.y. 'full_name'. Kitose DB, pvz Postgres, šiam veiksmui galime naudoti CONCAT paragrafą, tačiau SQLITE jis neveikia.

Skaičiavimai

Užklausose galime nurodyti, kokius aritmetinius veiksmus norime atlikti su stulpeliu, prieš jį parodant rezultate:

SELECT make, model, 2021 - year AS "age" FROM cars;

arba:

SELECT make, model, price, ROUND(price / 121.0 * 100, 2) AS "be PVM" FROM cars;

šiuo atveju naudojome funkciją ROUND gauto rezultato suapvalinimui. Nulis po kablelio (121.0) panaudotas tam, kad gautume float reikšmę pirmoje dalyboje.

GROUP BY

Rezultatus galime grupuoti:

SELECT * FROM person GROUP BY gender, last_name;


SELECT
	*
FROM
	person
GROUP BY
	last_name,
	first_name	
HAVING
	gender="Female";

sudėtingesnis pavyzdys:

SELECT
	*
FROM
	person
GROUP BY
	last_name,
	first_name	
HAVING
	date_of_birth 
		BETWEEN 
			date('1980-01-01') 
		AND 
			date('1990-01-01');

Grupavimas

grupavimui dažniausiai naudojamos funkcijos:

  • AVG() - nustato vidurkį grupei
  • COUNT() - nustato eilučių, kurių išraiška yra apibrėžta, kiekį lentelėje
  • MAX() - nustato didžiausią reikšmę
  • MIN() - nustato mažiausią reikšmę
  • SUM() - nustato bendrą sumą, ignoruojant neapibrėžtas reikšmes

funkcijų naudojimas be grupavimo:

SELECT MIN(price), MAX(price), AVG(price) from cars;

suraskime pigiausią fordą:

SELECT make, model, min(price) FROM cars WHERE make="Ford";

pavyzdžiai su grupavimu:

išrinkime kiek ir kokių automobilių yra lentelėje:

SELECT make, count(*) FROM cars GROUP BY make ORDER BY count(*) DESC;

padarykime lentelę, kurioje matytųsi brangiausios spalvos:

SELECT color, max(price), make, model FROM cars GROUP BY color ORDER BY price DESC;

Darant kompleksiškas užklausas, reikėtų laikytis tokio eiliškumo:

  • SELECT stulpelis, grupinė_funkcija
  • FROM lentelė
  • [WHERE sąlyga]
  • [GROUP BY sąrašas_grupavimui]
  • [HAVING grupės sąlyga]
  • [ORDER BY rūšiavimo sąlyga]

tarkime:

SELECT make, model, year, max(price)
FROM cars
WHERE make NOT IN ("Toyota", "Mercury", "Volvo")
GROUP BY price
HAVING year > 2007
ORDER BY make;

Išrinktos brangiausios mašinos, kurių tarpe nėra toyotų, mercury ir volvo. Išfiltruotos tos, kurios senesnės už 2007m. Išrūšiuotos pagal gamintoją.

SQL 2 užduotys

Parsisiųskite lentelę "darbuotojai2.db", atsidarykite ją programoje DB Browser for SQLite ir skiltyje "Execute SQL" atlikite šias SQL užklausas:

  • Išrinkite duomenis apie darbuotoją (asmens kodą, vardą ir pavarde) iš lenteles DARBUOTOJAS, kuris gimęs 1988 m. liepos 20 d.
  • Išrinkite duomenis apie darbuotojus (nuo kada dirba, asmens kodą) iš lentelės DARBUOTOJAS, kurie būtų įsidarbinę nuo 2009 m. spalio 30 d. iki 2012 m. lapkričio 11d.
  • Išrinkite duomenis apie darbuotojus (vardą, Skyriaus ID ir Projekto ID) iš lentelės DARBUOTOJAS, kurie dirba 2 ir 3 skyriuose (panaudoti IN operatorių).
  • Išrinkite duomenis (vardą, pavarde ir asmens kodą) apie visas moteris iš lentelės DARBUOTOJAS (panaudojant operatorių LIKE).
  • Išrinkite visus duomenis apie visus darbuotojus iš lentelės DARBUOTOJAS, kurie yra gimę 12 dieną (panaudojant operatorių LIKE).
  • išrinkite visus projektus iš lentelės PROJEKTAS, kurių pavadinime antra raidė būtų ‘a’.
  • Išrinkite visus darbuotojus iš lentelės DARBUOTOJAS, kuriems nepaskirtos jokios pareigos.
  • Išrinkite duomenis apie darbuotojus (vardą, pavarde, nuo kada dirba ir pareigas), kurie dirba nuo 2011-02-12 ir jų pareigos yra Programuotojai.
  • Išrinkite duomenis apie darbuotojus (vardą, pavardę, Skyriaus ID ir Projekto ID) iš lentelės DARBUOTOJAS, kurie yra iš Gamybos (2) skyriaus arba 1 projekto.
  • Išrinkite visus darbuotojų vardus, išskyrus tuos, kurių vardai prasideda raide ‘A’ .
  • Išrinkite duomenis apie darbuotojus (vardą, pavardę ir nuo kada dirba) iš lentelės DARBUOTOJAS ir išrikiuokite visus duomenis nuo dirbančio seniausiai iki naujausiai.
  • Išrinkite duomenis apie darbuotojus (vardą, pavardę ir nuo kada dirba) iš lentelės DARBUOTOJAS ir išrikiuokite visus duomenis nuo dirbančio naujausiai iki seniausiai.
  • Išrinkite iš lentelės DARBUOTOJAS projektų ID, kurie būtų minimalus ir maksimalus skaičius.
  • Išrinkite duomenis apie tai, kiek kiekviename projekte yra priskirta žmonių (projekto numeris ir skaičius, kiek jame dalyvauja žmonių).
  • #14 punkto užklausą pataisykite taip, kad rodytų tik tuos projektus, kuriems priskirti daugiau nei 3 darbuotojai.
  • Išrinkite duomenis (projekto numeris, pareigos, skaičius) iš lentelės DARBUOTOJAS, kiek programuotojų dirba kiekvienam projekte.