Comunidad Oracle Hispana

Pregunta: 1

Which statement is true regarding the INTERSECT operator?

 

A.  It ignores NULL values.

B.  Reversing the order of the intersected tables alters the result.

C. The names of columns in all SELECT statements must be identical.

D. The number of columns and data types must be identical for all SELECT statements in the query.

 

Pregunta: 2

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)

 

A. The value for a CHAR data type column is blank-padded to the maximum defined column width

B. A TIMESTAMP data type column stores only time values with fractional seconds.

C. The BLOB data type column is used to store binary data in an operating system file.

D. The minimum column width that can be specified for a VARCHAR2 data type column is one.

E. Only one LONG column can be used per table.

 

 

Pregunta: 3

Examine the structure of the COURSES table:

Name Null?          Type

---------- ------------- ---------------

COURSE_ID    NOT NULL     NUMBER(3)

COURSE _COST               NUMBER(8,2)

START_DATE   NOT NULL     DATE

END_DATE                   DATE

 

Which two SQL statements would execute successfully? (Choose two.)

 

A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM course;

B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)) FROM course;

C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM course;

D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Continuing') FROM course;

 

Pregunta: 4

View the Exhibit and examine the structure of the PROMOTIONS table.

Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 PROMO_ID                                  NOT NULL NUMBER(6)

 PROMO_NAME                                NOT NULL VARCHAR2(30)

 PROMO_SUBCATEGORY                         NOT NULL VARCHAR2(30)

 PROMO_SUBCATEGORY_ID                      NOT NULL NUMBER

 PROMO_CATEGORY                            NOT NULL VARCHAR2(30)

 PROMO_CATEGORY_ID                         NOT NULL NUMBER

 

Which two SQL statements would execute successfully? (Choose two.)

 

A. UPDATE promotions

SET promo_cost = promo_cost+ 300

WHERE TO_CHAR(promo_end_date, 'yyyy') > '2011';

B. SELECT promo_begin_date

FROM promotions

WHERE TO_CHAR(promo_begin_date,'mon dd yy')='may 04 10';

C. UPDATE promotions

SET promo_cost = promo_cost+ 100

WHERE promo_end_date > TO_DATE(SUBSTR('01-JUN-2009',8));

D. SELECT TO_CHAR(promo_begin_date,'dd/month')

FROM promotions

WHERE promo_begin_date IN (TO_DATE('JUN 01 99'), TO_DATE('SEP 01 10'));

 

Pregunta: 5

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

SQL> desc sales

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------

 PROD_ID                                   NOT NULL NUMBER

 CUST_ID                                   NOT NULL NUMBER

 TIME_ID                                   NOT NULL DATE

 CHANNEL_ID                                NOT NULL NUMBER

 PROMO_ID                                  NOT NULL NUMBER

 

SQL> desc  products

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------

 PROD_ID                                   NOT NULL NUMBER(6)

 PROD_NAME                                 NOT NULL VARCHAR2(50)

 PROD_DESC                                 NOT NULL VARCHAR2(4000)

 PROD_CATEGORY                             NOT NULL VARCHAR2(50)

 PROD_CATEGORY_ID                          NOT NULL NUMBER

 PROD_CATEGORY_DESC                        NOT NULL VARCHAR2(2000)

 

SQL> desc costs

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------

 PROD_ID                                   NOT NULL NUMBER

 TIME_ID                                   NOT NULL DATE

 PROMO_ID                                  NOT NULL NUMBER

 CHANNEL_ID                                NOT NULL NUMBER

 UNIT_COST                                 NOT NULL NUMBER(10,2)

 UNIT_PRICE                                NOT NULL NUMBER(10,2)

 

Evaluate the following SQL statement:

 

SQL>  SELECT prod_id FROM products

INTERSECT

SELECT prod_id FROM sales

MINUS

SELECT prod_id FROM costs;

 

Which statement is true regarding the above compound query?

 

A. It produces an error.

B. It shows products that were sold and have a cost recorded.

C. It shows products that were sold but have no cost recorded.

D. It shows products that have a cost recorded irrespective of sales.

 

Pregunta: 6

View the ER diagram and examine the structure of CUSTOMERS and SALES tables.

 

 

Evaluate the following SQL statement:

 

UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id

FROM sales)

SET time_id = '15-MAY-2010'

WHERE cust_id = (SELECT cust_id

FROM customers

WHERE cust_last_name = 'Ericsson' AND

credit_limit = 4000);

Which statement is true regarding the execution of the above UPDATE statement?

 

A. It would not execute because two tables cannot be used in a single UPDATE statement.

B. It would not execute because the SELECT statement cannot be used in place of the table name.

C. It would execute and restrict modifications to only the columns specified in the SELECT statement.

D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

 

Pregunta: 7

You are currently located in Boston and have connected to a remote database in Mexico.

You issue the following command:

 

SQL> SELECT ROUND(SYSDATE - course_begin_date,0)

FROM courses

WHERE (SYSDATE-course_begin_date)/365 > 2;

 

COURSE is the public synonym for the public database link for the COURSES table.

 

What is the outcome?

A. an error because the ROUND function specified is invalid

B. an error because the WHERE condition specified is invalid

C. number of days since the promo started based on the current Boston date and time

D. number of days since the promo started based on the current Mexico date and time

 

Pregunta: 8

You need to display the first names of all customers from the STUDENTS table that contain the character 'u' and have the character 'e' in the second last position. Which query would give the required output?

 

A. SELECT first_name

FROM students

WHERE INSTR(first_name, 'u')<>0 AND

SUBSTR(first_name, -2, 1)='e';

B. SELECT first_name

FROM students

WHERE INSTR(first_name, 'u')<>'' AND

SUBSTR(first_name, -2, 1)='e';

C. SELECT first_name

FROM students

WHERE INSTR(first_name, 'u')IS NOT NULL AND

SUBSTR(first_name, 1,-2)='e';

D. SELECT first_name

FROM students

WHERE INSTR(first_name, 'u')<>0 AND

SUBSTR(first_name, LENGTH(first_name),-2)='e';

 

Pregunta: 9

Evaluate the following query:

SQL> SELECT TRUNC(ROUND(178.00,-1),-1) FROM DUAL;

 

What would be the outcome?

A. 18

B. 100

C. 180

D. 200

E. 170

 

Pregunta: 10

Which two statements are true regarding the COUNT function? (Choose two.)

 

A.         The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

B.         COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.

C.         COUNT(employee_id) returns the number of rows including rows with duplicate employee IDs and NULL value in the EMPLOYEE_ID column

D.         COUNT(DISTINCT department)returns the number of rows excluding rows containing duplicates and NULL values in the DEPARTMENT column.

E.         A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

 

Pregunta: 11

View the tables and examine the structures of the PROMOTIONS and SALES tables.

Evaluate the following SQL statement:

SQL>SELECT p.promo_id, p.promo_name, s.prod_id

FROM sales s RIGHT OUTER JOIN promotions p

ON (s.promo_id = p.promo_id);

 

Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 PROMO_ID                                  NOT NULL NUMBER(6)

 PROMO_NAME                                NOT NULL VARCHAR2(30)

 PROMO_SUBCATEGORY                         NOT NULL VARCHAR2(30)

 PROMO_SUBCATEGORY_ID                      NOT NULL NUMBER

 PROMO_CATEGORY                            NOT NULL VARCHAR2(30)

 PROMO_CATEGORY_ID                         NOT NULL NUMBER

 PROMO_COST                                NOT NULL NUMBER(10,2)

 PROMO_BEGIN_DATE                          NOT NULL DATE

 PROMO_END_DATE                            NOT NULL DATE

 

SQL> desc sales

 Name                                      Null?    Type

 ----------------------------------------- -------- ---------------

 PROD_ID                                   NOT NULL NUMBER

 CUST_ID                                   NOT NULL NUMBER

 TIME_ID                                   NOT NULL DATE

 CHANNEL_ID                                NOT NULL NUMBER

 PROMO_ID                                  NOT NULL NUMBER

 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)

 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

 

 

Which statement is true regarding the output of the above query?

A. It gives the details of promos for which there have been sales.

B. It gives the details of promos for which there have been no sales.

C. It gives details of all promos irrespective of whether they have resulted in a sale or not.

D. It gives details of product IDs that have been sold irrespective of whether they had a promo ornot.

 

Pregunta: 12

View the description and examine the structure of the EMPLOYEES table.

Evaluate the query statement:

SQL> SELECT EMP_last_name, EMP_city, EMP_salary

FROM employees

WHERE EMP_last_name BETWEEN 'A' AND 'F' AND

EMP_salary BETWEEN 6000 AND 8500;

 

Name

Null?

Type

EMP_ID

NOT NULL

NUMBER

EMP_FIRST_NAME

NOT NULL

VARCHAR2(20)

EMP_LAST_NAME

NOT NULL

VARCHAR2(40)

COUNTRY_ID

NOT NULL

NUMBER

EMP_MAIN_PHONE_NUMBER

NOT NULL

VARCHAR2(25)

EMP_INCOME_LEVEL


VARCHAR2(30)

EMP_SALARY


NUMBER

 

What would be the outcome of the above statement?

 

A.     It produces an error because the condition on EMP_LAST_NAME is invalid.

B.     It executes successfully only if the EMP_SALARY column does not contain any null values.

C.     It produces an error because the AND operator cannot be used to combine multiple BETWEEN clauses.

D.     It executes successfully.

 

 

 

Pregunta: 13

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)

 

A.     A maximum of one pair of columns can be joined between two tables using the ON clause.

B.     The ON clause can be used to join tables on columns that have different names but compatible data types.

C.     The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

D.     Both USING and ON clauses can be used for equijoins and no equijoins.

 

 

Pregunta: 14

Where can subqueries be used? (Choose all that apply.)

A.     field names in the SELECT statement

B.     the FROM clause in the SELECT statement

C.     the HAVING clause in the SELECT statement

D.     the GROUP BY clause in the SELECT statement

E.     the WHERE clause in only the SELECT statement

F.     the WHERE clause in SELECT as well as all DML statements

 

Pregunta: 15

Using the EMPLOYEES table, you need to generate a report that shows 90% of each salary amount  in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

 

A. SELECT DISTINCT EMP_income_level || ' ' || EMP_salary * 0.90

AS "50% Credit Limit"

FROM employees;

B. SELECT EMP_income_level ||' '|| EMP_salary * 0.90 AS "90%

Credit Limit" FROM employees;

 

C. SELECT EMP_income_level, DISTINCT EMP_salary * 0.90

AS "50% Credit Limit"

FROM employees;

D. SELECT DISTINCT EMP_income_level, DISTINCT EMP_salary * 0.90

AS "50% Credit Limit"

FROM employees;

Pregunta: 16

Which statement is true regarding the UNION operator?

A.     By default, the output is not sorted.

B.     Names of all columns must be identical across all SELECT statements.

C.     The number of columns selected in all SELECT statements need not be the same.

D.     NULL values are not ignored during duplicate checking.

 

Pregunta: 17

Which two statements are true regarding working with dates? (Choose two.)

A.     The default internal storage of dates is in the character format.

B.     The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century.

C.     The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.

D.     The default internal storage of dates is in the numeric format.

 

 

 

Pregunta: 18

The ORDERS table belongs to the user HR. HR has granted the SELECT privilege on the JOBS  table to the user SH. Which statement would create a synonym JOB so that SH can execute the following query successfully?

SELECT * FROM job;

A.     CREATE SYNONYM job FOR orders; This command is issued by HR.

B.     CREATE PUBLIC SYNONYM job FOR orders; This command is issued by HR.

C.     CREATE SYNONYM job FOR hr.jobs; This command is issued by the database administrator.

D.     CREATE PUBLIC SYNONYM job FOR hr.jobs; This command is issued by the database administrator.

 

Pregunta: 19

View the Exhibit and examine the structure of the ITEMS table.

Which SQL statements are valid? (Choose all that apply.)

 

Name

Null?

Type

ITEM_ID

NOT NULL

NUMBER(6)

ITEM_NAME

NOT NULL

VARCHAR2(30)

ITEM_SUBCATEGORY

NOT NULL

VARCHAR2(30)

ITEM_SUBCATEGORY_ID

NOT NULL

NUMBER

ITEM_CATEGORY

NOT NULL

VARCHAR2(30)

ITEM_CATEGORY_ID

NOT NULL

NUMBER

ITEM_COST

NOT NULL

NUMBER(10,2)

ITEM_BEGIN_DATE

NOT NULL

DATE

ITEM_END_DATE

NOT NULL

DATE

ITEM_TOTAL

NOT NULL

VARCHAR2(15)

ITEM_TOTAL_ID

NOT NULL

NUMBER

 

 

A. SELECT item_id, DECODE(NVL(item_cost,0), item_cost,

item_cost * 0.15, 100) "Discount"

FROM items;

B. SELECT item_id, DECODE(item_cost, 200,

DECODE(item_category, 'NOKIA 101', item_cost *.15, NULL),

NULL) "Catcost"

FROM items;

C. SELECT item_id, DECODE(NULLIF(item_cost, 200),

NULL, item_cost*.15, 'N/A') "Catcost"

FROM items;

D. SELECT item_id, DECODE(item_cost, >200, 'High',

<10000, 'Low') "Range"

FROM items;

 

Pregunta: 20

Evaluate the following SQL statement:

SQL> SELECT EMP_id, EMP_last_name

FROM employees

WHERE EMP_salary IN

(select EMP_salary

FROM employees

WHERE EMP_city ='Lima');

Which statement is true regarding the above query if one of the values generated by the subquery is NULL?

A.     It produces an error.

B.     It generates output for NULL as well as the other values produced by the subquery.

C.     It executes but returns no rows.

A.     It ignores the NULL value and generates output for the other values produced by the subquery.

 

Pregunta: 21

View the table description and examine the structure of the CONTRIES  table.

 

Name

Null?

Type

COUNTRY_ID                               

NOT NULL

CHAR(2)

COUNTRY_NAME                                      


VARCHAR2(50)

REGION_ID                                         

NOT NULL

NUMBER

 

You need to generate a report in the following format:

 

REGIONS

---------------------------------------------

United Kingdom's region is 1

Singapore's region is 3

Nigeria's region is 4

Which two queries would give the required output? (Choose two.)

 

A. SELECT country_name || q'''s category is ' || region_id REGIONS

FROM products;

B. SELECT country_name || q'['s ]'region is ' || region_id REGIONS

FROM products;

C. SELECT country_name || q'\'s\' || ' region is ' || region_id REGIONS

FROM products;

D. SELECT country_name || q'<'s >' || ‘region is ' || region_id REGIONS

FROM countries;

 

Pregunta: 22

The  FILE_NAME column in the FILES table contains the following list of values:

 

FILE_NAME

-----------------

SP%_ZX966

S%PZX966

SP_ZX966

Evaluate the following query:

SQL> SELECT part_code

FROM spares

WHERE part_code LIKE '%\%_ZX96%' ESCAPE '\';

Which statement is true regarding the outcome of the above query?

B.     It produces an error.

C.     It displays only the values S%_ZX966 and S%PZX966.

D.     It displays all values.

E.     It displays only the values S%_ZX966 and SP_ZX966.

F.     It displays only the values S%BZX966 and SP_ZX966.

 

Visitas: 11736

Respuestas a esta discusión


Opción C

ROOPESH nunca vera los cambios de data que hizo JOHN, es decir vera las versiones antiguas de las filas hasta JOHN haga commit. La opción D no se entiende, pero tampoco es valida porque hace referencia a los SAVEPOINTS que solo se manejan en una sola sesión.

Saludos,


saul san miguel dice:

Me he encontrado con estas preguntas en los simuladores para la certificación y no he podido encontrar la respuesta me apoyan:

 

User JOHN updates some rows but does not commit the changes. User ROOPESH queries the

rows that JOHN updated. Which of the following statements is true? (Choose the correct answers.)

A.- ROOPESH will not be able to see the rows because they will be locked.

B.- ROOPESH will be able to see the new values, but only if he logs in as JOHN.

C.- ROOPESH will see the old versions of the rows.

D.-ROOPESH will see the state of the state of the data as it was when JOHN last created

a SAVEPOINT.

RSS

Siguenos en Twitter

Escucha nuestro podcast!

Eventos

Insignia

Cargando…

© 2017   Creado por Fernando Garcia.   Tecnología de

Insignias  |  Informar un problema  |  Términos de servicio