SQL Practice: Tesla

7 min readMar 24, 2021

Practice your SQL knowledge by writing queries on a database of Tesla data.

The Database

Open the database of Tesla data in a new tab here. The database is stored in DB Fiddle, a DBMS simulator that allows us to practice writing SQL queries without purchasing or downloading software.

The database is a relational database because the data is structured in data tables. There are four data tables in the database as described here:

Cars data table: a list of all Tesla vehicle models
Factories data table: list of factories that Tesla owns
Executives: information on Tesla’s executive team
Financials: revenue and profit data from 2016 through 2020

Question 1: Write a query that displays the Cars data table

This question requires a simple SELECT command that calls all rows from the Cars data table. To run a query in DB Fiddle, type the SQL code in the ‘Query SQL’ section of and then click ‘Run’ in the top left corner of the page. Here is the code to run this SQL query:

SELECT * FROM Cars

Running this query displays all seven rows of data from the Cars data table. The data table is only two columns, the vehicle id number and name.

Question 2: Write a query that displays both the Cars data table and the Factories table

To display multiple tables, we need to write two different queries. Note that to separate queries, a semicolon is needed after each query except for the last one.

SELECT * FROM Cars;
SELECT * FROM Factories

This code will produce both data tables that are displayed in the order of the code above. The Factories table has four columns of data on Tesla’s six factories around the world. A mockup of the Berlin Gigafactory is pictured below.

Question 3: Write a query that displays only the factory name and city columns from the Factories table

The asterisk in the first two questions is used to tell the database to display all columns from that table. We also have the ability in SQL to display only certain rows from a data table. When a data table has hundreds of columns, it is helpful to only select the columns needed.

To display specific columns, replace the asterisk with the direct name of the columns from the table. In this case, we only want the Name and City columns from the Factories data table. (Note the column names are not case sensitive).

SELECT Name, City FROM Factories

The result is a table of all factories but only the two columns we wanted to pull.

Question 4: Write a query that sorts Tesla executives from highest to lowest salary

Let’s introduce our second command, ORDER BY. The ORDER BY command in SQL allows us to sort data. Similar to sorting data in a spreadsheet, we can sort data in SQL.

To use this command, write ORDER BY followed by the column name that you want to sort. This will sort the column in ascending order (lowest to highest) by default. To sort a column in descending order (highest to lowest) add the word DESC at the end of the query as seen below.

SELECT * FROM Executives
ORDER BY Salary DESC

If you run this query you will see the Executives table displayed and sorted by the Salary column. You may be wondering why Elon Musk’s salary is only $62,400. This is Musk’s official salary per Tesla filings with the SEC. Musk is the largest shareholder in Tesla so most all of his income is from Tesla stock grants and options.

Question 5: Write a query that displays Tesla executives who are older than 45 years old

We learned the ORDER BY command will sort in SQL — so how do we filter data in a database?

Filtering data is the role of the WHERE command. This command allows to find values that are less than, greater than, or equal to a value we assign. In this question we want to filter the Executives table by the Age column.

Since we want to filter for people older than 45 years old, we write the following code:

SELECT * FROM Executives
WHERE Age > 45

Easy enough? Can you write a similar query that displays a list of executives who make more than $100,000?

Question 6: Write a query that displays all years for which Tesla made a profit

This question is another example of using the WHERE command except we now want to use it on the Financials data table.

SELECT * FROM Financials
WHERE Profit > 0

Running the query shows that 2020 was the first year in which Tesla made a profit.

Question 7: Write a query that displays all years in which Tesla made a profit OR made more than $20 billion in revenue

This question involves learning a new set of SQL commands. The AND, OR, and NOT commands are commonly used with the WHERE command when we want to use multiple filters in one query,

To filter a data table with multiple conditions, use one of those commands. In this case we want to use the OR command to filter the two different parameters stated in the question.

Lastly, keep in mind the financial numbers are in millions of dollars, meaning that 20,000 is actually $20,000,000,000 ($20 billion). Here is the query:

SELECT * FROM Financials
WHERE Profit > 0 OR Revenue > 20000

We see that this query returns three rows of data. Now change the OR statement to an AND statement. What happens and how many rows of data are displayed?

SELECT * FROM Financials
WHERE Profit > 0 AND Revenue > 20000

Question 8: How much revenue did Tesla make from 2016 through 2020?

The next set of questions will use one of three SQL functions: SUM, AVERAGE, and COUNT. These functions allow us to perform calculations in SQL.

A SQL function wraps a column name to perform a calculation on that column. To find out how much revenue Tesla made for that five year time period, we need to add up the revenue column from the Financials table.

See the code below to see how the SUM function wraps around the revenue column. Run the code to see the result.

SELECT SUM(Revenue) FROM Financials

The results section displays the number 96334, meaning Tesla made $96.3 billion in revenue from 2016 through 2020.

Question 9: What was Tesla’s average profit over the past five years?

The second function we want to cover is the AVG function, which stands for average. This function will calculate the average of a column of integers.

Similar to the previous question, we will again only use one line of code. Write the SELECT statement by just calling the profit column from the financials table. Then add the AVG() function to wrap around the profit column.

SELECT AVG(Profit) FROM Financials

The answer is -152.4 meaning Tesla has lost $152.4 million on average the past five years.

Question 10: How many factories does Tesla have?

The last function we will cover is the COUNT function. This function can count how many rows of data are in a column. To find out how many factories are in the factories data table, we use the COUNT function on any column name in the factories table.

The code for this query is below and will produce six results. You can replace the name column with the id, city, or state columns and you will get the same result.

SELECT COUNT(Name) FROM Factories

Want to learn more SQL?

The best way to learn is to practice. Try to ask your own questions using this dataset and attempt to solve them by writing SQL queries.

Check out other practice sets here: Top 1000 Movies, Fantasy Football, and PGA Tour.