-
Examine the following statement that is used to modify the primary key constraint on the SALES table： SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE； Which three statements aretrue regarding the above command（）
A . The constraint remains valid.
B . The index on the constraint is dropped.
C . It allows the loading of data into the table using SQL *Loader.
D . New data conforms to the constraint， but existing data is not checked.
E . It allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.
-
Examine the statement： SQL> CREATE TABLESPACE user_data 2> EXTENT MANAGEMENT LOCAL 3> SEGMENT SPACE MANAGEMENT AUTO； Which twp assumptions must be true for this statement to execute successfully？（）
A . Oracle Managed Files are used for this instance.
B . The USER_DATA tablespace is managed using FET$/UET$ tables.
C . The COMPATIBLE initialization parameter must be 9.0.0 or higher.
D . Space within segments in the USER_DATA tablespace is managed with freelists.
-
Examine these two statements:Which three are true about the MRKT tablespace?（）
A . The MRKT tablespace is created as a small file tablespace, because the file size is less than the minimum required for big file files.
B . The MRKT tablespace may be dropped if it has no contents.
C . Users who were using the old default tablespace will have their default tablespaces changed to the MRKT tablespace.
D . No more data files can be added to the tablespace.
E . The relative file number of the tablespace is not stored in rowids for the table rows that are stored in the MRKT tablespace.
-
Examine the command： SQL> ALTER USER skd ACCOUNT LOCK； Which two statements are true after the command is executed（）
A . The SKD user cannot log in to the database instance.
B . The objects owned by the SKD user are not accessible to any user.
C . The other users can access the objects owned by the SKD user， on which they have access.
D . The password for the SKD user expires and the user is forced to change the password at the next log in.
-
Examine the contents of SQL loader control file: Which three statements are true regarding the SQL* Loader operation performed using the control file?（）
A . An EMP table is created if a table does not exist. Otherwise, if the EMP table is appended with the loaded data.
B . The SQL* Loader data file myfile1.dat has the column names for the EMP table.
C . The SQL* Loader operation fails because no record terminators are specified.
D . Field names should be the first line in the both the SQL* Loader data files.
E . The SQL* Loader operation assumes that the file must be a stream record format file with the normal carriage return string as the record terminator.
-
Examine the command that is used to create a table： SQL> CREATE TABLE orders（oid NUMBER（6） PRIMARY KEY， odate DATE， ccode NUMBER（6）， oamt NUMBER（10，2）） TABLESPACE users； Which two statements are true about the effect of the above command） （）
A . A CHECK constraint is created on the OID column.
B . A NOT NULL constraint is created on the OID column.
C . The ORDERS table is the only object created in the USERS tablespace.
D . The ORDERS table and a unique index are created in the USERS tablespace.
E . The ORDERS table is created in the USERS tablespace and a unique index is created on the OIDcolumn in the SYSTEM tablespace.
-
View the Exhibit to examine the Automatic SQL Tuning result details.Which action would you suggest for the selected SQL statement in the Exhibit?（）https://assets.asklib.com/images/image2/201808021408025170.png
A . Accept the recommended SQL profile.
B . Collect statistics for the related objects.
C . Run the Access Advisor for the SQL statement.
D . Run the Segment Advisor for recommendations.
-
Examine the following statement that is used to modify the constraint on the SALES table： SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE； Which three statements are true regarding the above command（）
A . The constraint remains valid.
B . The index on the constraint is dropped.
C . It allows the loading of data into the table using SQL*Loader.
D . New data conforms to the constraint， but existing data is not checked
E . It allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.
-
Examine the following output： SQL> SELECT index_name，status FROM dba_indexes WHERE status=’UNUSABLE’； INDEX_NAME STATUS------------------------------ ----------- EIND UNUSABLE Which two statements about the above index are true（）
A . It is ignored by the query optimizer.
B . It is not used while the index is being rebuilt.
C . The index cannot be rebuilt， and has to be re-created.
D . The index is automatically rebuilt when used the next time
-
Examine the command： SQL> DBMS_STATS.SET_TABLE_PREFS（’SH’， ’CUSTOMERS’， ’PUBLISH’， ’false’）； Which statement describes the effect of the above command（）
A . Automatic statistics collection is stopped for the CUSTOMERS table.
B . Statistics for the CUSTOMERS table are locked and cannot be overwritten.
C . Existing statistics for the CUSTOMERS table become unusable for the query optimizer.
D . Subsequently， statistics gathered on the CUSTOMERS table are stored as pending statistics.
-
Examine the structures of the EMPLOYEES and TAX tables.
https://assets.asklib.com/images/image2/2018080116321734981.jpg
You need to find the percentage tax applicable for each employee. Which SQL statement would you use?（）
A . SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
B . SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary > t.min_salary, tax_percent
C . SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary
D . You cannot find the information because there is no common column between the two tables.
-
Click the Exhibit button to examine the structures of the EMPLOYEES and TAX tables.
You need to find the percentage tax applicable for each employee. Which SQL statement would you use?（）
https://assets.asklib.com/images/image2/2018080114273930960.jpg
A . SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t ON e.salary BETWEEN t.min_salary AND t.max_salary;
B . SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t WHERE e.salary > t.min_salary AND < t.max_salary;
C . SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t ON (MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary);
D . You cannot find the information because there is no common column between the two tables.
-
Examine the command： SQL> ALTER USER skd ACCOUNT LOCK； Which two statements are true after the command is executed（）
A . The SKD user cannot log in to the database instance.
B . The objects owned by the SKD user are not accessible to any user.
C . The other users can access the objects owned by the SKD user， on which they have access.
D . The password for the SKD user expires and the user is forced to change the password at the next log i
-
Examine the command that is used to create a table： SQL> CREATE TABLE orders（oid NUMBER（6） PRIMARY KEY， odate DATE， ccode NUMBER （6）， oamtNUMBER（10，2）） TABLESPACE users； Which two statements are true about the effect of the above command（）
A . A CHECK constraint is created on the OID column.
B . A NOT NULL constraint is created on the OID column.
C . The ORDERS table is the only object created in the USERS tablespace.
D . The ORDERS table and a unique index are created in the USERS tablespace.
E . The ORDERS table is created in the USERS tablespace and a unique index is created on the OID columnin the SYSTEM tablespace.
-
Examine these statements: CREATE ROLE registrar GRANT UPDATE ON dtudent_grades TO registrar; GRANT registrar to user1, user2, user3; What does this set of SQL statements do? （）
A . The set of statements contains an error and does not work.
B . It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
C . It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users.
D . It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role.
E . It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object.
F . It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar.
-
Examine the statement: Create synonym emp for hr. employees; What happens when you issue the statement? （）
A . An error is generated.
B . You will have two identical tables in the HR schema with different names.
C . You create a table called employees in the HR schema based on you EMP table.
D . You create an alternative name for the employees table in the HR schema in your own schema.
-
Examine this command: SQL > exec DBMS_STATS.SET_TABLE_PREFS (‘SH’, ‘CUSTOMERS’, ‘PUBLISH’, ‘false’); Which three statements are true about the effect of this command?（）
A . Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
B . Statistics collection is not done for the CUSTOMERS table when database stats are gathered.
C . Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
D . Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending statistics.
E . Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending statistics.
-
Examine the commands executed to monitor database operations: $> conn sys oracle/oracle@prod as sysdba SQL > VAR eid NUMBER SQL > EXEC: eid := DBMS_SQL_MONITOR.BEGIN_OPERATION (‘batch_job’ , FORCED_TRACKING => ‘Y’); Which two statements are true?（）
A . Database operations will be monitored only when they consume a significant amount of resource.
B . Database operations for all sessions will be monitored.
C . Database operations will be monitored only if the STATISTICS_LEVEL parameter is set to TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS is set DIAGNISTIC + tuning.
D . Only DML and DDL statements will be monitored for the session.
E . All subsequent statements in the session will be treated as one database operation and will be monitored.
-
Click the Exhibit button to examine the structures of the EMPLOYEES and TAX tables.You need to find the percentage tax applicable for each employee. Which SQL statement would you use？（）
<img src='https://img2.soutiyun.com/shangxueba/ask/17133001-17136000/17135988/ea49d0916fc0345195a85a9b2816cb50.jpg' />
A.SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t ON e.salary BETWEEN t.min_salary AND t.max_salary;
B.SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t WHERE e.salary ＞ t.min_salary AND ＜ t.max_salary;
C.SELECT employee_id, salary, tax_percent FROM employees e JOIN tax t ON (MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary);
D.You cannot find the information because there is nocommon column between the two tables.
-
Examine the structure of the STUDENTS table: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？（）
<img src='https://img2.soutiyun.com/ask/uploadfile/2019-03-27/7fe8306013ab0f36b81d7271dc0c8478.jpg' />
A.<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/cfff1591b450909431e9f7ae7dc05f14.jpg' />
B.<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/c75e1c68e2e140258467ca466b7e474d.jpg' />
C.<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/c87a2c32bbbd462ee3f4b85cd7438fec.jpg' />
D.<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/659c7750c67c9d05542c4c9f4dd01ed9.jpg' />
-
Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables.Which two SQL statements produce the name, department name, and the city of all the employees who earn more then 10000？（）
<img src='https://img2.soutiyun.com/ask/uploadfile/2019-03-27/24b8b2127f6e518cc48ee2eb3e995f55.jpg' />
A．<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/9c9ecd3bc18e9e5ea56864899f6fd379.jpg' />
B．<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/ce2012a411732ad75a4283de3ff8e2ef.jpg' />
C．<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/07b2ad486cb8e688542e91608c75a123.jpg' />
D．<img src='https://img2.soutiyun.com/ask/uploadfile/2019-02-04/a73cb726d1955a9d3c0133877ce50fd8.jpg' />
-
Examine the description of the MARKS table:SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.Examine this SELECT statement based on the MARKS table:What is the result of the SELECT statement？（）
<img src='https://img2.soutiyun.com/ask/uploadfile/2019-03-27/1179420481e271350f195c64f9e527b9.jpg' />
<img src='https://img2.soutiyun.com/ask/uploadfile/2019-03-27/a88968eec9764c2a8638e3aee2b1e23d.jpg' />
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.
-
Examine the structure of the EMPLOYEES and DEPARTMENTS tables:EMPLOYEESColumn name Data type RemarksEMPLOYEE_ID NUMBER NOT NULL, Primary KeyEMP_NAME VARCHAR2 (30)JOB_ID VARCHAR2 (20)SALARY NUMBERMGR_ID NUMBER References EMPLOYEE_ID COLUMNDEPARTMENT ID NUMBER Foreign key to DEPARTMENT IDcolumn of the DEPARTMENTS tableDEPARTMENTSColumn name Data type RemarksDEPARTMENT_ID NUMBER NOT NULL, Primary KeyDEPARTMENT_NAME VARCHAR2(30)MGR_ID NUMBER References MGR_ID column of theEMPLOYEES tableEvaluate this SQL statement:SELECT employee_id, e.department_id, department_name,salaryFROM employees e, departments dWHERE 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);
-
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.
