Oracle

database

http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html
http://www.youtube.com/watch?v=_zOGdeI3Rhc
http://www.dwhworld.com/2010/10/sql-interview-questions-part-2/
http://www.dwhworld.com/2010/12/combine-multiple-rows-to-a-column-oracle/
http://docs.oracle.com/cd/B12037_01/index.htm
http://stackoverflow.com/questions/4020786/oracle-operator

// Show indexes for a given table in a given schema
Select owner, table_name, index_name, column_name FROM dba_ind_columns
Where owner=? AND table_name=?
Order by owner, table_name, column_name

// INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS

// Inner join:
SELECT * FROM TableA INNER JOIN TableB
ON TableA.name = TableB.name

SELECT *
FROM employee 
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

SELECT * FROM employee A, department B WHERE A.departmentId = b.departmentId;

// OUTER JOIN: An outer join is the inverse of the inner join. It only returns those records
// not in T1 and T2. “Give me the records that DON’T have a match.” In programming logic,
// think in terms of NOT AND.

// A full outer join produces the set of all records in Table A and Table B, with matching 
// records from both sides where available. If there is no match, the missing side will contain null.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name;

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableA.id IS null OR TableB.id IS null

// To produce the set of records unique to Table A and Table B, we perform the same full 
// outer join, then exclude the records we don't want from both sides via a where clause.

// LEFT JOIN: A left join returns all the records in the “left” table (T1) whether they have 
// a match in the right table or not. If, however, they do have a match in the right table,
// give me the “matching” data from the right table as well. If not – fill in the holes with null.

// LEFT OUTER JOIN: A left outer join combines the ideas behind a left join and an outer join. 
// Basically – if you use a left outer join you will get the records in the left table that DO 
// NOT have a match in the right table.

SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

// Left outer join produces a complete set of records from Table A, with the matching records 
// (where available) in Table B. If there is no match, the right side will contain null.  To produce 
// the set of records only in Table A, but not in Table B, we perform the same left outer join, 
// then exclude the records we don't want from the right side via a where clause:

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableB.id IS null

// The result of a left outer join (or simply left join) for tables A and B always contains all rows 
// of the "left" table (A), even if the join-condition does not find any matching row in the "right"
// table (B). This means that if the ON clause matches 0 (zero) rows in B (for a given row in A), 
// the join will still return a row in the result (for that row)—but with NULL in each column from
// B. A left outer join returns all the values from an inner join plus all values in the left table that
// do not match to the right table, including rows with NULL (empty) values in the link column.

// For example, this allows us to find an employee's department, but still shows employees that
// have not been assigned to a department (contrary to the inner-join example above, where
// unassigned employees were excluded from the result). Example of a left outer join 
// (the OUTER keyword is optional), with the additional result row (compared with the inner join)
// italicized:

SELECT * FROM employee LEFT OUTER JOIN department 
ON employee.DepartmentID = department.DepartmentID;

// Theta JOIN:  A theta join is the Cartesian product of the 2 tables

SELECT t1.*, t2.* FROM table1 t1, table2 t2 WHERE t1.id = 5;

// CROSS JOIN: It is another name for Theta join

SELECT * FROM TableA CROSS JOIN TableB
SELECT * FROM employee CROSS JOIN department;
SELECT * FROM employee, department;

// NATURAL JOIN: It is another name for the regular inner join, however with NATURAL JOIN, 
// we do not specify the join conditions and it is determined automatically based on column 
// names that are common in both tables.

SELECT surname, first_name, country FROM persons NATURAL JOIN addresses;

// Natural joins between two tables without at least one common column results in cartesian product.

// With different data types but the same column names there might be type conversion problems
// and quite strange error messages.  

// The natural join is a special case of equi-join. A natural join is a type of equi-join where the join 
// predicate arises implicitly by comparing all columns in both tables that have the same 
// column-names in the joined tables. The resulting joined table contains only one column for each 
// pair of equally named columns. In the case that no columns with the same names are found, 
// the result is a cross join.

// Most experts agree that NATURAL JOINs are dangerous and therefore strongly discourage their 
// use. The danger comes from inadvertently adding a new column, named the same as another 
// column in the other table. An existing natural join might then "naturally" use the new column 
// for comparisons, making comparisons/matches using different criteria (from different columns) 
// than before. Thus an existing query could produce different results, even though the data in the
// tables have not been changed, but only augmented. The use of column names to automatically
// determine table links is not an option in large databases with hundreds or thousands of tables 
// where it would place an unrealistic constraint on naming conventions. Real world databases are
// commonly designed with foreign key data that is not consistently populated (NULL values are 
// allowed), due to business rules and context. It is common practice to modify column names of 
// similar data in different tables and this lack of rigid consistency relegates natural joins to a 
// theoretical concept for discussion.

// In many database environments the column names are controlled by an outside vendor, not 
// the query developer. A natural join assumes stability and consistency in column names which can 
// change during vendor mandated version upgrades.

// hash join or sort-merge join: algorithm for handling inner joins

// Equi-join: It is a join that uses only equality comparisons in the join predicate / condition. 
// Using other comparison operators (such as <) disqualifies a join as an equi-join.

// If columns in an equi-join have the same name, SQL-92 provides an optional shorthand notation 
// for expressing equi-joins, by way of the USING construct:

SELECT * FROM employee INNER JOIN department USING (DepartmentID);

// The USING construct is more than mere syntactic sugar, however, since the result set differs 
// from the result set of the version with the explicit predicate. Specifically, any columns mentioned
// in the USING list will appear only once, with an unqualified name, rather than once for each table 
// in the join. In the case above, there will be a single DepartmentID column and no 
// employee.DepartmentID or department.DepartmentID.

// The USING clause is not supported by MS SQL Server and Sybase.

// SELF-JOIN:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

SELECT id, name FROM product WHERE vendorID = (SELECT vendorID FROM product WHERE id='DTNTR');

// Find all products that has the same vendor as some given product ID (without given the vendor ID)
SELECT p1.id, p1.name FROM product AS p1, product AS p2 
WHERE p1.vendorID = p2.vendorID AND p2.id = 'DTNTR';

// Find all countries that have the same year of independence as some given country
SELECT t1.IndepYear, t1.Name, t2.Name FROM Country AS t1, Country AS t2 
WHERE t1.IndepYear = t2.IndepYear AND t1.Name = 'Qatar';

// STRAIGHT JOIN: Some database systems allow the user to force the system to read the tables
// in a join in a particular order. This is used when the join optimizer chooses to read the tables
// in an inefficient order. For example, in MySQL the command STRAIGHT_JOIN does an inner join
// but reads the tables in exactly the order listed in the query.

Total Recall
sqlldr
Oracle Enterprise Manager
Oracle Real Application Clusters (RAC)
Oracle ExaData
SQL Loader
Toad

Oracle Data Types

use CALL instead of EXEC

Various builtin functions

Toad / Oracle explain plan
How to give specific hints to the SQL optimizer
SELECT
The IN clause
UPDATE
DELETE
CREATE TABLE
ALTER TABLE
DROP TABLE
Data types
Session variables
Views
Materialized views
Sequences
Exploring the database
Index
Limits
Commenting
SQLPlus
Miscellaneous
PLSQL
Exception Handling
Large bulk insert or delete
Date
grant
XML
JSON
Regular Expression
The rownum pseudo-column
Administration
Performance
Getting information about your oracle database
SQL Developer

How can we use the IF condition?

See http://www.devx.com/tips/Tip/20063

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License