Using the APEX Database, write the appropriate SQL queries to address the following questions.

  • APEX wants a listing of the customer’s name (first and last) ord_no, ord_date, and tot_billed for each sales_order whose tot_bill is greater than the average of tot_bill. (use a sub-select)
  • APEX wants a listing of the ord_no, quantity ordered, product description, and quantity on hand for all order items involving products in the ‘STO’ product category. (Use a natural join for this query).
  • APEX wants a listing of the ord_no, quantity ordered, product description, and quantity on hand for all order items involving products in the ‘STO’ product category. (Use an equi-join with the ON clause for this query).
  • APEX wants a listing of the names (first and last), phone numbers, and credit_card types and numbers associated with them. If a customer does not have a credit card listed we still want to see a record for that customer. (Use a left outer join for this question).
  • APEX wants a listing of the names (first and last), phone numbers, and credit_card types and numbers associated with them. If a customer does not have a credit card listed we still want to see a record for that customer and if there is a credit card that has been used that is not linked to a customer, we want to see that data as well.. (Use a full outer join for this question).
  • A. APEX wants a listing of the Order_Date, Prod_Descr, Qty_Ord, Amt_Billed, and Qty_on_Hand for all orders of products in the IO category. (Must join 3 tables). B. Try to execute this using a natural join, C. add the USING clause to the Natural Join.
  • Apex wants a listing of the description of the assembled product (PC), the description of the component product and the number of units of the component product used for each E_PC_ASSEMBLY record. (use multiple copies of the E_PRODUCT table to retrieve the product descriptions)
  • A, B, and C. APEX wants a listing of Employee name, wage rate, the time they clocked in (start_time displayed as hours and minutes) and the number of hours they worked (end_time – start_time*24 -1) each day for all employees whose wage rate is over 17 dollars an hour. Write this using A. a traditional equi-join, B a natural join, and C an equi-join with the ON Clause.
  • Apex wants a listing of the description of the assembled product (PC), the description and quantity on hand of the component product and the number of units of the component product used for each E_PC_ASSEMBLY record whose component product is in the ‘STO’ product category. (use multiple copies of the E_PRODUCT table to retrieve the product descriptions)
  • APEX wants a listing of the order number, quantity ordered, amount billed, and product description for all order items whose amount billed is more than twice the average amount billed. (Use a sub-query and use a NATURAL JOIN or JOIN USING clause for the outer query).
  • APEX wants a listing of the names and phone numbers of all customers who have purchased products in the ‘IO’ product category. (Use DISTINCT in the column list so that you don’t get the same customer listed more than once, and be sure to include all tables need to link a customer to the product category. A. complete this query using a traditional equi-join. B. Rewrite your query using the JOIN ON or JOIN USING clause.
  • APEX wants a listing of each credit card’s number, expiration date, and card type along with the name of the customer associated with the card for all cards whose expiration date is befor the beginning of 2012. If a card does not have an associated customer, we still want to list the other information about the card. (Use an appropriate outer join.)
  • EXTRA CREDIT (1 point) APEX wants a listing of the name, wage rate, and hours worked (end-time – start_time*24 -1) on December 7th (end-time – start_time*24 -1) of all employees who worked more than the average number of hours worked by employees on the 7th of December, 2010. (use a sub-select and use either the natural join, join using, or join on syntax for the outer join.
Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.