SQLPLus Query Tips

To copy table across databases/schemes.

Oracle SQLplus Copy Command:
SQL> create table table_name as select * from table_name;
SQL> copy from usr/pwd@SID to usr/pwd@SID create table table_name as select * from table_name;

Drop Constraint Example:

- ALTER TABLE Employee DROP CONSTRAINT constraint_name;
- ALTER TABLE Employee disable/enable CONSTRAINT constraint_name;

ADD Constraint Examples
- ALTER TABLE Employee MODIFY (Department CONSTRAINT constraint_name_nn NOT NULL);
- ALTER TABLE Employee ADD/MODIFY
( CONSTRAINT constraint_name_fk FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID) )
- ALTER TABLE Employee ADD CONSTRAINT Employee_pk PRIMARY KEY (EmployeeID)
- ALTER TABLE Employee ADD CONSTRAINT Employee_ck UNIQUE (EmployeeID, Status)

Full table scan

1- SELECT COUNT(idA)
FROM tabA
WHERE idA NOT IN (SELECT idA FROM tabB)

2- SELECT COUNT(a.idA)
FROM tabB b, tabA a
WHERE a.idA = b.idA (+) AND b.idA IS NULL

3- SELECT COUNT(a.idA)
FROM tabA a
WHERE NOT EXISTS (SELECT ‘X’ FROM tabB a WHERE a.idA = b.idA)

HAVING Clause

The HAVING clause filters selected rows only after all rows have been fetched.
SELECT d.dname, AVG (e.sal) FROM emp e, dept d WHERE e.deptno = d.deptno
GROUP BY d.dname HAVING dname != ‘RESEAECH’ AND dname != ‘SALES’;

Anti Joins

An anti-join is used to return rows from a table that that are present in another table.
SELECT * FROM dept WHERE NOT EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);

Full Outer Joins

SELECT empno, ename, NVL(dept.deptno,emp.deptno) deptno, dname
FROM emp FULL OUTER JOIN dept ON (emp.deptno = dept.deptno) ORDER BY 1,2,3,4;

my weblog, my house, my heaven