Why server-side programming matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this server-side function call?

Consider a PostgreSQL function that returns the current user and the current timestamp. What will be the output of the following query?

SELECT current_user, now();

PostgreSQL

SELECT current_user, now();

A Returns only the current date without time.

B Returns an error because now() requires parameters.

C Returns the current database user name and the current date and time.

D Returns NULL values for both columns.

💡 Hint

Think about what built-in PostgreSQL functions like current_user and now() do.

current_user returns the name of the current database user. now() returns the current date and time. Both are built-in functions that do not require parameters.

🧠 Conceptual

intermediate

Why is server-side programming important for databases?

Which of the following best explains why server-side programming matters in database applications?

A It stores all data in the user's browser for faster access.

B It makes the database client-side only, so users can access data offline.

C It eliminates the need for any client applications to interact with the database.

D It allows processing data close to where it is stored, reducing data transfer and improving performance.

💡 Hint

Think about where the data lives and how processing it near the data can help.

Server-side programming runs code on the database server, close to the data. This reduces the amount of data sent over the network and speeds up processing.

📝 Syntax

advanced

Which PostgreSQL function definition is syntactically correct?

Identify the correct syntax for creating a simple PostgreSQL function that returns the current timestamp.

A CREATE FUNCTION get_time() RETURNS timestamp AS $$ BEGIN RETURN now(); END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION get_time RETURNS timestamp AS $$ BEGIN RETURN now(); END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION get_time() RETURNS timestamp BEGIN RETURN now(); END LANGUAGE plpgsql;

D CREATE FUNCTION get_time() RETURNS timestamp AS BEGIN RETURN now(); END; LANGUAGE plpgsql;

💡 Hint

Remember the correct order and keywords for defining a function in PostgreSQL.

Option A correctly uses parentheses for parameters, specifies RETURNS, uses AS with dollar quoting, and ends with LANGUAGE plpgsql.

❓ optimization

advanced

How does server-side programming improve query performance?

Which of the following best describes how server-side programming can optimize database query performance?

A By executing complex logic inside the database server, reducing network traffic and speeding up data processing.

B By moving all data processing to the client, freeing the server from any load.

C By duplicating data across multiple clients to avoid server queries.

D By disabling indexes to speed up insert operations.

💡 Hint

Consider where the heavy lifting happens in server-side programming.

Running logic inside the database server reduces the need to send large amounts of data over the network and allows the server to use indexes and optimizations effectively.

🔧 Debug

expert

What error does this server-side function produce?

Given the following PostgreSQL function, what error will occur when calling it?

CREATE FUNCTION faulty_func() RETURNS integer AS $$ BEGIN RETURN 'text'; END; $$ LANGUAGE plpgsql;

PostgreSQL

SELECT faulty_func();

A ERROR: syntax error near 'RETURN'

B ERROR: return type mismatch: function declared to return integer but returns text

C No error, returns 0

D ERROR: function does not exist

💡 Hint

Check the return type declared and the actual returned value type.

The function declares it returns an integer but tries to return a text string, causing a type mismatch error.

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