106. Examine the data inthe LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:
LIST_PRICE MIN_PRICE
10000 8000
20000
30000 30000
Which two expressionsgive the same output? (Choose two.)
A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price,min_price), 0)
C.NVL2(COALESCE(list_price, min_price), min_price, 0)
D.COALESCE(NVL2(list_price, list_price, min_price), 0)
Answer: BD
解析：
引用官方文档：
NULLIF compares expr1 and expr2. If theyare equal, then the function returns null.
If they are not equal, then the functionreturns expr1. You cannot specify the literal
NULL for expr1
NVL lets you replace null (returned as ablank) with a string in the results of a query. If
expr1 is null, then NVL returns expr2. Ifexpr1 is not null, then NVL returns expr1.
COALESCE returns the first non-null expr inthe expression list. You must specify at
least two expressions. If all occurrencesof expr evaluate to null, then the function
returns null.
NVL2 lets you determine the value returned bya query based on whether a specified
expression is null or not null. If expr1 isnot null, then NVL2 returns expr2. If expr1
is null, then NVL2 returns expr3.
107. View the Exhibitand examine the structure and data in the INVOICE table.
Which two SQL statementswould execute successfully? (Choose two.)
A. SELECT AVG(inv_date )
FROM invoice;
B. SELECTMAX(inv_date),MIN(cust_id)
FROM invoice;
C. SELECTMAX(AVG(SYSDATE - inv_date))
FROM invoice;
D. SELECT AVG( inv_date- SYSDATE), AVG(inv_amt)
FROM invoice;
Answer: BD
解析：
引用官方文档：
This function takes as an argument anynumeric data type or any nonnumeric data
type that can be implicitly converted to anumeric data type. The function returns the
same data type as the numeric data type ofthe argument.
所以A错
A later date is considered greater than anearlier one. For example, the date equivalent
of '29-MAR-2005' is less than that of'05-JAN-2006' and '05-JAN-2006 1:35pm' is greater
than '05-JAN-2005 10:09am'.
Oracle compares VARCHAR2 values usingnonpadded
comparison semantics.
所以B正确
C选项中avg得到单一结果，无法使用max函数
所以C错误
D选项中inv_date-sysdate得到数字，所以能使用avg
所以D正确
108. Which twostatements are true regarding the COUNT function? (Choose two.)
A. The COUNT functioncan be used only for CHAR, VARCHAR2, and NUMBER data types.
B. COUNT(*) returns thenumber of rows including duplicate rows and rows containing NULL value in
any of the columns.
C. COUNT(cust_id)returns the number of rows including rows with duplicate customer IDs and NULL
value in the CUST_IDcolumn.
D. COUNT(DISTINCTinv_amt)returns the number of rows excluding rows containing duplicates and
NULL values in theINV_AMT column.
E. A SELECT statementusing the COUNT function with a DISTINCT keyword cannot have a
WHERE clause.
Answer: BD
解析：
A选项，count()还可以用在date等数据类型的行上
B选项，如果count不指定distinct的话，就会返回所有的重复行
C选项，count不会计算列为空的
D选项，明确指定了distinct，就不会返回重复行，当然也不会返回空行
E选项，当然可以使用where，测试：
scott@ORCL>select count(distinct comm)from emp where sal>1000;
COUNT(DISTINCTCOMM)
-------------------
4
109. Examine thestructure of the MARKS table:
name Null Type
STUDENT_ID NOT NULLVARCHAR2(4)
STUDENT_NAMEVARCHAR2(25)
SUBJECT1 NUMBER(3)
SUBJECT2 NUMBER(3)
SUBJECT3 NUMBER(3)
Which two statementswould execute successfully? (Choose two.)
A. SELECT student_name,subject1
FROM marks
WHERE subject1 >AVG(subject1);
B. SELECTstudent_name,SUM(subject1)
FROM marks
WHERE student_name LIKE'R%';
C. SELECTSUM(subject1+subject2+subject3)
FROM marks
WHERE student_name ISNULL;
D. SELECT SUM(DISTINCTNVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 >subject2;
Answer: CD
解析：
A选项，不能在where后使用聚集函数
B选项，这里没有分组，无法使用sum
C，D正确
110. View the Exhibitand examine the structure of the CUSTOMERS table.
Using the CUSTOMERStable, you need to generate a report that shows the average credit limit for
customers in WASHINGTONand NEW YORK.
Which SQL statementwould produce the required result?
A. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN('WASHINGTON','NEW YORK')
GROUP BYcust_credit_limit, cust_city;
B. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN('WASHINGTON','NEW YORK')
GROUP BYcust_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
解析：
这里考察和上题的B选项一样，需要分组，才能使用avg()函数
所以正确答案为C选项
111. View the Exhibitand examine the structure of the CUSTOMERS table.
Which statement woulddisplay the highest credit limit available in each income level in each city inthe
CUSTOMERS 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;
C. SELECT cust_city,cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BYcust_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
解析：
这里也和上题考察的知识点一样，需要分组，并以cust_city和cust_income_level
所以正确答案为B选项
Answer: B
112. View the Exhibitand examine the structure of the PROMOTIONS table.
Evaluate the followingSQL statement:
SQL>SELECTpromo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHEREUPPER(promo_category) IN ('TV', 'INTERNET','POST')
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above querygenerates an error on execution.
Which clause in theabove SQL statement causes the error?
A. WHERE
B. SELECT
C. GROUP BY
D. ORDER BY
Answer: C
解析：
Group by 后面不能用分组函数
所以C正确
113. Examine the structureof the ORDERS table:
Name Null Type
ORDER_ID NOT NULLNUMBER(12)
ORDER_DATE NOT NULLTIMESTAMP(6)
CUSTOMER_ID NOT NULLNUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
You want to find thetotal value of all the orders for each year and issue the following command:
SQL>SELECTTO_CHAR(order_date,'rr'), SUM(order_total)
FROM orders
GROUP BYTO_CHAR(order_date,'yyyy');
Which statement is trueregarding the outcome?
A. It executessuccessfully and gives the correct output.
B. It gives an errorbecause the TO_CHAR function is not valid.
C. It executessuccessfully but does not give the correct output.
D. It gives an errorbecause the data type conversion in the SELECT list does not match the datatype
conversion in the GROUPBY clause.
Answer: D
解析：
To_char需要前后转换格式一样，测试：
scott@ORCL>select to_char(hiredate,'rr') from emp group byto_char(hiredate,'yyyy')
2 ;
selectto_char(hiredate,'rr') from emp group by to_char(hiredate,'yyyy')
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
114. View the Exhibitand examine the structure of the SALES table.
The following query iswritten to retrieve all those product ID s from the SALES table that have morethan
55000 sold and have beenordered more than 10 times.
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold >55000 AND COUNT(*)>10
GROUP BY prod_id
HAVING COUNT(*)>10;
Which statement is trueregarding this SQL statement?
A. It executessuccessfully and generates the required result.
B. It produces an errorbecause COUNT(*) should be specified in the SELECT clause also.
C. It produces an errorbecause COUNT(*) should be only in the HAVING clause and not in the WHERE
clause.
D. It executessuccessfully but produces no result because COUNT(prod_id) should be usedinstead of
COUNT(*).
Answer: C
解析：
Where后面不能用分组函数
所以C选项正确
115. View the Exhibitand examine the structure of the CUSTOMERS table.
Evaluate the followingSQL statement:
SQL> SELECTcust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit> 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit)BETWEEN 5000 AND 6000;
Which statement is trueregarding the outcome of the above query?
A. It executessuccessfully.
B. It returns an errorbecause the BETWEEN operator cannot be used in the HAVING clause.
C. It returns an error becauseWHERE and HAVING clauses cannot be used in the same SELECT
statement.
D. It returns an errorbecause WHERE and HAVING clauses cannot be used to apply conditions on the
same column.
Answer: A
解析：
Group by 后面可以用分组函数
所以A选项正确
116. Examine the data inthe ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
You want to find out ifthere is any item in the table for which the average maximum quantity is morethan
50.
You issue the followingquery:
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVINGAVG(MAX(qty))>50;
Which statement is trueregarding the outcome of this query?
A. It executessuccessfully and gives the correct output.
B. It gives an errorbecause the HAVING clause is not valid.
C. It executessuccessfully but does not give the correct output.
D. It gives an errorbecause the GROUP BY expression is not valid.
Answer: B
解析：
Having后不能使用嵌套分组函数，测试:
scott@ORCL>selectavg(max(sal)) from emp group by empno having avg(max(sal));
select avg(max(sal))from emp group by empno having avg(max(sal))
*
第 1 行出现错误:
ORA-00935: 分组函数的嵌套太深
117. Which statementsare true regarding the WHERE and HAVING clauses in a SELECT statement?
(Choose all that apply.)
A. The HAVING clause canbe used with aggregate functions in subqueries.
B. The WHERE clause canbe used to exclude rows after dividing them into groups.
C. The WHERE clause canbe used to exclude rows before dividing them into groups.
D. The aggregatefunctions and columns used in the HAVING clause must be specified in the SELECTlist
of the query.
E. The WHERE and HAVINGclauses can be used in the same statement only if they are applied to
different columns in thetable.
Answer: AC
解析：
Group by后可以使用聚集函数，而where 后不可以使用
Group by后使用的聚集函数不用首先在select中指定
所以A，C正确
118. View the Exhibitand examine the structure of the PROMOTIONS table.
Examine the followingtwo SQL statements:
Statement 1
SQL>SELECTpromo_category,SUM(promo_cost)
FROM promotions
WHEREpromo_end_date-promo_begin_date > 30
GROUP BY promo_category;
Statement 2
SQL>SELECTpromo_category,sum(promo_cost)
FROM promotions
GROUP BY promo_category
HAVINGMIN(promo_end_date-promo_begin_date)>30;
Which statement is trueregarding the above two SQL statements?
A. statement 1 gives anerror, statement 2 executes successfully
B. statement 2 gives anerror, statement 1 executes successfully
C. statement 1 andstatement 2 execute successfully and give the same output
D. statement 1 andstatement 2 execute successfully and give a different output
Answer: D
解析：
都无语法错误，statement 1先筛选行，再进行分组排列
Statement 2 先分组，再筛选，测试：
scott@ORCL>select job,sum(sal) from emp where sysdate-hiredate>11700group by job;
JOB SUM(SAL)
--------- ----------
CLERK 968
SALESMAN 5600
PRESIDENT 6300
MANAGER 8749.24
scott@ORCL>selectjob,sum(sal) from emp group by job having min(sysdate-hiredate)>11700;
JOB SUM(SAL)
--------- ----------
SALESMAN 5600
PRESIDENT 6300
MANAGER 8749.24
119. Examine the data inthe ORD_ITEMS table:
ORD_NO ITEM_NO QTY
1 111 10
1 222 20
1 333 30
2 333 30
2 444 40
3 111 40
Evaluate the followingquery:
SQL>SELECT item_no,AVG(qty)
FROM ord_items
HAVING AVG(qty) >MIN(qty) * 2
GROUP BY item_no;
Which statement is trueregarding the outcome of the above query?
A. It gives an errorbecause the HAVING clause should be specified after the GROUP BY clause.
B. It gives an errorbecause all the aggregate functions used in the HAVING clause must be specifiedin
the SELECT list.
C. It displays the itemnos with their average quantity where the average quantity is more than doublethe
minimum quantity of thatitem in the table.
D. It displays the itemnos with their average quantity where the average quantity is more than doublethe
overall minimum quantityof all the items in the table.
Answer: C
解析：
没有语法错误，得到item_no, AVG(qty)并且AVG(qty) >MIN(qty) * 2
所以C选择正确
120. View the Exhibitsand examine the structures of the PRODUCTS, SALES, and CUSTOMERS
tables.
You issue the followingquery:
SQL>SELECTp.prod_id,prod_name,prod_list_price,
quantity_sold,cust_last_name
FROM products p NATURALJOIN sales s NATURAL JOIN customers c
WHERE prod_id =148;
Which statement is trueregarding the outcome of this query?
A. It executessuccessfully.
B. It produces an errorbecause the NATURAL join can be used only with two tables.
C. It produces an errorbecause a column used in the NATURAL join cannot have a qualifier.
D. It produces an errorbecause all columns used in the NATURAL join should have a qualifier.
Answer: C
解析：
引用官方文档：
When specifying columnsthat are involved in the natural join, do not qualify
the column name with atable name or table alias.
所以C选项正确
121. Which twostatements are true regarding the USING clause in table joins? (Choose two .)
A. It can be used tojoin a maximum of three tables.
B. It can be used torestrict the number of columns used in a NATURAL join.
C. It can be used toaccess data from tables through equijoins as well as nonequijoins.
D. It can be used tojoin tables that have columns with the same name and compatible data types.
Answer: BD
解析：
引用官方文档：
When you are specifyingan equijoin of columns that have the same
name in both tables, theUSING column clause indicates the columns to be used. You
can use this clause onlyif the join columns in both tables have the same name. Within
this clause, do notqualify the column name with a table name or table alias.
Restriction on the USINGcolumn Clause
■ Within this clause, donot qualify the column name with a table name or table
alias.
■ You cannot specify aLOB column or a collection column in the USING column
clause.
■ You cannot specify thisclause with a NATURAL outer join.
122. View the Exhibit forthe structure of the STUDENT and FACULTY tables.
You need to display thefaculty name followed by the number of students handled by the faculty at the
base location.
Examine the followingtwo SQL statements:
Statement 1
SQL>SELECTfaculty_name,COUNT(student_id)
FROM student JOINfaculty
USING (faculty_id,location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECTfaculty_name,COUNT(student_id)
FROM student NATURALJOIN faculty
GROUP BY faculty_name;
Which statement is trueregarding the outcome?
A. Only s tatement 1executes successfully and gives the required result.
B. Only statement 2executes successfully and gives the required result.
C. Both statements 1 and2 execute successfully and give different results.
D. Both statements 1 and2 execute successfully and give the same required result.
Answer: D
解析：
USING (faculty_id,location_id)和NATURAL JOIN效果一样
123. View the Exhibitsand examine the structures of the PRODUCTS, SALES, and CUSTOMERS
tables.
You need to generate areport that gives details of the customer's last name, name of the product, and
the quantity sold forall customers in ' Tokyo' .
Which two queries givethe required result? (Choose two.)
A. SELECTc.cust_last_name,p.prod_name, s.quantity_sold
FROM sales s JOINproducts p
USING(prod_id)
JOIN customers c
USING(cust_id)
WHEREc.cust_city='Tokyo';
B. SELECTc.cust_last_name, p.prod_name, s.quantity_sold
FROM products p JOINsales s JOIN customers c
ON(p.prod_id=s.prod_id)
ON(s.cust_id=c.cust_id)
WHEREc.cust_city='Tokyo';
C. SELECT c.cust_last_name,p.prod_name, s.quantity_sold
FROM products p JOINsales s
ON(p.prod_id=s.prod_id)
JOIN customers c
ON(s.cust_id=c.cust_id)
AND c.cust_city='Tokyo';
D. SELECTc.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold
FROM products p JOINsales s
USING(prod_id)
JOIN customers c
USING(cust_id)
WHEREc.cust_city='Tokyo';
Answer: AC
解析：
A选项，使用USING(prod_id)连接 sales比表和product表 使用USING(prod_id)连接sales表和customers表
C选项，ON(p.prod_id=s.prod_id)和ON(s.cust_id=c.cust_id)与A选项中的using效果一样
124. View the Exhibitand examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.
You need to generate areport showing the promo name along with the customer name for all products
that were sold duringtheir promo campaign and before 30th October 2007.
You issue the followingquery:
SQL> SELECTpromo_name,cust_name
FROM promotions p JOINsales s
ON(time_id BETWEENpromo_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 regardingthe above query?
A. It executessuccessfully and gives the required result.
B. It executessuccessfully but does not give the required result.
C. It produces an errorbecause the join order of the tables is incorrect.
D. It produces an errorbecause equijoin and nonequijoin conditions cannot be used in the same SELECT
statement.
Answer:B
解析：
题目要求：
showing the promo namealong with the customer name for all products
that were sold duringtheir promo campaign and before 30th October 2007
不应该在连接表的时候就加上条件，应该在把条件最后面
125. Examine thestructure of the CUSTOMERS table:
name Null Type
CUSTNO NOT NULLNUMBER(3)
CUSTNAME NOT NULLVARCHAR2(25)
CUSTADDRESS VARCHAR2(35)
CUST_CREDIT_LIMITNUMBER(5)
CUSTNO is the PRIMARYKEY in the table. You want to find out if any customers' details have been
entered more than onceusing different CUSTNO, by listing all the duplicate names.
Which two methods canyou use to get the required result? (Choose two.)
A. self-join
B. subquery
C. full outer-join withself-join
D. left outer-join withself-join
E. right outer-join withself-join
Answer: AB
解析：
find out if any customers'details have been
entered more thanonce using different CUSTNO, by listing all the duplicate names.
需要用自连接比较names
需要使用子查询得到customers'details have beenentered more than once using different CUSTNO
126. View the Exhibitand examine the data in the PROJ_TASK_DETAILS table.
The PROJ_TASK_DETAILStable stores information about tasks involved in a project and the relation
between them.
The BASED_ON columnindicates dependencies between tasks. Some tasks do not depend on the
completion of any othertasks.
You need to generate areport showing all task IDs, the corresponding task ID they are dependent on,and
the name of the employeein charge of the task it depends on.
Which query would givethe required result?
A. SELECT p.task_id,p.based_on, d.task_in_charge
FROM proj_task_details pJOIN proj_task_details d
ON (p.based_on =d.task_id);
B. SELECT p.task_id,p.based_on, d.task_in_charge
FROM proj_task_details pLEFT OUTER JOIN proj_task_details d
ON (p.based_on =d.task_id);
C. SELECT p.task_id,p.based_on, d.task_in_charge
FROM proj_task_details pFULL OUTER JOIN proj_task_details d
ON (p.based_on =d.task_id);
D. SELECT p.task_id,p.based_on, d.task_in_charge
FROM proj_task_details pJOIN proj_task_details d
ON (p.task_id =d.task_id);
Answer: B
解析：
showing all task IDs,the corresponding task ID they are dependent on, and
the name of the employeein charge of the task it depends on.
题目的意思不管是否有depend on 都需要排列出来，这时候就需要左外连接
引用官方文档：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer joinreturns all the common column values from the right table in the
FROM clause.
■ A full outer joinreturns all the common column values from both joined tables.
127. Examine the data inthe CUSTOMERS table:
CUSTNO CUSTNAME CITY
1 KING SEATTLE
2 GREEN BOSTON
3 KOCHAR SEATTLE
4 SMITH NEW YORK
You want to list allcities that have more than one customer along with the customer details.
Evaluate the followingquery:
SQL>SELECTc1.custname, c1.city
FROM Customers c1__________________ Customers c2
ON (c1.city=c2.city ANDc1.custname<>c2.custname);
Which two JOIN optionscan be used in the blank in the above query to give the correct output? (Choose
two.)
A. JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. FULL OUTER JOIN
E. RIGHT OUTER JOIN
Answer: AE
解析：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer join returns all the common column valuesfrom the right table in the
FROM clause.
■ A full outer join returns all the common column valuesfrom both joined tables.
128. View the Exhibitsand examine the structures of the CUSTOMERS, SALES, and COUNTRIES
tables.
You need to generate areport that shows all country names, with corresponding customers (if any) and
sales details (if any),for all customers.
Which FROM clause givesthe required result?
A. FROM sales JOINcustomers USING (cust_id)
FULL OUTER JOINcountries USING (country_id);
B. FROM sales JOINcustomers USING (cust_id)
RIGHT OUTER JOINcountries USING (country_id);
C. FROM customers LEFTOUTER JOIN sales USING (cust_id)
RIGHT OUTER JOINcountries USING (country_id);
D. FROM customers LEFTOUTER JOIN sales USING (cust_id)
LEFT OUTER JOINcountries USING (country_id);
Answer: C
解析：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer join returns all the common column values from the righttable in the
FROM clause.
129. View the Exhibitsand examine the structures of the PROMOTIONS and SALES tables.
Evaluate the followingSQL statement:
SQL>SELECTp.promo_id, p.promo_name, s.prod_id
FROM sales s RIGHT OUTERJOIN promotions p
ON (s.promo_id =p.promo_id);
Which statement is trueregarding the output of the above query?
A. It gives the detailsof promos for which there have been sales.
B. It gives the detailsof promos for which there have been no sales.
C. It gives details ofall promos irrespective of whether they have resulted in a sale or not.
D. It gives details ofproduct ID s that have been sold irrespective of whether they had a promo ornot.
Answer: C
解析：
引用官方文档：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer join returns all the common column values from the righttable in the
FROM clause.
130. View the Exhibitand examine the data in the EMPLOYEES table:
You want to display allthe employee names and their corresponding manager names.
Evaluate the followingquery:
SQL> SELECTe.employee_name "EMP NAME", m.employee_name "MGR NAME"
FROM employees e______________ employees m
ON e.manager_id =m.employee_id;
Which JOIN option can beused in the blank in the above query to get the required output?
A. o nly inner JOIN
B. only FULL OUTER JOIN
C. only LEFT OUTER JOIN
D. only RIGHT OUTER JOIN
Answer: C
解析：
引用官方文档：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer join returns all the common column values from the righttable in the
FROM clause.
131. View the Exhibitand examine the structure of the PRODUCT, COMPONENT, and PDT_COMP
tables.
In PRODUCT table, PDTNOis 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 referencing
PDTNO in PRODUCT tableand COMPNO is the foreign key referencing the COMPNO in COMPONENT
table.
You want to generate areport listing the product names and their corresponding component names, ifthe
component names andproduct names exist.
Evaluate the followingquery:
SQL>SELECTpdtno,pdtname, compno,compname
FROM product _____________pdt_comp
USING (pdtno)____________ component USING(compno)
WHERE compname IS NOTNULL;
Which combination ofjoins used in the blanks in the above query gives the correct output?
A. JOIN; JOIN
B. FULL OUTER JOIN; FULLOUTER JOIN
C. RIGHT OUTER JOIN; LEFTOUTER JOIN
D. LEFT OUTER JOIN;RIGHT OUTER JOIN
Answer: C
解析：
题意：if the componentnames and product names exist. 存在就列出来
引用官方文档：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer joinreturns all the common column values from the right table in the
FROM clause.
132. View the Exhibitand 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 eachproduct ID and the number of times it has been sold.
Evaluate the followingquery:
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 optionscan be used in the blank in the above query to get the required output? (Choose
two.)
A. JOIN
B. FULL OUTER JOIN
C. LEFT OUTER JOIN
D. RIGHT OUTER JOIN
Answer: BC
解析：
题意：You want to listeach product ID and the number of times it has been sold，需要列出每个product id 所以需要将 products表中全部列查询
引用官方文档：
■ A left outer joinreturns all the common column values from the left table in the
FROM clause.
■ A right outer joinreturns all the common column values from the right table in the
FROM clause.
■ A full outer joinreturns all the common column values from both joined tables.
133. Which twostatements are true regarding subqueries? (Choose two.)
A. A subquery canretrieve zero or more rows.
B. Only two subqueriescan be placed at one level.
C. A subquery can beused only in SQL query statements.
D. A subquery can appearon either side of a comparison operator.
E. There is no limit onthe number of subquery levels in the WHERE clause of a SELECT statement.
Answer: AD
解析：
引用官方文档：
A subquery answers multiple-partquestions 所以A选项正确
B选项，可以有多个子查询在同一级上 所以B选项错误
C选项，create table zbcxy as select * from emp wheresal>(select avg(sal) from emp );
所以C选项错误
D选项 在运算符那一边结果一样 所以正确
E选项，引用官方文档：A subquery can contain another subquery. Oracle Databaseimposes no limit on the number of subquery levels in the FROM clause of the top-levelquery. You can nest up to 255 levels of subqueries in the WHERE clause.
所以E选项错误
134. Where cansubqueries be used? (Choose all that apply.)
A. field names in theSELECT statement
B. the FROM clause inthe SELECT statement
C. the HAVING clause inthe SELECT statement
D. the GROUP BY clausein the SELECT statement
E. the WHERE clause inonly the SELECT statement
F. the WHERE clause inSELECT as well as all DML statements
Answer: ABCF
解析：
D选项，group by 后面只能用列名
F选项，DML上也可以使用
135. Which threestatements are true regarding subqueries? (Choose three.)
A. Subqueries cancontain GROUP BY and ORDER BY clauses.
B. Main query andsubquery can get data from different tables.
C. Main query and subquerymust get data from the same tables.
D. Subqueries cancontain ORDER BY but not the GROUP BY clause.
E. Only one column orexpression can be compared between the main query and subquery.
F. Multiple columns orexpressions can be compared between the main query and subquery.
Answer: ABF
解析：
C选项，select * from dept where deptno=(select deptno from empwhere empno=7788);
D选项，select * from emp where deptno in (select deptno from empgroup by deptno);
E选项，select * from emp where deptno in (select deptno from empgroup by deptno);
136. View the Exhibitand examine the structure of the PRODUCTS table.
Which two tasks wouldrequire subqueries? (Choose two.)
A. Display the minimumlist price for each product status.
B. Display all supplierswhose list price is less than 1000.
C. Display the number ofproducts whose list price is more than the average list price.
D. Display the totalnumber of products supplied by supplier 102 and have product status as'obsolete'.
E. Display all productswhose minimum list price is more than the average list price of products andhave
the status 'orderable'.
Answer: CE
解析：
C选项，查询平均价格作为子查询
E选项，the average list price of products and have the status'orderable'需要作为子查询
137. View the Exhibitsand examine PRODUCTS and SALES tables.
You issue the followingquery to display product name and the number of times the product has been
sold:
SQL>SELECTp.prod_name, i.item_cnt
FROM (SELECT prod_id,COUNT(*) item_cnt
FROM sales
GROUP BY prod_id) iRIGHT OUTER JOIN products p
ON i.prod_id =p.prod_id;
What happens when theabove statement is executed?
A. The statementexecutes successfully and produces the required output.
B. The statementproduces an error because ITEM_CNT cannot be displayed in the outer query.
C. The statement producesan error because a subquery in the FROM clause and outer-joins cannot be
used together.
D. The statementproduces an error because the GROUP BY clause cannot be used in a subquery inthe
FROM clause.
Answer: A
解析：
将 SELECT prod_id, COUNT(*) item_cnt
FROM sales
GROUP BY prod_id
的查询结果作为一张表和product表右外连接 ，可以得到product中每个产品的销售次数
138. Which statement istrue regarding subqueries?
A. The LIKE operatorcannot be used with single- row subqueries.
B. The NOT IN operatoris equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALLoperators have the same functionality in multiple- row subqueries.
D. The NOT operator canbe used with IN, ANY, and ALL operators in multiple- row subqueries.
Answer: D
解析：
A选项，测试： scott@ORCL>select * from emp where ename like (selectename from emp where empno=7788);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-04-87 1850 20
B选项，不相等，not in 如果不处理子查询中的空值，就会全部返回为空
C选项，功能不一样，any表示已任意一个，all表示全部
139. Which threestatements are true about multiple-row subqueries? (Choose three.)
A. They can contain asubquery within a subquery.
B. They can returnmultiple columns as well as rows.
C. They cannot contain asubquery within a subquery.
D. They can return onlyone column but multiple rows.
E. They can containgroup functions and GROUP BY and HAVING clauses.
F. They can containgroup functions and the GROUP BY clause, but not the HAVING clause.
Answer: ABE
解析：
C选项，子查询可以嵌套
应用官方文档：
A subquery can containanother subquery. Oracle Database imposes no limit on the
number of subquerylevels in the FROM clause of the top-level query. You can nest up
to 255 levels ofsubqueries in the WHERE clause
D选项，可以返回多列多行
F选项，测试：
scott@ORCL>select *from emp where job in(select job from emp group by job having job like 'S%');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-02-81 1600 160 30
140. Examine thestructure of the PRODUCTS table:
name Null Type
PROD_ID NOT NULLNUMBER(4)
PROD_NAME VARCHAR2(20)
PROD_STATUS VARCHAR2(6)
QTY_IN_HAND NUMBER(8,2)
UNIT_PRICE NUMBER(10,2)
You want to display thenames of the products that have the highest total value for UNIT_PRICE *
QTY_IN_HAND.
Which SQL statementgives 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
解析：
(SELECT MAX(unit_price* qty_in_hand) FROM products) 得到unit_price *qty_in_hand的最大值
SELECT prod_name
FROM products
WHERE (unit_price* qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)
FROM products);
(unit_price *qty_in_hand) =最大值 所以能得到正确的结果