RR, YY and YYYY masks will interpret variable length years, means you can specify both two-digit or four-digit format as shown below, and it will work. They all produce the same result It worth to remember, because you may expect similar situation on the exam where the mask is RR or YY, but […]
Monthly Archives: October 2017
I’ve found this strange example in Roopesh Ramklass book. According to him, it may work very well, but it won’t actually. See below BETWEEN requires two values to specify the range. If an older date will be specified first, then no rows will be selected as shown below But, if […]
Is it possible to use a Reserved Word as an alias? The answer is YES. But you need to double quote this Reserved Word, otherwise, it will return an error. The SQL statement below will work just fine select sysdate as "SYSDATE" from dual; While this statement won’t select sysdate as […]
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 […]