Can you please help me pick up the right answer for the following SQL JOIN Queries?

Question 1

A query to compare the number of customers versus the number of suppliers could be:

a. select CustomersCount, SuppliersCount from (select count(CustomerID) as CustomersCount from customers) as a join (select count(SupplierID) as SuppliersCount from suppliers) as b

b. select a.CustomersCount, c.SuppliersCount from (select count(CustomerID) as CustomersCount from customers) as a join (select count(SupplierID) as SuppliersCount from suppliers) as b

c. select a.CustomersCount, b.SuppliersCount from (select count(Customer) as CustomersCount from customers) as a join (select count(SupplierID) as SuppliersCount from suppliers) as b

d. select a.CustomersCount, b.SuppliersCount from (select count(CustomerID) as CustomersCount from customers) as a join (select count(SupplierID) as SuppliersCount from suppliers) as b Try again. See Module Four, Page IV.

Question 2 What is the output of the following query? SELECT FIELD ('pig', 'dog', 'cat', 'pig') AS Index_Position; a. 0 b. 1 c. 2 d. 3 Correct! The query shows the position of pig in the list {dog,cat,pig}, which is three.

Question 3 A query that lists each employee, their sales amount, broken down by country name could be:

a. select distinct b.* , a.CategoryName from Categories a inner join Products b on a.CategoryID = b.CategoryID where b.Discontinued = 'N' order by b.ProductName;

b. select distinct b.* , a.CategoryName from Categories a inner join Products b on a.CategoryID = b.CategoryID where b.Discontinued = 'Y' order by b.ProductName;

c. select distinct b.* , a.CategoryName from categories a left join products b on a.CategoryID = b.CategoryID where b.Discontinued = 'N' order by b.ProductName;

d. select distinct b.* , a.CategoryName from Categories a inner join Products p on a.CategoryID = b.CategoryID where b.Discontinued = 'N' order by b.ProductName; Try again. See Module Four, Page IV.

Question 4 A query to create 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:

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;

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; Try again. See Module Four, Page II, Table Joins.

Question 5 A query that gets the list of products sold and the total sales amount for each category could be:

a. select distinct a.CategoryID, a.CategoryName, b.ProductName, sum(c.ExtendedPrice) as ProductSales from Categories a inner join Products b on a.CategoryID = b.CategoryID inner join ( select distinct y.OrderID, y.ProductID, x.ProductName, y.UnitPrice, y.Quantity, y.Discount, round(y.UnitPrice * y.Quantity * 1 - y.Discount, 2) as ExtendedPrice from Products x inner join Order_Details y on x.ProductID = y.ProductID order by y.OrderID

b. select distinct a.CategoryID, a.CategoryName, b.ProductName, sum(c.ExtendedPrice) as ProductSales from Categories a inner join Products b on a.CategoryID = b.CategoryID inner join ( select distinct y.OrderID, y.ProductID, x.ProductName, y.UnitPrice, y.Quantity, y.Discount, round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as ExtendedPrice from Products x inner join Order_Details y on x.ProductID = y.ProductID order by y.OrderID )c on c.ProductID = b.ProductID inner join Orders d on d.OrderID = c.OrderID where d.OrderDate between date('1997/1/1') and date('1997/12/31') group by a.CategoryID , a.CategoryName , b.ProductName order by a.CategoryName , b.ProductName , ProductSales;

c. select a.CategoryID, a.CategoryName, b.ProductName, sum(c.ExtendedPrice) as ProductSales from Categories a inner join Products b on a.CategoryID = b.CategoryID inner join ( select distinct y.OrderID, y.ProductID, x.ProductName, y.UnitPrice, y.Quantity, y.Discount, round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as ExtendedPrice from Products x inner join Order_Details y on x.ProductID = y.ProductID order by y.OrderID ) c on c.ProductID = b.ProductID inner join Orders d on d.OrderID = c.OrderID where d.OrderDate between date('1997/1/1') and date('1997/12/31') group by a.CategoryID , a.CategoryName , b.ProductName order by a.CategoryName , b.ProductName , ProductSales;

d. select distinct a.CategoryID, a.CategoryName, b.ProductName, sum(c.ExtendedPrice) as ProductSales from Categories a inner join Products b on a.CategoryID = b.CategoryID inner join ( select distinct y.OrderID, y.ProductID, x.ProductName, y.UnitPrice, y.Quantity, y.Discount, round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as ExtendedPrice from Products x inner join Order_Details y on x.ProductID = y.ProductID order by y.OrderID ) on c.ProductID = b.ProductID inner join Orders d on d.OrderID = c.OrderID where d.OrderDate between date('1997/1/1') and date('1997/12/31') group by a.CategoryID , a.CategoryName , b.ProductName order by a.CategoryName , b.ProductName , ProductSales; Try again. See Module Four, Page IV.

Question 6 A query that shows how to get the year part from Shipped_Date column and which calculates a subtotal by a sub-query for each order and which joins the sub-query to the Orders table could be:

a. select distinct date(a.ShippedDate) as ShippedDate, a.OrderID, b.Subtotal, year_part(a.ShippedDate) as Year from Orders a inner join ( select distinct OrderID, format (sum (UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal from order_details group by OrderID )b on a.OrderID = b.OrderID where a.ShippedDate is not null and a.ShippedDate between date('1996-12-24') and date('1997-09-30') order by a.ShippedDate;

b. select distinct date(a.ShippedDate) as ShippedDate, a.OrderID, b.Subtotal, year(a.ShippedDate) as Year from Orders a inner join ( select distinct OrderID, format (sum (UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal from order_details group by OrderID ) b on a.OrderID = b.OrderID where a.ShippedDate is not null and a.ShippedDate between date('1996-12-24') and date('1997-09-30') order by a.ShippedDate;

c. select distinct date(a.ShippedDate) as ShippedDate, a.OrderID, b.Subtotal, year(a.ShippedDate) as Year from Orders a inner join ( select distinct OrderID, format (sum (UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal from order_details group by OrderID ) b on a.OrderID = b.OrderID where a.ShippedDate is not null or a.ShippedDate between date('1996-12-24') and date('1997-09-30') order by a.ShippedDate;

d. select distinct date(a.ShippedDate) as ShippedDate, a.OrderID, b.Subtotal, year(a.ShippedDate) as Year from Orders a inner join ( select distinct OrderID, format (sum (UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal from order_details group by OrderID ) b on a.OrderID = b.OrderID where a.ShippedDate not is null and a.ShippedDate between date('1996-12-24') and date('1997-09-30') order by a.ShippedDate; Try again. See Module Four, Page IV.

Question 7 Which of the following is not a join type in MySQL? a. inner b. outer c. left d. right Correct! MySQL does not support outer joins.

Question 8 A query that shows how to convert order dates to their corresponding quarters and which demonstrates how the SUM function is used together with a CASE statement to get sales for each quarter, where quarters are converted from OrderDate column could be:

a. select a.ProductName, d.CompanyName, year(OrderDate) as OrderYear, format(sum(case quarter(c.OrderDate) when '1' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 1", format(sum(case quarter(c.OrderDate) when '2' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 2", format(sum(case quarter(c.OrderDate) when '3' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 3", format(sum(case quarter(c.OrderDate) when '4' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 4" from Products a inner join Order_Details b on a.ProductID = b.ProductID inner join Orders c on c.OrderID = b.OrderID inner join Customers d on d.CustomerID = c.CustomerID where c.OrderDate between date('1997-01-01') and date('1997-12-31') order by a.ProductName, d.CompanyName;

b. select a.ProductName, d.CompanyName, year(OrderDate) as OrderYear, format(sum(case quarter(c.OrderDate) then '1' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 1", format(sum(case quarter(c.OrderDate) then '2' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 2", format(sum(case quarter(c.OrderDate) then '3' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 3", format(sum(case quarter(c.OrderDate) then '4' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 4" from Products a inner join Order_Details b on a.ProductID = b.ProductID inner join Orders c on c.OrderID = b.OrderID inner join Customers d on d.CustomerID = c.CustomerID where c.OrderDate between date('1997-01-01') and date('1997-12-31') group by a.ProductName, d.CompanyName, year(OrderDate) order by a.ProductName, d.CompanyName;

c. select a.ProductName, d.CompanyName, year(OrderDate) as OrderYear, format(sum(case quarter(c.OrderDate) when '1' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 1", format(sum(case quarter(c.OrderDate) when '2' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 2", format(sum(case quarter(c.OrderDate) when '3' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 3", format(sum(case quarter(c.OrderDate) when '4' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 4" from Products a inner join Order_Details b on a.ProductID = b.ProductID inner join Orders c on c.OrderID = b.OrderID inner join Customers d on d.CustomerID = c.CustomerID where c.OrderDate between date('1997-01-01') and date('1997-12-31') group by a.ProductName, d.CompanyName, year(OrderDate) order by a.ProductName, d.CompanyName;

d. select a.ProductName, d.CompanyName, year(OrderDate) as OrderYear, format(sum(case quarter(c.OrderDate) when '1' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 1", format(sum(case quarter(c.OrderDate) when '2' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 2", format(sum(case quarter(c.OrderDate) when '3' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 3", format(sum(case quarter(c.OrderDate) when '4' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 4" from Products a inner join Order_Details b on a.ProductID = b.ProductID inner join Orders c on c.OrderID = b.OrderID inner join Customers d on d.CustomerID = c.CustomerID where c.OrderDate between date('1997-01-01') and date('1997-12-31') group by d.CompanyName, year(OrderDate) order by a.ProductName, d.CompanyName; Try again. See Module Four, Page IV.

Question 9 A query to create a report that shows the number of employees and customers from each city that has employees in it could be:

a. SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees, COUNT(DISTINCT c.CustomerID) AS numCompanies, e.City, c.City FROM Employees e RIGHT JOIN Customers c (e.City = c.City) GROUP BY e.City, c.City ORDER BY numEmployees DESC;

b. SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees, DISTINCT c.CustomerID AS numCompanies, e.City, c.City FROM Employees e RIGHT JOIN Customers c ON (e.City = c.City) GROUP BY e.City, c.City ORDER BY numEmployees DESC;

c. SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees, COUNT(DISTINCT c.CustomerID) AS numCompanies, e.City, c.City FROM Customers c RIGHT JOIN Employees e ON (e.City = c.City) GROUP BY e.City, c.City ORDER BY numEmployees DESC;

d. SELECT COUNT(DISTINCT e.EmployeeID) AS numEmployees, COUNT(DISTINCT c.CustomerID) AS numCompanies, e.City, c.City FROM Employees e RIGHT JOIN Customers c ON (e.City = c.City) GROUP BY e.City, c.City ORDER BY numEmployees DESC; Correct! Choice (a) is incorrect because the keyword on is missing. Choice (b) is incorrect because the keyword distinct is missing in the select clause. Choice (c) is incorrect because the from clause is incorrect; the tables are switched.

Question 10 A simple query to get an alphabetical list of active products could be:

a. select distinct b.*, a.Category_Name from Categories a inner join Products b on a.Category_ID = b.Category_ID where b.Discontinued = 'N'

b. select distinct b.*, a.Category_Name from Categories a inner join Products b on a.Category_ID = b.Category_ID order by b.Product_Name;

c. select distinct b.*, a.Category_Name from Categories inner join Products b on a.Category_ID = b.Category_ID where b.Discontinued = 'N' order by b.Product_Name;

d. select distinct b.*, a.Category_Name from Categories a inner join Products b on a.Category_ID = b.Category_ID where b.Discontinued = 'N' order by b.Product_Name; Try again. See Module Four, Page IV.