IOE 373 Lecture 13: IOE373_Lab9

• School: U of M
• Department: Industrial And Operations Engineering
• Course: IOE 373
• Professor: Luis Garcia- Guzman

Document Summary

Querying in sql to create tables for analysis. Iif ( boolean_expression, true_value, false_value : using inner join and and together, group by with aggregates. Using the access file puchasesdatabase_lab9 in ctools, complete the following queries. Then, submit each of the queries to ctools in a word document: you want to analyze spending by payment type. In order to do this, create a sql query which gives you the count of transactions between 0 and 10, the count of transactions between 10 and. 100, the count of transactions between 100 and 1000, the count of transactions over 1000, the number of purchases, and the total revenue for each payment type in ascending order of payment type. (numtransactionbyamountrange) Select paymenttype, sum(iif(0 <= totalprice and totalprice < 10, 1, 0)) as cnt_0_10_usd, Sum(iif(10 <= totalprice and totalprice < 100,1,0)) as cnt_10_100usd, sum(iif(100 <= totalprice and totalprice < 1000,1,0)) as cnt_100_1000usd, sum(iif(totalprice >= 1000,1,0)) as cnt_1000usd, count(*) as cnt, format(sum(totalprice),currency) as revenue.