Build an E-commerce Checkout Database

Introduction

30 Second Summary

An online checkout changes several records in seconds. One bad quantity or interrupted update can leave a store selling stock it does not have.

In this project, you will build the PostgreSQL database behind a small store called FlashCart. The finished system rejects invalid order data while keeping canceled checkouts from changing inventory.

What You'll Build

You run flashcart.sql and watch FlashCart finish with protected inventory plus a two-order summary showing each customer, status, units, and total value.

By the end of this project, you'll have:

• A normalized order model that recombines customers, products, orders, and line items into a report with line counts, units, and totals.
• A visible integrity test where you first see quantity = -2 enter the database. You then see the same invalid quantity rejected after the rules are enforced.
• An atomic checkout workflow that lets you cancel a checkout and confirm that stock plus order records stay unchanged.
• Secret Mission: Use a savepoint to replace one draft order line without undoing the earlier inventory update.

Are there any prerequisites?

You'll need a Windows 10 or later computer with administrator access. Basic SQL knowledge is enough for the guided PostgreSQL 18.6 and pgAdmin 4 v9.18 setup.

Before We Start

This first checkpoint defines the store data that must stay trustworthy. It also captures why checkout changes belong in one transaction.

Install and Verify PostgreSQL

FlashCart needs a real PostgreSQL database process to enforce the rules you identified. A local server gives you a realistic place to test those rules without creating a cloud account.

You will install PostgreSQL 18.6 as a Windows service. You will use pgAdmin v9.18 to connect to that service and create the empty FlashCart database.

In this step, get ready to:

• Install PostgreSQL 18.6 with its server and command-line components.
• Install pgAdmin v9.18 and connect it to your local PostgreSQL server.
• Create the flashcart database with SQL and verify the active database version.

Install PostgreSQL 18.6

PostgreSQL runs separately from the programs that connect to it. Installing the server as a Windows service keeps that database process available for pgAdmin and the checkout scripts.

Why PostgreSQL for FlashCart?

PostgreSQL exposes the client, server, connection, constraint, and transaction boundaries that FlashCart needs. This makes each database behavior visible while everything stays on your computer.

SQLite stores data directly in a file without a separate server process. PostgreSQL gives you a closer view of how a relational database management system operates.

PostgreSQL 18.6 is the required version for this project. Choose the tab that matches the PostgreSQL software currently installed on your Windows computer.

✔️ I have PostgreSQL 18.6

Your PostgreSQL version is ready. Continue below to record its port and confirm that its Windows service is running.

• Confirm that your installation includes PostgreSQL Server.
• Confirm that your installation includes Command Line Tools.

ⓧ I have an older version

An older release can produce different results from this project. Upgrade to PostgreSQL 18.6 before continuing.

• Open the official PostgreSQL Windows download page.
• Download the Windows installer for PostgreSQL 18.6.
• Right-click the downloaded installer in File Explorer.
• Select Run as administrator.

The component screen controls which PostgreSQL tools remain available after the upgrade.

• Keep PostgreSQL Server selected.
• Keep Command Line Tools selected.
• Complete the installer screens to upgrade PostgreSQL to 18.6.

ⓧ PostgreSQL is not installed

This is the expected starting point for this project. The official Windows installer sets up the database server as a local service.

• Open the official PostgreSQL Windows download page.
• Download the Windows installer for PostgreSQL 18.6.
• Right-click the downloaded installer in File Explorer.
• Select Run as administrator.

The component screen decides which parts of PostgreSQL are installed on your computer.

• Keep PostgreSQL Server selected.
• Keep Command Line Tools selected.

The installer asks for a database administrator password. Keeping this credential in your password manager prevents it from being lost or exposed in a project file.

• Enter a strong password for the postgres superuser.
• Store the password in your password manager.
• Keep port 5432 unless the installer reports that it is occupied.
• Complete the remaining installer screens.

The installation is complete when the installer confirms that PostgreSQL has been installed.

• Record the listener port selected during installation: 5432 or your chosen PostgreSQL port.
• Press the Windows key to open Windows search.
• Type Services and press Enter.
• Find the PostgreSQL service in the Services application.
• Confirm that the service status shows it is running.

That is the database engine running. FlashCart now has a local server ready to accept connections.

PostgreSQL service not running?

Confirm that the installer completed with PostgreSQL Server selected. Rerun the PostgreSQL installer if that component is missing.

If the service exists but is stopped, use its Windows service controls to start it. A port conflict can also prevent startup, so check that the service uses the port you recorded.

help me diagnose a PostgreSQL Windows service that will not start

Install pgAdmin and connect locally

pgAdmin provides the visual workspace where you will run FlashCart queries and inspect their results. Its server connection tells the client which PostgreSQL process to contact.

This project uses pgAdmin v9.18. Choose the tab that matches the pgAdmin version on your computer.

✔️ I have pgAdmin 4 v9.18

Your pgAdmin version is ready. Continue below to launch it and register the local PostgreSQL server.

ⓧ I have an older version

Update pgAdmin before creating the connection. This keeps the interface aligned with the instructions in this project.

• Open the official pgAdmin Windows download page.
• Download the Windows installer for pgAdmin 4 v9.18.
• Run the downloaded installer.
• Complete the installer screens to update pgAdmin.

The installation is complete when the installer confirms that pgAdmin has been installed.

ⓧ pgAdmin is not installed

Install the standalone Windows release so the project uses pgAdmin 4 v9.18.

• Open the official pgAdmin Windows download page.
• Download the Windows installer for pgAdmin 4 v9.18.
• Run the downloaded installer.
• Complete the installer screens.

The installation is complete when the installer confirms that pgAdmin has been installed.

• Press the Windows key to open Windows search.
• Type pgAdmin 4 and press Enter.
• Open the Help menu after pgAdmin loads.
• Select About pgAdmin 4.
• Confirm that the dialog reports version v9.18.

Connecting pgAdmin requires the database password you stored earlier. The password stays out of your SQL files.

• Right-click the Servers node in the Object Explorer.
• Select Register.
• Select Server.
• Enter FlashCart Local in the Name field.
• Select the Connection tab.
• Enter localhost in the Host name/address field.
• Enter 5432 or your chosen PostgreSQL port in the Port field.
• Enter postgres in the Maintenance database field.

• Enter postgres in the Username field.
• Enter your PostgreSQL superuser password in the Password field.
• Click Save.
• Confirm that pgAdmin displays a successful connection message in the lower-right status area.

The connection is live. pgAdmin can now send SQL to the PostgreSQL service as the postgres superuser.

Cannot connect to the local server?

Check that the PostgreSQL Windows service is running. Confirm that the Port field matches the port you recorded during installation.

Retype the password stored for the postgres superuser if authentication fails. Passwords are case-sensitive.

help me fix my local pgAdmin connection

Create and verify the FlashCart database

The PostgreSQL installer creates a maintenance database named postgres. You will connect the Query Tool to that database so it can create the separate flashcart database.

• Expand FlashCart Local in the Object Explorer.
• Expand the Databases node.
• Select the postgres database.
• Open the Tools menu.
• Select Query Tool.
• Create the database by entering this statement in the SQL editor:

CREATE DATABASE flashcart;

What does this code do?

The statement asks the connected PostgreSQL server to create a database named flashcart. The statement must run while the Query Tool is connected to another database.

• Click the file-save control in the Query Tool toolbar.
• Choose your Desktop as the file location.
• Enter setup.sql as the file name.
• Click Save.
• Click Execute script in the Query Tool toolbar.
• Confirm in the Messages tab that the statement completed successfully.
• Right-click the Databases node in the Object Explorer.
• Select Refresh.

You should see flashcart listed under Databases. That new entry proves the setup script changed the server.

Do not see the flashcart database?

Confirm that the Query Tool was connected to postgres when you executed the statement. Check the Messages tab for the reason the command stopped.

If the database already exists, refresh the Databases node before running the statement again.

help me troubleshoot CREATE DATABASE flashcart

✔️ Awesome, I've got everything!

Great. Make sure setup.sql is saved on your Desktop before continuing.

ⓧ I'd like to double check the full code

Here is the complete setup.sql file. Make sure yours matches exactly:

CREATE DATABASE flashcart;

• Select the new flashcart database in the Object Explorer.
• Open the Tools menu.
• Select Query Tool.

Before you run the final check, which database name and PostgreSQL version do you expect the result row to report?

• Verify the active connection and server version by running this query:

SELECT current_database(), version();

What does this query check?

• The current_database() function returns the name of the database used by this Query Tool connection.
• The version() function returns text describing the PostgreSQL server version.

• Click Execute script in the Query Tool toolbar.
• Open the Data Output tab below the SQL editor.
• Confirm that current_database() shows flashcart.
• Confirm that version() reports PostgreSQL 18.6.

You have a verified PostgreSQL server and a working FlashCart database connection. The Query Tool is ready for the store schema.

Seeing the wrong database or version?

If current_database() shows postgres, close that Query Tool tab. Select flashcart before opening a new Query Tool.

If the result reports an older PostgreSQL version, pgAdmin is connected to a different local server. Review the registered server's port and match it to your PostgreSQL 18.6 service.

help me verify my PostgreSQL connection

Your database workspace is ready. Next, you will build FlashCart's first relational schema and watch it accept a quantity that should never reach checkout.

Build a Store That Accepts Bad Data

Your flashcart database is live in the pgAdmin Query Tool. The verification result proves that your local server can run SQL in the correct database.

FlashCart now needs a normalized relational model for customers, products, orders, and line items. Data types organize the shape of each value.

Keys organize relationships between rows. This first schema leaves business-value rules undefined so you can test what slips through.

In this step, get ready to:

• Create four tables with identity primary keys, foreign keys, and a composite primary key.
• Load FlashCart's sample customers, products, and first paid order.
• Run a negative quantity through the schema to test its business rules.

Create the relational schema

A primary key gives each main record a stable identity. Foreign keys connect the records that belong together.

• In the Query Tool from earlier, select the existing verification query.
• Replace the selected query with the first table by pasting this code:

-- Step 2: relational schema and seed data
CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

What does the customers table do?

• The identity primary key generates a unique customer_id for each customer.
• The UNIQUE rule prevents two customers from sharing the same email address.

• Save the editor contents as flashcart.sql with Query Tool's file-save control.
• Highlight only the new table statement.
• Select Execute script in the Query Tool toolbar.

The Messages tab confirms that the statement completed. The customers table now has a generated identifier plus required customer details.

Customers table not created?

• Confirm that the Query Tool is still connected to flashcart.
• Highlight only the CREATE TABLE customers statement before selecting Execute script.

Help me diagnose why the customers table was not created in PostgreSQL.

• Append the product catalog table to flashcart.sql by pasting this code at the end:

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name varchar(100) NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    stock_quantity integer NOT NULL
);

What does the products table do?

• The numeric(10, 2) type stores exact product prices with two decimal places.
• The stock_quantity column records how many units FlashCart can sell.

• Save flashcart.sql.
• Highlight only the CREATE TABLE products statement.
• Select Execute script.

The Messages tab confirms that the product table was created. FlashCart can now store prices plus inventory counts.

Products table not created?

• Check that every column line ends with a comma except the final stock_quantity line.
• Confirm that numeric(10, 2) includes both parentheses.

Help me find the problem in my products table statement.

• Append the order header table to flashcart.sql by pasting this code at the end:

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_status varchar(20) NOT NULL DEFAULT 'PENDING',
    ordered_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

What does the orders table do?

• The customer reference requires every order to belong to a row in customers.
• The defaults give a new order the PENDING status plus its creation time.

• Save flashcart.sql.
• Highlight only the CREATE TABLE orders statement.
• Select Execute script.

The Messages tab confirms that the order table was created. Each future order must point to a stored customer.

Orders table not created?

• Confirm that the customers table was created before this statement.
• Check that the customer reference uses customers(customer_id) exactly.

Help me diagnose why the orders table cannot reference customers.

• Append the line-item table to flashcart.sql by pasting this code at the end:

CREATE TABLE order_items (
    order_id integer NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

What does the order_items table do?

• Each row connects one order to one product.
• The composite primary key prevents the same product from appearing twice in one order.
• The captured unit_price preserves the price used when the order was placed.

• Save flashcart.sql.
• Highlight only the CREATE TABLE order_items statement.
• Select Execute script.

The Messages tab confirms that the final table was created. That's the relational foundation in place: four tables now represent FlashCart's customers, products, orders, and line items.

Line-item table not created?

• Confirm that both orders and products already exist.
• Check that the composite key contains order_id plus product_id.

Help me fix the order_items table definition.

Load the store's first order

The tables provide the structure. Sample rows now give you a real order that can exercise every relationship.

• Append the customer rows to flashcart.sql by pasting this code at the end:

INSERT INTO customers (full_name, email) VALUES
    ('Asha Khan', 'asha@example.com'),
    ('Marco Silva', 'marco@example.com');

What does this insert do?

• The multi-row insert adds Asha Khan plus Marco Silva in one statement.
• PostgreSQL generates each customer_id from the identity column.

• Save flashcart.sql.
• Highlight only the customer insert.
• Select Execute script.

The Messages tab confirms that two customer rows were added.

Customer insert rejected?

• Confirm that each customer row contains a name plus an email address.
• Check whether the same customer insert was already executed in this database.

Help me diagnose why the FlashCart customer rows were rejected.

• Append the product rows to flashcart.sql by pasting this code at the end:

INSERT INTO products (product_name, unit_price, stock_quantity) VALUES
    ('Wireless Mouse', 25.00, 10),
    ('Mechanical Keyboard', 80.00, 5),
    ('USB-C Hub', 45.00, 8);

What does this insert do?

• The insert adds three products with exact prices.
• Each final value records the product's starting stock count.

• Save flashcart.sql.
• Highlight only the product insert.
• Select Execute script.

The Messages tab confirms that three product rows were added.

Product insert rejected?

• Check that each product row contains a name, a price, and a stock count.
• Confirm that each decimal price uses a full stop.

Help me find the issue in my product seed data.

• Append Asha's paid order to flashcart.sql by pasting this code at the end:

INSERT INTO orders (customer_id, order_status)
SELECT customer_id, 'PAID'
FROM customers
WHERE email = 'asha@example.com';

How does this create Asha's order?

• The query finds Asha through her unique email address.
• The insert reuses her generated customer_id to create a paid order.

• Save flashcart.sql.
• Highlight only the paid-order insert.
• Select Execute script.

The Messages tab confirms that Asha's order was inserted.

Paid order not inserted?

• Confirm that Asha's customer row was inserted first.
• Check that the email filter contains asha@example.com exactly.

Help me diagnose why Asha's paid order was not inserted.

• Append the valid Wireless Mouse line to flashcart.sql by pasting this code at the end:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, 2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Wireless Mouse'
WHERE c.email = 'asha@example.com';

How does this build the line item?

• The joins connect Asha's order to the Wireless Mouse product.
• The inserted row records two units at the product's current price.

• Save flashcart.sql.
• Highlight only the Wireless Mouse line-item insert.
• Select Execute script.

The Messages tab confirms that the valid line item was inserted. FlashCart now has a customer, a paid order, and the product sold in that order.

Valid line item not inserted?

• Confirm that Asha's order exists before running the line-item insert.
• Check that the product filter contains Wireless Mouse exactly.

Help me trace why the Wireless Mouse line item was not inserted.

Test an impossible quantity

The schema can connect valid customers, products, and orders. Its next test asks whether those relationships also protect the meaning of an order quantity.

• Append the Mechanical Keyboard test line to flashcart.sql by pasting this code at the end:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, -2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
WHERE c.email = 'asha@example.com';

What is this statement testing?

• The joins find Asha's order plus the Mechanical Keyboard product.
• The requested quantity is -2, which represents an impossible purchase.

• Save flashcart.sql.

Before you execute it, ask yourself whether the current schema accepts the negative quantity.

• Highlight only the Mechanical Keyboard line-item insert.
• Select Execute script.

The statement completes successfully. PostgreSQL accepted the negative quantity because every key relationship is valid and no business-value rule rejects it.

This shortfall is intentional

The schema protects row identity plus relationships. It currently treats any non-null integer as an acceptable quantity.

This gives you visible evidence that types and keys cannot express every business rule.

Test statement did not complete?

• Confirm that Asha's order plus the Mechanical Keyboard product already exist.
• Check whether you already inserted this order-product pair.

Help me diagnose why the deliberate negative line-item insert failed before business rules were added.

• Append the joined order report to flashcart.sql by pasting this code at the end:

SELECT
    o.order_id,
    c.full_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;

What does the report show?

• The aliases oi, o, c, and p keep the joined column references concise.
• The report combines each order line with its customer plus product details.
• The final sort keeps products together within each order.

• Save flashcart.sql.

Before you run the report, predict which quantity the Mechanical Keyboard row contains.

• Highlight only the final report query.
• Select Execute script.

In Data Output, you will see a Mechanical Keyboard row with quantity equal to -2. You have proved that the working relational model still accepts an impossible business value.

Negative row missing?

• Confirm that the Mechanical Keyboard test insert completed before the report ran.
• Check that the report starts from order_items AS oi.
• Highlight only the final SELECT query before selecting Execute script.

Help me find why my joined report does not show the negative Mechanical Keyboard quantity.

✔️ Awesome, I've got everything!

Your flashcart.sql file now contains the complete Step 2 schema plus seed data. Keep the Mechanical Keyboard row with quantity = -2 for the next step.

ⓧ I'd like to double check the full code

Compare your complete flashcart.sql file with this version:

-- Step 2: relational schema and seed data
CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name varchar(100) NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    stock_quantity integer NOT NULL
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_status varchar(20) NOT NULL DEFAULT 'PENDING',
    ordered_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id integer NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (full_name, email) VALUES
    ('Asha Khan', 'asha@example.com'),
    ('Marco Silva', 'marco@example.com');

INSERT INTO products (product_name, unit_price, stock_quantity) VALUES
    ('Wireless Mouse', 25.00, 10),
    ('Mechanical Keyboard', 80.00, 5),
    ('USB-C Hub', 45.00, 8);

INSERT INTO orders (customer_id, order_status)
SELECT customer_id, 'PAID'
FROM customers
WHERE email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, 2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Wireless Mouse'
WHERE c.email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, -2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
WHERE c.email = 'asha@example.com';

SELECT
    o.order_id,
    c.full_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;

Your store works, yet its data proves that relationships alone cannot protect every business rule. Next, you will move those rules into the database so impossible values are rejected at the boundary.

Enforce Data Integrity

The previous step proved that PostgreSQL accepted a negative quantity for the Mechanical Keyboard. That result exposed a gap between valid relationships and valid business data.

A named CHECK constraint gives that business rule to the database. You will remove the existing bad row before adding rules that protect prices, stock counts, and order quantities.

In this step, get ready to:

• Remove the invalid Mechanical Keyboard order line.
• Add named constraints for prices, stock counts, and order quantities.
• Prove that a negative quantity is rejected.

Remove the invalid row

PostgreSQL validates existing rows when you add a normal CHECK constraint. The negative quantity must leave the table before the new rule can be applied.

• Return to flashcart.sql in the Query Tool from earlier.
• Place your cursor after the existing ORDER BY o.order_id, p.product_name; line.
• Append the cleanup statement by copying this code:

-- Step 3: clean the bad row and enforce business rules
DELETE FROM order_items
WHERE quantity < 1;

What does this code do?

• The DELETE statement removes every order line whose quantity is below 1.
• This cleanup removes the known invalid row before PostgreSQL checks the existing data.

• Save flashcart.sql.
• Highlight only the cleanup statement.
• Click Execute script in the Query Tool toolbar.

Good work. The execution details report that one row was affected, so the deliberately invalid Mechanical Keyboard line is gone.

Was no row removed?

Confirm that the Query Tool is connected to flashcart. Check that you highlighted the DELETE statement before executing it.

If you already ran the cleanup once, no matching row remains. That result is safe.

help me diagnose the cleanup result

Add named check constraints

Constraint names make failures easier to trace because PostgreSQL can identify the exact rule that rejected a row. The product rules will protect catalog prices and inventory counts.

• Append the product constraints below the cleanup statement by copying this code:

ALTER TABLE products
    ADD CONSTRAINT products_unit_price_positive CHECK (unit_price > 0),
    ADD CONSTRAINT products_stock_nonnegative CHECK (stock_quantity >= 0);

What do these constraints protect?

• The products_unit_price_positive constraint requires every product price to be greater than zero.
• The products_stock_nonnegative constraint allows zero stock while rejecting negative inventory.

• Save flashcart.sql.
• Highlight only the ALTER TABLE products statement.
• Click Execute script.

The Messages tab shows successful execution details. Product prices and stock counts now have database-enforced limits.

Did the product constraints fail?

Check that you highlighted only the product statement. Running the earlier schema again would try to recreate tables that already exist.

Confirm that each constraint name matches the code exactly. A partially selected statement can also cause a syntax error.

help me fix the product constraint statement

The order-item rules protect the values captured during checkout. They prevent an order line from representing a free item or an impossible quantity.

• Append the order-item constraints below the product constraints by copying this code:

ALTER TABLE order_items
    ADD CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    ADD CONSTRAINT order_items_unit_price_positive CHECK (unit_price > 0);

What do these constraints protect?

• The order_items_quantity_positive constraint requires every order quantity to be greater than zero.
• The order_items_unit_price_positive constraint protects the price captured on each order line.

• Save flashcart.sql.
• Highlight only the ALTER TABLE order_items statement.
• Click Execute script.

The execution details confirm that the order-item rules were added. FlashCart now enforces the values that its relationships could not protect.

Did an existing row block the constraints?

Confirm that you executed the cleanup statement before this statement. The original negative quantity cannot pass the new quantity rule.

If the cleanup was successful, check that the Query Tool still targets flashcart.

help me add the order-item constraints

Prove the rules hold

A constraint becomes convincing when you test the exact behavior it must prevent. You will retry a negative quantity before checking the table for any nonpositive rows.

• Append the commented test and integrity query below the constraints by copying this code:

-- Constraint test: remove the comment markers, run only this statement,
-- observe the check-constraint error, then restore the comments.
-- INSERT INTO order_items (order_id, product_id, quantity, unit_price)
-- SELECT o.order_id, p.product_id, -1, p.unit_price
-- FROM orders AS o
-- JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
-- ORDER BY o.order_id
-- LIMIT 1;

SELECT *
FROM order_items
WHERE quantity <= 0;

What does this test prove?

• The commented INSERT attempts to add a Mechanical Keyboard line with a quantity of -1.
• The final SELECT searches for any order line whose quantity is zero or negative.
• Restoring the test to comments keeps the complete script safe to run against a fresh database.

• Remove the leading comment markers from the six lines that start with INSERT INTO order_items and end with LIMIT 1;.

Before you execute the statement, do you think PostgreSQL will store the negative quantity or reject it?

• Highlight only the uncommented INSERT statement.
• Click Execute script.

The Messages tab reports a check-constraint error that names order_items_quantity_positive. This rejection is the intended result.

• Restore the leading comment markers on the six test lines.
• Save flashcart.sql.

Before you run the final check, how many rows do you expect the query to return?

• Highlight only the final SELECT statement.
• Click Execute script.

You will see zero rows in Data Output. That result proves the failed insert left no invalid quantity behind.

Did the negative quantity get stored?

Confirm that the order_items_quantity_positive statement completed successfully before you ran the test. The test should be selected without the surrounding comments.

If Data Output contains a row, execute the cleanup statement before adding the order-item constraints again.

help me diagnose the integrity test

✔️ Awesome, I've got everything!

Your constraints are active. Make sure flashcart.sql is saved with the invalid test restored to comments.

ⓧ I'd like to double check the full code

Compare your complete flashcart.sql file with this version:

-- Step 2: relational schema and seed data
CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name varchar(100) NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    stock_quantity integer NOT NULL
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_status varchar(20) NOT NULL DEFAULT 'PENDING',
    ordered_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id integer NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (full_name, email) VALUES
    ('Asha Khan', 'asha@example.com'),
    ('Marco Silva', 'marco@example.com');

INSERT INTO products (product_name, unit_price, stock_quantity) VALUES
    ('Wireless Mouse', 25.00, 10),
    ('Mechanical Keyboard', 80.00, 5),
    ('USB-C Hub', 45.00, 8);

INSERT INTO orders (customer_id, order_status)
SELECT customer_id, 'PAID'
FROM customers
WHERE email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, 2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Wireless Mouse'
WHERE c.email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, -2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
WHERE c.email = 'asha@example.com';

SELECT
    o.order_id,
    c.full_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;

-- Step 3: clean the bad row and enforce business rules
DELETE FROM order_items
WHERE quantity < 1;

ALTER TABLE products
    ADD CONSTRAINT products_unit_price_positive CHECK (unit_price > 0),
    ADD CONSTRAINT products_stock_nonnegative CHECK (stock_quantity >= 0);

ALTER TABLE order_items
    ADD CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    ADD CONSTRAINT order_items_unit_price_positive CHECK (unit_price > 0);

-- Constraint test: remove the comment markers, run only this statement,
-- observe the check-constraint error, then restore the comments.
-- INSERT INTO order_items (order_id, product_id, quantity, unit_price)
-- SELECT o.order_id, p.product_id, -1, p.unit_price
-- FROM orders AS o
-- JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
-- ORDER BY o.order_id
-- LIMIT 1;

SELECT *
FROM order_items
WHERE quantity <= 0;

Your database now rejects invalid prices, stock counts, and order quantities at the storage boundary. Next, you will protect an entire checkout so its related changes succeed or disappear together.

Make Checkout Atomic

Your PostgreSQL database now rejects invalid quantities. That protects each individual row.

A checkout changes inventory plus several related rows. A transaction groups those changes into one unit so a canceled checkout cannot leave partial data behind.

In this step, get ready to:

• Stage a checkout that temporarily reduces stock and creates an order.
• Cancel the staged checkout with ROLLBACK.
• Repeat the checkout and make every change permanent with COMMIT.

Stage and cancel a checkout

The first transaction stages Marco Silva's order for two USB-C Hubs. Its inspection queries reveal the temporary stock count plus the temporary order before anything becomes permanent.

• Return to flashcart.sql in the pgAdmin Query Tool from earlier.
• Append the checkout staging block below the existing nonpositive-quantity query by copying this SQL:

-- Step 4: stage a checkout, inspect it, and cancel it
BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_name = 'USB-C Hub';

WITH new_order AS (
    INSERT INTO orders (customer_id, order_status)
    SELECT customer_id, 'PENDING'
    FROM customers
    WHERE email = 'marco@example.com'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.order_id, p.product_id, 2, p.unit_price
FROM new_order
JOIN products AS p ON p.product_name = 'USB-C Hub';

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

What does this SQL do?

• BEGIN opens a transaction that holds the following changes together.
• UPDATE temporarily reduces the USB-C Hub stock count by two.
• new_order is a data-modifying CTE that creates Marco Silva's pending order. Its RETURNING order_id output supplies the generated identifier to the line-item insert.
• The final queries inspect the temporary inventory state plus the number of pending orders.

• Save flashcart.sql.
• Highlight the new block from the Step 4 comment through the pending-order query.

Before you run this selection, what stock count and pending-order count do you expect inside the open transaction? Make your prediction now.

• Click Execute script in the Query Tool toolbar.

You will see USB-C Hub stock at 6. You will also see 1 pending order because the current transaction can inspect its own temporary changes.

Temporary checkout missing?

Confirm that you highlighted the entire block starting at BEGIN. Running only the inspection queries cannot stage the checkout.

Check the Messages tab for an earlier SQL error. An error can prevent the remaining statements from running.

help me diagnose my staged checkout

Rolling back can feel drastic. Here it only discards the changes made since BEGIN in this open transaction.

• Append the cancellation checks below the staging block by copying this SQL:

ROLLBACK;

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

How does rollback protect checkout?

• ROLLBACK discards every update and insert made by the open transaction.
• The first query checks whether the original USB-C Hub stock count returned.
• The second query checks whether the temporary pending order disappeared.

• Save flashcart.sql.
• Highlight the rollback statement plus its two inspection queries.

Before you run this selection, do you expect either temporary change to survive? Make your prediction now.

• Click Execute script in the Query Tool toolbar.

You will see USB-C Hub stock restored to 8. You will also see 0 pending orders.

That is the safety net working. The canceled checkout leaves inventory plus order data exactly as they were before the transaction.

Changes still visible after rollback?

Confirm that the staging block began with BEGIN. The rollback can only discard changes from its current transaction.

Check that you ran ROLLBACK before the two inspection queries.

help me fix my rollback check

Repeat and commit the checkout

The rollback proved that all checkout changes can disappear together. The successful path repeats the same work before COMMIT makes the stock reduction plus order permanent.

• Append the committed checkout block below the rollback checks by copying this SQL:

-- Step 4: repeat the checkout and commit it
BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_name = 'USB-C Hub';

WITH new_order AS (
    INSERT INTO orders (customer_id, order_status)
    SELECT customer_id, 'PENDING'
    FROM customers
    WHERE email = 'marco@example.com'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.order_id, p.product_id, 2, p.unit_price
FROM new_order
JOIN products AS p ON p.product_name = 'USB-C Hub';

COMMIT;

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

What makes this checkout atomic?

• BEGIN starts one transaction for the stock update plus both related inserts.
• The new_order CTE passes the generated order identifier directly into the order-item insert.
• COMMIT makes every successful change permanent as one unit.
• The final queries verify the permanent inventory state plus the committed pending order.

• Save flashcart.sql.
• Highlight the committed checkout block from its Step 4 comment through the final pending-order query.

Before you run this selection, what permanent stock count and pending-order count should prove the checkout committed? Make your prediction now.

• Click Execute script in the Query Tool toolbar.

You will see USB-C Hub stock at 6. You will also see exactly 1 pending order.

You have completed an atomic checkout. Marco Silva's two-unit order plus the matching stock reduction are now permanent together.

Order IDs Can Skip

The canceled transaction may still consume an identity value. A skipped order identifier is expected, so the checkout checks use stock plus order counts instead of consecutive identifiers.

Committed checkout does not match?

Check the Messages tab for a constraint or duplicate-key error. An earlier committed attempt can make a repeated run add another pending order.

Confirm that the stock update plus both inserts appear between BEGIN and COMMIT.

help me diagnose my committed checkout

✔️ Awesome, I've got everything!

Great. Make sure flashcart.sql is saved with both transaction demonstrations.

ⓧ I'd like to double check the full code

Compare your saved flashcart.sql with the complete script below. Use it as a reference without rerunning the earlier sections against the existing tables.

-- Step 2: relational schema and seed data
CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name varchar(100) NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    stock_quantity integer NOT NULL
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_status varchar(20) NOT NULL DEFAULT 'PENDING',
    ordered_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id integer NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (full_name, email) VALUES
    ('Asha Khan', 'asha@example.com'),
    ('Marco Silva', 'marco@example.com');

INSERT INTO products (product_name, unit_price, stock_quantity) VALUES
    ('Wireless Mouse', 25.00, 10),
    ('Mechanical Keyboard', 80.00, 5),
    ('USB-C Hub', 45.00, 8);

INSERT INTO orders (customer_id, order_status)
SELECT customer_id, 'PAID'
FROM customers
WHERE email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, 2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Wireless Mouse'
WHERE c.email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, -2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
WHERE c.email = 'asha@example.com';

SELECT
    o.order_id,
    c.full_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;

-- Step 3: clean the bad row and enforce business rules
DELETE FROM order_items
WHERE quantity < 1;

ALTER TABLE products
    ADD CONSTRAINT products_unit_price_positive CHECK (unit_price > 0),
    ADD CONSTRAINT products_stock_nonnegative CHECK (stock_quantity >= 0);

ALTER TABLE order_items
    ADD CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    ADD CONSTRAINT order_items_unit_price_positive CHECK (unit_price > 0);

-- Constraint test: remove the comment markers, run only this statement,
-- observe the check-constraint error, then restore the comments.
-- INSERT INTO order_items (order_id, product_id, quantity, unit_price)
-- SELECT o.order_id, p.product_id, -1, p.unit_price
-- FROM orders AS o
-- JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
-- ORDER BY o.order_id
-- LIMIT 1;

SELECT *
FROM order_items
WHERE quantity <= 0;

-- Step 4: stage a checkout, inspect it, and cancel it
BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_name = 'USB-C Hub';

WITH new_order AS (
    INSERT INTO orders (customer_id, order_status)
    SELECT customer_id, 'PENDING'
    FROM customers
    WHERE email = 'marco@example.com'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.order_id, p.product_id, 2, p.unit_price
FROM new_order
JOIN products AS p ON p.product_name = 'USB-C Hub';

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

ROLLBACK;

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

-- Step 4: repeat the checkout and commit it
BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_name = 'USB-C Hub';

WITH new_order AS (
    INSERT INTO orders (customer_id, order_status)
    SELECT customer_id, 'PENDING'
    FROM customers
    WHERE email = 'marco@example.com'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.order_id, p.product_id, 2, p.unit_price
FROM new_order
JOIN products AS p ON p.product_name = 'USB-C Hub';

COMMIT;

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

Your checkout now protects inventory plus order creation as one atomic operation. Next, you will turn the normalized rows into a business-ready order summary.

Produce the Order Summary

Your committed PostgreSQL transaction now protects Marco Silva's checkout as one unit. The canceled checkout also proved that inventory remains accurate after a rollback.

The useful order facts are still spread across normalized tables. In pgAdmin, SQL joins reconnect those facts. Aggregation turns the matching rows into one business summary per order.

In this step, get ready to:

• Join customers to their orders and line items.
• Calculate business metrics for each order.
• Save the complete flashcart.sql script and verify the finished report.

Build the joined summary

Each order stores its customer identifier while each line item stores its order identifier. Grouping the reconnected rows produces one result for each order.

• Switch back to the pgAdmin Query Tool connected to flashcart.
• Scroll to the bottom of flashcart.sql.
• Append the joined summary by adding this query:

-- Step 5: joined business summary
SELECT
    o.order_id,
    c.full_name,
    o.order_status,
    COUNT(oi.product_id) AS product_lines,
    SUM(oi.quantity) AS total_units,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.full_name, o.order_status
ORDER BY o.order_id;

What Does This Query Do?

• The orders table supplies the order identifier and status.
• The customer join connects each order to the matching name in customers.
• The line-item join connects each order to its matching rows in order_items.
• COUNT(oi.product_id) measures the number of product lines in each order.
• SUM(oi.quantity) calculates the total number of units.
• SUM(oi.quantity * oi.unit_price) calculates the exact order value.
• GROUP BY keeps the calculations separate for each order.

• Use the Query Tool's file controls to save flashcart.sql.

Before you run the query, how many order rows do you expect the grouping to return?

• Highlight only the final SELECT query.
• Select Execute script in the Query Tool toolbar.

The Data Output tab shows two orders.

Asha Khan's paid order has a total of 50.00. Marco Silva's pending order has a total of 90.00.

You've got it. The normalized checkout data now reads as a two-row business report.

Report Missing an Order?

Check that the customer join uses c.customer_id = o.customer_id. Check that the line-item join uses oi.order_id = o.order_id.

If a total looks wrong, compare your calculation with SUM(oi.quantity * oi.unit_price).

help me debug my FlashCart order summary

Verify the finished script

Your saved script now contains the complete FlashCart build from the initial schema through the final report. Use this comparison to confirm that the portfolio artifact is complete.

✔️ Awesome, I've got everything!

Great. Make sure flashcart.sql is saved before the final check.

ⓧ I'd like to double check the full code

Compare your saved flashcart.sql file with the complete project script below:

-- Step 2: relational schema and seed data
CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name varchar(100) NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    stock_quantity integer NOT NULL
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_status varchar(20) NOT NULL DEFAULT 'PENDING',
    ordered_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id integer NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id integer NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (full_name, email) VALUES
    ('Asha Khan', 'asha@example.com'),
    ('Marco Silva', 'marco@example.com');

INSERT INTO products (product_name, unit_price, stock_quantity) VALUES
    ('Wireless Mouse', 25.00, 10),
    ('Mechanical Keyboard', 80.00, 5),
    ('USB-C Hub', 45.00, 8);

INSERT INTO orders (customer_id, order_status)
SELECT customer_id, 'PAID'
FROM customers
WHERE email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, 2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Wireless Mouse'
WHERE c.email = 'asha@example.com';

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT o.order_id, p.product_id, -2, p.unit_price
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
WHERE c.email = 'asha@example.com';

SELECT
    o.order_id,
    c.full_name,
    p.product_name,
    oi.quantity,
    oi.unit_price
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN products AS p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_name;

-- Step 3: clean the bad row and enforce business rules
DELETE FROM order_items
WHERE quantity < 1;

ALTER TABLE products
    ADD CONSTRAINT products_unit_price_positive CHECK (unit_price > 0),
    ADD CONSTRAINT products_stock_nonnegative CHECK (stock_quantity >= 0);

ALTER TABLE order_items
    ADD CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    ADD CONSTRAINT order_items_unit_price_positive CHECK (unit_price > 0);

-- Constraint test: remove the comment markers, run only this statement,
-- observe the check-constraint error, then restore the comments.
-- INSERT INTO order_items (order_id, product_id, quantity, unit_price)
-- SELECT o.order_id, p.product_id, -1, p.unit_price
-- FROM orders AS o
-- JOIN products AS p ON p.product_name = 'Mechanical Keyboard'
-- ORDER BY o.order_id
-- LIMIT 1;

SELECT *
FROM order_items
WHERE quantity <= 0;

-- Step 4: stage a checkout, inspect it, and cancel it
BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_name = 'USB-C Hub';

WITH new_order AS (
    INSERT INTO orders (customer_id, order_status)
    SELECT customer_id, 'PENDING'
    FROM customers
    WHERE email = 'marco@example.com'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.order_id, p.product_id, 2, p.unit_price
FROM new_order
JOIN products AS p ON p.product_name = 'USB-C Hub';

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

ROLLBACK;

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

-- Step 4: repeat the checkout and commit it
BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_name = 'USB-C Hub';

WITH new_order AS (
    INSERT INTO orders (customer_id, order_status)
    SELECT customer_id, 'PENDING'
    FROM customers
    WHERE email = 'marco@example.com'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT new_order.order_id, p.product_id, 2, p.unit_price
FROM new_order
JOIN products AS p ON p.product_name = 'USB-C Hub';

COMMIT;

SELECT product_name, stock_quantity
FROM products
WHERE product_name = 'USB-C Hub';

SELECT COUNT(*) AS pending_orders
FROM orders
WHERE order_status = 'PENDING';

-- Step 5: joined business summary
SELECT
    o.order_id,
    c.full_name,
    o.order_status,
    COUNT(oi.product_id) AS product_lines,
    SUM(oi.quantity) AS total_units,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.full_name, o.order_status
ORDER BY o.order_id;

Why Run Only the Final Query?

The complete script starts by creating the FlashCart tables from scratch. Your current database already contains those tables.

Selecting only the final report reads the finished data without repeating the earlier setup or checkout operations.

Before you rerun the report, do you expect its two order totals to stay the same?

• Highlight only the final joined summary query.
• Select Execute script in the Query Tool toolbar.

The Data Output tab again shows two orders with totals of 50.00 and 90.00.

Final Report Changed?

Confirm that you highlighted only the final joined summary. Selecting earlier statements can repeat operations that are outside this verification.

help me restore the expected FlashCart report

Your FlashCart database now stores protected checkout data and turns it into a clear order summary. The complete flashcart.sql file demonstrates relational design, integrity rules, transactions, joins, and aggregation in one project.

Secret mission

Correct One Part of a Checkout with a Savepoint

A draft order line can be wrong even when the earlier stock update is valid. Use a savepoint to replace only that draft line while preserving the inventory change.

Clean Up Your Resources

Clean Up Your Resources

All FlashCart resources run locally, so keeping them costs $0. Choose whether to keep the database available, pause its service, or delete the database and SQL files.

Resources you used:

• Local PostgreSQL database flashcart.
• Local setup.sql file.
• Local flashcart.sql file.
• Local savepoint_mission.sql file.

Keep everything running

Keeping everything requires no action. Choose this if you want to run more queries or extend FlashCart later.

• The PostgreSQL service stays running on your computer.
• The flashcart database retains its schema and checkout data.
• Your pgAdmin Query Tool remains available for database work.
• Your three SQL files remain available as a record of the project.

Pause - I'll come back to this later

Pausing closes pgAdmin. Stopping the local PostgreSQL service frees memory while your database stays on disk.

The service can be easy to miss in the long Windows list.

• Switch back to pgAdmin.
• Close pgAdmin.
• Press the Windows key to open the search bar.
• Type Services and press Enter to open the Services application.
• Locate the PostgreSQL service in the list.
• Stop the PostgreSQL service from its context menu.
• Confirm that the service no longer has a running status.

Your flashcart database remains stored on your computer while the service is stopped.

• Start the same PostgreSQL service from the Services application when you return.
• Reconnect to flashcart in pgAdmin.

Delete - I don't want to use this again

Choose this when you no longer need the FlashCart database or its SQL files. PostgreSQL and pgAdmin remain installed for future projects.

Delete the FlashCart database

Deletion is permanent

Deleting the database can feel drastic because it removes the complete schema and every stored order. A deleted database cannot be restored through PostgreSQL.

• Close the Query Tool that is connected to flashcart.
• Select the postgres database in pgAdmin's Object Explorer.
• Open the database's context menu.
• Choose Query Tool.
• Delete flashcart by running this SQL:

DROP DATABASE flashcart;

What does this SQL do?

This statement removes the flashcart database and everything stored inside it. Running it from postgres keeps your active connection outside the database being deleted.

• Refresh the database tree in Object Explorer.

You should no longer see flashcart in the tree. That confirms the schema and stored orders are gone.

Database still visible?

• Close every remaining Query Tool connected to flashcart.
• Run the deletion SQL again from the Query Tool connected to postgres.

Help me fix a blocked FlashCart database deletion.

Delete the local SQL files

Deleting the database leaves the SQL files on your computer. Remove those files separately to complete the cleanup.

• Press the Windows key to open the search bar.
• Type File Explorer and press Enter to open it.
• Navigate to the folder where you saved the project files.
• Select setup.sql, flashcart.sql, and savepoint_mission.sql.

Permanent deletion uses a confirmation prompt, so you have one final chance to check the filenames.

• Press Shift+Delete to delete the selected files permanently.
• Confirm the permanent deletion.
• Check the folder where the three files were saved.

You should no longer see setup.sql, flashcart.sql, or savepoint_mission.sql in that folder. Your FlashCart project resources are now removed.

Nice Work!

Nice Work!

You did it! You built FlashCart's reliable checkout database with PostgreSQL.

You've learned how to:

• Build a normalized relational schema that connects FlashCart's four core tables. You also enforced its business rules with named CHECK constraints.
• Protect every checkout change inside an atomic transaction. You used ROLLBACK to cancel a checkout. You used COMMIT to make a successful checkout permanent.
• Produce a business-ready order summary from normalized rows. You used joins with aggregation to show each order's status. The report also shows its line count. It includes total units plus total value.
• Secret Mission: Use a savepoint to preserve the valid inventory update. You rolled back only the draft line item. You stored the corrected one-unit replacement without discarding the rest of the checkout.

Ready to quiz yourself?