Create Table in DBMS

CREATE TABLE

• Create command is used to create a table in the database with the structure specified by the user
• This structure includes the number of columns to be present in the table and the data type of the column, size of data, etc

Basic Syntax for CREATE

CREATE TABLE table_name
(
column1 datatype(size),
column2 datatype(size),
column3 datatype(size),
.....
columnN datatype(size),
PRIMARY KEY( one or more columns )
);

Example

create table emp( 
empno number(4,0), 
ename varchar2(10), 
job varchar2(9), 
mgr number(4,0), 
hiredate date, 
sal number(7,2), 
deptno number(2,0) 
PRIMARY KEY (ID)
);

• Table name:emp
• Column names: In the above table that we have created have 7 columns namely  empno, ename, job, mgr,hiredate, sal,deptno
• Data types: What type of data should be entered for each column value. for example, we have used number data type for the column empno , which means you must enter numerical values only while inserting data for emp column
• Size: Specifies the length of the value that is inserted, for example, we have used size 10  for ename as varchar2(10), which means the maximum number of characters that can be entered for the ename column is 10.

DESC command

• Describe  command shows the structure of the table that we have created
• It displays the column names, data types of column names, size of data , any constraints imposed the table, default values for each column, whether null values allowed or not for each column, etc

Example

desc emp;

O/P

Creating a new table from an existing table

• We can create a new table with exactly the same structure of any of the existing tables
• Here an empty table with exactly the same structure of the existing table is created but data and constraints of the table are not copied

Example

create table
sample as (select * from emp);

O/P

table sample crated successfully

Not both sample and emp tables will have the same structure i.e same number of columns, column names,  datatype, sizes, etc  .you can also cross-check by using desc command