1Z0-061 Oracle SQL

get through 1Z0-061 exam
  • Home
  • Sources used for preparation
  • Examination Score Report
  • Search

1Z0-061 Oracle SQL

  • Home
  • Sources used for preparation
  • Examination Score Report

Adding a NOT NULL column to existing non-empty table

If you have a table with existing data ( as shown below) and you’ve been asked ...

Read more »

HAVING with nested aggregation won't work

Rules for HAVING clause is not so easy as it may look like at first glance. It is ...

Read more »

DECODE is type sensitive

DECODE output may be type sensitive Assuming current months is MAY Since MM format ...

Read more »

COUNT(NULL)

NULL is not determinate value and is not counted when the COUNT function As we can ...

Read more »

ANY and ALL

The following table summarizes the equivalents for ANY and ALL:   Let’s ...

Read more »

IN NULL vs IS NULL

This is one more common mistake the one can make when it comes to NULL engaged with ...

Read more »

NULL and COUNT(DISTINCT )

NULL is not determinate value and is not counted when the COUNT function is used ...

Read more »

The operator "=ANY" is equivalent to IN operator

These two queries will produce identical results: IN   =ANY    

Read more »

You can have only one LONG field per table

Oracle won’t let you create a table with more than one LONG data type. Only ...

Read more »

LOB data types won't allow you ORDER BY or GROUP BY be applied over it

You can’t have LOB  data types (CLOB, BLOB, BFILE) ordered. Oracle will trigger ...

Read more »

Modify existing column with DEFAULT value

When you modify existing column with DEFAULT value, then modification to add a DEFAULT ...

Read more »

NLS_FORMAT_DATE default format is DD-MON-RR

It is a known fact that NLS_FORMAT_DATE default format is DD-MON-RR, but the date ...

Read more »

SYSDATE vs DATE and NLS_DATE_FORMAT

I’ve found this example very interesting and making you understand DATE data ...

Read more »

DISTINCT and UNIQUE

To suppress duplicates in a SELECT statement both clauses DISTINCT and UNIQUE can ...

Read more »

ALIAS is not allowed in HAVING clause, GROUP BY and WHERE clause. Allowed in ORDER ...

Alias is not allowed in HAVING clause and this is why. Again, we have to consider ...

Read more »

ORDER BY is not allowed in SUBQUERY

It does not make any sense to sort data before passing them to an outer query from ...

Read more »

INSERT ALL vs INSERT FIRST

INSERT ALL Since Oracle 9 we can perform multi-table inserts, allowing us to distribute ...

Read more »

Updates Based on Queries

SUBQUERY METHOD    INLINE VIEW METHOD

Read more »

Second highest in the table

There are some questions on the exam to verify your knowledge about finding the second ...

Read more »

IN vs EXISTS

Both techniques are going to be tested during the exam. IN and EXISTS are not the ...

Read more »

Grouping attributes may not be present in SELECT statement

It is normal if some or all grouping attributes will not be present in SELECT statement. ...

Read more »

Nested Group Functions

NESTED GROUP FUNCTIONS (2 levels deep) Group functions may only be nested two levels ...

Read more »

SUBQUERY within VALUES clause?

The common perception is that SUBQUERY can’t be used inside the VALUES clause. ...

Read more »

VIEW vs TABLE querying. What is faster?

You may have asked yourself will VIEW affect performance while querying from it if ...

Read more »

RENAME a table

If you wish to rename a table, you may have to remember some obstacles here. For ...

Read more »

TO_NUMBER needs a dot, not a comma

Pay attention, TO_NUMBER needs a dot, not a comma On the exam, you will be limited ...

Read more »

CURVAL and NEXTVAL

The sequence keywords CURVAL and NEXTVAL can’t be used in: the condition ...

Read more »

Subquery with group function in SELECT statement

It may look strange from the first glance, but it will work actually. Subquery will ...

Read more »

INSERT a NUMBER(p,s) value that does not fit precision or scale

It is an interesting example that deserves to be considered since it is a guaranteed ...

Read more »

DEFAULT NOT NULL

If you have a column with NOT NULL restriction and at the same time this same column ...

Read more »

TO_CHAR to CHAR, TO_DATE to DATE, TO_NUMBER to NUMBER

You can’t apply a TO_CHAR() function to an argument with CHAR data type because 10-JAN-16 is ...

Read more »

IS NOT NULL vs

You can’t apply <> operator to fetch NON NULL data. It won’t return any ...

Read more »

DEFAULT ON NULL

This is Oracle 12c feature. Prior to 12c, the DEFAULT for a column would not be ...

Read more »

Add a column with DEFAULT VALUE

This is another tricky example that worth your attention. When you adding a column ...

Read more »

LISTAGG

The LISTAGG analytic function was not covered in Roopesh Ramklass book, but it ...

Read more »

INLINE VIEW

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In ...

Read more »

A Non-Equijoin with no corresponding columns

A non-equijoin is represented by the use of an operator other than equality operator. ...

Read more »

SUM(1) vs SUM(DISTINCT 1)

This is  a tricky behavior of SUM() function which is going to be tested for sure ...

Read more »

Rollback to commited Savepoint gives error

Once commit is executed, the update is made permanent. Savepoint A is lost after ...

Read more »

Automatic type casting won't utilize INDEX

SQL is a strongly typed language: columns are defined as a certain data type, and ...

Read more »

NOT IN and NULL

Using NOT IN is fraught with problems because of the way SQL handles NULLs. As a ...

Read more »

Subquery usage with ANY, ALL, IN and =, >, >=,

This is a typical situation that can confuse an average student on the exam. We all ...

Read more »

Oracle substitutes values from SYSDATE function when incomplete date is provided

Most likely you may have this situation on the exam. It is incomplete date format ...

Read more »

The format mask can be stretched out but can't be contracted

When we are using a format mask ( for example with TO_NUMBER function ) we have basically ...

Read more »

COALESCE requires same data type for its arguments

The COALESCE function returns any datatype such as a string, numeric, date, etc. ...

Read more »

RR , YY and YYYY will interpret variable length years

  RR, YY and YYYY masks will interpret variable length years, means you can ...

Read more »

BETWEEN with reversed range won't work

I’ve found this strange example in Roopesh Ramklass book. According to him, ...

Read more »

Alias as a Reserved Word / Keyword

Is it possible to use a Reserved Word as an alias? The answer is YES. But you need ...

Read more »

Implicit COMMIT vs Implicit ROLLBACK

Implicit COMMIT is normal behavior for Oracle. In certain situations, Oracle will ...

Read more »

Subqueries can be used in INTO and VALUES part of the INSERT statement

This weird INSERT statement below will work The first subquery will replace traditional column ...

Read more »

DEFAULT inside INSERT statement

  If columns are explicitly provided in a column list, then all columns listed ...

Read more »

Substitution variables inside INSERT statement

Substitution variables can be placed inside INSERT statements to create reusable ...

Read more »

Using table aliases and real tables names in one SQL query is forbidden

Tables aliases have been specified for all tables in the FROM clause of this SELECT ...

Read more »

NON-EQUIJOIN based on condition rather than column

This is a 100% guaranteed situation on the exam. We all know how to join tables based ...

Read more »

NATURAL JOIN can join tables with no common column names

It is a well-known fact that NATURAL JOIN joins the two tables using all columns ...

Read more »

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

When you encounter an ORA-01719 error, the following error message will appear: ORA-01719: ...

Read more »

CONCAT function requires exactly two arguments

CONCAT function requires exactly two arguments and it seems obvious. But, when you ...

Read more »

NULLIF (NULL, NULL)

you can’t specify NULL for exp1 in NULLIF function select NULLIF(NULL, NULL) ...

Read more »

TIMESTAMP WITH TIME ZONE data type

TIMESTAMP WITH TIME ZONE contains all of the values of TIMESTAMP as well as time ...

Read more »

What is a common theme that causes people to fail the 1Z0-061 exam?

Time. Candidates often either run out of time before finishing every question or ...

Read more »

NUMBER data type

NUMBER is a numeric data type that can optionally have a precision and scale specified ...

Read more »

SELECT and HAVING COUNT(*) default behavior

select customer_id  from orders group by customer_id having count(*) > 4; Question: ...

Read more »

LONG data type and SET operators

Can LONG columns be used in queries joined by set operators? The UNION, INTERSECT, ...

Read more »

NVL requires argument data type compatibility when it necessary, but not always

NVL function requires data type match. If you try to evaluate NUMBER data with NVL, ...

Read more »

when SUBSTR returns NULL?

SUBSTR will always return NULL if number of characters to retrieve was specified ...

Read more »

LOB data types do not require length specification

What is wrong with the above statement? The Answer: It is not legitimate to specify ...

Read more »

ALIAS cannot be used within WHERE clause

ALIAS cannot be used within WHERE clause. It produces an error like this. Just remember ...

Read more »

NULL comparison won't work

NULL values cannot be compared to any value, comparison operators such as equal ...

Read more »

comparison operator and its equivalents

We all know that != operator is equivalent to <> operator. But there is another ...

Read more »

ADD_MONTHS(SYSDATE, -1)

This is a typical error that many students do: The right answer is “07-MAY-1996”. ...

Read more »

FULL OUTER JOIN won't work with traditional Oracle syntax

Unfortunately, FULL OUTER JOIN does not work with traditional Oracle join syntax. ...

Read more »

DEFERRABLE INITIALLY IMMEDIATE

DEFERRABLE INITIALLY IMMEDIATE will check constraint violation after each statement ...

Read more »

DEFERRABLE INITIALLY DEFERRED

  The general rule about DEFERRABLE constraints : [NOT DEFERRABLE] is default, ...

Read more »

ORDER BY can't be used within subquery

From first glance, this subquery looks absolutely legit. Unfortunately, it won’t ...

Read more »

VALUES keyword can not be used with a subquery

It looks like you have a very big chance of getting this question on your exam. In ...

Read more »

DEFAULT seq.NEXTVAL

It is a new feature in 12c – now you can use sequence while creating a column. In ...

Read more »

CHECK (expiry_date > sysdate)

You can’t use SYSDATE within CHECK condition. SYSDATE it is a pseudo-column ...

Read more »

Sources used for preparation

Find out about the sources that have been used while preparing this material

Read more »
Holder Thumbnail

Featured page

Featured page description text : use the page excerpt or set your own custom text in the Customizr screen.

Read more »
Holder Thumbnail

Featured page

Featured page description text : use the page excerpt or set your own custom text in the Customizr screen.

Read more »

If you have a table with existing data ( as shown below) and you’ve been asked to add a NOT NULL column to a table that already has data populated, it is not that simple as may seem at first glance. It did not work because the table must have […]

Adding a NOT NULL column to existing non-empty table

HAVING with nested aggregation won’t work

Rules for HAVING clause is not so easy as it may look like at first glance. It is an assumption that HAVING clause can contain the same grouping/aggregation as SELECT and it will work just fine because the role of HAVING is to filter whatever has left after aggregation done […]

HAVING with nested aggregation won’t work

DECODE is type sensitive

DECODE output may be type sensitive Assuming current months is MAY Since MM format will return ’05’ it won’t match ‘5’ as shown below select decode(to_char(sysdate,'MM'),'05','MAY','other month') from dual; --MAY select decode(to_char(sysdate,'MM'),'5','MAY','other month') from dual; -- other month At the same time, if 05 or 5 will be used with […]

DECODE is type sensitive

NULL is not determinate value and is not counted when the COUNT function As we can see when you supply NULL explicitly to COUNT it will produce 0, even if the query was executed against dual table. In plain words, you can translate this operation as “nothing to count” since […]

COUNT(NULL)

The following table summarizes the equivalents for ANY and ALL:   Let’s have a look for each case: < ANY  ( LESS THAN THE HIGHEST ) select * from employees where salary < ANY (select salary from employees where department_id = 80) ; The query will show all employees with […]

ANY and ALL

IN NULL vs IS NULL

This is one more common mistake the one can make when it comes to NULL engaged with comparison Consider these two statements, they will produce different results: select * from emp where commission_pct in (0.1, NULL) ; select * from emp where commission_pct = 0.1 or commission_pct IS NULL ; […]

IN NULL vs IS NULL

NULL is not determinate value and is not counted when the COUNT function is used with the DISTINCT keyword For instance, if you SELECT DISTINCT MANAGER_ID from EMPLOYEES; you will get 19 rows with NULL among them.     However, if you do SELECT COUNT(DISTINCT MANAGER_ID) from EMPLOYEES; you will get […]

NULL and COUNT(DISTINCT )

These two queries will produce identical results: IN   =ANY    

The operator “=ANY” is equivalent to IN operator

Oracle won’t let you create a table with more than one LONG data type. Only one LONG is allowed per table. And of course, you can’t make GROUP BY or ORDER BY operations over LONG data type fields ( similar to any LOB data type like CLOB, BLOB and BFILE) […]

You can have only one LONG field per table

You can’t have LOB  data types (CLOB, BLOB, BFILE) ordered. Oracle will trigger an error when you try to order on LOB fields Same with grouping. You can’t apply GROUP BY on LOB fields. It will raise the same error.  

LOB data types won’t allow you ORDER BY or GROUP …

Posts navigation

    • 1
    • 2
    • …
    • 8
  • Older posts Older posts
Widget 1
  • Examination Score Report
  • Sources used for preparation

© 2023 1Z0-061 Oracle SQL – All rights reserved

Designed by Press Customizr – Powered by