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

Commitf1c9ed3

Browse files
authored
Added tasks 602, 610, 619, 1045
1 parentd32a548 commitf1c9ed3

File tree

13 files changed

+606
-117
lines changed

13 files changed

+606
-117
lines changed

‎README.md

Lines changed: 116 additions & 117 deletions
Large diffs are not rendered by default.
Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
602\. Friend Requests II: Who Has the Most Friends
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`RequestAccepted`
8+
9+
+----------------+---------+
10+
| Column Name | Type |
11+
+----------------+---------+
12+
| requester_id | int |
13+
| accepter_id | int |
14+
| accept_date | date |
15+
+----------------+---------+
16+
17+
(requester_id, accepter_id) is the primary key for this table. This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.
18+
19+
Write an SQL query to find the people who have the most friends and the most friends number.
20+
21+
The test cases are generated so that only one person has the most friends.
22+
23+
The query result format is in the following example.
24+
25+
**Example 1:**
26+
27+
**Input:** RequestAccepted table:
28+
29+
+--------------+-------------+-------------+
30+
| requester_id | accepter_id | accept_date |
31+
+--------------+-------------+-------------+
32+
| 1 | 2 | 2016/06/03 |
33+
| 1 | 3 | 2016/06/08 |
34+
| 2 | 3 | 2016/06/08 |
35+
| 3 | 4 | 2016/06/09 |
36+
+--------------+-------------+-------------+
37+
38+
**Output:**
39+
40+
+----+-----+
41+
| id | num |
42+
+----+-----+
43+
| 3 | 3 |
44+
+----+-----+
45+
46+
**Explanation:** The person with id 3 is a friend of people 1, 2, and 4, so he has three friends in total, which is the most number than any others.
47+
48+
**Follow up:** In the real world, multiple people could have the same most number of friends. Could you find all these people in this case?
Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_06_05_Time_510_ms_(60.15%)_Space_0B_(100.00%)
3+
SELECT reqAS id,COUNT(acc)AS num
4+
FROM
5+
((SELECT requester_idAS req, accepter_idAS acc
6+
FROM requestaccepted)
7+
UNION
8+
(SELECT accepter_idAS req, requester_idAS acc
9+
FROM requestaccepted)) t
10+
GROUP BY req
11+
ORDER BY numDESC
12+
LIMIT1
Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
610\. Triangle Judgement
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Triangle`
8+
9+
+-------------+------+
10+
| Column Name | Type |
11+
+-------------+------+
12+
| x | int |
13+
| y | int |
14+
| z | int |
15+
+-------------+------+
16+
17+
(x, y, z) is the primary key column for this table.
18+
19+
Each row of this table contains the lengths of three line segments.
20+
21+
Write an SQL query to report for every three line segments whether they can form a triangle.
22+
23+
Return the result table in**any order**.
24+
25+
The query result format is in the following example.
26+
27+
**Example 1:**
28+
29+
**Input:** Triangle table:
30+
31+
+----+----+----+
32+
| x | y | z |
33+
+----+----+----+
34+
| 13 | 15 | 30 |
35+
| 10 | 20 | 15 |
36+
+----+----+----+
37+
38+
**Output:**
39+
40+
+----+----+----+----------+
41+
| x | y | z | triangle |
42+
+----+----+----+----------+
43+
| 13 | 15 | 30 | No |
44+
| 10 | 20 | 15 | Yes |
45+
+----+----+----+----------+
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_06_05_Time_499_ms_(62.77%)_Space_0B_(100.00%)
3+
SELECT*,
4+
CASE WHEN x+y>zAND y+z>xAND z+x>y THEN'Yes' ELSE'No' ENDAS triangle
5+
FROM Triangle
Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
619\. Biggest Single Number
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`MyNumbers`
8+
9+
+-------------+------+
10+
| Column Name | Type |
11+
+-------------+------+
12+
| num | int |
13+
+-------------+------+
14+
15+
There is no primary key for this table. It may contain duplicates.
16+
17+
Each row of this table contains an integer.
18+
19+
A**single number** is a number that appeared only once in the`MyNumbers` table.
20+
21+
Write an SQL query to report the largest**single number**. If there is no**single number**, report`null`.
22+
23+
The query result format is in the following example.
24+
25+
**Example 1:**
26+
27+
**Input:** MyNumbers table:
28+
29+
+-----+
30+
| num |
31+
+-----+
32+
| 8 |
33+
| 8 |
34+
| 3 |
35+
| 3 |
36+
| 1 |
37+
| 4 |
38+
| 5 |
39+
| 6 |
40+
+-----+
41+
42+
**Output:**
43+
44+
+-----+
45+
| num |
46+
+-----+
47+
| 6 |
48+
+-----+
49+
50+
**Explanation:** The single numbers are 1, 4, 5, and 6. Since 6 is the largest single number, we return it.
51+
52+
**Example 2:**
53+
54+
**Input:** MyNumbers table:
55+
56+
+-----+
57+
| num |
58+
+-----+
59+
| 8 |
60+
| 8 |
61+
| 7 |
62+
| 7 |
63+
| 3 |
64+
| 3 |
65+
| 3 |
66+
+-----+
67+
68+
**Output:**
69+
70+
+------+
71+
| num |
72+
+------+
73+
| null |
74+
+------+
75+
76+
**Explanation:** There are no single numbers in the input table so we return null.
Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_06_05_Time_779_ms_(66.08%)_Space_0B_(100.00%)
3+
SELECTMAX(num)AS num
4+
FROM (SELECT num,COUNT(num)
5+
FROM MyNumbers
6+
GROUP BY num
7+
HAVINGCOUNT(num)=1) t
Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
1045\. Customers Who Bought All Products
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Customer`
8+
9+
+-------------+---------+
10+
| Column Name | Type |
11+
+-------------+---------+
12+
| customer_id | int |
13+
| product_key | int |
14+
+-------------+---------+
15+
16+
There is no primary key for this table. It may contain duplicates.`customer_id` is not NULL`.` product\_key is a foreign key to`Product` table.
17+
18+
Table:`Product`
19+
20+
+-------------+---------+
21+
| Column Name | Type |
22+
+-------------+---------+
23+
| product_key | int |
24+
+-------------+---------+
25+
26+
product_key is the primary key column for this table.
27+
28+
Write an SQL query to report the customer ids from the`Customer` table that bought all the products in the`Product` table.
29+
30+
Return the result table in**any order**.
31+
32+
The query result format is in the following example.
33+
34+
**Example 1:**
35+
36+
**Input:** Customer table:
37+
38+
+-------------+-------------+
39+
| customer_id | product_key |
40+
+-------------+-------------+
41+
| 1 | 5 |
42+
| 2 | 6 |
43+
| 3 | 5 |
44+
| 3 | 6 |
45+
| 1 | 6 |
46+
+-------------+-------------+
47+
48+
Product table:
49+
50+
+-------------+
51+
| product_key |
52+
+-------------+
53+
| 5 |
54+
| 6 |
55+
+-------------+
56+
57+
**Output:**
58+
59+
+-------------+
60+
| customer_id |
61+
+-------------+
62+
| 1 |
63+
| 3 |
64+
+-------------+
65+
66+
**Explanation:** The customers who bought all the products (5 and 6) are customers with IDs 1 and 3.
Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_06_05_Time_1082_ms_(43.17%)_Space_0B_(100.00%)
3+
select customer_id
4+
from customer
5+
group by customer_id
6+
havingcount(distinct product_key)=(selectcount(*)from product);
Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
packageg0601_0700.s0602_friend_requests_ii_who_has_the_most_friends;
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 RequestAccepted(requester_id INTEGER, accepter_id INTEGER, accept_date DATETIME); "
24+
+"INSERT INTO RequestAccepted(requester_id, accepter_id, accept_date)"
25+
+" VALUES (1, 2, '2016-06-03'); "
26+
+"INSERT INTO RequestAccepted(requester_id, accepter_id, accept_date)"
27+
+" VALUES (1, 3, '2016-06-08'); "
28+
+"INSERT INTO RequestAccepted(requester_id, accepter_id, accept_date)"
29+
+" VALUES (2, 3, '2016-06-08'); "
30+
+"INSERT INTO RequestAccepted(requester_id, accepter_id, accept_date)"
31+
+" VALUES (3, 4, '2016-06-09'); ")
32+
classMysqlTest {
33+
@Test
34+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
35+
throwsSQLException,FileNotFoundException {
36+
try (finalConnectionconnection =dataSource.getConnection()) {
37+
try (finalStatementstatement =connection.createStatement();
38+
finalResultSetresultSet =
39+
statement.executeQuery(
40+
newBufferedReader(
41+
newFileReader(
42+
"src/main/java/g0601_0700/"
43+
+"s0602_friend_requests"
44+
+"_ii_who_has_the_most_friends"
45+
+"/script.sql"))
46+
.lines()
47+
.collect(Collectors.joining("\n"))
48+
.replaceAll("#.*?\\r?\\n",""))) {
49+
assertThat(resultSet.next(),equalTo(true));
50+
assertThat(resultSet.getInt(1),equalTo(3));
51+
assertThat(resultSet.getInt(2),equalTo(3));
52+
assertThat(resultSet.next(),equalTo(false));
53+
}
54+
}
55+
}
56+
}
Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
packageg0601_0700.s0610_triangle_judgement;
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 Triangle(x INTEGER, y INTEGER, z INTEGER); "
24+
+"INSERT INTO Triangle(x, y, z)"
25+
+" VALUES (13, 15, 30); "
26+
+"INSERT INTO Triangle(x, y, z)"
27+
+" VALUES (10, 20, 15); ")
28+
classMysqlTest {
29+
@Test
30+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
31+
throwsSQLException,FileNotFoundException {
32+
try (finalConnectionconnection =dataSource.getConnection()) {
33+
try (finalStatementstatement =connection.createStatement();
34+
finalResultSetresultSet =
35+
statement.executeQuery(
36+
newBufferedReader(
37+
newFileReader(
38+
"src/main/java/g0601_0700/"
39+
+"s0610_triangle_judgement"
40+
+"/script.sql"))
41+
.lines()
42+
.collect(Collectors.joining("\n"))
43+
.replaceAll("#.*?\\r?\\n",""))) {
44+
assertThat(resultSet.next(),equalTo(true));
45+
assertThat(resultSet.getInt(1),equalTo(13));
46+
assertThat(resultSet.getInt(2),equalTo(15));
47+
assertThat(resultSet.getInt(3),equalTo(30));
48+
assertThat(resultSet.getNString(4),equalTo("No"));
49+
assertThat(resultSet.next(),equalTo(true));
50+
assertThat(resultSet.getInt(1),equalTo(10));
51+
assertThat(resultSet.getInt(2),equalTo(20));
52+
assertThat(resultSet.getInt(3),equalTo(15));
53+
assertThat(resultSet.getNString(4),equalTo("Yes"));
54+
assertThat(resultSet.next(),equalTo(false));
55+
}
56+
}
57+
}
58+
}

0 commit comments

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