Programozás | SQL » SQL select gyakorlása, dátumkezelés Oracle RDBMS-ben

10. Gyakorlat SQL SELECT gyakorlása, dátumkezelés Oracle RDBMS-ben Oracle adatbáziskezelő elérése: http://arrakis.iituni-miskolchu:5560/isqlplus Tekintsük az alábbi relációs sémákat: EMBER [ id, név, város, szuldatum ] AUTO [ rsz, tulaj, tipus, szin, ar, evjarat ] Hozzuk létre a megfelelő táblákat a tablaletrehozas.sql script futtatásával Töltsük fel a táblákat adatokkal a tablafeltoltes.sql scriptet futtatva Kérdezzük le a táblák adatait: SELECT * FROM ember; SELECT * FROM auto; Konstruáljuk meg az alábbi lekérdezéseket: 1. A miskolci emberek neve névsorrendben. SELECT nev FROM ember WHERE varos=’Miskolc’ ORDER BY nev; 2. A Fiat típusú autók átlagára. SELECT AVG(ar) FROM auto WHERE tipus=’Fiat’; 3. A miskolci autók darabszáma. SELECT COUNT(*) FROM auto a, ember e WHERE a.tulaj=eid AND varos LIKE ’Miskolc%’; 4. Az autók darabszáma városonként, városok szerint névsorrendben. SELECT varos, COUNT(*) FROM auto a, ember e

WHERE a.tulaj=eid GROUP BY varos ORDER BY varos; 5. Azon emberek neve névsorrendben, akiknek van Fiat autója. SELECT nev FROM ember e, auto a WHERE e.id=atulaj AND tipus=’Fiat’ ORDER BY nev; SELECT nev FROM ember WHERE id IN (SELECT tulaj FROM auto WHERE tipus=’Fiat’) ORDER BY nev; 6. Azok az emberek, akiknek nincs Fiat autója (fordított névsorrendben). SELECT nev FROM ember WHERE id NOT IN (SELECT tulaj FROM auto WHERE tipus=’Fiat’) ORDER BY nev DESC; (SELECT nev FROM ember) MINUS (SELECT nev FROM ember e, auto a WHERE e.id=atulaj AND tipus=’Fiat’) ORDER BY nev DESC; 7. A Fiat autók átlagáránál olcsóbb autók darabszáma. SELECT COUNT(*) FROM auto WHERE ar < (SELECT AVG(ar) FROM auto WHERE tipus=’Fiat’); 8. A Fiat autók átlagáránál drágább autók tulajdonosainak a neve. SELECT nev FROM ember e, auto a WHERE e.id=atulaj AND ar > (SELECT AVG(ar) FROM auto WHERE tipus=’Fiat’); 9. A legolcsóbb Fiat típusú autó

tulajdonosának neve és címe. SELECT nev, varos FROM ember e, auto a WHERE e.id=atulaj AND ar = (SELECT MIN(ar) FROM auto WHERE tipus=’Fiat’); 10. Mely városokban van 2-nél kevesebb Fiat autó. SELECT varos, COUNT(*) db FROM auto a, ember e WHERE e.id=atulaj AND tipus=’Fiat’ GROUP BY varos HAVING COUNT(*)<2; 11. Az emberek neve, címe és autójuk rendszáma, típusa Azok az emberek is szerepeljenek az eredményben, akiknek nincs autójuk. SELECT nev, varos, rsz, tipus FROM ember e LEFT OUTER JOIN auto a ON a.tulaj=eid; 12. Kik azok az emberek, akik a saját városukra jellemző átlagárnál drágább autóval rendelkeznek? SELECT e1.nev FROM ember e1, auto a1 WHERE e1id=a1tulaj AND a1ar > (SELECT AVG(a2.ar) FROM ember e2, auto a2 WHERE e2id=a2tulaj GROUP BY e2varos HAVING e1.varos=e2varos); VIEW: adatbázis objektum, származtatott tábla Létrehozása: CREATE VIEW vnev [(mezo1, mezo2, )] AS SELECT .; Törlése: DROP VIEW vnev; Az adatbázisban permanens módon

nem kerül letárolásra a VIEW tartalma, csak a létrehozási műveletsor. A műveletsor minden VIEW-ra történő hivatkozáskor végrehajtódik 13. Melyek azok a városok, ahol mindenféle autótípus előfordul (osztás!). CREATE VIEW v1 AS SELECT varos, tipus FROM ember e, auto a WHERE e.id=atulaj; CREATE VIEW v2 AS SELECT DISTINCT tipus FROM auto; CREATE VIEW v3 AS SELECT DISTINCT varos FROM v1; CREATE VIEW v4 AS ( (SELECT varos, tipus FROM v3, v2) minus (SELECT varos, tipus FROM v1) ); (SELECT varos FROM v3) minus (SELECT varos FROM v4); 14. Melyik az a város, ahol a legtöbb fehér Fiat van. CREATE VIEW v AS SELECT varos, COUNT(*) db FROM auto a, ember e WHERE e.id=atulaj AND tipus=’Fiat’ AND szin=’feher’ GROUP BY varos; SELECT varos FROM v WHERE db = (SELECT MAX(db) FROM v); 15. Melyik az a város, ahol minden embernek van autója. CREATE VIEW v5 AS SELECT varos FROM ember WHERE id NOT IN (SELECT tulaj FROM auto); (SELECT DISTINCT varos FROM ember) MINUS

(SELECT varos FROM v5); Töröljük a létrehozott VIEW nézeti táblákat. DROP VIEW v5; DROP VIEW v4; DROP VIEW v3; DROP VIEW v2; DROP VIEW v1; DROP VIEW v; Dátumkezelés Oracle-ben: aktuális dátum lekérdezése: SELECT SYSDATE FROM DUAL; holnapi dátum lekérdezése: SELECT SYSDATE+1 FROM DUAL; aktuális dátum lekérdezése más formátumban: SELECT TO CHAR(SYSDATE, ‘yyyy.mmdd’) FROM DUAL; aktuális dátumból csak az év lekérdezése: SELECT TO CHAR(SYSDATE, ‘yyyy’) FROM DUAL; SELECT TO CHAR(SYSDATE, ‘yy’) FROM DUAL; aktuális dátumból csak a hónap lekérdezése: SELECT TO CHAR(SYSDATE, ‘mm’) FROM DUAL; aktuális dátumból csak a nap lekérdezése: SELECT TO CHAR(SYSDATE, ‘dd’) FROM DUAL; 5-évvel korábbi dátum lekérdezése: SELECT TO CHAR(SYSDATE, ‘yyyy’) –5 FROM DUAL; 16. A 40 évnél idősebb autótulajdonosok neve, névsorrendben. CREATE VIEW v1 AS SELECT nev, TO CHAR(SYSDATE, ‘yyyy’) – TO CHAR(szuldatum, ‘yyyy’) kor FROM

ember; SELECT DISTINCT nev FROM auto a, ember e WHERE a.tulaj=eid AND nev IN (SELECT nev FROM v1 WHERE kor > 40) ORDER BY nev; 17. A miskolci emberek átlagéletkora. CREATE VIEW v2 AS SELECT nev, TO CHAR(SYSDATE, ‘yyyy’) – TO CHAR(szuldatum, ‘yyyy’) kor FROM ember; SELECT AVG(kor) FROM v2 WHERE nev IN (SELECT nev FROM ember WHERE varos=’Miskolc’); 18. A „Nagy” nevezetű emberek közül ki a legidősebb. CREATE VIEW v3 AS SELECT nev, TO CHAR(SYSDATE, ‘yyyy’) – TO CHAR(szuldatum, ‘yyyy’) kor FROM ember WHERE nev LIKE ’Nagy%’; SELECT nev FROM v3 WHERE kor = (SELECT MAX(kor) FROM v3); 19. Emberek neve, autóik rendszáma, és az autók életkora. SELECT nev, rsz, TO CHAR(SYSDATE, ’yyyy’) – evjarat FROM auto a, ember e WHERE a.tulaj=eid; 20. Mindenki számolja ki, hogy hány éves, és hány napja született. (Az évszám és a születési dátum helyére mindenki a sajátját helyettesítse be!) SELECT TO CHAR(SYSDATE, ’yyyy’) – 1976

FROM DUAL; SELECT SYSDATE – TO DATE(’1976.0306’, ‘yyyymmdd’) FROM DUAL; Töröljük a létrehozott VIEW nézeti táblákat. DROP VIEW v1; DROP VIEW v2; DROP VIEW v3; Töröljük az autó, ember táblákat a tablatorles.sql script futtatásával