1 / 28

SQL1-ch6

SQL1-ch6. 子查詢. 題號. 80 題： 46 、 53 、 43 、 75 140 題： 33 、 37 、 54 、 60 、 84 、 102 、 124 、 126. 需要子查詢的原因. 子查詢的特點. 可將子查詢放於 Where 、 Having 、 From 、 Order by 子查詢回傳結果為空時，主查詢仍可執行. 子查詢的種類. 分為單列與多列子查詢. 單列子查詢. Q84/140. A subquery is called a single-row subquery when ____.

hachi
Télécharger la présentation

SQL1-ch6

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL1-ch6 子查詢

  2. 題號 • 80題：46、53、43、75 • 140題：33、37、54、60、 84、102、124、126

  3. 需要子查詢的原因

  4. 子查詢的特點 • 可將子查詢放於Where、Having、From、Order by • 子查詢回傳結果為空時，主查詢仍可執行

  5. 子查詢的種類 • 分為單列與多列子查詢

  6. 單列子查詢

  7. Q84/140 A subquery is called a single-row subquery when ____. A. the inner query returns a single value to the main query B. the inner query uses an aggregate function and returns one or more values C. there is only one inner query in the main query and the inner query returns one or more values D. the inner query returns one or more values and the main query returns a single value as output

  8. 多列子查詢 • <ANY即小於最大值 • >ALL即大於最小值 • =ANY即相當於IN

  9. Q46/80 Which two statements are true regarding operators used with subqueries? (Choose two.) A. the NOT IN operator is equivalent to IS NULL. B. The <ANY operator means less than the maximum. C. =ANY and =ALL operators have the same functionality. D. the IN operator cannot be used in single-row subqueries. E. the NOT operator can be used with IN, ANY and ALL operators.

  10. Q75/80 You want to display the names and salaries of only those employees who earn the highest salaries in their departments. Which two SQL statements give the required output? (Choose two.)

  11. A. SELECT ename, sal FROM emp e WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = e.deptno); B. SELECT ename, sal FROM emp WHERE sal = ALL (SELECT MAX(sal) FROM emp GROUP BY deptno); C. SELECT ename, sal FROM emp e WHERE EXISTS (SELECT MAX(sal) FROM emp WHERE deptno = e.deptno); D. SELECT ename, sal FROM emp NATURAL JOIN (SELECT deptno, MAX(sal) sal FROM emp GROUP BY deptno);

  12. 練習 • 找出員工資料表中，薪資大於部門50最高薪資的所有員工姓名、部門編號與薪資。

  13. Q53/80 View the Exhibit and examine the structure of the ORDER_ITEMS table. You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. Which query would produce the desired output?

  14. A. SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id; B. SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id; C. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id); D. SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);

  15. Q73/80 View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query: SQL>SELECT last_name, hire_date FROM employees WHERE (department_id, hire_date) IN (SELECT department_id, MAX(hire_date) FROM employees JOIN departments USING(department_id) WHERE location_id = 1700 GROUP BY department_id);

  16. What is the outcome? A. It executes but does not give the correct result B. It executes successfully and gives the correct result C. It generates an error because of the pairwise comparison D. It generates an error because the GROUP BY clause cannot be used with table joins in a subquery

  17. Q126/140(可略) Which two statements are true regarding operators used with subqueries? (Choose two.) A. The NOT IN operator is equivalent to IS NULL. B. The < maximum. the than less means operator> C. =ANY and =ALL operators have the same functionality. D. The IN operator cannot be used in single-row subqueries. E. The NOT operator can be used with IN, ANY and ALL operators.

  18. Q33/140 View the Exhibit and examine the data in the PRODUCT_INFORMATION table. Which two tasks would require subqueries? (Choose two.)

  19. A.displaying the minimum list price for each product status B.displaying all supplier IDs whose average list price is more than 500 C.displaying the number of products whose list prices are more than the average list price D.displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable E.displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE

  20. Q102/140 Which three statements are true regarding subqueries? (Choose three.) A. The ORDER BY clause can be used in the subquery. B. A subquery can be used in the FROM clause of a SELECT statement. (P6-4) C. If the subquery returns NULL, the main query may still return result rows. D. A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause. (P6-4) E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

  21. Q124/140 View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement: SELECT order_id, product_id, unit_price FROM order_items WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id); You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this?

  22. SELECT order_id, product_id, unit_price FROM order_items WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id); A. Replace = with the IN operator. B. Replace = with the >ANY operator. C. Replace = with the >ALL operator. D. Remove the GROUP BY clause from the subquery and place it in the main query.

  23. Q60/140 View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. Which two queries would work? (Choose two.) A. SELECT product_name FROM product_information WHERE list_price = (SELECT AVG(list_price) FROM product_information); B. SELECT product_status FROM product_information GROUP BY product_status WHERE list_price < (SELECT AVG(list_price) FROM product_information);

  24. C. SELECT product_status FROM product_information GROUP BY product_status HAVING list_price > (SELECT AVG(list_price) FROM product_information); D. SELECT product_name FROM product_information WHERE list_price < ANY(SELECT AVG(list_price) FROM product_information GROUP BY product_status); 非聚總資料

  25. Q37/140 View the Exhibit and examine the structure of the ORDERS table. You have to display ORDER_ID and ORDER_DATE for all those orders that were placed after the last orderplaced by the customer whose CUSTOMER_ID is 101. Which query would give you the desired output?

  26. A.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101; B.SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROM orders WHERE customer_id = 101); C.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101); D.SELECT order_id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer_id = 101);

More Related