-
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))
-
The EMP table has these columns:
https://assets.asklib.com/images/image2/2018080116130784614.jpg
Management wants a list of names of employees who have been with the company for more than five years. Which SQL statement displays the required results?（）
A . SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE > 5;
B . SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5;
C . SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5;
D . SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5
-
You want to check the EMP table in the user， Scott’s schema for block corruption. You also want to fix the corrupted blocks， if any. How will you accomplish the required task？（）
A . by using the ANALYZE utility
B . by using the DBVERIFY utility
C . by using the DB_BLOCK_CHECKING parameter
D . by using the DBMS_REPAIR package
-
Why would you use the following FLASHBACK TABLE command？（） FLASHBACK TABLE emp TO TIMESTAMP （’11：45’.’hh12：mi’）；
A . to undo the changes made to the EMP table since the specified time
B . to restore the EMP table that was wrongly dropped from the database
C . to view the transactions that have modified the EMP table since the specified time
D . to view the changes made to the EMP table for one or more rows since the specified time
-
Which SQL statement defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table?（）
A . CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(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 VARCHAR2(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 VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp_deptno_fk REFERENCES dept (deptno))
-
Why would you use the following FLASHBACK TABLE command？（） FLASHBACK TABLE emp TO TIMESTAMP （’11：45’，’hh12：mi’）；
A . to undo the changes made to the EMP table since the specified time
B . to restore the EMP table that was wrongly dropped from the database
C . to view the transactions that have modified the EMP table since the specified time
D . to view the changes made to the EMP table for one or more rows since the specified time
E . to recover the EMP table to a point in time in the past by restoring the most recent backu
-
You executed the following FLASHBACK TABLE command： FLASHBACK TABLE emp TO TIMESTAMP （’11：45’，’hh12：mi’）; Which two statements are correct？（）
A . The FLASHBACK TABLE statement will not be written to the alert log file.
B . The changes made to the EMP table since the specified time will be undone.
C . The EMP table that was dropped by mistake from the database will be restored.
D . The FLASHBACK TABLE statement will be executed within a single transaction.
E . The FLASHBACK TABLE statement will not maintain the existing indexes on the EMP table.
F . The list of transactions that have modified the EMP table since the specified time will be displayed.
-
You need to base a data block on the EMP table, but you do not want to give the users of the application access privileges on the table. Which type of data source would you use to create the data block?（）
A . table
B . transactional trigger
C . stored procedure
D . FROM clause query
-
The CUSTOMERS table has these columns:
https://assets.asklib.com/images/image2/2018080116294948484.jpg
The CUSTOMER_ID column is the primary key for the table.Which two statements find the number of customers? （）
A . SELECT TOTAL(*) FROM customers;
B . SELECT COUNT(*) FROM customers;
C . SELECT TOTAL(customer_id) FROM customers;
D . SELECT COUNT(customer_id) FROM customers;
E . SELECT COUNT(customers) FROM customers;
F . SELECT TOTAL(customer_name) FROM customer
-
In which two scenarios should you issue the following command？（） FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP SYSTIMESTAP- INTERVAL ’15’ MINUTE；
A . when the schema of the user， SCOTT， was deleted by mistake 15 minutes ago
B . when the table EMP of the SCOTT schema was dropped by mistake 15 minutes ago
C . when some rows of the table， EMP， in the SCOTT schema were deleted by mistake 15 minutes ago
D . when some incorrect values were inserted in the EMP table in the user Scott’s schema during the last 15 minutes
E . never， because FLASHBACK TABLE is not a valid command in Oracle 10g.
-
Which SQL statement defines the FOREIGN KEY constraint on the DEPTNO column of the EMP table?（）
A .https://assets.asklib.com/images/image2/2018080115165365786.jpg
B .https://assets.asklib.com/images/image2/2018080115165818527.jpg
C .https://assets.asklib.com/images/image2/2018080115170192388.jpg
D .https://assets.asklib.com/images/image2/2018080115170459610.jpg
-
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
-
You are performing the shrink operation on the SCOTT.EMP table. You want to perform the same operation on all dependent objects of the SCOTT.EMP table. What is NOT a prerequisite for the operation？（）
A . You must enable the row movement on the SCOTT.EMP table.
B . You must define the tablespace of the SCOTT.EMP table for automatic segment space management.
C . You must use the CASCADE clause in the ALTER TABLE SHRINK SPACE statement.
D . You must use the COMPACT clause in the ALTER TABLE SHRINK SPACE statement.
-
Why would you use the following FLASHBACK TABLE command？（） FLASHBACK TABLE emp TO TIMESTAMP （’11：45’，’hh12：mi’）;
A . to undo the changes made to the EMP table since the specified time
B . to restore the EMP table that was wrongly dropped from the database
C . to view the transactions that have modified the EMP table since the specified time
D . to view the changes made to the EMP table for one or more rows since the specified time
E . to recover the EMP table to a point in time in the past by restoring the most recent backu
-
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.
-
A redaction policy was added to the SAL column of the SCOTT.EMP table: All users have their default set of system privileges. For which three situations will data not be redacted?（）
A . SYS sessions, regardless of the roles that are set in the session
B . SYSTEM sessions, regardless of the roles that are set in the session
C . SCOTT sessions, only if the MGR role is set in the session
D . SCOTT sessions, only if the MGR role is granted to SCOTT
E . SCOTT sessions, because he is the owner of the table
F . SYSTEM session, only if the MGR role is set in the sessio
-
You need to check the EMP_EAST partition in the EMPLOYEES table for physical corruptions. You also need to verify that the rows belong to the correct partition. Which option could you use？（）
A . LogMiner
B . The DBNEWID utility
C . The DBVERIFY utility
D . The ANALYZE command
E . The RMAN REPORT command
F . The RMAN CROSSCHECK command.
G . The RMAN BLOCKRECOVER command.
-
You are shrinking the SCOTT.EMP table by executing the ALTER TABLE SCOTT.EMP SHRINK SPACE CASCADE statement. What is a prerequisite for shrinking the SCOTT.EMP table？（）
A . You must enable the block change tracking feature.
B . You must have enable the flashback feature.
C . You must use the OMF in your database.
D . You must define the tablespace of the SCOTT.EMP table for automatic segment space management.
-
The CUSTOMERS table has these columns:
https://assets.asklib.com/images/image2/2018080116181325145.jpg
Which statement finds the rows in the CUSTOMERS table that do not have a postal code?（）
A . SELECT customer_id, customer_name FROM customers WHERE postal_code CONTAINS NULL;
B . SELECT customer_id, customer_name FROM customers WHERE postal_code = '________';
C . SELECT customer_id, customer_name FROM customers WHERE postal_code IS NULL;
D . SELECT customer_id, customer_name FROM customers WHERE postal code IS NVL;
E . SELECT customer_id, customer_name FROM customers WHERE postal_code = NULL
-
You executed the following FLASHBACK TABLE command： FLASHBACK TABLE emp TO TIMESTAMP （’11：45’，’hh12：mi’）； Which two statements are correct？ （）
A . The FLASHBACK TABLE statement will not be written to the alert log file.
B . The EMP table that was dropped by mistake from the database will be restored.
C . The changes made to the EMP table since the specified time will be undone.
D . The FLASHBACK TABLE statement will not maintain the existing indexes on the EMP table.
E . The FLASHBACK TABLE statement will be executed within a single transaction.
F . The list of transactions that have modified the EMP table since the specified time will be displayed.
-
The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) Which statement finds the highest grade point average (GPA) per semester?（）
A . SELECT MAX (gpa) FROM student _ grades WHERE gpa IS NOT NULL;
B . SELECT (gpa) FROM student _ grades GROUP BY semester_end WHERE gpa IS NOT NULL;
C . SELECT MAX (gpa) FROM student _ grades WHERE gpa IS NOT NULL GROUP BY semester_end;
D . SELECT MAX (gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student _ grades;
E . SELECT MAX (gpa) FROM student _ grades GROUP BY semester_end WHERE gpa IS NOT NULL
-
The Peer Assessment process has four phases. These are:
-
The STUDENT_GRADES table has these columns:The register has requested a report listing the students‘ grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this？（）
<img src='https://img2.soutiyun.com/ask/uploadfile/2019-03-27/96672f85a7852424612e04353e00d7a4.jpg' />
A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC;
B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC;
C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC;
D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC;
E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;
-
The EMPLOYEES table contains these columns:EMPLOYEE_ID NUMBER（4)LAST_NAME VARCHAR2 （25)JOB
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_‘;
