Question

1. Each of the queries in this section contain mistakes that prevent them from running. Explain the mistake and rewrite the query so it works. a. SELECT Customers.custid, Customers.companyname, Orders.orderid, Orders.orderdate FROM Sales.Customers AS C INNER JOIN Sales.Orders AS O ON Customers.custid = Orders.custid b. SELECT C.custid, C.companyname, COUNT(*) as numOrders FROM Sales.Customers as C INNER JOIN Sales.Orders as O WHERE C.custid = O.custid c. SELECT E.empid, COALESCE(COUNT(C.orderid),0) as numOrders FROM HR.Employees as E OUTER JOIN Sales.Customers as C ON E.custid = C.custid;

Answer 1

a.
SELECT Customers.custid, Customers.companyname, Orders.orderid, Orders.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON Customers.custid = Orders.custid

--after aliasing with C, It is not allowed to use Customers.col. It must be C.col... Replace Cusotmers with C
Modified Query:

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid

b.
SELECT C.custid, C.companyname, COUNT(*) as numOrders
FROM Sales.Customers as C
INNER JOIN Sales.Orders as O
WHERE C.custid = O.custid

-- your code would run, but gives you fruiteless results.
--count(*) is of aggregation function which can only used by group by
Modified Query:

SELECT C.custid, C.companyname, COUNT(*) as numOrders
FROM Sales.Customers as C
INNER JOIN Sales.Orders as O
on C.custid = O.custid
group by .custid, C.companyname

c.
SELECT E.empid, COALESCE(COUNT(C.orderid),0) as numOrders
FROM HR.Employees as E
OUTER JOIN Sales.Customers as C
ON E.custid = C.custid;

--coalesce returns the First not null value from the given set. null values are replaced by 0 here.
Modified Query:

SELECT E.empid, COALESCE(COUNT(C.orderid),0) as numOrders
FROM HR.Employees as E
OUTER JOIN Sales.Customers as C
ON E.custid = C.custid
group by E.empid

/*

CustomerID	numberOrders
2	1
3	1
4	2
5	3
7	4
8	1
9	3
10	4
11	1
13	1
14	3

*/