#6
The count of the number of orders in the Orders table
The sum of the tax_amount columns in the Orders table
The category_name column from the Categories table
The count of the products in the Products table
The list price of the most expensive product in the Products table
Sort the result set so the category with the most products appears first.
The email_address column from the Customers table
The sum of the item price in the Order_Items table multiplied by the quantity in the Order_Items table
The sum of the discount amount column in the Order_Items table multiplied by the quantity in the Order_Items table
Sort the result set in descending sequence by the item price total for each customer.
The email_address from the Customers table
A count of the number of orders
The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)
Return only those rows where the customer has more than 1 order.
Sort the result set in descending sequence by the sum of the line item amounts.
The product name from the Products table
The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity)
Use the WITH ROLLUP operator to include a row that gives the grand total.
Note: Once you add the WITH ROLLUP operator, you may need to use MySQL Workbench’s Execute SQL Script button instead of its Execute Current Statement button to execute this statement.
The email address from the Customers table
The count of distinct products from the customer’s orders
1)
SELECT COUNT(OrderID) as OrderCount,
SUM(TaxAmount) AS TaxTotal
FROM Orders
2)
SELECT CategoryName, COUNT(*) AS ProductCount,
MAX(ListPrice) AS MostExpensiveProduct
FROM Categories c JOIN Products p
ON c.CategoryID = p.CategoryID
GROUP BY CategoryName
ORDER BY ProductCount DESC
3)
SELECT EmailAddress, SUM(ItemPrice * Quantity) AS
ItemPriceTotal,
SUM(DiscountAmount * Quantity) AS DiscountAmountTotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY EmailAddress
ORDER BY ItemPriceTotal DESC
4)
SELECT EmailAddress, COUNT(o.OrderID) AS OrderCount,
SUM((ItemPrice - DiscountAmount) * Quantity) AS OrderTotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY EmailAddress
HAVING COUNT(o.OrderID) > 1
ORDER BY OrderTotal DESC
5)
SELECT EmailAddress, COUNT(o.OrderID) AS OrderCount,
SUM((ItemPrice - DiscountAmount) * Quantity) AS OrderTotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
WHERE ItemPrice > 400
GROUP BY EmailAddress
HAVING COUNT(o.OrderID) > 1
ORDER BY OrderTotal DESC;
6)
SELECT ProductName, SUM((ItemPrice - DiscountAmount) * Quantity) AS
ProductTotal
FROM Products p
JOIN OrderItems oi ON p.ProductID = oi.ProductID
GROUP BY ProductName WITH ROLLUP
7)
SELECT EmailAddress,
COUNT(DISTINCT oi.ProductID) AS NumberOfProducts
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY EmailAddress
HAVING COUNT(DISTINCT oi.ProductID) > 1
ORDER BY EmailAddress
#6 Write a SELECT statement that returns these columns: The count of the number of orders...
5. Write a SELECT statement that returns these columns from the Orders table: OrderID OrderDate ShipDate Return only the rows where the ShipDate column contains a null value. 6. Write a SELECT statement that returns all columns from the Addresses table State is equal to CA 7. Write a SELECT statement that returns all columns from the Products table Product name has a "ru" in its name 8. Write a SELECT statement that returns these columns from the Customers table...
Table structure: administrators = admin_id, email_address, password, first_name, last_name categories = category_id, category_name customers = customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id order_items = item_id, order_id, product_id, item_price, discount_amount, quantity orders = order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id products = product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added 5. Write a SELECT statement that uses regular expression functions to get the username and domain name parts of the email addresses in the Administrators table. Return...
Write a select statement that returns all columns from the orders table, but only returns the five newest (most recent) orders in the orders table as shown by the order_date column
Figure 3-4 Sample Schema co CO PRODUCTS CUSTOMERS product_id customer_id product_name email_address unit_price full_name product_details product_image image_mime_type image_charset image_filename image_last_updated ORDER_ITEMS order_id line_item_id product_id unit_price quantity ORDERS order_id order_datetime customer_id order_status store_id STORES store_id store_name web_address physical address latitude longitude logo logo_mime_type logo_charset logo_filename logo_last_updated | 3. Write one SELECT statement to display ten most popular products (only ten rows) in 2018. The most popular means the highest quantity ordered on COMPLETED orders. Display the product id, product name, and...
SQL - write a select statement that returns four columns: vendorName, invoiceNumber, invoiceDate, and invoiceTotal. Return ONE ROW per vendor, representing the vendors invoice with the earliest date.
WRITE A SQL QUERY SQL SERVER Write a SELECT statement that returns one column from the Customers table named FullName that joins the LastName and FirstName columns. -- Format this column with the last name, a comma, a space, and the first name like this: -- Doe, John -- Sort the result set by last name in ascending sequence. -- Return only the contacts whose last name begins with a letter from M to Z. -- ...
Write a SELECT statement that returns these columns from the INVOICES table: the invoice_total column, a column that uses the ROUND function to return the invoice_total column with 1 decimal digit, a column that uses the ROUND function to return the invoice_total column with no decimal digits, a column that uses TRUNCATE function tom return the invoice_total column with no decimal digits.
1. Write an INSERT statement that adds this row to the Categories table: CategoryName: Brass Code the INSERT statement so SQL Server automatically generates the value for the CategoryID column. 2. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the Category Name column to “Woodwinds”, and it should use the CategoryID column to identify the row. 3.Write an INSERT statement that adds this row to the Products table: ProductID:...
Write a SELECT statement that returns: - Vendor Name - Invoice Number - Invoice Line Item Amount Use a subquery to display only the records that have an Invoice Line Item Amount that is greater than the Average Invoice Line Item Amount. Display in descending Invoice Line Item Amount order.
and blankS iI a SELECT statement that returns these columns from the Invoices Write table 4. The invoice number column The invoice date column The invoice_date column plus 30 days The payment_date column A column named days_to_pay that shows the number of days between the invoice date and the payment date The number of the invoice date's month The four-digit year of the invoice date When you have this working, add a WHERE clause that retrieves just the invoices for...