Oracle Learning Notes (v) _oracle

Combined Set Totals:
Group BY with Rollup/cube
Grouping sets
Sub-query by execution: standard subquery, associated subquery
Standard subquery: Nested subqueries
Scalar value Query
Case with Then
Using Exits queries
Select User_id,user_name from tb_001 TB
where [not] exists (SELECT ' x '
From tb_001
where manager_id=tb.user_id)
Associating updates
Update table1 ALIAS1
Set column= (SELECT expression
From Table2 ALIAS2
where Alias1.column=alias2.column);
Association Delete
Delete from table1 ALIAS11
where column operator
(SELECT expression
From Table2 ALIAS2
where Alias1.column=alias2.column);
Layered structure
Hierarchical search
SELECT [Level],column,expr ...
From table
[where with condition (s)]
[Connect by prior condition (s)]
Connect by Prior Column1=column2
Top down:
Colum1=parentkey
Column2=childkey
Bottom up:
Column1=childkey
Column2=parentkey

INSERT statement INSERT statement
Insert INTO table[(Column[,column ...])]
VALUES (Value[,value ...])
Modify Statement UPDATE statement
Update table
Set Column=value[,column=value,...]
[Where condition];

Multiple table INSERT statement (multitable INSERT statements)

Insert [All] [Conditional_insert_clause]
[Insert_into_cause Values_clause] (subquery)
Conditional_insert_clause
[All] [A]
[When condition then] [Insert_into_clause Values_clause]
[Else] [Insert_into+clause Values_clause]
Column:
Intsert All
into Tb_user values (ID,UID,UPWD)
into tb_user_info values (id,name,sex,age)
Select Id,uid,upwd,name,sex,age
From Tb_u_10
where id>10000;
Multiple-line inserts to be conditional
Insert All
When id>11000 Then
into Tb_user values (ID,UID,UPWD)
When Age>40 Then
into tb_user_info values (id,name,sex,age)
Select Id,uid,upwd,name,sex,age
From Tb_u_10
where id>10000;
Insert a
When conditional then
Into ...
When conditional then
Into ...
Else
Into ...
Select ...
Create an index
Cretate Table T_10
(ID number (6) isn't null primary key using index (CREATE index index1 on t_10 (ID)),
Name VARCHAR2 (50));
Query index
SELECT * from User_indexes;