Question

SQL Question:

What syntax to find the following? Out of the 10 employees, how many do we have a record of handling at least one order? You can do this by pulling the distinct employee ids from the Order table

What syntax would you use to pull a list of all Shipper IDs and Names along with the count of all orders they’ve shipped? Make sure rename count(orders) to OrdersShipped. To do this you’ll need to join Orders and Shippers and then “group by ShipperID, ShipperName”. What syntax did you use?

Answer 1

/* The solution for the given first SQL statement is: */

SELECT DISTINCT employee_id  
FROM Orders; 
--Using the distinct keyword we can get the distinct values of a column


/* The solution fro the second SQL statement is: */

/* Step-1: Initially select the ShipperID,ShipperName from Shippers table
Along with this we have to use COUNT keyword to display the count of orderID's
And for our convenience we will rename it y aliasing it as OrdersShipped */

/* Step-2: Selecting from the Orders table*/

/* step-3: As we need details from only the Orders table and not from Shippers we use LEFT JOIN*/

/* step-4: Connecting the both tables using the ShipperID */

SELECT Shippers.ShipperID,Shippers.ShipperName,COUNT(Orders.OrderID) AS OrdersShipped 
FROM Orders                                        
LEFT JOIN                                          
Shippers ON Orders.ShipperID = Shippers.ShipperID  
GROUP BY ShipperName;                              

/* step-5: To group the results based on ShipperName */