Book Image

SQL for Data Analytics - Third Edition

By : Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston
Book Image

SQL for Data Analytics - Third Edition

By: Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston

Overview of this book

Every day, businesses operate around the clock, and a huge amount of data is generated at a rapid pace. This book helps you analyze this data and identify key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. SQL for Data Analytics, Third Edition is a great way to get started with data analysis, showing how to effectively sort and process information from raw data, even without any prior experience. You will begin by learning how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you will learn how to write SQL queries to aggregate, calculate, and combine SQL data from sources outside of your current dataset. You will also discover how to work with advanced data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you will be able to understand your business at a deeper level. Finally, the book lets you in on the secret to getting information faster and more effectively by using advanced techniques like profiling and automation. By the end of this book, you will be proficient in the efficient application of SQL techniques in everyday business scenarios and looking at data with the critical eye of analytics professional.
Table of Contents (11 chapters)
9
9. Using SQL to Uncover the Truth: A Case Study

Loading the Sample Datasets – macOS

Most exercises in this book use a sample database, sqlda, which contains fabricated data for a fictional electric vehicle company called ZoomZoom. Now, set it up by performing the following steps:

  1. Enter the PostgreSQL shell by typing the following command in Terminal. Press the return key to execute it:
    psql postgres
  2. Now, create a new database called sqlda by typing the following command and pressing return (do not forget the semicolon at the end):
    create database sqlda;
  3. You should see the following output. Type \l (a backslash followed by lowercase L) in Terminal and press the return key to check whether the database was successfully created (you should see the sqlda database listed there):
Figure 0.30: Checking whether a new database is successfully created

Figure 0.30: Checking whether a new database is successfully created

  1. Type or paste \q in the PostgreSQL shell and press the return key to exit.
  2. Download the data.dump file from the Datasets folder in the GitHub repository of this book at https://packt.link/GuU31. Navigate to the folder where you have downloaded the file using the cd command. Then, type the following command:
    psql sqlda < ~/Downloads/data.dump

    Note

    The preceding command assumes that the file is saved in the Downloads directory. Make sure you change the highlighted path based on the location of the data.dump file on your system.

  3. Then, wait for the dataset to be imported:
Figure 0.31: Importing the dataset

Figure 0.31: Importing the dataset

  1. To test if the dataset was imported correctly, type psql postgres and then press the return key to enter the PostgreSQL shell again. Then, run \c sqlda followed by \dt to see the list of tables within the database:
Figure 0.32: List of tables within the sqlda database

Figure 0.32: List of tables within the sqlda database