Explain about Data Manipulation Language

     

    It explains about:

  • Insert
  • Update
  • Delete
  • Merge

    •  

     

    Explain about INSERT command

     

    Insert Command

     

  • It is used to add new rows in the Table
  • Only one row is inserted at a time through below mentioned systax

    •  

    Insert data into all Columns:

     

    Q: Write a Query to insert values in SALES table?

     

    Step 1. Create SALES Table

    create table sales (company varchar(10),
    Quantity int, price int, Sector varchar(10))

    Step 2. Insert values into SALES Table

    Insert into Sales values('Apple',1100,110,'south')

    Insert into Sales values('Banana',1200,210,'South')

    Insert into Sales values('Orange',1300,310,'North')

    Insert into Sales values('Papaya',1400,410,'East')

    Insert into Sales values('Grapes',1500,510,'West')

    Step 3. To retrive SALES Table data

    select * from sales

    Step 4. To delete SALES Table

    drop table sales

     

     

     

    Insert data into specified Columns:

     

     

    Q: Write a Query to insert values in CONTACT table against specified columns?

     

    Inserting NULL values:

  • Value of NULL represents to value of Unknown
  • NULL value doesn't have equivalent stastus with EMPTY/ZERO value
  • No two NULL values are equal

    • Explain about UPDATE command in DML

     

  • Update command is used to modify the data in table
  • SET is used as key word to execute this command

    •  

    We can update all rows at a time or specified rows with the help of WHERE clause

     

    Updating without WHERE clause(All rows in table for a column):

     

     

    Step 1:Create Table

    create table sales (company varchar(10),Quantity int, price int,Sector varchar(10))

     

    Step 2:Insert Data

    Insert into Sales values('Apple',1100,110,'south')

    Insert into Sales values('Banana',1200,210,'South')

    Insert into Sales values('Orange',1300,310,'North')

    Insert into Sales values('Papaya',1400,410,'East')

    Insert into Sales values('Grapes',1500,510,'West')

     

    Step 3:Update price

    Update sales set price = 50

     

    All the rows of PRICE has been udpated to 50

     

    Updating with WHERE clause(specific rows in table):

    Q:Write a Query to update price of Apple as 50

     

    Syntax: Update sales set price = 50 where company = 'Apple'

     

     

    Q. Write a Query to update Location & QUANTITY of Orange

     

    Syntax:update Sales set Location = 'East', Quantity = 1100 where Item = 'Orange'

     

     

    Explain about DELETE command in DML

     

  • It is used to remove all records (or) particular records from a table
  • User can ROLLBACK(undo) these records
  • The space for records remain exists in table

    •  

     

    Delete specified row using WHERE clause:

     

    Step 1: Create Table

    create table sales (company varchar(10),Quantity int, price int,Sector varchar(10))

     

    Step 2: Insert Data

    Insert into Sales values('Apple',1100,110,'south')

    Insert into Sales values('Banana',1200,210,'South')

    Insert into Sales values('Orange',1300,310,'North')

    Insert into Sales values('Papaya',1400,410,'East')

    Insert into Sales values('Grapes',1500,510,'West')

     

    Step 3: Delete the specified row

    Delete from sales where Item = 'Apple'

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Delete all records from table:

     

    Syntax:delete from sales

    All records got deleted from sales table