Oracle 1Z0-051 Online Practice Questions and Exam Preparation

Oracle 1Z0-051 Online Questions & Answers

• Question 281:

  Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?

  A. SELECT and1, "and2" FROM and3 WHERE last_name = 'and4';
  B. SELECT and1, 'and2' FROM and3 WHERE 'andlast_name = 'and4' ';
  C. SELECT and1, and2 FROM and3 WHERE last_name = 'and4';
  D. SELECT and1, 'and2' FROM EMP WHERE last_name = 'and4';
  C. SELECT and1, and2 FROM and3 WHERE last_name = 'and4';

• Question 282:

  You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.

  Which set of statements accomplishes this?

  A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
  B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
  C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
  D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
  E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
  D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

• Question 283:

  Examine the description of the EMPLOYEES table:

  EMP_ID NUMBER(4) NOT NULL

  LAST_NAME VARCHAR2(30) NOT NULL

  FIRST_NAME VARCHAR2(30)

  DEPT_ID NUMBER(2)

  Which statement produces the number of different departments that have employees with last name Smith?

  A. SELECT COUNT(*) FROM employees WHERE last_name='Smith';
  B. SELECT COUNT (dept_id) FROM employees WHERE last_name='Smith';
  C. SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
  D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
  E. SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';
  D. SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

• Question 284:

  Which two statements are true regarding constraints? (Choose two.)

  A. A constraint can be disabled even if the constraint column contains data
  B. A constraint is enforced only for the INSERT operation on a table
  C. A foreign key cannot contain NULL values
  D. All constraints can be defined at the column level as well as the table level
  E. A columns with the UNIQUE constraint can contain NULL values
  A. A constraint can be disabled even if the constraint column contains data
  E. A columns with the UNIQUE constraint can contain NULL values

• Question 285:

  View the Exhibit and examine the description for the PRODUCTS and SALES table. PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement?

  A. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE );
  B. DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 );
  C. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);
  D. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );
  C. DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id);

• Question 286:

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

  

  Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

  A. listing of customers who do not have a credit limit and were born before 1980
  B. finding the number of customers, in each city, whose marital status is 'married'
  C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
  D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
  E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
  D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
  E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

• Question 287:

  You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products;

  What is the implication of this command? (Choose all that apply.)

  A. All data in the table are deleted but the table structure will remain
  B. All data along with the table structure is deleted
  C. All views and synonyms will remain but they are invalidated
  D. The pending transaction in the session is committed
  E. All indexes on the table will remain but they are invalidated
  B. All data along with the table structure is deleted
  C. All views and synonyms will remain but they are invalidated
  D. The pending transaction in the session is committed

• Question 288:

  Which three statements are true regarding sub queries? (Choose three.)

  A. Multiple columns or expressions can be compared between the main query and sub query
  B. Sub queries can contain GROUP BY and ORDER BY clauses
  C. Only one column or expression can be compared between the main query and subqeury
  D. Main query and sub query can get data from different tables
  E. Main query and sub query must get data from the same tables
  F. Sub queries can contain ORDER BY but not the GROUP BY clause
  A. Multiple columns or expressions can be compared between the main query and sub query
  B. Sub queries can contain GROUP BY and ORDER BY clauses
  D. Main query and sub query can get data from different tables

• Question 289:

  Examine the following SQL commands:

  

  Which statement is true regarding the execution of the above SQL commands?

  A. Both commands execute successfully.
  B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
  C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
  D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
  E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
  B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.

• Question 290:

  Examine these statements:

  CREATE ROLE registrar;

  GRANT UPDATE ON student_grades TO registrar;

  GRANT registrar to user1, user2, user3;

  What does this set of SQL statements do?

  A. The set of statements contains an error and does not work.
  B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
  C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
  D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
  E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
  F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.
  C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.