What is PostgreSQL - Practice Questions & Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is PostgreSQL primarily used for?

Choose the best description of PostgreSQL's main purpose.

A A relational database management system to store and manage data.

B A web server software to host websites.

C A programming language for building mobile apps.

D A tool for graphic design and image editing.

💡 Hint

Think about where data is stored and organized for applications.

PostgreSQL is a powerful system designed to store, organize, and retrieve data efficiently.

🧠 Conceptual

intermediate

Which feature is a key strength of PostgreSQL?

Select the feature that best describes PostgreSQL's capabilities.

A Is mainly used for video editing and rendering.

B Only stores data in simple text files without structure.

C Runs only on Windows operating systems.

D Supports complex queries and transactions with data integrity.

💡 Hint

Think about how databases handle multiple operations safely.

PostgreSQL supports complex queries and ensures data integrity through transactions.

❓ query_result

advanced

What is the output of this PostgreSQL query?

Given the table 'employees' with columns 'id' and 'name', what does this query return?

SELECT COUNT(*) FROM employees WHERE name LIKE 'A%';

PostgreSQL

SELECT COUNT(*) FROM employees WHERE name LIKE 'A%';

A The total number of employees in the table.

B The number of employees whose names start with 'A'.

C An error because LIKE cannot be used with COUNT.

D A list of employee names starting with 'A'.

💡 Hint

COUNT(*) counts rows matching the condition.

The query counts how many employees have names starting with 'A'.

📝 Syntax

advanced

Which option is a valid PostgreSQL command to create a table?

Identify the correct SQL syntax to create a table named 'products' with columns 'id' (integer) and 'name' (text).

A CREATE products TABLE (id INTEGER, name TEXT);

B MAKE TABLE products {id INTEGER, name STRING};

C CREATE TABLE products (id INT, name TEXT);

D TABLE CREATE products (id INT, name TEXT);

💡 Hint

The command starts with CREATE TABLE followed by table name and columns.

Option C uses correct PostgreSQL syntax for creating a table.

❓ optimization

expert

Which index type is best for speeding up text search in PostgreSQL?

You want to optimize searches for words inside large text columns. Which index type should you use?

A GIN (Generalized Inverted Index)

B Hash index

C B-tree index

D Bitmap index

💡 Hint

Think about indexes designed for full-text search.

GIN indexes are optimized for searching within text and arrays efficiently.

Practice

1. What is PostgreSQL primarily used for?

easy

A. Creating graphics

B. Designing websites

C. Storing and managing data using tables

D. Writing mobile apps

Solution

1. Step 1: Understand PostgreSQL's purpose

   PostgreSQL is a database system designed to store and manage data.

2. Step 2: Compare options with PostgreSQL's use

   Only storing and managing data using tables matches PostgreSQL's function.

3. Final Answer:

   Storing and managing data using tables -> Option C

4. Quick Check:

   PostgreSQL = Data storage and management [OK]

Hint: PostgreSQL is a database, not a design or app tool [OK]

Common Mistakes:

• Confusing PostgreSQL with web design tools
• Thinking PostgreSQL creates graphics
• Assuming PostgreSQL is for mobile apps

2. Which of the following is the correct way to start a SQL query in PostgreSQL?

easy

A. GET * FROM table_name;

B. SELECT * FROM table_name;

C. FIND * IN table_name;

D. SHOW ALL FROM table_name;

Solution

1. Step 1: Recall SQL syntax for retrieving data

   The standard SQL command to get all data from a table is SELECT * FROM table_name;

2. Step 2: Check each option's validity

   Only SELECT * FROM table_name; uses correct SQL syntax; others are invalid commands.

3. Final Answer:

   SELECT * FROM table_name; -> Option B

4. Quick Check:

   Correct SQL SELECT syntax = SELECT * FROM table_name; [OK]

Hint: SQL queries start with SELECT to get data [OK]

Common Mistakes:

• Using GET instead of SELECT
• Using FIND or SHOW incorrectly
• Missing semicolon at end

3. What will be the result of this query in PostgreSQL?

CREATE TABLE fruits (id INT, name TEXT);
INSERT INTO fruits VALUES (1, 'Apple'), (2, 'Banana');
SELECT name FROM fruits WHERE id = 2;

medium

A. Banana

B. Apple

C. 1

D. No rows returned

Solution

1. Step 1: Understand the table and inserted data

   The table 'fruits' has two rows: (1, 'Apple') and (2, 'Banana').

2. Step 2: Analyze the SELECT query condition

   The query selects 'name' where id = 2, which matches 'Banana'.

3. Final Answer:

   Banana -> Option A

4. Quick Check:

   id=2 corresponds to Banana [OK]

Hint: Match WHERE condition to row values to find output [OK]

Common Mistakes:

• Confusing id with name
• Expecting multiple rows
• Thinking query returns id instead of name

4. Identify the error in this PostgreSQL query:

INSERT INTO users (id, name VALUES (1, 'John');

medium

A. Missing quotes around 'John'

B. Wrong table name

C. Using INSERT instead of UPDATE

D. Missing closing parenthesis after column names

Solution

1. Step 1: Check syntax of INSERT statement

   The column list must be enclosed in parentheses: (id, name)

2. Step 2: Identify missing parenthesis

   The query misses the closing parenthesis after 'name', causing syntax error.

3. Final Answer:

   Missing closing parenthesis after column names -> Option D

4. Quick Check:

   Parentheses must enclose columns list [OK]

Hint: Count parentheses carefully in SQL statements [OK]

Common Mistakes:

• Forgetting closing parenthesis
• Misplacing quotes
• Confusing INSERT with UPDATE

5. You want to store user information with unique IDs and names in PostgreSQL. Which feature ensures IDs are unique and automatically assigned?

hard

A. PRIMARY KEY with SERIAL type

B. TEXT column with UNIQUE constraint

C. FOREIGN KEY referencing another table

D. CHECK constraint on name length

Solution

1. Step 1: Understand uniqueness and auto-assignment

   Unique IDs that auto-increment are done using PRIMARY KEY with SERIAL type in PostgreSQL.

2. Step 2: Evaluate other options

   TEXT with UNIQUE only ensures uniqueness but no auto-assignment; FOREIGN KEY links tables; CHECK validates data but doesn't assign IDs.

3. Final Answer:

   PRIMARY KEY with SERIAL type -> Option A

4. Quick Check:

   Auto-increment unique IDs = PRIMARY KEY + SERIAL [OK]

Hint: Use SERIAL type with PRIMARY KEY for auto IDs [OK]

Common Mistakes:

• Using UNIQUE without auto-increment
• Confusing FOREIGN KEY with unique ID
• Using CHECK for uniqueness