Class-3 Pl Sql

 

Notes:-   🠋             jobs updates 👉 click me 

SET OPERATORS:

-------------

1. UNION     -->It display the only uniq records. 

2. UNION ALL -->It display the all the records from both the tables.

3. INTERSECT -->It display the common records from both the tables.

4. MINUS     -->It will minus the common records from first table and display the remaining first

                table records.

DM1

---

CMOB  CNAME

 1      A

 2      B

 3      C

 4      D

 5      E

DM2

---

CMOB  CNAME

 6      F

 7      G

 8      H

 1      A

 3      C

SQL> CREATE TABLE DM1

  2  (CMOB NUMBER(10),

  3  CNAME VARCHAR2(10));

Table created.

SQL> INSERT INTO DM1 VALUES(1, 'A');

1 row created.

SQL> INSERT INTO DM1 VALUES(2, 'B');

1 row created.

SQL> INSERT INTO DM1 VALUES(3, 'C');

1 row created.

SQL> INSERT INTO DM1 VALUES(4, 'D');

1 row created.

SQL> INSERT INTO DM1 VALUES(5, 'E');

1 row created.

SQL> CREATE TABLE DM2

  2  (CMOB NUMBER(10),

  3  CNAME VARCHAR2(10));

Table created.

SQL> INSERT INTO DM2 VALUES(6, 'F');

1 row created.

SQL> INSERT INTO DM2 VALUES(7, 'G');

1 row created.

SQL> INSERT INTO DM2 VALUES(8, 'H');

1 row created.

SQL> INSERT INTO DM2 VALUES(1, 'A');

1 row created.

SQL> INSERT INTO DM2 VALUES(3, 'C');

1 row created.

SQL> SELECT * FROM DM1;

      CMOB CNAME

---------- ----------

         1 A

         2 B

         3 C

         4 D

         5 E

SQL> SELECT * FROM DM2;

      CMOB CNAME

---------- ----------

         6 F

         7 G

         8 H

         1 A

         3 C

SQL> SELECT * FROM DM1

  2  UNION

  3  SELECT * FROM DM2;\

  4

SQL> SELECT * FROM DM1

  2  UNION

  3  SELECT * FROM DM2;

      CMOB CNAME

---------- ----------

         1 A

         2 B

         3 C

         4 D

         5 E

         6 F

         7 G

         8 H

8 rows selected.

SQL> SELECT * FROM DM1

  2  UNION ALL

  3  SELECT * FROM DM2;

      CMOB CNAME

---------- ----------

         1 A

         2 B

         3 C

         4 D

         5 E

         6 F

         7 G

         8 H

         1 A

         3 C

10 rows selected.

SQL> SELECT * FROM DM1

  2  INTERSECT

  3  SELECT * FROM DM2;

      CMOB CNAME

---------- ----------

         1 A

         3 C

SQL> SELECT * FROM DM1

  2  MINUS

  3  SELECT * FROM DM2;

      CMOB CNAME

---------- ----------

         2 B

         4 D

         5 E

SQL> SELECT * FROM DM2

  2  MINUS

  3  SELECT * FROM DM2;

no rows selected

SQL> ED

Wrote file afiedt.buf

  1  SELECT * FROM DM2

  2  MINUS

  3* SELECT * FROM DM1

SQL> /

      CMOB CNAME

---------- ----------

         6 F

         7 G

         8 H

SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO=10

  2  UNION ALL

  3  SELECT JOB FROM EMP WHERE DEPTNO=10;

ENAME

----------

CLARK

KING

MILLER

MANAGER

PRESIDENT

CLERK

6 rows selected.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO=10

  2  UNION

  3  SELECT JOB FROM EMP WHERE DEPTNO=10;

ENAME

----------

CLARK

CLERK

KING

MANAGER

MILLER

PRESIDENT

6 rows selected.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO=10

  2  UNION ALL

  3  SELECT JOB FROM EMP WHERE DEPTNO=30;

ENAME

----------

CLARK

KING

MILLER

SALESMAN

SALESMAN

SALESMAN

MANAGER

SALESMAN

CLERK

9 rows selected.

SQL> ED

Wrote file afiedt.buf

  1  SELECT ENAME FROM EMP WHERE DEPTNO=10

  2  UNION

  3* SELECT JOB FROM EMP WHERE DEPTNO=30

SQL> /

ENAME

----------

CLARK

CLERK

KING

MANAGER

MILLER

SALESMAN

6 rows selected.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO=10

  2  UNION ALL

  3  SELECT SAL FROM EMP WHERE DEPTNO=10;

SELECT ENAME FROM EMP WHERE DEPTNO=10

       *

ERROR at line 1:

ORA-01790: expression must have same datatype as corresponding expression

SQL> SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10

  2  UNION ALL

  3  SELECT SAL FROM EMP WHERE DEPTNO=10;

SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10

*

ERROR at line 1:

ORA-01789: query block has incorrect number of result columns

SQL> SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10

  2  UNION ALL

  3  SELECT SAL, ENAME FROM EMP WHERE DEPTNO=10;

SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10

       *

ERROR at line 1:

ORA-01790: expression must have same datatype as corresponding expression

SQL> SELECT JOB FROM EMP WHERE DEPTNO=10

  2  UNION ALL

  3  (SELECT JOB FROM EMP WHERE DEPTNO=20

  4  MINUS

  5  SELECT JOB FROM EMP WHERE DEPTNO=30)

  6  UNION ALL

  7  SELECT ENAME FROM EMP WHERE DEPTNO=10;

JOB

----------

MANAGER

PRESIDENT

CLERK

ANALYST

CLARK

KING

MILLER

7 rows selected.

SQL>