Tasks:
Referring to narrative section 4.4.1.2. “Operations Database” your group will build a database, in the DBMS of your choosing, containing the tables found within the Order and Product Schemas. Ensure each of the tables within the aforementioned schemas contain, at a minimum, 10 records each. Ensure that each of your records adheres to the database data specifications found within your narrative file. Do not share your dataset with any other group, doing so will result in a grade of 0 for this assessment. You will then complete the SQL queries within this assessment file submitting both the SQL statements and screenshots of your output following the formatting standards outlined at the end of this document. Additionally, a simple template “Group Assessment - Template - SQL Queries & Output.docx” has been made available for your submission, it is available in your assessment module on eConestoga.
You are required to write a single SQL statement for each of the following questions:
Query 1:
Within the PRODUCT schema, show all records from the Product.Category table.
Query 2:
Within the ORDER schema, show all data from the CustomerOrder and ShipmentMethod tables: - Only show records where there are row matches between the PK and FK fields - The join of equality (“equijoin”) must be listed in the WHERE statement
Query 3:
Within the ORDER schema, without using a WHERE statement, write a single SQL statement that shows the following data: - From the CustomerOrder table, show the OrderID and TotalPaid - From the ShipmentMethod table, show the shipment mode. - Hint, you’ll need to make use of the ‘ON’ command
Query 4:
Within the ORDER schema, display all orders from the CustomerOrder table that have a total amount paid that is less than $1,000 Canadian dollars. - Display all the fields from the CustomerOrder table in your output
Query 5:
Within the ORDER schema, display shipment method records where Estimated date shipped is the same as the actual date shipped.
Query 6:
Within the ORDER schema, list the OrderID for all orders that have an order status of either in transit, or delayed.
Query 7:
Within the PRODUCT schema, list the description for all products that have a product review rating of 4 or higher
Query 8:
Within the ORDER schema, list the total number of orders grouped by the different types of shipment modes
Query 9:
Within the PRODUCT and ORDER schema, list all product details for products that have are not included on any order
Query 10:
Within the ORDER schema, Display OrderID and CustomerID for any orders that have an actual shipping date that occurred in the month of September.
Query 11:
Within the ORDER schema, Display OrderID and CustomerID for any orders that have an estimated shipping date that occurred in the year of 2021.
Query 12:
Within the ORDER schema, Display OrderID and CustomerID for any orders that have an actual shipping date that occurred in the month of October, but did not occur in the Year of 2020
Query 13:
Within the PRODUCT schema, list the item with the greatest (highest) price.
Query 14:
Within the ORDER schema, using only the ProductSelection table, display the three Orders that have the greatest (highest) prices, grouped by OrderID
Query 15:
Within the ORDER schema, count the total number of products that have been purchased on all orders - Do not group orders
Query 16:
Within the ORDER schema, count the number of orders placed by each customer. - The number of items per order is irrelevant - You do not require the use of a JOIN or Subquery
Query 17:
Within the PRODUCT schema, show the product information for any product that has a review rating between 3 and 5 with a product category name of either Perishable or Miscellaneous. - You must utilize one, or more, subqueries for your response - You cannot use a JOIN anywhere within your query
Query 18:
Within the PRODUCT and ORDER schema, show orders that include any products that were packaged in a gift box - You must utilize one, or more, subqueries for your response - You cannot use a JOIN anywhere within your query
Query 19:
Within the ORDER and PRODUCT schema, show any order [OrderID] and Customer [CustomerID] where there is a single item which has a weight greater than 1 kilogram. - Evaluate the weight of each item independently, not the combined weight
Query 20:
Within the ORDER and PRODUCT schemas, for each Customer Order, display the OrderID, CustomerID, ProductID and the Description for each of the products
SQL specific formatting requirements:
All SQL reserved words must be capitalized, bolded, and have a distinct colour
Entities (Tables) and Attributes (Fieldnames) shall be black in text
Text words, and numeric values used for titles, filters, and comparators shall have a distinct colour
You will not be required to list the schema in front of the tables for this assessment as we have not setup schemas in the database yet
To get solution of above queries you can send your requirement details at:
realcode4you@gmail.com