071-新增2

16. View the Exhibit and examine the structure of the EMPLOYEES table You want to select all employees having 100 as their MANAGER ID managers and their manager. You want the output in two columns: the first column should have the employee’s manager’s LAST_ NAME and the second column should have the employee’s LAST NAME. Which SQL statement would you use?
    A) SELECT m.last_ name “Manager”, e.last_ name “Employee” FROM employees m JOIN employees e ON m.employee_id=e.manager_id WHERE m.manager_ id=100;
    B) SELECT m.last_ name “Manager”, e.last_ name “Employee” FROM employees m JOIN employees e ON m.employee_id=e.manager_id WHERE e.manager_ id=100;
    C)SELECT m.last_ name “Manager”, e.last_ name “Employee” FROM employees m JOIN employees e ON e.employee_id=m.manager_id WHERE m.manager_ id=100;
    D) SELECT m.last_ name “Manager”, e.last_ name “Employee” FROM employees m JOIN employees e WHERE m.employee_id=e.manager_id AND m.manager_ id=100;

JOIN 等价于 INNER JOIN，表 m 看作是管理员表，表 e 是员工表，m 和 e 的关系是 1 对 多，根据提纲，第一列显示管理员的姓，第二列显示员工的姓。如果一个员工是管理员，那 么他的员工 ID（employee_id）就是其下属的管理员 ID（manager_id），管理员表和员 工表的关联条件为 m.employee_id = e.manager_id，筛选条件为 e.manager_id = 100。

17. Examine the command to create the BOOKS table SQL>CREATE TABLE books (book_id CHAR(6) PRIMARY KEY, title VARCHAR2 (100) NOT NULL, publisher_ id VARCHAR2 (4), author_ id VARCHAR2 (50)); The BOOK_ ID value 101 does not exist in the table. Examine the SQL statement:
    SQL> INSERT INTO books(BOOK_ ID, TITLE, AUTHOR_ ID) VALUES (‘101’, ‘LEARNING SQL’, ‘Tim Jones’);
    Which statement is true?
    A) It executes successfully and the row is inserted with a null PUBLISHER_ ID.
    B) It executes successfully only if NULL is explicitly specified in the INSERT statement.
    C) It executes successfully only if the PUBLISHER_ ID column name is added to the columns list in the INSERT statement.
    D) It executes successfully only if the PUBLISHER_ ID column name is added to the columns list and NULL is explicitly specified in the INSERT statement.
18. View the Exhibit and examine the details of the ORDER ITEMS table.Evaluate the following SQL statements: Statement 1: SELECT MAX (unit_ price*quantity) “Maximum Order” FROM order items ;
    Statement 2: SELECT MAX (unit_ price *quantity) “Maximum Order” FROM order_ items GROUP BY order_id; Which statements are true regarding the output of these SQL statements? (Choose all that apply.)
    
    A) Statement 1 would return only one row of output.
    B) Both the statements would give the same output.
    C) Statement 2 would return multiple rows of output.
    D) Statement 1 would not return any row because the GROUP BY clause is missing.
    E) Both statements would ignore NULL values for the UNIT PRICE and QUANT LTY columns.

分组函数的规则