Book Image

The Applied SQL Data Analytics Workshop - Second Edition

By : Matt Goldwasser, Upom Malik, Benjamin Johnston
3.5 (2)
Book Image

The Applied SQL Data Analytics Workshop - Second Edition

3.5 (2)
By: 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. Hidden in this data are key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. Are you ready to enter the exciting world of data analytics and unlock these useful insights? Written by a team of expert data scientists who have used their data analytics skills to transform businesses of all shapes and sizes, The Applied SQL Data Analytics Workshop is a great way to get started with data analysis, showing you how to effectively sieve and process information from raw data, even without any prior experience. The book begins by showing you how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you'll learn how to write SQL queries to aggregate, calculate and combine SQL data from sources outside of your current dataset. You'll also discover how to work with different data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you'll finally 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 The Applied SQL Data Analytics Workshop, you'll have the skills you need to start identifying patterns and unlocking insights in your own data. You will be capable of looking and assessing data with the critical eye of a skilled data analyst.
Table of Contents (9 chapters)
Preface
7
7. The Scientific Method and Applied Problem Solving

Deleting Data and Tables

We often discover that data in a table is incorrect and, therefore, can no longer be used. At such times, we need to delete data from a table.

Deleting Values from a Row

Often, we will be interested in deleting a value in a row. The easiest way to accomplish this task is to use the UPDATE structure we have already discussed and set the column value to NULL, like so:

UPDATE {table_name}
SET {column_1} = NULL,
    {column_2} = NULL,
    …
    {column_last} = NULL
WHERE
  {conditional};

Here, {table_name} is the name of the table with the data that needs to be changed, {column_1}, {column_2},… {column_last} is the list of columns whose values you want to delete, and {WHERE} is a conditional statement like the one you would find in a SQL query.

Let's say, for instance, that we have the wrong email on file for the customer with the customer ID equal to 3. To fix that, we can use the following query:

UPDATE 
  customers
SET 
  email = NULL
WHERE 
  customer_id=3;

In the next section, we will learn how to delete rows from a table.

Deleting Rows from a Table

Deleting a row from a table can be done using the DELETE statement, which looks like this:

DELETE FROM {table_name}
WHERE {conditional};

Let's say, for instance, that we have to delete the details of a customer whose email is [email protected]. To perform that, we can use the following query:

DELETE FROM 
  customers
WHERE 
  email='[email protected]';

If we wanted to delete all the data in the customers table without deleting the table, we could write the following query:

DELETE FROM customers;

Alternatively, if you want to delete all the data in a query without deleting the table, you could use the TRUNCATE keyword, like so:

TRUNCATE TABLE customers;

Deleting Tables

To delete all the data in a table and the table itself, you can just use the DROP TABLE statement with the following syntax:

DROP TABLE {table_name};

Here, {table_name} is the name of the table you want to delete. If we wanted to delete all the data in the customers table along with the table itself, we would write the following:

DROP TABLE customers;

Let's perform an exercise to delete/drop the table using the DROP TABLE statement.

Exercise 1.09: Deleting an Unnecessary Reference Table

In this exercise, you will learn how to delete a table using SQL. The marketing team has finished analyzing the potential number of customers they have in every state, and they no longer need the state_populations table. To save space on the database, delete the table.

Perform the following steps to complete the exercise:

  1. Open your favorite SQL client and connect to the sqlda database.
  2. Run the following query to drop the state_populations table:
    DROP TABLE state_populations;

    The state_populations table should now be deleted from the database.

  3. Since the table has just been dropped, a SELECT query on this table throws an error, as expected:
    SELECT 
      * 
    FROM 
      state_populations;

    You will find the error shown in the following figure:

    Figure 1.48: Error shown as the state_populations table was dropped

Figure 1.48: Error shown as the state_populations table was dropped

Note

To access the source code for this specific section, please refer to https://packt.live/2XWLVZA.

In this exercise, we learned how to delete a table using the DROP TABLE statement. In the next activity, we will create and modify tables using SQL.

Activity 1.04: Creating and Modifying Tables for Marketing Operations

In this activity, you will test your ability to create and modify tables using SQL. You did a great job of pulling data for the marketing team. However, the marketing manager, who you helped, realized that they had made a mistake. It turns out that instead of just the query, the manager needs to create a new table in the company's analytics database. Furthermore, they need to make some changes to the data that is present in the customers table. It is your job to help the marketing manager with the table:

  1. Create a new table called customers_nyc that pulls all the rows from the customers table where the customer lives in New York City in the state of New York.
  2. Delete all customers in postal code 10014 from the new table. Due to local laws, they will not be eligible for marketing.
  3. Add a new text column called event.
  4. Set the value of the event column to thank-you party. The following is the expected output:
    Figure 1.49: The customers_nyc table with event set to thank-you party

    Figure 1.49: The customers_nyc table with event set to thank-you party

  5. You've told the manager that you've completed these steps. He tells the marketing operations team, who then uses the data to launch a marketing campaign. The marketing manager thanks you and then asks you to delete the customers_nyc table.

    Note

    The solution for this activity can be found via this link.

In this activity, we used different CRUD operations to modify a table as requested by the marketing manager. We will now come full circle to talk about how SQL and analytics connect.