How to Use SQL for Create Table By Select

How to Use SQL for Create Table By Select by use below sql.

Create table <<<Table Name>>>
TABLESPACE HRAPP STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
as
(select * from <<<Table Name>>>
)

Example 1 :

create table course_backup
TABLESPACE HRAPP
as
select * from course;

Example 2 :

Create table ps_jl_wo_tbl_2020
TABLESPACE HRAPP STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
as
(select * from ps_jl_wo_tbl )

PL SQL Select Update

This is PL SQL Select Update

declare
cursor c1 is

select course_no,course_name,faculty
from coursemain;

rec c1%rowtype;
begin

for rec in c1 loop

update coursemain
Set course_name = rec.course_name,
faculty_no = rec.faculty
where course_no = rec.course_no;

end loop;

end;

Select Insert Long Character PL SQL

DECLARE

CURSOR c IS

SELECT GRP_LVL, DATE, SCORE_FLAG, DESCRLONG
FROM grp_lvl_c1;

rc c%ROWTYPE;
BEGIN

OPEN c;
LOOP

FETCH c INTO rc;
EXIT WHEN c%NOTFOUND;
INSERT INTO grp_lvl_c1
( GRP_LVL, DATE, SCORE_FLAG, DESCRLONG   )

VALUES

( rc.GRP_LVL, rc.DATE, rc.SCORE_FLAG, rc.DESCRLONG);
END LOOP;
COMMIT;
END;

SQL Select Age

SELECT trunc(months_between(sysdate,A.EFFDT)/12) “NumOfYear”
, decode( sign(trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT)))), -1, trunc(mod(months_between(sysdate,A.EFFDT),12))-1
, trunc(mod(months_between(sysdate,A.EFFDT),12))) “NumOfMonth”

, decode( sign(trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT)))) , -1, trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT))-1)
, trunc(sysdate) – add_months(A.EFFDT, trunc(months_between(sysdate,A.EFFDT)))
) as “NumOfDay”
FROM PS_JOB A

Analyze Table/Index SQL

Syntax SQL Analyze Table

analyze table <<table_name>> compute statistics;

Example :
analyze table course compute statistics;

Syntax SQL Analyze Index

analyze index <<table_name>> compute statistics;

Example :
analyze index course compute statistics;

Update Select SQL

Syntax SQL : Update Table a

set(a.column1, a.column2,a…) = (select column1, column2,… from table b where b.key = a.key)

where exists (select ‘X’ from table b where b.key = a.key)

Example1 : Update Select

Update course a set (a.course,a.start_date) = (select b.course , b.start_date from course  b where b.course=a.course and b.course like ‘A%’)

where exists  (select ‘X’ where b.course=a.course and b.course like ‘A%’)

Example 2 : Update Select

UPDATE PS_TEST_TABLE a SET (a.FLAG1 ,a.DT_TM1, a.DT_TM2) =

( SELECT ‘P’,SYSDATE,SYSDATE FROM PS_TEST_TABLE b
WHERE b.FLAG2 =’Y’ AND b.SUBMIT_FLAG <>’S’
AND b.EMPLID = a.emplid AND b.seqnum = a.seqnum AND b.YEAR =a.YEAR AND b.YEAR = ‘2017’
)

WHERE exists

( SELECT ‘X’ FROM PS_TEST_TABLE b
WHERE b.FLAG2 =’Y’ AND b.SUBMIT_FLAG <>’S’
AND b.EMPLID = a.emplid AND b.seqnum = a.seqnum AND b.YEAR =a.YEAR AND b.YEAR = ‘2017’
)