Books with NULL Ratings - Practice Coding Problems

Books with NULL Ratings - Problem

Database Easy

You're working as a data analyst for a digital library platform, and you need to identify which books in the catalog are missing ratings. This information is crucial for the recommendation system to decide which books need more user engagement to generate ratings.

Given a books table with columns for book_id, title, author, published_year, and rating, your task is to find all books that haven't been rated yet (where rating is NULL).

Your Goal: Write a SQL query to retrieve all books with NULL ratings, ordered by book_id in ascending order. Return all columns except the rating column since we already know it's NULL.

Note: This is a fundamental SQL problem that teaches you how to handle NULL values using the IS NULL condition.

Input & Output

example_1.sql — Basic Query

$ Input: books table with 6 records, 3 having NULL ratings

› Output: 3 books with NULL ratings ordered by book_id

💡 Note: The query filters out books with actual ratings and returns only those where rating IS NULL, sorted by book_id in ascending order.

example_2.sql — Edge Case: All Books Rated

$ Input: books table where all books have ratings (no NULLs)

› Output: Empty result set

💡 Note: When no books have NULL ratings, the query returns an empty result set, which is the correct behavior.

example_3.sql — Edge Case: All Books Unrated

$ Input: books table where all books have NULL ratings

› Output: All books returned in book_id ascending order

💡 Note: When all books have NULL ratings, all records are included in the result, demonstrating the filter works correctly in edge cases.

Visualization

Tap to expand

Understanding the Visualization

Library Scan

We systematically go through each book in our digital catalog

Rating Check

For each book, we check if there's a rating sticker (not NULL) or an empty slot (IS NULL)

Collection

We collect all books that have empty rating slots into our 'needs attention' pile

Organization

Finally, we organize our collection by book ID number for easy reference

Key Takeaway

🎯 Key Insight: SQL's IS NULL condition is the proper way to identify missing values. Unlike regular comparisons, NULL values require special handling because NULL represents 'unknown' rather than a specific value.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through all records in the table

✓ Linear Growth

Space Complexity

O(k)

Space for k records that match the NULL condition

✓ Linear Space

Constraints

1 ≤ book_id ≤ 10⁶
1 ≤ title.length, author.length ≤ 100
1800 ≤ published_year ≤ 2024
rating can be NULL or a decimal between 0.0 and 5.0

Asked in

a Amazon 85 G Google 72 ⊞ Microsoft 68 f Meta 45

This problem is solved with a simple SQL WHERE clause using IS NULL condition. The key insight is understanding that NULL values require special handling in SQL - you cannot use = NULL, but must use IS NULL to check for missing values. The solution scans the table once with O(n) time complexity and filters records efficiently.

Common Approaches

Approach	Time	Space	Notes
✓ Basic NULL Check (Standard SQL)	O(n)	O(k)	Simple WHERE clause with IS NULL condition

Basic NULL Check (Standard SQL) — Algorithm Steps

SELECT the required columns (all except rating)
FROM the books table
WHERE rating IS NULL
ORDER BY book_id in ascending order

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Table

Database engine scans through each row in the books table

Check Condition

For each row, check if rating IS NULL

Filter Results

Include rows where rating is NULL in the result set

Sort Output

Sort the filtered results by book_id in ascending order

Code -

solution.c — C

// SQL query for finding books with NULL ratings
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

// Structure to represent a book
typedef struct {
    int book_id;
    char title[256];
    char author[256];
    int published_year;
} Book;

const char* get_books_null_ratings_query() {
    return "SELECT book_id, title, author, published_year "
           "FROM books WHERE rating IS NULL ORDER BY book_id ASC;";
}

// Example function that would execute the query
// (actual implementation depends on the SQL library used)
int execute_query(Book** results, int* count) {
    const char* query = get_books_null_ratings_query();
    
    // Database execution logic would go here
    // For demonstration, we'll just print the query
    printf("Executing query: %s\n", query);
    
    *count = 0;
    *results = NULL;
    return 0; // Success
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single scan through all records in the table

✓ Linear Growth

Space Complexity

O(k)

Space for k records that match the NULL condition

✓ Linear Space

Constraints

1 ≤ book_id ≤ 10⁶
1 ≤ title.length, author.length ≤ 100
1800 ≤ published_year ≤ 2024
rating can be NULL or a decimal between 0.0 and 5.0

89.5K Views

High Frequency

~8 min Avg. Time

2.3K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Basic NULL Check (Standard SQL) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler