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.