Oracle 19c OCP 1Z0-082认证考试题库（24-29）

24.Which three statements are true regarding the UNION and UNION ALL operators?A.The number of columns selected by the first SELECT statement can be greater than the number selected in subsequent SELECT statementsB.Duplicates are eliminated automatically by the UNION ALL operatorC.The number of columns selected in each SELECT statement must be identical D.NULIS are not ignored during duplicate checkingE.The names of columns selected in each SELECT statement must be identicalF.The names of columns selected in each SELECT statement can be identical G.Duplicates can optionally be eliminated by the UNION operator答案：CDF25.The SALES table has columns PROD_ ID and QUANTITY_SOLD of data type  NUMBER. Which two queries execute successfully?A.SELECT prod_id FROM sales WHERE quantity_sold >55000 AND COUNT(*)> 10 GROUP BY prod_id HAVING COUNT(*)>10:B.SELECT COUNT (prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold >55000 C.SELECT prod_id FROM sales WHERE quantity_sold >55000 AND COUNT(*)>10 GROUP BY COUNT(*)>10:D.SELECT prod_id FROM sales WHERE quantity_sold >55000 GROUP BY prod_id HAVING COUNT (*)>10;E.SELECT COUNT (prod_id) FROM sales WHERE quantity_sold >55000 GROUP BY prod_id;答案：DESQL> select count(EMPLOYEE_ID) from employees where salary>10000 group by EMPLOYEE_ID;26.Which two statements are true about INTERVAL data types? A.INTERVAL YEAR TO MONTH columns support yearly intervalsB.The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive valueC.The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TOMONTH columnD.INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year E.INTERVAL DAY TO SECOND columns support fractions of secondsF. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years答案：AE27.Which two statements are true concerning logical and physical database structures?A.A segment's blocks can be of different sizes.B.A segment might have only one extentC.All tablespaces may have one or more data files D.Segments can span multiple tablespacesE.A segment can span multiple data files in some tablespaces答案：BE28.Which two statements are true about the Oracle join and ANSI join syntax?A.The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax. B.The Oracle join syntax lacks the ability to do outer joinsC.The SQL: 1999 compliant ANSI join syntax supports creation of a Cartesian product of two tablesD.The Oracle join syntax supports creation of a Cartesian product of two tablesE.The Oracle join syntax performs less well than the SQL: 1999 compliant ANSI join syntax答案：CD29.Examine this commandSQL> ALTER TABLE ORDERS SHRINK SPACE COMPACTWhich two statements are true?A.Queries and DML statements are allowed on ORDERs while the SHRINK is executing B.Dependent indexes become UNUSABLEC.The SHRINK operation causes rows to be moved to empty space starting toward the end of the ORDERs segmentD.The SHRINK operation causes rows to be moved to empty space starting from the beginning of the ORDERs segment.E.Only queries are allowed on ORDERs while the SHRINK is executingF.The high-water mark(HWM)of ORDERs is adjusted答案：ADsegment shrink 分为两个阶段：1、数据重组（compact）：通过一系列insert、delete操作，将数据尽量排在段的前面，这个过程中需要在表上加RX锁，及只需要移动的行上加锁2、2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块，此过程需要在表上加X锁（独享锁，因此这个表都被锁定，如果系统处在高峰期的时候，其他在此表的DML被挂起，会产生严重阻塞），会造成表上所有DML语句阻塞，系统忙时影响较大。shrink space语句两个阶段都执行 。  shrink space compact语句只执行第一个阶段。