Oracle Interview

oracle

// 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.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License