Why server-side programming matters in PostgreSQL - See It in Action

Why server-side programming matters

📖 Scenario: You are building a simple online bookstore database. Customers can browse books and place orders. To manage this, you need to create tables and queries that run on the server to store and retrieve data securely and efficiently.

🎯 Goal: Build a small PostgreSQL database with tables for books and orders. Then write queries to insert data and retrieve orders for a specific customer. This project shows why server-side programming is important for managing data safely and reliably.

📋 What You'll Learn

Create a books table with columns book_id, title, and price

Create an orders table with columns order_id, customer_name, and book_id

Insert at least two books into the books table

Insert at least one order into the orders table

Write a query to select all orders for a given customer_name

💡 Why This Matters

🌍 Real World

Online stores, booking systems, and many web applications use server-side databases to store and manage data safely.

💼 Career

Understanding server-side database programming is essential for backend developers, data analysts, and anyone working with data-driven applications.

1

Create the books table

Write SQL to create a table called books with columns: book_id as an integer primary key, title as text, and price as numeric.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types.

2

Create the orders table

Write SQL to create a table called orders with columns: order_id as an integer primary key, customer_name as text, and book_id as integer referencing books(book_id).

PostgreSQL

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);
-- Your code here

Need a hint?

Remember to link book_id in orders to books using REFERENCES.

3

Insert data into books and orders

Write SQL to insert two books into books with book_id 1 and 2, titles 'Learn SQL' and 'Database Basics', prices 29.99 and 39.99. Then insert one order into orders with order_id 1, customer_name 'Alice', and book_id 1.

PostgreSQL

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    book_id INTEGER REFERENCES books(book_id)
);
-- Your code here

Need a hint?

Use INSERT INTO with the exact values given.

4

Query orders for a specific customer

Write a SQL query to select all columns from orders where customer_name is 'Alice'.

PostgreSQL

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    price NUMERIC
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    book_id INTEGER REFERENCES books(book_id)
);

INSERT INTO books (book_id, title, price) VALUES
(1, 'Learn SQL', 29.99),
(2, 'Database Basics', 39.99);

INSERT INTO orders (order_id, customer_name, book_id) VALUES
(1, 'Alice', 1);

-- Your code here

Need a hint?

Use SELECT * FROM orders WHERE customer_name = 'Alice' to get Alice's orders.