Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    DML Commands Exp 5

    Uploaded by

    sahasradaragani1

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DML Commands Exp 5

    Uploaded by

    sahasradaragani1
    3 views9 pages

    Original Title

    Dml Commands Exp 5

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    3 views9 pages

    DML Commands Exp 5

    Uploaded by

    sahasradaragani1

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 9

    EXP:4 DML commands

    AIM: Implementation of DML commands of SQL with suitable


    examples
    1.Insert
    2.Update
    3. Delete
    Theory

    Data Manipulation Language ( DML )


    It provides the following commands to update the data in the database tables
    1) INSERT  To Insert the records into the database table
    2) UPDATE To Modify the already existing record in the table
    3) DELETE  To Delete the already existing record in the table

    Create the some table and test it


    SQL> CREATE TABLE emp_test ( empno number(10),
    ename varchar2(30),
    salary number(10,2),
    deptno number(10)
    );
    INSERTING RECORDS INTO THE TABLE
    Syntax:
    INSERT INTO <table_name> VALUES (value1, value2, value3, ….., …….., ….. );

    SQL> SELECT * FROM emp_test;


    no rows selected
    SQL> INSERT INTO EMP_TEST VALUES(1001, 'MNRAO', 5500, 20);
    SQL> SELECT * FROM EMP_TEST;

    (OR)
    Inserting records dynamically
    Oracle provides substation variables method to insert the records dynamically
    Syntax :
    INSERT INTO <table_name> values( &col1, &col2, &col3, …, …., …. ) (or)
    &Var1, &var2, &var3, …, …., …. )

    Note : The column or variable name should be in single quotations for char and date type data

    UPDATE : to modify the records in a table

    Syntax :
    UPDATE <table_name> SET Col1 = value, Col2=value, Col3=val

    Update statement is used to update rows in existing tables which is in your


    own schema or if you have update privilege on them.
    For example to raise the salary by Rs.500 of employee number 104. You can
    give the following statement.
    Ex:
    mysql> update emp set sal=sal+500 where empno = 104;

    In the above statement if we did not give the where condition then all
    employees salary will be raised by Rs. 500. That’s why always specify proper
    WHERE condition if don’t want to update all employees.

    For example We want to change the name of employee no 102 from ‘Sami’
    to ‘Mohd Sami’ and to raise the salary by 10%. Then the statement will be.
    Ex:
    mysql> update emp set name=’Mohd Sami’,
    sal=sal+(sal*10/100) where empno=102;
    DELETING RECORDS FROM THE TABLE
    Syntax:
    DELETE FROM <table_name> ; to delete all records from the table
    Ex:
    mysql> Delete from emp;
    it will delete all records from table

    Use the DELETE statement to delete the rows from existing tables which are
    in your schema or if you have DELETE privilege on them.

    Mysql> Deleting particular records


    Sql provides the where clause to delete particular records. It will be covered as part of oracle
    clauses.
    For example to delete the employee whose empno is 102.
    Ex:
    mysql> delete from emp where empno=102;
    If you don’t mention the WHERE condition then all rows will be deleted.
    Suppose we want to delete all employees whose salary is above 2000. Then
    give the following DELETE statement.
    Mysql> delete from emp where salary > 2000;

    LAB PRACTICE:

    EXP:4 DML commands

    Step1: connecting to database

    open terminal and enter following command


    mysql -u root -p
    enter password: root

    Step2: creating a database


    syntax:
    mysql> create database dml_db;
    mysql>use dml_db;
    mysql>
    create table dept(
    deptno int(2),
    dname varchar(14),
    loc varchar(13),
    constraint pk_dept primary key (deptno)
    );

    mysql>desc dept;

    mysql> create table emp(empno int(4), ename varchar(10),job


    varchar(9),mgr int(4), hiredate date, sal
    int(7),comm int(7),deptno int(2),constraint pk_emp primary
    key(empno),constraint fk_deptno foreign key (deptno) references
    dept(deptno));
    mysql>desc dept;

    Inserting values into dept table


    mysql>insert into dept (deptno, dname, loc)
    values(10, 'ACCOUNTING', 'NEW YORK');
    mysql> insert into dept
    values(20, 'RESEARCH', 'DALLAS');
    mysql>insert into dept
    values(30, 'SALES', 'CHICAGO');
    insert into dept
    values(40, 'OPERATIONS', 'BOSTON');

    Inserting data into emp table


    insert into emp
    values(
    7839, 'KING', 'PRESIDENT', null,
    '1981-11-17',
    5000, null, 10
    );

    insert into emp


    values(
    7698, 'BLAKE', 'MANAGER', 7839,
    '1981-5-1',
    2850, null, 30
    );

    insert into emp


    values(
    7782, 'CLARK', 'MANAGER', 7839,
    '1981-6-9',
    2450, null, 10
    );
    insert into emp
    values(
    7566, 'JONES', 'MANAGER', 7839,
    '1981-4-2',
    2975, null, 20
    );

    insert into emp


    values(
    7788, 'SCOTT', 'ANALYST', 7566,
    '1987-7-13',
    3000, null, 20
    );

    insert into emp


    values(
    7902, 'FORD', 'ANALYST', 7566,
    '1981-12-3',
    3000, null, 20
    );
    insert into emp
    values(
    7369, 'SMITH', 'CLERK', 7902,
    '1980-12-17',
    800, null, 20
    );

    insert into emp


    values(
    7499, 'ALLEN', 'SALESMAN', 7698,
    '1981-2-20',
    1600, 300, 30
    );
    insert into emp
    values(
    7521, 'WARD', 'SALESMAN', 7698,
    '1981-2-22',
    1250, 500, 30
    );
    insert into emp
    values(
    7654, 'MARTIN', 'SALESMAN', 7698,
    '1981-9-28',
    1250, 1400, 30
    );
    insert into emp
    values(
    7844, 'TURNER', 'SALESMAN', 7698,
    '1981-9-8',
    1500, 0, 30
    );
    insert into emp
    values(
    7876, 'ADAMS', 'CLERK', 7788,
    '1987-7-13',
    1100, null, 20
    );
    insert into emp
    values(
    7900, 'JAMES', 'CLERK', 7698,
    '1981-12-3',
    950, null, 30
    );

    insert into emp


    values(
    7934, 'MILLER', 'CLERK', 7782,
    '1982-1-23',
    1300, null, 10
    );

    You might also like