Dproviding update capabilities for data in external files
Eproviding variable definition capabilities
Examine this statement:
On which two columns of the table will an index be created automatically? (Choose two.)
AORDER_ID
BORDER_TOTAL
CORDER_DATE
DPRODUCT_ID
ESTATUS
FSERIAL_NO
Which two are true about creating tables in an Oracle database? (Choose two.)
ACreating an external table will automatically create a file using the specified directory and file name.
BA system privilege is required.
CThe same table name can be used for tables in different schemas.
DA primary key constraint is mandatory.
EA CREATE TABLE statement can specify the maximum number of rows the table will contain.
Examine these statements executed in a single Oracle session:
Which three statements are true? (Choose three.)
AThe code for pen is 10.
BThere is no row containing fountain pen.
CThere is no row containing pen.
DThere is no row containing pencil.
EThe code for fountain pen is 3.
FThe code for pen is 1.
Which two are true about granting privileges on objects? (Choose two.)
AAn object privilege can be granted to other users only by the owner of that object.
BThe owner of an object acquires all object privileges on that object by default.
CThe WITH GRANT OPTION clause can be used only by DBA users.
DAn object privilege can be granted to a role only by the owner of that object.
EA table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.
Examine this partial statement:
Which is true?
ABoth the query and the subquery can select only zero rows or one row.
BBoth the query and the subquery can select any number of rows.
CThe query can select only zero rows or one row, but the subquery can select any number of rows.
DThe query can select any number of rows, but the subquery can select only zero rows or one row.
Examine this statement which executes successfully:
Which is true?
AOnly if the salary is 20000 or less and the employee id is 125 or higher, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
BRegardless of salary and employee id, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
CRegardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
DOnly if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.
Examine the description of the BOOKS_TRANSACTIONS table:
Examine this partial SQL statement:
SELECT * FROM books_transactions
Which two WHERE conditions give the same result? (Choose two.)
AWHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');
BWHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
CWHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');
DWHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101', 'A102');
EWHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101', 'A102');
Which two statements are true about Oracle synonyms? (Choose two.)
AUsers must have the required privileges on the underlying objects to use public synonyms.
BSynonyms can be created for roles.
CSynonyms cannot be created for sequences.
DSynonyms cannot be created for synonyms.
ESynonyms can be created for packages.
FUsers must have the DBA role to create public synonyms.
Which two are true about self joins? (Choose two.)
AThey can use INNER JOIN and LEFT JOIN.
BThey require the EXISTS operator in the join condition.
CThey require the NOT EXISTS operator in the join condition.
DThey require table aliases.
EThey have no join condition.
FThey are always equijoins.
Examine the description of the EMPLOYEES table:
You write this failing statement:
Which clause causes the error?
ASELECT
BGROUP BY
CWHERE
DORDER BY
Which two statements are true about the data dictionary?
AThe data dictionary is accessible when the database is closed.
BThe data dictionary does not store metadata in tables.
CViews with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.
DViews with the prefix DBA_ display only metadata for objects in the SYS schema.
EViews with the prefix ALL_ display metadata for objects to which the current user has access.
Examine the description of the COUNTRIES table:
Examine the description of the DEPARTMENTS table:
Examine the description of the LOCATIONS table:
Which two queries will return a list of countries with no departments? (Choose two.)
A
B
C
D
You execute this query:
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd "Monday for" fmMonth rrrr')
FROM DUAL;
What is the result?
AIt executes successfully but does not return any result.
BIt returns the date for the first Monday of the next month.
CIt returns the date for the last Monday of the current month.
DIt generates an error.
Which three statements are true about a self join? (Choose three.)
AIt must be an equijoin.
BThe ON clause must be used.
CIt must be an inner join.
DIt can be an outer join.
EThe ON clause can be used.
FThe query must use two different aliases for the table.
Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)
AWHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
BThe WHERE clause can be used to exclude rows after dividing them into groups.
CThe HAVING clause can be used with aggregating functions in subqueries.
DThe WHERE clause can be used to exclude rows before dividing them into groups.
EAggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query.
Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION?
AColumn positions must be used in the ORDER BY clause.
BOnly column names from the first SELECT statement in the compound query are recognized.
CEach SELECT statement in the compound query must have its own ORDER BY clause.
DThe first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.
EEach SELECT statement in the compound query can have its own ORDER BY clause.
You need to calculate the number of days from 1st January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
ASELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2019' FROM
BSELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;
CSELECT ROUND(SYSDATE – TO_DATE('01/JANUARY/2019')) FROM DUAL;
DSELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;
ESELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;
Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?
A
B
C
D
Examine these statements which execute successfully:
Both statements display departments ordered by their average salaries.
Which two are true? (Choose two.)
ABoth statements will execute successfully if you add E.AVG_SAL to the select list.
BBoth statements will display departments with no employees.
COnly the second statement will execute successfully if you add E.AVG_SAL to the select list.
DOnly the first statement will display departments with no employees.
EOnly the second statement will display departments with no employees.
FOnly the first statement will execute successfully if you add E.AVG_SAL to the select list.
Which three actions can you perform only with system privileges? (Choose three.)
AQuery any table in a database.
BExecute a procedure in another schema.
CLog in to a database instance.
DAccess flat files, which are stored in an operating system directory, via the UTL_FILE package.
EUse the WITH GRANT OPTION clause.
Which two statements will convert the string Hello World to ello world? (Choose two.)
ASELECT INITCAP(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;
BSELECT SUBSTR(‘Hello World’, 2) FROM DUAL;
CSELECT LOWER(SUBSTR(‘Hello World’, 2, 1)) FROM DUAL;
DSELECT LOWER(TRIM(‘H’ FROM ‘Hello World’)) FROM DUAL;
ESELECT LOWER(SUBSTR(‘Hello World’, 2)) FROM DUAL;
Examine the description of the TRANSACTIONS table:
Which two SQL statements execute successfully? (Choose two.)
ASELECT customer_id AS “CUSTOMER_ID”, transaction_date AS “DATE”, amount + 100 DUES FROM transactions;
BSELECT customer_id AS ‘CUSTOMER_ID’, transaction_date AS DATE, amount + 100 ‘DUES’ FROM transactions;
CSELECT customer_id AS “CUSTOMER_ID”, transaction_date AS DATE, amount + 100 “DUES” FROM transactions;
DSELECT customer_id AS CUSTOMER_ID, transaction_date AS TRANS_DATE, amount + 100 “DUES AMOUNT” FROM transactions;
ESELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;
Which two statements are true about external tables? (Choose two.)
ADML statements cannot be used on them.
BYou can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
CTheir data can be retrieved by using only SQL or PL/SQL.
DIndexes can be created on them.
ETheir metadata and actual data are both stored outside the database.
Which three are true about system and object privileges? (Choose three.)
AWITH GRANT OPTTON cannot be used when granting an object privilege to PUBLIC.
BWITH GRANT OPTION can be used when granting an object privilege to both users and roles.
CRevoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.C. Adding a primary key constraint to an existing table in another schema requires a system privilege.
DAdding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege.
ERevoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect.