1Z0-071 practice questions 151–160

1. Question 151 · 238 in bank

   Select 2

   Examine the description of the CUSTOMERS table:

   

   CUSTNO is the PRIMARY KEY. You must determine if any customers’ details have been entered more than once using a different CUSTNO, by listing all duplicate names. Which two methods can you use to get the required result? (Choose two.)

   • Aself join
   • BRIGHT OUTER JOIN with self join
   • CLEFT OUTER JOIN with self join
   • DFULL OUTER JOIN with self join
   • Esubquery

   Show answer

   Answer: AE

   Next question

2. Question 152 · 238 in bank

   Select 2

   The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? (Choose two.)

   • ASELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;
   • BSELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10;
   • CSELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;
   • DSELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;
   • ESELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;

   Show answer

   Answer: BE

   Next question

3. Question 153 · 238 in bank

   Select 2

   Which two statements are true regarding a SAVEPOINT? (Choose two.)

   • ARolling back to a SAVEPOINT can undo a CREATE INDEX statement.
   • BOnly one SAVEPOINT may be issued in a transaction.
   • CRolling back to a SAVEPOINT can undo a DELETE statement.
   • DA SAVEPOINT does not issue a COMMIT.
   • ERolling back to a SAVEPOINT can undo a TRUNCATE statement.

   Show answer

   Answer: CD

   Next question

4. Question 154 · 238 in bank

   Select 2

   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;

   Show answer

   Answer: AD

   Explanation

   A,D,E is correct. Tested. Feel free to try SELECT 'abc' AS "customer_id", Sysdate as "date", 10+100 Dues FROM DUAL; {ASS SELECT 'abc' AS 'customer_id', Sysdate as date, 10+100 'Dues' FROM DUAL; Fail because single quote for alias and not double quote on date. Alias cannot be in a single quote and date must be in a double quote as date is saved as a datatype SELECT 'abc' AS "customer_id", Sysdate as DATE, 10+100 "Dues" FROM DUAL; FAIL. No double quote for date SELECT 'abc' AS "customer_id", Sysdate as TRANS_DATE, 10+100 "Dues AMOUNT" FROM DUAL; PASS SELECT 'abc' CUSTID, Sysdate TRANS_DATE, 10+100 Dues FROM DUAL; PASS

   Source: Adapted from community discussion — Misi_Oracle

   Next question

5. Question 155 · 238 in bank

   Select 3

   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.

   Show answer

   Answer: ACD

   Explanation

   But A is also not correct "You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role. "

   Source: Adapted from community discussion — tom100men

   Next question

6. Question 156 · 238 in bank

   Select 2

   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.

   Show answer

   Answer: AB

   Next question

7. Question 157 · 238 in bank

   Examine this schema information: 1. EMPLOYEES.DEPARTMENT_ID has a foreign key referencing DEPARTMENTS.DEPARTMENT_ID. 2. EMP_VIEW is based on the EMPLOYEES and DEPARTMENTS tables. 3. EMP_VIEW has columns EMPLOYEE_ID, EMPLOYEE_NAME and DEPARTMENT_NAME. You must add a new column, MANAGER_ID, from the EMPLOYEES table, to the view, showing each employee’s manager. Which statement will do this?

   • AALTER VIEW emp_view ADD (employee.manager_id);
   • BALTER VIEW emp_view MODIFY ( SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d - WHERE e.department_id = d.department_id);
   • CALTER VIEW emp_view ADD (SELECT manager_id FROM employees);
   • DCREATE OR REPLACE VIEW emp_view AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d - WHERE e.department_id = d.department_id;

   Show answer

   Answer: D

   Next question

8. Question 158 · 238 in bank

   Select 3

   Which three are true about privileges and roles? (Choose three.)

   • ASystem privileges always set privileges for an entire database.
   • BPUBLIC can be revoked from a user.
   • CAll roles are owned by the SYS schema.
   • DA user has all object privileges for every object in their schema by default.
   • EA role is owned by the user who created it.
   • FA role can contain a combination of several privileges and roles.
   • GPUBLIC acts as a default role granted to every user in a database.

   Show answer

   Answer: DFG

   Explanation

   Please ignore my previous answer. D:Who Can Grant Schema Object Privileges? A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. If the grant includes the GRANT OPTION (of the GRANT command), the grantee can further grant the object privilege to other users; otherwise, the grantee can use the privilege but cannot grant it to other users. https://docs.oracle.com/cd/A58617_01/server.804/a58227/ch18.htm F: https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm#:~:text=A%20role%20groups%20several%20privileges,to%20help%20in%20database%20administration. G: https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm#:~:text=Because%20PUBLIC%20is%20accessible%20to,requires%20the%20privilege%20or%20role.

   Source: Adapted from community discussion — billysunday1

   References

   • Oracle documentation
   • Oracle documentation

   Next question

9. Question 159 · 238 in bank

   Select 2

   Which two are true about the WITH GRANT OPTION clause? (Choose two.)

   • AIt can be used for system and object privileges.
   • BThe grantee can grant the object privilege to any user in the database, with or without including this option.
   • CThe grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option.
   • DIt cannot be used to pass on privileges to PUBLIC by the grantee.
   • EIt can be used when granting privileges to roles.
   • FIt can be used to pass on privileges to other users by the grantee.

   Show answer

   Answer: BF

   Explanation

   Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles. The user whose schema contains an object is automatically granted all associated object privileges with the GRANT OPTION. This special privilege allows the grantee several expanded privileges: The grantee can grant the object privilege to any users in the database, with or without the GRANT OPTION, or to any role in the database. If both of the following are true, the grantee can create views on the table and grant the corresponding privileges on the views to any user or role in the database. The grantee receives object privileges for the table with the GRANT OPTION. The grantee has the CREATE VIEW or CREATE ANY VIEW system privilege. The GRANT OPTION is not valid when granting an object privilege to a role. Oracle prevents the propagation of object privileges through roles so that grantees of a role cannot propagate object privileges received by means of roles.

   Source: Adapted from community discussion — tom100men

   References

   • Oracle documentation

   Next question

10. Question 160 · 238 in bank

    Examine the description of the EMPLOYEES table:

    

    Which query is valid?

    • ASELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
    • BSELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
    • CSELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
    • DSELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

    Show answer

    Answer: B

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

1Z0-071 practice, question 151. 238 questions available.