Question

1. For each query, there are one or more things to do. Some of these queries use SQL constructs we haven't covered in class but that you should be able to figure out the meaning of.

a.

SELECT C.custid, C.companyname

FROM Sales.Customers AS C

LEFT OUTER JOIN Sales.Orders as O

ON C.custid = O.custid

WHERE O.orderid IS NULL

Provide the result set generated by this query.

One of the result rows should be (22, Customer DTDMN). What does this tell you about customer 22?

b.

SELECT custid, companyname

FROM Sales.Customers as C

WHERE NOT EXISTS (

  SELECT * FROM Sales.Orders as O

  WHERE O.custid = C.custid

)

Explain what the result set represents.

c.

SELECT empid

FROM HR.Employees as E

WHERE (

  SELECT COUNT(*)

  FROM Sales.Orders as O

  WHERE O.empid = E.empid

) > 90

Explain what the result set represents.

Write a query that produces the same result that uses GROUP BY and HAVING.

Answer 1

a.) Left outer join basically returns everything from Customer table irrespective of the fact that wether the Orders table has related value, hence there are various null value in the resultant table.

According to query we have to return customer id and company name from the resultant table after performing left outer join.

Hence, (22, Customer DTDMN) means customer with customer id=22 has not placed any order with the company Customer DTDMN.

b.) It will give wrong answer as the inner loop will select everything from orders table and then the outer loop would not be able to exclude the instances on the basis of particular values.

C.) It has 2 loops inner loop and outer loop the value obtained from outer loop dependent on value of inner loop.

Inner loop: it will perform natural join of orders and employee table on the basis of employeId amd return the count

Outer loop: it will return all empId where the count is greator than 90.