Free Oracle Database SQL 1Z0-071 Exam Practice Test

Examine the data in the CUST_NAME column of the CUSTOMERS table:CUST_NAME---------------------Renske LadwigJason MallinSamuel McCainAllan MCEwenIrene MikkilineniJulia NayerYou want to display the CUST_NAME values where the last name starts with Mc or MC.Which two WHERE clauses give the required result?

• WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE UPPER('MC%')
  
• WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%' OR 'MC%'
  
• WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) IN ('MC%','Mc%')
  
• WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE 'Mc%'
  
• WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%'
  

Which two statements execute successfully?

• SELECT TO_ DATE('2019-DEC-25 15:30' 'YYYY-MON-DD HH24:MI', 'NLS_ DATE_ LANGUAGE
  =AMERICAN' ) FROM DUAL;
  
• SELECT TO_CHAR('2019-DEC-25 15:30' YY-MON-D HH24:M2', 'NLS_DATE LANGUAGE =
  AMERICAN')
  FROM DUAL;
  
• SELECT TO _DATE (TO_ CHAR ('2019-DEC-25 03:30', 'YYYY-MON-DD HH12:MI'))
  FROM DUAL;
  
• SELECT TO _ CHAR (TO_ DATE ('2019-DEC-25 03:30','YYYY-MON-DD HH12:MI'))
  FROM DUAL
  
• SELECT TO _ CHAR ('2019-DEC-25 15:30'.'YYYY-MON-DD HH24:MI')
  FROM DUAL
  

Examine this query:SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?

• 16
  
• 160
  
• 150
  
• 200
  
• 100
  

In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR.Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query?

• TO_NUMBER(PROMO_BEGIN_DATE)-5 will return number
  
• TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
  
• PROMO_BEGIN_DATE-SYSDATE will return a number.
  
• PROMO_BEGIN_DATE-5 will return a date.
  
• PROMO_BEGIN_DATE-SYSDATE will return an error.
  

Which three are true about dropping columns from a table?

• A column can be removed only if it contains no data.
  
• A column drop is implicitly committed
  
• A column that is referenced by another column in any other table cannot be dropped.
  
• A column must be set as unused before it is dropped from a table.
  
• A primary key column cannot be dropped.
  
• Multiple columns can be dropped simultaneously using the ALTER TABLE command.
  

Which three are true about the MERGE statement

• It can merge rows only from tables.
  
• It can use views to produce source rows.
  
• It can combine rows from multiple tables conditionally to insert into a single table.
  
• It can use subqueries to produce source rows.
  
• It can update the same row of the target table multiple times.
  
• It can update, insert, or delete rows conditionally in multiple tables.
  

Which three statements are true about the Oracle join and ANSI Join syntax?

• The Oracle join syntax only supports right outer joins,
  
• The Oracle join syntax supports creation of a Cartesian product of two tables.
  
• The SQL:1999 compliant ANSI join syntax supports natural joins.
  
• The Oracle join syntax supports natural joins.
  
• The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
  
• The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
  
• The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.
  

Examine these requirements:1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000.2. Sort the titles by date of purchase, starting with the most recently purchased book.Which two queries can be used?

• SELECT book_title FROM books WHERE (price< 500 OR >1000) AND (purchase date< '17-JAN-2007') ORDER BY purchase date DESC;
  
• SELECT book_title FROM books WHERE (price IN (500, 1000)) AND (purchase date < '17-JAN-2007') ORDER BY purchase_date ASC;
  
• SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND (purchase_date< '17-JAN-2007') ORDER BY purchase_date DESC;
  
• SELECT book_title FROM books WHERE (price BETWEEN 500 AND 1000) AND (purchase_date<'17-JAN-2007') ORDER BY purchase_date;
  

Examine the description of the CUSTOMERS table:Which three statements will do an implicit conversion?

• SELECT * FROM customers WHERE insert_date=DATE'2019-01-01';
  
• SELECT * FROM customers WHERE customer_id='0001';
  
• SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE'2019-01-01';
  
• SELECT * FROM customers WHERE insert_date'01-JAN-19';
  
• SELECT * FROM customers WHERE customer_id=0001;
  
• SELECT * FROM customers WHERE TO_CHAR(customer_id)='0001';
  

Which three statements are true about indexes and their administration in an Oracle database?

• An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
  
• An index can be created as part of a CREATE TABLE statement.
  
• A DROP INDEX statement always prevents updates to the table during the drop operation
  
• A UNIQUE and non-unique index can be created on the same table column
  
• A descending index is a type of function-based index
  
• If a query filters on an indexed column then it will always be used during execution of the query