1Z0-071 ORACLE SQL (part 2)

Examine the data in the CUST_NAME column of the CUSTOMERS table: You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? (Choose two.). WHERE SUBSTR(cust_name, INSTR(cust_name, ‘’) + 1) LIKE ‘Mc%’. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘’) + 1)) IN (‘MC%’, ‘Mc%). 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)) LIKE ‘Mc%’.

Which three are true about the MERGE statement? (Choose three.). It can combine rows from multiple tables conditionally to insert into a single table. It can merge rows only from tables. It can use subqueries to produce source rows. It can update, insert, or delete rows conditionally in multiple tables. It can update the same row of the target table multiple times. It can use views to produce source rows.

Which three actions can you perform only with system privileges? (Choose three.). Query any table in a database. Log in to a database instance. Access flat files via a database, which are stored in an operating system directory. Create stored procedures, functions and packages. Execute a procedure in another schema. Use the WITH GRANT OPTION clause.

Which three are true about multitable INSERT statements? (Choose three.). They can be performed on external tables using SQL* Loader. They can be performed on relational tables. They can be performed only by using a subquery. They can insert each computed row into more than one table. They can be performed on views. They can be performed on remote tables.

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? (Choose two.). SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10;. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;.

Examine these statements executed in a single Oracle session: Which three statements are true? (Choose three.). The code for pen is 1. There is no row containing pencil. The code for fountain pen is 3. The code for pen is 10. There is no row containing fountain pen. There is no row containing pen.

Which two are true about dropping columns from a table? (Choose two.). A column drop is implicitly committed. A column that is referenced by another column in any other table cannot be dropped. A column can be removed only if it contains no data. Multiple columns can be dropped simultaneously using the ALTER TABLE command. A column must be set as unused before it is dropped from a table. A primary key column cannot be dropped.

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week. Which query can be used?. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;.

Examine the description of the PRODUCT_STATUS table: A. B. C. D. E. F.

Examine the description of the CUSTOMERS table: You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters. Which query can be used?. SELECT * FROM customers WHERE city = ‘D_%’;. SELECT * FROM customers WHERE city LIKE ‘D_’;. SELECT * FROM customers WHERE city LIKE ‘D_%’;. SELECT * FROM customers WHERE city = ‘%D_’;.

Which three statements are true about multiple row subqueries? (Choose three.). They can contain GROUP BY clauses. They can return multiple columns. Two or more values are always returned from the subquery. They can contain HAVING clauses. They cannot contain a subquery.

In which three situations does a new transaction always start? (Choose three.). when issuing a TRUNCATE statement after a SELECT statement was issued in the same session. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session. when issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session. when issuing a DML statement after a DML statement failed in the same session. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session.

Which two statements are true regarding the COUNT function? (Choose two.). A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column. It can only be used for NUMBER data types.

Examine this statement: What is returned upon execution?. 0 rows. an error. 1 row. 2 rows.

Which statement is true about aggregate functions?. The AVG function implicitly converts NULLS to zero. Aggregate functions can be nested to any number of levels. The MAX and MIN functions can be used on columns with character data types. Aggregate functions can be used in any clause of a SELECT statement.

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.). The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC). A TIMESTAMP data type column contains information about year, month, and day. The CURRENT_TIMESTAMP function returns data without time zone information. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC).

MANAGER is an existing role with no privileges or roles. EMP is an existing role containing the CREATE TABLE privilege. EMPLOYEES is an existing table in the HR schema. Which two commands execute successfully? (Choose two.). GRANT CREATE SEQUENCE TO manager, emp;. GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;. GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION;. GRANT CREATE TABLE, emp TP manager;. GRANT CREATE TABLE, SELECT ON hr.employees TO manager;.

Which two are true about granting privileges on objects? (Choose two.). An object privilege can be granted to other users only by the owner of that object. An object privilege can be granted to a role only by the owner of that object. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table. The owner of an object acquires all object privileges on that object by default. The WITH GRANT OPTION clause can be used only by DBA users.

Which statement is true about TRUNCATE and DELETE?. You can never TRUNCATE a table if foreign key constraints will be violated. For large tables TRUNCATE is faster than DELETE. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE. You can never DELETE rows from a table if foreign key constraints will be violated.

In the PROMOTIONS table, the PROMO_BEGIN_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 a query? (Choose two.). PROMO_BEGIN_DATE – 5 will return a date. PROMO_BEGIN_DATE – SYSDATE will return a number. TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date. PROMO_BEGIN_DATE – SYSDATE will return an error.

Examine the description of the SALES1 table: SALES2 is a table with the same description as SALES1. Some sales data is duplicated in both tables. You want to display the rows from the SALES1 table which are not present in the SALES2 table. Which set operator generates the required output?. SUBTRACT. INTERSECT. UNION ALL. UNION. MINUS.

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.). WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’));. WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’, ‘A102’);. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id = A101’ OR member_id = ‘A102’));. WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’);. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);.

Which two statements are true about a self join? (Choose two.). It can be a left outer join. It must be a full outer join. It can be an inner join. It must be an equijoin. The join key column must have an index.

You create a table by using this command: CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements? (Choose two.). INSERT INTO rate_list VALUES (-10) produces an error. INSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6. INSERT INTO rate_list VALUES (0.551) inserts the value as .55. INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99. INSERT INTO rate_list VALUES (0.999) produces an error. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9.

Examine these SQL statements which execute successfully: Which two statements are true after execution? (Choose two.). The foreign key constraint will be disabled. The primary key constraint will be enabled and DEFERRED. The foreign key constraint will be enabled and DEFERRED. The foreign key constraint will be enabled and IMMEDIATE. The primary key constraint will be enabled and IMMEDIATE.

Which two statements are true about conditional INSERT ALL? (Choose two.). Each WHEN condition is tested for each row returned by the subquery. The total number of rows inserted is always equal to the number of rows returned by the subquery. A single WHEN condition can be used for multiple INTO clauses. It cannot have an ELSE clause. Each row returned by the subquery can be inserted into only a single target table.

Examine the description of the EMPLOYEES table: Which query is valid?. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;.

Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? (Choose three.). Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. Insert statements can always be done on a table through a view. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.

Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.). Each SELECT statement in the compound query must have its own ORDER BY clause. Each SELECT statement in the compound query can have its own ORDER BY clause. Column positions must be used in the ORDER BY clause. The 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. Only column names from the first SELECT statement in the compound query are recognized.

Which three statements are true about Data Manipulation Language (DML)? (Choose three.). UPDATE statements can have different subqueries to specify the values for each updated column. INSERT statements can insert NULLS explicitly into a column. DELETE statements can remove multiple rows based on multiple conditions. DML statements require a primary key be defined on a table. INSERT INTO…SELECT…FROM statements automatically commit.

Which three are true about privileges and roles? (Choose three.). A role is owned by the user who created it. A role can contain a combination of several privileges and roles. System privileges always set privileges for an entire database. A user has all object privileges for every object in their schema by default. All roles are owned by the SYS schema. PUBLIC can be revoked from a user. PUBLIC acts as a default role granted to every user in a database.

Examine this query: Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.). Replace ‘&1’ with ‘&&1’ in the query. Use the DEFINE command before executing the query. Use the UNDEFINE command before executing the query. Execute the SET VERIFY ON command before executing the query. Store the query in a script and pass the substitution value to the script when executing it. Execute the SET VERIFY OFF command before executing the query.

Which two statements are true about a full outer join? (Choose two.). It includes rows that are returned by an inner join. It returns only unmatched rows from both tables being joined. It includes rows that are returned by a Cartesian product. It returns matched and unmatched rows from both tables being joined. The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.

Which three statements are true about defining relations between tables in a relational database? (Choose three.). Primary key columns allow null values. Every primary or unique key value must refer to a matching foreign key value. Foreign key columns allow null values. Every foreign key value must refer to a matching primary or unique key value. Unique key columns allow null values.

It drops any triggers defined on the table. It always retains the space used by the removed rows. A ROLLBACK statement can be used to retrieve the deleted data. It retains the integrity constraints defined on the table. It retains the indexes defined on the table. A FLASHBACK TABLE statement can be used to retrieve the deleted data.

Which two are true about a SQL statement using SET operators such as UNION? (Choose two.). The number, but not names, of columns must be identical for all SELECT statements in the query. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query. The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query. The names and number of columns must be identical for all SELECT statements in the query. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.

Which three statements are true about Structured Query Language (SQL)? (Choose three.). It best supports relational databases. It is used to define encapsulation and polymorphism for a relational table. It is the only language that can be used for both relational and object-oriented databases. It guarantees atomicity, consistency, isolation, and durability (ACID) features. It provides independence for logical data structures being manipulated from the underlying physical data storage. It requires that data be contained in hierarchical data storage.

Which two statements are true about the DUAL table? (Choose two.). It can display multiple rows but only a single column. It can be accessed by any user who has the SELECT privilege in any schema. It can display multiple rows and columns. It consists of a single row and single column of VARCHAR2 data type. It can be used to display only constants or pseudo columns. It can be accessed only by the SYS user.

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully? (Choose two.). SELECT NVL(cust_credit_limit * .15, ‘Not Available’) FROM customers;. SELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;. SELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;. SELECT NVL2(cust_credit_limit * .15, ‘Not Available’) FROM customers;.

Which two are true about the WITH GRANT OPTION clause? (Choose two.). The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option. It can be used when granting privileges to roles. It cannot be used to pass on privileges to PUBLIC by the grantee. It can be used for system and object privileges. It can be used to pass on privileges to other users by the grantee. The grantee can grant the object privilege to any user in the database, with or without including this option.

Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.). GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. GLOBAL TEMPORARY TABLE space allocation occurs at session start. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. A GLOBAL TEMPORARY TABLE’s definition is available to multiple sessions. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

Examine this description of the PRODUCTS table: Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS? (Choose three.). DROP TABLE products;. ALTER TABLE products. ALTER TABLE products SET UNUSED (expiry_date);. ALTER TABLE products DROP UNUSED COLUMNS;. CREATE INDEX price_idx ON products (price);. TRUNCATE TABLE products;.

Which two statements are true about transactions in the Oracle Database server? (Choose two.). If a session has an uncommitted transaction, then a DDL statement issues a COMMIT before starting a new transaction. An uncommitted transaction commits automatically if the user exists SQL*Plus. Data Manipulation Language (DML) statements always start a new transaction. A user can always see uncommitted updates made by the same user in a different session. A Data Definition Language (DDL) statement does a COMMIT automatically only for the data dictionary updates caused by the DDL. A session can always see uncommitted updates made by itself.

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEE_ID column is indexed. Rows exist for employees 100 and 200. Examine this statement: Which two statements are true? (Choose two.). Employees 100 and 200 will have the same JOB_ID as before the update command. Employees 100 will have JOB_ID set to the same value as the JOB_ID of employee 200. Employees 100 and 200 will have the same SALARY as before the update command. Employee 200 will have SALARY set to the same value as the SALARY of employee 100. Employee 100 will have SALARY set to the same value as the SALARY of employee 200. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100.

Which two statements are true about the SET VERIFY ON command? (Choose two.). It can be used in SQL Developer and SQL*Plus. It displays values for variables used only in the WHERE clause of a query. It can be used only in SQL*Plus. It displays values for variables prefixed with &&. It displays values for variables created by the DEFINE command.

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.). It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted. Only the primary key can be defined at the column and table level. The foreign key columns and parent table primary key columns must have the same names. A table can have only one primary key and one foreign key. A table can have only one primary key but multiple foreign keys. Primary key and foreign key constraints can be defined at both the column and table level. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.

Which three are true about system and object privileges? (Choose three.). WITH GRANT OPTION can be used when granting an object privilege to both users and roles. Adding a primary key constraint to an existing table in another schema requires a system privilege. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege. Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC.

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.). Implementing a relationship between two tables might require joining additional tables. Relating data from a table with data from the same table is implemented with a self join. Rows from unrelated tables cannot be joined. Every relationship between the two tables must be implemented in a join condition. An inner join relates rows within the same table.

Which two statements are true about substitution variables? (Choose two.). A substitution variable can be used with any clause in a SELECT statement. A substitution variable used to prompt for a column name must be enclosed in a single quotation marks. A substitution variable prefixed with & always prompts only once for a value in a session. A substitution variable can be used only in a SELECT statement. A substitution variable used to prompt for a column name must be enclosed in double quotation marks. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session.

Which three statements are true about the DESCRIBE command? (Choose three.). It can be used to display the structure of an existing view. It can be used only from SQL*Plus. It displays the PRIMARY KEY constraint for any column or columns that have that constraint. It can be used from SQL Developer. It displays all constraints that are defined for each column. It displays the NOT NULL constraint for any columns that have that constraint.

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson. Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?. SELECT REPLACE (TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘O’) FROM customers;. SELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘O’)) FROM customers;. SELECT REPLACE (SUBSTR(cust_last_name, -3), ‘An’, ‘O’) FROM customers;. SELECT REPLACE (REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘O’) FROM customers;.

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.). Insert statements can always be done on a table through a view. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. Delete statements can always be done on a table through a view. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transactions complete thereby committing the updates? (Choose three.). when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT. when a CREATE INDEX statement is executed successfully in the same session. when a COMMIT statement is issued by the same user from another session in the same database instance. when the session logs out successfully. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session.

The ORDERS table has a primary key constraint on the ORDER_ID column. The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table. The constraint is defined with ON DELETE CASCADE. There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000. Which three DELETE statements execute successfully?. DELETE order_id FROM orders WHERE order_total < 1000;. DELETE FROM orders WHERE order_total < 1000;. DELETE orders WHERE order_total < 1000;. DELETE * FROM orders WHERE order_total < 1000;. DELETE FROM orders;.

Which three statements are true regarding indexes? (Choose three.). A SELECT statement can access one or more indices without accessing any tables. An update to a table can result in no updates to any of the table’s indexes. A table belonging to one user can have an index that belongs to a different user. A UNIQUE index can be altered to be non-unique. An update to a table can result in updates to any or all of the table’s indexes. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.

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?. SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;. SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;. SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;. SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;. SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;.

Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.). Read data from an external table and load it into a table in the database. Create a directory object for an external table. Execute DML statements on an external table. Query data from an external table. Read data from a table in the database and insert it into an external table. Create a directory object for a flat file.

Which three statements are true about single-row functions? (Choose three.). They can be nested to any level. The data type returned can be different from the data type of the argument. They can accept only one argument. The argument can be a column name, variable, literal or an expression. They can be used only in the WHERE clause of a SELECT statement. They return a single result row per table.

Which two statements are true regarding a SAVEPOINT? (Choose two.). A SAVEPOINT does not issue a COMMIT. Only one SAVEPOINT may be issued in a transaction. Rolling back to a SAVEPOINT can undo a TRUNCATE statement. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement. Rolling back to a SAVEPOINT can undo a DELETE statement.

Which three privileges can be restricted to a subset of columns in a table? (Choose three.). ALTER. DELETE. UPDATE. SELECT. INDEX. REFERENCES. INSERT.

Examine the description of the MEMBERS table: Examine the partial query: SELECT city, last_name LNAME FROM members …; You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order. Which two clauses must you add to the query? (Choose two.). ORDER BY last_name DESC, city ASC. WHERE city IN (‘%AN%’). ORDER BY 1, LNAME DESC. ORDER BY 1, 2. WHERE city = ‘%AN%’. WHERE city LIKE ‘%AN%’.

You execute this command: ALTER TABLE employees SET UNUSED (department_id) Which two are true? (Choose two.). No updates can be made to the data in the DEPARTMENT_ID column. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table. A query can be display data from the DEPARTMENT_ID column. The DEPARTMENT_ID column is set to null for all rows in the table. The DEPARTMENT_ID column can be recovered from the recycle bin. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

You have been tasked to create a table for a banking application. One of the columns must meet three requirements: 1) Be stored in a format supporting date arithmetic without using conversion functions 2) Store a load period of up to 10 years 3) Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?. TIMESTAMP WITH LOCAL TIMEZONE. TIMESTAMP WITH TIMEZONE. INTERVAL DAY TO SECOND. TIMESTAMP. INTERVAL YEAR TO MONTH.

Which two are true about savepoints? (Choose two.). After issuing a savepoints, you can roll back to the savepoint name within the current transaction. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error. They make uncommitted updates visible to other sessions owned by the same user. After issuing a savepoint, you cannot roll back the complete transaction. You can commit updates done between two savepoints without committing other updates in the current transaction. They make uncommitted updates visible to sessions owned by other users.

Examine the description of the ORDER_ITEMS table: Which two can replace <clause> so the query completes successfully? (Choose two). quantity, unit_price. quantity * unit_price. quantity. total_paid. product_id.

Which two are SQL features? (Choose two.). processing sets of data. providing update capabilities for data in external files. providing graphical capabilities. providing variable definition capabilities. providing database transaction control.

Which two are true about scalar subquery expressions? (Choose two.). They can return at most one row. You can use them as a default value for a column. You cannot correlate them with a table in the parent statement. You must enclose them in parentheses. They can return two columns.

You have the privileges to create any type of synonym. Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accessible to all users?. CREATE PUBLIC SYNONIM emp FOR hcm.employee_records;. CREATE GLOBAL SYNONIM emp FOR hcm.employee_records;. CREATE SYNONIM emp FOR hcm.employee_records;. CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;. CREATE SYNONIM SYS.emp FOR hcm.employee_records;.

Examine the description of the EMPLOYEES table: Which two statements will run successfully? (Choose two.). SELECT ‘The first_name is ‘’ || first_name || ‘’ FROM employees;. SELECT ‘The first_name is ‘ || first_name || ‘’ FROM employees;. SELECT ‘The first_name is ‘’’ || first_name || ‘’’ FROM employees;. SELECT ‘The first_name is \‘’ || first_name || ‘\‘’ FROM employees;. SELECT ‘The first_name is ‘’’ || first_name || ‘‘’’ FROM employees;.

Examine the description of the ORDERS table: Which three rows will it return? (Choose three.). 5 01-MAR-2019. 3 <null>. 1 <null>. 4 01-FEB-2019. 2 <null>. 5 <null>. 3 01-JAN-2019.

Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.). When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks. Creating an external table creates a dump file that can be used only by an external table in the same database. When creating an external table, data can be selected only from a table whose rows are stored in database blocks. Creating an external table creates a directory object. Creating an external table creates a dump file that can be used by an external table in the same or a different database.

Which statement is true about TRUNCATE and DELETE?. You can never TRUNCATE a table if foreign key constraints will be violated. For large tables, DELETE is faster than TRUNCATE. For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE. You can DELETE rows from a table with referential integrity constraints.

Examine these statements and results: How many rows are retrieved by the last query?. 28. 0. 42. 14.

Which three statements about roles are true? (Choose three.). Roles are assigned to roles using the ALTER ROLE statement. A single role can be assigned to multiple users. A single user can be assigned multiple roles. Privileges are assigned to a role using the ALTER ROLE statement. A role is named group of related privileges that can only be assigned to a user. Privileges are assigned to a role using the GRANT statement. Roles are assigned to users using the ALTER USER statement.

Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109. User ALICE has no privileges to access HR.EMPLOYEES. User ALICE starts a session. User HR starts a session and successfully executes these statements: GRANT DELETE ON employees TO alice; UPDATE employees SET salary = 24000 WHERE employee_id = 109; In her existing session ALICE then executes: DELETE FROM hr.employees WHERE employee_id = 109; What is the result?. The DELETE command will wait for HR'S transaction to end then delete the row. The DELETE command will immediately delete the row. The DELETE command will immediately return an error. The DELETE command will wait for HR'S transaction to end then return an error.

Which two are true about using constraints? (Choose two.). A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level. NOT NULL can be specified at the column and at the table level. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

Which three statements are true about multiple row subqueries? (Choose three.). Two or more values are always returned from the subquery. They can contain GROUP BY clauses. They cannot contain a subquery. They can return multiple columns. They can contain HAVING clauses.

Examine the structure of the MEMBERS table: You execute the SQL statement: SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members; What is the outcome?. It fails because the alias name specified after the column names is invalid. It fails because the space specified in single quotation marks after the first two column names is invalid. It executes successfully and displays the column details in a single column with only the alias column heading. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

What is outcome?. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement. It generates an error because a subquery cannot have a join condition in a UPDATE statement. It executes successfully and gives the desired update. It executes successfully but does not give the desired update.

View the Exhibit and examine the details of PRODUCT_INFORMATION table. You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: SELECT product_name FROM product_informationWHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses. It would execute and the output would display the desired result. It would execute but the output would return no rows.

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables. What would be the outcome of the above SQL statement?. It displays prod IDs in the promo with the lowest cost. It displays prod IDs in the promos with the lowest cost in the same time interval. It displays prod IDs in the promos with the highest cost in the same time interval. It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables. The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table. The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively. Examine this command: CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE) AS SELECT prod_id, cust_id, time_id FROM sales; Which statement is true?. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition. The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment. SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN' ------------------------------------- SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN'; Choose two correct SET operators which would cause the query to return the desired result. UNION. MINUS. INTERSECT. UNION ALL.

Examine the structure of the SALES table. Which two statements are true about the SALES1 table? (Choose two.). It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match. It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table. It will not be created because of the invalid WHERE clause. It is created with no rows. It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. Which statement is true regarding the execution? (Choose the best answer.). It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement. It would not execute because two tables cannot be referenced in a single UPDATE statement. It would execute and restrict modifications to the columns specified in the SELECT statement. It would not execute because a SELECT statement cannot be used in place of a table name.

View the Exhibits and examine PRODUCTS and SALES tables. What happens when the above statement is executed?. The statement executes successfully and produces the required output. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.

Examine the structure of the BOOKS_TRANSACTIONS table: Which statement is true about the outcome?. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE. It displays details for members who have borrowed before today’s date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102. It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

View the Exhibit and examine the data in the EMPLOYEES table. What is the outcome?. It executes successfully but does not give the correct output. It generates an error because the concatenation operator can be used to combine only two items. It generates an error because the usage of the ROUND function in the expression is not valid. It generates an error because the alias is not valid. IT executes successfully and gives the correct output.

View the Exhibit and examine the structure of the PROMOTIONS table. Which statement is true regarding the outcome of the above query?. It produces an error because subqueries cannot be used with the CASE expression. It shows COST_REMARK for all the promos in the promo category ‘TV’. It shows COST_REMARK for all the promos in the table. It produces an error because the subquery gives an error.

Examine the structure of the BOOKS_TRANSACTIONS table. Which condition must be used in the WHERE clause to perform the required update?. MEMBER_ID = '';. MEMBER_ID = NULL;. MEMBER_ID IS NULL;. MEMBER_ID = "";.

Examine the data in the ORD_ITEMS table: Which statement is true regarding the result?. It returns an error because the HAVING clause should be specified after the GROUP BY clause. It returns an error because all the aggregate functions used in the HAVING clause must be specified in the SELECT list. It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table. It displays the item nos with their average quantity where the average quantity is more than double the overall minimum quantity of all the items in the table.

Examine the description of the CUSTOMERS table: A. B. C. D. E.

Examine the structure of the two tables. A. B. C. D. E.

Examine the data in the PRODUCTS table: 1 and 2. 1 and 3. 1,2 and 3. 2 and 3.

Examine the description of the EMPLOYEES table: A. B. C. D. E.

Examine the data in the COLORS table: A. B. C. D. E.