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

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

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

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

Hint

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

Practice

1. Why is server-side programming important for managing data in a database?

easy

A. It centralizes data control and keeps data secure.

B. It runs only on the user's device.

C. It makes the website load slower.

D. It stores data only in the browser.

Solution

1. Step 1: Understand server-side role

   Server-side programming runs on a central server, not on user devices.

2. Step 2: Identify data management benefits

   This central control helps keep data safe and organized for all users.

3. Final Answer:

   It centralizes data control and keeps data secure. -> Option A

4. Quick Check:

   Server-side = central control and security [OK]

Hint: Server-side means central control of data [OK]

Common Mistakes:

• Thinking server-side runs on user devices
• Believing data is stored only in browsers
• Assuming server-side slows down websites

2. Which of the following is the correct way to write a simple SQL query in PostgreSQL to select all rows from a table named users?

easy

A. FETCH * users;

B. SELECT * FROM users;

C. GET ALL FROM users;

D. SELECT ALL users;

Solution

1. Step 1: Recall SQL SELECT syntax

   The correct syntax to get all rows is SELECT * FROM table_name;.

2. Step 2: Match syntax to options

   Only SELECT * FROM users; matches the correct SQL syntax for PostgreSQL.

3. Final Answer:

   SELECT * FROM users; -> Option B

4. Quick Check:

   SELECT * FROM table = correct query [OK]

Hint: SELECT * FROM table_name; is the standard query [OK]

Common Mistakes:

• Using GET or FETCH instead of SELECT
• Omitting FROM keyword
• Adding ALL incorrectly

3. Consider this PostgreSQL query run on a server:

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');
SELECT * FROM users ORDER BY id;

What will be the output of the SELECT query?

medium

A. [{"id":1, "name":"Alice"}, {"id":2, "name":"Bob"}]

B. [{"name":"Alice"}, {"name":"Bob"}]

C. [{1, 'Alice'}, {2, 'Bob'}]

D. Syntax error

Solution

1. Step 1: Understand the INSERT commands

   Two rows are inserted with ids 1 and 2 and names Alice and Bob.

2. Step 2: Analyze the SELECT query

   The SELECT fetches all rows ordered by id, so rows appear in order 1 then 2.

3. Final Answer:

   [{"id":1, "name":"Alice"}, {"id":2, "name":"Bob"}] -> Option A

4. Quick Check:

   Inserted rows appear ordered by id [OK]

Hint: SELECT * ORDER BY id returns rows sorted by id [OK]

Common Mistakes:

• Ignoring ORDER BY and expecting random order
• Expecting only names without ids
• Thinking syntax error due to multiple inserts

4. You wrote this PostgreSQL query on the server:

SELECT name FROM users WHERE id = 'two';

But it returns no rows. What is the likely problem?

medium

A. The table users does not exist.

B. The query is missing a semicolon.

C. The SELECT keyword is misspelled.

D. The id column expects a number, but 'two' is a string.

Solution

1. Step 1: Check data type of id column

   Usually, id columns are numeric, so comparing to string 'two' fails to match.

2. Step 2: Understand why no rows return

   Since no id equals the string 'two', the query returns empty result.

3. Final Answer:

   The id column expects a number, but 'two' is a string. -> Option D

4. Quick Check:

   Data type mismatch causes no rows [OK]

Hint: Match data types in WHERE clause [OK]

Common Mistakes:

• Assuming table does not exist without checking
• Thinking missing semicolon causes no rows
• Ignoring data type mismatch

5. A web app uses server-side programming to handle user logins securely. Which of these is a key reason server-side code improves security compared to client-side only?

hard

A. Server-side code stores passwords in the browser cache.

B. Server-side code runs faster on the user's device.

C. Server-side code keeps passwords hidden and checks them safely on the server.

D. Server-side code allows users to see all database details.

Solution

1. Step 1: Understand server-side security role

   Server-side code processes sensitive data like passwords away from the user's device.

2. Step 2: Identify why this improves security

   Keeping passwords on the server prevents exposure and unauthorized access.

3. Final Answer:

   Server-side code keeps passwords hidden and checks them safely on the server. -> Option C

4. Quick Check:

   Server-side hides sensitive data from users [OK]

Hint: Passwords checked on server stay secure [OK]

Common Mistakes:

• Thinking server-side runs on user device
• Believing passwords are stored in browser cache
• Assuming users can see database details