pgAdmin graphical interface overview in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to list all tables in the current database.

PostgreSQL

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = [1];

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

A'public'

B'main'

C'system'

D'default'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong schema name like 'main' or 'default' which do not exist by default.

Forgetting to put the schema name in quotes.

In PostgreSQL, the default schema where user tables are stored is called public. So, to list all tables, we filter by schemaname = 'public'.

2fill in blank

medium

Complete the code to show the first 5 rows of the table named 'employees'.

PostgreSQL

SELECT * FROM employees [1] 5;

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

AFIRST

BTOP

CLIMIT

DROWS

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'TOP' which is used in SQL Server, not PostgreSQL.

Using 'FIRST' or 'ROWS' which are not valid PostgreSQL keywords.

In PostgreSQL, to limit the number of rows returned, we use the LIMIT clause.

3fill in blank

hard

Fix the error in the code to rename a table from 'old_name' to 'new_name'.

PostgreSQL

ALTER TABLE [1] RENAME TO new_name;

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

Anew_name

Bold_name

Ctable_name

Drename_table

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the new table name where the old name should be.

Using a generic name like 'table_name' instead of the actual table name.

To rename a table, you specify the current table name after ALTER TABLE. So, use 'old_name' there.

4fill in blank

hard

Fill both blanks to create a new database named 'testdb' owned by user 'admin'.

PostgreSQL

CREATE DATABASE [1] OWNER [2];

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

Atestdb

Badmin

Cpostgres

Duser1

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the database name and owner.

Using default users like 'postgres' instead of the specified 'admin'.

The database name goes right after CREATE DATABASE, and the owner username goes after OWNER.

5fill in blank

hard

Fill all three blanks to grant SELECT and INSERT privileges on table 'customers' to user 'guest'.

PostgreSQL

GRANT [1], [2] ON [3] TO guest;

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

ASELECT

BINSERT

Ccustomers

DUPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong privileges like 'UPDATE' instead of 'INSERT'.

Forgetting to separate privileges with a comma.

Putting the user name in the wrong place.

We list the privileges to grant separated by commas, then specify the table name after ON.