【Oracle学习】 OCP-071 练习题
Q1.
Choose the best answer. Examine the description of the EMPLOYEES table: Name Null Type ---------------------------------------------------------- EMP_ID EMP_NAME DEPT_ID SALARY JOIN_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;
Answer: A
Q2.
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 Answer: ABC
Q3.
Choose two The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number. Which two queries execute successfully? A) SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers; B) SELECT NVL2(cust_credit_limit * .15,'Not Available') 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; Answer: DE 解析:NVL 函数的参数数据类型需一样，NVL2 函数的第二个参数与第三个参数数据类型需 要一样。
Q4.
Choose two Examine the description of the PRODUCT DETALS table: NOT NUL NUMBER VARCHAR2 (40) NUMBER(2) NUMBER(8,2) DATE
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 default 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 duplicate values. Answer: AD 解析:D 选项当价格列没有值的时候，做算术表达式运算依然为没有值，但是不影响做算术 表达式运算。
Q5.
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; Answer: A 解析:trim 只能处理过滤掉一个字符，不能过滤多个字符，所以 BC 可以排除。
Q6. (XX)
Choose three Which three statements are true about indexes and their administration in an Oracle 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 operation D) A UNIQUE and non-unique index can be created on the same table column E) A descending index is a type of function-based index F) If a query filters on an indexed column then it will always be used during execution of the query Answer: BCE 解析:Unlike unusable indexes, an invisible index is maintained during DML statements.
Q7. (XX)
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, views, and sequences. E) SELECT can be granted only on tables and views. F) ALTER can be granted only on tables and sequences. Answer: ACF
解析:对 sequence 的操作只有 select 与 alter 权限
Q8.
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 of the 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; Answer: A
Q9.
Choose three Which three statements are true about views in an Oracle database? A) A SELECT statement cannot contain a where clause when querying a view containing 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 dropped 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. Answer: BDF F:在没有基表的情况下也可以创建视图，通过 create force view 语法创建
Q10.
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. Answer: AE
Q11.
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. Answer: BD
Q12.
Choose 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 remained automatically when the block containing that column is next queried D) An UNUSED column's space is remained automatically 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. Answer: AEF 官档:SQL language reference
Q13.
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 Answer: D
SQL> select trunc(156.12 , -1 ) from dual;
TRUNC(156.12,-1)
----------------
150
SQL> select trunc(ROUND(156.00,-2) , -1 ) from dual;
TRUNC(ROUND(156.00,-2),-1)
--------------------------
200
Q14.
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. Answer: BC 解析:
Q15.
Choose three Examine the description of the PRODUCTS table: Name Null? Type
PRODUCT_ID PRODUCT_NANE UNIT_PRICE SURCHARGE EXPIRY_DATE DELIVERY_DATE Which three queries 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; Answer: BCE 解析:别名不能做运算，日期不能和数值做运算，可以日期和日期之间做运算，F 选项语法 不对
SQL> select sysdate - TO_DATE('1-12-2019', 'DD-MM-YYYY') from dual;
SYSDATE-TO_DATE('1-12-2019','DD-MM-YYYY')
-----------------------------------------
55.7914468
#||后要接单引号
SQL> select to_char(0001) || " is a special number" from dual; --error
ORA-00904: " is a special number": invalid identifier
SQL> select to_char(0001) || ' is a special number' from dual; --ok
TO_CHAR(0001)||'ISASPECIALNUMBER'
------------------------------------------
1 is a special number
#
SQL> select 1 || 'is a special number' from dual; --ok
1||'ISASPECIALNUMBER'
----------------------------------------
1is a special number
Q16. (外部表)
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 NOT NULL NUMBER(2) VARCHAR2(10) NUMBER(3) VARCHAR2(2) DATE DATE
C) the LOCATION clause D) the ACCESS PARAMETERS clause E) the access driver TYPE
clause Answer: AC 解析:
Q17.
Choose two Examine this business rule: Each student can work on multiple projects and earth project can have multiple students You must decision an Entity Relationship (ER) model for optional data storage and allow generating reports in this format: STUDENT_ID FIRST_NAMELAST_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 entity 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. Answer: AB
Q18.
Choose two Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? A) The WHERE clause can be used to exclude rows after dividing them into groups B) WHERE and HAVING clauses can be used in the same statement only if applied to different 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 specified in these SELECT list of a query. E) The WHERE clause can be used to exclude rows before dividing them into groups. Answer: CE
Q19.
Choose two The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_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) : requires explicit conversion
D) qty_sold BETWEEN '101' AND '110' : uses implicit conversion E) invoice_date = '15-march-2019' : uses implicit conversion Answer: DE 解析:A 选项不会执行成功，和时间格式不匹配 D 隐式转换，数值和字符的转换 Implicit conversion:隐式转换 Explicit conversion:显式转换 这道题考察的显式转换和隐式转换
select employee_id,HIRE_DATE, salary from employees where salary between '1000' and '20000' and HIRE_DATE>'21-6月-99';
Q20.
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_INFORMATION; 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 as $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 ##### Answer: AE
number(p,s),s大于0，表示有效位最大为p，小数位最多为s，小数点右边s位置开始四舍五入，若s>p，小数点右侧至少有s-p个0填充（必须从小数点处开始并连续）。
举例：
number(2,1) 有效位最大为2，小数点后最多保留1位：
存1.115 得1.2
存1.11 的1.1
存1 得1
存0.01 得0
存11.1 得出错 有效位为3，大于2
存11 得出错 因为11等于11.0 有效位为3，大于2
number(2,4) 有效位最大为2，小数点后最多保留4位：
最大存值：0.0099，至少从小数点处开始并连续填充4-2=2个0，
SQL> select TO_CHAR(1023.99,'$9,999') from dual;
TO_CHAR(1023.9
--------------
$1,024
SQL> select TO_CHAR(10235.99 ,'$9,999') from dual;
TO_CHAR(10235.
--------------
#######
Q21.
Choose two In the PROMOTIONS table, the PROMO_BEGTN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? 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. Answer: CD 考察转换函数，数值和字符，字符和日期可以互相转换，数值和日期不能转换，所以直接 排除 A 选项不对，日期类型和数值不能做*运算
select to_number(sysdate) from dual; --ORA-01722: 无效数字
SQL> select sysdate -5 from dual;
SYSDATE-5
-------------------
2020-01-20 23:01:03
Q22.
Choose two Which two statements are true about transactions in the Oracle Database server? 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 uncommitted updates made by the same user in a different session. D) A Data Definition Language (DDL) statement does a commit automatically only for the data dictionary updates caused by the DDL E) A session can always see uncommitted updates made by itself.
F) If a session has an uncommitted transaction, then a before starting a new transaction. Answer: AE
Q23.(只读表)
DDL statement issue a COMMIT Choose three Examine this description of the PRODUCTS table: Name NULL? TYPE
PROD_ID QUANTITY PRICE EXPIRY_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 Answer: BEF 解析:Set unused 操作是不允许的，可以在表可读可写状态时，置为不可用，然后在只读状 态下再去删除
Q24.(临时表)
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 trigger can be created on a GLOBAL TEMPORARY TABLE Answer: BCF 解析:D 会产生 redo，可以通过查看统计信息，打开 set autotrace on,然后对临时表执行 dml 操作，会有 redo size
Q25.(外键)
Choose three. Which three statements are true about defining relations between tables in a relational 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. NOT NULL VARCHAR2(6) NUMBER(8,2) NUMBER(10.2) DATE
E) Every foreign key value must refer to a matching primary or unique key value. Answer: ABE
Q26.(系统权限)
Choose three Which three actions can you perform 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. Answer: CDF 考察对象权限与系统权限 解析: 访问平台操作系统文件，可以理解为 directory 的可读可写权限，不属于系统权限。
Q27.
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. Answer: AC
Q28. to_date
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 conditions demonstrate the correct usage of conversion functions? A) WHERE ordet_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('Nov 21 2018','MON DD YYYY')) E) WHERE order_date > TO_DATE(ADD_MONTHS(SYSDATE,6),'MON DD YYYY') Answer: BC 解析:to_date 语法:
SQL> select to_number(sysdate) from dual;
ORA-01722: invalid number
select * from nls_session_parameters where PARAMETER='NLS_DATE_FORMAT';
SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';
SQL> select add_months(SYSDATE,6) from dual;
ADD_MONTHS(S
------------
25-JUL-20
select TO_DATE(ADD_MONTHS(SYSDATE,6),'MON-DD-YYYY') from dual; --ORA-01843: 无效的月份
SQL> select TO_DATE(ADD_MONTHS(SYSDATE,6),'DD-MON-YYYY') from dual; --ok
TO_DATE(ADD_
------------
25-JUL-20
Q29. （Grant)
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; Answer: AC BAnswer：报错：ORA-01926: cannot GRANT to a role WITH GRANT OPTION DAnswer：：不能同时授权系统权限和对象权限给角色 EAnswer：：没有 create any session 的权限
Q30. (SEQUENCE)
Choose two Evaluate these commands which execute successfully 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 sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO. C) Sequence 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 generate duplicate numbers. Answer: AB 解析:如果序列被删除，在插入数据时会报序列不存在
Q31.
Choose two Examine the data in the CUST NAME column of the CUSTOMERS table: CUST_NAME
Renske Ladwig Jason Mallin
Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? A) WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,' ') +1 ) ) IN ('MC%', 'Mc%) B) WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, ' ') +1 ) ) LIKE UPPER('MC%') C) WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') +1 ) ) LIKE 'Mc%' D) WHERE SUBSTR(cust_name,INSTR(cust_name,' ') +1 ) LIKE 'Mc%' OR 'MC%' E) WHERE SUBSTR(cust_name, INSTR(cust_name,' ') +1 ) LIKE 'Mc%' Answer: BC 解析:D 选项语法问题，... like ... or ... like ....
Q32.
Choose three Examine this SQL statement: SELECT cust_id, cus_last_name "Last Name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30 Identify three ORDER BY clauses, any one of which can complete the query successfully. A) ORDERBY 2, 1 B) ORDER BY "CUST_NO" C) ORDER BY 2,cust_id D) ORDER BY CUST_NO E) ORDER BY "Last Name" Answer: ACE
Q33. 自联接
Choose three. Which three statements are true about a self join? A) It must be an inner join. B) It can be an outer join. C) The ON clause must be used. D) It must be an equijoin. E) The query must use two different aliases for the table. F) The ON clause can be used. Answer: BEF 考察自连接，两个表必须有别名
Q34. (INTERSECT)
Choose two. Which two statements are true about the results of using the INTERSECT operator in
compound queries? A) Reversing the order of the intersected tables can sometimes affect the output. B) Column names in each SELECT in the compound query can be different. C) INTERSECT returns rows common to both sides of the compound query. D) The number of columns in each SELECT in the compound query can be different. E) INTERSECT ignores NULLs Answer: BC 考察交集
SQL> select region_name from regions INTERSECT (select region_name as name from regions where region_name like 'A%' ) ;
REGION_NAME
--------------------------------------------------
Americas
Asia
Q35. （COUNT）
Choose two Which two statements are true about the COUNT function? A) It can only be used for NUMBER data types. B) COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column C) COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column. D) A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. E) COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. Answer: BC 解析:count 统计非空值，加上 distinct 列名之后统计非空的惟一值。
SQL> select count(department_id),count(*) from employees;
COUNT(DEPARTMENT_ID) COUNT(*)
-------------------- ----------
106 107
#count
SQL> select count(distinct department_id),count(distinct nvl(department_id,0)) from employees;
COUNT(DISTINCTDEPARTMENT_ID) COUNT(DISTINCTNVL(DEPARTMENT_ID,0))
---------------------------- -----------------------------------
11 12
Q36. （sequences）
Choose three Which three statements are true about sequences in a single instance Oracle database? A) A sequence's unallocated cached values are lost if the instance shuts down. B) Two or more tables cannot have keys generated from the same sequence. C) A sequence number that was allocated can be rolled back if a transaction fails. D) A sequence can issue duplicate values. E) Sequences can always have gaps. F) A sequence can only be dropped by a DBA. Answer: ADE
Q37.
Choose the best answer Examine the description of the PRODUCT_INFORMATION table: Name NULL? Type ------------------------------------------------------------------------ PROD_ID PROD_NANE LIST_PRICE Which query retrieves the number of products with a null list price? A) SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL; B) SELECT count(nvl( list_price,0) ) FROM product_information WHERE list_price is null; C) SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null. 结果为 0,DISTINCT统计非空唯一值 NOT NULL NUMBER(2) VARCRAR2 (10) NUMBER(6,2)
D) BELECT COUNT(list_price) FROM product_information where list_price is NULL; Answer: B 解析:直接用 count 会排除空值，所以不对，必须要先对空值进行非空处理，再统计
Q38. (TO_DATE)
Choose two. You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? A) SELECT SYSDATE - TO_DATE ('01-JANUARY-2019') FROM DUAL; B) SELECT TO_DATE (SYSDATE, ' DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL; C) SELECT ROUND (SYSDATE - TO_DATE ('01/JANUARY/2019' ) ) FROM DUAL; D) SELECT TO_CHAR (SYSDATE, ' DD-MON-YYYY') - '01-JAN-2019' FROM DUAL; E) SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL; Answer: AC 解析:考察 to_date 用法及数据类型的转换，运算
SQL> SELECT SYSDATE - TO_DATE ('01-JANUARY-2019') FROM DUAL;
SYSDATE-TO_DATE('01-JANUARY-2019')
----------------------------------
737440.008
SQL> SELECT ROUND (SYSDATE - TO_DATE ('01/JANUARY/2019' ) ) FROM DUAL;
ROUND(SYSDATE-TO_DATE('01/JANUARY/2019'))
-----------------------------------------
737440
#SQL> SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL;
SELECT ROUND (SYSDATE- '01-JAN-2019') FROM DUAL
*
ERROR at line 1:
ORA-01722: invalid number
Q39.
Choose two Examine the description of the BOOKS_TRANSACTIONS table Name Null? Type ------------------------------------------------------------------------------- --------------- THANSACTION_ID TRANSACTION_TYPE BORROWED_DATE BOOK_ID MEMBER_ID Examine this partial SQL statement: SELECT * FROM books_transctions; Which two WHERE conditions give the same result? A) WHERE borrowed_date = SYSDATE AND (transaction_type= 'RM' OR member_id IN ('A101','A102')); B) WHERE borrowed_date = SYSDATE AND transaction_type= 'RM' OR member_id IN ('A101','A102'); C) WHERE borrowed_date = SYSDATE AND (transaction_type= 'RM' AND member_id='A101' OR member_id = 'A102')); D) WHERE (borrowed_date = SYSDATE AND transaction_type= 'RM' ) OR member_id IN ('A101','A102'); E) WHERE borrowed_date = SYSDATE AND (transaction_type= 'RM' AND ( member_id= 'A101' OR member_id = 'A102' ) ); Answer: BD
Q40.
Choose two. Which two statements are true about single row functions? A) CONCAT: can be used to combine any number of values NOT NULL VARCHAR2(6) VARCHAR2(3) DATE VARCHAR2(6) VARCHAR2(6) B) MOD: returns the quotient of a division operation C) CEIL: can be used for positive and negative numbers D) FLOOR: returns the smallest integer greater than or equal to a specified number E) TRUNC: can be used with NUMBER and DATE values Answer: CE 解析: Positiv:正数 negative:负数
Q41.
Choose three The ORDERS table has a primary key constraint on the ORDER_ID column. The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table. The constraint is defined with on DELETE CASCADE. There are rows in the ORDERS table with an ORDER_TOTAL less than 1000. Which three DELETE statements execute successfully? A) DELETE FROM orders WHERE order_total<1000; B) DELETE * FROM orders WHERE order_total<1000; C) DELETE orders WHERE order_total<1000; D) DELETE FROM orders; E) DELETE order_id FROM orders WHERE order_total<1000; Answer: ACD 考察 delete 语法
Q42. (EXIST)
Choose two Examine this SQL statement: DELETE FROM employees e WHERE EXISTS (SELECT 'dummy' FROM emp_history WHERE employee_id = e.employee_id) Which two are true? A) The subquery is executed for every row in the EMPLOYEES table. B) The subquery is not a correlated subquery. C) The subquery is executed before the DELETE statement is executed. D) All existing rows in the EMPLOYEE table are deleted. E) The DELETE statement executes successfully even if the subquery selects multiple rows. Answer: AE 解析:相关子查询，只会将父表中匹配条件的行记录删除，保留其它行。 用 exist，子表中反馈多行，也可以操作成功，只要反馈行，说明子查询结果为真，与反馈几 行没有关系。
Q43.
Choose two. You execute this command:
TRUNCATE TABLE dept; Which two are true? A) It drops any triggers defined on the table. B) It retains the indexes defined on the table. C) It retains the integrity constraints defined on the table. D) A ROLLBACK statement can be used to retrieve the deleted data. E) It always retains the space used by the removed rows. F) A FLASHBACK TABLE statement can be used to retrieve the deleted data. Answer: BC 解析:truncate 是 DDL 语句，不能用闪回技术查看，会报错表结构已改变
Q44.
Choose two The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? A) SELECT COUNT(prod_id) FROM sales WHERE quantity_sold>55000 GROUP BY prod_id; B) SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT() > 10; C) SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000; D) SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT() > 10 GROUP BY COUNT() > 10; E) SELECT prod_id FROM sales WHERE quantity_sold> 55000 AND COUNT() > 10 GROUP BY prod_id HAVING COUNT(*) > 10; Answer: AB 考察 where 和 having、聚合函数出现的位置
select department_id,count(*) from employees group by department_id order by count(*) desc ;
#
SQL> select department_id from employees group by department_id having count(department_id) >40;
DEPARTMENT_ID
-------------
50
Q45. INTERVAL DAY
Choose the best answer You have been asked to create a table for a banking application. One of the columns must meet three requirements:
- Be stored in a format supporting date arithmetic without using conversion functions
- Store a loan period of up to 10 years
- Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use? A) TIMESTAMP WITH TIMEZONE B) TIMESTAMP C) TIMESTAMP WITH LOCAL TIMEZONE D) INTERVAL YEAR TO MONTH E) INTERVAL DAY TO SECOND Answer: E 解析:官档 SQL Language reference--->Basic Elements of Oracle SQL
Q46. (Drop)
Choose three Which three are true about dropping columns from a table? A) A column can be removed only if it contains no data. B) A column drop is implicitly committed C) A column that is referenced by another column in any other table cannot be dropped. D) A column must be set as unused before it is dropped from a table. E A primary key column cannot be dropped. F) Multiple columns can be dropped simultaneously using the ALTER TABLE command. Answer: BCF 解析:alter table tablename drop (col1,col2);--->删除多列
Q47. (GLOBAL TEMPORARY TABLES)
Choose three Which three statements are true about GLOBAL TEMPORARY TABLES? A) GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. B) A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. (只会删除当前session数据) C) A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. D) A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions. E) Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. F) GLOBAL TEMPORARY TABLE space allocation occurs at session start. Answer:BDF
create global temporary table emp_temp(eno number) on commit preserve rows;
insert into emp_temp values(101);
commit;
select * from emp_temp;
delete from emp_temp;
ROLLBACK;
select * from emp_temp;
truncate table emp_temp;
1. 创建SQL语句
CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ ON COMMIT PRESERVE | DELETE ROWS ]
SQL> create global temporary table emp_temp(eno number) on commit delete rows;
-- transaction level duration，事务级别，此为默认选项
SQL> create global temporary table emp_temp(eno number) on commit preserve rows;
-- session level duration，会话级别
2. 隔离性：
数据只在会话或者事务级别可见。不同用户可以使用同一个临时表，但是看到的都是各自的数据。
3. 表上可以创建索引、视图、触发器等对象。
4. 索引只有在临时表是empty时可创建。
5. 临时表不产生数据的redo，但是会生成undo的redo。
6. 临时表目前只支持GLOBAL的，所以创建语句为create global temporary table XXX。
7. 使用truncate只对当前会话有效。
8. 不能export/import表上的数据，只能导入导出表定义。
9. 临时段在第一次insert或CATS时产生。
缺点：
1. 表定义不能自动drop。
2. 临时表目前只支持GLOBAL。
3. 只有无会话时才能DDL，否则可能报错：
Q48.(NUMBER)
Choose two. You create a table by using this command: CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements? A) INSERT INTO rate_list VALUES (-.9) inserts the value as -.9. B) INSERT INTO rate_list VALUES (0.999) produces an error. C) INSERT INTO rate_list VALUES (-10) produces an error. D) INSERT INTO rate_list VALUES (87654. 556) inserts the value as 87654.6. E) INSERT INTO rate_list VALUES (0.551) inserts the value as .55. F) INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99. Answer: AE 解析:实验操作即可验证，D 选项会报超出范围的报错
CREATE TABLE rate_list (rate NUMBER(6,2));
INSERT INTO rate_list VALUES (-.9);
INSERT INTO rate_list VALUES (0.999) ;
INSERT INTO rate_list VALUES (87654) ; --ORA-01438: 值大于为此列指定的允许精度
INSERT INTO rate_list VALUES (1234.567) ; --1234.57
INSERT INTO rate_list VALUES (-99.99) ; -- -99.99
select * from rate_list;
Q49. non-equijoin
What is true about non-equijoin statement performance? Choose two Which two statements are true about date/time functions in a session where A) The between condition always performs less well than using the >= and <= conditions. B) The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax. C) The join syntax used makes no difference to performance. D) The between condition always performs better than using the >= and <= conditions. E) Table aliases can improve performance. Answer: CE
Q50.
NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS A) SYSDATE can be used in expressions only if the default date format is DD-MON-RR. B) CURRENT_TIMESTAMP returns the same date as CURRENT_DATE. C) CURRENT_DATE returns the current date and time as per the session time zone D) SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. E) CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds. F) SYSDATE can be queried only from the DUAL table. Answer: CE 解析:操作验证结果: 官档:
SQL> select sysdate from dual;
SYSDATE
-------------------
2020-02-03 23:35:35
SQL> select current_date from dual;
CURRENT_DATE
-------------------
2020-02-03 23:35:35
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
03-FEB-20 11.35.35.799136 PM +08:00
Q51.INSERT ALL
Choose two Which two statements are true about conditional INSERT ALL? A) Each row returned by the subquery can be inserted into only a single target table. B) It cannot have an ELSE clause. C) The total number of rows inserted is always equal to the number of rows returned by the subquery D) A single WHEN condition can be used for multiple INTO clauses. E) Each WHEN condition is tested for each row returned by the subquery. Answer: CE 解析:官档语法:
Q52. INTERVAL
Choose two Which two statements are true about INTERVAL data types A) INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. B) The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column. C) INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year. D) The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value. E) INTERVAL DAY TO SECOND columns support fractions of seconds. F) INTERVAL YEAR TO MONTH columns support yearly intervals. Answer: EF 解析:官档语法
Oracle语法:
INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
select sysdate,sysdate + interval '100' year(3) from dual;--当前时间+100年
select sysdate,sysdate + interval '1-1' year to month from dual;--当前时间+1年1月
select sysdate,sysdate + interval '-1-1' year to month from dual;--当前时间-(1年1月)
select sysdate,sysdate + interval '100-1' year(3) to month from dual;--当前时间+100年1月
select sysdate,sysdate + interval '100' year(2) from dual;--报错ORA-01873: 间隔的前导精度太小，因为100是3位数，而2只指定了两位
select sysdate,sysdate + interval '100-1' year(2) to month from dual;--报错ORA-01873: 间隔的前导精度太小，因为100是3位数，而2只指定了两位
##
INTERVAL DAY TO SECOND
包含天、小时、分钟、秒的一段时间的间隔
select sysdate,sysdate+1 from dual;--当前时间+1天
select sysdate,sysdate + interval '1' day from dual;--当前时间+1天
select sysdate,sysdate+numtodsinterval(1,'day') as res from dual;--当前时间+1天
select sysdate,sysdate + interval '1 0' day to hour from dual;--当前时间+1天
select sysdate,sysdate + interval '1 1' day to hour from dual;--当前时间+1天1小时
select sysdate,sysdate + interval '1 01:01' day to minute from dual;--当前时间+1天1小时1分钟
select sysdate,sysdate + interval '1 01:01:01' day to second from dual;--当前时间+1天1小时1分钟1秒
#
SQL> select sysdate,sysdate + interval '-1-1' year to month from dual;
SYSDATE SYSDATE+INTERVAL'-1
------------------- -------------------
2020-02-04 06:56:55 2019-01-04 06:56:55
SQL> select sysdate,sysdate + interval '1' year from dual;
SYSDATE SYSDATE+INTERVAL'1'
------------------- -------------------
2020-02-04 06:44:48 2021-02-04 06:44:48
#
SQL> select sysdate,sysdate + interval '100-1' year(3) to month from dual;
SYSDATE SYSDATE+INTERVAL'10
------------------- -------------------
2020-02-04 06:45:56 2120-03-04 06:45:56
##
SQL> select sysdate,sysdate + interval '1 01:01:01' day to second from dual;
SYSDATE SYSDATE+INTERVAL'10
------------------- -------------------
2020-02-04 06:47:53 2020-02-05 07:48:54
Q53. UNION
Choose two Which two true about a sql statement using SET operations such as UNION? A) The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query B) The data type of each column retuned by the second query must exactly match the data type of the corresponding column returned by the first query C) The number, but not names, of columns must be identical for all SELECT statements in the query D) The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query E) The names and number of columns must be identical for all SELECT statements in the query. Answer: AC 考察集合运算，只要满足每个 SQL 的列的个数一样，每个列的数量类型能够互相转换就可 以。
Q54.
Choose the best answer. Examine this query: SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3; What is returned upon execution? A) 0 rows B) an error C) 8 rows D) 6 rows E) 1 row F) 3 rows Answer: E 解析:实验验证:
SQL> SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
2
----------
2
Q55.
Choose two Examine this query: SELECT employee_id,first_name,salary FROM employees WHERE hire_date > '&1'; Which two methods should you use to prevent prompting for a hire date value when this query is executed? A) Use the DEFINE command before executing the query. B) Store the query in a script and pass the substitution value to the script when executing it. C) Replace '&1' with '&&1' in the query. D) Execute the SET VERIFY OFF command before executing the query. E) Use the UNDEFINE command before executing the query. F) Execute the SET VERIFY ON command before executing the query. Answer: AB 考点:禁止提示输入&1 变量的值，那么提前声明变量赋值
替换变量可以看成是占位符。执行时解析&变量，有时称为绑定(runtime binding)或 替换(runtime substitution)。
当有相同的变量在SQL中重复出现，可以使用&&来替换避免重复输入。
# DEFINE
SQL> DEFINE col=EMAIL
SQL> select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col;
old 1: select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col
new 1: select first_name,job_id, EMAIL from hr.employees where job_id = 'MK_MAN' order by EMAIL
SET DEFINE OFF，可使客户工具本次不再保存会话变量或给&特殊含义。
Q56.
Choose the best answer Examine the description of the CUSTOMERS table:
Name Null? Type ------------------------------------------------------------------------------- ---------- CUST_ID FIRST_NAME LAST_NAME ADDRRESS CITY You want to display details of all customers who followed by at least two character Which query can be used? A) SELECT * FROM customers WHERE city = 'D_%'; B) SELECT * FROM customers WHERE city = '%D_'; C) SELECT * FROM customers WHERE city LIKE 'D__%'; D) SELECT * FROM customers WHERE city LIKE 'D_'; Answer: C 解析:考察 like 模糊匹配
Q57.
Examine the description or the CUSTOMERS table: Name CUST_ID CUST_FIRST_NAM CUST_LAST_NAME CUST_INCOME_LEVEL CUST_CREDIT_LIMIT Null? NOT NULL NOT NULL NOT NULL Type NUMBER VARCHAR2(20) VARCHAR2(30) VARCHAR2(30) NUMBER Not NULL Not NULL VARCHAR2(6) VARCHAR2(50) VARCHAR2(50) VARCHAR2(50) VARCHAR2(25) reside in cities starting with the letter D For Customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used? A) SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level != NULL; B) SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL; C) SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;
D) SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL; E) SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMONT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL; Answer: C
Q58. full outer join
Which two statements are true about a full outer join? A) It includes rows that are returned by an inner join. B) The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause. C) It includes rows that are returned by a Cartesian product. D) It returns matched and unmatched rows from both tables being joined. E) It returns only unmatched rows from both tables being joined. Answer: AD
对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：
（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
（+）操作符只适用于列，而不能用在表达式上。
（+）操作符不能与OR和IN操作符一起使用。
（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
CREATE TABLE t_A (
id number,
name VARCHAR2(10)
);
CREATE TABLE t_B (
id number,
name VARCHAR2(10)
);
INSERT INTO t_A VALUES(1,'A');
INSERT INTO t_A VALUES(2,'B');
INSERT INTO t_A VALUES(3,'C');
INSERT INTO t_A VALUES(4,'D');
INSERT INTO t_A VALUES(5,'E');
INSERT INTO t_B VALUES(1,'AA');
INSERT INTO t_B VALUES(1,'BB');
INSERT INTO t_B VALUES(2,'CC');
INSERT INTO t_B VALUES(1,'DD');
select * from t_A;
select * from t_B;
select * from t_A a left join t_B b on a.id = b.id; -- 左链接
select * from t_A a right join t_B b on a.id = b.id; -- 右链接
Select * from t_A a,t_B b where a.id=b.id(+); --左链接
Select * from t_A a,t_B b where a.id(+)=b.id; --右链接
Q59. 替换符
You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your session. Which query can be used? A) SELECT &col1, &col2 FROM &&table WHERE &condition; B) SELECT &col1, &col2 FROM “&table” WHERE &condition; C) SELECT &&col1,&&col2 FROM &table WHERE &&condition= &&cond; D) SELECT ‘&co11’, ‘&&co12’ FROM &table WHERE ‘&&condition‘ = ‘&cond’; E) SELECT&&col1, &&col2 FROM &table WHERE &&condition; Answer: A
SQL> select first_name,last_name from hr.employees where last_name like '%&SEARCH%' and first_name like '%&&SEARCH%'; 要输入两次.
SQL> select first_name,last_name from hr.employees where last_name like '%&&SEARCH%' and first_name like '%&SEARCH%'; 输入一次.
Enter value for search: G
Q60. ORACLE DATAPUMP
Which three actions can you perform by using the ORACLE DATAPUMP access driver? A) Create a directory object for an external table. B) Read data from an external table and load it into a table in the database. C) Query data from an external table. D) Create a directory object for a flat file. E) Execute DML statements on an external table. F) Read data from a table in the database and insert it into an external table. Answer:ACF
Q61. single row functions
Which two statements are true about single row functions? A) CONCAT: can be used to combine any number of values B) FLOOR: returns the smallest integer greater than or equal to a specified number C) CEIL: can be used for positive and negative numbers D) TRUNC: can be used with NUMBER and DATE values E) MOD: returns the quotient of a division operation Answer:CD
Q62.
Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEES_ID column is indexed. Rows exist for employees 100 and 200. Examine this statement: UPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees WHERE employee_id = 200) WHERE employee id=100; Which two statements are true? A) Employees 100 and 200 will have the same SALARY as before the update command. B) Employee 100 will have SALARY set to the same value as the SALARY of employee 200. C) Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200. D) Employees 100 and 200 will have the same JOB ID as before the update command. E) Employee 200 will have SALARY set to the same value as the SALARY of employee 100. F) Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100 Answer:BC
Q63. non equijoins
Which two statements are true regarding non equijoins? A) The ON clause can be used. B) The USING clause can be used. C) The SQL:1999 compliant ANSI join syntax must be used. D) Table aliases must be used. E) The Oracle join syntax can be used. Answer:AE
Q64. ORDER BY
Which two statements are true about the ORDER BY clause? A) Numeric values are displayed in descending order if they have decimal positions. B) Only columns that are specified in the SELECT list can be used in the ORDER BY cause. C) NULLS are not included in the sort operation. D) Column aliases can be used In the ORDER BY cause. E) Ina character sort, the values are case-sensitive. Answer:DE
Q65.
Examine the description of the CUSTONERS table: Name CUSTNO CUSTNAME CUSTADDRESS CUST_CREDIT_LIMIT CUSTNO is the PRIMARY KEY. You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names. Which two methods can you use to get the required result? A) LEFT OUTER JOIN with self join B) PULL OUTER JOIN with self join C) subquery D) RIGHT OUTER JOIN with self join E) self Join Answer:CE
select EMPLOYEE_ID,last_name from employees where last_name in (
select last_name from employees group by last_name having count(last_name)>1
);
Q66. explicit data type conversion
Examine the description of the EMPLOYEES table: Null? NOT NULL Type NUMBER(3) NOT NULL VARCHAR2 (25) VARCHAR2 (35) Name EMP_ID EMP_NAME DEPT_ID SALARY JOIN_DATE NLS_DATE_FORMAT is set to DD-MON-YY. Which query requires explicit data type conversion? A) SELECT salary + 120.50 FROM employees; B) SELECT SUBSTR(join date, 1, 2)- 10 FROM employees; C) SELECT join date 11.’11 salary FROM employees; D) SELECT join date FROM employees where join date > 10-02-2018; E) SELECT join date + 20 FROM employees; Answer:D
SQL> select substr(sysdate,1,2) from dual;
SUBSTR(SYSDATE,1
----------------
20
SQL> select sysdate + 2 from dual;
SYSDATE+2
-------------------
2020-02-06 09:49:02
Q67.MERGE
Which two are true about the MERGE statement? Null Type NOT NULL NUMBER VARCHAR2 (10) NUMBER (2) NUMBER(8,2) DATE NUMBER(5)
A) The WHEN NOT MATCHED clause can be used to specify the deletions to be performed. B) The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. C) The WHEN MATCHED clause can be used to specify the inserts to be performed. D) The WHEN NOT MATCHED clause can be used to specify the updates to be performed. E) The WHEN MATCHED clause can be used to specify the updates to be performed. Answer:BE
1、可省略的UPDATE或INSERT子句
在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:
SQL> MERGE INTO products p
2 USING newproducts np
3 ON (p.product_id = np.product_id)
4 WHEN MATCHED THEN
5 UPDATE
6 SET p.product_name = np.product_name,
7 p.category = np.category;
Q68. ER模型
Examine this business rule: Each student can work on multiple projects and each project can have multiple students. You must design an Entity Relationship(ER) model for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NANE PROJECT_TASK Which two statements are true? A) An associative table must be created with a composite key of STUDENT_ID and PROJRCT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities. B) PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity. C) The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities. D) The ER must have a many to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. E) STUDENT ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity. Answer:AD
Q69.
Which statements are true regarding primary and foreign key constraints and the effect they can have on table data? A) A table can have only one primary key but multiple foreign keys. B) It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted. C) Primary key and foreign key constraints can be defined at both the column and table level. D) Only the primary key can be defined the column and table level. E) It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted. F) The foreign key columns and parent table primary key columns must have the same names. G) A table can have only one primary key and one foreign key. Answer:ABCE
Q70. substitution variables
Which two statements are true about substitution variables? A) A substitution variable used to prompt for a column name must be endorsed in single quotation marks. B) A substitution variable used to prompt for a column name must be endorsed in double quotation marks. C) A substitution variable prefixed with & always prompts only once for a value in a session. D) A substitution variable can be used with any clause in a SELECT statement. E) A substitution variable can be used only in a SELECT statement. F) A substitution variable prefixed with 6 prompts only once for a value in a session unless is set to undefined in the session. Answer:DF
SQL> select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col;
Enter value for col: email
Q71. （transaction xx)
In which three situations does a new transaction always start? A) When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session B) When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session C) When issuing a TRUNCATE statement after a SELECT statement was issued in the same session D) When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session E) When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session F) When issuing a DML statement after a DML statement filed in the same session. Answer:ABE
Q72. SET VERIFY ON
Which two statements are true about the SET VERIFY ON command? A) It displays values for variables created by the DEFINE command. B) It can be used in SQL Developer and SQLPlus. C) It can be used only in SQLplus. D) It displays values for variables prefixed with &&. E) It displays values for variables used only in the WHERE clause of a query. Answer:CD
SET VERIFY ON;
UNDEFINE col;
select first_name,job_id, &&col from hr.employees where job_id = 'MK_MAN' order by &col;
Q73. regarding indexes
Which three statements are true regarding indexes? A) A SELECT statement can access one or more indices without accessing any tables. B) A table belonging to one user can have an index that belongs to a different user, C) When a table is dropped and is moved to the RECYCLE BIN, all Indexes built on that table are permanently dropped. D) A UNIQUE index can be altered to be non-unique. E) An update to a table can result in no updates to any of the table's indexes. F) An update to a table can result in updates to any or all of the table's indexes. Answer:BCE
Q74.
Examine the description of the PROMTIONS table: Name Null? Type
PROMO_ID PROMO_NAME PROMO_CATEGORY PROMO_COST NOT NULL NOT NULL NOT NULL Which two queries can be used? A) SELECT promo_cost, | pxomo_category FROM promotions ORDER BY 1; B) SELECT promo_category, DISTINCT promo_cost PROM promotions ORDER BY 2: C) SELECT DISTINCT promo_category ||'has’|| promo_cost AS COSTS FROM promotions ORDER BY 1; D) SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; E) SELECT DISTINCT promo_cost ||' in' II DISTINCT promo_category FROM promotions ORDER BY 1; Answer:CD
SQL> select distinct last_name,salary, department_id from employees where last_name ='King' ;
LAST_NAME SALARY DEPARTMENT_ID
-------------------------------------------------- ---------- -------------
King 10000 80
King 10000 90
SQL> select distinct last_name,salary from employees where last_name ='King' ;
LAST_NAME SALARY
-------------------------------------------------- ----------
King 10000
#error
SQL> select salary ,distinct last_name from employees where last_name ='King' ;
select salary ,distinct last_name from employees where last_name ='King'
*
ERROR at line 1:
ORA-00936: missing expression
Q75. CTAS 建表无索引
Examine the description of the SALES table NUMBER(6) VARCHAR2 (30) VARCHAR2 (30) NOT NULL You want to display the unique promotion costs in each promotion category. Name PRODUCT_ID CUSTOMER_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD PRICE AMOUNT_SOLD Null? NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL Type NUMBER(10) NUMBER(10) DATE NUMBER(5) NUMBER(5) NUMBER(10,2) NUMBER(10,2) NUMBER(10,2) NUMBER(10,2) The SALES table has 5,000 rows. Examine this statement: CREATE TABLE sales1 (prod id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1=1 Which two statements are true? A) SALES1 is created with 1 row. B) SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table. C) SALES1 Is created with 5,000 rows. D) SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table. Answer:CD
#CTAS建表不会有索引，即新表不会有Primary key ,UNIQUE Key,Foreign Key。但是会有 NOT NULL和Check约束。
create table emp_copy as select * from employees;
select count(*) from emp_copy;
desc emp_copy;
desc employees;
TABLESPACE "USERS" ENABLE,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
Q76.
Examine the data in the CUST_NAME column of the CUSTOMERS table:
CUST_NAME --------------------- Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? A) WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) LIKE UPPER('MC%') B) WHERE SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1) LIKE 'Mc%’ OR 'MC%’ C) WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ’) + 1)) IN (‘MC%’,’Mc%’) D) WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,’ ') + 1)) LIKE ‘Mc%’ E) WHERE SUBSTR(cust_name, INSTR(cust_name,’ ‘) + 1) LIKE ‘Mc%’ Answer:AD
SQL> select instr('Abc Efg', ' ' ) from dual;
INSTR('ABCEFG','')
------------------
4
SQL> select substr('Abc Efg', instr('Abc Efg', ' ' )+ 1) from dual;
SUBSTR
------
Efg
Q77.
Examine this SQL statement: SELECT cust_id, cust_last_name "Last Name FROM customers WHERE countryid=10 UNION SELECT custid CUSTNO, cust_last_name FROM customers WHERE countryid=30 Identify three ORDER BY clauses, any one of which can complete the query successfully. A) ORDER BY“CUST NO" B) ORDER BY 2, cust_id C) ORDERBY2, 1 D) ORDER BY "Last Name" E) ORDER BY CUSTNO Answer:BCD
单引号和双引号:
- 带有空格的列名，只能用双引号 ,不能是单引号引起来。
- 连字符只能用'单引号。 select "abc" || " efg " from dual; 会出错
#单引号和双引号是不同的。
SQL> select "abc" || " efg " from dual;
ORA-00904: " efg ": invalid identifier
#
SQL> select employee_id,last_name 'emp name' from employees ; --将出错。
SQL> select employee_id,last_name "emp name" from employees where last_name like 'A%' order by "emp name";
EMPLOYEE_ID emp name
----------- --------------------------------------------------
174 Abel
166 Ande
130 Atkinson
105 Austin
Q78.
Examine the description of the CUSTOMERS table: Name CUST_ID CUST_LAST_NAME CITY CUST_CREDIT_LIMIT You need to display last names and credit limits of all customers whose last name starts with A or B In Null? NOT NULL Type VARCHAR2(2) VARCHAR2 (30) VARCHAR2 (10) NUMBER(6,2)
lower or upper case, and whose credit limit is below 1000. Examine this partial query: SELECT cust_last_nare, cust_credit_limit FROM customers Which two WHERE conditions give the required result? A) WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000: B) WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR ITITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000 C) WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%’) AND ROUND(cust_credit_limit) < 1000; D) WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000; E) WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ; Answer:BD
Q79.
Examine this query: SELECT employee_id, first_name, salary FROM employees WHERE hiredate > 61* Which two methods should yours to prevent prompting for hire date value when this queries executed? A) Execute the SET VERIFY ON command before executing the query. B) Execute the SET VERIFY OFF command before executing the query. C) Store the query in a script and pass the substitution value to the script when executing it. D) Replace 's1' with &1'in the query: E) Use the UNDEFINE command before executing the query. F) Use the DEFINE command before executing the query Answer:CF
Q80. oracle外表(external table)]
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 ACCESS PARAMETERS clause B) the DEFAULT DIRECTORY clause C) the access driver TYPE clause D) the LOCATION clause E) the REJECT LIMIT clause Answer:BD
oracle外表:
- 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"
- 数据在数据库的外部组织，是操作系统文件。
- 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
- 数据是只读的。（外部表相当于一个只读的虚表）
- 不可以在上面运行任何 DML 操作，不可以创建索引。
- 可以查询操作和连接。可以并行操作。
mkdir -p /home/oracle/external_tb/data
SQL> create or replace directory data_dir as '/home/oracle/external_tb/data/';
SQL> grant read,write on directory data_dir to scott;
SQL> conn scott/tiger
create table ex_tb1
(ename,job,sal,dname)
organization external
(type oracle_datapump default directory data_dir location('ex_tb1'))
parallel 1
as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;
select * from ex_tb1;
#外部表
SQL> create directory TestTable_dir as '/home/oracle/';
Directory created.
SQL> grant read,write on directory TestTable_dir to public;
Grant succeeded.
create table TestTable(
ID varchar2 ( 10 ),
NAME varchar2 ( 20 ))
organization external (
type oracle_loader
default directory TestTable_dir
access parameters (fields terminatedby ',' )
location ( 'TestTable.csv' )
);
Q81. constraint
You execute this command:
TRUNCATE TABIE depts; Which two are true? A) A ROLLBACK statement can be used to retrieve the deleted data. B) It drops any triggers defined on the table. C) It retains the indexes defined on the table. D) It retains the integrity constraints defined on the table, E) It always retains the space used by the removed rows. F) A FLASHBACK TABLE statement can be used to retrieve the deleted data. Answer:CD
Q82.
Examine these SQL statements which execute successfully: CREATE TABLE emp (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR2(15), salary NUMBER(8,2), mgr_no NUMBER(2));
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr_no) REFERENCES emp(emp_no) ON DELETE SET NULL;
ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk; Which two statements are true after execution? A) The primary key constraint will be enabled and DEFERRED. B) The primary key constraint will be enabled and IMMEDIATE. C) The foreign key constraint will be disabled. D) The foreign key constraint will be enabled and DEFERRED. E) The foreign key constraint will be enabled and IMMEDIATE. Answer:BC
在默认情况下，Oracle的约束是不允许延迟（not deferrable）、立即应用和验证的（immediate、validated）。在数据变化的时候，立即进行约束验证。
create table t (id number);
alter table T add constraint c_t_id1 check (id>5);
select constraint_name, constraint_type ctype, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' ;
CONSTRAINT_NAME CTYPE COND STATUS DEFERRABLE DEFERRED VALIDATED
-------------------- ----- ---------- -------- -------------- --------- -------------
C_T_ID1 C id>5 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
alter table T drop constraint C_T_ID1;
alter table T add constraint C_T_ID1 check (id>5) deferrable;
###
drop table emp;
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2));
select owner,constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where table_name='emp';
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr_no)
REFERENCES emp(emp_no) ON DELETE SET NULL;
ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk;
select owner,constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where owner='HR' and table_name='EMP';
SQL> select owner,constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from user_constraints where owner='HR' and table_name='EMP';
OWNER CONSTRAINT_NAME COND STATUS DEFERRABLE DEFERRED VALIDATED
----------------------------------------------------------------------------------------------
HR EMP_EMP_NO_PK ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
HR EMP_MGR_FK DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED
Q83. quote
Examine the description of the PRODUCT_STATUS table: Name PROD_ID STATUS Null? NOT NULL NOT NULL Type NUMBER(2) VARCHAR2(15)
The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row Which two queries will execute successfully? A) SELECT prod_id "CURRENT AVAILABILITY" || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK'; B) SELECT prod_id || q's not available'' FROM product_status WHERE status=’OUT OF STOCK’; C) SELECT prod_id || q'('s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK'; D) SELECT prod_id || q'('s not available)' FROM product_status WHERE status = ’OUT OF STOCK’; E) SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK'; F) SELECT prod_id || q"'s not available" FROM product_status WHERE status = 'OUT OF STOCK'; Answer CD
SQL> select employee_id || '''s exist ' "test_colum" from employees where rownum<2;
test_colum
--------------------------------------------------------------------------------------------------
100's exist
SQL> select employee_id || q'('s exist )' "test_colum" from employees where rownum<2;
test_colum
--------------------------------------------------------------------------------------------------
100's exist
Q84. oracle join
Which three statements are true about the Oracle join and ANSI Join syntax? A) The Oracle join syntax only supports right outer joins, B) The Oracle join syntax supports creation of a Cartesian product of two tables. C) The SQL:1999 compliant ANSI join syntax supports natural joins. D) The Oracle join syntax supports natural joins. E) The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax. F) The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables, G) The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer: Answer:BCF
- oracle 联接: 支持内联接,外联接和笛卡尔积，不支持自然join.
Q85. GRANT
Which two are true about the WITH GRANT OPTION clause? A) The grantee can grant the object privilege to any user in the database, with of without including this option, B) The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option. C) It can be used when granting privileges to roles. D) It can be used for system and object privileges. E) It cannot be used to pass on privileges to PUBLIC by the grantee. F) It can be used to pass on privileges to other users by the grantee. Answer:AF
Q86. VIEW
Which three statements are true about performing Data Manipulation Language (DML) operations on a view In an Oracle Database? A) Insert statements can always be done on a table through a view. B) The WITH CHECK clause has no effect when deleting rows from the underlying table
through the view. C Views cannot be used to query rows from an underlying table if the table has a PRIPOARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. D) Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. E) Views cannot be used to add on modify rows in an underlying table if the defining query of the view contains aggregating functions. F) Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. Answer:DEF
Q87. GLOBAL TEMPORARY TABLES
Which three statements are true about GLOBAL TEMPORARY TABLES? A) GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. B) A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. C) A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. D) A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions. E) Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. F) GLOBAL TEMPORARY TABLE space allocation occurs at session start. Answer:BDF
1. 创建SQL语句
CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ ON COMMIT PRESERVE | DELETE ROWS ]
SQL> create global temporary table emp_temp(eno number) on commit delete rows;
-- transaction level duration，事务级别，此为默认选项
SQL> create global temporary table emp_temp(eno number) on commit preserve rows;
-- session level duration，会话级别
2. 隔离性：
数据只在会话或者事务级别可见。不同用户可以使用同一个临时表，但是看到的都是各自的数据。
3. 表上可以创建索引、视图、触发器等对象。
4. 索引只有在临时表是empty时可创建。
5. 临时表不产生数据的redo，但是会生成undo的redo。
6. 临时表目前只支持GLOBAL的，所以创建语句为create global temporary table XXX。
7. 使用truncate只对当前会话有效。
8. 不能export/import表上的数据，只能导入导出表定义。
9. 临时段在第一次insert或CATS时产生。
临时表:
create global temporary table emp_temp1(eno number) on commit delete rows;
create global temporary table emp_temp2(eno number) on commit preserve rows;
insert into emp_temp1 values(100);
insert into emp_temp2 values(200);
commit;
select * from emp_temp2;
truncate table emp_temp2;
select * from emp_temp2;
Q88. GROUP
Examine the description of the EMPLOYEES table: Name EMP_ID EMPNAME DEPT_ID SALARY JOIN_DATE Which query is valid? Null? NOT NULL Type NUMBER VARCHAR2 (40) NUMBER(2) NUNGER(B,2) DATE A)SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date; B) SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id; C) SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id; D) SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id; Answer:A
SELECT DEPARTMENT_ID, JOB_ID , SUM(salary) FROM employees GROUP BY DEPARTMENT_ID,JOB_ID ; --ok
Q89. 运算优先级
Examine the description of the BOOKS_TRANSACTIONS table: Name TRANSACTION_ID TRANSACTION_TYPE BORROWED_DATE Null? NOT NULL Type VARCIUR2 (6) VARCHAR2 (3) DATE
BOOK_ID VARCHAR2 (6) MEMBER_ID VARCHAR2 (6) Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result? A) WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101','A102'); B) WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102'); C) WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN('A101','A102'); D) WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102')); E) WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102'); Answer:AB
Q90. multiple row subqueries
Which three statements are true about multiple row subqueries? A) They can contain HAVING clauses. B) Two or more values are always returned from the subquery. C) They cannot contain subquery. D) They can return multiple columns. E) They can contain GROUP BY clauses. Answer:ABE
多行子查询可以返回单列多行数据。
多行运算符：in、any和all。
与单行运算符组合：
=any：与in等价
>any：表示大于最小值
>all：表示大于最大值
Q91. MINUS
Examine the description of the SALES1 table: Name SALES_ID STORE_ID ITEMS_ID QUANTITY SALES_DATE Null NOT NULL NOT NULL Type NUMBER NUMBER NUMBER NUMBER DATE SALES2 is a table with the same description as SALES1, Some sales data is duplicated In both tables.
You want to display the rows from the SALES1 table which are not present in the SALIES2 table. Which set operator generates the required output? A) SUBTRACT B) INTERSECT C) UNION ALL D) MINUS E) UNION Answer:D
Q92. UNION
PRODUCTS; PROD_ID PROD_NAME EXP_DATE NEW PRODUCTS; PROD_ID PROD_NAME EXP_DATE CHAR(2) CHAR(4) TIMESTAMP (6) CHAR(4) VARCHAR2(10) DATE Which two queries execute successfully? A. SELECT prod_id, exp_date FROM products UNION ALL SELECT prod_id, NULL FROM new_products; B. SELECT prod_id, prod_name FROM products INTERSECT SELECT 100, prod_name FROM newproducts; C. SELECT * FROM products UNION SELECT * FROM new_products; D. SELECT k FROM products MINUS SELECT prod_id FROM new_products; E. SELECT prod_id FROM products UNION ALL SELECT prod_id, prod_name FROM new_products;
Answer:AC
原答案为 BC?? 测试A是正确的,B是错误的，因为类型不同。
create table tt1 (PROD_ID char(2), PROD_NAME char(4), EXP_DATE TIMESTAMP(6));
insert into tt1 values(1,'111',sysdate);
insert into tt1 values(1,'111',systimestamp);
select * from tt1;
create table tt2 (PROD_ID char(2), PROD_NAME varchar2(20), EXP_DATE date);
insert into tt2 values(2,'222',sysdate);
select prod_id ,exp_date from tt1 union all select prod_id ,null from tt2;
select * from tt1 union select * from tt2;
SQL> desc tt1;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID CHAR(2)
PROD_NAME CHAR(4)
EXP_DATE TIMESTAMP(6)
SQL> desc tt2;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROD_ID CHAR(2)
PROD_NAME VARCHAR2(20)
EXP_DATE DATE
SQL> SELECT prod_id, prod_name FROM tt1 INTERSECT SELECT 100, prod_name FROM tt2;
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
Q93. sequence
BOOK_SEQ is an existing sequence in your schema. Which two CREATE TABLE commands are valid? A. CREATE TABLE bookings ( bk_id NUMBER(4) NOT NULL PRIMARY KEY, start_date DATE NOT NULL, end_date DATE DEFAULT SYSDATE);
B. CREATE TABLE bookings ( bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL, start_date DATE NOT NULL, end_date DATE DEFAULT SYSDATE);
C. CREATE TABLE bookings ( bk_id NUMBER(4) DEFAULT book_seq.CURRVAL, start_date DATE DEFAULT SYSDATE, end_date DATE DEFAULT start date); D. CREATE TABLE bookings ( bk_id NUMBER(4), start_date DATE DEFAULT SYSDATE, end_date DATE DEFAULT (end_date >= start_date)); E. CREATE TABLE bookings ( bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY, start_date DATE DEFAULT SYSDATE, end_date DATE DEFAULT SYSDATE NOT NULL); Answer:AE
not null要放在最后面。
create sequence book_seq
minvalue 1
maxvalue 5
start with 1
increment by 1
nocache
order;
SQL> select book_seq.nextval from dual;
NEXTVAL
----------
5
SQL> select book_seq.nextval from dual;
select book_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence BOOK_SEQ.NEXTVAL exceeds MAXVALUE and cannot be
instantiated
#CYCLE
SQL> create sequence book_seq2
minvalue 1 maxvalue 3
start with 1 increment by 1
CYCLE nocache ;
SQL> select book_seq2.nextval from dual;
NEXTVAL
----------
3
SQL> select book_seq2.nextval from dual;
NEXTVAL
----------
1
#CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.CURRVAL PRIMARY KEY NOT NULL,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE); --ok
Q94. NULLIF
Which two queries execute successfully? A. SELECT NULLIF(100, 100) FROM DUAL; B. SELECT COALESCE(100, NULL, 200) FROM DUAL; C. SELECT NULLIF(100, 'A') FROM DUAL; D. SELECT NULLIF(NULL, 100) FROM DUAL; E. SELECT COALESCE(100, 'A' ) FROM DUAL; Answer:AB 类型要相同才可比较。
SQL> SELECT NULLIF(100, 100) FROM DUAL;
NULLIF(100,100)
---------------
null
SQL> SELECT COALESCE(100, NULL, 200) FROM DUAL;
COALESCE(100,NULL,200)
----------------------
100
SQL> SELECT NULLIF(NULL, 100) FROM DUAL;
SELECT NULLIF(NULL, 100) FROM DUAL
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
Q95. primary and foreign key
Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? A. Only the primary key can be defined at the column and table level. B. The foreign key columns and parent table primary key columns must have the same names. C. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted. D. A table can have only one primary key but multiple foreign keys. E. Primary key and foreign key constraints can be defined at both the column and table level. F. A table can have only one primary key and one foreign key. G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted Answer:CDEG
#主键，外键都可以在列和表级别中定义
Q96.
Which three queries use valid expressions? A. SELECT product_id,(unit_price * 0.15 / (4.75 + 552.25)) FROM products; B. SELECT product_id,(expiry_date - delivery_date) * 2 FROM products; C. SELECT product_id,unit_price || 5 "Discount" , unit_price + surcharge - discount FROM products; D. SELECT product_id, expiry_date * 2 from products; E. SELECT product_id,unit_price,5 "Discount", unit_price + surcharge-discount FROM products; F. SELECT product_id, unit_price, unit_price + surcharge FROM products; Answer:ABF 没有给出表结构, E是否也可以？
SQL> select employee_id,1 "id" , salary from employees where rownum<2;
EMPLOYEE_ID id SALARY
----------- ---------- ----------
198 1 2600
Q97.entity relationship diagram
Which two statements are true about selecting related rows from two tables based on entity relationship diagram (ERD)? A. Relating data from a table with data from the same table is implemented with a self join. B. An inner join relates rows within the same table. C. Rows from unrelated tables cannot be joined. D. Implementing a relationship between two tables might require joining additional tables. E. Every relationship between the two tables must be implemented in a Join condition. Answer:AD
Q98. single row functions
Which three statements are true about single row functions? A. They can be used only in the where clause of a select statement. B. They can accept only one argument. C. They return a single result row per table. --error D. The argument can be a column name, variable, literal or an expression. E. They can be nested to any level. F. The date type returned can be different from the data type of the argument. Answer:DEF return a single result per row
Q99. synonyms
Which two statements are true about Oracle synonyms? A. A synonym can have a synonym. B. A synonym has an object number. C. Any user can create a public synonym. D. All private synonym names must be unique in the database. E. A synonym can be created on an object in a package Answer:AB
Q100. granting privilege
Which two are true about granting privilege on objects? A. The owner of an object acquires all object privilege on that object by default. B. The WITH GRANT OPTION clause can be used only by DBA users. C. A table owner must grant the references privilege to allow other users to create FOREIGN KEY constraints using that table. D. An object privilege can be granted to a role only by the owner of that object. E. An object privilege can be granted to other users only by the owner of object. Answer:AC
