SQL példák Középszintű Érettségire 12._B osztály

/* teljes tablazat lekerdezese */
SELECT * FROM ALKALMAZOTT;
/* projekcio, csak a kivalasztott oszlopok */
SELECT OSZT_AZON, NEV, FONOK FROM ALKALMAZOTT;

/* kifejezesek az oszlopokban */
/* eves fizetes */
SELECT NEV, FIZETES * 12 "EVES FIZETES" FROM ALKALMAZOTT;
/* szoveges oszlopok osszevonasa */
SELECT ALK_AZON||'-'||NEV DOLGOZO, 'OSZTALY', OSZT_AZON FROM ALKALMAZOTT;
/* NULL ertekek a kifejezesben */
SELECT NEV, FIZETES * 12 + JUTALOM "EVES JOVEDELEM" FROM ALKALMAZOTT;
SELECT NEV, FIZETES * 12 + NVL(JUTALOM, 0) "EVES JOVEDELEM" FROM ALKALMAZOTT;

/* duplikalt sorok elhagyása */
SELECT OSZT_AZON FROM ALKALMAZOTT;
SELECT DISTINCT OSZT_AZON FROM ALKALMAZOTT;
SELECT DISTINCT OSZT_AZON, BEOSZTAS FROM ALKALMAZOTT;

/* eredmeny sorok rendezese */
SELECT NEV, BEOSZTAS, FIZETES * 12, OSZT_AZON FROM ALKALMAZOTT
ORDER BY NEV;
/* csokkeno sorrend */
SELECT NEV, BEOSZTAS, BELEPES FROM ALKALMAZOTT
ORDER BY BELEPES DESC;
/* tob rendezesi szempont */
SELECT OSZT_AZON, BEOSZTAS, NEV FROM ALKALMAZOTT
ORDER BY OSZT_AZON, FIZETES DESC;
/* a rendezesi szempont az oszlop sorszamaval */
SELECT NEV, FIZETES FROM ALKALMAZOTT
ORDER BY 2;

/* válogatás a sorok kozul */
select * from alkalmazott where nev = 'SKOT';
select * from alkalmazott where fizetés < 1200';
select * from alkalmazott where belepes > '04-JUN-87';
select * from alkalmazott where jutalom >= 500;
select * from alkalmazott where fizetes between 1000 and 2000;
select * from alkalmazott where fonok in (7902, 7566, 7788);
select * from alkalmazott where nev like 'S%';
select * from alkalmazott where nev like '____';
select * from alkalmazott where fonok is null;
select * from alkalmazott where fonok is not null;
/* osszetett feltetelek */
select alk_azon, nev, beosztas, fizetes from alkalmazott
where sal between 1000 and 2000 and beosztas = 'ELOADO';
select alk_azon, nev, beosztas, fizetes from alkalmazott
where sal between 1000 and 2000 or beosztas = 'ELOADO';

/* osztalyok kodjai es nevei rendezve */
select oszt_azon, nev from osztaly order by oszt_azon;
/* kulonbozo beosztasok */
select distinct beosztas from alkalmazott;
/* 10-es és a 20-as osztalyon dolgozok nev szerint rendezve */
select * from alkalmazott
where oszt_azon in (10,20)
order by nev;
/* 20-as osztalyon dolgozo eloadok neve es beosztasa */
select nev, beosztas from alkalmazott where oszt_azon = 20;
/* "ES"-t vagy "IR"-t tartalmazo nevek */
select nev from alkalmazott
where nev like '%ES%' or nev like '%IR%';
/* nev, beosztas, fizetes azokra akiknek van fonokuk */
select nev, beosztas, fizetes from alkalmazott
where fonok is not null;
/* az 1981-ben belepett alkalmazottak */
select nev, oszt_azon, belepes from alkalmazott
where belepes like '%81';
select nev, oszt_azon, belepes from alkalmazott
where belepes between '01-JAN-81' and '31-DEC-81';
/* osszevont oszlopok */
select nev||' '||beosztas||' beosztasban dolgozik '||belepes||' ota'
"ki, mit csinal, mi ota" from alkalmazott;

/* valtozokat tartalmazo kifejezesek */
select nev, belepes from alkalmazott
where belepes between '&kezdo' and '&veg';
select nev, beosztas, fizetes, fonok, oszt_azon from alkalmazott
where beosztas = '&beosztas';
/* sqlplus valtozo hasznalata */
define eves = 'fizetes*12+nvl(jutalom,0)'
select nev, &eves from alkalmazott  where &eves > 30000;

/* karakteres fuggvenyek */
/* nagybetusse alakitas */
select nev, beosztas from alkalmazott
where beosztas = upper ('&beosztas');
/* kisbetusse alakitas */
select lower(beosztas) from alkalmazott;
/* szavak kezdo betui nagy betuve */
select initcap(nev) from alkalmazott;
/* feltoltes jobbrol vagy balrol */
select rpad(nev, 10, '_') nev, lpad(beosztas, 10) beosztas,
lpad (oszt_azon, 7, '.') reszleg from alkalmazott;
/* karakterek eltavolitasa jobb vagy bal szelrol */
select rtrim(beosztas, 'KR'), ltrim(nev, 'SM') from alkalmazott;
/* sztring pozicioja az oszlopban */
select nev, instr(nev, 'A') from alkalmazott;
/* sztring hossza */
select nev, length(nev) from alkalmazott;
/* karakterek csereje, ekezetek nelkuli kiiratas */
select translate(nev, 'ÁÉÓÖÔÚÜŰ', 'AEOOOUUU') from alkalmazott;
/* fuggvenyek egymasba agyazasa, E betuk szama a nevekben */
select nev, length(nev)-length(replace(nev, 'E')) E from alkalmazott;

/* numerikus fuggvények */
/* kerekites */
define x=fizetes/30
select &x, round(&x), round(&x,2), round(&x,-1) from alkalmazott
where oszt_azon = 10;
/* csonkitas */
select &x, trunc(&x), trunc(&x,2), trunc(&x,-1) from alkalmazott
where oszt_azon = 10;
/* fizetes 15%-al novelt erteke */
select nev, round(fizetes*1.15) "novelt fizetes" from alkalmazott;

/* datum fuggvenyek */
/* aktualis datum lekerdezese */
select sysdate from sys.dual;
/* aritmetikai muveletek datumokkal */
select belepes, belepes+7, sysdate-belepes from alkalmazott
where oszt_azon = 10;
/* kulonbseg honapokban, a 160 honapnal regebben belepett alkalmazottak */
select nev, belepes, months_between(sysdate, belepes)
from alkalmazott
where months_between(sysdate, belepes) > 160;
/* honapok hozzaadasa, levonasa */
select add_months(sysdate, 2) from sys.dual;
select add_months(sysdate, -5) from sys.dual;
/* kovetkezo nap */
select next_day(sysdate, 'SUNDAY') from sys.dual;
/* honap utolso napja */
select last_day(sysdate) from sys.dual;
/* datum kerekites */
select sysdate, round(sysdate, 'month'), round(sysdate, 'year') from sys.dual;
/* datum csonkitas */
select sysdate, trunc(sysdate, 'month'), trunc(sysdate, 'year') from sys.dual;

/* konverzios fuggvenyek */
/* szam sztringge alakitas */
select nev, to_char(fizetes, '$9,999') from alkalmazott
where beosztas = 'MANAGER';
/* datum sztringge alakitas */
select to_char(sysdate, 'yyyy month dd day') from sys.dual;
/* ido es datum */
select to_char(sysdate, 'yyyy.mm.dd HH:MI:SS') from sys.dual;
/* mahoz 2 evre milyen nap lesz */
select to_char(add_months(sysdate, 24), 'DAY') from sys.dual;
/* sztring datumma alakitas */
select to_date('96.11.20', 'YY.MM.DD') from sys.dual;

/* csoport fuggvenyek */
/* teljes tablara */
select min(fizetes), max(fizetes), sum(fizetes), avg(fizetes), count(fizetes)
from alkalmazott;
/* csoportokra */
select oszt_azon, min(fizetes), max(fizetes), sum(fizetes), avg(fizetes), count(fizetes)
from alkalmazott
group by oszt_azon;
/* valogatas a csoportok kozul */
select oszt_azon, avg(fizetes) from alkalmazott
group by oszt_azon having count(*) > 3;
/* valogatas a sorok es csoportok kozul is */
select oszt_azon, avg(fizetes) from alkalmazott
where beosztas != 'ELOADO'
group by oszt_azon having min(fizetes) > 1000;
/* beosztasonkent a legkisebb fizetes */
select beosztas, min(fizetes) MIN, max(fizetes) MAX from alkalmazott
group by beosztas;
/* managerek szama */
select count(*) from alkalmazott where beosztas = 'MANAGER';

/* lekerdezes tobb tablabol */
/* egyen osszekapcsolas */
select alkalmazott.nev, beosztas, osztaly.nev from alkalmazott, osztaly
where alkalmazott.oszt_azon = osztaly.oszt_azon;
/* Descartes szorzat, osszekapcsolas minden kombinacioban */
select osztaly.nev, alkalmazott.nev from osztaly, alkalmazott;
/* nem egyen osszekapcsolas */
select a.nev, a.fizetes, f.f_oszt from alkalmazott a, fiz_oszt f
where a.fizetes between f.min and f.max;
/* Budapesten dolgozok nevsora */
select a.nev from alkalmazott a, osztaly o
where a.oszt_azon = o.oszt_azon and o.varos = 'BUDAPEST';

/* kulso osszekapcsolas */
select o.oszt_azon, o.nev, a.nev from alkalmazott a, osztaly o
where a.oszt_azon (+) = o.oszt_azon;

/* tabla osszekapcsolasa onmagaval */
/* fonok beosztott parok */
select a.nev fonok, b.nev beosztott from alkalmazott a, alkalmazott b
where b.fonok = a. alk_azon;
select a.nev fonok, b.nev beosztott from alkalmazott a, alkalmazott b
where b.fonok = a. alk_azon (+);
/* alkalmazottak akik a fonokuk elott leptek be */
select a.nev BEOSZTOTT, a.belepes, b.nev FÔNÖK, b.belepes
from alkalmazott a, alkalmazott b
where a.fonok = b.alk_azon and a.belepes < b.belepes;

/* halmaz muveletek */
/* unio osztalyok, ahol manager vagy ugynok dolgozik */
select distinct oszt_azon from alkalmazott
where beosztas = 'MANAGER'
union
select distinct oszt_azon from alkalmazott
where beosztas = 'UGYNOK';
/* metszet, azok az osztalyok amelyeknek van dolgozoja */
select distinct oszt_azon from alkalmazott
intersect
select oszt_azon from osztaly;
/* kulonbseg, azok az osztalyok, ahol nem dolgozik senki */
select oszt_azon from osztaly
minus
select oszt_azon from alkalmazott;

/* egymasba agyazott lekerdezesek */
/* legkisebb fizetesu dolgozo */
select nev, fizetes from alkalmazott
where fizetes = (select min(fizetes) from alkalmazott);
/* szaboval azonos munkakorben dolgozok */
select nev, beosztas from alkalmazott
where beosztas = (select beosztas from alkalmazott
where nev = 'SZABO');
/* legkisebb fizetesu dolgozo osztalyonkent */
select nev, fizetes, oszt_azon from alkalmazott
where (fizetes, oszt_azon) in
(select min(fizetes), oszt_azon from alkalmazott
group by oszt_azon);
/* 30-as minimum fizetesenel tobbet keresok */
select nev, beosztas, fizetes from alkalmazott
where fizetes > any
(select fizetes from alkalmazott
where oszt_azon = 30);
/* a 30-as osztalyon legtobbet keresonel magasabb fizetesuek */
select fizetes, beosztas, nev from alkalmazott
where fizetes > all
(select fizetes from alkalmazott where oszt_azon = 30);
/* legmagasabb atlagfizetesu beosztas */
select beosztas, avg(fizetes) from alkalmazott
group by beosztas
having avg(fizetes) =
(select max(avg(fizetes)) from alkalmazott
group by beosztas);
/* korrelacio */
/* akiknek nagyobb a fizetesuk, mint az osztaly atlag */
select oszt_azon, nev, fizetes from alkalmazott a
where fizetes > (select avg(fizetes) from alkalmazott
where oszt_azon = a.oszt_azon)
order by oszt_azon;
/* akiknek legalabb egy beosztottja van */
select nev, beosztas, oszt_azon from alkalmazott a
where exists (select * from alkalmazott
where fonok = a.alk_azon);
/* osztolyonkent az utolsonak belepett dolgozo */
select oszt_azon, nev, belepes from alkalmazott
where (oszt_azon, belepes) in (select oszt_azon, max(belepes)
from alkalmazott group by oszt_azon)
order by belepes dec;
/* osztaly amelyiknek nincs dolgozoja */
select nev from osztaly o
where not exists (select * from alkalmazott
where oszt_azon = o.oszt_azon);
/* az az osztaly, ahol legmagasabb az eves atlagjovedelem */
define x=fizetes * 12+nvl(jutalek,0)
select oszt_azon, avg(&x) from alkalmazott
group by oszt_azon
having avg(&x) = (select max(avg(&x)) from alkalmazott
group by oszt_azon);
===========================================================
/* Demo tablak feltoltese */
/* set termout off */
DROP TABLE ALKALMAZOTT;
DROP TABLE OSZTALY;
DROP TABLE FIZ_OSZT;
DROP TABLE DUMMY;
DROP SEQUENCE AZON;
DROP VIEW OSZT_ATLAG;
DROP VIEW BEOSZT_ATLAG;

CREATE TABLE OSZTALY (
 OSZT_AZON        NUMBER(2) NOT NULL,
 NEV              VARCHAR(14),
 VAROS            VARCHAR(13));

INSERT INTO OSZTALY VALUES (10,'SZAMLAZAS','BUDAPEST');
INSERT INTO OSZTALY VALUES (20,'KUTATAS','ESZTERGOM');
INSERT INTO OSZTALY VALUES (30,'RAKTAR','BUDAORS');
INSERT INTO OSZTALY VALUES (40,'FORGALMAZAS','BUDAPEST');

CREATE TABLE ALKALMAZOTT (
 ALK_AZON            NUMBER(4) NOT NULL,
 NEV                 VARCHAR(10),
 BEOSZTAS            VARCHAR(9),
 FONOK               NUMBER(4),
 BELEPES             DATE,
 FIZETES             NUMBER(7,2),
 JUTALOM             NUMBER(7,2),
 OSZT_AZON           NUMBER(2) NOT NULL);

INSERT INTO ALKALMAZOTT VALUES (7839,'KIRALY','ELNOK',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO ALKALMAZOTT VALUES (7698,'BIRO','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO ALKALMAZOTT VALUES (7782,'CSALO','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO ALKALMAZOTT VALUES (7566,'JONAS','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7654,'MARTON','UGYNOK',7698,'28-SEP-81',1250,1400,30);
INSERT INTO ALKALMAZOTT VALUES (7499,'ALI','UGYNOK',7698,'20-FEB-81',1600,300,30);
INSERT INTO ALKALMAZOTT VALUES (7844,'TURCSI','UGYNOK',7698,'8-SEP-81',1500,0,30);
INSERT INTO ALKALMAZOTT VALUES (7900,'JENES','ELOADO',7698,'3-DEC-81',950,NULL,30);
INSERT INTO ALKALMAZOTT VALUES (7521,'VAMOS','UGYNOK',7698,'22-FEB-81',1250,500,30);
INSERT INTO ALKALMAZOTT VALUES (7902,'FINESZ','ELEMZO',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7369,'SZABO','ELOADO',7902,'17-DEC-80',800,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7788,'SKOT','ELEMZO',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7876,'ADAM','ELOADO',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO ALKALMAZOTT VALUES (7934,'MULLER','ELOADO',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE FIZ_OSZT (
 F_OSZT            NUMBER,
 MIN               NUMBER,
 MAX               NUMBER);

INSERT INTO FIZ_OSZT VALUES (1,700,1200);
INSERT INTO FIZ_OSZT VALUES (2,1201,1400);
INSERT INTO FIZ_OSZT VALUES (3,1401,2000);
INSERT INTO FIZ_OSZT VALUES (4,2001,3000);
INSERT INTO FIZ_OSZT VALUES (5,3001,9999);

CREATE TABLE DUMMY (
 DUMMY               NUMBER );

INSERT INTO DUMMY VALUES (0);


CREATE SEQUENCE AZON
INCREMENT BY 1
START WITH 7935
NOCACHE;


CREATE VIEW OSZT_ATLAG AS
SELECT OSZTALY.NEV, AVG(FIZETES) ATLAG FROM OSZTALY, ALKALMAZOTT
WHERE OSZTALY.OSZT_AZON = ALKALMAZOTT.OSZT_AZON
GROUP BY OSZTALY.NEV;

CREATE VIEW BEOSZT_ATLAG AS
SELECT BEOSZTAS, AVG(FIZETES) ATLAG FROM ALKALMAZOTT
GROUP BY BEOSZTAS;


COMMIT;