Why schemas matter in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the table named 'employees' in the schema 'hr'.

PostgreSQL

SELECT * FROM hr.[1];

Drag options to blanks, or click blank then click option'

Apositions

Bdepartments

Csalaries

Demployees

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to specify the schema name before the table.

Using the wrong table name.

The table 'employees' is in the 'hr' schema, so we use hr.employees to access it.

2fill in blank

medium

Complete the code to create a new schema named 'sales'.

PostgreSQL

CREATE SCHEMA [1];

Drag options to blanks, or click blank then click option'

Amarketing

Bsales

Chr

Dfinance

Attempts:

3 left

💡 Hint

Common Mistakes

Using a table name instead of a schema name.

Misspelling the schema name.

To create a schema named 'sales', use CREATE SCHEMA sales;.

3fill in blank

hard

Fix the error in the code to insert a row into the 'orders' table inside the 'sales' schema.

PostgreSQL

INSERT INTO [1].orders (order_id, amount) VALUES (1, 100);

Drag options to blanks, or click blank then click option'

Asales

Bpublic

Chr

Dfinance

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong schema name.

Omitting the schema name.

The 'orders' table is in the 'sales' schema, so use sales.orders to insert data.

4fill in blank

hard

Fill both blanks to grant SELECT permission on the 'customers' table in the 'crm' schema to user 'alice'.

PostgreSQL

GRANT [1] ON [2].customers TO alice;

Drag options to blanks, or click blank then click option'

ASELECT

BINSERT

Ccrm

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Granting the wrong permission like INSERT.

Using the wrong schema name.

To allow 'alice' to read data, grant SELECT on the 'customers' table in the 'crm' schema.

5fill in blank

hard

Fill all three blanks to create a table named 'products' in the 'inventory' schema with columns 'id' (integer) and 'name' (text).

PostgreSQL

CREATE TABLE [1].[2] ([3]);

Drag options to blanks, or click blank then click option'

Ainventory

Bproducts

Cid INTEGER, name TEXT

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong schema or table name.

Incorrect column definitions.

We create the 'products' table inside the 'inventory' schema with columns 'id' as integer and 'name' as text.

Practice

1. What is the main purpose of schemas in PostgreSQL?

easy

A. To speed up query execution automatically

B. To store user passwords securely

C. To organize database objects like folders

D. To backup the entire database

Solution

1. Step 1: Understand what schemas do

   Schemas act like folders to group tables, views, and other objects in a database.

2. Step 2: Identify the main purpose

   This grouping helps organize and separate objects logically.

3. Final Answer:

   To organize database objects like folders -> Option C

4. Quick Check:

   Schemas = Organize objects [OK]

Hint: Think of schemas as folders for database objects [OK]

Common Mistakes:

• Confusing schemas with backups
• Thinking schemas speed up queries automatically
• Believing schemas store passwords

2. Which of the following is the correct way to create a schema named sales in PostgreSQL?

easy

A. NEW SCHEMA sales;

B. MAKE SCHEMA sales;

C. CREATE DATABASE sales;

D. CREATE SCHEMA sales;

Solution

1. Step 1: Recall PostgreSQL syntax for schema creation

   The correct command to create a schema is CREATE SCHEMA schema_name;.

2. Step 2: Match the options

   Only CREATE SCHEMA sales; uses the correct syntax CREATE SCHEMA sales;.

3. Final Answer:

   CREATE SCHEMA sales; -> Option D

4. Quick Check:

   CREATE SCHEMA = create schema [OK]

Hint: Use CREATE SCHEMA to make new schemas [OK]

Common Mistakes:

• Using MAKE SCHEMA instead of CREATE SCHEMA
• Confusing CREATE SCHEMA with CREATE DATABASE
• Using NEW SCHEMA which is invalid syntax

3. Given two schemas public and sales both containing a table named customers, what will the query SELECT * FROM customers; return if the search_path is set to sales, public?

medium

A. Rows from sales.customers table

B. Rows from public.customers table

C. An error because of ambiguous table name

D. No rows because table is not found

Solution

1. Step 1: Understand search_path order

   PostgreSQL looks for tables in schemas in the order listed in search_path.

2. Step 2: Apply search_path to table lookup

   Since sales is first, customers from sales schema is used.

3. Final Answer:

   Rows from sales.customers table -> Option A

4. Quick Check:

   search_path order decides table used [OK]

Hint: search_path order picks first matching schema table [OK]

Common Mistakes:

• Assuming ambiguous error occurs
• Thinking public schema is always default
• Believing query fails if multiple tables exist

4. You run CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, amount NUMERIC); but get an error saying schema "sales" does not exist. What is the best fix?

medium

A. Restart PostgreSQL server

B. Create the sales schema first using CREATE SCHEMA sales;

C. Change table name to public.orders

D. Use CREATE DATABASE sales; instead

Solution

1. Step 1: Identify cause of error

   Error says schema "sales" does not exist, so PostgreSQL cannot create table there.

2. Step 2: Fix by creating missing schema

   Run CREATE SCHEMA sales; to create the schema before creating the table.

3. Final Answer:

   Create the sales schema first using CREATE SCHEMA sales; -> Option B

4. Quick Check:

   Missing schema? Create it first [OK]

Hint: Create schema before creating tables in it [OK]

Common Mistakes:

• Trying to create database instead of schema
• Restarting server unnecessarily
• Changing table name without schema

5. You want to restrict access so only the marketing team can read tables in the marketing schema, while the sales team can only access sales schema tables. Which approach best uses schemas to achieve this?

hard

A. Create separate schemas marketing and sales and grant SELECT only to respective teams

B. Put all tables in public schema and grant SELECT to both teams

C. Create one schema and use table-level permissions for each team

D. Use different databases for marketing and sales teams

Solution

1. Step 1: Understand schema-based permission control

   Schemas allow grouping tables and setting permissions at schema level.

2. Step 2: Apply separate schemas with team-specific grants

   Creating marketing and sales schemas and granting SELECT only to respective teams isolates access cleanly.

3. Final Answer:

   Create separate schemas marketing and sales and grant SELECT only to respective teams -> Option A

4. Quick Check:

   Schemas help isolate permissions by team [OK]

Hint: Use schemas to separate data and control access [OK]

Common Mistakes:

• Granting all access in public schema
• Using one schema with complex table permissions
• Using separate databases unnecessarily