need question 1-9 and question 13 done
Chapter 9 Advanced Query Formulation with SQL 367 2017 2. Using a Type Il nested query, list the customer number, name (first and last), and city of each customer who has a balance greater than $150 and placed an order in February 2017. 3. Using two Type I nested queries, list the product number, the name, and the price of products with a price greater than $150 that were ordered on January 23, 4. Using two Type I nested queries and another join style, list the product number, name, and price of products with a price greater than $150 that were ordered in January 2017 by customers with balances greater than $400. 5. List the order number, order date, employee number, and employee name (first and last) of orders placed on January 23, 2017. List the order even if there is not 6. List the order number, order date, employee number, employee name (first and last), customer number, and customer name (first and last) of orders placed on January 23, 2017. List the order even if there is not an associated 7. List all the people in the database. The resulting table should have all columns of the Customer and Employee tables. Match the Customer and Employee tables on first and last names. If a customer does not match any employees, the columns pertaining to the Employee table will be blank. Similarly for an employee who does not match any customers, the columns pertaining to the Customer table will an associated employee. employee. be blank. . For each Ink Jet product ordered in January 2017, list the order number, order date, customer number, customer name (first and last), employee number (if present), employee name (first and last), quantity ordered, product number, and product name. Include products containing Ink Jet in the product name. Include both Internet (no employee) and phone orders (taken by an employee). 9. Using a Type II nested query, list the customer number and name of Colorado customers who have not placed orders in February 2017. 10. Repeat problem 9 using a Type I nested query with a NOT IN condition instead of a nested query. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. 11. Repeat problem 9 using the MINUS keyword. Note that Access does not support the MINUS keyword. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. 12. Repeat problem 9 using a one-sided outer join and an IS NULL condition. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. 13. Using a Type II nested query, list the employee number, first name, and last name of employees in the (720) area code who have not taken orders. An employee is in the (720) area code if the employee phone number contains the string (720) in the beginning of the column value. 14. Repeat problem 13 using a Type I nested query with a NOT IN condition instead of a nested query. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. (Hint: you need to think carefully about the effect of null values in the OrderTbl.EmpNo column.) 15. Repeat problem 9 using a one-sided outer join and an IS NULL condition. If the problem cannot be formulated in this manner, provide an explanation indicating 16. Repeat problem 9 using the MINUS keyword. Note that Access does not support the MINUS keyword. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. the reason. The problems use the tables of the Order Entry database introduced in the Problems section of Chapter 4. When formulating the problems, remember that the EmpNo fora eign key in the Order Tbl table allows null values. An order does not have an associated employee if taken over the Internet. 1. Using a Type I nested query, list the customer number, name (first and last), and February 2017 city of each customer who has a balance greater than $150 and placed an order in oo OrdLine OrdNo * ProdNo 00 Customer * CustNo CustFirstName CustLastName CustStreet CustCity CustState CustZip CustBal Product Y ProdNo ProdName ProdMfg ProdQOH ProdPrice ProdNextShipDate 81 - 81 OrderTb! 8 OrdNO OrdDate CustNo EmpNo OrdName OrdStreet Ord City OrdState Ordzip Employee_1 7 EmpNo EmpFirstName EmplastName EmpPhone SupEmpNo EmpCommRate EmpEmail Employee EmpNo EmpFirstName EmplastName EmpPhone SupEmpNo EmpCommRate EmpEmail Chapter 9 Advanced Query Formulation with SQL 367 2017 2. Using a Type Il nested query, list the customer number, name (first and last), and city of each customer who has a balance greater than $150 and placed an order in February 2017. 3. Using two Type I nested queries, list the product number, the name, and the price of products with a price greater than $150 that were ordered on January 23, 4. Using two Type I nested queries and another join style, list the product number, name, and price of products with a price greater than $150 that were ordered in January 2017 by customers with balances greater than $400. 5. List the order number, order date, employee number, and employee name (first and last) of orders placed on January 23, 2017. List the order even if there is not 6. List the order number, order date, employee number, employee name (first and last), customer number, and customer name (first and last) of orders placed on January 23, 2017. List the order even if there is not an associated 7. List all the people in the database. The resulting table should have all columns of the Customer and Employee tables. Match the Customer and Employee tables on first and last names. If a customer does not match any employees, the columns pertaining to the Employee table will be blank. Similarly for an employee who does not match any customers, the columns pertaining to the Customer table will an associated employee. employee. be blank. . For each Ink Jet product ordered in January 2017, list the order number, order date, customer number, customer name (first and last), employee number (if present), employee name (first and last), quantity ordered, product number, and product name. Include products containing Ink Jet in the product name. Include both Internet (no employee) and phone orders (taken by an employee). 9. Using a Type II nested query, list the customer number and name of Colorado customers who have not placed orders in February 2017. 10. Repeat problem 9 using a Type I nested query with a NOT IN condition instead of a nested query. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. 11. Repeat problem 9 using the MINUS keyword. Note that Access does not support the MINUS keyword. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. 12. Repeat problem 9 using a one-sided outer join and an IS NULL condition. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. 13. Using a Type II nested query, list the employee number, first name, and last name of employees in the (720) area code who have not taken orders. An employee is in the (720) area code if the employee phone number contains the string (720) in the beginning of the column value. 14. Repeat problem 13 using a Type I nested query with a NOT IN condition instead of a nested query. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. (Hint: you need to think carefully about the effect of null values in the OrderTbl.EmpNo column.) 15. Repeat problem 9 using a one-sided outer join and an IS NULL condition. If the problem cannot be formulated in this manner, provide an explanation indicating 16. Repeat problem 9 using the MINUS keyword. Note that Access does not support the MINUS keyword. If the problem cannot be formulated in this manner, provide an explanation indicating the reason. the reason. The problems use the tables of the Order Entry database introduced in the Problems section of Chapter 4. When formulating the problems, remember that the EmpNo fora eign key in the Order Tbl table allows null values. An order does not have an associated employee if taken over the Internet. 1. Using a Type I nested query, list the customer number, name (first and last), and February 2017 city of each customer who has a balance greater than $150 and placed an order in oo OrdLine OrdNo * ProdNo 00 Customer * CustNo CustFirstName CustLastName CustStreet CustCity CustState CustZip CustBal Product Y ProdNo ProdName ProdMfg ProdQOH ProdPrice ProdNextShipDate 81 - 81 OrderTb! 8 OrdNO OrdDate CustNo EmpNo OrdName OrdStreet Ord City OrdState Ordzip Employee_1 7 EmpNo EmpFirstName EmplastName EmpPhone SupEmpNo EmpCommRate EmpEmail Employee EmpNo EmpFirstName EmplastName EmpPhone SupEmpNo EmpCommRate EmpEmail
