1z0-007 Practice Test

Approved & Edited by ProProfs Editorial Team

The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.

Learn about Our Editorial Process

| By Sudha_test

Sudha_test

Community Contributor

Quizzes Created: 12 | Total Attempts: 36,937

Questions: 65 | Attempts: 568 | Updated: Mar 21, 2023

Questions

Settings

Feedback

During the Quiz End of Quiz

Play as

Quiz Flashcard

Create your own Quiz

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
Correct Answer
D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION

Explanation
The correct answer is "GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION". This statement grants the MANAGER role the ability to select from, insert into, and modify existing rows in the STUDENT_GRADES table. The "WITH GRANT OPTION" allows anyone given this MANAGER role to pass those privileges on to others.

Rate this question:
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));
Correct Answer(s)
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);

Explanation
The given correct answers are the queries that utilize subqueries correctly. In the first query, it selects all the employees whose salary is greater than the minimum salary in their respective departments. The second query selects all the employees whose salary is equal to the average salary in their respective departments. The third query selects all the distinct department IDs where the salary is greater than any average salary in any department. The fourth query selects the department IDs where the salary is greater than the average salary in all departments. The fifth query selects the last names of employees whose salary is greater than the maximum salary in any department.

Rate this question:
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;
Correct Answer
B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id,job_cat;

Explanation
The correct answer is: SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id,job_cat;

This statement uses the GROUP BY clause to group the data by department and job category. Then, it uses the MAX function to calculate the maximum salary within each group. This will give the maximum salary paid in each job category of each department.

Rate this question:
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);
Correct Answer
D. ALTER TABLE students ADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);

Explanation
The correct answer is "ALTER TABLE students ADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);". This statement adds a constraint named "stud _ id _pk" to the "students" table, specifying that the "student _ id" column should be the primary key. This ensures that each value in the "student _ id" column will be unique and not null, thereby fulfilling the requirement of adding a primary key to the table.

Rate this question:
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.
Correct Answer
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.

Explanation
The SQL statement uses a subquery to find the maximum salary for each department and then joins it with the employees table using the department ID. The condition in the WHERE clause filters out employees whose salary is less than the maximum salary in their department. Therefore, 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.

Rate this question:
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.
Correct Answer
B. The statement fails because there are child records in the EMPLOYEES table with department ID 40.

Explanation
When the DELETE statement is executed, it fails because there are child records in the EMPLOYEES table with department ID 40. This means that there are employees assigned to this department, and deleting the department would result in orphaned records in the employees table. Therefore, the statement is not executed to maintain data integrity.

Rate this question:
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));
Correct Answer
B. CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

Explanation
The correct answer is the third option: CREATE TABLE EMP
(empno NUMBER(4),
ename VARCNAR2(35),
deptno NUMBER(7,2)
CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)).

This statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table. It specifies the constraint name "emp_deptno_fk" and references the DEPTNO column of the DEPT table.

Rate this question:
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);
Correct Answer
C. SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department _ id = d. department_id;

Explanation
The correct answer is the fourth option: SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department_id = d.department_id. This statement uses the JOIN keyword to combine the EMPLOYEES and DEPARTMENTS tables based on the department_id column. The SELECT clause specifies the columns to be retrieved from both tables. The ON clause specifies the condition for the join, which is matching the department_id column in both tables.

Rate this question:
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
Correct Answer
A. 0

Explanation
The SQL statement calculates the remainder of 1600 divided by 10 using the MOD function, which is 0. Then, it truncates the result to the nearest tens place using the TRUNC function, which is still 0. Finally, it rounds the result to two decimal places using the ROUND function, which remains 0. Therefore, the result displayed will be 0.

Rate this question:
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);
Correct Answer
C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

Explanation
The statement "ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);" explicitly names a constraint by using the keyword "CONSTRAINT" followed by the name "student_id_fk". It also specifies that this constraint is a foreign key constraint by using the keyword "FOREIGN KEY" and references the "student_id" column in the "students" table.

Rate this question:
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.
Correct Answer
F. You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

Explanation
To modify the view by adding a fourth column, the existing view must be removed first. Then, the CREATE VIEW command can be used with a new column list to modify the view. This process ensures that the view is updated with the desired changes.

Rate this question:
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;
Correct Answer
A. UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id = 180;

Explanation
The correct answer is the first option: UPDATE new_employees SET name = (Select last_name|| first_name FROM employees Where employee_id =180) WHERE employee_id = 180. This statement updates the name column in the new_employees table with the concatenation of the last_name and first_name columns from the employees table, where the employee_id is 180. The WHERE clause ensures that only the row with employee_id 180 in the new_employees table is updated.

Rate this question:
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;
Correct Answer
B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);

Explanation
This SQL statement creates a view called EMP_VU that selects the columns employee_id, emp_name, job_id, and department_id from the EMPLOYEES table. It also includes a condition WHERE mgr_id IN (102, 120), which filters the rows based on the manager IDs. This allows the user to insert rows through the view, as it selects the necessary columns and includes the required condition.

Rate this question:
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);
Correct 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);

Explanation
The correct answer is the first option: "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)." This is the valid MERGE statement because it correctly specifies the source and target tables using the USING clause, joins the tables on the employee_id column, and performs an UPDATE when a match is found and an INSERT when there is no match. The SET clause updates the name column in the new_employees table with the concatenation of the first_name and last_name columns from the employees table.

Rate this question:
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
Correct Answer
E. After the WHERE clause

Explanation
The GROUP BY clause is placed after the WHERE clause in a SELECT statement. This is because the WHERE clause is used to filter the rows before grouping them, and the GROUP BY clause is used to group the rows based on a specific column or expression. By placing the GROUP BY clause after the WHERE clause, the query ensures that the filtering is applied before the grouping is done.

Rate this question:
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.
Correct Answer(s)
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.
D. A single row subquery can retrieve data from more than one table.

Explanation
The first statement is false because a single row subquery can retrieve data from multiple tables. The second statement is true as a SQL query statement cannot display data from a table referred to in its subquery unless that table is included in the main query's FROM clause.

Rate this question:
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.
Correct Answer
B. You need SELECT privileges on the view.

Explanation
To execute a query on an existing view successfully, it is necessary to have SELECT privileges on the view. This means that the user executing the query must have the necessary permissions to retrieve data from the view. The other options mentioned, such as the underlying tables having data or being in the same schema, are not relevant to the execution of the query on the view.

Rate this question:
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.
Correct Answer
A. The two statements produce identical results

Explanation
The two SQL statements produce identical results because they both select the last_name, salary, and hire_date columns from the EMPLOYEES table and order them in descending order based on the salary column. The second statement uses the shorthand notation "ORDER BY 2 DESC" where the number 2 represents the second column in the SELECT statement, which is the salary column.

Rate this question:
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.
Correct Answer
C. There will be no difference in the value displayed in the CALC_VALUE column.

Explanation
If all the parentheses are removed from the calculation, the order of operations will still be followed because multiplication and addition have predefined precedence. Therefore, the result of the calculation will remain the same, and there will be no difference in the value displayed in the CALC_VALUE column.

Rate this question:
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
Correct Answer
D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks DESC) WHERE ROWNUM

Explanation
The correct answer is the fourth option. This SQL statement selects the student ID, marks, and assigns a row number as "Rank" for each student from the subquery. The subquery selects the student ID and marks from the STUDENTS table and orders them in descending order based on the marks. The outer query then filters the results based on the row number, selecting only the top 10 students. This will give a report of the 10 students who achieved the highest ranking in the INT SQL course and completed it in the year 1999.

Rate this question:
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;
Correct Answer(s)
B. SELECT COUNT(*) FROM customer;
D. SELECT COUNT(customer_id) FROM customer;

Explanation
The correct answers for finding the number of customers are "SELECT COUNT(*) FROM customer;" and "SELECT COUNT(customer_id) FROM customer;". The COUNT function is used to count the number of rows in a table. The asterisk (*) in "COUNT(*)" counts all rows in the table, while "COUNT(customer_id)" counts the number of non-null values in the customer_id column, which is the primary key for the table. The other statements are incorrect because they use invalid syntax or count columns that are not suitable for counting the number of customers.

Rate this question:
22.

Which are DML statements?
- A.
  
  COMMIT...
- B.
  
  MERGE...
- C.
  
  UPDATE...
- D.
  
  DELETE...
- E.
  
  CREATE...
- F.
  
  DROP...
Correct Answer(s)
B. MERGE...
C. UPDATE...
D. DELETE...

Explanation
The correct answer is MERGE, UPDATE, and DELETE. These are all examples of Data Manipulation Language (DML) statements used in SQL. The MERGE statement combines multiple operations such as INSERT, UPDATE, and DELETE into a single statement. UPDATE is used to modify existing records in a table, while DELETE is used to remove records from a table. CREATE and DROP are examples of Data Definition Language (DDL) statements, not DML. COMMIT is a transaction control statement, not a DML statement.

Rate this question:
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.
Correct Answer
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.

Explanation
By creating a synonym for the view EMP_DEPT_LOC_VU using the command "CREATE SYNONYM EDL_VU FOR mary.EMP_DEPT_LOC_VU", Scott can eliminate the need to qualify the view with the name MARY.EMP_DEP_LOC_VU each time it is referenced. This allows Scott to simply prefix the columns with the synonym EDL_VU when using the view.

Rate this question:
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.
Correct Answer
A. You have no rows in the table.

Explanation
At the end of this transaction, the table "new_emp" will have no rows. This is because the statements "DELETE from new_emp;" and "Rollback to s2;" remove all the rows from the table. The subsequent statements "DELETE from new_emp where employee_id=180;" and "UPDATE new_emp set name = 'James' Where employee_id=180;" do not have any effect as the row with employee ID 180 was already deleted. Therefore, the table remains empty.

Rate this question:
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.
Correct Answer
D. The indexed column contains a wide range of values.

Explanation
In scenarios where the indexed column contains a wide range of values, indexing would be most useful. This is because indexing allows for faster retrieval of data by creating a data structure that organizes the values in the indexed column. When the indexed column contains a wide range of values, it means that there is a large diversity in the values stored in that column. By indexing such a column, the database can quickly locate and retrieve specific values or ranges of values, improving the efficiency of queries and overall performance.

Rate this question:
26.

Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)DEPARTMENT_ID NUMBERSALARY NUMBERWhat is the correct syntax for an inline view?(Choose only one answer)
- A.
  
  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;
- B.
  
  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))
- C.
  
  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);
- D.
  
  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);
Correct Answer
A. 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;

Explanation
The correct syntax for an inline view is to use a subquery to create a temporary table (or view) within the main query. In this case, the subquery is used to find the maximum salary for each department in the EMPLOYEES table, and then the main query joins the EMPLOYEES table with the subquery using the department_id column. The main query selects the last_name, salary, and department_id columns from the EMPLOYEES table, as well as the maxsal column from the subquery. The WHERE clause is used to filter the results to only include rows where the salary is less than the maximum salary for the department.

Rate this question:
27.

Which statements about subqueries are true?
- A.
  
  A single row subquery can retrieve only one column and one row.
- B.
  
  A single row subquery can retrieve only one row but many columns
- C.
  
  A multiple row subquery can retrieve multiple rows and multiple columns
- D.
  
  A multiple row subquery can be compared by using the ">" operator.
- E.
  
  A single row subquery can use the IN operator.
- F.
  
  A multiple row subquery can use the "=" operator.
Correct Answer(s)
B. A single row subquery can retrieve only one row but many columns
C. A multiple row subquery can retrieve multiple rows and multiple columns
D. A multiple row subquery can be compared by using the ">" operator.

Explanation
A single row subquery can retrieve only one row but many columns. This means that the subquery can return multiple values in a single row, but it will only retrieve one row of data.

A multiple row subquery can retrieve multiple rows and multiple columns. This means that the subquery can return multiple rows of data, and each row can contain multiple columns.

A multiple row subquery can be compared by using the ">" operator. This means that the subquery can be used in a comparison statement where the ">" operator is used to compare the subquery result with another value.

Rate this question:
28.

Which are correct guidelines for naming database tables?
- A.
  
  Must begin with either a number or a letter.
- B.
  
  Must be 1-30 characters long.
- C.
  
  Should not be an Oracle Server reserved word.
- D.
  
  Must contain only A-Z, a-z, 0-+, _, *, and #.
- E.
  
  Must contain only A-Z, a-z, 0-9, _, $, and #.
- F.
  
  Must begin with a letter.
Correct Answer(s)
B. Must be 1-30 characters long.
C. Should not be an Oracle Server reserved word.
E. Must contain only A-Z, a-z, 0-9, _, $, and #.
F. Must begin with a letter.

Explanation
The guidelines for naming database tables include the following: the name must be 1-30 characters long, it should not be an Oracle Server reserved word, it must contain only A-Z, a-z, 0-9, _, $, and #, and it must begin with a letter. These guidelines ensure that the table names are within a reasonable length, avoid conflicts with reserved words, follow a specific character set, and start with a letter for clarity and consistency.

Rate this question:
29.

Which operator can be used with a multiple-row subquery?(Choose only one answer)
- A.
  
  =
- B.
  
  LIKE
- C.
  
  BETWEEN
- D.
  
  NOT IN
- E.
  
  IS
- F.
Correct Answer
D. NOT IN

Explanation
The NOT IN operator can be used with a multiple-row subquery. This operator is used to exclude rows that are returned by the subquery from the result set of the main query. It allows for the comparison of a value with a set of values returned by the subquery, and returns true if the value is not found in the set.

Rate this question:
30.

You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?(Choose only one answer)
- A.
  
  The main query executes with the first value returned by the subquery.
- B.
  
  The main query executes with the last value returned by the subquery.
- C.
  
  The main query executes with all the values returned by the subquery
- D.
  
  The main query fails because the multiple-row subquery cannot be used with the comparison operator.
- E.
  
  You cannot define a multiple-row subquery in the WHERE clause of a SQL query.
Correct Answer
D. The main query fails because the multiple-row subquery cannot be used with the comparison operator.

Explanation
When the main query is executed, it fails because a multiple-row subquery cannot be used with the comparison operator "=" in the WHERE clause. This is because the comparison operator "=" expects a single value, but the subquery is returning multiple rows. Therefore, the main query cannot be executed successfully.

Rate this question:
31.

You need to calculate the total of all salaries in the accounting department. Which group function should you use?(Choose only one answer)
- A.
  
  MAX
- B.
  
  MIN
- C.
  
  SUM
- D.
  
  COUNT
- E.
  
  TOTAL
- F.
  
  LARGEST
Correct Answer
C. SUM

Explanation
The question asks for the appropriate group function to calculate the total of all salaries in the accounting department. The correct answer is SUM. The SUM function is used to calculate the total of a set of values, which is exactly what is needed in this scenario. MAX and MIN functions are used to find the highest and lowest values respectively, which are not relevant to calculating the total. COUNT function is used to count the number of values, not their sum. TOTAL and LARGEST are not valid group functions.

Rate this question:
32.

Scott issues the SQL statements:CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13)};GRANT SELECT ON DEPT TI SUE;If Sue needs to select from Scott's DEPT table, which command should she use?(Choose only one answer)
- A.
  
  SELECT * FROM DEPT;
- B.
  
  SELECT * FROM SCOTT. DEPT;
- C.
  
  SELECT * FROM DBA.SCOTT DEPT;
- D.
  
  SELECT * FROM ALL_USERS WHERE USER_NAME = 'SCOTT' AND TABLE NAME = 'DEPT';
Correct Answer
B. SELECT * FROM SCOTT. DEPT;

Explanation
The correct answer is "SELECT * FROM SCOTT.DEPT;". This is because Sue needs to select from Scott's DEPT table, and to do so she needs to specify the schema name (SCOTT) and the table name (DEPT) using the dot notation. The other options either do not specify the schema or the table name correctly.

Rate this question:
33.

A subquery can be used to _________(Choose only one answer).
- A.
  
  Create groups of data
- B.
  
  Sort data in a specific order
- C.
  
  Convert data to a different format
- D.
  
  Retrieve data based on an unknown condition
Correct Answer
D. Retrieve data based on an unknown condition

Explanation
A subquery can be used to retrieve data based on an unknown condition. This means that the subquery can be used to filter and retrieve data from a table based on a condition that is not known in advance. The subquery is executed first to determine the condition, and then the main query is executed to retrieve the data based on that condition. This allows for more flexible and dynamic querying of data.

Rate this question:
34.

The EMPLOYEES table contains these columns:LAST_NAME VARCHAR2 (25)SALARY NUMBER (6,2)COMMISSION_PCT NUMBER (6)You need to write a query that will produce these results:1 Display the salary multiplied by the commission_pct.2 Exclude employees with a zero commission_pct.3 Display a zero for employees with a null commission value.Evaluate the SQL statement:SELECT LAST_NAME, SALARY*COMMISSION_PCTFROM EMPLOYEESWHERE COMMISSION_PCT IS NOT NULL;What does the statement provide?(Choose only one answer)
- A.
  
  All of the desired results
- B.
  
  Two of the desired results
- C.
  
  One of the desired results
- D.
  
  An error statement
Correct Answer
C. One of the desired results

Explanation
The SQL statement SELECT LAST_NAME, SALARY*COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL; provides one of the desired results, which is to display the salary multiplied by the commission_pct. It also excludes employees with a zero commission_pct. However, it does not display a zero for employees with a null commission value.

Rate this question:
35.

Which is an SQL*Plus command?(Choose only one answer)
- A.
  
  INSERT
- B.
  
  UPDATE
- C.
  
  SELECT
- D.
  
  DESCRIBE
- E.
  
  DELETE
- F.
  
  RENAME
Correct Answer
D. DESCRIBE

Explanation
The correct answer is "DESCRIBE". The DESCRIBE command is used in SQL*Plus to display the structure of a table, including its columns, data types, and constraints. This command is helpful for understanding the schema of a table and its relationships with other tables in a database. It provides essential information about the table's structure, allowing users to analyze and query the data effectively.

Rate this question:
36.

Which statement creates a new user?(Choose only one answer)
- A.
  
  CREATIVE USER susan;
- B.
  
  CREATIVE OR REPLACE USER susan;
- C.
  
  CREATE NEW USER susan DEFAULT
- D.
  
  CREATE USER susan IDENTIFIED BY blue;
- E.
  
  CREATE NEW USER susan IDENTIFIED BY blue;
- F.
  
  CREATE OR REPLACE USER susan IDENTIFIED BY blue;
Correct Answer
D. CREATE USER susan IDENTIFIED BY blue;

Explanation
The statement "CREATE USER susan IDENTIFIED BY blue;" creates a new user named "susan" with the password "blue".

Rate this question:
37.

What is true about the WITH GRANT OPTION clause?(Choose only one answer)
- A.
  
  It allows a grantee DBA privileges.
- B.
  
  It is required syntax for object privileges.
- C.
  
  It allows privileges on specified columns of tables
- D.
  
  It is used to grant an object privilege on a foreign key column.
- E.
  
  It allows the grantee to grant object privileges to other users and roles.
Correct Answer
E. It allows the grantee to grant object privileges to other users and roles.

Explanation
The WITH GRANT OPTION clause allows the grantee to grant object privileges to other users and roles. This means that the grantee has the authority to give permissions to other individuals or roles, allowing them to access and manipulate the specified object. This clause is commonly used in database management systems to delegate authority and control over database objects to different users and roles.

Rate this question:
38.

Which two statements complete a transaction?(Choose only one answer)
- A.
  
  DELETE employees;
- B.
  
  DESCRIBE employees;
- C.
  
  ROLLBACK TO SAVE POINT C;
- D.
  
  GRANT SELECT ON employees TO SCOTH
- E.
  
  ALTER TABLE employees SET UNUSED COLUMN sal;
- F.
  
  Select MAX(sal) FROM employees WHERE department _ id 20;
Correct Answer(s)
C. ROLLBACK TO SAVE POINT C;
E. ALTER TABLE employees SET UNUSED COLUMN sal;

Explanation
The two statements that complete a transaction are "ROLLBACK TO SAVE POINT C;" and "ALTER TABLE employees SET UNUSED COLUMN sal;". The "ROLLBACK TO SAVE POINT C;" statement allows the transaction to be rolled back to a specific save point, undoing any changes made after that point. The "ALTER TABLE employees SET UNUSED COLUMN sal;" statement modifies the table "employees" by setting the column "sal" as unused, indicating that it will no longer be used in any operations.

Rate this question:
39.

Which statement describes the ROWID data type?(Choose only one answer)
- A.
  
  Binary data up to 4 gigabytes.
- B.
  
  Character data up to 4 gigabytes.
- C.
  
  Raw binary data of variable length up to 2 gigabytes.
- D.
  
  Binary data stored in an external file, up to 4 gigabytes.
- E.
  
  A hexadecimal string representing the unique address of a row in its table.
Correct Answer
E. A hexadecimal string representing the unique address of a row in its table.

Explanation
The ROWID data type is a hexadecimal string that represents the unique address of a row in its table. This means that each row in the table has a unique identifier in the form of a hexadecimal string. This identifier is used to locate and access the specific row in the table. The other options do not accurately describe the ROWID data type.

Rate this question:
40.

You are granted the CREATE VIEW privilege. What does this allow you to do?(Choose only one answer)
- A.
  
  Create a table view.
- B.
  
  Create a view in any schema.
- C.
  
  Create a view in your schema.
- D.
  
  Create a sequence view in any schema.
- E.
  
  Create a view that is accessible by everyone.
- F.
  
  Create a view only of it is based on tables that you created.
Correct Answer
C. Create a view in your schema.

Explanation
The CREATE VIEW privilege allows the user to create a view in their own schema. This means that they can define a virtual table based on the data from one or more existing tables in their schema. The view can then be queried like a regular table, providing a customized and simplified view of the data.

Rate this question:
41.

Which statements about creating constraints are true?
- A.
  
  Constraint names must start with SYS_C.
- B.
  
  All constraints must be defines at the column level.
- C.
  
  Constraints can be created after the table is created.
- D.
  
  Constraints can be created at the same time the table is created.
- E.
  
  Information about constraints is found in the VIEW_CONSTRAINTS dictionary view.
Correct Answer(s)
C. Constraints can be created after the table is created.
D. Constraints can be created at the same time the table is created.

Explanation
Constraints can be created after the table is created and can also be created at the same time the table is created.

Rate this question:
42.

Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?(Choose only one answer)
- A.
  
  SELECT &1, "&2" FROM &3 WHERE last_name = '&4';
- B.
  
  SELECT &1, '&2' FROM &3 WHERE '& last_name = '&4';
- C.
  
  SELECT &1, &2 FROM &3 WHERE last_name = '&4';
- D.
  
  SELECT &1, '&2' FROM EMP WHERE last_name = '&4';
Correct Answer
C. SELECT &1, &2 FROM &3 WHERE last_name = '&4';

Explanation
The correct answer is the SQL statement that accepts user input for the columns to be displayed, the table name, and the WHERE condition. This statement allows the user to dynamically specify the columns, table name, and condition based on their requirements.

Rate this question:
43.

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:EMPLOYEESEMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2 (25)LAST_NAME VARCHAR2 (25)HIRE_DATE DATENEW EMPLOYEESEMPLOYEE_ID NUMBER Primary KeyNAME VARCHAR2 (60)Which DELETE statement is valid?(Choose only one answer)
- A.
  
  DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);
- B.
  
  DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_ employees);
- C.
  
  DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ('Carrey')'
- D.
  
  DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_ name = ('Carrey')'
Correct Answer
C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ('Carrey')'

Explanation
The correct answer is "DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ('Carrey')'". This statement is valid because it uses the correct syntax for the DELETE statement and specifies the condition for deleting records from the "employees" table based on the employee_id values that exist in the "new_employees" table where the name is 'Carrey'.

Rate this question:
44.

Examine the structure of the EMP_DEPT_VU view: Column Name Type Remarks EMPLOYEE_IDNUMBER From the EMPLOYEES table EMP_NAME VARCHAR2(30) From the EMPLOYEES table JOB_ID VARCHAR2(20) From the EMPLOYEES table SALARY NUMBER From the EMPLOYEES table DEPARTMENT_ID NUMBER From the DEPARTMENTS table DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table Which SQL statement produces an error?(Choose only one answer)
- A.
  
  SELECT * FROM emp_dept_vu;
- B.
  
  SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department _ id;
- C.
  
  SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department _ id, job_id;
- D.
  
  SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM (salary) > 20000
- E.
  
  None of the statements produce an error; all are valid.
Correct Answer
E. None of the statements produce an error; all are valid.

Explanation
The given view structure includes columns that are sourced from both the EMPLOYEES and DEPARTMENTS tables. The SELECT statements provided in the options are all valid and do not contain any errors. Therefore, none of the statements produce an error; all are valid.

Rate this question:
45.

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?(Choose only one answer)
- A.
  
  DROP emp_dept_uv;
- B.
  
  DELETE emp_dept_uv;
- C.
  
  REMOVE emp_dept_uv;
- D.
  
  DROP VIEW emp_dept_uv;
- E.
  
  DELETE VIEW emp_dept_uv;
- F.
  
  REMOVE VIEW emp_dept_uv;
Correct Answer
D. DROP VIEW emp_dept_uv;

Explanation
The correct answer is DROP VIEW emp_dept_uv; because the DROP VIEW statement is used to remove a view from the database schema. In this case, the view named EMP_DEPT_VU is being removed.

Rate this question:
46.

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?(Choose only one answer)
- A.
  
  CREATE ROLL registrar; GRANT MODIFY ON student_grant TO registrar; GRANT registrar to user 1, user2, user3
- B.
  
  CREATE NEW ROLE registrar; GRANT ALL ON student_grant TO registrar; GRANT registrar to user 1, user2, user3
- C.
  
  CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT ROLE to user1, user2, user3
- D.
  
  CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT registrar to user 1, user2, user3;
- E.
  
  CREATE registrar; GRANT CHANGE ON student_grant TO registrar; GRANT registrar;
Correct Answer
D. CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT registrar to user 1, user2, user3;

Explanation
The correct answer is "CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT registrar to user 1, user2, user3;". This set of statements creates a role called "registrar" and grants the UPDATE privilege on the "student_grant" table to the role. It then grants the "registrar" role to user 1, user 2, and user 3, allowing them to modify existing rows in the "STUDENT_GRADES" table.

Rate this question:
47.

Which describes the default behavior when you create a table?(Choose only one answer)
- A.
  
  The table is accessible to all users
- B.
  
  Tables are created in the public schema.
- C.
  
  Tables are created in your schema.
- D.
  
  Tables are created in the DBA schema
- E.
  
  You must specify the schema when the table is created.
Correct Answer
C. Tables are created in your schema.

Explanation
When you create a table, the default behavior is that the table is created in your schema. This means that only you, as the creator, will have access to the table by default. Other users will need to be granted permission in order to access the table.

Rate this question:
48.

Which are attributes of single row functions?
- A.
  
  Cannot be nested
- B.
  
  Manipulate data items
- C.
  
  Act on each row returned
- D.
  
  Return one result per row
- E.
  
  Accept only one argument and return only one value
- F.
  
  Accept arguments which can be a column or an expression
Correct Answer(s)
B. Manipulate data items
C. Act on each row returned
D. Return one result per row
E. Accept only one argument and return only one value

Explanation
Single row functions are functions that manipulate data items and act on each row returned. They return one result per row and accept only one argument and return only one value. These functions can accept arguments which can be a column or an expression. They cannot be nested.

Rate this question:
49.

You need to create a table named ORDERS that contain four columns:1 an ORDER_ID column of number data type2 aCUSTOMER_ID column of number data type3 an ORDER_STATUS column that contains a character data type4 aDATE_ORDERED column to contain the date the order was placed.When a row is inserted into the table, if no value is provided when the order was placed, today's date should be used instead. Which statement accomplishes this?(Choose only one answer)
- A.
  
  CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
- B.
  
  CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
- C.
  
  CREATE OR REPLACE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE);
- D.
  
  CREATE OR REPLACE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE = SYSDATE);
- E.
  
  CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE);
- F.
  
  CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE DEFAULT SYSDATE);
Correct Answer
E. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status NUMBER (10), date_ordered DATE = SYSDATE);

Explanation
The correct answer is "CREATE TABLE orders (
order_id NUMBER (10),
customer_id NUMBER (8),
order_status NUMBER (10),
date_ordered DATE = SYSDATE);"

This statement creates a table named ORDERS with four columns: ORDER_ID of number data type, CUSTOMER_ID of number data type, ORDER_STATUS of number data type, and DATE_ORDERED of date data type. The DATE_ORDERED column is set to the current date (SYSDATE) as the default value, so if no value is provided when inserting a row, today's date will be used instead.

Rate this question:
50.

The user Sue issues this SQL statement:GRANT SELECT ON sue. EMP TO alice WITH GRANT OPTION;The user Alice issues this SQL statement:GRANT SELECT ON sue. EMP TO reena WITH GRANT OPTION;The user Reena issues this SQL statement:GRANT SELECT ON sue. EMP TO timber;The user Sue issues this SQL statement:REVOKE select on sue. EMP FROM alice;For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?(Choose only one answer)
- A.
  
  Alice only
- B.
  
  Alice and Reena
- C.
  
  Alice, Reena, and Timber
- D.
  
  Sue, Alice, Reena, and Timber
Correct Answer
C. Alice, Reena, and Timber

Explanation
The revoke command revokes SELECT privileges on the SUE.EMP table for Alice, Reena, and Timber. This is because Sue initially granted SELECT privileges on the table to Alice with the GRANT OPTION. Alice then granted the same privileges to Reena with the GRANT OPTION. Finally, Reena granted the privileges to Timber without the GRANT OPTION. When Sue issues the REVOKE command to revoke SELECT privileges from Alice, it also cascades to revoke the privileges from Reena and Timber since they were granted the privileges through Alice.

Rate this question:

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

Current Version
Mar 21, 2023

Quiz Edited by
ProProfs Editorial Team
Nov 18, 2010

Quiz Created by
Sudha_test

1z0-007 Practice Test

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?

Which subqueries work?

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)

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)

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)

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

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

Which statement explicitly names a constraint?(Choose only one answer)

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)

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)

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

Which statements about subqueries are true?

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

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)

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)

Which are DML statements?

In which scenario would index be most useful?(Choose only one answer)

Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)DEPARTMENT_ID NUMBERSALARY NUMBERWhat is the correct syntax for an inline view?(Choose only one answer)

Which statements about subqueries are true?

Which are correct guidelines for naming database tables?

Which operator can be used with a multiple-row subquery?(Choose only one answer)

You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?(Choose only one answer)

You need to calculate the total of all salaries in the accounting department. Which group function should you use?(Choose only one answer)

Scott issues the SQL statements:CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14),loc VARCHAR2(13)};GRANT SELECT ON DEPT TI SUE;If Sue needs to select from Scott's DEPT table, which command should she use?(Choose only one answer)

A subquery can be used to _________(Choose only one answer).

Which is an SQL*Plus command?(Choose only one answer)

Which statement creates a new user?(Choose only one answer)

What is true about the WITH GRANT OPTION clause?(Choose only one answer)

Which two statements complete a transaction?(Choose only one answer)

Which statement describes the ROWID data type?(Choose only one answer)

You are granted the CREATE VIEW privilege. What does this allow you to do?(Choose only one answer)

Which statements about creating constraints are true?

Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?(Choose only one answer)

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:EMPLOYEESEMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2 (25)LAST_NAME VARCHAR2 (25)HIRE_DATE DATENEW EMPLOYEESEMPLOYEE_ID NUMBER Primary KeyNAME VARCHAR2 (60)Which DELETE statement is valid?(Choose only one answer)

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?(Choose only one answer)

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?(Choose only one answer)

Which describes the default behavior when you create a table?(Choose only one answer)

Which are attributes of single row functions?