1. View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES
tables.
The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the
CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the
column definition.
B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified
columns would be passed to the new table.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE
command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the
specified columns would be passed to the new table.
Answer: B
解析:
SQL> create table SALES(prod_id number not null,cust_id number not null,time_id date not null,channel_id number not null,promo_id number not null,quantity_sold number(10,2) not null);
表已创建。
SQL> CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE) AS SELECT prod_id, cust_id, time_id FROM sales;
表已创建。
SQL> DESC new_sales;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
ORDER_DATE NOT NULL DATE
SQL> drop table new_sales;
表已删除。
SQL> drop table SALES;
表已删除。
可以看出NOT NULL约束可以传递给子表;
2. View the Exhibit to examine the description for the SALES table.
Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE - 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;
Answer: AB
解析:
注意本地题干:Which views can have all DML operations performed on it?
SQL> create table SALES(prod_id number not null,CUST_ID number not null,time_id date not null,channel_id number not null,promo_id number not null,quantity number(10,2) not null);
SQL> CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id=2034 WITH CHECK OPTION;
View created.
SQL> CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id<=SYSDATE - 2*365 WITH CHECK OPTION;
View created.
C答案其他列不能为空
D答案sum(quantity_sold)后面需要加别名
SQL> create view v4 as select prod_id,cust_id,sum(quantity_SOLD) sumqty from sales where time_id<=sysdate-2*365 group by prod_id,cust_id with check option;
View created.
3. You need to extract details of those products in the SALES table where the PROD_ID column
contains the string '_D123'.
Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
Answer: B
答案解析:
ESCAPE子句为指定转译字符,因为‘_’下滑线在LIKE子句中指的是任意一个字符,所以需要把_’下滑线进行转译。
4. Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.
Answer: DE
答案解析:
A.单行函数可以是含有两个参数,如MONTHS_BETWEEN函数。
B.单行函数可以被多级嵌套,所以B答案错误。
C.参数也可以是变量或者表达式,所以C错误
D.单行函数只返回单一值
E. 返回的数据类型值可以与之前引用的数据类型不同,所以正确。
5. Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three .)
A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
Answer: ADE
答案解析:
A:
SQL> SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
TO_CHAR(18
----------
$1,890.55
B:
SQL> SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
TO_CHAR(1
---------
$1,89055
C:
SQL> SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL
*
ERROR at line 1:
ORA-01481: invalid number format model
D:
SQL> SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
TO_CHAR(189
-----------
$1,890.55
E:
SQL> SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
TO_CHAR(189
-----------
$1,890.55
G表示分组分隔符group
D表示小数点decimal point
V表示移动N位(小数点),乘以10的n次方后面跟几位就是几次方。
","和G或者D不能同时出现,如果把C答案的D改成“.”,也正确
SQL> SELECT TO_CHAR(1890.55,'$99,999.99') FROM DUAL;
TO_CHAR(189
-----------
$1,890.55
总之,字符和字母不能同时出现。
6. Examine the structure of the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULL DATE
SHIPMENT_MODE VARCHAR2(30)
SHIPMENT_COST NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THENTO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY
FROM shipments;
SQL>SELECT po_id, DECODE
(MONTHS_BETWEEN (po_date,shipment_date)>1,
TO_CHAR((shipment_date - po_date) * 20), 'No Penalty') PENALTY
FROM shipments;
Which statement is true regarding the above commands?
A. Both execute successfully and give correct results.
B. Only the first query executes successfully but gives a wrong result.
C. Only the first query executes successfully and gives the correct result.
D. Only the second query executes successfully but gives a wrong result.
E. Only the second query executes successfully and gives the correct result.
Answer: C
答案解析:
显示PO_ID和支付的罚款总金额,SHIPMENT_DATE与PO_DATE进行比较,SHIPMENT_DATE如果比PO_DATE晚一个月,则每天罚款$20。
SQL> CREATE TABLE SHIPMENTS(PO_ID NUMBER(3) NOT NULL,PO_DATE DATE NOT NULL,SHIPMENT_DATE DATE NOT NULL,SHIPMENT_MODE VARCHAR2(30),SHIPMENT_COST NUMBER(8,2));
Table created.
SQL> SELECT po_id, CASE WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY FROM shipments;
no rows selected
7. Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data
types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the
ON or the USING clause.
Answer: CD
A. USING和ON子句可以用于等值连接和非等值连接,USING不能用于非等值连接,ON可以。
B. 使用ON子句最大只能使用两个列连接两个表.错误,可以连接多个列
C.ON子句用于连接表的列可以是不同的名字,但是数据类型要兼容,正确。ON子句还可以用于联接同一表内或者不同表中具有不同名称的列。
D.在包含ON或USING子句的SELECT命令中,WHERE子句可以做为附加的条件,正确
8. View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in thecity 'Tokyo'
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of
all the customers
Answer: DE
ABC不用子查询就可以查询出,题意选择两个,排除ABC,选择DE
A. 列出没有信贷限额并且1980年以前出生的客户。
SELECT 客户 from 表名 where 信贷限额 is null and 出生日期>1980;
B. 查找每个城市的已婚客户的数量。
SELECT 城市名,COUNT(*) FROM 表名 where 客户婚否=‘结婚’ group by 城市名;
C. 查找属于'Tokyo'或者 'Sydney'两个城市的男性客户的平均信贷限额。
SELECT 城市名, AVG(信贷限额) from 表名 where 性别=‘男’ and 城市 in('Tokyo', 'Sydney') group by 城市名
D 列出与'Tokyo'城市的客户的信贷限额相等的客户
E. 查找每个城市的客户数量,这些客户的信贷限额大于所有客户的平均信贷限额。
9. Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B. Reversing the order of the intersected tables alters the result.
C. The names of columns in all SELECT statements must be identical.
D. The number of columns and data types must be identical for all SELECT statements in the query.
Answer: D
A. 它忽略空值,错误,不会忽略空值
B. 交换交集表的前后顺序可以改变交集结果,错误,不会改变结果
C. 所有SELECT查询语句中的列的名字必须相同。错误,列名可以不必相同
D. 对于所有SELECT查询语句,列的数量和数据类型必须相同。
使用INTERSECT运算符可以返回多个查询的所有共同行。
准则
? 在查询中使用的所有SELECT语句中,由查询中的SELECT语句选定的列数和列的数据类型必须相同。不过,列名不必相同。
? 使相交的表按反方向排序不会更改结果。
? INTERSECT不会忽略NULL值。
如果选择列表中包含有表达式或者函数,那么必须为表达式或函数定义列别名
1、Uinon:无重并集,并以第一列的结果进行升序排序
2、Uinon all:有重并集,不对结果集排序
3、Intersect:交集,以第一列的结果进行升序排列
4、Minus:差集,以第一列的结果进行升序排列
5、可使用order by,必须放在最后一条select之后,当列名相同时,可以直接用列名排序,如果不同可以用位置排序,也可以使用别名使其相同。
10. View the Exhibit; e xamine the structure of the PROMOTIONS table.
Each promotion has a duration of at least seven days .
Your manager has asked you to generate a report, which provides the weekly cost for each promotion
done to l date.
Which query would achieve the required result?
A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7
FROM promotions;
B. SELECT promo_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
问题解析:
需要提供促销期间每周的成本。
ABC语法错误,故答案为D;
11. View the Exhibit and examine the structure of the PRODUCTS table.
All products have a list price.
You issue the following command to display the total price of each product after a discount of 25% and a
tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.
SQL>SELECT prod_name, prod_list_price -(prod_list_price*(25/100))
+(prod_list_price -(prod_list_price*(25/100))*(15/100))+100
AS "TOTAL PRICE"
FROM products;
What would be the outcome if all the parenthese s are removed from the above statement?
A. It produces a syntax error.
B. The result remains unchanged.
C. The total price value would be lower than the correct value.
D. The total price value would be higher than the correct value.
Answer: B
此题的意思是去除括号后,答案是否一样,其实这是一道数学题,去除括号后,其实一样。
12. You need to produce a report where each customer's credit limit has been incremented by $1000. In
the output, t he customer's last name should have the heading Name and the incremented credit limit
should be labeled New Credit Limit. The column headings should have only the first letter of each word in
uppercase .
Which statement would accomplish this requirement?
A. SELECT cust_last_name Name, cust_credit_limit + 1000
"New Credit Limit"
FROM customers;
B. SELECT cust_last_name AS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers;
C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000
AS "New Credit Limit"
FROM customers;
D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000
INITCAP("NEW CREDIT LIMIT")
FROM customers;
Answer: C
列别名需要加""才能保留列别名的大小写。
13. View the Exhibit and examine the structure of the PRODUCTS table.
You need to generate a report in the following format:
CATEGORIES
5MP Digital Photo Camera's category is Photo
Y Box's category is Electronics
Envoy Ambassador's category is Hardware
Which two queries would give the required output? (Choose two.)
A. SELECT prod_name q'''s category is ' prod_category CATEGORIES
FROM products;
B. SELECT prod_name q'['s ]'category is ' prod_category CATEGORIES
FROM products;
C. SELECT prod_name q'\'s\' ' category is ' prod_category CATEGORIES
FROM products;
D. SELECT prod_name q'<'s >' 'category is ' prod_category CATEGORIES
FROM products;
Answer: CD
另外Oracle还提供了一个Q-quote的表达式,来简化SQL或PLSQL中字符串的表示,其格式为q'[Camera's category is Photo]',输出为方括号中的原始字符串格式,其中方括号可以更换为其他的任意一对特殊符号,但必须是成对出现的,不可为q'[Camera's category is Photo|'。
如:
SQL> select q'[Camera's category is Photo]' name from dual;
NAME
--------------------------
Camera's category is Photo
SQL> select q'|Camera's category is 'Photo'|' name from dual;
NAME
----------------------------
Camera's category is 'Photo'
A:错误, is后面应该是两个单引号,正确的为
SELECT prod_name || q'''s category is ''|| prod_category CATEGORIES
FROM products;
B:错误,category 前面少了一个',正确的为
SELECT prod_name || q'['s ]'||'category is '||prod_category CATEGORIES FROM products;
14. Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit
amount in each income level. The report should NOT show any repeated credit amounts in each income
level.
Which query would give the required result?
A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% Credit Limit"
FROM customers;
B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% Credit Limit"
FROM customers;
C. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
D. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
Answer: C
1.DISTINCT只能出现在SQL的开头。
2.每个SQL语句只能有一个DISTINCT关键字。
15. View the Exhibit and examine the data in the CUSTOMERS table.
Evaluate the following query:
SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100 AS "MAX
LOWER LIMIT"
FROM customers;
The above query produces an error on execution.
What is the reason for the error?
A. An alias cannot be used in an expression.
B. The a lias NAME should not be enclosed with in double quotation marks .
C. The MIDPOINT+100 expression gives an error because CUST_CREDIT_LIMIT contains NULL
values.
D. The a lias MIDPOINT should be enclosed with in double quotation marks for the
CUST_CREDIT_LIMIT/2 expression .
Answer: A
AS后面需要加""