071-新增2

4. View the Exhibit and examine the description for the PRODOCTS and SALES table. PROD_ ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove 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) DELETE FROM products WHERE prod_id=(SELECT prod id FROM sales WHERE time_ id - 3365=SYSDATE);
   B) DELETE FROM products WHERE prod_ id=(SELECT prod id FROM sales WHERE SYSDATE>= time_ id - 3365);
   C) DELETE FROM products WHERE prod_ id IN (SELECT prod id FROM sales WHERE SYSDATE - 3*365 >= time_ id);
   D) DELETE FROM products WHERE prod_id IN (SELECT prod_ id FROM sales WHERE time. id >=SYSDATE - 3*365);

5. View the Exhibit and examine the data n the PRODUCT_INFORMATION TABLE. Which two tasks would require subqueries? (Chose two.)
   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
   询问使用子查询的语句

6. View the Exhibit and examine the structure of the ORDERS table. Which UPDATE statement is valid?
   A) UPDATE orders SET order_ date = ‘12 -mar-2007’, order_ total IS NULL WHERE order_id = 2455;
   B) UPDATE orders SET order_ date = ‘12 -mar-2007’, order_ total = NULL WHERE order_id = 2455;
   C) UPDATE orders SET order_ date = ‘12 -mar-2007’, AND order_ total =TO_NUMBER(NULL) WHERE order_id = 2455;
   D) UPDATE orders SET order_ date = To DATE(‘12-mar-2007’,‘dd-mon-yyyy’), SET order_total = To_ NUMBER(NULL) WHERE order_id = 2455;
   要注意更新的语法，在更新里 NULL 也得遵守使用=