Table of Contents
hide
Create Table
Definition :
- This statement creates a new/fresh table with various column or field names and respective datatypes in a database.
Syntax :
- CREATE TABLE schema_name.table_name (column_name1 data_type1 column_constraint1, column_name2 data_type2 column_constraint2, . . . table_constraints );
- CREATE TABLE table_name ( column_name1 data_type1 column_constraint1, column_name2 data_type2 column_constraint2, . . . );
Example :
In Oracle :
CREATE TABLE system.employee( emp_id VARCHAR2(20), emp_first_name VARCHAR2(50) NOT NULL, emp_last_name VARCHAR2(50) NOT NULL,emp_dob DATE NOT NULL,emp_mob NUMBER NOT NULL,emp_image BLOB NOT NULL,emp_remarks VARCHAR2(250) NULL, PRIMARY KEY(emp_id) );————————————————————————————————-
CREATE TABLE employee( emp_id VARCHAR2(20) PRIMARY KEY, emp_first_name VARCHAR2(50) NOT NULL, emp_last_name VARCHAR2(50) NOT NULL,emp_dob DATE NOT NULL,emp_mob NUMBER(12) NOT NULL,dept_mob NUMBER(12) DEFAULT 28416258,emp_image BLOB NOT NULL,emp_remarks VARCHAR2(250) NULL);————————————————————————————————-
Create Table As
Definition :
- This statement is used to create a new fresh table from one/more existing table’s data/records by copying the columns/fields of the existing table partially or fully as needed.
Syntax :
(i) CREATE TABLE new_table_name
AS (SELECT * FROM old_table_name);
(ii) CREATE TABLE new_table_name
AS (SELECT * FROM old_table_name where condition);
(iii) CREATE TABLE new_table_name
AS (SELECT column_name1, column_name2, column_name3, column_name4, column_name5 FROM old_table_name);
(iv) CREATE TABLE new_table_name
AS (SELECT column_name1, column_name2, column_name3, column_name4, column_name5 FROM old_table_name);
(v) CREATE TABLE new_table_name
AS (SELECT column_name1, column_name2, … column_name_n FROM old_table_name1, old_table_name2, … old_table_nameN);
Example :
(i) To display the structure details (metadata) of a table in SQL, including column names, types, and nullable constraints.
DESCRIBE employee;
(ii) To display the stored/added records or data of the created table in Oracle.
(a) To display all added or stored records
SELECT * FROM employee;
(b) To display selected/customized added or stored records
SELECT employee_id, first_name, salary FROM employee;
(iii) To create a table from an existing table
CREATE TABLE employeeB
AS (SELECT * FROM employeeA WHERE emp_id = “50B3”);
(iv) To create a table from an existing table
CREATE TABLE employeeB
AS (SELECT emp_id, emp_name, emp_addr, emp_dept, emp_sal FROM employeeA WHERE emp_id = “50B3”);
(v) To Remove/Delete existing Tables
DROP TABLE Employee; (Press Enter)
0 Comments