Logo
Let's Talk
Portfolio

SQL Challenge 1 – Steve’s Car Showroom

In this SQL project, we delve into the fascinating world of data analysis, a crucial tool for informed decision-making. By transforming the raw data retrieved from Steve’s Car Showroom into valuable insights, we will drive his business towards success. Let’s go!

Problem Statement

Steve runs a top-end car showroom, but his data analyst has just quit and left him without his crucial insights. Can you analyze the following data to provide him with all the answers he requires?

Tables

Here are the tables you will be using:

Steve’s Car Showroom Datasets

Schema SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<span class="line"><span class="cl"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">cars</span> <span class="p">(</span>
</span></span><span class="line"><span class="cl"><span class="n">car_id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">make</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="k">type</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="n">style</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="n">cost</span> <span class="nb">INT</span>
</span></span><span class="line"><span class="cl"><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">cars</span> <span class="p">(</span><span class="n">car_id</span><span class="p">,</span> <span class="n">make</span><span class="p">,</span> <span class="k">type</span><span class="p">,</span> <span class="n">style</span><span class="p">,</span> <span class="n">cost</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'Honda'</span><span class="p">,</span> <span class="s1">'Civic'</span><span class="p">,</span> <span class="s1">'Sedan'</span><span class="p">,</span> <span class="mi">30000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'Toyota'</span><span class="p">,</span> <span class="s1">'Corolla'</span><span class="p">,</span> <span class="s1">'Hatchback'</span><span class="p">,</span> <span class="mi">25000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s1">'Ford'</span><span class="p">,</span> <span class="s1">'Explorer'</span><span class="p">,</span> <span class="s1">'SUV'</span><span class="p">,</span> <span class="mi">40000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s1">'Chevrolet'</span><span class="p">,</span> <span class="s1">'Camaro'</span><span class="p">,</span> <span class="s1">'Coupe'</span><span class="p">,</span> <span class="mi">36000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">5</span><span class="p">,</span> <span class="s1">'BMW'</span><span class="p">,</span> <span class="s1">'X5'</span><span class="p">,</span> <span class="s1">'SUV'</span><span class="p">,</span> <span class="mi">55000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">6</span><span class="p">,</span> <span class="s1">'Audi'</span><span class="p">,</span> <span class="s1">'A4'</span><span class="p">,</span> <span class="s1">'Sedan'</span><span class="p">,</span> <span class="mi">48000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="s1">'Mercedes'</span><span class="p">,</span> <span class="s1">'C-Class'</span><span class="p">,</span> <span class="s1">'Coupe'</span><span class="p">,</span> <span class="mi">60000</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">8</span><span class="p">,</span> <span class="s1">'Nissan'</span><span class="p">,</span> <span class="s1">'Altima'</span><span class="p">,</span> <span class="s1">'Sedan'</span><span class="p">,</span> <span class="mi">26000</span><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span>
<span class="line"><span class="cl"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">salespersons</span> <span class="p">(</span>
</span></span><span class="line"><span class="cl"><span class="n">salesman_id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">name</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="n">age</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">city</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">50</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">salespersons</span> <span class="p">(</span><span class="n">salesman_id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">age</span><span class="p">,</span> <span class="n">city</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'John Smith'</span><span class="p">,</span> <span class="mi">28</span><span class="p">,</span> <span class="s1">'New York'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'Emily Wong'</span><span class="p">,</span> <span class="mi">35</span><span class="p">,</span> <span class="s1">'San Fran'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s1">'Tom Lee'</span><span class="p">,</span> <span class="mi">42</span><span class="p">,</span> <span class="s1">'Seattle'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="s1">'Lucy Chen'</span><span class="p">,</span> <span class="mi">31</span><span class="p">,</span> <span class="s1">'LA'</span><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span>
<span class="line"><span class="cl"><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">sales</span> <span class="p">(</span>
</span></span><span class="line"><span class="cl"><span class="n">sale_id</span> <span class="nb">INT</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">car_id</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">salesman_id</span> <span class="nb">INT</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="n">purchase_date</span> <span class="nb">DATE</span><span class="p">,</span>
</span></span><span class="line"><span class="cl"><span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">car_id</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">cars</span><span class="p">(</span><span class="n">car_id</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="k">FOREIGN</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">salesman_id</span><span class="p">)</span> <span class="k">REFERENCES</span> <span class="n">salespersons</span><span class="p">(</span><span class="n">salesman_id</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="p">);</span>
</span></span><span class="line"><span class="cl"><span class="c1">--------------------
</span></span></span><span class="line"><span class="cl"><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">sales</span> <span class="p">(</span><span class="n">sale_id</span><span class="p">,</span> <span class="n">car_id</span><span class="p">,</span> <span class="n">salesman_id</span><span class="p">,</span> <span class="n">purchase_date</span><span class="p">)</span>
</span></span><span class="line"><span class="cl"><span class="k">VALUES</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2021-01-01'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2021-02-03'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'2021-02-10'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">4</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'2021-03-01'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">5</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2021-04-02'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">6</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2021-05-05'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">7</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'2021-06-07'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">8</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2021-07-09'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">9</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'2022-01-01'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">10</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2022-02-03'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">11</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'2022-02-10'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">12</span><span class="p">,</span> <span class="mi">7</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'2022-03-01'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">13</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2022-04-02'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">14</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2022-05-05'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">15</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'2022-06-07'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">16</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="s1">'2022-07-09'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">17</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2023-01-01'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">18</span><span class="p">,</span> <span class="mi">6</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="s1">'2023-02-03'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">19</span><span class="p">,</span> <span class="mi">7</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'2023-02-10'</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"><span class="p">(</span><span class="mi">20</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="s1">'2023-03-01'</span><span class="p">);</span>
</span></span>

Entity Relationship Diagram

Questions

Click here to follow along and answer the questions:

  1. What are the details of all cars purchased in the year 2022?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">make</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="k">type</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">style</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">cost</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">sales</span><span class="p">.</span><span class="n">purchase_date</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">cars</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="k">YEAR</span><span class="p">(</span><span class="n">purchase_date</span><span class="p">)</span> <span class="o">=</span> <span class="mi">2022</span><span class="p">;</span>
    </span></span>

  2. What is the total number of cars sold by each salesperson?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">age</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">city</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">COUNT</span><span class="p">(</span><span class="n">sales</span><span class="p">.</span><span class="n">sale_id</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total_cars_sold</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">salespersons</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">;</span>
    </span></span>

  3. What is the total revenue generated by each salesperson?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">age</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">city</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">SUM</span><span class="p">(</span><span class="n">cars</span><span class="p">.</span><span class="n">cost</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total_revenue</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">salespersons</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">cars</span> <span class="k">ON</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">;</span>
    </span></span>

  4. What are the details of the cars sold by each salesperson?

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <span class="line"><span class="cl"><span class="k">SELECT</span> <span class="k">DISTINCT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">make</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="k">type</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">style</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">cars</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">salespersons</span> <span class="k">ON</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">sales</span><span class="p">.</span><span class="n">sale_id</span><span class="p">;</span>
    </span></span>

  5. What is the total revenue generated by each car type?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">make</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="k">type</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">style</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">SUM</span><span class="p">(</span><span class="n">cars</span><span class="p">.</span><span class="n">cost</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total_revenue_by_car</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">cars</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span><span class="p">;</span>
    </span></span>

  6. What are the details of the cars sold in the year 2021 by salesperson ‘Emily Wong’?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">make</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="k">type</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">style</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">cost</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">cars</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">salespersons</span> <span class="k">ON</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="k">YEAR</span><span class="p">(</span><span class="n">sales</span><span class="p">.</span><span class="n">purchase_date</span><span class="p">)</span> <span class="o">=</span> <span class="mi">2021</span> <span class="k">AND</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="s1">'Emily Wong'</span><span class="p">;</span>
    </span></span>

  7. What is the total revenue generated by the sales of hatchback cars?
    1
    2
    3
    4
    5
    6
    7
    8
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">style</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">SUM</span><span class="p">(</span><span class="n">cars</span><span class="p">.</span><span class="n">cost</span><span class="p">)</span> <span class="k">AS</span> <span class="n">hatchback_total_revenue</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">cars</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">cars</span><span class="p">.</span><span class="n">style</span> <span class="o">=</span> <span class="s1">'Hatchback'</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span><span class="p">;</span>
    </span></span>

  8. What is the total revenue generated by the sales of SUV cars in the year 2022?
    1
    2
    3
    4
    5
    6
    7
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">cars</span><span class="p">.</span><span class="n">style</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">SUM</span><span class="p">(</span><span class="n">cars</span><span class="p">.</span><span class="n">cost</span><span class="p">)</span> <span class="k">AS</span> <span class="n">SUV_total_revenue_2022</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">cars</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="n">cars</span><span class="p">.</span><span class="n">style</span> <span class="o">=</span> <span class="s1">'SUV'</span> <span class="k">AND</span> <span class="k">YEAR</span><span class="p">(</span><span class="n">purchase_date</span><span class="p">)</span> <span class="o">=</span> <span class="mi">2022</span><span class="p">;</span>
    </span></span>

  9. What is the name and city of the salesperson who sold the most number of cars in the year 2023?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">city</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">COUNT</span><span class="p">(</span><span class="n">sales</span><span class="p">.</span><span class="n">sale_id</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total_cars_sold</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">salespersons</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="k">YEAR</span><span class="p">(</span><span class="n">sales</span><span class="p">.</span><span class="n">purchase_date</span><span class="p">)</span> <span class="o">=</span> <span class="mi">2023</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">total_cars_sold</span> <span class="k">DESC</span>
    </span></span><span class="line"><span class="cl"><span class="k">LIMIT</span> <span class="mi">1</span><span class="p">;</span>
    </span></span>

  10. What is the name and age of the salesperson who generated the highest revenue in the year 2022?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    <span class="line"><span class="cl"><span class="k">SELECT</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">name</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="n">salespersons</span><span class="p">.</span><span class="n">age</span><span class="p">,</span>
    </span></span><span class="line"><span class="cl">    <span class="k">SUM</span><span class="p">(</span><span class="n">cars</span><span class="p">.</span><span class="n">cost</span><span class="p">)</span> <span class="k">AS</span> <span class="n">highest_revenue</span>
    </span></span><span class="line"><span class="cl"><span class="k">FROM</span> <span class="n">salespersons</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">sales</span> <span class="k">ON</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">JOIN</span> <span class="n">cars</span> <span class="k">ON</span> <span class="n">cars</span><span class="p">.</span><span class="n">car_id</span> <span class="o">=</span> <span class="n">sales</span><span class="p">.</span><span class="n">car_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">WHERE</span> <span class="k">YEAR</span><span class="p">(</span><span class="n">sales</span><span class="p">.</span><span class="n">purchase_date</span><span class="p">)</span> <span class="o">=</span> <span class="mi">2022</span>
    </span></span><span class="line"><span class="cl"><span class="k">GROUP</span> <span class="k">BY</span> <span class="n">salespersons</span><span class="p">.</span><span class="n">salesman_id</span>
    </span></span><span class="line"><span class="cl"><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">highest_revenue</span> <span class="k">DESC</span>
    </span></span><span class="line"><span class="cl"><span class="k">LIMIT</span> <span class="mi">1</span><span class="p">;</span>
    </span></span>

Conclusion

This SQL project offers valuable insights into the world of data analysis, showcasing its significance as a vital tool for informed decision-making. Steve’s Car Showroom project informs us about cars purchased, salesperson performance, and revenue generation throughout the years. This is achieved by identifying popular cars, evaluating the sales team, and acknowledging high-earning salespeople. These insights are key to driving Steve’s Car Showroom towards success and prosperity by enabling data-driven decision-making and optimizing business operations.

Adopted form Steel Data.

Download

Cta

Let’s Work Together

Contact Me