Oracle Live SQL - Script: Session 01

Statement 1

create table employees as 
  select * from hr.employees 
  where  department_id in ( 90, 100, 60 )

Table created.

Statement 2

select * from employees

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
103	Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60
104	Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60
100	Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24000	-	-	90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
102	Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
108	Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	12008	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
110	John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100
113	Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT	6900	-	108	100

14 rows selected.

Statement 3

select * from employees 
start with manager_id is null 
connect by prior employee_id = manager_id

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
100	Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24000	-	-	90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
108	Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	12008	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
110	John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100
113	Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT	6900	-	108	100
102	Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
103	Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60
104	Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60

14 rows selected.

Statement 4

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id is 107 
connect by next manager_id = employee_id

ORA-00908: missing NULL keyword

Statement 5

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id is null 
connect by next manager_id = employee_id

ORA-00920: invalid relational operator

Statement 6

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id is 107 
connect by next manager_id = employee_id

ORA-00908: missing NULL keyword

Statement 7

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id is null 
connect by next manager_id = employee_id

ORA-00920: invalid relational operator

Statement 8

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by /*TODO*/

ORA-01742: comment not terminated properly

Statement 9

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by prior employee_id = manager_id

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
107	Diana	Lorentz	103

Statement 10

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by next employee_id = manager_id

ORA-00920: invalid relational operator

Statement 11

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by next  manager_id = employee_id

ORA-00920: invalid relational operator

Statement 12

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by next manager_id = employee_id

ORA-00920: invalid relational operator

Statement 13

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by prior manager_id = employee_id

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
107	Diana	Lorentz	103
103	Alexander	Hunold	102
102	Lex	De Haan	100
100	Steven	King	-

4 rows selected.

Statement 14

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by prior  employee_id = manager_id

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
107	Diana	Lorentz	103

Statement 15

select employee_id, first_name, last_name, manager_id 
from  employees 
start with employee_id = 107 
connect by prior  manager_id = employee_id

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
107	Diana	Lorentz	103
103	Alexander	Hunold	102
102	Lex	De Haan	100
100	Steven	King	-

4 rows selected.

Statement 16

  select e.employee_id, e.first_name, e.last_name, e.manager_id

ORA-00923: FROM keyword not found where expected

Statement 17

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is null 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
100	Steven	King	-
101	Neena	Kochhar	100
102	Lex	De Haan	100
108	Nancy	Greenberg	101
103	Alexander	Hunold	102
109	Daniel	Faviet	108
110	John	Chen	108
111	Ismael	Sciarra	108
112	Jose Manuel	Urman	108
113	Luis	Popp	108
104	Bruce	Ernst	103
105	David	Austin	103
106	Valli	Pataballa	103
107	Diana	Lorentz	103

14 rows selected.

Statement 18

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  employee_id = 107 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.employee_id = oc.manager_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
107	Diana	Lorentz	103
103	Alexander	Hunold	102
102	Lex	De Haan	100
100	Steven	King	-

4 rows selected.

Statement 19

select level, employee_id, first_name, last_name, manager_id  
from   employees 
start  with manager_id is null 
connect by prior employee_id = manager_id

LEVEL	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
1	100	Steven	King	-
2	101	Neena	Kochhar	100
3	108	Nancy	Greenberg	101
4	109	Daniel	Faviet	108
4	110	John	Chen	108
4	111	Ismael	Sciarra	108
4	112	Jose Manuel	Urman	108
4	113	Luis	Popp	108
2	102	Lex	De Haan	100
3	103	Alexander	Hunold	102
4	104	Bruce	Ernst	103
4	105	David	Austin	103
4	106	Valli	Pataballa	103
4	107	Diana	Lorentz	103

14 rows selected.

Statement 20

select level, employee_id,  
       lpad ( ' ', level, ' ' ) || first_name || ' ' || last_name name, manager_id  
from   employees 
start  with manager_id is null 
connect by prior employee_id = manager_id

LEVEL	EMPLOYEE_ID	NAME	MANAGER_ID
1	100	Steven King	-
2	101	Neena Kochhar	100
3	108	Nancy Greenberg	101
4	109	Daniel Faviet	108
4	110	John Chen	108
4	111	Ismael Sciarra	108
4	112	Jose Manuel Urman	108
4	113	Luis Popp	108
2	102	Lex De Haan	100
3	103	Alexander Hunold	102
4	104	Bruce Ernst	103
4	105	David Austin	103
4	106	Valli Pataballa	103
4	107	Diana Lorentz	103

14 rows selected.

Statement 21

with org_chart ( 
  employee_id, first_name, last_name, manager_id, lvl 
) as ( 
  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id, oc.lvl + 1 
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1
101	Neena	Kochhar	100	2
102	Lex	De Haan	100	2
108	Nancy	Greenberg	101	3
103	Alexander	Hunold	102	3
109	Daniel	Faviet	108	4
110	John	Chen	108	4
111	Ismael	Sciarra	108	4
112	Jose Manuel	Urman	108	4
113	Luis	Popp	108	4
104	Bruce	Ernst	103	4
105	David	Austin	103	4
106	Valli	Pataballa	103	4
107	Diana	Lorentz	103	4

14 rows selected.

Statement 22

with org_chart ( 
  employee_id, first_name, last_name, manager_id, lvl 
) as ( 
  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null 
  union 
  select e.employee_id, e.first_name, e.last_name, e.manager_id, oc.lvl + 1 
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

ORA-32040: recursive WITH clause must use a UNION ALL operation

Statement 23

with org_chart ( 
  employee_id, first_name, last_name, manager_id, lvl 
) as ( 
  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null 
  union all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id, oc.lvl + 1 
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1
101	Neena	Kochhar	100	2
102	Lex	De Haan	100	2
108	Nancy	Greenberg	101	3
103	Alexander	Hunold	102	3
109	Daniel	Faviet	108	4
110	John	Chen	108	4
111	Ismael	Sciarra	108	4
112	Jose Manuel	Urman	108	4
113	Luis	Popp	108	4
104	Bruce	Ernst	103	4
105	David	Austin	103	4
106	Valli	Pataballa	103	4
107	Diana	Lorentz	103	4

14 rows selected.

Statement 24

with org_chart ( 
  employee_id, first_name, last_name, manager_id, lvl 
) as ( 
  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null 
  union all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id, oc.lvl + 1 
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
)

ORA-00928: missing SELECT keyword

Statement 25

with org_chart ( 
  employee_id, first_name, last_name, manager_id, lvl 
) as ( 
  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null 
  union all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id, oc.lvl + 1 
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  /*select * from org_chart;*/

ORA-01742: comment not terminated properly

Statement 26

with org_chart ( 
  employee_id, first_name, last_name, manager_id, lvl 
) as ( 
  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null 
  union all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id, oc.lvl + 1 
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1
101	Neena	Kochhar	100	2
102	Lex	De Haan	100	2
108	Nancy	Greenberg	101	3
103	Alexander	Hunold	102	3
109	Daniel	Faviet	108	4
110	John	Chen	108	4
111	Ismael	Sciarra	108	4
112	Jose Manuel	Urman	108	4
113	Luis	Popp	108	4
104	Bruce	Ernst	103	4
105	David	Austin	103	4
106	Valli	Pataballa	103	4
107	Diana	Lorentz	103	4

14 rows selected.

Statement 27

  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1

Statement 28

  select 100, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null

100	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1

Statement 29

  select 101, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null

101	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
101	Steven	King	-	1

Statement 30

  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null and employee_id = 100

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1

Statement 31

  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null and employee_id = 101

no data found

Statement 32

  select employee_id, first_name, last_name, manager_id, 1 lvl 
  from   employees 
  where  manager_id is null

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL
100	Steven	King	-	1

Statement 33

  select employee_id, manager_id 
  from   employees 
  where  manager_id is null

EMPLOYEE_ID	MANAGER_ID
100	-

Statement 34

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id = 103 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
104	Bruce	Ernst	103
105	David	Austin	103
106	Valli	Pataballa	103
107	Diana	Lorentz	103

4 rows selected.

Statement 35

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is 103 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

ORA-00908: missing NULL keyword

Statement 36

 = 103

Invalid statement

Statement 37

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is 103 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

ORA-00908: missing NULL keyword

Statement 38

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is null 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
100	Steven	King	-
101	Neena	Kochhar	100
102	Lex	De Haan	100
108	Nancy	Greenberg	101
103	Alexander	Hunold	102
109	Daniel	Faviet	108
110	John	Chen	108
111	Ismael	Sciarra	108
112	Jose Manuel	Urman	108
113	Luis	Popp	108
104	Bruce	Ernst	103
105	David	Austin	103
106	Valli	Pataballa	103
107	Diana	Lorentz	103

14 rows selected.

Statement 39

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is 100 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

ORA-00908: missing NULL keyword

Statement 40

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is '100' 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

ORA-00908: missing NULL keyword

Statement 41

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is null 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
100	Steven	King	-
101	Neena	Kochhar	100
102	Lex	De Haan	100
108	Nancy	Greenberg	101
103	Alexander	Hunold	102
109	Daniel	Faviet	108
110	John	Chen	108
111	Ismael	Sciarra	108
112	Jose Manuel	Urman	108
113	Luis	Popp	108
104	Bruce	Ernst	103
105	David	Austin	103
106	Valli	Pataballa	103
107	Diana	Lorentz	103

14 rows selected.

Statement 42

  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is null 
  union  all

ORA-00928: missing SELECT keyword

Statement 43

with org_chart ( 
  employee_id, first_name, last_name, manager_id 
) as ( 
  select employee_id, first_name, last_name, manager_id  
  from   employees 
  where  manager_id is null 
  union  all 
  select e.employee_id, e.first_name, e.last_name, e.manager_id  
  from   org_chart oc 
  join   employees e 
  on     e.manager_id = oc.employee_id 
) 
  select * from org_chart

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID
100	Steven	King	-
101	Neena	Kochhar	100
102	Lex	De Haan	100
108	Nancy	Greenberg	101
103	Alexander	Hunold	102
109	Daniel	Faviet	108
110	John	Chen	108
111	Ismael	Sciarra	108
112	Jose Manuel	Urman	108
113	Luis	Popp	108
104	Bruce	Ernst	103
105	David	Austin	103
106	Valli	Pataballa	103
107	Diana	Lorentz	103

14 rows selected.

Session 01

Additional Information