2. SQL for Data Preparation

Activity 2.01: Building a Sales Model Using SQL Techniques

Solution

1. Open your favorite SQL client and connect to the sqlda database.
2. Use INNER JOIN to join the customers table to the sales table, INNER JOIN to join the products table to the sales table, and LEFT JOIN to join the dealerships table to the sales table.
3. Now, return all columns of the customers table and the products table. Then, return the dealership_id column from the sales table, but fill in dealership_id in sales with -1 if it is NULL.
4. Add a column called high_savings that returns 1 if the sales amount was 500 less than base_msrp or lower. Otherwise, it returns 0. There are many approaches to this query, but one of these approaches could be as follows:

   SELECT
     c.*,
     p.*,
   COALESCE(s.dealership_id, -1),
     CASE WHEN p.base_msrp - s.sales_amount >500 
          THEN 1 
         ...