Question

1. For each of the following, write a single SELECT query against the TSQLV4 database that returns the result set described. Each of these queries involves two tables and can be written using a join operation.

a. One row for each order shipped to France or Germany, showing the order ID, the last name of the employee for the order, and the customer ID for the order.

b. One row for each employee who handled orders to Belgium, showing the employee’s initials (for example, for an employee named Yael Peled, this would be YP) and the number of orders that employee handled that were shipped to Belgium.

c. Same as part b., but include a row for every employee in the HR.Employees table, even if they did not handle any orders shipped to Belgium. (The number of orders for such an employee should be 0.)

Answer 1

a.

Select Sales.Orders.orderid, HR.Employees.lastname , Sales.Customers.custid from Sales.Orders inner join HR.Employees on Sales.Orders.empid = HR.Employees.empid inner join Sales.Customers on Sales.Orders.custid = Sales.Customers.custid where shipcountry = 'France' or shipcountry = 'Germany';

b.

Select HR.Employees.empid,CONCAT(substring(firstname,1,1) , substring(lastname,1,1)) as EmployeeInitials , count(Orders.orderid) as NumOfOrders from Sales.Orders inner join HR.Employees on Sales.Orders.empid = HR.Employees.empid group by HR.Employees.empid having shipcountry = 'France' or shipcountry = 'Belgium';

c.

Select HR.Employees.empid,CONCAT(substring(firstname,1,1) , substring(lastname,1,1)) as EmployeeInitials , count(Orders.orderid) as NumOfOrders from Sales.Orders right inner join HR.Employees on Sales.Orders.empid =HR.Employees.empid group by HR.Employees.empid having shipcountry = 'France' or shipcountry = 'Belgium';

Do ask if any doubt. Please upvote.