First Normal Form (1NF) in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the table named 'students'.

SQL

SELECT [1] FROM students;

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

AEVERY

BALL

C*

DALL_COLUMNS

Attempts:

3 left

💡 Hint

Common Mistakes

Using keywords like ALL or EVERY which are not valid for selecting all columns.

The asterisk (*) selects all columns from the table.

2fill in blank

medium

Complete the code to select the 'name' and 'phone_numbers' columns from the 'contacts' table.

SQL

SELECT name, [1] FROM contacts;

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

Aphone_numbers

Bphone_number

Cphones

Dphone

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing singular form 'phone_number' when the column is named 'phone_numbers'.

The column name is 'phone_numbers' which may contain multiple phone numbers, but in 1NF, each value should be atomic.

3fill in blank

hard

Fix the error in the query that tries to select rows where the 'phone_numbers' column contains multiple values, which violates 1NF.

SQL

SELECT * FROM contacts WHERE phone_numbers [1] '%,%';

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

AIN

BLIKE

C=

DBETWEEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which checks for exact match, not pattern matching.

The LIKE operator checks if the 'phone_numbers' string contains a comma, indicating multiple values in one field, which violates 1NF.

4fill in blank

hard

Fill both blanks to create a query that selects 'id' and 'phone' from 'contacts' where 'phone' is atomic (does not contain commas).

SQL

SELECT id, [1] FROM contacts WHERE [2] NOT LIKE '%,%';

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

Aphone

Bphone_numbers

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'phone_numbers' which may contain multiple values.

We select the atomic 'phone' column and filter rows where 'phone' does not contain commas, ensuring 1NF compliance.

5fill in blank

hard

Fill all three blanks to create a query that creates a new table 'contacts_1nf' with atomic phone numbers by splitting 'phone_numbers' into single values.

SQL

CREATE TABLE contacts_1nf AS SELECT id, [1] AS phone FROM contacts, [2](phone_numbers) AS [3] WHERE phone IS NOT NULL;

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

Aphone

Bunnest

Dsplit

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'split' which is not a standard SQL function for this purpose.

The 'unnest' function is used to split array-like 'phone_numbers' into single 'phone' values, creating a 1NF-compliant table.