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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Database Concepts
Authors: David Kroenke, David J. Auer
3rd Edition
0131986252, 978-0131986251
