Creating databases and connecting in PostgreSQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this database creation query?

Consider the following SQL command executed in PostgreSQL:

CREATE DATABASE testdb WITH OWNER = alice ENCODING = 'UTF8' CONNECTION LIMIT = 5;

What will be the result of this command?

PostgreSQL

CREATE DATABASE testdb WITH OWNER = alice ENCODING = 'UTF8' CONNECTION LIMIT = 5;

A Syntax error due to incorrect use of CONNECTION LIMIT.

B Error because owner 'alice' does not exist.

C Database 'testdb' is created but with default encoding and unlimited connections.

D Database 'testdb' is created with owner 'alice', UTF8 encoding, and max 5 connections.

💡 Hint

Check the syntax for CREATE DATABASE and the parameters allowed.

The command correctly creates a database named 'testdb' owned by user 'alice' with UTF8 encoding and limits connections to 5. CONNECTION LIMIT is valid syntax in PostgreSQL.

📝 Syntax

intermediate

Identify the syntax error in this database connection command

Which option shows a correct way to connect to a PostgreSQL database named 'shopdb' using psql command line?

A psql -d shopdb -U admin

B psql --database shopdb --user admin

C psql -database shopdb -user admin

D psql -db shopdb -username admin

💡 Hint

Check the correct short options for database and user in psql.

Option A uses correct short options '-d' for database and '-U' for user. Options A and D use invalid flags, and B uses long options that do not exist.

❓ optimization

advanced

Which command optimizes database creation for a template?

You want to create a new database 'analytics' that is an exact copy of an existing database 'template_db'. Which command achieves this efficiently?

A CREATE DATABASE analytics COPY template_db;

B CREATE DATABASE analytics AS template_db;

C CREATE DATABASE analytics TEMPLATE template_db;

D CREATE DATABASE analytics CLONE template_db;

💡 Hint

PostgreSQL supports creating a database from a template using a specific keyword.

Option C uses the correct syntax 'TEMPLATE' to create a database from an existing one. Other options are invalid syntax.

🔧 Debug

advanced

Why does this connection attempt fail?

A user tries to connect to PostgreSQL using:

psql -d salesdb -U john

But gets the error:

psql: FATAL: role "john" does not exist

What is the most likely cause?

A The database 'salesdb' does not exist.

B The user 'john' has not been created as a PostgreSQL role.

C The password for 'john' is incorrect.

D The psql command is missing the host parameter.

💡 Hint

The error message mentions the role does not exist.

PostgreSQL roles represent users. If the role 'john' does not exist, connection fails regardless of database existence or password.

🧠 Conceptual

expert

What is the effect of connecting to a database with the wrong encoding?

If a client connects to a PostgreSQL database that was created with ENCODING = 'UTF8' but the client uses a different encoding like LATIN1, what is the most likely outcome?

A Data sent or received may be corrupted or cause errors due to encoding mismatch.

B PostgreSQL automatically converts all data to the client encoding without issues.

C The connection will be refused by the server immediately.

D The database encoding changes to match the client encoding for the session.

💡 Hint

Think about how character encoding affects data interpretation.

If client encoding differs from database encoding, data can be misinterpreted or corrupted unless properly converted. PostgreSQL tries to convert but may fail or produce errors.