What would be the best answer for the following MySQL syntax and why? 1) A query
ID: 3748967 • Letter: W
Question
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.
Explanation / Answer
1 - c
A query to formulate a report that shows the total number of orders by a customer since December 31, 1996, and which returns rows for which the NumOrders is greater than 15 could be:
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;
because when we look at the query we are said to list down the total number of orders by a customer so we are using order id to get any unique order and its details. Since we are using both tables customer and order, we are joining Table customer and Order using the JOIN(types - inner and outer) keyword. Since this is an inner join(all elements are considered here) we need to filter data and the ON keyword does that. It displays the result for only those rows which have the customer id from customer column and order column same. Now, as per the condition, the OrderDate should be since Dec 31, 1996, the WHERE clause does this work for us. Now, we are said to list the rows which have numOrders greater than 15. Hence, the HAVING and WHEREBY clause come into play.
2 - d
A query to show sales figures by categories could be:
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;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.