Please write queries based on the following requirements using the Microsoft Acc
ID: 3679861 • Letter: P
Question
Please write queries based on the following requirements using the Microsoft Access database "Premier Database" (link below; no viruses I promise :) ). Premier Database can be downloaded from (https://www.dropbox.com/s/znbnovyvsawu8w7/Premier.accdb?dl=0). Please only submit SQL query code. You do NOT need to submit your query results.
11.) List the total amount of credit limit for each state with more than 20 members. Restrict the list to only members with a name starts with letter “D”. (Use Members Table)
1.) List all the brand name and the number of products offered by each brand, arrange the results in ascending order on brand name. (Use Products Table) 2.) List the Item Code, Whole Sale Cost, and Catalog Price for all the product of which the product description starts with word “Aloe Vera”. (Use Products Table) 3.) List all the Item code, category, Brand, description, and sales margin. Sales margin = catalog Price-Wholesale Cost. (Use Products Table) 4.) List all the product item codes that were sold in 32oz unit or have a catalog price between $2 and $10. (Use Products Table) 5.) List all the Categories and Brands with 3-character category name. (Use Products Table) 6.) List the total item quantity for each transaction. (Use Transaction_Details Table) 7.) List the total discount for each item in each transaction. (Use Transaction_Details Table) Hint: To test your result, rename Discount% column to Discount. 8.) List earliest customer enrollment date for each state. (Use Members Table) 9.) List the number of Male members and the number of Female members in the state of IN, IL, and IA. (Use Members Table) 10). List the full name and full address of members who have not revealed their email address. (Use Members Table) Hint: To test your result, rename E-mailAddress column to EmailAddress.11.) List the total amount of credit limit for each state with more than 20 members. Restrict the list to only members with a name starts with letter “D”. (Use Members Table)
12.) List the class ID, Class Name, and the total maximum enrollment for each Class, arrange your results in descending order on Class Name. (Use Table Classes) 13.) List the class ID, Class Name, Maximum Enrollment of the courses that are scheduled to start after 8am in IN Office. (Use Table Classes) Hint: use #8:0:0# to use Date datatype. 14.) . List the class ID, Date, Start Time, and End Time of the course with the word “Management” in the Class name. (Use Table Classes) 15.) Show the highest, lowest, and average shipping and handling fee in United States. (Use Taxes_And_Shipping_Fees Table) Hint: To test your result, rename Shipping&Handling column to ShippingAndHandling.Explanation / Answer
1. SELECT BrandName, count(*) AS NumberOfProducts FROM Products ORDERBY BrandName ASC;
2. SELECT WholesaleCost, CateLogPrice, ItemCode FROM Products WHERE ProductDescription LIKE ‘ALOE VERA%’;
3. SELECT ItemCode, Category, Brand, ProductDescription, (CateLogPrice_WholesaleCost) AS Salesmargin FROM Products;
4. SELECT ItemCode FROM Products WHERE Sold = “32oz” AND CateLogPrice BETWEEN $2 AND $10;