Evaluate this SQL statement: SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d USING (department_id) WHERE d.department_id NOT IN (10,40) ORSER BY dept_name; The statement fails when executed. Which change fixes the error?（）
A . remove the ORDER BY clause
B . remove the table alias prefix from the WHERE clause
C . remove the table alias from the SELECT clause
D . prefix the column in the USING clause with the table alias
E . prefix the column in the ORDER BY clause with the table alias
F . replace the condition "d.department_id NOT IN (10,40)" in the WHERE clause with "d.department_id <> 10 AND d.department_id <> 40"
-
Evaluate the SQL statement:
https://assets.asklib.com/images/image2/2018080115100724106.jpg
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.
-
Evaluate this SQL statement:
https://assets.asklib.com/images/image2/2018080116554090144.jpg
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
-
Evaluate these two SQL statements:
https://assets.asklib.com/images/image2/2018080116163047395.jpg
What is true about them?（）
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.
-
Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? （）
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.
-
Evaluate the following SQL statements that are issued in the given order：
https://assets.asklib.com/images/image2/2018080114465944231.jpg
What would be the status of the foreign key EMP_MGR_FK？（）
A . It would be automatically enabled and deferred.
B . It would be automatically enabled and immediate.
C . It would remain disabled and has to be enabled manually using the ALTER TABLE command.
D . It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
-
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
-
Evaluate the SQL statement:
https://assets.asklib.com/images/image2/2018080115244941445.jpg
What will be displayed?（）
A . 0
B . 1
C . 0.00
D . An error statement
-
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 ORA-04043: 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.
-
Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(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 occupied by the DEPT table.
C . The DESCRIBE DEPT statement returns an error ORA-04043: 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.
-
Evaluate this SQL statement:
https://assets.asklib.com/images/image2/2018080116195195495.jpg
What will happen if you remove all the parentheses from the calculation?（）
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.
-
Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement? （）
A . It releases the storage space used by the table.
B . It does not release the storage space used by the table.
C . You can roll back the deletion of rows after the statement executes.
D . You can NOT roll back the deletion of rows after the statement executes.
E . An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
F . You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table
-
Evaluate the following command: SQL>ALTER SYSTEM SET db_securefile = 'IGNORE'; What is the impact of this setting on the usage of SecureFiles?（）
A . It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB
B . It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB
C . It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is specified to create the LOB
D . It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile
-
Evaluate the following command： SQL>ALTER SYSTEM SET db_securefile = ’IGNORE’； What is the impact of this setting on the usage of SecureFiles？（）
A . It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB
B . It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB
C . It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is specified to create the LOB
D . It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile
-
Evaluate the following command： SQL> CREATE TABLE design_data （id NUMBER， doc CLOB） LOB（doc） STORE AS SECUREFILE（DEDUPLICATE）； Which statement is true regarding the above command？（）
A . The LOB values are automatically compressed.
B . The LOB values are cached by default in the buffer cache.
C . The LOB values are automatically stored in encrypted mode.
D . All LOB data that is identical in two or more rows in a LOB column share the same data blocks.
-
Evaluate this SQL statement: SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMPLOYEES e, DEPARTMENTS 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
-
Evaluate the SQL statement: SELECT LPAD (salary,10,*) FROM EMP WHERE EMP _ ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?（）
A . 17000.00
B . 17000*****
C . ****170.00
D . **17000.00
E . an error statement
-
The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8,2) Evaluate this SQL statement: SELECT * FROM PRODUCTS ORDER BY price, product _ name; What is true about the SQL statement? （）
A . The results are not sorted.
B . The results are sorted numerically.
C . The results are sorted alphabetically.
D . The results are sorted numerically and then alphabetically.
-
Evaluate the following SQL statement used to create the PRODUCTS table:
https://assets.asklib.com/images/image2/2018080210365762118.jpg
Which statement is true regarding this command?（）
A . It executes successfully but partition pruning cannot happen for this partition key.
B . It produces an error because the TOTAL_VALUE column cannot be used as a partition key.
C . It produces an error because compression cannot be used for the TOTAL_VALUE partition key.
D . It executes successfully but the values in the TOTAL_VALUE column would not be physically stored in the partitions.
-
Evaluate the set of SQL statements:
https://assets.asklib.com/images/image2/201808011518065034.jpg
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 ORA-04043: 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.
-
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
-
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
-
Evaluate this SQL statement:What will happen if you remove all the parentheses from the calculation？（）
<img src='https://img2.soutiyun.com/ask/uploadfile/2019-03-27/b0aed9f70f3cf7c71c4d975acbe48082.jpg' />
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.
-
Evaluate the set of SQL statements:CREATE TABLE dept（deptno NUMBER（2),dname VARCNAR2（14),1oc VARCNAR2 （13));ROLLBACK;DESCRIBE DEPTWhat 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 ORA-04043: 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.
-
Examine the data of the EMPLOYEES table.EMPLOYEES （EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)Evaluate this SQL statement:What is its output？（）
<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/6bce46904716f2e8357f4849d2a3423d.jpg' />
<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/c5c76fa6bab26c6735c868df586306cf.jpg' />
A.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/0f921b8c15106e9d6cc8e36a50134106.jpg' />
B.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/d072515564553d90b433296aa71e021a.jpg' />
C.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/b4721c6b94a9b61c92cdbe82aabc3ae1.jpg' />
D.<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135449/b27c35e83798de4bbbccaef22fa31b48.jpg' />
E. The SQL statement produces an error.
