Answered>Order 27170

What would be the best answer for the following MySQL syntax and why?

1) A query to formulate a report that shows the total number of orders by customer since December 31, 1996 and which returns rows for which the NumOrders is greater than 15 could be:

• Selected:
• a.
• SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
• FROM Customers c 
• JOIN Orders o 
• ON
•                (c.CustomerID = o.CustomerID)
• WHERE 
•                OrderDate >= ‘1996-12-31’
• GROUP BY 
•                c.CompanyName
• HAVING 
•                COUNT (o.OrderID) > 15
• ORDER BY 
•                NumOrders DESC;This answer is incorrect.
• b.
• SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
• FROM Customers c 
• JOIN Orders o 
• ON
•                (c.CustomerID = o.CustomerID)
• WHERE 
•                OrderDate > ‘1996-12-31’
• GROUP BY 
•                c.CompanyName
• HAVING 
•                COUNT (o.OrdrID) > 15
• ORDER BY 
•                NumOrders DESC;
• c.
• SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
• FROM Customers c 
• JOIN Orders o 
• ON
•                (c.CustomerID = o.CustomerID)
• WHERE 
•                OrderDate > ‘1996-12-31’
• GROUP BY 
•                c.CompanyName
• HAVING 
•                COUNT (o.OrderID) > 15
• ORDER BY 
•                NumOrders DESC;
• d.
• SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
• FROM Customers c 
• JOIN Orders o 
• ON
•                (c.CustomerID = o.CustomerID)
• WHERE 
•                OrderDate > ‘1996-12-31’
• GROUP BY 
•                c.CompanyName
• ORDER BY 
•                NumOrders DESC;

2) A query to show sales figures by categories could be:

• Selected:
• a.
• select CategoryName, format(sum(ProductSales), 2) as CategorySales
• from
• (
•    select distinct a.CategoryName, 
•        b.ProductName, 
•        format(sum(c.UnitPrice * c.Quantity * (1 – c.Discount)), 2) as ProductSales,
•        concatenate(‘Qtr ‘, quarter(d.ShippedDate)) as ShippedQuarter
•    from Categories as a
•    inner join Products as b on a.CategoryID = b.CategoryID
•    inner join Order_Details as c on b.ProductID = c.ProductID
•    inner join Orders as d on d.OrderID = c.OrderID 
•    where d.ShippedDate between date(‘1997-01-01’) and date(‘1997-12-31’)
•    group by a.CategoryName, 
•        b.ProductName, 
•        concat(‘Qtr ‘, quarter(d.ShippedDate))
•    order by a.CategoryName, 
•        b.ProductName, 
•        ShippedQuarter
• ) as x
• group by CategoryName
• order by CategoryName;This answer is incorrect.
• b.
• select CategoryName, format(sum(ProductSales), 2) as CategorySales
• from
• (
•    select distinct a.CategoryName, 
•        b.ProductName, 
•        format(sum(c.UnitPrice * c.Quantity * (1 – c.Discount)), 2) as ProductSales,
•        concat(‘Qtr ‘, quarter(d.ShippedDate)) as ShippedQuarter
•    from Categories as a
•    inner join Products as b on a.CategoryID = b.CategoryID
•    inner join Order_Details as c on b.ProductID = c.ProductID
•    inner join Orders as d on d.OrderID = c.OrderID 
•    where d.ShippedDate between date(‘1997-01-01’) and date(‘1997-12-31’)
•    group by a.CategoryName, 
•        b.ProductName, 
•        concat(‘Qtr ‘, quarter(d.ShippedDate))
•    order by a.CategoryName, 
•        b.ProductName, 
•        ShippedQuarter
• ) as x
• group by CategoryName
• order by CategoryName;
• c.
• select CategoryName, format(sum(ProductSales), 2) as CategorySales
• from
• (
•    select distinct a.CategoryName, 
•        b.ProductName, 
•        format(sum(c.UnitPrice * c.Quantity * (1 – c.Discount)), 2) as ProductSales,
•        concat(‘Qtr ‘, quarter(d.ShippedDate)) as ShippedQuarter
•    from Categories as a
•    inner join Products as b on a.CategoryID = b.CategoryID
•    inner join Order_Details as c on b.ProductID = c.ProductID
•    where d.ShippedDate between date(‘1997-01-01’) and date(‘1997-12-31’)
•    group by a.CategoryName, 
•        b.ProductName, 
•        concat(‘Qtr ‘, quarter(d.ShippedDate))
•    order by a.CategoryName, 
•        b.ProductName, 
•        ShippedQuarter
• ) as x
• group by CategoryName
• order by CategoryName;
• d.
• select CategoryName, format(sum(ProductSales), 2) as CategorySales
• from
• (
•    select distinct a.CategoryName, 
•        b.ProductName, 
•        format(sum(c.UnitPrice * c.Quantity * (1 – c.Discount)), 2) as ProductSales,
•        concat(‘Qtr ‘, quarter(d.ShippedDate)) as ShippedQuarter
•    from Categories as a
•    inner join Products as b on a.CategoryID = b.CategoryID
•    inner join Order_Details as c on b.ProductID = c.ProductID
•    inner join Orders as d on d.OrderID = c.OrderID 
•    where d.ShippedDate between date(‘1997-01-01’) and date(‘1997-12-31’)
•    group by a.CategoryName, 
•        b.ProductName, 
•        concat(‘Qtr ‘, qtr(d.ShippedDate))
•    order by a.CategoryName, 
•        b.ProductName, 
•        ShippedQuarter
• ) as x
• group by CategoryName
• order by CategoryName;

Try again. See Module Four, Page IV.