http://127.0.0.1:8080/apex
#### mi loggo come utente normale:

nikoz@debian:~$ sqlplus davido/password@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Mer Mar 12 20:15:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connesso a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

#### divento SYSDBA:

SQL>  connect SYS@XE AS SYSDBA
Immettere la password:
Connesso.
SQL> select name from v$database;

NAME
---------
XE

SQL> quit
Disconnesso da Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

#### per settare le preferenze di sqlplus creo un file nella home login.sql
#### more login.sql
prompt Loading login.sql file...
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER SQL>"
define _editor=vim

#### oppure
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/scripts/sqlplus.sh
#http://www.oracle-base.com/articles/10g/DatabaseSecurityEnhancements10g.php
#http://www.java2s.com/Code/Oracle/Table/Copyingselectedcolumnsfrommultipletables.htm
#http://www.techonthenet.com/sql/insert.php
#http://database.html.it/guide/lezione/3081/operatori-logici-e-di-paragone/
#http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html#date%20format
#http://en.allexperts.com/q/Oracle-1451/insert-date-value-oracle.htm


CREATE table "EMP" (
    "EMPNO"      NUMBER(4),
    "ENAME"      VARCHAR2(30),
    "JOB"        CHAR(10),
    "MGR"        NUMBER,
    "HIREDATE"   DATE,
    "SAL"        NUMBER(7,2),
    "DEPTNO"     NUMBER(2)
)
/
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, to_date('23/05/1998', 'dd/mm/yyyy') , 800, 20);
delete from EMP where empno = 7369 and hiredate = '23-mag-98';

#rispettto al tutorial in inglese (mesi in Inglese es. DEC) se oracle in italiano vuole i mesi in italiano es. DIC
INSERT ALL
  INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DiC-1980', 'dd-mon-yyyy'), 800, 20)
  INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'dd-mon-yyyy'), 1600, 30)
  INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 30)
  INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, 20)
  INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEt-1981', 'DD-MON-YYYY'), 1250, 30)
  INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAg-1981', 'DD-MON-YYYY'), 2850, 30)
  INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-giu-1981', 'DD-MON-YYYY'), 2450, 10)
  INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DiC-1982', 'DD-MON-YYYY'), 3000, 20)
  INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, 10)
  INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEt-1981', 'DD-MON-YYYY'), 1500, 30)
  INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-geN-1983', 'DD-MON-YYYY'), 1100, 20)
  INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DiC-1981', 'DD-MON-YYYY'), 950, 30)
  INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DiC-1981', 'DD-MON-YYYY'), 3000, 20)
  INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-geN-1982', 'DD-MON-YYYY'), 1300, 10)
SELECT * FROM dual;

CREATE table "DEPT" (
    "DEPTNO"	NUMBER(3),
    "DNAME"	CHAR(10),
    "LOC"	CHAR(15)
)
/
INSERT INTO "DEPT" VALUES(10, 'STORE','CHICAGO');
INSERT INTO "DEPT" VALUES(20, 'RESEARCH','DALLAS');
INSERT INTO "DEPT" VALUES(30, 'SALES','NEW YORK');
INSERT INTO "DEPT" VALUES(40, 'MARKETING','BOSTON');
/
CREATE table "SALGRADE" (
    "GRADE"	NUMBER(3),
    "LOSAL"	NUMBER(5),
    "HISAL"	NUMBER(5)
)
/
#inserimento riga per riga
INSERT INTO "SALGRADE" VALUES(1,700,1200);
INSERT INTO "SALGRADE" VALUES(2,1201,1400);
INSERT INTO "SALGRADE" VALUES(3,1401,2000);
INSERT INTO "SALGRADE" VALUES(4,2001,3000);
INSERT INTO "SALGRADE" VALUES(5,3001,4000);
#svotamento tabella
TRUNCATE table "SALGRADE";
#inserimento multi records
INSERT ALL
   INTO SALGRADE VALUES (1,700,1200)
   INTO SALGRADE VALUES (2,1201,1400)
   INTO SALGRADE VALUES (3,1401,2000)
   INTO SALGRADE VALUES (4,2001,3000)
   INTO SALGRADE VALUES (5,3001,4000)
SELECT * FROM dual;

select emp.ename, emp.job, dept.dname, dept.loc from emp, dept
where emp.deptno=dept.deptno;

select ename, deptno, sal *1.55 from emp;
select ename, deptno, hiredate from emp order by deptno, hiredate;
select job, sal from emp where (mgr = 7698 or mgr = 7566) and sal > 1500;
select job, sal from emp where deptno in (20,30);
select job, sal from emp where mgr is null;
select job, sal from emp where job LIKE 'C%';

create table project (
pno number(3) constraint prj_pk primary key,
pname varchar2(60) unique,
pmgr number(4) not null,
persons number(5),
budget number(8,2) not null,
pstart date,
pend date );

insert all
 into project values(1,'progetto 1', 7782, 3, 10000,'10-gen-2008','12-feb-2008')
 into project values(2,'progetto 2', 7698, 4, 20000,'20-gen-2008','18-feb-2008')
 into project values(3,'progetto 3', 7782, 2, 12000,'10-mar-2008','12-mag-2008')
 into project values(4,'progetto 4', 7782, 5, 34000,'30-gen-2008','12-feb-2008')
 into project values(5,'progetto 5', 7698, 2, 11000,'20-mar-2008','12-giu-2008')
select * from dual;

####join

select ename, dname, pname
from emp e, dept d, project p
where e.empno = p.pmgr
and d.deptno = e.deptno;

#join sulla stessa tabella
select e1.ename as submissed, e2.ename as manager
from emp e1, emp e2
where e1.mgr = e2.empno;

###############################
#subquery
select *
 from emp, project 
where emp.empno = project.pmgr
and emp.deptno = 30
and project.pend <'01-mag-08';

select ename, sal from emp
where empno in
   ( select pmgr
     from project
     where pend <'01-mag-08' )
and deptno =10;


select emp.ename, emp.sal 
 from emp, project
where emp.empno = project.pmgr
and emp.deptno = 30
and project.pend <'01-mag-08';

###############################
select * 
from dept, emp
where emp.deptno = dept.deptno
and loc = 'DALLAS'


select * from emp
where deptno in 
( select deptno from dept
where loc = 'DALLAS');

###############################
elect ename 
from emp 
where deptno in  
(select deptno 
 from emp 
 where job='MANAGER')
and job !='MANAGER'

select ename 
from emp e1
where deptno in  
(select deptno 
 from emp e
 where e.empno = e1.mgr);
###############################
#### any
##seleziono gli impegati del deptno 10 che hanno uno stipendio >= agli stipendi del deptno 30
select * from emp
where sal >= any 
(select sal from emp 
where deptno=30)
and deptno = 10;
#### all
##seleziono gli impegati dei deptno diversi da 30 che hanno uno stipendio >= allo stipendio + alto del deptno 30
select * from emp
where sal >all  
(select sal from emp
where deptno = 30)
and deptno != 30;

###############################
##seleziono i dipartimenti che non hanno impegati 
select * from dept
where not exists 
(select * from emp
where deptno = dept.deptno);

###############################
create table project2 (
pno number(3) constraint prj_pk primary key,
pname varchar2(60) unique,
pmgr number(4) not null,
persons number(5),
budget number(8,2) not null,
pstart date,
pend date );

insert all
 into project2 values(21,'progetto 1', 7782, 3, 10000,'10-gen-2008','12-feb-2008')
 into project2 values(22,'progetto 2', 7698, 4, 20000,'20-gen-2008','18-feb-2008')
 into project2 values(3,'progetto 3', 7782, 2, 12000,'10-mar-2008','12-mag-2008')
 into project2 values(24,'progetto 4', 7782, 5, 34000,'30-gen-2008','12-feb-2008')
 into project2 values(5,'progetto 5', 7698, 2, 11000,'20-mar-2008','12-giu-2008')
select * from dual;

###############################
# union
#mi mostra tutti i record tranne i duplicati in comune
select pno, pname from project
union
select pno, pname from project2

###############################
# intersect
#mi mostra solo i record in comune
select pno, pname from project
intersect
select pno, pname from project2

###############################
# minus
#mi mostra tutti i record della prima query meno quelli in comune (nessun'altro della seconda query)
#importante l'ordine dell query
select pno, pname from project
minus
select pno, pname from project2

###############################
# update
update project2 set pname='Progetto 221' where pno=21

###############################
# grouping
#seleziona il salario max e min per reparto
select deptno, max(sal), min(sal)
from emp
group by deptno;
#seleziona il salario max e min per reparto dove il job = clerk e >2 clerk
select deptno, max(sal), min(sal)
from emp
where job='CLERK'
group by deptno
having count(*)>2;
#group by logic
#1 seleziona tutte le righe che corrispondono al 'where'
#2 raggruppa i records in base al 'group by'
#3 rimuove i gruppi che non soddisfano 'having'
#4 applica le funzioni di aggregazione
#5 visualizza i valori selezionati nella 'select'

#creazione indice
CREATE UNIQUE INDEX  "DEPT_IDX1" ON  "DEPT" ("DEPTNO")
#creazione chiave primaria
alter table "DEPT" add constraint
"DEPT_PK" primary key ("DEPTNO")
######################################################################@#####################################################
###  user bubu
#########################################################################################################################
create table DEPT (
	DEPTNO	number(4) constraint dep_pk primary key,
	DNAME	varchar2(60), 
	LOC 	varchar2(60)
);

insert all
into DEPT VALUES(10,'ACCOUNTING', 'NEW YORK') 
into DEPT VALUES(20,'RESEARCH', 'DALLAS') 
into DEPT VALUES(30,'SALES', 'CHICAGO') 
into DEPT VALUES(40,'OPERATIONS', 'BOSTON')
select * from dual; 

create table EMP (
	EMPNO		number(5) constraint emp_pk primary key, 
	ENAME		varchar2(40),
	JOB 		varchar2(20),
	MGR		number(5),
	HIREDATE 	date,
	SAL 		number(8),
	COMM 		number(6),
	DEPTNO		number(4)
) ;
insert ALL
into EMP VALUES( 7369 , 'SMITH' , 'CLERK',7902,'17-DIC-80',800,null,20)
into EMP VALUES( 7499 , 'ALLEN' , 'SALESMAN',7698,'20-FEB-81',1600,300,30)
into EMP VALUES( 7521 , 'WARD' , 'SALESMAN',7698,'22-FEB-81',1250,500,30)
into EMP VALUES( 7566 , 'JONES' , 'MANAGER',7839,'02-APR-81',2975,null,20)
into EMP VALUES( 7654 , 'MARTIN' , 'SALESMAN',7698,'28-SET-81',1250,1400,30)
into EMP VALUES( 7698 , 'BLAKE' , 'MANAGER',7839,'01-MAG-81',2850,null,30)
into EMP VALUES( 7782 , 'CLARK' , 'MANAGER',7839,'09-GIU-81',2450,null,10)
into EMP VALUES( 7788 , 'SCOTT' , 'ANALYST',7566,'09-DIC-81',3000,null,20)
into EMP VALUES( 7839 , 'KING' , 'PRESIDENT',null,'17-NOV-81',5000,null,10)
into EMP VALUES( 7844 , 'TURNER','SALESMAN',7698,'08-SET-81',1500,0,30)
into EMP VALUES( 7876 , 'ADAMS','CLERK',7788,'12-GEN-81',1100,null,20)
into EMP VALUES( 7900 , 'JAMES','CLERK',7698,'03-DIC-81',950,null,30)
into EMP VALUES( 7902 , 'FORD','ANALYST',7566,'03-DIC-81',3000,null,20)
into EMP VALUES( 7934 , 'MILLER','CLERK',7782,'23-GEN-81',1300,null,10)
select * from dual;

create table PROJECTS (
        PROJNO  number(6) constraint prj_pk primary key,
        PNAME   varchar2(40)
);

insert all
into PROJECTS VALUES(1,'ORACLE DEVELOPMENT') 
into PROJECTS VALUES(2,'DOCUMENTATION') 
into PROJECTS VALUES(3,'CUSTOMER TRAINING') 
into PROJECTS VALUES(10,'TESTING') 
into PROJECTS VALUES(20,'INSTALLATION') 
into PROJECTS VALUES(30,'QUALITY CONTROL') 
select * from dual;

create table ASSIGNEMENT (
	EMPNO	number(5),
	PROJNO 	number(6),
	STARTDATE date
)
insert all
into ASSIGNEMENT VALUES( 7369, 1,'17-DIC-80') 
into ASSIGNEMENT VALUES( 7369, 2,'01-GEN-81') 
into ASSIGNEMENT VALUES( 7499, 1,'20-FEB-81') 
into ASSIGNEMENT VALUES( 7521, 10,'22-FEB-81')  
into ASSIGNEMENT VALUES( 7521, 20,'15-MAR-81')  
into ASSIGNEMENT VALUES( 7521, 30,'15-MAR-81')  
into ASSIGNEMENT VALUES( 7566, 1,'02-APR-81')  
into ASSIGNEMENT VALUES( 7654, 2,'01-OTT-81')  
into ASSIGNEMENT VALUES( 7698, 10,'01-MAG-81')  
into ASSIGNEMENT VALUES( 7782, 20,'10-GIU-81')  
into ASSIGNEMENT VALUES( 7788, 10,'09-NOV-81')  
into ASSIGNEMENT VALUES( 7839, 2,'20-NOV-81')  
into ASSIGNEMENT VALUES( 7844, 3,'12-SET-81')  
into ASSIGNEMENT VALUES( 7876, 10,'23-SET-81')  
into ASSIGNEMENT VALUES( 7900, 20,'04-DIC-81')  
into ASSIGNEMENT VALUES( 7902, 1,'04-DIC-81')  
into ASSIGNEMENT VALUES( 7934, 1,'10-GEN-81')  
into ASSIGNEMENT VALUES( 7934, 2,'10-GEN-81')  
into ASSIGNEMENT VALUES( 7934, 3,'22-APR-81')  
into ASSIGNEMENT VALUES( 7934, 10,'23-GEN-81') 
select * from dual;

#select : equi-join
select  emp.deptno, dname, loc, ename, job
from emp,dept
where emp.deptno = dept.deptno
and job='CLERK'
order by emp.deptno;

#self-join
select i.ename NOME_IMP, m.ename NOME_MGR
from emp i, emp m
where i.mgr = m.empno;
#outer-join
select dept.deptno, dname, ename
from dept, emp
where dept.deptno =emp.deptno(+);

select * 
from  emp, assignement, projects
where assignement.empno = emp.empno
and projects.projno = assignement.projno

select ename NOME, sal STIPENDIO, comm, sal+NVL(comm,0) "COMPNSO TOTALE"
from emp
where deptno = 30 and SAL > 800
order by STIPENDIO