What is meant by TABLE?

  • Database is the collection of tables.

    •  

  • A table (entity or relation) is a collection of rows and columns (or) A table is a rectangular row/column arrangement of data values

    •  

     

  • A table (entity or relation) is a collection of rows and columns (or) A table is a rectangular row/column arrangement of data values

    •  

  • Each table consists of Unique Name in Data base(Duplciation of name not allowed)

    •  

  • Each Table comprises rows and columns
  • ROWS: also termed as RECORD\TUPLES.
  • COLUMNS: also termed as FIELDS\ATTRIBUTES.
  • Intersection of row & column must be atomic [single]
  • Table has at least 1 Column
  • Max Column = 255
  • Can have any number of rows

    • Table Structure

     

    Classifiation of Tables:

     

    There are two types of tables

    1.User Tables:Consists of Database Management system information

    2.System Tables:Consists of Database description, maintained by DBMS

    Rules to create Table

     

     

  • A database can have minimum of 1 table and Max of 2 Billion tables.

    •  

  • The table name should start with “Alphabet”

    •  

  • Spaces & Special symbols are not allowed except (_ underscore)

    •  

  • A table name can contain maximum of 128 characters
  • A table can contain maximum of 1024 cols
  • No limit for rows

    •  

     

    State the syntax to create a TABLE:

    Syntax - Create Table

    Create Table <Table name>
    (Column1 data type [(size)],
    Column 2 data type [(size)]….)

    NOTE: Mentioned in square bracket [ ] is optional

     

     

    Q: Write a Query to create a SALES table with the columns of “company, Qty, price, Sector”

    Copy the code - to Create SALES Table

    Create table sales
    (Company varchar (15),
    Qty int,
    Price int,
    Sector Varchar (10))

    Result:

     

    Q: Write a Query to create a CONTACT table with the columns of “company name, location, Organizer”?

     

    Copy the code - to Create CONTACT Table

    Create table Contact
    (Company varchar (11),
    Location Varchar(10),
    Organizer Varchar(10))

    Result:

     

    State the procedure to ALTER the structure of Table

     

    After defining the Table structure, we can ALTER the Table structure by: 1)Adding the Column 2)Editing the Column 3)Deleting the Column

     

     

    Add a Column:

     

     

     

    Q: Write a Query by adding “STATUS “column to “Contact” table?

    Steps:

    1)Create CONTACT table

    Copy the code - to Create CONTACT Table

    Create table Contact
    (Company varchar (11),
    Location Varchar(10),
    Organizer Varchar(10))

    Result:

    2)Add STATUS column to CONTACT table

    Copy the code - to Alter CONTACT Table

    Alter table contact add Status varchar(11)

    STATUS column has been added to previous table.

     

    Alter a Column:

     

    This is used to change the:

    • Data Type of Column
    • Data Size of Column

     

    Q: Write a Query to change Data type and data size of “location” column in CONTACT table?

    Syntax - to Alter Data type and size of Location Column

    Alter table contact alter column location char(15)

    Note: previously data type is Varchar now it was replaced with Char and size previously 10 now replaced with 15.

    Delete Column

    To delete a column DROP command is used

    Q: Write a Query to drop column of “Status” in “Contact” table?

    Syntax - to DROP column of STATUS

    alter table contact drop column Status

    Result:

    STATUS column has been deleted.

     

    Rename a Table & Column: SP_RENAME:

     

    It is used to Re-name a column

    Syntax: SP_Rename ‘Tab_Name.Col_Name’, ‘New_Col’

    It is used to Re-name a Table

    SP_Rename ‘Old table Name’, ‘New Table Name’