Please write queries based on the following requirements using Premier Database.
ID: 3678939 • Letter: P
Question
Please write queries based on the following requirements using Premier Database. Premier
Database can be downloaded from Blackboard. You are required to submit SQL query code. You
do NOT need to submit your query results. However, it is strongly recommended that you test
your queries in Premier Database to make sure your query is accurate.
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.
here is the link to the database. I swear no virus whatsoever.
https://onedrive.live.com/redir?resid=A1E5259BDD065B48!4477&authkey=!AALECp9dv_C-1gA&ithint=folder%2caccdb
Explanation / Answer
1:- SELECT Brand, Count(*) AS Items FROM PRODUCTS GROUP BY Brand
2:- SELECT ItemCode,WholesaleCost,CatalogPrice FROM PRODUCTS where Description Like 'Aloe Vera*'
3:- SELECT ItemCode,Category,Brand,Description, (CatalogPrice-WholesaleCost) as Salesmargin FROM PRODUCTS
4 :- SELECT ItemCode FROM PRODUCTS where CatalogPrice >= 2 and CatalogPrice <= 10
5 :- SELECT Category,Brand FROM products WHERE LEN(Category) = 3
6:- SELECT TransactionID, Count(Quantity) AS totalitemquantity FROM TRANSACTION_DETAILS GROUP BY TransactionID
7:- SELECT TransactionID, sum(Discount) AS totaldiscount FROM TRANSACTION_DETAILS GROUP BY TransactionID
10 :- select First,LastName, Address, City, State, ZIPCode from MEMBERS where ZIPCode is not null
12:-SELECT ClassID, ClassName, MaxEnrollment FROM CLASSES ORDER BY ClassName DESC
14 :- select ClassID,Date,StartTime,EndTime from CLASSES where ClassName Like '*Management*'