A. SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) \"2000\SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0)) \"1999\"FROM promotions;
B. SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1ELSE 0 END) \"1999\ELSE 0 END) \"2000\"FROM promotions;
C. SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1ELSE 0 END) \"1999\ELSE 0 END) \"2000\"FROM promotions;
D. SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0)) \"1999\COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,0)) \"2000\"FROM promotions;Answer: A
答案解析：分析A答案
SUBSTR(promo_begin_date,8)，即是取日期的最后两个值。
(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)如果最后两个值是00，则值为1，否则为0 (DECODE(SUBSTR(promo_begin_date,8),'99',1,0)如果最后两个值是99，则值为1，否则为0 SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) 加起来有多少个1，即有多少个2000年的SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0))加起来有多少个1，即有多少个1999年的B，条件不对，应该 TO_CHAR(promo_begin_date,'yyyy') 改成 TO_CHAR(promo_begin_date,'yy') C,与B同样错误
D,截取不对，SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8)改成SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 1)
102. Examine the structure of the TRANSACTIONS table:name Null Type
TRANS_ID NOT NULL NUMBER(3)CUST_NAME VARCHAR2(30)TRANS_DATE TIMESTAMPTRANS_AMT NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12noon. The value zero should be displayed for transactions where the transaction amount has not beenentered.
Which query gives the required result?
A. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),TO_CHAR(trans_amt,'$99999999D99')FROM transactions
WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)<>NULL; B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
NVL(TO_CHAR(trans_amt,'$99999999D99'),0)FROM transactions
WHERE TO_CHAR(trans_date,'hh24') < 12;
C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) 已经是number类型，不需要转换FROM transactions
WHERE TO_DATE(trans_date,'hh24') < 12;
D. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'),
NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) 已经是number类型，不需要转换FROM transactions
WHERE TO_DATE(trans_date,'hh24') < 12;Answer: B
答案解析：
A,TO_DATE(trans_date,'hh24')，trans_date已经为日期类型，不用再to_date
scott@TESTDB> select to_date(HIREDATE,'yy24') from emp;select to_date(HIREDATE,'yy24') from emp *ERROR at line 1:
ORA-01821: date format not recognized
B,正确，这里0隐式转换为字符型
C,(TO_NUMBER(trans_amt,'$99999999.99')，trans_amt已经是数值型，不用再to_numberD,错误同C一样。
103. Examine the structure of the TRANSACTIONS table:name Null Type
TRANS_ID NOT NULL NUMBER(3)CUST_NAME VARCHAR2(30)TRANS_DATE DATETRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend.Evaluate the following two queries:SQL>SELECT TRANS_DATE,CASE
WHEN TRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'ELSE 'weekday'END \"Day Type\"FROM transactions;
SQL>SELECT TRANS_DATE, CASE
WHEN TO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'ELSE 'weekend'
END \"Day Type\"FROM transactions;
Which statement is true regarding the above queries?A. Both give wrong results.B. Both give the correct result.
C. Only the first query gives the correct result.
D. Only the second query gives the correct result.
Answer: C 答案解析：
此处 BETWEEN 'MONDAY' AND 'FRIDAY'是指MONDA****后面的值到FRIDAY这里是按照字符的ASCII码来排序的，此处的排序永远为假，即输出的都是weekend。
SQL>SELECT HIREDATE,CASE
WHEN TRIM(TO_CHAR(HIREDATE,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend' ELSE 'weekday' END \"Day Type\"FROM emp;
104. Examine the structure of the PROMOS table:name Null Type
PROMO_ID NOT NULL NUMBER(3)PROMO_NAME VARCHAR2(30)PROMO_START_DATE NOT NULL DATEPROMO_END_DATE DATE
You want to generate a report showing promo names and their duration (number of days). If thePROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed.Which queries give the correct output? (Choose all that apply.)
A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date - promo_start_date),'ONGOING') FROM promos;
C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
D. SELECT promo_name, DECODE(promo_end_date -promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date)FROM promos;
E. SELECT promo_name, decode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',promo_end_date - promo_start_date)FROM promos;
Answer: BCD 答案解析：
ANVL要求括号内参数的类型需一致，,NVL(promo_end_date -promo_start_date,'ONGOING')此处类型不一致，一个为数值型，一个为字符型
B, COALESCE 需括号内参数类型需一致，TO_CHAR(promo_end_date - promo_start_date),'ONGOING') ，此处使用to_char转换，两个参数都为字符型，可以比较。正确。
C, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING')，NVL括号内两个参数都为字符型，正确。D,表达式正确
E,不满足题意，coalesce返回第一个不为空的值，因为promo_start_date肯定不为空，所以即使promo_end_date为空，coalesce也会返回promo_start_dat，所以都会显示promo_end_date - promo_start_date值，即当promo_end_date为空时，还是会显示空（注意，如果你直接指定NULL-sysdate会报转换数据类型错，而在字段里就可以减，并返回空。
105. Examine the structure of the PROMOS table:name Null Type
PROMO_ID NOT NULL NUMBER(3)PROMO_NAME VARCHAR2(30)PROMO_START_DATE NOT NULL DATEPROMO_END_DATE NOT NULL DATE
You want to display the list of promo names with the message 'Same Day' for promos that started andended on the same day.
Which query gives the correct output?
A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') FROM promos;B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') FROM promos;
C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'SameDay') FROM promos;D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') FROM promos;Answer: D
答案解析：
A。NULLIF里面额数据类型为date与 'Same Day'字符串类型不匹配，错误。B。TRUNC将小数部分截断，但类型不一致，错误。
C。TO_CHAR(TRUNC(promo_end_date-promo_end_date))，如果相等，TRUNC为0，to_char后，字符为0，即始终有值，NVL2返回第二个值，即NULL
106. Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:LIST_PRICE MIN_PRICE10000 80002000030000 30000
Which two expressions give the same output? (Choose two.)A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price, min_price), 0) 如果list_price为空，则返回min_price，如果min_price为空，则返回空值，如果是空值，则返回
0
C. NVL2(COALESCE(list_price, min_price), min_price, 0)
D. COALESCE(NVL2(list_price, list_price, min_price), 0) 如果list_price为空，则返回min_price，如果min_price为空，则返回0，否则返回
list_price，
Answer: BD
答案解析：
LIST_PRICE 从表中看，没有空值，故
B的结果为list_price，D的结果为list_priceC的结果为min_price
A分情况，不等时为list_price，相等时为0
107. View the Exhibit and examine the structure and data in the INVOICE table.Which two SQL statements would execute successfully? (Choose two.)
A. SELECT AVG(inv_date ) 类型不一致，不能对date求平均值FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id)FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date)) 缺少group by
FROM invoice;D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt)FROM invoice;Answer: BD 答案解析：参考：http://blog.csdn.net/rlhua/article/details/12868639A，不能对date类型求平均数B,MAX,MIN可以对数值和date求最大值和最小值C,组合函数，需要分组，错误D,AVG对数值求平均数，正确。108. Which two statements are true regarding the COUNT function? (Choose two.)A. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.B. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value inany of the columns.C. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULLvalue in the CUST_ID column.D. COUNT(DISTINCT inv_amt)returns the number of rows excluding排除在外 rows containing duplicates andNULL values in the INV_AMT column.E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have aWHERE clause.Answer: BD 答案解析：参考：http://blog.csdn.net/rlhua/article/details/12868639COUNT函数有以下三种格式：• COUNT(*)• COUNT(expr)• COUNT(DISTINCT expr)COUNT(*)用于返回表中符合SELECT语句标准的行数，包括重复行和在任何列中含有空值的行。如果SELECT语句中包含WHERE子句，则COUNT(*)会返回符合WHERE子句中条件的行数。相反，COUNT(expr)返回由expr标识的列中非空值的数量。COUNT(DISTINCT expr)返回由expr标识的列中不同非空值的数量。109. Examine the structure of the MARKS table:name Null TypeSTUDENT_ID NOT NULL VARCHAR2(4)STUDENT_NAME VARCHAR2(25)SUBJECT1 NUMBER(3)SUBJECT2 NUMBER(3)SUBJECT3 NUMBER(3)Which two statements would execute successfully? (Choose two.)A. SELECT student_name,subject1 FROM marks WHERE subject1 > AVG(subject1); where不能跟表达式B. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%'; 少了group byC. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marks
WHERE subject1 > subject2;Answer: CD
A答案：AGV分组函数不能用在where子句里B答案：缺少group by
C答案：因为测试数据里没有student_name没有为空的，所有无返回值
110. View the Exhibit and examine the structure of the CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows the average credit limit forcustomers in WASHINGTON and NEW YORK.
Which SQL statement would produce the required result? A. SELECT cust_city, AVG(cust_credit_limit)FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')GROUP BY cust_credit_limit, cust_city;B. SELECT cust_city, AVG(cust_credit_limit)FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')GROUP BY cust_city,cust_credit_limit;C. SELECT cust_city, AVG(cust_credit_limit)FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')GROUP BY cust_city; 按照城市来取平均值D. SELECT cust_city, AVG(NVL(cust_credit_limit,0))FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');Answer: C
答案解析：
A答案：此处是按照cust_credit_limit, cust_city;这两个一起分组的B答案，与A答案一样，按照cust_credit_limit, cust_city;这两个一起分组的。C答案，按照cust_city来分组，与题意复合D答案：缺少group by
111. View the Exhibit and examine the structure of the CUSTOMERS table.
Which statement would display the highest credit limit available in each income level in each city in theCUSTOMERS table?
A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit )FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit;B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customers
GROUP BY cust_city, cust_income_level; 分组先按照city再按照income levelC. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ; 顺序错了D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);
Answer: B答案解析：
按照题意，先按照每个城市后按照每个城市中的收入水平来分组查看最高的credit limit故在group by时应为 group by cust_city, cust_income_level;A答案，没有涉及到cust_credit_limit的分组。
B答案与题意相符，正确，数据太多，取前5行。
C答案，没有涉及cust_credit_limit分组，且cust_income_level, cust_city 顺序错了，应为group by cust_city, cust_income_level;
D答案，语法错误
112. View the Exhibit and examine the structure of the PROMOTIONS table.Evaluate the following SQL statement:
SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_OverheadFROM promotions
WHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST')GROUP BY Avg_CostORDER BY Avg_Overhead;
The above query generates an error on execution.Which clause in the above SQL statement causes the error?
A. WHEREB. SELECTC. GROUP BYD. ORDER BYAnswer: C
答案解析：
group by不能跟列别名
113. Examine the structure of the ORDERS table: Name Null Type
ORDER_ID NOT NULL NUMBER(12) ORDER_DATE NOT NULL TIMESTAMP(6) CUSTOMER_ID NOT NULL NUMBER(6) ORDER_STATUS NUMBER(2) ORDER_TOTAL NUMBER(8,2)
You want to find the total value of all the orders for each year and issue the following command:SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)FROM orders
GROUP BY TO_CHAR(order_date,'yyyy');Which statement is true regarding the outcome?A. It executes successfully and gives the correct output.B. It gives an error because the TO_CHAR function is not valid.C. It executes successfully but does not give the correct output.
D. It gives an error because the data type conversion in the SELECT list does not match the data typeconversion in the GROUP BY clause. 格式不一致Answer: D
答案解析：
报错，因为SELECT列表中的数据类型转换与GROUP BY子句中的数据类型转换不匹配。rr和yyyy的格式不一致，改为一致后可以得出数据。
114. View the Exhibit and examine the structure of the SALES table.
The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times.SQL> SELECT prod_idFROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10GROUP BY prod_idHAVING COUNT(*)>10;
Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result.
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHEREclause.
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead ofCOUNT(*).
Answer: C答案解析：
因为COUNT(*)只能用在HAVING子句中，不能在WHERE子句中 where子句不能带组函数
115. View the Exhibit and examine the structure of the CUSTOMERS table.Evaluate the following SQL statement:SQL> SELECT cust_city, COUNT(cust_last_name)FROM customers
WHERE cust_credit_limit > 1000GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query?
A. It executes successfully.
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECTstatement.
D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on thesame column.Answer: A
116. Examine the data in the ORD_ITEMS table:ORD_NO ITEM_NO QTY1 111 101 222 201 333 302 333 302 444 403 111 40
You want to find out if there is any item in the table for which the average maximum quantity is more than50.
You issue the following query: SQL> SELECT AVG(MAX(qty))FROM ord_itemsGROUP BY item_noHAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?A. It executes successfully and gives the correct output.B. It gives an error because the HAVING clause is not valid.
C. It executes successfully but does not give the correct output.D. It gives an error because the GROUP BY expression is not valid. Answer: BHAVING子句后面不能有好几个组函数嵌套，只能使用一个组函数。117. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?(Choose all that apply.)A. The HAVING clause can be used with aggregate functions in subqueries. B. The WHERE clause can be used to exclude rows after dividing them into groups.C. The WHERE clause can be used to exclude rows before dividing them into groups. D. The aggregate functions and columns used in the HAVING clause must be specified in the SELECT listof the query. E. The WHERE and HAVING clauses can be used in the same statement only if they are applied todifferent columns in the table. Answer: AC答案解析：参考：http://blog.csdn.net/rlhua/article/details/12871951A,having可以用在聚合函数的子句里。正确B,分组之后不能使用where来，因为where只能行，不能组，错误C,分组之前可以使用where来所要输出的行，正确D,在having子句的聚合函数或者字段不一定要在select列表中，错误。E,在同一句子中，where和having可以都使用，可以应用在不同的字段，也可以相同的字段。118. View the Exhibit and examine the structure of the PROMOTIONS table. Examine the following two SQL statements: Statement 1 SQL>SELECT promo_category,SUM(promo_cost) FROM promotions WHERE promo_end_date-promo_begin_date > 30 GROUP BY promo_category; Statement 2 SQL>SELECT promo_category,sum(promo_cost) FROM promotions GROUP BY promo_category HAVING MIN(promo_end_date-promo_begin_date)>30; Which statement is true regarding the above two SQL statements? A. statement 1 gives an error, statement 2 executes successfully B. statement 2 gives an error, statement 1 executes successfully C. statement 1 and statement 2 execute successfully and give the same output D. statement 1 and statement 2 execute successfully and give a different output 119. Examine the data in the ORD_ITEMS table:ORD_NO ITEM_NO QTY1 111 101 222 201 333 302 333 302 444 403 111 40
Evaluate the following query: SQL>SELECT item_no, AVG(qty)FROM ord_items
HAVING AVG(qty) > MIN(qty) * 2GROUP BY item_no;
Which statement is true regarding the outcome of the above query?
A. It gives an error because the HAVING clause should be specified after the GROUP BY clause.B. It gives an error because all the aggregate functions used in the HAVING clause must be specified inthe SELECT list.
C. It displays the item nos with their average quantity where the average quantity is more than double theminimum quantity of that item in the table.
D. It displays the item nos with their average quantity where the average quantity is more than double theoverall minimum quantity of all the items in the table.Answer: C
120. View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.
You issue the following query:
SQL>SELECT p.prod_id,prod_name,prod_list_price, quantity_sold,cust_last_name
FROM products p NATURAL JOIN sales s NATURAL JOIN customers c WHERE prod_id =148;
Which statement is true regarding the outcome of this query? A. It executes successfully.
B. It produces an error because the NATURAL join can be used only with two tables.
C. It produces an error because a column used in the NATURAL join cannot have a qualifier.D. It produces an error because all columns used in the NATURAL join should have a qualifier. Answer: C
sh@TEST0910> SELECT p.prod_id,prod_name,prod_list_price,quantity_sold,cust_last_name 2 FROM products p NATURAL JOIN sales s NATURAL JOIN customers c 3 WHERE prod_id =148 and rownum<6;
SELECT p.prod_id,prod_name,prod_list_price,quantity_sold,cust_last_name *
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
列用于自然连接不能有限定符。去掉限定符后：
sh@TEST0910> SELECT prod_id,prod_name,prod_list_price,quantity_sold,cust_last_name 2 FROM products p NATURAL JOIN sales s NATURAL JOIN customers c 3 WHERE prod_id =148 and rownum<6;
PROD_ID PROD_NAME PROD_LIST_PRICE QUANTITY_SOLD CUST_LAST_NAME---------- ------------------------------ --------------- ------------- ------------------------------ 148 Xtend Memory 20.99 1 Llyles 148 Xtend Memory 20.99 1 Lake 148 Xtend Memory 20.99 1 Koch 148 Xtend Memory 20.99 1 Skillman 148 Xtend Memory 20.99 1 Speer
列不用于自然连接，可以使用限定符。
自然连接:自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。 来看一下自然连接的例子。Select emp.ename,dept.dnameFrom emp natural join dept;这里我们并没有指定连接的条件，实际上oracle为我们自作主张的将，emp中的deptno和dept中的deptno做了连接。也就是实际上相当于Select emp.ename,dept.dnameFrom emp join dept on emp.deptno = dept.deptno;因为这两张表的这两个字段deptno的类型个名称完全相同。所以使用natural join时被自然的连接在一起了。另外：1.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为自然连接的条件。2.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。3.由于oracle中可以进行这种非常简单的natural join，我们在设计表时，应该尽量在不同表中具有相同含义的字段使用相同的名字和数据类型。以方便以后使用natural join。121. Which two statements are true regarding the USING clause in table joins? (Choose two .)A. It can be used to join a maximum of three tables.B. It can be used to restrict the number of columns used in a NATURAL join.C. It can be used to access data from tables through equijoins as well as nonequijoins.D. It can be used to join tables that have columns with the same name and compatible data types.Answer: BD答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591A,最多可以连接三个表，错误，可以连接三个以上的表B,自然连接的等值连接数，正确，因为当有多个列相匹配时，使用USING子句可仅与一列相匹配，NATURAL JOIN和USING语句是互相排斥的。C,错误，USING只能用在等值连接，不能用在非等值连接。D,列的名称相同和数据类型匹配，则可以连接表，正确。122. View the Exhibit for the structure of the STUDENT and FACULTY tables.You need to display the faculty name followed by the number of students handled by the faculty at thebase location.Examine the following two SQL statements:Statement 1SQL>SELECT faculty_name,COUNT(student_id) FROM student JOIN faculty USING (faculty_id, location_id) GROUP BY faculty_name;Statement 2SQL>SELECT faculty_name,COUNT(student_id) FROM student NATURAL JOIN faculty GROUP BY faculty_name;Which statement is true regarding the outcome? A. Only s tatement 1 executes successfully and gives the required result.B. Only statement 2 executes successfully and gives the required result.C. Both statements 1 and 2 execute successfully and give different results.D. Both statements 1 and 2 execute successfully and give the same required result.Answer: D答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591总结，当且仅当student表中的faculty_id列能隐式转换为数字类型时，这两个查询语句才可以查询成功。因为NATURAL JOIN是将两个表中具有相同名称的所有列都连接起来，即FACULTY_ID和LOCATION_ID,而USING子句也使用了这两个字段来连接，故结果相同。123. View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERStables.You need to generate a report that gives details of the customer's last name, name of the product, andthe quantity sold for all customers in ' Tokyo' .Which two queries give the required result? (Choose two.)A. SELECT c.cust_last_name,p.prod_name, s.quantity_soldFROM sales s JOIN products pUSING(prod_id)JOIN customers cUSING(cust_id)WHERE c.cust_city='Tokyo';B. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id)ON(s.cust_id=c.cust_id)WHERE c.cust_city='Tokyo';C. SELECT c.cust_last_name, p.prod_name, s.quantity_soldFROM products p JOIN sales sON(p.prod_id=s.prod_id)JOIN customers cON(s.cust_id=c.cust_id)AND c.cust_city='Tokyo';D. SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM products p JOIN sales sUSING(prod_id)JOIN customers cUSING(cust_id)WHERE c.cust_city='Tokyo';Answer: AC答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591B语法错误D中p.prod_id不应该跟限定的表别名。不要对USING子句中使用的列加以限定。USING子句中引用的那些列不能在SQL 语句的任何位置使用限定词（表名或别名）。如果在SQL 语句的另一个位置使用了同一列，则不要对其设置别名。124. View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.You need to generate a report showing the promo name along with the customer name for all productsthat were sold during their promo campaign and before 30th October 2007.You issue the following query:SQL> SELECT promo_name,cust_name FROM promotions p JOIN sales s ON(time_id BETWEEN promo_begin_date AND promo_end_date) JOIN customer c ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';Which statement is true regarding the above query? A. It executes successfully and gives the required result.B. It executes successfully but does not give the required result.C. It produces an error because the join order of the tables is incorrect.D. It produces an error because equijoin and nonequijoin conditions cannot be used in the same SELECTstatement.Answer: B答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591题意要求：需要一个报表，显示所有产品的promo name和customer name，销售的产品是在promo campaign之内，并且在2007年10月30日以前 缺少条件， promotions p JOIN sales s 之间应该有个PROMO_ID的等值连接，才能得出正确结果。125. Examine the structure of the CUSTOMERS table:name Null TypeCUSTNO NOT NULL NUMBER(3)CUSTNAME NOT NULL VARCHAR2(25)CUSTADDRESS VARCHAR2(35)CUST_CREDIT_LIMIT NUMBER(5)CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have beenentered more than once using different CUSTNO, by listing all the duplicate names.Which two methods can you use to get the required result? (Choose two.)A. self-joinB. subqueryC. full outer-join with self-joinD. left outer-join with self-joinE. right outer-join with self-joinAnswer: AB答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591题意：找到是否存在客户信息使用不同的CUSTNO录入了多次，显示所有重复的客户名称自连接即是查询两次自身的表，首先根据客户信息来查看CUSTNO大于1的记录，然后再根据CUSTNO来显示客户名称。子查询也是一样。126. View the Exhibit and examine the data in the PROJ_TASK_DETAILS table.The PROJ_TASK_DETAILS table stores information about tasks involved in a project and the relationbetween them.The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on thecompletion of any other tasks.You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, andthe name of the employee in charge of the task it depends on.Which query would give the required result? A. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details dON (p.based_on = d.task_id);C. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details dON (p.based_on = d.task_id);D. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);Answer: B答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591显示所有任务的ID，依赖对应任务的ID，和它依赖任务负责的员工姓名A为只显示有依赖任务的task_idB是LEFT OUTER JOIN也就是左连表的所有task_id全部显示出来，如果右边表没有匹配的信息，则显示空127. Examine the data in the CUSTOMERS table:CUSTNO CUSTNAME CITY1 KING SEATTLE2 GREEN BOSTON3 KOCHAR SEATTLE4 SMITH NEW YORKYou want to list all cities that have more than one customer along with the customer details. Evaluate the following query:SQL>SELECT c1.custname, c1.cityFROM Customers c1 __________________ Customers c2ON (c1.city=c2.city AND c1.custname<>c2.custname);Which two JOIN options can be used in the blank in the above query to give the correct output? (Choosetwo.)A. JOINB. NATURAL JOIN C. LEFT OUTER JOIN D. FULL OUTER JOIN E. RIGHT OUTER JOINAnswer: AE 答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591128. View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIEStables.You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.Which FROM clause gives the required result?A. FROM sales JOIN customers USING (cust_id)FULL OUTER JOIN countries USING (country_id);B. FROM sales JOIN customers USING (cust_id)RIGHT OUTER JOIN countries USING (country_id);C. FROM customers LEFT OUTER JOIN sales USING (cust_id)RIGHT OUTER JOIN countries USING (country_id);D. FROM customers LEFT OUTER JOIN sales USING (cust_id)LEFT OUTER JOIN countries USING (country_id);Answer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591显示所有客户的所有country names，对应的customers（如果有）和sales details（如果有）即是左外连接customers ，显示所有客户，右外连接countries ，显示所有 country names。129. View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.Evaluate the following SQL statement:SQL>SELECT p.promo_id, p.promo_name, s.prod_idFROM sales s RIGHT OUTER JOIN promotions pON (s.promo_id = p.promo_id);Which statement is true regarding the output of the above query?A. It gives the details of promos for which there have been sales.B. It gives the details of promos for which there have been no sales.C. It gives details of all promos irrespectiveof whether they have resulted in a sale or not.D. It gives details of product ID s that have been sold irrespective of whether they had a promo or not.Answer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591此处是右连接promotions ，即显示所有promotions 表中的信息，不管sales 表中promo_id 是否有。130. View the Exhibit and examine the data in the EMPLOYEES table:You want to display all the employee names and their corresponding manager names.Evaluate the following query:SQL> SELECT e.employee_name \"EMP NAME\FROM employees e ______________ employees mON e.manager_id = m.employee_id;Which JOIN option can be used in the blank in the above query to get the required output? A. o nly inner JOINB. only FULL OUTER JOINC. only LEFT OUTER JOIND. only RIGHT OUTER JOINAnswer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591显示所有employee names和对应的manager names此处e代表员工表，而m代表管理人员表，所有只有左连接才能显示 all the employee names131. View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMPtables.In PRODUCT table, PDTNO is the primary key.In COMPONENT table, COMPNO is the primary key.In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencingPDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENTtable.You want to generate a report listing the product names and their corresponding component names, if thecomponent names and product names exist.Evaluate the following query:SQL>SELECT pdtno,pdtname, compno,compnameFROM product _____________ pdt_compUSING (pdtno) ___________ component USING(compno)WHERE compname IS NOT NULL;Which combination of joins used in the blanks in the above query gives the correct output? A. JOIN; JOINB. FULL OUTER JOIN; FULL OUTER JOINC. RIGHT OUTER JOIN; LEFT OUTER JOIN 外键的关系D. LEFT OUTER JOIN; RIGHT OUTER JOINAnswer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591listing the product names and their corresponding component names, if thecomponent names and product names exist报表显示的是如果component names和product names存在，则显示product names和对应的component names即显示PDT_COMP表中所有的值132. View the Exhibit and examine the structure of the SALES and PRODUCTS tables.In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table,You want to list each product ID and the number of times it has been sold.Evaluate the following query:SQL>SELECT p.prod_id, COUNT(s.prod_id) FROM products p _____________ sales s ON p.prod_id = s.prod_id GROUP BY p.prod_id;Which two JOIN options can be used in the blank in the above query to get the required output? (Choosetwo.) A. JOIN B. FULL OUTER JOINC. LEFT OUTER JOIND. RIGHT OUTER JOINAnswer: BC答案解析：参考：http://blog.csdn.net/rlhua/article/details/12877591列出每一个产品ID和产品已销售的次数。列出每一个产品ID，所以C正确，由于s表的prod_id是products表的外键，所以是products的子集，所以B也正确。133. Which two statements are true regarding subqueries? (Choose two.)A. A subquery can retrieve zero or more rows.B. Only two subqueries can be placed at one level.C. A subquery can be used only in SQL query statements. D. A subquery can appear on either side of a comparison operatorE. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement. Answer: AD答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585A,子查询可以返回一行或者多行，也可以返回0行。B,只能有两个子查询被放在同一层，这句话不是很懂。C,子查询不仅可以用在查询语句，也可以用在插入和更新语句。D,子查询可以出现在任何一方的一个比较运算符。E,WHERE子句中的嵌套子查询最多能嵌套255层134. Where can subqueries be used? (Choose all that apply.)A. field names in the SELECT statement 标量子查询B. the FROM clause in the SELECT statement 内联视图C. the HAVING clause in the SELECT statement select的having子句D. the GROUP BY clause in the SELECT statementE. the WHERE clause in only the SELECT statementF. the WHERE clause in SELECT as well as all DML statements select的where子句与所有的DML操作。Answer: ABCF答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585子查询是一个SELECT语句，它嵌入到另一个SELECT语句的子句中。通过使用子查询，可以用简单的语句构建功能强大的语句。当需要从表中选择行，而选择条件却取决于该表自身中的数据时，子查询非常有用。可以在许多SQL 子句中使用子查询，其中包括以下子句：• WHERE子句• HAVING子句• FROM子句在该语法中：operator包括比较条件，例如>、=或IN注：比较条件分为以下两类：单行运算符（>、=、>=、<、<>、<=）和多行运算符（IN、ANY、ALL、EXISTS）。子查询通常被称为嵌套SELECT语句、子SELECT语句或内部SELECT语句。通常先执行子查询，然后使用其输出来完善主查询（即外部查询）的查询条件。A,SELECT语句中的字段名称B,SELECT语句的FROM子句中C,SELECT语句的HAVING子句中D,SELECT语句的GROUP BY子句中，不行E,只能用于WHERE子句,错误，还可以用在HAVING和FROM子句。F,SELECT与DML语句的WHERE子句中135. Which three statements are true regarding subqueries? (Choose three.)A. Subqueries can contain GROUP BY and ORDER BY clauses. 对，可以包含group by 和order byB. Main query and subquery can get data from different tables. 对，可以分别拿不同表的数据C. Main query and subquery must get data from the same tables. 错。看B选项D. Subqueries can contain ORDER BY but not the GROUP BY clause. 错，看A选项E. Only one column or expression can be compared between the main query and subquery.F. Multiple columns or expressions can be compared between the main query and subquery.Answer: ABF答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585A,子查询可以包含GROUP BY和ORDER BY子句B,主查询和子查询能从不同的表里获取数据C,主查询和子查询必须从同一个表获取数据,错误，可以从不同表获取数据D,子查询能包含ORDER BY 子句，但是不能包含GROUP BY子句,错误，可以包含GROUP BY子句E,主查询与子查询之间只能比较一个列或一个表达式，错误，可以比较多行，使用any，all等F,主查询与子查询之间可以比较多个列或多个表达式.136. View the Exhibit and examine the structure of the PRODUCTS table.Which two tasks would require subqueries? (Choose two.) A. Display the minimum list price for each product status.B. Display all suppliers whose list price is less than 1000. C. Display the number of products whose list price is more than the average list price. D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.E. Display all products whose minimum list price is more than the average list price of products and havethe status 'orderable'.Answer: CE答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585题意要求两个需要使用子查询的。A,显示每个product status的minimum list price,可以使用group by product status来找出 minimum list priceB,显示所有价格低于1000的供应商，找出供应商的价格，选择价格低于1000的，不需要使用子查询C,显示产品价格大于平均价格的产品数量，首先需要计算出平均价格，然后在找出产品价格大于平均价格的数量，需要使用子查询。D,显示102供应商供应的并且产品状态为'obsolete'的全部产品数量，直接查询，不需要使用子查询E,显示产品的最低价格大于平均价格并且产品状态是'orderable'的所有产品,首先计算出平均价格，然后使用min（）函数找出最低价格来比较品均价格以及状态是'orderable'的所有产品，需要使用到子查询。137. View the Exhibits and examine PRODUCTS and SALES tables.You issue the following query to display product name and the number of times the product has beensold: SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i RIGHT OUTER JOIN products p ON i.prod_id = p.prod_id;What happens when the above statement is executed?A. The statement executes successfully and produces the required output.B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot beused together.D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in theFROM clause.Answer: A答案解析：通过子查询，查询出产品卖出去的次数。在通过右连接产品表，显示出所有产品。138. Which statement is true regarding subqueries?A. The LIKE operator cannot be used with single- row subqueries. B. The NOT IN operator is equivalent to IS NULL with single- row subqueries. C. =ANY and =ALL operators have the same functionality in multiple- row subqueries. D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.Answer: D答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585A,LIKE操作符不能用在单行子查询中，错误，LIKE可以用于单行函数B，NOT IN操作符相当于单选子查询的IS NULL，次两个不等价，NOT IN运算符等同于<> ALL，C，多行子查询中的=ANY and =ALL操作符具有相同的功能，=ANY是等于任意一个，=ALL是等于所有D，NOT操作可以用在多行子查询中的IN,ANY和ALL操作符上。其实就是把NOT放到整个表达式之前：WHERE NOT col IN (SELECT ...) （也可以是WHERE col not IN (SELECT ...)）WHERE NOT col = ANY (SELECT ...) （大于、小于都可以）WHERE NOT col = ALL (SELECT ...) （大于、小于都可以）139. Which three statements are true about multiple-row subqueries? (Choose three.)A. They can contain a subquery within a subquery. B. They can return multiple columns as well as rows. C. They cannot contain a subquery within a subquery. D. They can return only one column but multiple rows. E. They can contain group functions and GROUP BY and HAVING clauses. F. They can contain group functions and the GROUP BY clause, but not the HAVING clause. Answer: ABE答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585A,子查询中能包含子查询,可以嵌套，但最多255层。B，能返回多列和多行，正确。C，子查询中不能包含子查询，错误，子查询可以嵌套D，只能返回一列多行，错误，可以返回多行多列E，可以包含组函数和GROUP BY,HAVING子句，正确F，可以包含组函数和GROUP BY，但不能包含HAVING子句，错误，是可以包含HAVING子句的。140. Examine the structure of the PRODUCTS table:name Null TypePROD_ID NOT NULL NUMBER(4)PROD_NAME VARCHAR2(20)PROD_STATUS VARCHAR2(6)QTY_IN_HAND NUMBER(8,2)UNIT_PRICE NUMBER(10,2)You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND.Which SQL statement gives the required output?A. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);B. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);C. SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);D. SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products) GROUP BY prod_name;Answer: A答案解析：BC都犯了一个错误，都会返回一个错误single-row subquery returns more than one row。即是单行子查询返回了多行。该WHERE子句包含一个等于(=) 运算符，该运算符是一个只需要一个值的单行比较运算符。=运算符无法接受子查询中的多个值，因此产生了错误。D的查询条件不满足题意。141. View the Exhibit and examine the structure of CUSTOMERS and GRADES tables.You need to display names and grades of customers who have the highest credit limit.Which two SQL statements would accomplish the task? (Choose two.) A. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;B. SELECT custname, gradeFROM customers, gradesWHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval AND cust_credit_limit BETWEEN startval AND endval;C. SELECT custname, gradeFROM customers, gradesWHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers)AND cust_credit_limit BETWEEN startval AND endval;D. SELECT custname, gradeFROM customers , gradesWHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers)AND MAX(cust_credit_limit) BETWEEN startval AND endval;Answer: BC答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585题意要求显示有最高credit limit的用户的名称和等级。
A，缺少cust_credit_limit BETWEEN startval AND endval这个条件。BC，使用的非等值连接
D有语法错误，WHERE子句里不能使用组函数
142. View the Exhibit and examine the structure of the PRODUCTS table.Evaluate the following query:SQL> SELECT prod_nameFROM products
WHERE prod_id IN (SELECT prod_id FROM products WHERE prod_list_price =
(SELECT MAX(prod_list_price)FROM products WHERE prod_list_price < (SELECT MAX(prod_list_price)FROM products)));
What would be the outcome of executing the above SQL statement?
A. It produces an error.
B. It shows the names of all products in the table.
C. It shows the names of products whose list price is the second highest in the table.D. It shows the names of all products whose list price is less than the maximum list price.Answer: C
答案解析：
1、首先来运行一下上面的sql语句，是可以出结果的。2、拆分上面sql语句，首先运行子句查询出价格最高的产品的价格3、低于价格最高的，即价格次高的产品。按题意，故选C.
143. View the Exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started afterthe last promo in the 'INTERNET' category.Which query would give you the required output?
A. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET';B. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET');C. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');D. SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET');Answer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12879585显示在最后一个promo之后并且category为INTERNET开始的所有promos的promo name和start date。A,promo_category = 'INTERNET'应该再子查询里面。B,条件不满足，条件要求找出最后一个promo之后并且category为INTERNET开始的所有promos的promo name和start date。C,正确，大于ALL，即是大于最大值。D,错误，大于ANY,即是大于最小值。144. View the Exhibit and examine the structure of the PRODUCTS table.You want to display the category with the maximum number of items.You issue the following query:SQL>SELECT COUNT(*),prod_category_idFROM productsGROUP BY prod_category_idHAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products); 没有group byWhat is the outcome?
A. It executes successfully and gives the correct output.B. It executes successfully but does not give the correct output.
C. It generates an error because the subquery does not have a GROUP BY clause.D. It generates an error because = is not valid and should be replaced by the IN operator.Answer: C
答案解析：
1、首先来运行前半句，可见语法是正确的。
sh@TESTDB> SELECT COUNT(*),prod_category_id 2 FROM products
3 GROUP BY prod_category_id 4 HAVING COUNT(*)=2;
COUNT(*) PROD_CATEGORY_ID---------- ---------------- 2 202
2、再来运行子查询，报错，缺少group by函数。故选C，子查询缺少group by函数。
sh@TESTDB> SELECT MAX(COUNT(*)) FROM products;SELECT MAX(COUNT(*)) FROM products *
ERROR at line 1:
ORA-00978: nested group function without GROUP BY
145. View the Exhibit and examine the structure of the CUSTOMERS table.
You issue the following SQL statement on the CUSTOMERS table to display the customers who are in thesame country as customers with the last name 'KING' and whose credit limit is less than the maximum 小于最大值credit limit in countries that have customers with the last name 'KING':SQL> SELECT cust_id,cust_last_nameFROM customers
WHERE country_id IN(SELECT country_id FROM customers WHERE cust_last_name ='King')
AND cust_credit_limit < (SELECT MAX(cust_credit_limit) FROM customers
WHERE country_id IN(SELECT country_id FROM customers WHERE cust_last_name='King'));
Which statement is true regarding the outcome of the above query?
A. It executes and shows the required result.
B. It produces an error and the < operator should be replaced by < ALL to get the required output.C. It produces an error and the < operator should be replaced by < ANY to get the required output.D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the mainquery to get the required output.Answer: A
146. Evaluate the following SQL statement:SQL> SELECT cust_id, cust_last_nameFROM customers
WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery isNULL?
A. It produces an error.B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.
Answer: C答案解析：
这道题的意思是说：如果子查询出现的值是null会怎样？1、首先运行子查询里面的sql语句，结果为nullsh@TEST0910> select cust_credit_limit 2 FROM customers
3 WHERE cust_city ='Singapore';no rows selected
2、如果子查询为null，那么结果也为null。
147. View the Exhibit and examine the structure of the PROMOTIONS table.Evaluate the following SQL statement: SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost) FROM promotions
WHERE promo_category='TV') then 'HIGH' else 'LOW'
END COST_REMARKFROM promotions;
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table. 分类显示所有值B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'.D. It produces an error because subqueries cannot be used with the CASE expression.Answer: A
148. View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the averagelist price is less than half the maximum in each category.Which query would give the correct output?
A. SELECT prod_category,avg(prod_list_price)FROM products GROUP BY prod_category
HAVING avg(prod_list_price) < ALL 小于所有，小于最小值(SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
B. SELECT prod_category,avg(prod_list_price)FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2FROM products
GROUP BY prod_category);
C. SELECT prod_category,avg(prod_list_price) FROM products
HAVING avg(prod_list_price) < ALL
(SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);D. SELECT prod_category,avg(prod_list_price)FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);Answer: A 答案解析：1、首先查询子查询中出每个PROD_CATEGORY的prod_list_price的最大值的1/2的值。2、找出prod_list_price的平均值3、平均值小于1/2最大值的所有值 ，即小于最小值B与题意不符，> ANY大于最小值C语法错误，缺少group by149. View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.Evaluate the following SQL statement:SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date- promo_begin_date)));What would be the outcome of the above SQL statement?A. It displays prod IDs in the promo with the lowest cost.B. It displays prod IDs in the promos with the lowest cost in the same time interval.C. It displays prod IDs in the promos with the highest cost in the same time interval.D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.Answer: DALL参考：http://blog.csdn.net/rlhua/article/details/120033ANY参考：http://blog.csdn.net/rlhua/article/details/12007309150. View the Exhibit and examine the data in the PROMOTIONS table.You need to display all promo categories that do not have 'discount' in their subcategory.Which two SQL statements give the required result? (Choose two.) A. SELECT promo_categoryFROM promotionsMINUSSELECT promo_categoryFROM promotionsWHERE promo_subcategory = 'discount';
所有promo_category 减去包含promo_subcategory = 'discount'的，符合题意。
B. SELECT promo_categoryFROM promotionsINTERSECT 交集SELECT promo_categoryFROM promotions
WHERE promo_subcategory = 'discount';
所有promo_category 中包含promo_subcategory = 'discount'的，与题意相反。
C. SELECT promo_categoryFROM promotionsMINUS
SELECT promo_categoryFROM promotions
WHERE promo_subcategory <> 'discount';
所有promo_category 中减去不包含promo_subcategory = 'discount'的，与题意相反。
D. SELECT promo_categoryFROM promotionsINTERSECT
SELECT promo_categoryFROM promotions
WHERE promo_subcategory <> 'discount';
所有promo_category 交集不包含promo_subcategory = 'discount'的，符合题意。
Answer: AD
答案解析：
本题意是让找出不包含promo_subcategory = 'discount'的产品。
MINUS 返回第一个查询有，第二个查询无的
which returns only unique rows returned by the first query but not by the second
INTERSECT 返回共同有的
which returns only those unique rows returned by both queries
151. View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.The CUSTOMERS table contains the current location of all currently active customers. TheCUST_HISTORY table stores historical details relating to any changes in the location of all current as wellas previous customers who are no longer active with the company.You need to find those customers who have never changed their address.Which SET operator would you use to get the required output?
A. MINUSB. UNIONC. INTERSECTD. UNION ALLAnswer: A 答案解析：本题意是让找出没有改变过地址的客户。即从CUSTOMERS 减去改变过地址的CUST_HISTORY 的客户即可。 UNION Example The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match data type (using the TO_CHAR function) when columns do not exist in one or the other table所有的行都包括UNION ALL Example The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows去除重复的行INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queries返回共同有的唯一的行MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second返回第一个有，第二个表无的行官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#i2054381152. Which statement is true regarding the UNION operator?A. By default, the output is not sorted. B. NULL values are not ignored during duplicate checking.C. Names of all columns must be identical across all SELECT statements. D. The number of columns selected in all SELECT statements need not be the same. Answer: B答案解析：参考：http://blog.csdn.net/rlhua/article/details/12883007UNION运算符用于返回由任一查询选定的所有行。使用UNION运算符可以返回多个表中的所有行，但不包括重复行。准则• 所选列的数量必须相同。• 所选列的数据类型必须属于相同的数据类型组（如数字或字符）。• 列名不必相同。• UNION将对所有选定的列执行操作。• 在重复项检查过程中不会忽略NULL值。• 默认情况下，输出按SELECT子句中列的升序进行排序。A,默认情况下，结果不排序，错误，在默认的情况下，输出按SELECT子句中列的升序进行排序。B，在重复项检查过程中不会忽略NULL值，正确。C，列名在所有SELECT语句中必须是相同的，错误，列名不必相同。D，在所有SELECT语句中选择的列的数量不需要相同，错误，所选列的数量必须相同。153. View the Exhibits and examine the structures of the PRODUCTS and SALES tables.Which two SQL statements would give the same output? (Choose two.)A. SELECT prod_id FROM productsINTERSECTSELECT prod_id FROM sales;B. SELECT prod_id FROM productsMINUSSELECT prod_id FROM sales;C. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id=s.prod_id;D. SELECT DISTINCT p.prod_idFROM products p JOIN sales sON p.prod_id <> s.prod_id;Answer: AC答案解析：A：所有的产品和已卖出的产品相交，即返回已经卖出去的产品的id。B:所有的产品和已卖出去的产品相减，即返回没有卖出去的产品C:返回已卖出去的产品的idD答案：会把所有的都显示出来返回共同有的唯一的行INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queriesMINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second返回第一个有，第二个表无的行官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#i2054381154. View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.Evaluate the following SQL statement:SQL>SELECT prod_id FROM productsINTERSECTSELECT prod_id FROM salesMINUSSELECT prod_id FROM costs;Which statement is true regarding the above compound query?A. It produces an error.B. It shows products that were sold and have a cost recorded.C. It shows products that were sold but have no cost recorded.D. It shows products that have a cost recorded irrespective of sales.Answer: C答案解析：按照题意，查询出在sales表里的已经卖出去的但是不在costs表里的产品id。 INTERSECT Example The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queries返回共同有的唯一的行MINUS Example The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second返回第一个有，第二个表无的行官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#i2054381155. Evaluate the following SQL statement:SQL> SELECT promo_id, promo_categoryFROM promotionsWHERE promo_category = 'Internet' ORDER BY 2 DESCUNIONSELECT promo_id, promo_categoryFROM promotionsWHERE promo_category = 'TV'UNIONSELECT promo_id, promo_categoryFROM promotionsWHERE promo_category ='Radio';Which statement is true regarding the outcome of the above query?A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.B. It produces an error because positional notation cannot be used in the ORDER BY clause with SEToperators.C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of thecompound statement.D. It produces an error because the ORDER BY clause should appear only at the end of a compoundquery-that is, with the last SELECT statement. ORDER BY必须要在句子最后Answer: D 答案解析：ORDER BY 子句应该放在复合sql查询语句的最后。156. Evaluate the following SQL statement:SQL> SELECT cust_id, cust_last_name \"Last Name\"FROM customersWHERE country_id = 10UNIONSELECT cust_id CUST_NO, cust_last_nameFROM customersWHERE country_id = 30;Which ORDER BY clauses are valid for the above query? (Choose all that apply.)A. ORDER BY 2,1B. ORDER BY CUST_NOC. ORDER BY 2,cust_idD. ORDER BY \"CUST_NO\"E. ORDER BY \"Last Name\"Answer: ACE答案解析：ORDER BY 后面可以跟数字，可以完全一摸一样的列名,经过union后，字段名根据第一个查询语句显示，变为cust_id和Last Name。B答案：报错，没有CUST_NO字段。157. View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.Evaluate the following SQL command:SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limitFROM orders o JOIN customers cUSING (customer_id)WHERE o.order_total > c.credit_limitFOR UPDATEORDER BY o.order_id;Which two statements are true regarding the outcome of the above query? (Choose two.) A. It locks all the rows that satisfy the condition in the statement. B. It locks only the columns that satisfy the condition in both the tables. C. The locks are released only when a COMMIT or ROLLBACK is issued. D. The locks are released after a DML statement is executed on the locked rows. Answer: AC答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143For update of 实际列名（可以加of，也可以省略of，如果加上of就是锁定指定的表，而列并不重要，只要是该表中的实际列名，不可以使用列别名，如果不加of，则锁定满足条件的所有行）A,锁定语句中满足条件的所有行,正确B,只锁定两个表中满足条件的列。错误，是满足条件的所有行，不是列。C，只有执行COMMIT或ROLLBACK后，锁才会释放，正确。D，锁定的行上执行DML语句后，锁被释放，错误。如C。158. Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose allthat apply.)A. It locks only the columns specified in the SELECT list. B. It locks the rows that satisfy the condition in the SELECT statement. C. It can be used only in SELECT statements that are based on a single table. D. It can be used in SELECT statements that are based on a single or multiple tables. E. After it is enforced by a SELECT statement, no other query can access the same rows until aCOMMIT or ROLLBACK is issued. Answer: BD答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143A，只锁定SELECT列表中指定的列，错误，是指定的行。B，锁定SELECT语句中满足条件的行，正确。C，只能用于基于单个表的SELECT语句中，错误。可以针对多个表在SELECT语句中使用FOR UPDATE子句。D，可以用于基于单个表或多个表的SELECT语句中，正确。E，用SELECT语句执行之后，其它查询直到执行COMMIT或ROLLBACK后才能访问相同的行，错误，即使被锁定，也是可以查询的。159. View the Exhibit and examine the structure of the CUSTOMERS table.NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY thathave the same data types and size as the corresponding columns in the CUSTOMERS table.Evaluate the following INSERT statement:INSERT INTO new_customers (cust_id, cust_name, cust_city)VALUES(SELECT cust_id,cust_first_name' 'cust_last_name,cust_city 去掉FROM customers WHERE cust_id > 23004);The INSERT statement fails when executed. What could be the reason? A. The VALUES clause cannot be used in an INSERT with a subquery. values后面不能跟子查询，或者直接不要VALUESB. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.C. The WHERE clause cannot be used in a subquery embedded in an INSERT statement.D. The total number of columns in the NEW_CUSTOMERS table does not match the total number ofcolumns in the CUSTOMERS table.Answer: A答案解析：即insert into后面的如果有子句，则不用values160. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.There is only one customer with the cust_last_name column having value Roberts. Which INSERTstatement should be used to add a row into the ORDERS table for the customer whoseCUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? A. INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000);C. INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);D. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600),order_total) VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000);Answer: A答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143只有一个customer的cust_last_name列的值是Roberts。题意问哪一个INSERT语句能被用于给ORDERS添加一行，customer的cust_last_name为Roberts，并且CREDIT_LIMIT为600，只有A正确BCD语法错误。161. View the exhibit and examine the description for the SALES and CHANNELS tables.You issued the following SQL statement to insert a row in the SALES table:INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc='Direct Sales'), 12, 1, 500);Which statement is true regarding the execution of the above statement?A. The statement will execute and the new row will be inserted in the SALES table.B. The statement will fail because subquery cannot be used in the VALUES clause.C. The statement will fail because the VALUES clause is not required with subquery.D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotationmarks .Answer: A答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143可以在values括号中使用子查询 该语句可以执行成功，前提是子查询的结果是一个值。 这里需要注意的是：图中只有6个字段，但实际在sh用户下sales表中确有7个字段，此题是按照7个字段来的。162. View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARYtables.SALE_VW is a view created using the following command :SQL>CREATE VIEW sale_vw ASSELECT prod_id, SUM(quantity_sold) QTY_SOLDFROM sales GROUP BY prod_id;You issue the following command to add a row to the SALE_SUMMARY table :SQL>INSERT INTO sale_summarySELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products USING (prod_id) WHERE prod_id = 16;What is the outcome? A. It executes successfully.B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do notmatch.D. It gives an error because the number of columns to be inserted does not match with the number ofcolumns in the SALE_SUMMARY table.Answer: D答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143由于列的数量不匹配造成的，如果数量一致，会执行成功。 带有子查询的INSERT语句，勿使用VALUES子句，并且使INSERT子句中的列数与子查询中的列数匹配。163. View the Exhibit and examine the description for the CUSTOMERS table.You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, whereCUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish thetask? A. UPDATE customersSET cust_credit_limit = NULLWHERE CUST_INCOME_LEVEL = NULL; B. UPDATE customersSET cust_credit_limit = NULLWHERE cust_income_level IS NULL;C. UPDATE customersSET cust_credit_limit = TO_NUMBER(NULL)WHERE cust_income_level = TO_NUMBER(NULL);D. UPDATE customersSET cust_credit_limit = TO_NUMBER(' ',9999)WHERE cust_income_level IS NULL;Answer: B答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143题意要求：更新CUSTOMERS表中所有customers的CUST_CREDIT_LIMIT值，当CUST_INCOME_LEVEL为NULL时更新CUST_CREDIT_LIMIT为空。AC的where条件都为假，所有都不更新。D的set语句有语法问题，故错误。1. View the Exhibit and examine the structure of CUSTOMERS and SALES tables.Evaluate the following SQL statement:UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)SET time_id = '22-MAR-2007'WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600);Which statement is true regarding the execution of the above UPDATE statement?A. It would not execute because two tables cannot be used in a single UPDATE statement.B. It would not execute because the SELECT statement cannot be used in place of the table name.C. It would execute and restrict modifications to only the columns specified in the SELECT statement. D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATEstatement.Answer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143UPDATE后面的SELECT语句相当于一个简单视图，执行并只能修改SELECT语句指定的列，前提确保子查询的的结果为一个值。165. View the Exhibit and examine the description for the CUSTOMERS table.You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customerwith the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value asthat of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value asthat of the customer with CUST_ID 2566.Which UPDATE statement will accomplish the task? A. UPDATE customersSET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566)WHERE cust_id=2360;B. UPDATE customersSET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566)WHERE cust_id=2360;C. UPDATE customersSET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566)WHERE cust_id=2360;D. UPDATE customersSET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566)WHERE cust_id=2360;Answer: A答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143BC的子查询返回的是多行，所有报错。D的子查询条件不正确，题意说：更新CUST_ID为2360的CUST_INCOME_LEVEL和CUST_CREDIT_LIMIT列值。你想让CUST_INCOME_LEVEL的值与CUST_ID为2560的值一样，让CUST_CREDIT_LIMIT的值与CUST_ID为2566的值一样按题意，只有A正确。166. View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.You want to update the EMPLOYEES table as follows:4 ? 4;
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id2100).
-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.-Set the employees' commission in location_id 2100 to 1.5 times the average commission of theirdepartment.
You issue the following command:SQL>UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments
WHERE departments.location_id IN(2900,2700,2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700)What is the outcome?
A. It executes successfully and gives the correct result.B. It executes successfully but does not give the correct result.
C. It generates an error because a subquery cannot have a join condition in an UPDATE statement.D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified togetherin an UPDATE statement.Answer: B
答案解析：题意要求更新条件：
1.只更新那些在Boston或Seattle工作的employees（locations为2900或者2700）
2.设置这些employees的department_id为London（location_id 2100）对应的department_id3.设置location_id 2100的employees' salary为他们部门的平均薪水的1.1倍4.设置location_id 2100的employees' commission为他们部门的平均提成的1.5倍UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900,2700,2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700) WHERE departments.location_id IN(2900,2700,2100)) 应该使用部门ID进行关联。167. Evaluate the following DELETE statement:DELETE FROM sales;There are no other uncommitted transactions on the SALES table.Which statement is true about the DELETE statement?A. It would not remove the rows if the table has a primary key.B. It removes all the rows as well as the structure of the table.C. It removes all the rows in the table and deleted rows can be rolled back.D. It removes all the rows in the table and deleted rows cannot be rolled back.Answer: C答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143A，如果表中有主键则不能移除行，错误，有主键是可以删除的B，移除表中所有行及表结构，错误，表结构是保留的C，移除表中所有行，并且删除的行可以回滚，正确，删除的数据保留在undo段，可以回滚。D，移除表中所有行，并且删除的行不可以回滚，错误，删除的行是可以回滚的。168. View the Exhibit and examine the description of SALES and PROMOTIONS tables.You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONStable has either blowout sale or everyday low price as values.Which DELETE statements are valid? (Choose all that apply.)delete from saleswhere promo_id=(select promo_id from promotions where) A. DELETE FROM salesWHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale')AND promo_id = (SELECT promo_id FROM promotions
WHERE promo_name = 'everyday low price');B. DELETE FROM sales
WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale')OR promo_id = (SELECT promo_id FROM promotions
WHERE promo_name = 'everyday low price');C. DELETE FROM sales
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price');D. DELETE FROM sales
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_name IN ('blowout sale','everyday low price'));Answer: BCD
答案解析：
题意要求：从SALES表中删除行，条件为PROMOTIONS表中PROMO_NAM列的值为blowout sale或者everyday low price）
BCD这三个选项是等价的。
而A的where条件中的AND不满足题意要求。
169. View the Exhibit and examine the description for the PRODUCTS and SALES table.PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want toremove all the rows from the PRODUCTS table for which no sale was done for the last three years.Which is the valid DELETE statement? A. DELETEFROM products
WHERE prod_id = (SELECT prod_id FROM sales
WHERE time_id - 3*365 = SYSDATE ); B. DELETEFROM products
WHERE prod_id = (SELECT prod_id FROM sales
WHERE SYSDATE >= time_id - 3*365 );C. DELETEFROM products
WHERE prod_id IN (SELECT prod_id FROM sales
WHERE SYSDATE - 3*365 >= time_id); D. DELETE
FROM productsWHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 );Answer: C 答案解析：题意要求：从PRODUCTS表移除所有过去三年没有销售过的产品行SYSDATE - 3*365 <= time_id三年前之前的时间，三年前多，SYSDATE - 3*365 >= time_id三年前之后的时间，三年前往后面数，即应该用这个即选C170. Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.)A. DELETE can be used to remove only rows from only one table at a time.B. DELETE can be used to remove only rows from multiple tables at a time. C. DELETE can be used only on a table that is a parent of a referential integrity constraint. 父级的参照完整性约束错？D. DELETE can be used to remove data from specific columns as well as complete rows. 指定行删除E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constrainthaving ON DELETE rule .Answer: AE答案解析：A，DELETE一次只能用于移除一个表的行，正确B，DELETE一次只能用于移除多个表的行，错误，一次只能一个表C，DELETE只能用于删除有引用完整性约束的父表，错误，有完整性约束的父表不能被删除，除非禁用约束等D，DELETE可以用于删除指定列的数据以及完整的行，错误，只能删除行，不能删除列E，DELETE和TRUNCATE可以用于有引用完整性约束ON DELETE规则的父表。正确。建表时可以使用ON DELETE CASCADE（当删除父表数据时，子表数据也一起删除）或ON DELETE CASCAD SET NULL（当删除父表数据时，子表相关的列设置为NULL）子句，可以在SQL Language Reference里的Constraints部分查找到ON DELETE子句171. Which three statements/commands would cause a transaction to end? (Choose three.)A. COMMITB. SELECTC. CREATED. ROLLBACKE. SAVEPOINTAnswer: ACD答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143事务处理在遇到第一条DML 语句时开始，在发生以下事件之一时结束：• 发出COMMIT或ROLLBACK语句。• 发出DDL 语句，例如CREATE。• 发出DCL 语句。• 用户退出SQL Developer 或SQL*Plus。• 计算机出现故障或系统崩溃。一个事务处理结束后，下一个可执行的SQL 语句会自动启动下一个事务处理。DDL 语句或DCL 语句是自动提交的，因此会隐式结束一个事务处理。172. The SQL statements executed in a user session are as follows:SQL> CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(10));SQL> INSERT INTO product VALUES (1, 'pen');SQL> INSERT INTO product VALUES (2,'pencil');SQL> SAVEPOINT a;SQL> UPDATE product SET pcode = 10 WHERE pcode = 1;SQL> SAVEPOINT b;SQL> DELETE FROM product WHERE pcode = 2;SQL> COMMIT;SQL> DELETE FROM product WHERE pcode=10;Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT acommand in the session? (Choose two.)A. The rollback generates an error. B. No SQL statements are rolled back.C. Only the DELETE statements are rolled back.D. Only the second DELETE statement is rolled back.E. Both the DELETE statements and the UPDATE statement are rolled back.Answer: AB 答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143由于COMMIT命令提交后之前建立的保存点都变成无效的了。ROLLBACK TO SAVEPOINT a;命令报错后，只会影响本身这条语句，不会影响其它语句，所以第二个DELETE语句不会回滚。173. When does a transaction complete? (Choose all that apply.)A. when a DELETE statement is executedB. when a ROLLBACK command is executedC. when a PL/SQL anonymous block is executedD. when a data definition language ( DDL) statement is executedE. when a TRUNCATE statement is executed after the pending transaction Answer: BDE答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143事务处理在遇到第一条DML 语句时开始，在发生以下事件之一时结束：• 发出COMMIT或ROLLBACK语句。• 发出DDL 语句，例如CREATE,TRUNCATE等。• 发出DCL 语句。• 用户退出SQL Developer 或SQL*Plus。• 计算机出现故障或系统崩溃。一个事务处理结束后，下一个可执行的SQL 语句会自动启动下一个事务处理。DDL 语句或DCL 语句是自动提交的，因此会隐式结束一个事务处理。174. Which statement is true regarding transactions? (Choose all that apply.)A. A transaction can consist only of a set of DML and DDL statements. B. A p art or an entire transaction can be undone by using ROLLBACK command . C. A transaction consists of a set of DML or DCL statements. D. A part or an entire transaction can be made permanent with a COMMIT. E. A transaction can consist of only a set of queries or DML or DDL statements. Answer: BC答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143A，一个事务只能由一组DML和DDL语句组成，错误，事务由第一条DML开始，由事件之一时结束：• 发出COMMIT或ROLLBACK语句。• 发出DDL 语句，例如CREATE,TRUNCATE等。• 发出DCL 语句。• 用户退出SQL Developer 或SQL*Plus。• 计算机出现故障或系统崩溃。一个事务处理结束后，下一个可执行的SQL 语句会自动启动下一个事务处理。DDL 语句或DCL 语句是自动提交的，因此会隐式结束一个事务处理。B，部分或整个事务可以使用ROLLBACK命令回滚，正确，可以用rollback来回滚部分或者全部事务，回滚部分需要保存点。C，一个事务由一组DML或DCL语句组成，正确。D，部分或整个事务可以使用COMMIT命令永久提交，错误。因为COMMIT把整个事务都进行提交，不能提交部分事务。E，一个事务只能由一组查询或DML或DDL语句组成，错误。查询不是事务。175. Which two statements are true regarding savepoints? (Choose two.)A. Savepoints are effective only for COMMIT.B. Savepoints may be used to ROLLBACK. C. Savepoints can be used for only DML statements. D. Savepoints are effective for both COMMIT and ROLLBACK. E. Savepoints can be used for both DML and DDL statements. Answer: BC 答案解析：参考：http://blog.csdn.net/rlhua/article/details/12885143A，保存点只对COMMIT有效，错误，只对ROLLBACKB，保存点可以用于ROLLBACK，正确。C，保存点只能用于DML语句，正确。D，保存点对于COMMIT和ROLLBACK都有效，错误。只对ROLLBACK。E，保存点可以用于DML和DDL语句，错误。DDL语句不行，DDL相当于commit，commit后不能回滚。保存点只能用于DML语句，并且只用于ROLLBACK。
因篇幅问题不能全部显示，请点此查看更多更全内容