Create intermediate table for calculation : Create Table « Table « Oracle PL/SQL Tutorial

SQL> create table emp(
  2           emp_no                 integer         primary key,
  3           lastname               varchar2(20)    not null,
  4           firstname              varchar2(15)    not null,
  5           midinit                varchar2(1),
  6           street                 varchar2(30),
  7           city                   varchar2(20),
  8           state                  varchar2(2),
  9           zip                    varchar2(5),
 10           shortZipCode                   varchar2(4),
 11           area_code              varchar2(3),
 12           phone                  varchar2(8),
 13           salary                 number(5,2),
 14           birthdate              date,
 15           startDate              date,
 16           title                  varchar2(20),
 17           dept_no                integer,
 18           mgr                    integer,
 19           region                 number,
 20           division               number,
 21           total_sales            number
 22  );

Table created.

SQL> -- emp Table Inserts:
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
  2                      (1,'Z','Joy','R','1 Ave','New York','NY','12122','2333','212','200-1111','12-nov-1976','President');

1 row created.

SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (2,'X','Lucy','J','1 Street','New York','NY','43552','6633','212','234-4444',7.75,'21-mar-1976','1-feb-1994','Sales Manager',2,1,100,10,40000);

1 row created.

SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (3,'Y','Jordan','E','1 Drive','New York','NY','76822','8763','212','222-2222',7.75,'14-feb-1963','15-mar-1995','Sales Clerk',2,2,100,10,10000);

1 row created.

SQL>
SQL> create table avg_sal
  2  as select avg(salary) AS avg_Sal from emp;



SQL>
SQL>
SQL> select lastname, salary,
  2  CASE WHEN salary > avg_sal THEN '> Average of ' || to_char(avg_sal, '99.99')
  3       WHEN salary < avg_sal THEN '< Average of ' || to_char(avg_sal, '99.99')
  4       ELSE '= Average of ' || to_char(avg_sal, '99.99')
  5       END
  6  from emp, avg_sal
  7  /

6.1.Create Table
	6.1.1.	Creating a Table
	6.1.2.	Create table with storage setting
	6.1.3.	Create table with storage setting 2
	6.1.4.	Create an external table
	6.1.5.	Create copy table
	6.1.6.	Create a table from one or more other tables
	6.1.7.	Rename a table
	6.1.8.	One column with three constraints
	6.1.9.	Use referencing columns
	6.1.10.	Create a table based on a hash cluster
	6.1.11.	Create table with 'organization index OVERFLOW'
	6.1.12.	Create table with 'organization index'
	6.1.13.	Cascade delete setting
	6.1.14.	Demonstrate a simple External table
	6.1.15.	Create intermediate table for calculation
	6.1.16.	Make myCode a CACHE table.