An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In the query below the INLINE VIEW is : (SELECT AVG(salary) avg_salary from employees ) Inline views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query. So, the purpose of INLINE VIEW is to […]
transcender.com
15 posts
A non-equijoin is represented by the use of an operator other than equality operator. A non-equijoin is used when no corresponding columns exist between the tables in the query, but rather a relationship exists between two columns having compatible data types. Several conditions can be used to define a non-equijoin, […]
Implicit COMMIT is normal behavior for Oracle. In certain situations, Oracle will commit everything automatically without you being involved. It is called Implicit COMMIT. When will it happen? It happens if someone has issued any DCL command (GRANT, REVOKE) or any DDL command (CREATE, ALTER, DROP, TRUNCATE, RENAME). Implicit commit may […]
This weird INSERT statement below will work The first subquery will replace traditional column specification, while the second subquery will replace VALUES part of the INSERT statement. Looks weird, but it works. The situation with SQL queries/subqueries in INSERT statement is 100% guaranteed on your exam.
If columns are explicitly provided in a column list, then all columns listed must be included in the VALUES clause with an explicit value, NULL, or DEFAULT. Let’s consider DEFAULT because it has much more complex behavior if compared to explicit value or NULL. It may or may not […]
Substitution variables can be placed inside INSERT statements to create reusable scripts. You can place single ampersand (&) and double ampersand (&&) variables as shown below You can also use substitution variables to replace column names as shown below
Tables aliases have been specified for all tables in the FROM clause of this SELECT statement. After they have been specified, these table aliases must be used. You can’t mix tables aliases with real table names as shown below. It will generate an error. Instead, you should have done the following […]
This is a 100% guaranteed situation on the exam. We all know how to join tables based on equijoin conditions, but non-equijoin will also be tested. Moreover, tests become more and more complex. They include more logic than before. Therefore, be prepared for questions containing 3 or 4 tables being joined. […]
It is a well-known fact that NATURAL JOIN joins the two tables using all columns with the same name. But if two tables have no columns with the same name, then cross product is produced. Let’s have a look for these two tables – they don’t have any columns with the […]
CONCAT function requires exactly two arguments and it seems obvious. But, when you encounter some complex SQL queries like one below, you can easily forget about this rule and make mistake during the test. As we can see, || operator turned two SUBSTR results into one, therefore CONCAT received only one […]