Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb900a04

Browse files
authored
Added task 3465
1 parent4b8aaf7 commitb900a04

File tree

3 files changed

+142
-0
lines changed
  • src
    • main/java/g3401_3500/s3465_find_products_with_valid_serial_numbers
    • test/java/g3401_3500/s3465_find_products_with_valid_serial_numbers

3 files changed

+142
-0
lines changed
Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
3465\. Find Products with Valid Serial Numbers
2+
3+
Easy
4+
5+
Table:`products`
6+
7+
+--------------+------------+
8+
| Column Name | Type |
9+
+--------------+------------+
10+
| product_id | int |
11+
| product_name | varchar |
12+
| description | varchar |
13+
+--------------+------------+
14+
(product_id) is the unique key for this table.
15+
Each row in the table represents a product with its unique ID, name, and description.
16+
17+
Write a solution to find all products whose description**contains a valid serial number** pattern. A valid serial number follows these rules:
18+
19+
* It starts with the letters**SN** (case-sensitive).
20+
* Followed by exactly`4` digits.
21+
* It must have a hyphen (-)**followed by exactly**`4` digits.
22+
* The serial number must be within the description (it may not necessarily start at the beginning).
23+
24+
Return_the result table ordered by_`product_id`_in**ascending** order_.
25+
26+
The result format is in the following example.
27+
28+
**Example:**
29+
30+
**Input:**
31+
32+
products table:
33+
34+
+------------+--------------+------------------------------------------------------+
35+
| product_id | product_name | description |
36+
+------------+--------------+------------------------------------------------------+
37+
| 1 | Widget A | This is a sample product with SN1234-5678 |
38+
| 2 | Widget B | A product with serial SN9876-1234 in the description |
39+
| 3 | Widget C | Product SN1234-56789 is available now |
40+
| 4 | Widget D | No serial number here |
41+
| 5 | Widget E | Check out SN4321-8765 in this description |
42+
+------------+--------------+------------------------------------------------------+
43+
44+
**Output:**
45+
46+
+------------+--------------+------------------------------------------------------+
47+
| product_id | product_name | description |
48+
+------------+--------------+------------------------------------------------------+
49+
| 1 | Widget A | This is a sample product with SN1234-5678 |
50+
| 2 | Widget B | A product with serial SN9876-1234 in the description |
51+
| 5 | Widget E | Check out SN4321-8765 in this description |
52+
+------------+--------------+------------------------------------------------------+
53+
54+
**Explanation:**
55+
56+
***Product 1:** Valid serial number SN1234-5678
57+
***Product 2:** Valid serial number SN9876-1234
58+
***Product 3:** Invalid serial number SN1234-56789 (contains 5 digits after the hyphen)
59+
***Product 4:** No serial number in the description
60+
***Product 5:** Valid serial number SN4321-8765
61+
62+
The result table is ordered by product\_id in ascending order.
Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2025_02_25_Time_716_ms_(100.00%)_Space_0.0_MB_(100.00%)
3+
SELECT*FROM productsWHERE description REGEXP'SN[0-9]{4}-[0-9]{4}$'
4+
OR description REGEXP'SN[0-9]{4}-[0-9]{4}[^0-9]+'ORDER BY product_id
Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
packageg3401_3500.s3465_find_products_with_valid_serial_numbers;
2+
3+
importstaticorg.hamcrest.CoreMatchers.equalTo;
4+
importstaticorg.hamcrest.MatcherAssert.assertThat;
5+
6+
importjava.io.BufferedReader;
7+
importjava.io.FileNotFoundException;
8+
importjava.io.FileReader;
9+
importjava.sql.Connection;
10+
importjava.sql.ResultSet;
11+
importjava.sql.SQLException;
12+
importjava.sql.Statement;
13+
importjava.util.stream.Collectors;
14+
importjavax.sql.DataSource;
15+
importorg.junit.jupiter.api.Test;
16+
importorg.zapodot.junit.db.annotations.EmbeddedDatabase;
17+
importorg.zapodot.junit.db.annotations.EmbeddedDatabaseTest;
18+
importorg.zapodot.junit.db.common.CompatibilityMode;
19+
20+
@EmbeddedDatabaseTest(
21+
compatibilityMode =CompatibilityMode.MySQL,
22+
initialSqls =
23+
" CREATE TABLE products ("
24+
+" product_id INT,"
25+
+" product_name VARCHAR(50),"
26+
+" description VARCHAR(100)"
27+
+");"
28+
+"insert into products (product_id, product_name, description) values "
29+
+"(1, 'Widget A', 'This is a sample product with SN1234-5678');"
30+
+"insert into products (product_id, product_name, description) values "
31+
+"(2, 'Widget B', 'A product with serial SN9876-1234 in the description');"
32+
+"insert into products (product_id, product_name, description) values "
33+
+"(3, 'Widget C', 'Product SN1234-56789 is available now');"
34+
+"insert into products (product_id, product_name, description) values "
35+
+"(4, 'Widget D', 'No serial number here');"
36+
+"insert into products (product_id, product_name, description) values "
37+
+"(5, 'Widget E', 'Check out SN4321-8765 in this description');")
38+
classMysqlTest {
39+
@Test
40+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
41+
throwsSQLException,FileNotFoundException {
42+
try (finalConnectionconnection =dataSource.getConnection()) {
43+
try (finalStatementstatement =connection.createStatement();
44+
finalResultSetresultSet =
45+
statement.executeQuery(
46+
newBufferedReader(
47+
newFileReader(
48+
"src/main/java/g3401_3500/"
49+
+"s3465_find_products_with_valid_serial_numbers/"
50+
+"script.sql"))
51+
.lines()
52+
.collect(Collectors.joining("\n"))
53+
.replaceAll("#.*?\\r?\\n",""))) {
54+
assertThat(resultSet.next(),equalTo(true));
55+
assertThat(resultSet.getNString(1),equalTo("1"));
56+
assertThat(resultSet.getNString(2),equalTo("Widget A"));
57+
assertThat(
58+
resultSet.getNString(3),
59+
equalTo("This is a sample product with SN1234-5678"));
60+
assertThat(resultSet.next(),equalTo(true));
61+
assertThat(resultSet.getNString(1),equalTo("2"));
62+
assertThat(resultSet.getNString(2),equalTo("Widget B"));
63+
assertThat(
64+
resultSet.getNString(3),
65+
equalTo("A product with serial SN9876-1234 in the description"));
66+
assertThat(resultSet.next(),equalTo(true));
67+
assertThat(resultSet.getNString(1),equalTo("5"));
68+
assertThat(resultSet.getNString(2),equalTo("Widget E"));
69+
assertThat(
70+
resultSet.getNString(3),
71+
equalTo("Check out SN4321-8765 in this description"));
72+
assertThat(resultSet.next(),equalTo(false));
73+
}
74+
}
75+
}
76+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp