1z0-007 Practice Test

Immediately after the SELECT clause

Before the WHERE clause

Before the FROM clause

After the ORDER BY clause

After the WHERE clause

DROP emp_dept_uv;

DELETE emp_dept_uv;

REMOVE emp_dept_uv;

DROP VIEW emp_dept_uv;

DELETE VIEW emp_dept_uv;

REMOVE VIEW emp_dept_uv;

The two statements produce identical results

The second statement returns a syntax error.

There is no need to specify DESC because the results are sorted in descending order by default

The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX (salary);

SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id,job_cat;

SELECT dept_id, job_cat, MAX(salary) FROM employees;

SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id; E. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept _ id job _ cat salary;

INSERT

UPDATE

SELECT

DESCRIBE

DELETE

RENAME

ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students (student_id);

ALTER TABLE student_grades ADD CONSTRAINT NAME = student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

It allows a grantee DBA privileges.

It is required syntax for object privileges.

It allows privileges on specified columns of tables

It is used to grant an object privilege on a foreign key column.

It allows the grantee to grant object privileges to other users and roles.

SELECT * FROM DEPT;

SELECT * FROM SCOTT. DEPT;

SELECT * FROM DBA.SCOTT DEPT;

SELECT * FROM ALL_USERS WHERE USER_NAME = 'SCOTT' AND TABLE NAME = 'DEPT';

CREATIVE USER susan;

CREATIVE OR REPLACE USER susan;

CREATE NEW USER susan DEFAULT

CREATE USER susan IDENTIFIED BY blue;

CREATE NEW USER susan IDENTIFIED BY blue;

CREATE OR REPLACE USER susan IDENTIFIED BY blue;

SELECT * FROM emp_dept_vu;

SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department _ id;

SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department _ id, job_id;

SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM (salary) > 20000

None of the statements produce an error; all are valid.

The main query executes with the first value returned by the subquery.

The main query executes with the last value returned by the subquery.

The main query executes with all the values returned by the subquery

The main query fails because the multiple-row subquery cannot be used with the comparison operator.

You cannot define a multiple-row subquery in the WHERE clause of a SQL query.

SELECT &1, "&2" FROM &3 WHERE last_name = '&4';

SELECT &1, '&2' FROM &3 WHERE '& last_name = '&4';

SELECT &1, &2 FROM &3 WHERE last_name = '&4';

SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

The table is accessible to all users

Tables are created in the public schema.

Tables are created in your schema.

Tables are created in the DBA schema

You must specify the schema when the table is created.

The value displayed in the CALC_VALUE column will be lower.

The value displayed in the CALC_VALUE column will be higher.

There will be no difference in the value displayed in the CALC_VALUE column.

An error will be reported.

You have no rows in the table.

You have an employee with the name of James.

You cannot roll back to the same savepoint more than once.

Your last update fails to update any rows because employee ID 180 was already deleted.

Create a table view.

Create a view in any schema.

Create a view in your schema.

Create a sequence view in any schema.

Create a view that is accessible by everyone.

Create a view only of it is based on tables that you created.

SELECT MAX (gpa) FROM student _ grades WHERE gpa IS NOT NULL;

SELECT (gpa) FROM student _ grades GROUP BY semester_end WHERE gpa IS NOT NULL;

SELECT MAX (gpa) FROM student _ grades WHERE gpa IS NOT NULL GROUP BY semester_end;

SELECT MAX (gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student _grades;

SELECT MAX (gpa) FROM student _ grades GROUP BY semester_end WHERE gpa IS NOT NULL;

Create groups of data

Sort data in a specific order

Convert data to a different format

Retrieve data based on an unknown condition

Binary data up to 4 gigabytes.

Character data up to 4 gigabytes.

Raw binary data of variable length up to 2 gigabytes.

Binary data stored in an external file, up to 4 gigabytes.

A hexadecimal string representing the unique address of a row in its table.

0

1

0.00

An error statement

The SELECT statement is syntactically accurate

The SELECT statement does not work because there is no HAVING clause

The SELECT statement does not work because the column specified in the GROUP BY clause is not in the SELECT list.

The SELECT statement does not work because the GROUP BY clause should be in the main query and not in the subquery.

A schema object

A subquery that can contain an ORDER BY clause

Another name for a view that contains group functions

A subquery that is part of the FROM clause of another query

Only the row with department ID 40 is deleted in the DEPARTMENTS table.

The statement fails because there are child records in the EMPLOYEES table with department ID 40.

The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 110 and 106 are deleted from the EMPLOYEES table.

The row with department ID 40 is deleted in the DEPARTMENTS table. Also the rows with employee IDs 106 and 110 and the employees working under employee 110 are deleted from the EMPLOYEES table.

The row with department ID 40 is deleted in the DEPARTMENTS table. Also all the rows in the EMPLOYEES table are deleted.

The statement fails because there are no columns specifies in the DELETE clause of the DELETE statement.

The indexed column is declared as NOT NULL.

The indexed columns are used in the FROM clause.

The indexed columns are part of an expression.

The indexed column contains a wide range of values.

=

LIKE

BETWEEN

NOT IN

IS

DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);

DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_ employees);

DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ('Carrey')'

DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_ name = ('Carrey')'

SELECT employee_id, department_id, department_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments);

SELECT employee_id, department_id, department_name, salary FROM employees NATURAL JOIN departments;

SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department _ id = d. department_id;

SELECT employee_id, department_id, department_name, Salary FROM employees JOIN departments USING (e.department_id, d.department_id);

UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id = 180;

UPDATE new_employees SET name = (SELECT last_name||first_name FROM employees) WHERE employee_id = 180;

UPDATE new_employees SET name = (SELECT last_name|| first_name FROM employees WHERE employee_id =180) WHERE employee_id =(SELECT employee_id FROM new employees

UPDATE new_employees SET name = (SELECT last name|| first_name FROM employees WHERE employee_id= (SELECT employee_id FROM new_employees)) WHERE employee_id =180;

MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXIST THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

MERGE INTO new employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new _ employees VALUES (e.employees_id, e.first_name ||','||e.last_name);

The set of statements contains an error and does not work.

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.

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.

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.

It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.

It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.

CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY student (student_id));

CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id));

CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES student (student_id));

CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id));

SELECT a.last_name, a.salary, a.department_id, b.maxsal FROM employees a, (SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary < b.maxsal;

SELECT a.last name, a.salary, a.department_id FROM employees a WHERE a.department_id IN (SELECT department_id FROM employees b GROUP BY department_id having salary = (SELECT max(salary) from employees))

SELECT a.last_name, a.salary, a.department_id FROM employees a WHERE a.salary = (SELECT max(salary) FROM employees b WHERE a.department _ id = b.department _ id);

SELECT a.last_name, a.salary, a.department_id FROM employees a WHERE (a.department_id, a.salary) IN (SELECT department_id, a.salary) IN (SELECT department_id max(salary) FROM employees b GROUP BY department_id ORDER BY department _ id);

CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);

CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);

CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_ id;

CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;

The underlying tables must have data.

You need SELECT privileges on the view.

The underlying tables must be in the same schema

You need SELECT privileges only on the underlying tables.

CREATE ROLL registrar; GRANT MODIFY ON student_grant TO registrar; GRANT registrar to user 1, user2, user3

CREATE NEW ROLE registrar; GRANT ALL ON student_grant TO registrar; GRANT registrar to user 1, user2, user3

CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT ROLE to user1, user2, user3

CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT registrar to user 1, user2, user3;

CREATE registrar; GRANT CHANGE ON student_grant TO registrar; GRANT registrar;

GRANT select, insert, update ON student_grades TO manager

GRANT select, insert, update ON student_grades TO ROLE manager

GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;

GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION

GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION;

GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION

Alice only

Alice and Reena

Alice, Reena, and Timber

Sue, Alice, Reena, and Timber

All of the desired results

Two of the desired results

One of the desired results

An error statement

The statement produces an error at line 1.

The statement produces an error at line 3.

The statement produces an error at line 6.

The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.

The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) NOT NULL CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

CREATE TABLE EMP (empno NUMBER(4) ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));

SELECT student_ id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM

SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID

SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM

SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks DESC) WHERE ROWNUM

Scott can create a synonym for the EMP_DEPT_LOC_VU bus using the command: CREATE PRIVATE SYNONYM EDL_VU FOR mary. EMP DEPT_LOC_VU; then he can prefix the columns with this synonymn.

Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command: CREATE SYNONYM EDL_VU FOR mary. EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command: CREATE LOCAL SYNONYM EDL_VU FOR mary. EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

Scott can create a synonym for the EMP_DEPT_LOC_VU by using the command: CREATE SYNONYM EDL_VU FOR mary. EMP DEPT_LOC_VU; then he can prefix the columns with this synonym.

Scott cannot create a synonym because synonyms can be created only for tables.

Scott cannot create any synonym for Mary's view. Mary should create a private synonym for the view and grant SELECT privilege on that synonym to Scott.