Composite types in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a composite type named 'person'.

PostgreSQL

CREATE TYPE person [1] (name text, age integer);

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

AWITH

BIS

CAS

DBY

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'WITH' or 'IS' instead of 'AS' causes syntax errors.

Omitting the keyword entirely.

The CREATE TYPE statement uses AS to define the structure of the composite type.

2fill in blank

medium

Complete the code to insert a value into a table with a composite type column.

PostgreSQL

INSERT INTO employees (info) VALUES (ROW('Alice', [1]));

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

A'30'

B'thirty'

Cage

D30

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the number in quotes makes it a string, causing type mismatch.

Using a variable name that is not defined.

The ROW constructor needs the age as an integer without quotes.

3fill in blank

hard

Fix the error in the SELECT statement to access the 'name' field of the composite type column 'info'.

PostgreSQL

SELECT info[1]name FROM employees;

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

A.

B->

C->>

D::

Attempts:

3 left

💡 Hint

Common Mistakes

Using JSON operators causes errors.

Using type cast operator :: instead of field access.

Use the dot . operator to access fields of a composite type in PostgreSQL.

4fill in blank

hard

Fill both blanks to create a composite type and use it in a table.

PostgreSQL

CREATE TYPE address AS (street [1], city [2]);
CREATE TABLE locations (id serial PRIMARY KEY, addr address);

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

Atext

Binteger

Cvarchar

Dboolean

Attempts:

3 left

💡 Hint

Common Mistakes

Using integer or boolean types for text fields.

Mixing different text types unnecessarily.

Both fields 'street' and 'city' should be of type text in the composite type.

5fill in blank

hard

Fill all three blanks to select the 'city' from the 'addr' composite column and filter by 'street'.

PostgreSQL

SELECT addr[1]city FROM locations WHERE addr[2]street = [3]'Main St';

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

A.

C'

D"

Attempts:

3 left

💡 Hint

Common Mistakes

Using double quotes for string literals causes errors.

Using wrong operators for field access.

Use the dot operator to access composite fields and single quotes for string literals.