1.
Choose the best answer.
Exanine the desciption of the EMPLOYEES table:
Nane        Null        Type
----------------------------------------------------------
EMP_ID        NOT NUL        NUMBER
EMP_NAME        VARCHAR2 (40)
DEPT_ID        NUMBER(2)
SALARY        NUMBER(8,2)
JOIN_DATE        DATE
Which query is valid?
A)SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
B)SELECT depe_id,join_date, SUM(salary) FROM employees GROUP BY dept_id:
C)SELECT dept_id,MAX (AVG (salary)) FROM employees GROUP BY dept_id;
D)SELECT dept_ id,AVG (MAX (salary)) FROM employees GROUP BY dapt_id;
Correct Answer:A

2.
choose three
Which three are true about the CREATE TABLE command?
A)It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
B)The owner of the table should have space quota available on the tablespace where the table is defined.
C)It implicitly executes a commit.
D)It implicitly rolls back any pending transactions.
E)A user must have the CREATE ANY TABLE privilege to create tables,
F)The owner of the table must have the UNLIMITED TABLESPACE system privilege
Correct Answer:ABC

3.
choose two
The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number. Which two queries execute successtully?
A)SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;
B)SELECT NVL2(cust_credit_limit * .15,'Not AvailabIe') FROM customers;
C)SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
D)SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;
E)SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;
Correct AnswerE
解析：NVL 函数的参数数据类型需一样，NVL2 函数的第二个参数与第三个参数数据类型需要一样。

4.
choose two
Exammine the desciption of the PRODUCT DETALS table:
NAME                NULL                TYPE
--------------------------------------------------
PRODUCT_ID        NOT NULL        NUMBER(2)
PRODUCT_NAME        NOT NULL        VARCHAR2(25)
PRODUCT_PRICE                        NUMBER(8,2)
EXPIRY_DATE                        DATE
Which two statements are true?
A)PRODUCT_ID can be assigned the PEIMARY KEY constraint.
B)EXPIRY_DATE cannot be used in arithmetic expressions.
C)EXPIRY_DATE contains the SYSDATE by defalt if no date is assigned to it
D)PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
E)PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
F)PRODUCT_NAME cannot contain dupicate values.
Correct Answer:AD
解析：D 选项当价格列没有值的时候，做算术表达式运算依然为没有值，但是不影响做算术表达式运算。

5.
choose the best answer
The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?
A)SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
B)SELECT REPLACE (TRIM(TRALING 'son' FROM cust_last_name), 'An','O') FROM customers;
C)SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
D)SELECT REPLACE (SUBSTR(cust_last_name,-3), 'An', 'O') FROM customers;
Correct Answer:A
解析：trim 只能处理过滤掉一个字符，不能过滤多个字符，所以 BC 可以排除。

6.
choose three
Which three statements are true about indexes and their administration in an Orade database?
A)An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
B)An index can be created as part of a CREATE TABLE statement.
C)A DROP INDEX statement always prevents updates to the table during the drop opeaton
D)A UNIQUE and non-unique index can be ceated on the same table colunn
E)A descending index is a type of funcion-based index
F)If a query filters on an indexed column then it will always be used during execution of the query
Correct Answer:BCE
解析：Unlike unusable indexes, an invisible index is maintained during DML statements.  

7.
Choose three
Which three are true about granting object privileges on tables, views, and sequences?
A)UPDATE can be granted only on tables and views.
B)DELETE can be granted on tables, views, and sequences.
C)REFERENCES can be granted only on tables and views.
D)INSERT can be granted on tables, vews, and sequences.
E)SELECT can be granted only on tables and views.
F)ALTER can be granted only on tables and sequences.
Correct Answer:ACF
解析：对 sequence 的操作只有 select 与 alter 权限

8.
Choose the best answer
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE You want to display the date of the first Monday after the completion of six months since hiring
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day ofthe week
Which query can be used?
A)SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
B)SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
C)SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
D)SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
Correct Answer:A

9.
Choose three
Which three statements are true about views in an Orade batabase?
A)A SELECT statement cannot contain a where clause when querying a view contaning a WHERE clause in its defining query
B)Rows inserted into a table using a view are retained in the table if the view is dopped
C)Views can join tables only if they belong to the same schema.
D)Views have no segment.
E)Views have no object number.
F)A view can be created that refers to a non-existent table in its defining query.
Correct Answer:BDF
解析：在没有基表的情况下也可以创建视图，通过 create force view 语法创建

10.
Choose two
Which two statements are true about Oracle synonyms?
A)A synonym can have a synonym.
B)All private synonym names must be unique in the database.
C)Any user can create a PUBLIC synonym.
D)A synonym can be created on an object in a package.
E)A synonym has an object number.
Correct Answer:AE

11.
Choose two.
Which two statements are true about a self join?
A)The join key column must have an index.
B)It can be a left outer join.
C)It must be a full outer join.
D)It can be an inner join.
E)It must be an equijoin.
Correct Answer:BD

12.
Choone two
Which three statements are true about dropping and unused columns in an Oracle database?
A)A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
B)A DROP COLUMN command can be rolled back
C)An UNUSED column's space is redaimed automatially when the block containing that column is next queried
D)An UNUSED column's space is redaimed automatially when the row containing that column is next queried.
E)Partition key columns cannot be dropped.
F)A column that is set to NNUSED still counts towards the limit of 1000 columns per table.
Correct Answer:AEF
解析：官档：SQL langauage reference

13.
Choose the best answer. Examine this query:
SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;
What is the result?
A) 16
B) 160
C) 150
D) 200
E) 100
Correct Answer

14.
Choose two.
Examine this SQL statement:
UPDATE orders o
SET customer_name =
(SELECT cust_last_name
FROM customers
WHERE customer_id=o.customer_id);
Which two are true?
A)The subquery is executed before the UPDATE statement is executed.
B)All existing rows in the ORDERS table are updated.
C)The subquery is executed for every updated row in the ORDERS table.
D)The UPDATE statement executes successfully even if the subquery selects multiple rows.
E)The subquery is not a correlated subquery.
Correct Answer:BC
解析：

15.
Choone three
Examine the description of the PRODUCTS table:
Name           Null?          Type
-------------------------------------------
PRODUCT_ID     NOT NULL       NUMBER(2)
PRODUCT_NAME                  VARCHAR2(10)
UNIT_PRICE                    NUMBER(3)
SURCHARGE                     VARCHAR2(2)
EXPIRY_DATE                   DATE
DELIVERY_DATE                      DATE
Which three queres use valid expressions?
A)SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
B)SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
C)SELECT ptoduct_id, (expiry_date - delivery_date) * 2 FROM products;
D)SPLECT product_id, expiry_date * 2 FROM products;
E)SELEGT product_id, unit_price, unit_price + surcharge FROM products;
F)SELECT product_id,unit_price || "Discount" , unit_price + surcharge - discount FROM products;
Correct Answer:BCE
解析：别名不能做运算，日期不能和数值做运算，可以日期和日期之间做运算，F  选项语法不对

16.
Choose two.
Examine this partial command:
CREATE TABLE cust (
cust_id               NUMBER(2),
credit_limit   NUMBER(10)
)
ORGANIZATION EXTERNAL
Which two clauses are required for this command to execute successfully?
A)the DEFAULT DIRECTORY clause
B)the REJECT LIMIT clause
C)the LOCATION clause
D)the ACCESS PARAMETERS clause
E)the access dirver TRPE clause
Correct Answer:AC
解析：

17.
choose two
Examine this business rule:
Each studert can work on mutiple projects and eath project can have multiple students
You must desion an Entity Relationsep (ER) model for optional data storage and allow generating reports in this format: STUDENT_ID        FIRST_NAME  LAST_NAME        PROJECT_ID        PROJECT_NAME        PROJECT_TASK
Which two statements are true?
A)An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID,which is the foreign key linked to the STUDENTS and PROJECTS entities.
B)The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
C)PROJECT_ID must be the primary key in the PROJECTS eitity and foreign key in the STUDENTS entity.
D)The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
E)STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
Correct Answer:AB

18.
Choose two
Which two statements are true about the WHERE and HAVING clouses in a SELECT statement?
A)The WHERE clause can be used to exclude rows after divding them into groups
B)WHERE and HAVING clauses can be used In the same statement only if applied to dfferent table columns.
C)The HAVING clause can be used with aggregating functions in subqueries.
D)Aggregating functions and columns used in HAVING clauses must be spectfied in thes SELECT list of a query.
E)The WHERE clause can be used to exclude rows before dviding them into goups.
Correct Answer:CE

19.
Choose two
The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOTCE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type conversions involving these columns in query expressions?
A)invoice_date > '01-02-2019' : uses implicit conversion
B)qty_sold =' 05549821 ' : requires explicit conversion
C)CONCAT(qty_sold,        invoice_date) : reuires explicit conversion
D)qty_sold BETWEEN '101' AND '110' : uses implicit convesion
E)invoice_date = '15-march-2019' : uses implicit conversion
Correct AnswerE
解析：A 选项不会执行成功，和时间格式不匹配
D 隐式转换，数值和字符的转换
Implicit conversion:隐式转换
Explicit conversion:显式转换
这道题考察的显式转换和隐式转换

20.
Choose two
The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2). Evaluate this SQL statement:
SELECT TO_CHAR(unit_price,'$9,999') FROM Product_intormation; Which two statements are true about the output?
A)A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
B)A row whose UNIT_PRICE column contains the value 1023.99 will be displayed a5 $1,023.
C)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
D)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
E)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####
Correct Answer:AE

21.
choose two
In the PROMOTIONS table, the PROMO_BEGTN_DATE columm is of datatype DATE and the default date format is DD-MON- RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a queny?
A)TO_NUMBER(PROMO_ BEGIN_DATE) - 5 will return number
B)TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
C)PROMO_BEGIN_DATE - SYSDATE will return a number.
D)PROMO_BEGIN_DATE - 5 will return a date.
E)PROMO_BEGIN_DATE - SYSDATE will return an error.
Correct Answer:CD
解析：考察转换函数，数值和字符，字符和日期可以互相转换，数值和日期不能转换，所以直接排除 A 选项不对，日期 类型和数值不能做*运算

22.
Choose two
Which two statements are true about transactions in the Oracle Database serve?
A)An uncommitted transaction commits automatically if the user exits SQL*Plus
B)Data Manipulation Language (DML) statements always start a new transaction.
C)A user can always see uncomitted updates made by the same user in a dfferert session.
D)A Data Detiniton Language (DDL) statement does a commit automatially only for the data dictionary updates caused by the DDL
E)A session can always see uncomnitted updetes made by itselt.
F)If a session has an uncometted transaction,then a DDL statement issue a COMMIT before starting a new transaction.
Correct Answer:AE

23.
Choose three
Examine this description of the PRODUCTS table:
Name           NULL?        TYPE
------------------------------------------------------------------------
PROD_ID           NOT NULL        VARCHAR2(6)
QUANTITY                NUMBER(8,2)
PRICE                        NUMBER(10.2)
EXPIRY_DATE                DATE
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?
A)ALTER TAELE products DROP COLUMN expiry_date;
B)CREATE INDEX price_idx on products (price);
C)ALTER TABLE products SET UNUSED(expiry_date);
D)TRUNCATE TABLE products;
E)ALTER TABLE products DROP UNUSED COLUMNS
F)DROP TABLE products
Correct Answer:BEF
解析：Set unused 操作是不允许的，可以在表可读可写状态时，置为不可用，然后在只读状态下再去删除

24.
Choose three.
Which three statements are true about GLOBAL TEMPORARY TABLES?
A)A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.
B)A GLOBAL TEMPORARY TABLE can have multiple indexes
C)A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
D)Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
E)A GLOBAL TEMPORARY TABLE can have only one index.
F)A tigger can be created on a GLOBAL TEMPORARY TABLE
Correct Answer:BCF
解析：D 会产生 redo，可以通过查看统计信息，打开 set autotrace on,然后对临时表执行 dml 操作，会有 redo size

25.
Choose three.
Which three statements are true about defining relations between tables in a relatonal database?
A)Foreign key columns allow null values.
B)Unique key columns allow null values
C)Primary key columns allow null values.
D)Every primary or unique key value must refer to a matching foreign key value.
E)Every foreign key value must refer to a matching primary or unique key value.
Correct Answer:ABE

26.
Choose three
Which three actions can you perfom only with system privileges?
A)Truncate a table in another schema.
B)Access flat files via a database, which are stored in an operating system directory.
C)Log in to a database.
D)Query any table in a database.
E)Use the WITH GRANT OPTION clause.
F)Execute a procedure in another schema.
Correct Answer:CDF
解析：考察对象权限与系统权限
访问平台操作系统文件，可以理解为 directory 的可读可写权限，不属于系统权限。

27.
Choose two.
Which two statements are true about the DUAL table?
A)It can display multiple rows and columns.
B)It can be accessed only by the SYS user.
C)It can be accessed by any user who has the SELECT privilege in any schema
D)It can display multiple rows but only a single column.
E)It consists of a single row and single column of VARCHAR2 data type.
F)It can be used to display only constants or pseudo columns.
Correct Answer:AC

28.
choose two
The ORDERS table has a column ORDER_DATE of date type DATE
The default display format for a date is DD-MON-RR
Which two WHERE condittions demonstrate the correct usage of conversion functions?
A)WHERE order_date>TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
B)WHERE TO_CHAR(order_date,'MON DD YYYY')='JAN 20 2019'
C)WHERE order_date>TO_DATE('JUL 10 2018','MON DD YYYY')
D)WHERE order_date IN(TO_DATE('Oct 21 2018','MON DD YYYY'),TO_CHAR('Now 21 2018','MON DD YYYY'))
E)WHERE order_date>TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
Correct Answer:BC

29.
choose two.
·MANAGER is an existing role with no privileges or roles.
·EMP is an existing role containing the CREATE TABLE privilege.
·EMPLOYEES is an existing table in the HR schema.
Which two commands execute successfully?
A)GRANT CREATE SEQUENCE TO manager, emp;
B)GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION:
C)GRANT CREATE TABLE, emp TO manager;
D)GRANT CREATE TABLE, SELECT ON hr. employees TO manager ;
E)GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;
Correct Answer:AC

30.
choose two
Evalute these conmands which execate sucestully
CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
A)Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
B)Column ORD_NO gets the next number from squence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explict value is given for ORD_NO.
C)Sepuence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
D)IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
E)Sequence ORD_SEQ is guaranteed not to genenate duplicate numbers.
Correct Answer:AB
解析：如果序列被删除，在插入数据时会报序列不存在

1.
Choose the best answer.
Exanine the desciption of the EMPLOYEES table:
Nane        Null        Type
----------------------------------------------------------
EMP_ID        NOT NUL        NUMBER
EMP_NAME        VARCHAR2 (40)
DEPT_ID        NUMBER(2)
SALARY        NUMBER(8,2)
JOIN_DATE        DATE
Which query is valid?
A)SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
B)SELECT depe_id,join_date, SUM(salary) FROM employees GROUP BY dept_id:
C)SELECT dept_id,MAX (AVG (salary)) FROM employees GROUP BY dept_id;
D)SELECT dept_ id,AVG (MAX (salary)) FROM employees GROUP BY dapt_id;
Correct Answer:A

2.
choose three
Which three are true about the CREATE TABLE command?
A)It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint.
B)The owner of the table should have space quota available on the tablespace where the table is defined.
C)It implicitly executes a commit.
D)It implicitly rolls back any pending transactions.
E)A user must have the CREATE ANY TABLE privilege to create tables,
F)The owner of the table must have the UNLIMITED TABLESPACE system privilege
Correct Answer:ABC

3.
choose two
The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number. Which two queries execute successtully?
A)SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers;
B)SELECT NVL2(cust_credit_limit * .15,'Not AvailabIe') FROM customers;
C)SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
D)SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers;
E)SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers;
Correct AnswerE
解析：NVL 函数的参数数据类型需一样，NVL2 函数的第二个参数与第三个参数数据类型需要一样。

4.
choose two
Exammine the desciption of the PRODUCT DETALS table:
NAME                NULL                TYPE
--------------------------------------------------
PRODUCT_ID        NOT NULL        NUMBER(2)
PRODUCT_NAME        NOT NULL        VARCHAR2(25)
PRODUCT_PRICE                        NUMBER(8,2)
EXPIRY_DATE                        DATE
Which two statements are true?
A)PRODUCT_ID can be assigned the PEIMARY KEY constraint.
B)EXPIRY_DATE cannot be used in arithmetic expressions.
C)EXPIRY_DATE contains the SYSDATE by defalt if no date is assigned to it
D)PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it
E)PRODUCT_PRICE contains the value zero by default if no value is assigned to it.
F)PRODUCT_NAME cannot contain dupicate values.
Correct Answer:AD
解析：D 选项当价格列没有值的时候，做算术表达式运算依然为没有值，但是不影响做算术表达式运算。

5.
choose the best answer
The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose COST_LAST_MANE values are Anderson and Ausson.
Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?
A)SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers;
B)SELECT REPLACE (TRIM(TRALING 'son' FROM cust_last_name), 'An','O') FROM customers;
C)SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM customers;
D)SELECT REPLACE (SUBSTR(cust_last_name,-3), 'An', 'O') FROM customers;
Correct Answer:A
解析：trim 只能处理过滤掉一个字符，不能过滤多个字符，所以 BC 可以排除。

6.
choose three
Which three statements are true about indexes and their administration in an Orade database?
A)An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
B)An index can be created as part of a CREATE TABLE statement.
C)A DROP INDEX statement always prevents updates to the table during the drop opeaton
D)A UNIQUE and non-unique index can be ceated on the same table colunn
E)A descending index is a type of funcion-based index
F)If a query filters on an indexed column then it will always be used during execution of the query
Correct Answer:BCE
解析：Unlike unusable indexes, an invisible index is maintained during DML statements.  

7.
Choose three
Which three are true about granting object privileges on tables, views, and sequences?
A)UPDATE can be granted only on tables and views.
B)DELETE can be granted on tables, views, and sequences.
C)REFERENCES can be granted only on tables and views.
D)INSERT can be granted on tables, vews, and sequences.
E)SELECT can be granted only on tables and views.
F)ALTER can be granted only on tables and sequences.
Correct Answer:ACF
解析：对 sequence 的操作只有 select 与 alter 权限

8.
Choose the best answer
The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE You want to display the date of the first Monday after the completion of six months since hiring
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day ofthe week
Which query can be used?
A)SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6),'MONDAY') FROM employees;
B)SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees;
C)SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees;
D)SELECT emp_id,NEXT_DAY(ADD_MONTHS(hire_date,6),1) FROM employees;
Correct Answer:A

9.
Choose three
Which three statements are true about views in an Orade batabase?
A)A SELECT statement cannot contain a where clause when querying a view contaning a WHERE clause in its defining query
B)Rows inserted into a table using a view are retained in the table if the view is dopped
C)Views can join tables only if they belong to the same schema.
D)Views have no segment.
E)Views have no object number.
F)A view can be created that refers to a non-existent table in its defining query.
Correct Answer:BDF
解析：在没有基表的情况下也可以创建视图，通过 create force view 语法创建

10.
Choose two
Which two statements are true about Oracle synonyms?
A)A synonym can have a synonym.
B)All private synonym names must be unique in the database.
C)Any user can create a PUBLIC synonym.
D)A synonym can be created on an object in a package.
E)A synonym has an object number.
Correct Answer:AE

11.
Choose two.
Which two statements are true about a self join?
A)The join key column must have an index.
B)It can be a left outer join.
C)It must be a full outer join.
D)It can be an inner join.
E)It must be an equijoin.
Correct Answer:BD

12.
Choone two
Which three statements are true about dropping and unused columns in an Oracle database?
A)A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
B)A DROP COLUMN command can be rolled back
C)An UNUSED column's space is redaimed automatially when the block containing that column is next queried
D)An UNUSED column's space is redaimed automatially when the row containing that column is next queried.
E)Partition key columns cannot be dropped.
F)A column that is set to NNUSED still counts towards the limit of 1000 columns per table.
Correct Answer:AEF
解析：官档：SQL langauage reference

13.
Choose the best answer. Examine this query:
SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL;
What is the result?
A) 16
B) 160
C) 150
D) 200
E) 100
Correct Answer

14.
Choose two.
Examine this SQL statement:
UPDATE orders o
SET customer_name =
(SELECT cust_last_name
FROM customers
WHERE customer_id=o.customer_id);
Which two are true?
A)The subquery is executed before the UPDATE statement is executed.
B)All existing rows in the ORDERS table are updated.
C)The subquery is executed for every updated row in the ORDERS table.
D)The UPDATE statement executes successfully even if the subquery selects multiple rows.
E)The subquery is not a correlated subquery.
Correct Answer:BC
解析：

15.
Choone three
Examine the description of the PRODUCTS table:
Name           Null?          Type
-------------------------------------------
PRODUCT_ID     NOT NULL       NUMBER(2)
PRODUCT_NAME                  VARCHAR2(10)
UNIT_PRICE                    NUMBER(3)
SURCHARGE                     VARCHAR2(2)
EXPIRY_DATE                   DATE
DELIVERY_DATE                      DATE
Which three queres use valid expressions?
A)SELECT produet_id, unit_pricer, 5 "Discount",unit_price+surcharge-discount FROM products;
B)SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
C)SELECT ptoduct_id, (expiry_date - delivery_date) * 2 FROM products;
D)SPLECT product_id, expiry_date * 2 FROM products;
E)SELEGT product_id, unit_price, unit_price + surcharge FROM products;
F)SELECT product_id,unit_price || "Discount" , unit_price + surcharge - discount FROM products;
Correct Answer:BCE
解析：别名不能做运算，日期不能和数值做运算，可以日期和日期之间做运算，F  选项语法不对

16.
Choose two.
Examine this partial command:
CREATE TABLE cust (
cust_id               NUMBER(2),
credit_limit   NUMBER(10)
)
ORGANIZATION EXTERNAL
Which two clauses are required for this command to execute successfully?
A)the DEFAULT DIRECTORY clause
B)the REJECT LIMIT clause
C)the LOCATION clause
D)the ACCESS PARAMETERS clause
E)the access dirver TRPE clause
Correct Answer:AC
解析：

17.
choose two
Examine this business rule:
Each studert can work on mutiple projects and eath project can have multiple students
You must desion an Entity Relationsep (ER) model for optional data storage and allow generating reports in this format: STUDENT_ID        FIRST_NAME  LAST_NAME        PROJECT_ID        PROJECT_NAME        PROJECT_TASK
Which two statements are true?
A)An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID,which is the foreign key linked to the STUDENTS and PROJECTS entities.
B)The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
C)PROJECT_ID must be the primary key in the PROJECTS eitity and foreign key in the STUDENTS entity.
D)The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
E)STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
Correct Answer:AB

18.
Choose two
Which two statements are true about the WHERE and HAVING clouses in a SELECT statement?
A)The WHERE clause can be used to exclude rows after divding them into groups
B)WHERE and HAVING clauses can be used In the same statement only if applied to dfferent table columns.
C)The HAVING clause can be used with aggregating functions in subqueries.
D)Aggregating functions and columns used in HAVING clauses must be spectfied in thes SELECT list of a query.
E)The WHERE clause can be used to exclude rows before dviding them into goups.
Correct Answer:CE

19.
Choose two
The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOTCE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR.
Which two are true about data type conversions involving these columns in query expressions?
A)invoice_date > '01-02-2019' : uses implicit conversion
B)qty_sold =' 05549821 ' : requires explicit conversion
C)CONCAT(qty_sold,        invoice_date) : reuires explicit conversion
D)qty_sold BETWEEN '101' AND '110' : uses implicit convesion
E)invoice_date = '15-march-2019' : uses implicit conversion
Correct AnswerE
解析：A 选项不会执行成功，和时间格式不匹配
D 隐式转换，数值和字符的转换
Implicit conversion:隐式转换
Explicit conversion:显式转换
这道题考察的显式转换和隐式转换

20.
Choose two
The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2). Evaluate this SQL statement:
SELECT TO_CHAR(unit_price,'$9,999') FROM Product_intormation; Which two statements are true about the output?
A)A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024.
B)A row whose UNIT_PRICE column contains the value 1023.99 will be displayed a5 $1,023.
C)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236.
D)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023.
E)A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as #####
Correct Answer:AE

21.
choose two
In the PROMOTIONS table, the PROMO_BEGTN_DATE columm is of datatype DATE and the default date format is DD-MON- RR.
Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a queny?
A)TO_NUMBER(PROMO_ BEGIN_DATE) - 5 will return number
B)TO_DATE(PROMO_BEGIN_DATE * 5) will return a date
C)PROMO_BEGIN_DATE - SYSDATE will return a number.
D)PROMO_BEGIN_DATE - 5 will return a date.
E)PROMO_BEGIN_DATE - SYSDATE will return an error.
Correct Answer:CD
解析：考察转换函数，数值和字符，字符和日期可以互相转换，数值和日期不能转换，所以直接排除 A 选项不对，日期 类型和数值不能做*运算

22.
Choose two
Which two statements are true about transactions in the Oracle Database serve?
A)An uncommitted transaction commits automatically if the user exits SQL*Plus
B)Data Manipulation Language (DML) statements always start a new transaction.
C)A user can always see uncomitted updates made by the same user in a dfferert session.
D)A Data Detiniton Language (DDL) statement does a commit automatially only for the data dictionary updates caused by the DDL
E)A session can always see uncomnitted updetes made by itselt.
F)If a session has an uncometted transaction,then a DDL statement issue a COMMIT before starting a new transaction.
Correct Answer:AE

23.
Choose three
Examine this description of the PRODUCTS table:
Name           NULL?        TYPE
------------------------------------------------------------------------
PROD_ID           NOT NULL        VARCHAR2(6)
QUANTITY                NUMBER(8,2)
PRICE                        NUMBER(10.2)
EXPIRY_DATE                DATE
Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode.
Which three commands execute successfully on PRODUCTS?
A)ALTER TAELE products DROP COLUMN expiry_date;
B)CREATE INDEX price_idx on products (price);
C)ALTER TABLE products SET UNUSED(expiry_date);
D)TRUNCATE TABLE products;
E)ALTER TABLE products DROP UNUSED COLUMNS
F)DROP TABLE products
Correct Answer:BEF
解析：Set unused 操作是不允许的，可以在表可读可写状态时，置为不可用，然后在只读状态下再去删除

24.
Choose three.
Which three statements are true about GLOBAL TEMPORARY TABLES?
A)A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM.
B)A GLOBAL TEMPORARY TABLE can have multiple indexes
C)A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
D)Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
E)A GLOBAL TEMPORARY TABLE can have only one index.
F)A tigger can be created on a GLOBAL TEMPORARY TABLE
Correct Answer:BCF
解析：D 会产生 redo，可以通过查看统计信息，打开 set autotrace on,然后对临时表执行 dml 操作，会有 redo size

25.
Choose three.
Which three statements are true about defining relations between tables in a relatonal database?
A)Foreign key columns allow null values.
B)Unique key columns allow null values
C)Primary key columns allow null values.
D)Every primary or unique key value must refer to a matching foreign key value.
E)Every foreign key value must refer to a matching primary or unique key value.
Correct Answer:ABE

26.
Choose three
Which three actions can you perfom only with system privileges?
A)Truncate a table in another schema.
B)Access flat files via a database, which are stored in an operating system directory.
C)Log in to a database.
D)Query any table in a database.
E)Use the WITH GRANT OPTION clause.
F)Execute a procedure in another schema.
Correct Answer:CDF
解析：考察对象权限与系统权限
访问平台操作系统文件，可以理解为 directory 的可读可写权限，不属于系统权限。

27.
Choose two.
Which two statements are true about the DUAL table?
A)It can display multiple rows and columns.
B)It can be accessed only by the SYS user.
C)It can be accessed by any user who has the SELECT privilege in any schema
D)It can display multiple rows but only a single column.
E)It consists of a single row and single column of VARCHAR2 data type.
F)It can be used to display only constants or pseudo columns.
Correct Answer:AC

28.
choose two
The ORDERS table has a column ORDER_DATE of date type DATE
The default display format for a date is DD-MON-RR
Which two WHERE condittions demonstrate the correct usage of conversion functions?
A)WHERE order_date>TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
B)WHERE TO_CHAR(order_date,'MON DD YYYY')='JAN 20 2019'
C)WHERE order_date>TO_DATE('JUL 10 2018','MON DD YYYY')
D)WHERE order_date IN(TO_DATE('Oct 21 2018','MON DD YYYY'),TO_CHAR('Now 21 2018','MON DD YYYY'))
E)WHERE order_date>TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
Correct Answer:BC

29.
choose two.
·MANAGER is an existing role with no privileges or roles.
·EMP is an existing role containing the CREATE TABLE privilege.
·EMPLOYEES is an existing table in the HR schema.
Which two commands execute successfully?
A)GRANT CREATE SEQUENCE TO manager, emp;
B)GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION:
C)GRANT CREATE TABLE, emp TO manager;
D)GRANT CREATE TABLE, SELECT ON hr. employees TO manager ;
E)GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;
Correct Answer:AC

30.
choose two
Evalute these conmands which execate sucestully
CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
A)Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
B)Column ORD_NO gets the next number from squence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explict value is given for ORD_NO.
C)Sepuence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
D)IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
E)Sequence ORD_SEQ is guaranteed not to genenate duplicate numbers.
Correct Answer:AB
解析：如果序列被删除，在插入数据时会报序列不存在