/* DML utasítások
insert, delete, update
*/
select * from sz;


insert into sz (n,gy) values('Zelefánt','banán');

insert into sz (n) values('Robi');

select * from sz2;
insert into sz2 (n) values('majom');

insert into sz (n,gy) values('Kutya',null);
select * from sz;

insert into sz2 (n,gy) values('Kutya',null);
select * from sz2;


insert into sz (gy,n) values('Macska','egér');
select * from sz;

insert into sz values('Viziló','kenyér');

desc sz

insert into sz (n) select ename from emp;

select * from sz;

insert into sz select ename,job from emp where sal >1300;

select * from sz;
insert into sz select ename,sysdate from emp where sal <1000;


update sz set gy='ropi' where n='Robi';

select * from sz;

update sz set gy='alma' where gy is null;

update sz set gy='karfiol';
rollback;

select * from emp;

update emp set sal=sal+nvl(comm,0),comm=0;



delete sz where n like 'M%';
select * from sz;

delete sz;

drop table sz;

rollback;

-------------------------------------------------------

/*
VIEW
CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW [ schema.]nameview AS subquery
   [ (alias [ inline_constraint [ inline_constraint ]... ]
            | out_of_line_constraint [, alias [ inline_constraint [ inline_constraint ]... ]
            | out_of_line_constraint
            ]...
*/


select * from sz;

create view nevsor as select n from sz;
select * from nevsor;

select * from sila.szeret;
create view hnevsor as select nev from sila.szeret;
select * from hnevsor;

select * from emp;
create or replace view munkas as select ename,job from emp;
select * from munkas;

create or REPLACE view ALMATSZERETOK AS select * from sz where gy='alma';
select * from almatszeretok;

--bevitel nézet táblába---Lehet?-----------------------------

-- nevsor-ba
insert into nevsor values('Zelefant');

select * from nevsor;

select * from sz;

select * from sz2;
create view nevsor2 as select n from sz2;
select * from nevsor2;

-- nevsor2-be
insert into nevsor2 values('Majom');
select * from nevsor2;
select * from sz2;
insert into nevsor2 values('Robert Gida');
insert into sz2 values('Micimackó','málna');
insert into sz2 values('Micimackó',null);

-- sila táblájába
insert into hnevsor values('Zelefánt');

-- almatszeretok-be
select * from almatszeretok;
insert into almatszeretok values('Zelefánt','banán');
select * from almatszeretok;
select * from sz;

-- tábla és nézet készítés
create table szamok (n number primary key);
select * from szamok;

create view szamok_nezet as select * from szamok;
select * from szamok_nezet;

-- bevitel
insert into szamok_nezet values(1);
insert into szamok_nezet values(2);
select * from szamok_nezet;
select * from szamok;
insert into szamok_nezet values(2);
select * from szamok_nezet;

-- név, foglalkozás emp-bõl
select * from emp;
create view munkas as select ename,job from emp;
select * from munkas;

-- bevitel
insert into munkas values('Valaki','valamit');
-- miert nem
desc emp
-- jó nézet létrehozása
create view munkas2 as select empno,deptno,ename,job from emp;
select * from munkas2;

-- bevitel
insert into munkas2 values(1,2,'Valaki','valamit');
insert into munkas2 values(1,2,null,null);
select * from emp;

-- munkas számolt fizetés mezõ
create OR REPLACE FORCE VIEW OSSZFIZ AS 
select empno,deptno,ename név,job foglalkozás,sal+nvl(comm,0) fizetés,sal,comm from emp;
select * from osszfiz;
-- bevitel
insert into osszfiz values(5,4,'a','b',1500,500,1000);

-- distinct
create view nevsor3 as select distinct n from sz;
select * from nevsor3;
-- bevitel
insert into nevsor3 values('Kutya');


-- összekapcsolás
create view munkas3 as select * from  emp natural join dept;
select * from munkas3;
--tábla attribútumai:    DEPTNO,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DNAME,LOC    
insert into munkas3 values(99, 1000, 'X', 'Y', null, null, null, null, null, null);

-- nézetek lekérdezése
select * from user_views;


-- group BY

-- allekérdezés



-- rollback;
-- commit;
-- savepoint 


-- 

select max(sal) from emp;
select count(sal),count(job),count(mgr),count(*) from emp;
select max(job) from emp;
select min(job) from emp;
select max(hiredate) from emp;
select min(hiredate) from emp;

-- nézetek,táblák törlése, készítése
drop view nevsor;
drop view nevsor2;
drop view nevsor3;
drop view hnevsor;
drop view almatszeretok;
drop table szamok;
drop view szamok_nezet;
drop view OSSZFIZ;
drop view munkas;
drop view munkas2;
drop view munkas3;
drop table sz2;
create table sz2 (n varchar2(20),GY VARCHAR2(20) DEFAULT 'BANÁN');
DROP TABLE sz;
CREATE TABLE sz
    (n          VARCHAR2(15),
     gy         VARCHAR2(15));

INSERT INTO sz VALUES ('Füles','málna');
INSERT INTO sz VALUES ('Füles','körte');
INSERT INTO sz VALUES ('Füles','alma');
INSERT INTO sz VALUES ('Micimackó','málna');
INSERT INTO sz VALUES ('Micimackó','körte');
INSERT INTO sz VALUES ('Micimackó','dió');
INSERT INTO sz VALUES ('Kanga','körte');
INSERT INTO sz VALUES ('Nyuszi','eper');
INSERT INTO sz VALUES ('Malacka','körte');
INSERT INTO sz VALUES ('Malacka','alma');
INSERT INTO sz VALUES ('Malacka','eper');
INSERT INTO sz VALUES ('Malacka','málna');
INSERT INTO sz VALUES ('Malacka','dió');
INSERT INTO sz VALUES ('Tigris','körte');
INSERT INTO sz VALUES ('Tigris','málna');

ALTER SESSION SET NLS_DATE_LANGUAGE = ENGLISH;
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MON-YYYY';
drop table emp;
CREATE TABLE emp
    (empno               NUMBER(4) PRIMARY key,
     ename               VARCHAR2(10),
     job                 VARCHAR2(9),
     mgr                 NUMBER(4) ,
     hiredate            DATE,
     sal                 NUMBER(7,2),
     comm                NUMBER(7,2),
     deptno              NUMBER(2) NOT NULL);

INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);
INSERT INTO emp VALUES (8000,'PROBA','DOLGOZO',NULL,'27-NOV-1980',6000,NULL,50);

ALTER SESSION SET NLS_DATE_LANGUAGE = HUNGARIAN;
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MON-DD';


delete sz2;
drop table sszeret;
drop view nevek;
drop view nevek2;
drop view almatszeretok;
drop view fizetesek;
drop view fizetesmunkahely;
drop view kereset;
drop view nevmunkahely;