ORACLE AND MSSQLSERVER

Refreshing materialized views In Oracle, if you specify REFRESH FAST for a single-table aggregate Oracle materialized view, you must have created a materialized view log for the underlying table, or the refresh command will fail. When creating an Oracle...

Ease Network Loads Create a Mass Deployment Environment Enable Data Subsetting Enable Disconnected Computing

SQL ACID ACID properties When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics. The developers of the components that comprise the transaction are assured that these characteristics...

SQL Database in Suspect Mode What are the reasons for the database to be in suspect state? 1) Data and Log files missing 2) Corruption of pages in the Data files. 3) Issues that are caused during Recovery/Restoring process 4) Disk level failures 5) SNA...

set echo on create table dept ( deptno number(2), dname varchar2(14), loc varchar2(13) ) / LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,BC 20,Accounting,BC 30,Consulting,BC 40,Finance,BC drop table...

SQL> create table student_emails_ext 2 (firstname varchar(40), 3 lastname varchar(40), 4 email varchar(80) ); Table created. SQL> SQL> create table student_emails 2 as select * from student_emails_ext 3 where 0=1 4 / Table created. SQL> SQL> SQL> merge...

select emp_id, lastname from emp c where not exists (select * from gift o where o.emp_id = c.emp_id); SELECT e1.ename, d.dname FROM emp e1, dept d WHERE e1.deptno = d.deptno AND EXISTS (SELECT 'x' FROM emp e2 WHERE e2.ename = 'Smart' AND e2.deptno =...

  SQL> CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (degree NUMBER) RETURN NUM BER IS 2 buffer NUMBER; 3 BEGIN 4 buffer := (degree * 9/5) + 32; 5 RETURN buffer; 6 END celsius_to_fahrenheit; 7 / Function created

> create or replace type numberlist_t 2 as varray(4) of varchar2(20); 3 / Type created. SQL> SQL> column numlist format a60 SQL> SQL> alter table e add (numlist numberlist_t) SQL> create or replace function ext 2 (p_varray_in numberlist_t) 3 return varchar2...

Advanced Grouping in oracle, more helpful for report generate. SQL> SELECT * FROM EMP; -- GROUP BY CLAUSE SQL> SELECT deptno, SUM(sal) FROM emp WHERE sal <= 4000 GROUP BY deptno; -- ROLLUP SQL> SELECT deptno, SUM(sal) FROM emp WHERE sal <= 4000 GROUP...