DML COMMANDS


TABLE CREATION:

SQL>CREATE TABLE  employee(e_no number(10),e_name  varchar(15),e_age number(5),d_no number(10),salary number(10));

Table created.

INSERTION:

SQL> insert into employee
 values (&e_no,'&e_name', &e_age, &d_no,&salary);
Enter value for e_no: 5025
Enter value for e_name: John
Enter value for e_age: 21
Enter value for d_no: 02
Enter value for salary: 35000
Old   1: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New   1: values (1,'John', 22, 02, 35000)

1 row created.

SQL>/
Enter value for e_no: 5032
Enter value for e_name: Jithin
Enter value for e_age: 20
Enter value for d_no: 02
Enter value for salary: 20000
Old   2: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New   2: values (1,'Jithin', 21, 02, 20000)

1 row created.

SQL>/
Enter value for e_no: 5033
Enter value for e_name: Midhu
Enter value for e_age: 19
Enter value for d_no: 03
Enter value for salary: 20000
Old   3: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New   3: values (1,' Midhu', 21, 03, 20000)

1 row created.

SQL>/
Enter value for e_no: 5047
Enter value for e_name: Stephen
Enter value for e_age: 20
Enter value for d_no: 01
Enter value for salary: 25000
Old   4: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New   4: values (1,'Stephen', 21, 01, 25000)

1 row created.

Enter value for e_no: 5001
Enter value for e_name: Aswin
Enter value for e_age: 19
Enter value for d_no: 04
Enter value for salary: 30000
Old   5: values (&e_no,'&e_name', &e_age, &d_no, &salary)
New   5: values (1,'Aswin', 20, 04, 30000)

1 row created.

SQL> select * from employee;

  E_NO  E_NAME       E_AGE      D_NO        SALARY  
---------- ---------- ---------- ---------- ---------- ---------------
    5025      John          21             02             35000
    5032      Jithin                 20             02             20000
    5033      Midhu                   19             03             20000
    5047      Stephen              20             01             25000
    5001      Aswin                19            04              30000
        
SQL>UPDATE employee  SET e_name=’ Midhun’
          WHERE e_no=5033;

Updated.

SQL> select * from employee;

  E_NO  E_NAME       E_AGE      D_NO         SALARY  
---------- ---------- ---------- ---------- ---------- ---------------
     5025        John           21              02           35000
     5032        Jithin                  20             02           20000
     5033        Midhun       19             03            20000
     5047        Stephen               20             01            25000
     5001       Aswin                 19             04             30000

SQL>DELETE from employee WHERE  employee=5001;

Deleted.

SQL> select * from employee;

  E_NO  E_NAME       E_AGE            D_NO     SALARY  
---------- ---------- ---------- ---------- ---------- ---------------
     5025        John           21            02           35000
     5032        Jithin                  20            02           20000
     5033        Midhun       19            03            20000
     5047        Stephen               20            01            25000

SQL> select e_name
          from employee
          order by e_name DESC;

E_NAME
--------------
Stephen
Midhun
Jithin
John

SQL> select e_name, e_age
          from employee
          order by e_age desc, e_name asc;

E_NAME          E_AGE
---------- -------------------
Midhun         19
Stephen                 20
Jithin                    20
John             21

SQL>SELECT * from employee WHERE salary>20000 AND salary<35000;

   E_NO  E_NAME       E_AGE      D_NO     SALARY  
---------- ---------- ---------- ---------- ---------- ---------------
5047        Stephen               20             01            25000

SQL> select e_name,e_no,salary
          from employee
          where e_age>20and salary>20000;


E_NAME           E_NO     SALARY
---------- ---------- -------------------------
John            5025         35000

GROUP FUNCTIONS:

SQL> select avg (salary)
          from employee;

AVG (SALARY)
----------------------
        25000

SQL> select max (e_age)
          from employee;

MAX (E_AGE)
---------------------
        21

SQL> select min (salary)
          from employee;

MIN (SALARY)
---------------------
      20000

SQL> select sum (salary)
          from employee;

SUM (SALARY)
-----------
     100000

COUNT FUNCTION:
 
  SQL> select count (d_no)
             from employee;

COUNT (D_NO)  
-----------
        4
         
SQL> select count (distinct (e_no))
          from employee;

COUNT (DISTINCT (D_NO))
---------------------
                    3

CHARACTER FUNCTION:

SQL> select lower (e_name)
          from employee;

LOWER (E_NA
----------
John
jithin
midhun
Stephen

SQL> select upper (e_name)
   from employee;

UPPER (E_NA
----------
JOHN
JITHIN
MIDHUN
STEPHEN

SQL> select length (e_name)
          from employee;

LENGTH (E_NAME)
--------------
          8
          6
          11
          6
      
Previous
Next Post »

If you still didn't find what you're looking for, you can search this website below: