1Z0-007: Anthony

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), 1oc VARCNAR2 (13)); ROLLBACK; DESCRIBE DEPT What is true about the set? A. The DESCRIBE DEPT statement displays the structure of the DEPT table. B. The ROLLBACK statement frees the storage space occupies by the DEPT table. C. The DESCRIBE DEPT statement returns an error ORA04043: object DEPT does not exist. D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),1), 2) FROM dual; What will be displayed? A. 0 B. 1 C. 0.00 D. An error statement

A. 0

Which two statements about views are true? (Choose two.) A. A view can be created as read only. B. A view can be created as a join on two or more tables. C. A view cannot have an ORDER BY clause in the SELECT statement. D. A view cannot be created with a GROUP BY clause in the SELECT statement. E. A view must have aliases defined for the column names in the SELECT statement.

A. A view can be created as read only. B. A view can be created as a join on two or more tables.

Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? (Choose four) A. You cannot roll back this statement. B. All pending transactions are committed. C. All views based on the DEPT table are deleted. D. All indexes based on the DEPT table are dropped. E. All data in the table is deleted, and the table structure is also deleted. F. All data in the table is deleted, but the structure of the table is retained. G. All synonyms based on the DEPT table are deleted.

A. You cannot roll back this statement. B. All pending transactions are committed. D. All indexes based on the DEPT table are dropped. E. All data in the table is deleted, and the table structure is also deleted. Notes: Remember that VIEWS and SYNONYMS (normally created by the user) are not dropped.

Evaluate this SQL statement: SELECT ename, sal, 12* sal+100 FROM emp; The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"? A. No change is required to achieve the desired results. B. SELECT ename, sal, 12* (sal+100) FROM emp; C. SELECT ename, sal, (12* sal)+100 FROM emp; D. SELECT ename, sal +100,*12 FROM emp;

B. SELECT ename, sal, 12* (sal+100) FROM emp;

Evaluate this SQL statement: SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed? A. Selection, projection, join B. Difference, projection, join C. Selection, intersection, join D. Intersection, projection, join E. Difference, projection, product

A. Selection, projection, join Note: Sadly, I memorized this answer as selection and projection rhyme, and have the same number of syllables! haha

Which /SQL*Plus feature can be used to replace values in the WHERE clause? A. Substitution variables B. Replacement variables C. Prompt variables D. Instead of variables E. This feature cannot be implemented through /SQL*Plus.

A. Substitution variables

Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.) A. TIMESTAMP B. INTERVAL MONTH TO DAY C. INTERVAL DAY TO SECOND D. INTERVAL YEAR TO MONTH E. TIMESTAMP WITH DATABASE TIMEZONE

A. TIMESTAMP C. INTERVAL DAY TO SECOND D. INTERVAL YEAR TO MONTH

Which two are character manipulation functions? (Choose two.) A. TRIM B. REPLACE C. TRUNC D. TO_DATE E. MOD F. CASE

A. TRIM B. REPLACE

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12? A. SELECT ename, salary*12 'Annual Salary' FROM employees; B. SELECT ename, salary*12 "Annual Salary" FROM employees; C. SELECT ename, salary*12 AS Annual Salary FROM employees; D. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees

B. SELECT ename, salary*12 "Annual Salary" FROM employees; Note: The correct answer has Annual Salary" in proper-case and in quotation marks - "Annual Salary" Wrong: A. Single quotes C. No quotes and AS qualifier D. AS qualifier and no ;

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_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? 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.employees_id, e.first_name ||','|| e.last_name); B. MERGE 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); 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.employees_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);

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.employees_id, e.first_name ||','|| e.last_name); Notes: Use MERGE INTO, not just MERGE. Use MATCHED THEN UPDATE, not EXISTS.

The DBA issues this SQL command: CREATE USER scott IDENTIFIES by tiger; What privileges does the user Scott have at this point? A. No privileges. B. Only the SELECT privilege. C. Only the CONNECT privilege. D. All the privileges of a default user.

A. No privileges.

What is true about sequences? A. Once created, a sequence belongs to a specific schema. B. Once created, a sequence is linked to a specific table. C. Once created, a sequence is automatically available to all users. D. Only the DBA can control which sequence is used by a certain table. E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.

A. Once created, a sequence belongs to a specific schema.

The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with the letter "n"? A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, 1,1) = 'n'; B. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,1,1) FROM EMPLOYEES WHERE SUBSTR (ENAME, 1,1) = 'n'; C. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, 1,1) FROM EMPLOYEES WHERE INSTR (ENAME, 1,1) = 'n'; D. SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, 1,1) FROM EMPLOYEES WHERE INSTR (ENAME, 1,1) = 'n';

A. SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (ENAME, 1,1) = 'n'; Notes: Use INSTR, not SUBSTR. And... A is the only answer that even looks for the letter 'a'.

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"? A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; B. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual; C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual; D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual; E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) DEPARTMENT_ID NUMBER SALARY NUMBER What is the correct syntax for an inline view? A. SELECT a.last_name, a.salary, a.department_id, 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);

A. SELECT a.last_name, a.salary, a.department_id, 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; Note: The correct answer is the only one that has maxsal and max(salary), instead of JUST max(salary).

The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use? A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%'ESCAPE'\'; B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'; C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_'ESCAPE'\'; D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id '%SA_';

A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%'ESCAPE'\';

You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results? A. SELECT last_name FROM EMP WHERE last_ name LIKE '_A%'; B. SELECT last_name FROM EMP WHERE last name ='*A%' C. SELECT last_name FROM EMP WHERE last name ='_A%'; D. SELECT last_name FROM EMP WHERE last name LIKE '*A%'

A. SELECT last_name FROM EMP WHERE last_ name LIKE '_A%'; Note: _ indicates that there is a single character there. * means a wildcard, and can indicate more than one character.

Examine the SQL statements that creates the 7ORDERS table: CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL STATUS VARCHARD2(10) CHECK (status IN ('CREDIT','CASH')), PROD_ID_NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order id, order date)); For which columns would an index be automatically created when you execute the aboveSQL statement? (Choose two) A. SER_NO B. ORDER_ID C. STATUS D. PROD_ID E. ORD_TOTAL F. Composite index on ORDER_ID and ORDER_DATE

A. SER_NO F. Composite index on ORDER_ID and ORDER_DATE Notes: These are the first three columns in the Table as well.

Click the Exhibit button to examine the structures of the EMPLOYEES, DEPARTMENTS, and TAX tables. *** Insert Image *** For which situation would you use a nonequijoin query? A. to find the tax percentage for each of the employees B. to list the name, job_id, and manager name for all the employees C. to find the name, salary, and the department name of employees who are not working with Smith D. to find the number of employees working for the Administrative department and earning less than 4000 E. to display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned

A. to find the tax percentage for each of the employees Note: If this is the only question with TAX tables, I will remember it based on the only answer with TAX percentages.

Which two statements are true about WHERE and HAVING clauses? (Choose two) A. A WHERE clause can be used to restrict both rows and groups. B. A WHERE clause can be used to restrict rows only. C. A HAVING clause can be used to restrict both rows and groups. D. A HAVING clause can be used to restrict groups only. E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. F. A HAVING clause CANNOT be used in subqueries.

B. A WHERE clause can be used to restrict rows only. D. A HAVING clause can be used to restrict groups only. Note: Both answers revolve around the ONLY restriction that the clause can perform.

The EMPLOYEES table has these columns: LAST NAME VARCHAR2(35) SALARY NUMBER(8,2) HIRE_DATE DATE Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement: ALTER TABLE EMPLOYEES MODIFY ( SALARY DEFAULT 5000); What is true about your ALTER statement? A. Column definitions cannot be altered to add DEFAULT values. B. A change to the DEFAULT value affects only subsequent insertions to the table. C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type. D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

B. A change to the DEFAULT value affects only subsequent insertions to the table.

Which three statements about subqueries are true? (Choose three) 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.

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.

Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table? 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));

B. CREATE TABLE EMP (empno NUMBER(4), ename VARCNAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)); Notes: - The correct answer does not have NOT NULL, as this is not referenced in the question. This narrows the answer to 2 choices. - The only correct answer does not actually have FOREIGN KEY spelled out in the statement.

You need to create a table named ORDERS that contain four columns: 1. an ORDER_ID column of number data type 2. a CUSTOMER_ID column of number data type 3. an ORDER_STATUS column that contains a character data type 4. a DATE_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? 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);

B. CREATE TABLE orders ( order_id NUMBER (10), customer_id NUMBER (8), order_status VARCHAR2 (10), date_ordered DATE DEFAULT SYSDATE); Notes: B has tablename, number requirements, varchar2 requirements, and date default sysdate requirements. Wrong answers are missing varchar, do not have date default sysdate, or have create/replace table.

Which clause should you use to exclude group results? A. WHERE B. HAVING C. RESTRICT D. GROUP BY E. ORDER BY

B. HAVING

Which four are correct guidelines for naming database tables? (Choose four) 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 AZ, az, 0+, _, *, and #. E. Must contain only AZ, az, 09, _, $, and #. F. Must begin with a letter.

B. Must be 1-30 characters long. C. Should not be an Oracle Server reserved word. E. Must contain only: AZ, az, 09, _, $, and #. F. Must begin with a letter. WRONG: A. A database table cannot start with a number. D. A database table cannot contain an asterisk (*), as this is a type of wildcard (all).

Which constraint can be defined only at the column level? A. UNIQUE B. NOT NULL C. CHECK D. PRIMARY KEY E. FOREIGN KEY

B. NOT NULL

What does the TRUNCATE statement do? A. Removes the table B. Removes all rows from a table C. Shortens the table to 10 rows D. Removes all columns from a table E. Removes foreign keys from a table

B. Removes all rows from a table

Scott issues the SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)}; GRANT SELECT ON DEPT T0 SUE; If Sue needs to select from Scott's DEPT table, which command should she use? 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';

B. SELECT * FROM SCOTT. DEPT;

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

B. SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id,job_cat; Note: - This is the only answer that SELECTS and GROUPS BY the same two columns (dept_id and job_cat). No other answers have matching Selects and Groups By. - E. has incorrect spacing in the dept_id and job_cat columns.

Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables. *** Insert Image *** Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin placed his orders? A. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin' AND ord_date IN ('18JUL2000',' 21JUL2000'); B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin')); C. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders, customers WHERE cust_name = 'Martin'); D. SELECT ord_id, cust_id, ord_total FROM orders WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust_name = 'Martin');

B. SELECT ord_id, cust_id, ord_total FROM orders WHERE ord_date IN (SELECT ord_date FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin')); Note: The answer searches WHERE ord_date and cust_id. Wrong: A: Wrong Date C: Has FROM orders, customers WHERE cust_name = 'Martin'); but ORDERS does not have a cust_name column. D: Does not reference the Order Date (Ord_Date).

Examine the data in the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY EMPLOYEE_ID 101 Smith 20 120 SA_REP 4000 102 Martin 10 105 CLERK 2500 103 Chris 20 120 IT_ADMIN 4200 104 John 30 108 HR_CLERK 2500 105 Diana 30 108 IT_ADMIN 5000 106 Smith 40 110 AD_ASST 3000 108 Jennifer 30 110 HR_DIR 6500 110 Bob 40 EX_DIR 8000 120 Ravi 20 110 SA*DIR 6500 DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Admin 20 Education 30 IT 40 Human Resources Also 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), MGR_ID NUMBER REFERENCES employees(employee id), JOB_ID VARCHAR2(15). SALARY NUMBER); 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? 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.

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

Which four are valid Oracle constraint types? (Choose four.) A. CASCADE B. UNIQUE C. NONUNIQUE D. CHECK E. PRIMARY KEY F. CONSTANT G. NOT NULL

B. UNIQUE D. CHECK E. PRIMARY KEY G. NOT NULL Note: (P.U.NN.CH)

The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR; A. You obtain the results retrieved from the public synonym HR created by the database administrator. B. You obtain the results retrieved from the HR table that belongs to your schema. C. You get an error message because you cannot retrieve from a table that has the same name as a public synonym. D. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product. E. You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a FULL JOIN.

B. You obtain the results retrieved from the HR table that belongs to your schema.

Which two statements about sequences are true? (Choose two) A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value. B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence. C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence. D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column. E. If a sequence starting from a value 100 and incremented by 1 is used by more then one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence. F. You use REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence. C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.

Which four are attributes of single row functions? (Choose four.) 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

B. manipulate data items C. act on each row returned D. return one result per row F. accept arguments which can be a column or an expression Notes: The two wrong answers below are both restrictive/negative. A. Cannot E. Accept ONLY one

Evaluate the SQL statement: SELECT ROUND(45.953, 1), TRUNC(45.936, 2) FROM dual; Which values are displayed? A. 46 and 45 B. 46 and 45.93 C. 50 and 45.93 D. 50 and 45.9 E. 45 and 45.93 F. 45.95 and 45.93

C. 50 and 45.93

Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value? A. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL; B. ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL; C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL; D. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL; E. ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL; F. ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

C. ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL; Notes: The answer has NOT NULL, it does not have *is not null*. You MODIFY customer_name, not ADD customer_name. Look for Modify and Not Null. E is wrong because it says modify NAME instead of CUSTOMER_NAME.

Which statement explicitly names a constraint? 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);

C. ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id); Note: This is the only answer that specifically states ADD CONSTRAINT, without additional verbiage.

Which syntax turns an existing constraint on? A. ALTER TABLE table_name ENABLE constraint_name; B. ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint _ name; C. ALTER TABLE table_name ENABLE CONSTRAINT constraint _ name; D. ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint _ name; E. ALTER TABLE table_name TURN ON CONSTRAINT constraint _ name; F. ALTER TABLE table_name TURN ON CONSTRAINT constraint _ name;

C. ALTER TABLE table_name ENABLE CONSTRAINT constraint _ name;

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? A. Alice only B. Alice and Reena C. Alice, Reena, and Timber D. Sue, Alice, Reena, and Timber

C. Alice, Reena, and Timber Note: Everyone but Sue, the original user.

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two) A. SUM(start_date) B. AVG(start_date) C. COUNT(start_date) D. AVG(start_date, end_date) E. MIN(start_date) F. MAXIMUM(start_date)

C. COUNT(start_date) E. MIN(start_date)

Which two statements about creating constraints are true? (Choose two) A. Constraint names must start with SYS_C. B. All constraints must be defined 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.

C. Constraints can be created after the table is created. D. Constraints can be created at the same time the table is created.

Which three statements correctly describe the functions and use of constraints? (Choose three.) A. Constraints provide data independence. B. Constraints make complex queries easy. C. Constraints enforce rules at the view level. D. Constraints enforce rules at the table level. E. Constraints prevent the deletion of a table if there are dependencies. F. Constraints prevent the deletion of an index if there are dependencies.

C. Constraints enforce rules at the view level. D. Constraints enforce rules at the table level. E. Constraints prevent the deletion of a table if there are dependencies.

You are granted the CREATE VIEW privilege. What does this allow you to do? 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.

C. Create a view in your schema.

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_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 DELETE statement is valid? 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')'

C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ('Carrey')' Notes: Do not delete * Use IN after the WHERE clause The answer has Carrey in it

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table? (Choose three) A. INSERT INTO employees VALUES (NULL, 'JOHN','Smith'); B. INSERT INTO employees( first_name, last_name) VALUES ('JOHN','Smith'); C. INSERT INTO employees VALUES ('1000','JOHN','NULL'); D. INSERT INTO employees(first_name,last_name, employee_id) VALUES ('1000, 'john','Smith'); E. INSERT INTO employees (employee_id) VALUES (1000); F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',");

C. INSERT INTO employees VALUES ('1000','JOHN','NULL'); E. INSERT INTO employees (employee_id) VALUES (1000); F. INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, 'john',"); Notes: - All correct answers have 1000 - D is wrong because it inserts 1000 into the first_name column

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements insert a row into the table? (Choose three.) A. INSERT INTO employees VALUES (NULL, 'John', 'smith'); B. INSERT INTO employees (first_name, last_name) VALUES ('John', 'smith'); C. INSERT INTO employees VALUES ('1000, 'John', 'smith'); D. INSERT INTO employees (first_name, last_name, employee_id) VALUES (1000, 'John', 'smith'); E. INSERT INTO employees (employee_id) VALUES (1000); F. INSERT INTO employees ( employee_id, first_name, last_name, ) VALUES (1000, 'John','');

C. INSERT INTO employees VALUES ('1000, 'John', 'smith'); E. INSERT INTO employees (employee_id) VALUES (1000); F. INSERT INTO employees ( employee_id, first_name, last_name, ) VALUES (1000, 'John',''); Notes: All answer have 1000. D: is wrong because it has 1000 as first name, John as Last, and Smith as Emp ID

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_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL; What does the statement provide? A. All of the desired results B. Two of the desired results C. One of the desired results D. An error statement

C. One of the desired results

Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition? A. SELECT &1, "&2" FROM &3 WHERE last_name = '&8'; B. SELECT &1, '&2' FROM &3 WHERE '& last_name = '&8'; C. SELECT &1, &2 FROM &3 WHERE last_name = '&8'; D. SELECT &1, '&2' FROM EMP WHERE last_name = '&8';

C. SELECT &1, &2 FROM &3 WHERE last_name = '&8'; Note: This is just a standard select statement Select first_name, last_name from dba_users where last_name='BAILEY';

Click the Exhibit button and examine the data in the EMPLOYEES table. *** Insert Image *** Which three subqueries work? (Choose three.) 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 AVG(SALARY));

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); Notes: No correct answers have SELECT * All correct answers have GROUP BY department_id

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES Column 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? 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);

C. SELECT employee_id, d.department_id, department_name, salary FROM employees e JOIN departments d ON e.department _ id = d. department_id; Notes: The correct answer is the only one with employees e and join departments d.

Management has asked you to calculate the value 12*salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns: LAST NAME VARCNAR2(35) NOT NULL SALARY NUMBER(9,2) NOT NULL COMMISION_PCT NUMBER(4,2) Which statement ensures that a value is displayed in the calculated columns for all employees? A. SELECT last_name, 12*salary* commission_pct FROM emp; B. SELECT last_name, 12*salary* (commission_pct,0) FROM emp; C. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp; D. SELECT last_name, 12*salary*(decode(commission_pct,0)) FROM emp;

C. SELECT last_name, 12*salary*(nvl(commission_pct,0)) FROM emp; Note: The answer is the only one with the NVL value to account for the NOT NULL variables in the table. NVL function lets you substitute a value when a null value is encountered.

You need to calculate the total of all salaries in the accounting department. Which group function should you use? A. MAX B. MIN C. SUM D. COUNT E. TOTAL F. LARGEST

C. SUM

Which describes the default behavior when you create a table? 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.

C. Tables are created in your schema.

You own a table called EMPLOYEES with this table structure: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement? DELETE employees; A. You get an error because of a primary key violation. B. The data and structure of the EMPLOYEES table are deleted. C. The data in the EMPLOYEES table is deleted but not the structure. D. You get an error because the statement is not syntactically correct.

C. The data in the EMPLOYEES table is deleted but not the structure. Note: This is true because you are DELETING instead of DROPPING. Deleting will remove the data and leave the structure of the table.

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement? A. The SQL statement displays the desired results. B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results. D. The WHERE clause should be changed to use an outer join to display the desired results.

C. The operator in the WHERE clause should be changed to display the desired results. Notes: Because this should be WHERE DEPARTMENT_ID LIKE NULL; ???

Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_ marks; What is the result of the SELECT statement? A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject. B. The statement returns an error at the SELECT clause. C. The statement returns an error at the WHERE clause. D. The statement returns an error at the ORDER BY clause.

C. The statement returns an error at the WHERE clause. Notes: Not sure, but how can subj1 be greater than subj1. How can subj2 be greater than subj2? This might be why the WHERE clause is broken.

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) DEFAULT 'SA_REP' SAL NUMBER COMM_PCT NUMBER MGR_ID NUMBER DEPARTMENT_ID NUMBER You need to update the records of employees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below: JOB_ID: Default value specified for this column definition. SAL: Maximum salary earned for the job ID SA_REP. COMM_PCT: Default value specified for this commission percentage column, if any. If no default value is specified for the column, the value should be NULL. DEPARTMENT_ID: Supplied by the user during run time through substitution variable. Which UPDATE statement meets the requirements? A. UPDATE employees SET job_id = DEFAULT AND Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') AND comm_pct = DEFAULT AND department_id = &did WHERE employee _id IN (103,115); B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department_id = &did WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP'; C. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP'), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115); D. UPDATE employees SET job_id = DEFAULT, Sal = MAX(sal), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115) AND job _ id = 'SA_ REP'; E. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHERE employee_id IN (103,115);

C. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP'), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115); Note: C: Sal = (SELECT MAX(sal) comm_pct = DEFAULT, A. Incorrectly has an AND clause. B. Incorrectly has an AND clause, no Sal = (SELECT MAX(sal). D. No Sal = (SELECT MAX(sal). E. Incorrectly has comm_pct = DEFAULT OR NULL, (instead of just DEFAULT).

When should you create a role? (Choose two) A. To simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyy statement. B. To grant a group of related privileges to a user. C. When the number of people using the database is very high. D. To simplify the process of granting and revoking privileges. E. To simplify profile maintenance for a user who is constantly traveling.

C. When the number of people using the database is very high. D. To simplify the process of granting and revoking privileges.

Top N analysis requires _____ and _____. (Choose two.) A. the use of rowid B. a GROUP BY clause C. an ORDER BY clause D. only an inline view E. an inline view and an outer query

C. an ORDER BY clause E. an inline view and an outer query

Which two statements complete a transaction? (Choose two) 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;

D. GRANT SELECT ON employees TO SCOTH E. ALTER TABLE employees SET UNUSED COLUMN sal; Note: Both correct answers make a change to the table, without deleting it. One answer grants permissions, and the other alters the table.

Which substitution variable would you use if you want to reuse the variable without prompting the user each time? A. & B. ACCEPT C. PROMPT D. &&

D. &&

You need to change the definition of an existing table. The COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up to 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows. Which statement is valid? A. ALTER TABLE commercials MODIFY (description CHAR2(2000)); B. ALTER TABLE commercials CHANGE (description CHAR2(2000)); C. ALTER TABLE commercials CHANGE (description VARCHAR2 (2000)); D. ALTER TABLE commercials MODIFY (description VARCHAR2 (2000)); E. You cannot increase the size of a column if the table has rows.

D. ALTER TABLE commercials MODIFY (description VARCHAR2 (2000)); Notes: You are MODIFYING, not CHANGING. You are using VARying CHARacters, not plain Characters (hence VARCHAR2).

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

D. ALTER TABLE students ADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id); Notes: - C & D are the same answer, but D is correct for some reason. Ignore the odd spacing in stud_id_pk and answer D.

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? A. ALTER TABLE students ADD PRIMARY KEY _ 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);

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table. Which set of statements accomplishes this? A. CREATE 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;

D. CREATE ROLL registrar; GRANT UPDATE ON student_grant TO registrar; GRANT registrar to user 1, user2, user3; Notes: The answer has GRANT UPDATE ON, but grants Register, not Roll.

Which statement creates a new user? 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;

D. CREATE USER susan IDENTIFIED BY blue;

Which object privileges can be granted on a view? A. none B. DELETE, INSERT,SELECT C. ALTER, DELETE, INSERT, SELECT D. DELETE, INSERT, SELECT, UPDATE

D. DELETE, INSERT, SELECT, UPDATE

Which is an /SQL*Plus command? A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME

D. DESCRIBE

Which is an iSQL*Plus command? A. INSERT B. UPDATE C. SELECT D. DESCRIBE E. DELETE F. RENAME

D. DESCRIBE

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema? 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;

D. DROP VIEW emp_dept_uv;

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. F.GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION;

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

Which operator can be used with a multiplerow subquery? A. = B. LIKE C. BETWEEN D. NOT IN E. IS F. <>

D. NOT IN Note: This makes sense. You can ask for data that appears in one row, but NOT IN another row.

You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created.) How do you obtain the definition of the view? A. Use the DESCRIBE command in the EMP_DEPT VU view. B. Use the DEFINE VIEW command on the EMP_DEPT VU view. C. Use the DESCRIBE VIEW command on the EMP_DEPT VU view. D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view. E. Query the USER_SOURCE data dictionary view to search for the EMP_DEPT_VU view. F. Query the USER_OBJECTS data dictionary view to search for the EMP_DEPT_VU view.

D. Query the USER_VIEWS data dictionary view to search for the EMP_DEPT_VU view. Notes: Definitions are stored in Dictionaries. It is a USER VIEW as the USER (you) created the VIEW. (correct???)

A subquery can be used to _________. 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

D. Retrieve data based on an unknown condition

The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_ADDRESS VARCHAR2(150) CUSTOMER_PHONE VARCHAR2(20) You need to produce output that states "Dear Customer customer_name, ". The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output? A. SELECT dear customer, customer_name, FROM customer; B. SELECT "Dear Customer", customer_name, ||',' FROM customer; C. SELECT 'Dear Customer' || customer_name ',' FROM customer; D. SELECT 'Dear Customer' || customer_name || ',' FROM customer; E. SELECT "Dear Customer" || customer_name || "," FROM customer; F SELECT 'Dear Customer' || customer_name || ',' FROM customer;

D. SELECT 'Dear Customer' || customer_name || ',' FROM customer; Note: The other answers are missing the complete set of ||'s. E: has too many ||'s.

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith? A. SELECT COUNT (*) FROM employees WHERE last _name='smith'; B. SELECT COUNT (dept_id) FROM employees WHERE last _name='smith'; C. SELECT DISTINCT (COUNT (dept_id) FROM employees WHERE last _name='smith'; D. SELECT COUNT (DISTINCT dept_id) FROM employees WHERE last _name='smith'; E. SELECT UNIQE (dept_id) FROM employees WHERE last _name='smith';

D. SELECT COUNT (DISTINCT dept_id) FROM employees WHERE last _name='smith'; Note: In the question, DIFFERENT indicates that you will need to add DISTINCT to the statement. Since it wants different departments, choose distinct dept_id.

You want to display the titles of books that meet these criteria: 1. Purchased before January 21, 2001 2. Price is less then $500 or greater than $900 You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use? A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21JAN2001' ORDER BY purchase_date; B. SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < '21JAN2001' ORDER BY purchase date ASC; C. SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < '21JAN2001' ORDER BY purchase date DESC; D. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '21JAN2001' ORDER BY purchase date DESC;

D. SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '21JAN2001' ORDER BY purchase date DESC;

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns: CUST_ID NUMBER(4) NOT NULL CUST_NAME VARCHAR2(100) NOT NULL CUST_ADDRESS VARCHAR2(150) CUST_PHONE VARCHAR2(20) Which SELECT statement accomplishes this task? A. SELECT* FROM customers; B. SELECT name, address FROM customers; C. SELECT id, name, address, phone FROM customers; D. SELECT cust_name, cust_address FROM customers; E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

D. SELECT cust_name, cust_address FROM customers;

The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results? A. SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES; B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES; C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES; D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;

D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES; Note: The answer is the only one with the NVL value to account for a NULL commission_pct in the table. NVL function lets you substitute a value when a null value is encountered.

In which scenario would index be most useful? 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

D. The indexed column contains a wide range of values

You define a multiplerow subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed? 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 multiplerow subquery cannot be used with the comparison operator. E. You cannot define a multiplerow subquery in the WHERE clause of a SQL query.

D. The main query fails because the multiplerow subquery cannot be used with the comparison operator.

Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables. Evaluate the SQL statement: *** Insert Image *** SELECT * FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Smith'); What is the result when the query is executed? A. Insert Picture B. Insert Picture C. Insert Picture D. The query fails because the subquery returns more than one row. E. The query fails because the outer query and the inner query are using different tables.

D. The query fails because the subquery returns more than one row.

Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables. Insert Picture On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. On the DEPARTMENTS table, DEPARTMENT_ID is the primary key. Evaluate this UPDATE statement: UPDATE employees SET mgr_id = (SELECT mgr_id FROM employees WHERE dept_id = (SELECT department_id FROM departments WHERE department_name = 'Administration')), Salary = (SELECT salary FROM employees WHERE emp_name = 'Smith') WHERE job_id = 'IT_ADMIN'; What happens when the statement is executed? A. The statement executes successfully, leaves the manager ID as the existing value, and changes the salary to 4000 for the employees with ID 103 and 105. B. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 for the employees with ID 103 and 105. C. The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for the employees with ID 103 and 105. D. The statement fails because there is more than one row matching the employee name Smith. E. The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES table. F. The statement fails because there is no 'Administration' department in the DEPARTMENTS table.

D. The statement fails because there is more than one row matching the employee name Smith.

Which data dictionary table should you query to view the object privileges granted to the user on specific columns? A. USER_TAB_PRIVS_MADE B. USER_TAB_PRIVS C. USER_COL_PRIVS_MADE D. USER_COL_PRIVS

D. USER_COL_PRIVS

Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000? A. ORDER BY SALARY > 5000 B. GROUP BY SALARY > 5000 C. HAVING SALARY > 5000 D. WHERE SALARY > 5000

D. WHERE SALARY > 5000 Note: - Order and Group by would just change the order of the results. It would not LIMIT the results to only > 5000. - The HAVING clause is used for GROUPING.

Which three are true regarding the use of outer joins? (Choose three.) A. You cannot use IN operator in a condition that involves an outerjoin. B. You use (+) on both sides of the WHERE condition to perform an outerjoin. C. You use (*) on both sides of the WHERE condition to perform an outerjoin. D. You use an outerjoin to see only the rows that do not meet the join condition. E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin. F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.

D. You use an outerjoin to see only the rows that do not meet the join condition. E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin. F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator. Notes: E. You have one (+) following the column name. You would never have a (+) before the name, eliminating B.

In which scenario would TOP N analysis be the best solution? A. You want to identify the most senior employee in the company. B. You want to find the manager supervising the largest number of employees. C. You want to identify the person who makes the highest salary for all employees. D. You want to rank the top three sales representatives who have sold the maximum number of products.

D. You want to rank the top three sales representatives who have sold the maximum number of products.

Which statement describes the ROWID data type? 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.

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

The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER (4) NOT NULL CUSTOMER_NAME VARCHAR2 (100) NOT NULL STREET_ADDRESS VARCHAR2 (150) CITY_ADDRESS VARHCAR2 (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. You need to determine how dispersed your customer base is. Which expression finds the number of different countries represented in the CUSTOMERS table? A. COUNT(UPPER(country_address)) B. COUNT(DIFF(UPPER(country_address))) C. COUNT(UNIQUE(UPPER(country_address))) D. COUNT DISTINTC UPPER(country_address) E. COUNT(DISTINTC (UPPER(country_address)))

E. COUNT(DISTINTC (UPPER(country_address)))

Examine the structure of the EMPLOYEES table: Column name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCNAR2(30) FIRST_NAME VARCNAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER You need to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task? A. CREATE INDEX NAME _IDX (first_name, last_name); B. CREATE INDEX NAME _IDX (first_name, AND last_name) C. CREATE INDEX NAME_IDX ON (First_name, last_name); D. CREATE INDEX NAME_IDX ON employees (First_name, AND last_name); E. CREATE INDEX NAME_IDX ON employees (First_name, last_name); F. CREATE INDEX NAME_IDX FOR employees (First_name, last_name);

E. CREATE INDEX NAME_IDX ON employees (First_name, last_name); Wrong: A-C: These do not even point to the Employees table. D: Has AND between First_name, last_name) F: Says FOR employees instead of ON employees

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? 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.

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; Notes: To MODIFY the view, you must run CREATE OR REPLACE VIEW.

What is true about the WITH GRANT OPTION clause? 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.

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

Examine the structure of the EMP_DEPT_VU view: Column Name Type Remarks EMPLOYEE_ID NUMBER 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? 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.

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

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? 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.

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. Notes: The statement refers to dept_id throughout.

What is true about joining tables through an equijoin? A. You can join a maximum of two tables through an equijoin. B. You can join a maximum of two columns through an equijoin. C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement. D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns. E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n1 join conditions.

E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n1 join conditions.

Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables. ***Insert Picture*** You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use? A. SELECT last_name, department_name FROM employees NATURAL JOIN departments; B. SELECT last_name, department_name FROM employees JOIN departments ; C. SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id); D. SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); E. SELECT last_name, department_name FROM employees FULL JOIN departments ON (e.department_id = d.department_id); F. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

F. SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); Note: The answer is the only option that includes a LEFT OUTER JOIN.

The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest. Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar? A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC; B. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC; C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa; D. SELECT student_id, gpa FROM student_grades ORDER BY gpa; E. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa DESC; F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC; Note: Order by Descending, *not* "Sort" Order by.