Important points to remember:

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

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department, count (*) as cnt

From emp

Group by department

Correct way is:

Select deptno as department, count (*) as cnt

From emp

Group by deptno

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

WITH CHECK OPTION

The WITH CHECK OPTION clause is used for an updatable view to prohibits the changes to the view that would produce rows which are not included in the defining query.
Sql> create or replace view t1_view as select name, id from t1 where id > 3 with check option;
The COUNT (*) will count all rows in the table.
--------------------------------------------------------------------------------------------------------------------------
If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.
SELECT DISTINCT  (a.sal) 

-------------------------------------------------------------------------------------------------------------------------
Which is faster – IN or EXISTS?

• IN for big outer query and small inner query.

• EXISTS for small outer query and big inner query.

CREATE TABLE Employees
Emp_ID int NOT NULL CHECK (Emp_ID>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255)
);

 Table Based Records

 Cursor Based Records

 Programmer-defined Records

x



View created.

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

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

--------------------------------------------------------------------------------------------------------------------------------------
SQL Inline Views
An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query.

--------------------------------------------------------------------------------------------------------------------------------------
Query to find the Nth highest salary:

FROM EMP A 
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) 
            FROM EMP B 
            WHERE a.sal<=b.sal)

In other words,

If both the outer query and inner query were large, either could work well – the choice would depend on indexes and other factors. Use whichever makes logical sense in the context. Or whichever is your personal favorite. Oracle will figure out the most efficient way to run the query, either way.

--------------------------------------------------------------------------------------------------------------------------------------
Union all is faster than union, union's duplicate elimination requires a sorting operation, which takes time.

--------------------------------------------------------------------------------------------------------------------------------------
The SQL CHECK constraint bounds/limits the value range that can be placed in a column.

(

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

PL/SQL Collections

 Records

 PL/SQL Tables

 Varrays

 Nested Tables

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

In XML Publisher Data Template The location of the trigger indicate at what point the trigger fires:

• Place a beforeReport trigger anywhere in your data template before the <dataStructure> section.. A beforeRepot trigger fires before the dataQuery is executed.
• Place an afterReport trigger after the <dataStructure> section. An afterReport trigger fires after you exit and after XML output has been generated.

--------------------------------------------------------------------------------------------------------------------------------------
What is the difference between the bad file and the discard file in SQL*Loader.
Answer:  The bad file and discard files both contain rejected rows, but they are rejected for different reasons:

• Bad file:  The bad file contains rows that were rejected because of errors.  These errors might include bad datatypes or referential integrity constraints.
• Discard file:  The discard file contains rows that were discarded because they were filtered out because of a statement in the SQL*Loader control file.

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