Working With Table

Working With Table

Create Table

- Whenever we create a table that time we need to provide a table_name with column details such as name,data type and constraints. 

- But constraints are optional.

- We will be able to add constraints on columns after table creation. By using add constraints statements. 

 Syntax:
    Create Table   tblName (column_name1 Data_Type,column_name2 Data_Type constraint(optional),column_name3 Data_Type constraint(optional),column_name4 Data_Type constraint(optional))

Create Table tblname (column_name1 Data_Type,column_name2 Data_Type,column_name3 Data_Type)

Example:

Create Table tblPerson(personId Int,personName varchar(50))

Insert Data/record into table

  - By using the insert statement we will add record(row) into the table. 

            - There are three ways to insert data into a table.

              1. We have to insert data in the same sequence that we used to create a    table.

            Syntax:
                  Insert into tbl_name values(value1,value2,...valuen)

           Example:
                  Insert into tblPerson values(1,'Ram')

           Insert into tblPerson values(2,'Laxman')

            2. We insert the data into the table by mentioning column names. Here we don’t need to remember column sequence.

             Syntax:
                  Insert into tbl_name(column1,column3)values(value1,value2)

             Example:

              insert into tblPerson(personId) values(3)

           3. Insert multiple record in table by using single insert statement

               insert into tblperson values

          (1,'Ram'),(2,'Laxman'),(3,'Sita');

            Sometimes we don’t have a value to insert in a column. At that time we need to set the default value based on data type or mentioned blank single blank single quote(‘’).

               insert into tblperson values

          (1,'Ram'),(2,'Laxman'),(0,'Sita');

           insert into tblperson values

          (1,'Ram'),(2,'Laxman'),(‘','Sita');

Select Statement

• It is used to fetch records/data from the table

           select tblPerson  //Invalid column name tblPerson

         How to fetch all records from the table?

       Select * from tblperson

         How to fetch particular column records?

        Select personName from tblPerson

Update Statement

• By using update statements we are able to update existing records from the table.

      Syntax:
          Update table_name

            set column1 = value1, column12 = value2, ... column1n = valuen

      Example:
   update tblPerson set personName = ''

  update tblPerson set personName = 'Ram',personId=''

Delete Statement
     - By using the Delete statement we are able to delete one or more records from the table.

Syntax:
Delete [Top (number)]  from table_ame
Here top part is optional

Example:

    delete top (3) from tblPerson

• To delete all rows from the table.
  Syntax:
  delete from tbl_name
  Example:

            delete from tblPerson

Drop single or multiple Table:

    -  Used it able to drop all records as well as table schema also drop with column and constraint

Syntax:

single:
  Drop table tablename

Multiple:

   Drop table tablename,tablename…tablename..n

Example

Drop table tblPerson

Drop table A,B

To update or delete the particular rows based on conditions that we are not able to achieve using only update and delete statements so to achieve this we need to use update and delete statements with Where clause.