Combine Shipment and Inventory Data with SQL in PostgreSQL

Table of Contents

1. Challenge

   Introduction

   In this lab, you will learn the fundamentals of modifying and retrieving data in a PostgreSQL database using SQL. You'll insert new records, update existing data, delete rows, and use transactions to control when changes are saved or discarded. Along the way, you'll use the RETURNING clause to inspect modified data and write SELECT queries to verify the results of your work. By the end of the lab, you'll be comfortable performing the core data manipulation operations that form the foundation of everyday database development. Throughout the lab, you will write SQL in the files located in the sql folder.

   To execute a task file and view the results, run:

   psql -d postgres -f sql/task-X-Y.sql
   

   For example:

   psql -d postgres -f sql/task-1-1.sql
   

   If you need to restore the database to its original state, run:

   bash db/reset_database.sh
   

   To exit the PostgreSQL interactive shell (psql), run the following command:

   \q
   ``` info> This lab experience was developed by the Pluralsight team using an internally developed AI tool. All sections were verified by human experts for accuracy prior to publications. However, content may still contain errors or inaccuracies, and we recommend independent verification.
   <br></br> To report a problem or provide feedback, [click here](https://help.pluralsight.com/hc/en-us/requests/new). Feedback may be used to improve accuracy in accordance with our Privacy Policy.

2. Challenge

   Step 1: Insert New Customer Orders

   In this step, you will add new customer orders to the order management system.

   Customer service representatives regularly enter new orders as customers place purchases. The operations team relies on accurate order records to track fulfillment, inventory planning, and customer communication.

   You will use INSERT statements to add new rows to the orders table. Along the way, you will practice inserting individual records, returning inserted rows for verification, and adding multiple records in a single statement.

   By the end of this step, you will understand:

   • How to add new records to a PostgreSQL table
   • How to specify values for individual columns
   • How to use the RETURNING clause to verify inserted data
   • How to insert multiple rows with a single statement
3. Challenge

   Step 2: Update Existing Order Data

   In this step, you will correct existing customer order records with targeted UPDATE statements.

   Operational databases often contain records that need to be corrected after review. An order total may be entered incorrectly, a status may be outdated, or a support team may need to update one specific row after confirming new information.

   You will use UPDATE statements with WHERE clauses to make safe, targeted changes to the orders table. You will also use RETURNING to confirm which row was modified.

   By the end of this step, you will understand:

   • How to update existing rows in PostgreSQL
   • How to use WHERE to target one specific order
   • How to use RETURNING to review modified data immediately
4. Challenge

   Step 3: Delete Invalid or Cancelled Orders

   In this step, you will remove invalid or cancelled orders from the system.

   Not every order should remain in an operational database. Cancelled orders, duplicate entries, and invalid records are often removed after review so downstream reporting and fulfillment processes remain accurate.

   You will use DELETE statements to remove rows from the orders table and use RETURNING to confirm which records were deleted.

   By the end of this step, you will understand:

   • How to remove rows from a PostgreSQL table
   • How to safely target records with a WHERE clause
   • How to use RETURNING to review deleted records
5. Challenge

   Step 4: Control Changes with Transactions

   In this step, you will use transactions to control when database changes are applied.

   When working with production systems, it is often necessary to test a change before permanently saving it. PostgreSQL transactions allow you to group one or more operations together and decide whether to keep the changes with COMMIT or undo them with ROLLBACK.

   You will start a transaction, make a change, undo that change, and then commit a separate update. These are common workflows used by database administrators, developers, and support teams when modifying operational data.

   By the end of this step, you will understand:

   • How to start a transaction with BEGIN
   • How to undo changes with ROLLBACK
   • How to permanently save changes with COMMIT
   • Why transactions help protect data integrity
6. Challenge

   Step 5: Query the Final Order State

   In this final step, you will review the current contents of the orders table after completing the modifications throughout the lab.

   After inserting, updating, deleting, and managing transactions, it is important to verify that the data reflects the intended business outcomes. Analysts and support teams often perform final review queries before closing a ticket, approving a deployment, or handing work off to another team.

   You will inspect the final table, review important status and pricing fields, and focus on the records that were affected during the lab.

   By the end of this step, you will understand:

   • How to review the contents of a modified table
   • How to inspect key business fields after data changes
   • How to verify the records affected by previous operations Congratulations on completing this lab!