Problem Statement

Table: Comments
Each row in this table represents a comment made by a user, identified by comment_id, and includes the text of the comment.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| comment_id  | int     |
| text        | varchar |
+-------------+---------+
comment_id is the primary key for this table.
This table can contain duplicate rows.

Develop a solution to identify comments that are considered too long. A comment is deemed too long if it consists of more than 100 characters, including spaces and punctuation. Return the IDs of such comments, sorted in any order.

Example

Input:

Comments table:
+------------+-------------------------------------------------------------+
| comment_id | text                                                        |
+------------+-------------------------------------------------------------+
| 1          | I absolutely love this!                                     |
| 2          | This is way too long of a comment, and it should probably   |
|            | be shortened or split into multiple comments                |
+------------+-------------------------------------------------------------+

Output:

+------------+
| comment_id |
+------------+
| 2          |
+------------+

Only comment 2 is too long, exceeding 100 characters.

Try It Yourself

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Solution

To find comments that exceed 100 characters, we will filter the records in the Comments table based on the length of the text column.

1. Select Comment IDs: Start with a SELECT statement to fetch all comment_id from the Comments table.
2. Apply Length Condition: Use a WHERE clause to filter comments where the length of the text exceeds 100 characters.

SQL Query

The following SQL query identifies long comments:

SELECT comment_id
FROM Comments
WHERE LENGTH(text) > 100

Step by Step Approach

Step 1: Select Comment IDs

Select comment_id from the Comments table to start building the list of comments.

SELECT comment_id
FROM Comments

Output After Step 1:

+------------+
| comment_id |
+------------+
| 1          |
| 2          |
+------------+

Step 2: Apply Length Condition

Filter the selection to include only those comments where the length of the text is greater than 100 characters.

SELECT comment_id
FROM Comments
WHERE LENGTH(text) > 100

Final Output:

+------------+
| comment_id |
+------------+
| 2          |
+------------+