Oracle and SQL Server Topics

Oracle Topics

-          Installation of Oracle client

-          Tablespace

-          DBF file

-          Relation between tablespace and DBF file

-          User

-          Role and permission

-          Pseudo columns

-          Save point,Rollback and commit

-          Insert,delete,update and select

-          Equi join and non equi join

-          Natural join

-          Cross join/ Cartesian join

-          Self join

-          Left, Right and full outer join

-          Column format

-          Aggregate, Number and Conversion function

-          Buffer command

-          IN/ Exist

-          Subquery

-          Table

-          View

-          Index

-          Materialize view

-          Exception handling

-          Cursor

-          Trigger

-          Function

-          Procedure

-          Package

-          Dynamic SQL

-          Loops

-          Array

-          Collection

-          PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips

-          Replication

-          Job

http://www.way2tutorial.com/plsql/tutorial.php

https://docs.oracle.com/en/database/oracle/oracle-database/index.html

https://docs.oracle.com/cd/B14117_01/nav/portal_1.htm - Download PDF

https://www.tutorialspoint.com/plsql/plsql_arrays.htm

https://www.guru99.com/pl-sql-data-types.html

http://sql-plsql.blogspot.in/2007/05/oracle-plsql-nested-tables.html

http://dotnetmentors.com/sql/sql-server-common-table-expression-with-examples.aspx
https://www.tutorialgateway.org/sql-server-cte/


Common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, DELETE or MERGE statement.

SQL Server supports two types of CTEs-recursive and nonrecursive.

WHEN TO USE

• If you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code reuse.
• After declaration of table, you can refer the table created multiple times in scope of the same query.
• Can be used in place of views or temporary tables.
• Recursive query is easily created through CTE.
• Instead of cursor

Syntax of CTE
With CTE_name <col1,col2,..>
<CTE_query>
select <col1,col2,..>
from CTE_name