Basic SQL: Create (Part 6)

Create statement is used to create database objects, and different objects have different syntax. Below are basic syntax for creating some common database objects:

1. Create Table:
   create table [table name]
(
[column name] [data type] [not null],
constraint [constraint name] [constraint type] [constraint detail]
)
   
   For example:
   create table emp
(
id number not null,
name varchar(50),
dept number,
salary number(9,2) not null,
constraint emp_pk primary key (id),
constraint emp_fk foreign key (dept) references dept (id),
constraint emp_uq unique (id),
constraint emp_chk check (salary >= 0)
);

   Tips to clone a table:
   create table emp_clone as
select * from emp;


2. Create View:
   create view [view name] as
[select statement]
   
   For example:
   create view emp_vu as
select *
from emp
where dept = 'ACCOUNT';

   
3. Create User:
   create user [user name]
identified by [password]
   
   For example:
   create user james
identified by secret;



4. Create Synonym:
   create [public] synonym [alias] for [database object]
   
   For example:
   create public synonym employee for emp;