PL/SQL - Set Operator
UNION , INTERSECT , MINUS , DISTINCT:
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 27 12:46:26 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> CREATE TABLE supplierenginetab
2 (sengid number,
3 sengcompname varchar2(20)
4 );
Table created.
SQL> DESC supplierenginetab;
Name Null? Type
----------------------------------------- -------- ----------------------------
SENGID NUMBER
SENGCOMPNAME VARCHAR2(20)
SQL> INSERT INTO supplierenginetab
2 (sengid, sengcompname)
3 VALUES(101,'Toyota');
1 row created.
SQL> INSERT INTO supplierenginetab
2 (sengid, sengcompname)
3 VALUES(102,'Eicher');
1 row created.
SQL> INSERT INTO supplierenginetab
2 (sengid, sengcompname)
3 VALUES(103,'Ford');
1 row created.
SQL> INSERT INTO supplierenginetab
2 (sengid, sengcompname)
3 VALUES(104,'imcl');
1 row created.
SQL> SELECT * FROM supplierenginetab;
SENGID SENGCOMPNAME
---------- --------------------
101 Toyota
102 Eicher
103 Ford
104 imcl
SQL> CREATE TABLE supplierbraketab
2 (
3 sbrakeid number,
4 sbrakecompname varchar2(15)
5 );
Table created.
SQL> INSERT INTO supplierbraketab
2 (sbrakeid, sbrakecompname)
3 VALUES(9960, 'brembo');
1 row created.
SQL> INSERT INTO supplierbraketab
2 (sbrakeid, sbrakecompname)
3 VALUES(9961, 'Eicher');
1 row created.
SQL> INSERT INTO supplierbraketab
2 (sbrakeid, sbrakecompname)
3 VALUES(9962, 'Amg');
1 row created.
SQL> INSERT INTO supplierbraketab
2 (sbrakeid, sbrakecompname)
3 VALUES(9963, 'imcl');
1 row created.
SQL> SELECT * FROM supplierenginetab;
SENGID SENGCOMPNAME
---------- --------------------
101 Toyota
102 Eicher
103 Ford
104 imcl
SQL> SELECT * FROM supplierbraketab;
SBRAKEID SBRAKECOMPNAME
---------- ---------------
9960 brembo
9961 Eicher
9962 Amg
9963 imcl
SQL> SELECT sengid AS "ID", SENGCOMPNAME AS "Supplier"
2 FROM supplierenginetab
3 UNION
4 SELECT sbrakeid AS "ID", SBRAKECOMPNAME AS "Supplier"
5 FROM supplierbraketab;
ID Supplier
---------- --------------------
101 Toyota
102 Eicher
103 Ford
104 imcl
9960 brembo
9961 Eicher
9962 Amg
9963 imcl
8 rows selected.
SQL> SELECT sengid AS "ID", SENGCOMPNAME AS "Supplier"
2 FROM supplierenginetab
3 INTERSECT
4 SELECT sbrakeid AS "ID", SBRAKECOMPNAME AS "Supplier"
5 FROM supplierbraketab;
no rows selected
SQL> SELECT SENGCOMPNAME AS "Supplier"
2 FROM supplierenginetab
3 INTERSECT
4 SELECT SBRAKECOMPNAME AS "Supplier"
5 FROM supplierbraketab;
Supplier
--------------------
Eicher
imcl
SQL> SELECT SENGCOMPNAME AS "Common Supplier"
2 FROM supplierenginetab
3 INTERSECT
4 SELECT SBRAKECOMPNAME AS "Common Supplier"
5 FROM supplierbraketab;
Common Supplier
--------------------
Eicher
imcl
SQL> SELECT SENGCOMPNAME AS "Eng. Supp. who are not supplying brakes"
2 FROM supplierenginetab
3 MINUS
4 SELECT SBRAKECOMPNAME AS "Eng. Supp. who are not supplying brakes"
5 FROM supplierbraketab;
SELECT SENGCOMPNAME AS "Eng. Supp. who are not supplying brakes"
*
ERROR at line 1:
ORA-00972: identifier is too long
SQL> SELECT SENGCOMPNAME AS "Eng. Supp. only"
2 FROM supplierenginetab
3 MINUS
4 SELECT SBRAKECOMPNAME AS "Eng. Supp. only"
5 FROM supplierbraketab;
Eng. Supp. only
--------------------
Ford
Toyota
SQL> SELECT SBRAKECOMPNAME AS "Brake Supp. only"
2 FROM supplierbraketab
3 MINUS
4 SELECT SENGCOMPNAME AS "Brake Supp. only"
5 FROM supplierenginetab;
Brake Supp. only
--------------------
Amg
brembo
SQL> SELECT * FROM employeetab;
EMPID EMPNAME G DOJ BSAL ITR GSAL
---------- --------------- - --------- ---------- ---------- ----------
101 Julie f 14-MAR-07 2222 888.8 1333.2
102 Toffee f 12-APR-08 5400 2160 3240
103 Larry M 01-SEP-10 15175.1 6070.04 9105.06
105 Julie f 01-OCT-00 9000 3600 5400
106 Aan Kaur Brar f 10-OCT-01 7000 2800 4200
SQL> SELECT gender from employeetab;
G
-
f
f
M
f
f
SQL> SELECT DISTINCT gender from employeetab;
G
-
M
f
SQL> SELECT DISTINCT empname from employeetab;
EMPNAME
---------------
Julie
Toffee
Larry
Aan Kaur Brar
SQL> SELECT COUNT(empname) FROM employee;
SELECT COUNT(empname) FROM employee
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT COUNT(empname) FROM employeetab;
COUNT(EMPNAME)
--------------
5
SQL> SELECT DISTINCT COUNT(empname) FROM employeetab;
COUNT(EMPNAME)
--------------
5
SQL> SELECT COUNT(DISTINCT empname) FROM employeetab;
COUNT(DISTINCTEMPNAME)
----------------------
4
SQL>
------------------------------------------------------------
SELF JOIN, CREATE TABLE FROM SELECT:
->s1. alter the table to add a new column "mgrid number(6)"
->s2. alter the table to convert the empid as the PK & the mgrid as the FK
->s3. add the mgrid s of the employees using the UPDATE command
-------------------------------------------
SQL> DESC employeetab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER(6)
EMPNAME VARCHAR2(15)
GENDER CHAR(1)
DOJ DATE
BSAL NUMBER(7,2)
ITR NUMBER(7,2)
GSAL NUMBER(7,2)
SQL> select * from employeetab;
EMPID EMPNAME G DOJ BSAL ITR GSAL
---------- --------------- - --------- ---------- ---------- ----------
101 Julie f 14-MAR-07 2222 888.8 1333.2
102 Toffee f 12-APR-08 5400 2160 3240
103 Larry M 01-SEP-10 15175.1 6070.04 9105.06
105 Julie f 01-OCT-00 9000 3600 5400
106 Aan Kaur Brar f 10-OCT-01 7000 2800 4200
SQL> ALTER TABLE employeetab
2 Add Mgrid number(6);
Table altered.
SQL> DESC employeetab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER(6)
EMPNAME VARCHAR2(15)
GENDER CHAR(1)
DOJ DATE
BSAL NUMBER(7,2)
ITR NUMBER(7,2)
GSAL NUMBER(7,2)
MGRID NUMBER(6)
SQL> UPDATE employeetab
2 SET Mgrid = 101
3 WHERE empid = 102;
1 row updated.
SQL> UPDATE employeetab
2 SET Mgrid = 103
3 WHERE empid = 105;
1 row updated.
SQL> UPDATE employeetab
2 SET Mgrid = 105
3 WHERE empid = 106;
1 row updated.
SQL> SELECT *FROM employeetab;
EMPID EMPNAME G DOJ BSAL ITR GSAL
---------- --------------- - --------- ---------- ---------- ----------
MGRID
----------
101 Julie f 14-MAR-07 2222 888.8 1333.2
102 Toffee f 12-APR-08 5400 2160 3240
101
103 Larry M 01-SEP-10 15175.1 6070.04 9105.06
EMPID EMPNAME G DOJ BSAL ITR GSAL
---------- --------------- - --------- ---------- ---------- ----------
MGRID
----------
105 Julie f 01-OCT-00 9000 3600 5400
103
106 Aan Kaur Brar f 10-OCT-01 7000 2800 4200
105
SQL> ALTER TABLE employeetab
2 Add CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid),
3 CONSTRAINT FK_employeetab_Mgrid FOREIGN KEY(Mgrid);
Add CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid),
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option
SQL> ALTER TABLE employeetab
2 CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid);
CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid)
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option
SQL> ALTER TABLE employeetab
2 Add CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid),
3 ADD CONSTRAINT FK_employeetab_Mgrid FOREIGN KEY(Mgrid)
4 REFERENCES employeetab(empid);
Add CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid),
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option
SQL> ALTER TABLE employeetab
2 Add CONSTRAINT PK_employeetab_empid PRIMARY KEY(empid);
Table altered.
SQL> ALTER TABLE employeetab
2 ADD CONSTRAINT FK_employeetab_Mgrid FOREIGN KEY(Mgrid)
3 REFERENCES employeetab(empid);
Table altered.
SQL> DESC employeetab;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(6)
EMPNAME VARCHAR2(15)
GENDER CHAR(1)
DOJ DATE
BSAL NUMBER(7,2)
ITR NUMBER(7,2)
GSAL NUMBER(7,2)
MGRID NUMBER(6)
SQL> SELECT empid, empname, mgrid FROM employeetab;
EMPID EMPNAME MGRID
---------- --------------- ----------
101 Julie
102 Toffee 101
103 Larry
105 Julie 103
106 Aan Kaur Brar 105
SQL> SELECT e.empid AS "EMP ID", e.empname AS "EMP NAME"
2 ;
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> SELECT e.empid AS "EMP ID", e.empname AS "EMP NAME",
2 m.empname AS "MGR NAME"
3 FROM employeetab e, employeetab m
4 WHERE e.empid = m.mgrid;
EMP ID EMP NAME MGR NAME
---------- --------------- ---------------
101 Julie Toffee
103 Larry Julie
105 Julie Aan Kaur Brar
SQL> SELECT e.empid AS "EMP ID", e.empname AS "EMP NAME",
2 m.empname AS "MGR NAME"
3 FROM employeetab e, employeetab m
4 WHERE m.empid = e.mgrid;
EMP ID EMP NAME MGR NAME
---------- --------------- ---------------
102 Toffee Julie
105 Julie Larry
106 Aan Kaur Brar Julie
SQL> SELECT empid, empname, empname AS "Mgr name"
2 FROM employeetab
3 WHERE empid=mgrid;
no rows selected
SQL> SELECT e.empid AS "EMP ID", e.empname AS "EMP NAME",
2 m.empname AS "MGR NAME"
3 FROM employeetab e, employeetab m
4 WHERE m.mgrid = e.empid;
EMP ID EMP NAME MGR NAME
---------- --------------- ---------------
101 Julie Toffee
103 Larry Julie
105 Julie Aan Kaur Brar
SQL> CREATE TABLE EmpMgr
2 AS
3 SELECT e.empid AS "EMP ID", e.empname AS "EMP NAME",
4 m.empname AS "MGR NAME"
5 FROM employeetab e, employeetab m
6 WHERE e.mgrid = m.empid;
Table created.
SQL> SELECT * FROM empmgr;
EMP ID EMP NAME MGR NAME
---------- --------------- ---------------
102 Toffee Julie
105 Julie Larry
106 Aan Kaur Brar Julie
SQL> DESC empmgr;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP ID NUMBER(6)
EMP NAME VARCHAR2(15)
MGR NAME VARCHAR2(15)
SQL> SELECT * FROM empmgr;
EMP ID EMP NAME MGR NAME
---------- --------------- ---------------
102 Toffee Julie
105 Julie Larry
106 Aan Kaur Brar Julie
SQL> SELECT * FROM empmgr
2 WHERE Emp id=105;
WHERE Emp id=105
*
ERROR at line 2:
ORA-00920: invalid relational operator
SQL>
----------------------------------------------------------------------------------
0 Comments