Online Access Free 1Z0-007 Practice Test

The STUDENT_GRADES table has these columns:

The registrar has asked for a report on the average grade point average (GPA) for students enrolled during semesters that end in the year 2000. Which statement accomplish this?

• A.SELECT COUNT (gpa)
  FROM student grades
  WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000';
• B.SELECT SUM (gpa)
  FROM student grades
  WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000';
• C.SELECT AVG (gpa)
  FROM student_grades
  WHERE semester_end BETWEEN '01-JAN-2000' and '31.DEC.2000';
• D.SELECT AVERAGE (gpa)
  FROM student_grades
  WHERE semester_end > '01-JAN-2000' and semester end < 31-DEC-2000';
• E.SELECT MEDIAN (gpa)
  FROM student_grades
  WHERE semester end > '01-JAN-2000' and semester end < '31-DEC-2000';
• F.SELECT MIN (gpa)
  FROM student grades
  WHERE semester_end > '01-JAN-2000' and semester end < '31-DEC-2000';

You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20.
Which SQL statement would you use to create the view EMP_VU?

• A.CREATE VIEW emp_vu AS
  SELECT *
  FROM employees
  WHERE department_id IN (10,20);
• B.CREATE VIEW emp_vu AS
  SELECT *
  FROM employees
  WHERE department_id IN (10,20)
  WITH CHECK OPTION;
• C.CREATE VIEW emp_vu AS
  SELECT *
  FROM employees
  WHERE department_id IN (10,20)
  WITH READ ONLY;
• D.CREATE FORCE VIEW emp_vu AS
  SELECT *
  FROM employees
  WHERE department_id IN (10,20)
  NO UPDATE;
• E.CREATE FORCE VIEW emp_vu AS
  SELECT *
  FROM employees
  WHERE department_id IN (10,20);

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?

• A.ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;
• B.ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;
• C.ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;
• D.ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;
• E.ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;
• F.ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

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 UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
• 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 STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.
• E.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.
• F.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.

Examine the data in the EMPLOYEES table.

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column.
Evaluate this DELETE statement:
DELETE employee_id, salary, job_id FROM employees WHERE dept_id = 90;
Why does the DELETE statement fail when you execute it?

• A.You cannot specify column names in the DELETE clause of the DELETE statement.
• B.There is no row with dept_id 90 in the EMPLOYEES table.
• C.You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.
• D.You cannot delete the JOB_ID column because it is a NOT NULL column.