6.Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATENOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You wantto generate a report that displays the PO_ID and the penalty amount to be paidif the
SHIPMENT_DATEis later than one month from the PO_DATE. The penalty is $20 per day.
Evaluatethe following two queries:
SQL>SELECT po_id, CASE
WHENMONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date- po_date) * 20) ELSE 'No Penalty' END PENALTY
FROMshipments;
SQL>SELECTpo_id, DECODE
(MONTHS_BETWEEN(po_date,shipment_date)>1,
TO_CHAR((shipment_date- po_date) * 20), 'No Penalty') PENALTY
FROMshipments;
Whichstatement is true regarding the above commands?
A. Bothexecute successfully and give correct results.
B. Onlythe first query executes successfully but gives a wrong result.
C. Onlythe first query executes successfully and gives the correct result.
D. Onlythe second query executes successfully but gives a wrong result.
E. Onlythe second query executes successfully and gives the correct result.
Answer: C
原题翻译: |
检查SHIPMENTS表结构如下:
|
相关理论: | ||
DECODE 与CASE WHEN 的比较 1. DECODE 只能用做相等判断,但是可以配合sign函数进行大于,小于,等于的判断 CASE when可用于=,>=,<,<=,<>,is null,is not null 等的判断;
|
实验: |
CREATETABLE SHIPMENTS ( PO_ID NUMBER(3) NOTNULL, PO_DATE DATENOTNULL, SHIPMENT_DATE DATE NOTNULL, SHIPMENT_MODE VARCHAR2(30), SHIPMENT_COST NUMBER(8,2) );
SQL> SELECT po_id, CASE 2 WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN 3 TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY 4 FROM shipments; PO_ID PENALTY ----- ---------------------------------------- --以上可以执行
SQL> SELECT po_id, DECODE 2 (MONTHS_BETWEEN (po_date,shipment_date)>1, 3 TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY 4 FROM shipments; SELECT po_id, DECODE (MONTHS_BETWEEN (po_date,shipment_date)>1, TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY FROM shipments ORA-00907: missing right parenthesis --以上语法有误,修改后可执行, --注:sign()函数根据参数1的值是0、正数还是负数,分别返回0、1、-1 SQL> SELECT po_id, 2 DECODE(SIGN(MONTHS_BETWEEN(shipment_date, po_date) - 1), 3 1, 4 TO_CHAR((shipment_date - po_date) * 20), 5 -1, 6 'No Penalty') PENALTY 7 FROM shipments; PO_ID PENALTY ----- ----------------------------------------
|
7. Whichtwo statements are true regarding the USING and ON clauses in table joins?(Choose two.)
A. BothUSING and ON clauses can be used for equijoins and nonequijoins.
B. Amaximum of one pair of columns can be joined between two tables using the ONclause.
C. The ONclause can be used to join tables on columns that have different names butcompatible data types.
D. TheWHERE clause can be used to apply additional conditions in SELECT statementscontaining the ON or the USING clause.
Answer:CD
题目翻译 |
关于多表连接的USING和ON子句,下面哪两个选项是正确的?(选择两个)
|
题目解析 A选项,不正确,USING子句只能用于等值连接, ON子句可用于等值和非等值连接, |
相关知识点: |
SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2...);
SELECT table1.column, table2.column FROM table1 JOIN table2 ON (table1.column_name = table2.column_name);
纯自然联接和JOIN ...USING 子句依赖具有相同列名的联接列.JOIN ... ON 子句允许显式指定联接列,不管它们的列名是什么. 这是联接子句最灵活、最常用的形式。同等联接被完全限定为table1.column1 = table2.column2 并可以在ON 关键字之后的括号内指定。
The pure natural join and the JOIN . . . USING clauses depend on join columns with identical column names. The JOIN . . . ON clause allows the explicit specification of join columns, regardless of their column names. This is the most flexible and widely used form of the join clauses. The equijoin columns are fully qualified as table1.column1 = table2.column2 and are optionally specified in brackets after the ON keyword.
Query 1: select d.department_name from departments d join locations l on (l.LOCATION_ID=d.LOCATION_ID) where d.department_name like 'P%';
Query 2: select d.department_name from departments d join locations l on (l.LOCATION_ID=d.LOCATION_ID and d.department_name like 'P%'); |
8. View the Exhibit and examine the structureof the CUSTOMERS table.
Which two tasks would require subqueries orjoins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have acredit limit and were born before 1980
B. finding the number of customers, in eachcity, whose marital status is 'married'
C. finding the average credit limit of malecustomers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose creditlimit is the same as the credit limit of customers residing in the city 'Tokyo'
E. finding the number of customers, in eachcity, whose credit limit is more than the average credit limit of all thecustomers
Answer: DE
题目翻译 |
下面是CUSTOMERS表的结构: |
题目解析 |
A选项,只需要在where条件中判断有没有信贷限额和出生日期小于1980的人,两个条件and连接就行了,不需要子查询或多表关联。 SELECT 客户 from 表名 where 信贷限额 is null and 出生日期>1980; SELECT 城市名,COUNT(*) FROM 表名 where 客户婚否=‘结婚’ group by 城市名; SELECT 城市名, AVG(信贷限额) from 表名 where 性别=‘男’ and 城市 in('Tokyo', 'Sydney') group by 城市名
|
9. Which statement is true regarding theINTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersectedtables alters the result.
C. The names of columns in all SELECTstatements must be identical.
D. The number of columns and data types mustbe identical for all SELECT statements in the query.
Answer: D
题目翻译 |
关于INTERSECT操作符,哪句话正确? |
题目解析 |
A不正确,INTERSECT不会忽略空值, |
10. View the Exhibit; examine the structureof the PROMOTIONS table.
Each promotion has a duration of at leastseven days .
Your manager has asked you to generate areport, which provides the weekly cost for each promotion done to l date.
Which query would achieve the requiredresult?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7FROM promotions;
B. SELECTpromo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;
C. SELECT promo_name,promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;
D. SELECT promo_name, promo_cost/((promo_end_date-promo_begin_date)/7)FROM promotions;
Answer: D
题目翻译 |
查看PROMOTIONS表结构。 |
题目解析 |
AB C语法错误 |