Google Search

Wednesday, October 12, 2011

DATA MANIPULATION LANGUAGE (DML) SQL QUERY LANGUAGE FOR ME/M.TECH STUDENTS


2. DATA MANIPULATION LANGUAGE (DML): The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:
1. INSERT                  2. UPDATE                3. DELETE
1. INSERT INTO:  This is used to add records into a relation. These are three type of INSERT INTO queries which are as
a) Inserting a single record
Syntax:  INSERT INTO relationname(field_1,field_2,.field_n)VALUES   
               (data_1,data_2,........data_n);
Example:  SQL>INSERT INTO student(sno,sname,class,address)VALUES  
       (1,’Ravi’,’M.Tech’,’Palakol’);
b) Inserting all records from another relation
Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n  
      FROM relation_name_2 WHERE field_x=data;
Example: SQL>INSERT INTO std SELECT sno,sname FROM student
       WHERE name = ‘Ramu‘;

c) Inserting multiple records
Syntax:  INSERT INTO relation_name field_1,field_2,.....field_n) VALUES   
               (&data_1,&data_2,........&data_n);
Example:  SQL>INSERT INTO student(sno,sname,class,address)   
      VALUES(&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101
Enter value for name: Ravi
Enter value for class: M.Tech
Enter value for name: Palakol
2. UPDATE-SET-WHERE: This is used to update the content of a record in a relation.
Syntax:  SQL>UPDATE relation name SET Field_name1=data,field_name2=data,
          WHERE field_name=data;
Example:         SQL>UPDATE student SET sname = ‘kumar’ WHERE sno=1;

3. DELETE-FROM: This is used to delete all the records of a relation but it will retain the structure of that relation.
a) DELETE-FROM: This is used to delete all the records of relation.
    Syntax:        SQL>DELETE FROM relation_name;
   Example:      SQL>DELETE FROM std;
 b) DELETE -FROM-WHERE: This is used to delete a selected record from a relation.
    Syntax:        SQL>DELETE FROM relation_name WHERE condition;
   Example:      SQL>DELETE FROM student WHERE sno = 2;

3. DRL(DATA RETRIEVAL LANGUAGE): Retrieves data from one or more tables.
1. SELECT FROMTo display all fields for all records.
Syntax :           SELECT * FROM relation_name;
Example :        SQL> select * from dept;
DEPTNO     DNAME           LOC
     --------   -----------     ----------
        10      ACCOUNTING      NEW YORK
        20      RESEARCH        DALLAS
        30      SALES           CHICAGO
        40      OPERATIONS      BOSTON
2. SELECT FROM:  To display a set of fields for all records of relation.
Syntax:                        SELECT a set of fields FROM relation_name;
Example:         SQL> select deptno, dname from dept;
    DEPTNO     DNAME
     -------    ----------
      10        ACCOUNTING
      20        RESEARCH
      30        SALES
3. SELECT - FROM -WHERE: This query is used to display a selected set of fields for a selected set of records of a relation.
Syntax:            SELECT a set of fields FROM relation_name WHERE  condition;
Example: SQL> select * FROM dept WHERE deptno<=20;
    DEPTNO     DNAME           LOC
     ------     -----------     ------------
     10         ACCOUNTING      NEW YORK
     20       RESEARCH        DALLAS
4. SELECT - FROM -GROUP BY: This query is used to group to all the records in a relation together for each and every value of a specific key(s) and then display them for a selected set of fields the relation.
Syntax: SELECT a set of fields FROM relation_name GROUP BY field_name;
Example:         SQL> SELECT EMPNO, SUM (SALARY) FROM EMP GROUP BY EMPNO;
     EMPNO      SUM (SALARY)
     ------     ----------
       1        3000
       2        4000
       3        5000
       4       6000
    4 rows selected.
5. SELECT - FROM -ORDER BY: This query is used to display a selected set of fields from a relation in an ordered manner base on some field.
Syntax:            SELECT a set of fields FROM relation_name
ORDER BY field_name;

Example: SQL> SELECT empno,ename,job FROM emp ORDER BY job;
EMPNO      ENAME      JOB
------     ---------  --------
      4         RAVI      MANAGER
      2         aravind   Manager
      1         sagar     clerk
      3         Laki       clerk
    4rows selected.
6. JOIN using SELECT - FROM - ORDER BY: This query is used to display a set of fields from two relations by matching a common field in them in an ordered manner based on some fields.
Syntax:     SELECT a set of fields from both relations FROM relation_1, relation_2 WHERE relation_1.field_x = relation_2.field_y ORDER BY field_z;
Example: SQL>SELECT empno,ename,job,dname FROM emp,dept
WHERE emp.deptno = 20 ORDER BY job;
EMPNO      ENAME      JOB       DNAME
------     ------     -------    ----------
7788       SCOTT      ANALYST    ACCOUNTING
7902       FORD       ANALYST    ACCOUNTING
------
7566       JONES      MANAGER    OPERATIONS
7566       JONES      MANAGER    SALES
  20 rows selected.

7. JOIN using SELECT - FROM - GROUP BY: This query is used to display a set of fields from two relations by matching a common field in them and also group the corresponding records for each and every value of a specified key(s) while displaying.
Syntax:  SELECT a set of fields from both relations FROM relation_1,relation_2 WHERE relation_1.field-x=relation_2.field-y GROUP BY field-z;
Example:         SQL> SELECT empno,SUM(SALARY) FROM emp,dept
WHERE emp.deptno =20 GROUP BY empno;
            EMPNO SUM (SALARY)
     -------    --------
     7369       3200
     7566       11900
     7788       12000          
     7876       4400
8. UNION: This query is used to display the combined rows of two different queries, which are having the same structure, without duplicate rows.

Syntax:     SELECT field_1,field_2,....... FROM relation_1 WHERE (Condition) UNION SELECT field_1,field_2,....... FROM relation_2 WHERE (Condition);
Example:
SQL> SELECT * FROM STUDENT;
SNO        SNAME
     -----      -------
     1          kumar
     2          ravi
     3          ramu

SQL> SELECT * FROM STD;
           SNO        SNAME
     -----      -------
    3          ramu
     5          lalitha
     9          devi
     1          kumar

SQL> SELECT * FROM student UNION SELECT * FROM std;
SNO        SNAME
           ----       ------
           1         kumar
           2         ravi
           3         ramu
           5         lalitha
           9         devi
9. INTERSET: This query is used to display the common rows of two different queries, which are having the same structure, and to display a selected set of fields out of them.
Syntax: SELECT field_1,field_2,.. FROM relation_1 WHERE
(Condition) INTERSECT SELECT field_1,field_2,.. FROM relation_2 WHERE(Condition);
Example : SQL> SELECT * FROM student INTERSECT SELECT * FROM std;
     SNO        SNAME
     ----       -------
     1          Kumar

10. MINUS: This query is used to display all the rows in relation_1,which are not having in the relation_2.
Syntax: SELECT field_1,field_2,......FROM  relation_1
 WHERE(Condition) MINUS  SELECT field_1,field_2,.....
 FROM relation_2 WHERE(Conditon);


SQL> SELECT * FROM student MINUS SELECT * FROM std;
     SNO      SNAME
     ----       -------
     2          RAVI
     3          RAMU

0 comments:

Post a Comment