CREATE VIEW syntax in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a view named 'active_users' that selects all columns from the 'users' table where 'status' is 'active'.

PostgreSQL

CREATE VIEW active_users AS SELECT * FROM users WHERE status = [1];

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

Aactive

B'active'

C"active"

DACTIVE

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put single quotes around string values.

Using double quotes which are for identifiers, not strings.

The value 'active' must be a string literal in SQL, so it needs single quotes.

2fill in blank

medium

Complete the code to create a view named 'recent_orders' that selects 'order_id' and 'order_date' from 'orders' where 'order_date' is after January 1, 2023.

PostgreSQL

CREATE VIEW recent_orders AS SELECT order_id, order_date FROM orders WHERE order_date > [1];

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

ADATE '2023-01-01'

B'2023-01-01'

C2023-01-01

D"2023-01-01"

Attempts:

3 left

💡 Hint

Common Mistakes

Using unquoted dates which cause syntax errors.

Using double quotes instead of single quotes for date strings.

In PostgreSQL, the DATE keyword before a string literal explicitly casts it as a date type, which is best practice here.

3fill in blank

hard

Fix the error in the code to create a view named 'customer_emails' selecting 'email' from 'customers'.

PostgreSQL

CREATE VIEW customer_emails [1] SELECT email FROM customers;

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

AON

BIS

CAS

DWITH

Attempts:

3 left

💡 Hint

Common Mistakes

Using IS or WITH instead of AS.

Omitting the keyword entirely.

The correct keyword to define the query for a view is AS.

4fill in blank

hard

Fill both blanks to create a view named 'top_products' selecting 'product_id' and 'sales' from 'products' where 'sales' is greater than 1000.

PostgreSQL

CREATE VIEW [1] AS SELECT product_id, sales FROM products WHERE sales [2] 1000;

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

Atop_products

B>

C<

Dbest_products

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong view name.

Using less than instead of greater than.

The view name should be 'top_products' and the condition is sales greater than 1000, so use >.

5fill in blank

hard

Fill all three blanks to create a view named 'employee_summary' selecting 'employee_id', 'department', and the count of projects as 'project_count' from 'employees' joined with 'projects' on 'employee_id', grouping by 'employee_id' and 'department'.

PostgreSQL

CREATE VIEW [1] AS SELECT employee_id, department, COUNT(project_id) AS [2] FROM employees JOIN projects ON employees.employee_id = projects.[3] GROUP BY employee_id, department;

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

Aemployee_summary

Bproject_count

Cemployee_id

Ddept_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong alias names.

Joining on the wrong column.

The view name is 'employee_summary', the alias for count is 'project_count', and the join is on 'employee_id'.