1z0-007 Practice Test

65 Questions | Total Attempts: 453

SettingsSettingsSettings
Practice Test Quizzes & Trivia

This is a practice test for 1Z0-007 Exam


Questions and Answers
  • 1. 
    You need to give the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?
    • A. 

      GRANT select, insert, update ON student_grades TO manager

    • B. 

      GRANT select, insert, update ON student_grades TO ROLE manager

    • C. 

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

    • D. 

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

    • E. 

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

    • F. 

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

  • 2. 
    Which  subqueries work?
    • A. 

      SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department _ id);

    • B. 

      SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department _ id);

    • C. 

      SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department _ id);

    • D. 

      SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department _ id);

    • E. 

      SELECT last_name FROM employees Where salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department _ id);

    • F. 

      SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY ANG (SALARY));

  • 3. 
    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) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

      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;

  • 4. 
    You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?(Choose only one answer)
    • A. 

      ALTER TABLE students ADD PRIMARY KEY student_id;

    • B. 

      ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student _ id);

    • C. 

      ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student _ id);

    • D. 

      ALTER TABLE students ADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);

    • E. 

      ALTER TABLE students MODIFY CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);

  • 5. 
    Evaluate the SQL statement: 1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal 2 FROM employees a, 3 (SELECT dept_id, MAX(sal) maxsal 4. FROM employees 5 GROUP BY dept_id) b 6 WHERE a.dept_id = b.dept_id 7 AND a. asl < b.maxsal; What is the result of the statement?(Choose only one answer)
    • A. 

      The statement produces an error at line 1.

    • B. 

      The statement produces an error at line 3.

    • C. 

      The statement produces an error at line 6.

    • D. 

      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.

    • E. 

      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.

  • 6. 
    examine the SQL statements that create the EMPLOYEES and DEPARTMENTS tables: CREATE TABLE departments (department_id NUMBER PRIMARY KEY, department _ name VARCHAR2(30)); CREATE TABLE employees (EMPLOYEE_ID NUMBER PRIMARY KEY, EMP_NAME VARCHAR2(20), DEPT_ID NUMBER REFERENCES departments(department_id), MGR_ID NUMBER REFERENCES employees(employee id), JOB_ID VARCHAR2(15). SALARY NUMBER); ON the EMPLOYEES, On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. DEPT_ID is foreign key to DEPARTMENT_ID column of the DEPARTMENTS table. On the DEPARTMENTS table, DEPARTMENT_ID is the primary key. Examine this DELETE statement: DELETE FROM departments WHERE department id = 40; What happens when you execute the DELETE statement?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

      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.

    • D. 

      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.

    • E. 

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

    • F. 

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

  • 7. 
    Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table?(Choose only one answer)
    • A. 

      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);

    • B. 

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

    • C. 

      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));

    • D. 

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

  • 8. 
    Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEESColumn name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20), SALARY NUMBER MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS table DEPARTMENTSColumn name Data type Remarks DEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30) MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table Evaluate this SQL statement: SELECT employee_id, e.department_id, department_name, salary FROM employees e, departments d WHERE e. department_id = d.department_id; Which SQL statement is equivalent to the above SQL statement?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 9. 
    Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?(Choose only one answer)
    • A. 

      0

    • B. 

      1

    • C. 

      0.00

    • D. 

      An error statement

  • 10. 
    Which statement explicitly names a constraint?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

    • E. 

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

  • 11. 
    You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?(Choose only one answer)
    • A. 

      ALTER VIEW EMP_dept_vu (ADD manger_id NUMBER);

    • B. 

      MODIFY VIEW EMP_dept_vu (ADD manger_id NUMBER);

    • C. 

      ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department _ id = d.department_id;

    • D. 

      MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;

    • E. 

      CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id =d.department_id;

    • F. 

      You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

  • 12. 
    Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: MPLOYEESEMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHARD2(25) LAST_NAME VARCHARD2(25) HIRE_DATE DATE NEW EMPLOYEESEMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which UPDATE statement is valid?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

      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

    • D. 

      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;

  • 13. 
    Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 14. 
    Examine the structure if the EMPLOYEES and NEW EMPLOYEES tables: EMPLOYEESEMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME  VARCHAR2(60) Which MERGE statement is valid?(Choose only one answer)
    • A. 

      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);

    • B. 

      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);

    • C. 

      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);

    • D. 

      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);

  • 15. 
    In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?(Choose only one answer)
    • A. 

      Immediately after the SELECT clause

    • B. 

      Before the WHERE clause

    • C. 

      Before the FROM clause

    • D. 

      After the ORDER BY clause

    • E. 

      After the WHERE clause

  • 16. 
    Which statements about subqueries are true?
    • A. 

      A single row subquery can retrieve data from only one table.

    • B. 

      A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

    • C. 

      A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

    • D. 

      A single row subquery can retrieve data from more than one table.

    • E. 

      A single row subquery cannot be used in a condition where the LIKE operator is used for comparison

    • F. 

      A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

  • 17. 
    What is necessary for your query on an existing view to execute successfully?(Choose only one answer)
    • A. 

      The underlying tables must have data.

    • B. 

      You need SELECT privileges on the view.

    • C. 

      The underlying tables must be in the same schema

    • D. 

      You need SELECT privileges only on the underlying tables.

  • 18. 
    Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?(Choose only one answer)
    • A. 

      The two statements produce identical results

    • B. 

      The second statement returns a syntax error.

    • C. 

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

    • D. 

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

  • 19. 
    Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales amount * (.35 * e.bonus)) AS CALC_VALUEFROM employees e, sales sWHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?(Choose only one answer)
    • A. 

      The value displayed in the CALC_VALUE column will be lower.

    • B. 

      The value displayed in the CALC_VALUE column will be higher.

    • C. 

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

    • D. 

      An error will be reported.

  • 20. 
    Examine the structure of the STUDENTS table: STUDENT_ID NUMBER NOT NULL, Primary KeySTUDENT_NAME VARCHAR2(30) COURSE_ID VARCHAR2(10) NOT NULL MARKS NUMBERSTART_DATE DATE FINISH_DATE DATE You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.Which SQL statement accomplishes this task?(Choose only one answer)
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 21. 
    The CUSTOMERS table has these columns:CUSTOMER_ID NUMBER(4) NOT NULLCUSTOMER_NAME VARCHAR2(100) NOT NULLSTREET_ADDRESS VARCHAR2(150)CITY_ADDRESS VARCHAR2(50)STATE_ADDRESS VARCHAR2(50)PROVINCE_ADDRESS VARCHAR2(50)COUNTRY_ADDRESS VARCHAR2(50)POSTAL_CODE VARCHAR2(12)CUSTOMER_PHONE VARCHAR2(20)The CUSTOMER_ID column is the primary key for the table.Which statements find the number of customers?
    • A. 

      SELECT TOTAL(*) FROM customer;

    • B. 

      SELECT COUNT(*) FROM customer;

    • C. 

      SELECT TOTAL(customer_id) FROM customer;

    • D. 

      SELECT COUNT(customer_id) FROM customer;

    • E. 

      SELECT COUNT(customers) FROM customer;

    • F. 

      SELECT TOTAL(customer_name) FROM customer;

  • 22. 
    Which are DML statements?
    • A. 

      COMMIT...

    • B. 

      MERGE...

    • C. 

      UPDATE...

    • D. 

      DELETE...

    • E. 

      CREATE...

    • F. 

      DROP...

  • 23. 
    Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view.Which option enables Scott to eliminate the need to qualify the view with the name MARY .EMP_DEP_LOC_VU each time the view is referenced?(Choose only one answer)
    • A. 

      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.

    • B. 

      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.

    • C. 

      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.

    • D. 

      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.

    • E. 

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

    • F. 

      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.

  • 24. 
    Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)HIRE_DATE DATEYou issue these statements:CREATE table new_emp ( employe_id NUMBER, name VARCGAR2(30_));INSERT INTO new_emp SELECT employee_id, last_name from employees;Savepoint s1;UPDATE new_emp set name = UPPER(name);Savepoint s2;Delete from new_emp;Rollback to s2;Delete from new_emp where employee_id=180;UPDATE new_emp set name = 'James';Rollback to s2;UPDATE new_emp sey name = 'James' Where employee_id=180;Rollback;At the end of this transaction, what is true?(Choose only one answer)
    • A. 

      You have no rows in the table.

    • B. 

      You have an employee with the name of James.

    • C. 

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

    • D. 

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

  • 25. 
    In which scenario would index be most useful?(Choose only one answer)
    • A. 

      The indexed column is declared as NOT NULL.

    • B. 

      The indexed columns are used in the FROM clause.

    • C. 

      The indexed columns are part of an expression.

    • D. 

      The indexed column contains a wide range of values.

Back to Top Back to top