Oracle 11g: Creating A Table By Using SubQuery

===>  SQL Queries

create table dept80
as
  select employee_id,
         last_name,
         salary*12 ANNSAL,
         hire_date
  from employees
  where department_id = 80;
 
select * from dept80; 

Results:
create table succeeded.
EMPLOYEE_ID   LAST_NAME   ANNSAL          HIRE_DATE                
---------------------- ------------------------- ---------------
145                    Russell                   168000                 01-OCT-96                
146                    Partners                  162000                 05-JAN-97                
147                    Errazuriz                 144000                 10-MAR-97                
148                    Cambrault                 132000                 15-OCT-99                
149                    Zlotkey                   126000                 29-JAN-00                
150                    Tucker                    120000                 30-JAN-97                
151                    Bernstein                 114000                 24-MAR-97                
152                    Hall                      108000                 20-AUG-97                
153                    Olsen                     96000                  30-MAR-98                
154                    Cambrault                 90000                  09-DEC-98                
155                    Tuvault                   84000                  23-NOV-99                
156                    King                      120000                 30-JAN-96                
157                    Sully                     114000                 04-MAR-96                
158                    McEwen                    108000                 01-AUG-96                
159                    Smith                     96000                  10-MAR-97                
160                    Doran                     90000                  15-DEC-97                
161                    Sewall                    84000                  03-NOV-98                
162                    Vishney                   126000                 11-NOV-97                
163                    Greene                    114000                 19-MAR-99                
164                    Marvins                   86400                  24-JAN-00                
165                    Lee                       81600                  23-FEB-00                
166                    Ande                      76800                  24-MAR-00                
167                    Banda                     74400                  21-APR-00                
168                    Ozer                      138000                 11-MAR-97                
169                    Bloom                     120000                 23-MAR-98                
170                    Fox                       115200                 24-JAN-98                
171                    Smith                     88800                  23-FEB-99                
172                    Bates                     87600                  24-MAR-99                
173                    Kumar                     73200                  21-APR-00                
174                    Abel                      132000                 11-MAY-96                
175                    Hutton                    105600                 19-MAR-97                
176                    Taylor                    103200                 24-MAR-98                
177                    Livingston                100800                 23-APR-98                
179                    Johnson                   74400                  04-JAN-00                

34 rows selected

Oracle 11g: Creating Constraints

Types Of Constraints

• not null
• primary key
• foreign key
• check
• unique

===>  SQL Queries: Column Level

create table employee_column_level
(
  employee_id number(6) CONSTRAINT emp_emp_level_id_pk primary key,
  first_name varchar2(20)
);







create table employee_table_level
(
  employee_id number(6),
  first_name varchar2(20),
  CONSTRAINT emp_emp_table_id_pk primary key (employee_id)
);

Oracle 11g: Create Tables

Naming Rules

• MUST begin with a Character
• Length : 1-30 characters long
• Can only contains:  A-Z, a-z, $,_,#,0-9
• CANNOT be duplicate
• CANNOT be oracle reserved words.

Must Have

• CREATE TABLE privilege
• a storage area.

If referring to other User's table, please use Users as prefix before table name: 
Select * from userA.employees

===>  SQL Queries:  Creating New Table & Use DEFAULT
create table dept
(
  deptno number(2),
  dname varchar2(14),
  loc  varchar2(13),
  create_date DATE default sysdate
);

describe dept;

Results

create table succeeded.

describe dept;
Name                           Null     Type                                                   
------------------------------ -------- ---------------
DEPTNO                                NUMBER(2)               
DNAME                                 VARCHAR2(14)          
LOC                                       VARCHAR2(13)                                                      
CREATE_DATE                    DATE