Quick Enquiry Form

×

    EnquiryEnquire Now

    Quick Enquiry

      Create SQL Table

      create sql table
      Blog

      Create SQL Table

      Create SQL Table :

      SQL stands for Structured Query Language that allows us to access and manipulate databases. With SQL, we can run queries against databases, we can insert and update data in a database, can retrieve and delete from a database, can create new databases, tables, views, and procedures in a database, and can set permission on tables, views, and procedures. SQL is used to develop websites with a database application.

      To create SQL table database, the SQL CREATE TABLE statement will be used. It is important to name the newly created table and define its column along with its data type. Below is the simple syntax for creating the table.

      create table “tablename”

      (“column1” “data type”,

      “column2” “data type”,

      “column3” “data type”,

      ….

      “columnN” “datatype”);

      Here is an example for creating a table name “EMPLOYEE” with EMPID as the primary key. NOT NULL denotes that the fields must not be null while creating records in the table.

      SQL > CREATE TABLE EMPLOYEE (

      EMPID INT NOT NULL,

      EMPNAME VARCHAR(20) NOT NULL,

      EMPAGE INT NOT NULL,

      EMPADDRESS CHAR (25),

      PRIMARY KEY (EMPID) );

      Displaying the table in a table format

      Once the table has been created, you can verify and view the table by using the DESC command as follows

      SQL > DESC EMPLOYEE;

      Table
      FIELDTYPENULLKEYDEFAULTEXTRA
      EMPIDInt(11)NOPRI  
      EMPNAMEVarchar(20)NO   
      EMPAGEInt(11)NO   
      EMPADDRESSVarchar(25)YESNULL  

      4 rows in set (0.00 sec)

      Creating a Table in MySQL

      Following is the example to create a table in the MySQL database

      CREATE TABLE Employee (

      EmployeeID int,

      EmpName varchar (255),

      Email varchar (255),

      AddressLine varchar (255),

      City varchar (255)

      );

      Creating a Table in Oracle

      Below is the example for creating a table in Oracle

      CREATE TABLE Employee

      (

      EmployeeID number(10),

      EmpName varchar(255),

      Email varchar(255),

      AddressLine varchar (255),

      City varchar (255)

      );

      Creating a Table in Microsoft SQL Server

      Following is the example for creating a table in MS SQL Server

      CREATE TABLE Employee

      (

      EmployeeID int;

      EmpName varchar (255),

      Email varchar (255),

      Address varchar (255),

      City varchar (255)

      );

      Creating a new table using the existing table

      It is possible to create a copy of the existing table using create table command and the new table can get all the fields and columns from the old table or we can select the required columns for the new table.

      The syntax for creating a table with an existing table

      CREATE TABLE table_name AS

      SELECT column1, column2, ……

      FROM old_table_name WHERE ……;

      SQL creates the following copy of the employee table

      CREATE TABLE EmployeeCopy AS

      SELECT EmployeeID, EmpName, Email

      FROM Employee;

      Assigning PRIMARY KEY in a TABLE

      Use the following query for the respective database applications for creating a primary key to the table

      MySQL

      CREATE TABLE Employee (

      EmpID NOT NULL,

      EmpName varchar (255) NOT NULL,

      City varchar (255),

      PRIMARY KEY (EmpID)

      );

      SQL Server/Oracle/MS Access

      CREATE TABLE Employee

      EmpID NOT NULL PRIMARY KEY,

      EmpName varchar (255) NOT NULL,

      City varchar (255),

      );

      Use the following query for creating a PRIMARY KEY for multiple columns of the table

      For MySQL / SQL Server / Oracle / MS Access

      CREATE TABLE Employee (

      EmpID NOT NULL,

      EmpName varchar (255),

      City varchar (255),

      CONSTRAINT PK_Employee PRIMARY KEY (EmpID, EmpName));

      Conclustion

      Learn more about creating a table and querying against tables in various database applications by enrolling in our SQL Training in Chennai at SLA Institute.

      1
      Softlogic-Academy