Adatbázis Rendszerek II. 3. SQL alapok
B IT M A N
22/1
v: 2016.10.20 B IT MAN
Oracle adattípusok Szöveges adattípusok
– – – – –
22/2
CHAR (1-2000) VARCHAR (1-4000) VARCHAR2 (1-4000, nemzeti karakterekhez) CLOB (max. 8 TB) NCLOB (max. 8 TB, nemzeti karakterekkel, kínai, japán, ciril…)
B IT M A N
Oracle adattípusok Numerikus adattípusok
– – – – – –
22/3
NUMBER(SzámjegyekSzáma, TizedesekSzáma) NUMBER(38,0) – Ez a maximum! INTEGER SHORTINTEGER LONGINTEGER DECIMAL
B IT M A N
Oracle adattípusok Dátum és időpont adattípusok
– – – – – –
22/4
DATE INTERVAL DAY TO SECOND INTERVAL YEAR TO MONTH TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE
B IT M A N
Dolgozók
22/5
B IT M A N
Órai feladat 1.
Dolgozó Kód
Név
Város
Beosztás
Belépés
Fizetés
Osztály
Készítsünk egy táblát, dolgozók nyilvántartására: CREATE TABLE Dolgozó( Kód Number(4) Primary key, Név Varchar(20) Not Null, Város Varchar(20), Beosztás Varchar(20), Belépés Date Default sysdate, Fizetés Number(7), Osztály Varchar(15) );
22/6
Elsődleges kulcs mező A mezőbe kötelező adatot írni
Alapérték: az aktuális dátum
B IT M A N
Órai feladat 1.
Dolgozó Kód
Név
Város
Beosztás
Belépés
Fizetés
Osztály
Írasd ki a tábla szerkezetét. Töltsd fel a megadott adatokkal.
1. Vigye fel mindenki saját magát, tetszőleges adatokkal! 2. Hibás Ilona miskolci elemző. Vigyük fel az új adatokat. 3. Hibás Ilona a fejlesztésre került, 300 fizetéssel. Vigyük fel az új adatokat. 4. Írassuk ki a miskolci emberek nevét. 5. Írassuk ki a nem miskolci emberek nevét, városát. 6. Írassuk ki a fejlesztés osztályon dolgozók nevét, ABC sorrendben. 7. Írassuk ki a neveket, fizetéseket, fizetés szerinti csökkenő sorrendben. 8. Írassuk ki az M betűvel kezdődő neveket! 9. Írassuk ki az A és a Z betűre végződő neveket! 10. Írassuk ki azok kódját, nevét, akiknek a kódjában van 4-es számjegy. 22/7
B IT M A N
Órai feladat 1.
Dolgozó Kód
Név
Város
Beosztás
Belépés
Fizetés
Osztály
11. Írassuk ki azok kódját, nevét, akiknek a kódjában van 1-es, 2-es, és 4-es számjegy is. 12. Írassuk ki azok kódját, nevét, akiknek a vezetékneve 4 betűs! 13. Írassuk ki az 1030 és 1130 közötti kódúak nevét, kódját. 14. Kinek nincs megadva a városa? 15. Írassuk ki a rendszerdátumot. 16. Írassuk ki azok nevét, belépési dátumát, akik 2013-ban léptek be. 17. Írassuk ki azok nevét, belépési dátumát, akik márciusban léptek be. 18. Írassuk ki a 2010.01.01 és 2013.12.31 között belépett dolgozók nevét, belépési dátumát. 19. Ki, hány éve dolgozik a cégnél? 20. Milyen osztályok vannak? 21. Írassuk ki a fejlesztésen dolgozó miskolciak nevét. 22/8
B IT M A N
Órai feladat 1.
Dolgozó Kód
Név
Város
Beosztás
Belépés
Fizetés
Osztály
22. Írassuk ki a fejlesztésen és a terjesztésen dolgozó nem miskolciak nevét. 23. Írassuk ki azon osztályok nevét, ahol dolgozik budapesti! 24. Hány rekord van a táblában? 25. Írassuk ki az átlagfizetést. 26. Írassuk ki az átlagfizetéstől többet keresők nevét, fizetését. 27. Írassuk ki az osztályonkénti átlagfizetést. 28. Melyik programozónak a legmagasabb a fizetése? 28b. Melyik programozónak a legalacsonyabb a fizetése? 29. Írassuk ki az átlagos programozói fizetéstől többet kereső programozók nevét, fizetését. 30. Melyik tesztelő keres többet, mint a legtöbbet kereső eladó? 31. Hányan dolgoznak az egyes osztályokon? 32. Az egyes városokban hány programozó van? 22/9
B IT M A N
Órai feladat 1.
Dolgozó Kód
Név
Város
Beosztás
Belépés
Fizetés
Osztály
33. Írassuk ki azok nevét, akik abban a városban laknak, ahol a legtöbben laknak. 34. Mennyit keresnek összesen a tervezők és az elemzők? 35. Mely osztályok azok, ahol összesen legalább 1000 BitCoin-t keresnek? 36. Mely osztályokon dolgoznak legalább hárman? 37. Írassuk ki a miskolci eladók és az egri tesztelők nevét. 38. Írassuk ki a miskolci nem programozók nevét. 39. Írassuk ki azon osztályok nevét, ahol minden ember fizetése kevesebb 320 BitCoin-nál! 40. Hány darab különböző város van? 41. Az egyes beosztásokban dolgozók hány darab különböző városban laknak? 42. Írassuk ki azon beosztások nevét, melyeknek minden városban van képviselője! 22/10
B IT M A N
Autók
22/11
B IT M A N
Órai feladat 2.
Autó - Tulajdonos
Autó Rendszám
Tulajdonos Típus
Szín
Kor
Ár
Készítsük el a táblákat: Create Table Tulajdonos( TKód Number(4) Primary Key, Név Varchar2(20) Not Null, Város Varchar2(20) );
22/12
Tulaj
TKód
Név
Város
Create Table Autó( Rendszám Char(7) Primary Key, Típus Varchar2(25) Not Null, Szín Varchar2(15), Kor Number(2), Ár number(8), Tulaj Number(4), Foreign key (Tulaj) References Tulajdonos(TKód) );
B IT M A N
Órai feladat 2. Autó Rendszám
Típus
Autó - Tulajdonos Szín
Kor
Ár
Tulaj
Tulajdonos TKód Név
Város
1. Írassuk ki az autók rendszámát, és tulajdonosuk nevét. 2. Írjuk ki a miskolci tulajdonosok autóinak adatait. 3. Hány darab autó van az egyes városokban? 4. Kinek, milyen rendszámú autója van? Szerepeljen az is, akinek nincs autója! 5. Az egyes autóknak (rendszám) ki a tulajdonosa? Minden autó szerepeljen a listában! 6. A miskolci autók átlagárától drágább, nem miskolci autók rendszáma. 7. Azok neve, akiknek több autójuk van?
22/13
B IT M A N
Órai feladat 2. Autó Rendszám
Típus
Autó - Tulajdonos Szín
Kor
Ár
Tulaj
Tulajdonos TKód Név
Város
8. Azon autók rendszáma, melyek idősebbek, mégis drágábbak saját típustársuknál. 9. Bármely piros autó áránál olcsóbb autók adatai. 10. Minden piros autó áránál olcsóbb autók adatai. 11. Azok neve, akiknek nincs autójuk.
22/14
B IT M A N
B
22/15
O
L
T
B IT M A N
Órai feladat 3. Kkod
Nev
Bolt adatbázis Tkod
Nev
Ar
Kategoria
K-T
Termek Leiras
Idopont Sorszam
Vasarlas Darab
Fkod
Leiras
Nev
Vasarlo
V-F
FizModok
VID Cim
22/16
B IT M A N
Órai feladat 3.
Bolt – Struktúra
Kategoria
Termek
Kkod Nev
Kategoria Tkod Nev Ar
Leiras
Vasarlas Tkod Sorszam Idopont Darab VID
FizModok
Vasarlo VID Nev Cim
22/17
Fizmod
Fkod Leiras
B IT M A N
Órai feladat 3.
Kategoria[Kkod, Nev] Termek[Kategoria,Tkod,Nev,Ar,Leiras] Vasarlas[Tkod,Sorszam,Idopont,Darab,VID] Vasarlo[VID,Nev,Cim,Fizmod] FizModok[Fkod, Leiras]
Hozd létre a táblákat! Megszorítások:
– NOT NULL – Kötelező adatot írni a mezőbe – UNIQUE(Nev) – Egyediség előírása – FOREIGN KEY (Katkód) REFERENCES Kategoria(Kkod)) – Idegen kulcs megadása – DEFAULT Current_Timestamp – Alapérték megadása (itt az aktuális időbélyeg van megadva) Megoldás kódok, azonosítók kezelésére: – CREATE SEQUENCE seq_v; – Automatikusan növekvő érték létrehozása – seq_v.NEXTVAL – Az épp soron következő érték lekérése Vidd fel a táblákba az adatokat! 22/18
B IT M A N
Órai feladat 3.
Kategoria[Kkod, Nev] Termek[Kategoria,Tkod,Nev,Ar,Leiras] Vasarlas[Tkod,Sorszam,Idopont,Darab,VID] Vasarlo[VID,Nev,Cim,Fizmod] FizModok[Fkod, Leiras]
1. Írassa ki a kategóriák minden adatát. 2. Írassa ki a ruha kategóriájú termékek nevét, árát. 3. Írassa ki a 200 Ft-nál olcsóbb termékek nevét, árát. 4. Írassa ki a 4000 Ft-nál drágább, de 12000 Ft-nál olcsóbb termékek nevét, árát. 5. Írassa ki Korcs Éva vásárlásait (időpont, termék neve, darabszáma) 6. Kik vásároltak készpénzért? Elég a nevük. 7. Hány féle pia kategóriájú termék van? 8. Összesen hány darab pia kategóriájú terméket adtak már el? 9. Írassa ki az egri vásárlók nevét, címét. 10. Írassa ki a nem egri vásárlók nevét, címét. 11. Írassa ki a bankkártyával fizető egriek nevét. 12. Ki vásárolt puskát? 22/19
B IT M A N
Órai feladat 3.
Kategoria[Kkod, Nev] Termek[Kategoria,Tkod,Nev,Ar,Leiras] Vasarlas[Tkod,Sorszam,Idopont,Darab,VID] Vasarlo[VID,Nev,Cim,Fizmod] FizModok[Fkod, Leiras]
13. Írassa ki a sört vagy bort vásárlók nevét? 14. Benzinből ki, mikor, mennyit vásárolt? 15. Összesen mennyi volt a bevétel benzinből? 16. Ki, mikor, milyen nőt vásárolt? 17. Ki, mikor vásárolt mackót, és milyet (alsó, póló v. felső)? 18. Hány darab mackó (alsó, póló v. felső) fogyott összesen? 19. Összesen mennyi volt az utánvétes bevétel? 20. Ki, melyik településen lakik? 21. Melyik településen hányan laknak? 22. Melyek az s-betűre végződő leírású termékek? (Név, leírás) 23. Hány darab termék nevében van c-betű? 24. Mely termékek kódja végződik 2-esre? (Név, kód) 25. A nem kaja kategóriájú termékek közül melyek olcsóbbak 1000 Ftnál? (Név, ár) 22/20
B IT M A N
Órai feladat 3.
Kategoria[Kkod, Nev] Termek[Kategoria,Tkod,Nev,Ar,Leiras] Vasarlas[Tkod,Sorszam,Idopont,Darab,VID] Vasarlo[VID,Nev,Cim,Fizmod] FizModok[Fkod, Leiras]
26. A Béla keresztnevű ember milyen egyéb kategóriájú terméket vásárolt? (Termék neve) 27. Összesen hány darab csipszet vásároltak már? (a meglévő adatok alapján) 28. Milyen termékeket vásároltak már átutalással? (Név, de egy név csak egyszer szerepeljen!) 29. Milyen termékeket nem vásároltak még átutalással? 30. Az egriek mennyi pénzt költöttek el összesen? 31. Az egyes termék kategóriákban hány darab termék van? 32. Kinek nincs megadva a lakcíme? 33. Törölje ki Ká Rozi lakcímét! Futtassa a 31. parancsot újra! 34. Ki az, aki átutalással fizet, de nincs megadva a lakcíme? 35. Mely termékek kódja végződik nem 2-esre? (Név, kód) 22/21
B IT M A N
VÉGE 22/22
B IT M A N
