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

Commita280a50

Browse files
authored
Added tasks 1327, 1341, 1378
1 parent7bf3e70 commita280a50

File tree

9 files changed

+541
-0
lines changed

9 files changed

+541
-0
lines changed
Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,85 @@
1+
1327\. List the Products Ordered in a Period
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Products`
8+
9+
+------------------+---------+
10+
| Column Name | Type |
11+
+------------------+---------+
12+
| product_id | int |
13+
| product_name | varchar |
14+
| product_category | varchar |
15+
+------------------+---------+
16+
17+
product_id is the primary key for this table. This table contains data about the company's products.
18+
19+
Table:`Orders`
20+
21+
+---------------+---------+
22+
| Column Name | Type |
23+
+---------------+---------+
24+
| product_id | int |
25+
| order_date | date |
26+
| unit | int |
27+
+---------------+---------+
28+
29+
There is no primary key for this table. It may have duplicate rows. product_id is a foreign key to the Products table. unit is the number of products ordered in order_date.
30+
31+
Write an SQL query to get the names of products that have at least`100` units ordered in**February 2020** and their amount.
32+
33+
Return result table in**any order**.
34+
35+
The query result format is in the following example.
36+
37+
**Example 1:**
38+
39+
**Input:** Products table:
40+
41+
+-------------+-----------------------+------------------+
42+
| product_id | product_name | product_category |
43+
+-------------+-----------------------+------------------+
44+
| 1 | Leetcode Solutions | Book |
45+
| 2 | Jewels of Stringology | Book |
46+
| 3 | HP | Laptop |
47+
| 4 | Lenovo | Laptop |
48+
| 5 | Leetcode Kit | T-shirt |
49+
+-------------+-----------------------+------------------+
50+
51+
Orders table:
52+
53+
+--------------+--------------+----------+
54+
| product_id | order_date | unit |
55+
+--------------+--------------+----------+
56+
| 1 | 2020-02-05 | 60 |
57+
| 1 | 2020-02-10 | 70 |
58+
| 2 | 2020-01-18 | 30 |
59+
| 2 | 2020-02-11 | 80 |
60+
| 3 | 2020-02-17 | 2 |
61+
| 3 | 2020-02-24 | 3 |
62+
| 4 | 2020-03-01 | 20 |
63+
| 4 | 2020-03-04 | 30 |
64+
| 4 | 2020-03-04 | 60 |
65+
| 5 | 2020-02-25 | 50 |
66+
| 5 | 2020-02-27 | 50 |
67+
| 5 | 2020-03-01 | 50 |
68+
+--------------+--------------+----------+
69+
70+
**Output:**
71+
72+
+--------------------+---------+
73+
| product_name | unit |
74+
+--------------------+---------+
75+
| Leetcode Solutions | 130 |
76+
| Leetcode Kit | 100 |
77+
+--------------------+---------+
78+
79+
**Explanation:**
80+
81+
Products with product_id = 1 is ordered in February a total of (60 + 70) = 130.
82+
83+
Products with product_id = 2 is ordered in February a total of 80. Products with product_id = 3 is ordered in February a total of (2 + 3) = 5.
84+
85+
Products with product_id = 4 was not ordered in February 2020. Products with product_id = 5 is ordered in February a total of (50 + 50) = 100.
Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
# Write your MySQL query statement below
2+
# #Easy #Database #2023_08_15_Time_1355_ms_(82.29%)_Space_0B_(100.00%)
3+
SELECT*FROM (
4+
SELECT
5+
a.product_name,
6+
SUM(b.unit)as unit
7+
FROM Products a
8+
LEFT JOIN Orders b
9+
ONa.product_id=b.product_id
10+
WHEREb.order_date BETWEEN'2020-02-01'AND'2020-02-29'
11+
GROUP BYa.product_name
12+
)AS d
13+
GROUP BYd.product_name
14+
HAVINGd.unit>=100
Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,101 @@
1+
1341\. Movie Rating
2+
3+
Medium
4+
5+
SQL Schema
6+
7+
Table:`Movies`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| movie_id | int |
13+
| title | varchar |
14+
+---------------+---------+
15+
16+
movie_id is the primary key for this table. title is the name of the movie.
17+
18+
Table:`Users`
19+
20+
+---------------+---------+
21+
| Column Name | Type |
22+
+---------------+---------+
23+
| user_id | int |
24+
| name | varchar |
25+
+---------------+---------+
26+
27+
user_id is the primary key for this table.
28+
29+
Table:`MovieRating`
30+
31+
+---------------+---------+
32+
| Column Name | Type |
33+
+---------------+---------+
34+
| movie_id | int |
35+
| user_id | int |
36+
| rating | int |
37+
| created_at | date |
38+
+---------------+---------+
39+
40+
(movie_id, user_id) is the primary key for this table. This table contains the rating of a movie by a user in their review. created_at is the user's review date.
41+
42+
Write an SQL query to:
43+
44+
* Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
45+
* Find the movie name with the**highest average** rating in`February 2020`. In case of a tie, return the lexicographically smaller movie name.
46+
47+
The query result format is in the following example.
48+
49+
**Example 1:**
50+
51+
**Input:** Movies table:
52+
53+
+-------------+--------------+
54+
| movie_id | title |
55+
+-------------+--------------+
56+
| 1 | Avengers |
57+
| 2 | Frozen 2 |
58+
| 3 | Joker |
59+
+-------------+--------------+
60+
61+
Users table:
62+
63+
+-------------+--------------+
64+
| user_id | name |
65+
+-------------+--------------+
66+
| 1 | Daniel |
67+
| 2 | Monica |
68+
| 3 | Maria |
69+
| 4 | James |
70+
+-------------+--------------+
71+
72+
MovieRating table:
73+
74+
+-------------+--------------+--------------+-------------+
75+
| movie_id | user_id | rating | created_at |
76+
+-------------+--------------+--------------+-------------+
77+
| 1 | 1 | 3 | 2020-01-12 |
78+
| 1 | 2 | 4 | 2020-02-11 |
79+
| 1 | 3 | 2 | 2020-02-12 |
80+
| 1 | 4 | 1 | 2020-01-01 |
81+
| 2 | 1 | 5 | 2020-02-17 |
82+
| 2 | 2 | 2 | 2020-02-01 |
83+
| 2 | 3 | 2 | 2020-03-01 |
84+
| 3 | 1 | 3 | 2020-02-22 |
85+
| 3 | 2 | 4 | 2020-02-25 |
86+
+-------------+--------------+--------------+-------------+
87+
88+
**Output:**
89+
90+
+--------------+
91+
| results |
92+
+--------------+
93+
| Daniel |
94+
| Frozen 2 |
95+
+--------------+
96+
97+
**Explanation:**
98+
99+
Daniel and Monica have rated 3 movies ("Avengers", "Frozen 2" and "Joker") but Daniel is smaller lexicographically.
100+
101+
Frozen 2 and Joker have a rating average of 3.5 in February but Frozen 2 is smaller lexicographically.
Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
# Write your MySQL query statement below
2+
# #Medium #Database #2023_08_15_Time_2843_ms_(48.31%)_Space_0B_(100.00%)
3+
(SELECT name results
4+
FROM Usersas U, MovieRatingas MR
5+
WHEREU.user_id=MR.user_id
6+
GROUP BYU.user_id
7+
ORDER BYCOUNT(MR.user_id)DESC, nameASCLIMIT1)
8+
UNION ALL
9+
(SELECT title results
10+
FROM Moviesas M, MovieRatingas MR
11+
WHEREM.movie_id=MR.movie_idAND created_at BETWEEN'2020-02-01'AND'2020-02-29'
12+
GROUP BYM.movie_id
13+
ORDER BYAVG(rating)DESC, titleASCLIMIT1)
Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
1378\. Replace Employee ID With The Unique Identifier
2+
3+
Easy
4+
5+
SQL Schema
6+
7+
Table:`Employees`
8+
9+
+---------------+---------+
10+
| Column Name | Type |
11+
+---------------+---------+
12+
| id | int |
13+
| name | varchar |
14+
+---------------+---------+
15+
id is the primary key for this table.
16+
Each row of this table contains the id and the name of an employee in a company.
17+
18+
Table:`EmployeeUNI`
19+
20+
+---------------+---------+
21+
| Column Name | Type |
22+
+---------------+---------+
23+
| id | int |
24+
| unique_id | int |
25+
+---------------+---------+
26+
(id, unique_id) is the primary key for this table.
27+
Each row of this table contains the id and the corresponding unique id of an employee in the company.
28+
29+
Write an SQL query to show the**unique ID** of each user, If a user does not have a unique ID replace just show`null`.
30+
31+
Return the result table in**any** order.
32+
33+
The query result format is in the following example.
34+
35+
**Example 1:**
36+
37+
**Input:**,
38+
39+
Employees table:
40+
+----+----------+
41+
| id | name |
42+
+----+----------+
43+
| 1 | Alice |
44+
| 7 | Bob |
45+
| 11 | Meir |
46+
| 90 | Winston |
47+
| 3 | Jonathan |
48+
+----+----------+
49+
50+
EmployeeUNI table:
51+
+----+-----------+
52+
| id | unique_id |
53+
+----+-----------+
54+
| 3 | 1 |
55+
| 11 | 2 |
56+
| 90 | 3 |
57+
+----+-----------+
58+
59+
**Output:**
60+
61+
+-----------+----------+
62+
| unique_id | name |
63+
+-----------+----------+
64+
| null | Alice |
65+
| null | Bob |
66+
| 2 | Meir |
67+
| 3 | Winston |
68+
| 1 | Jonathan |
69+
+-----------+----------+
70+
71+
**Explanation:**
72+
73+
Alice and Bob do not have a unique ID, We will show null instead.
74+
75+
The unique ID of Meir is 2.
76+
77+
The unique ID of Winston is 3.
78+
79+
The unique ID of Jonathan is 1.
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+
# #Easy #Database #2023_08_15_Time_2747_ms_(40.02%)_Space_0B_(100.00%)
3+
selectu.unique_id,e.name
4+
from Employees e
5+
left join EmployeeUNI u
6+
one.id=u.id;
Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,85 @@
1+
packageg1301_1400.s1327_list_the_products_ordered_in_a_period;
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(product_id INTEGER, product_name VARCHAR, product_category VARCHAR); "
24+
+"INSERT INTO Products(product_id, product_name, product_category)"
25+
+" VALUES (1, 'Leetcode Solutions', 'Book'); "
26+
+"INSERT INTO Products(product_id, product_name, product_category)"
27+
+" VALUES (2, 'Jewels of Stringology', 'Book'); "
28+
+"INSERT INTO Products(product_id, product_name, product_category)"
29+
+" VALUES (3, 'HP', 'Laptop'); "
30+
+"INSERT INTO Products(product_id, product_name, product_category)"
31+
+" VALUES (4, 'Lenovo', 'Laptop'); "
32+
+"INSERT INTO Products(product_id, product_name, product_category)"
33+
+" VALUES (5, 'Leetcode Kit', 'T-shirt'); "
34+
+"CREATE TABLE Orders(product_id INTEGER, order_date DATE, unit INTEGER); "
35+
+"INSERT INTO Orders(product_id, order_date, unit)"
36+
+" VALUES (1, '2020-02-05', 60); "
37+
+"INSERT INTO Orders(product_id, order_date, unit)"
38+
+" VALUES (1, '2020-02-10', 70); "
39+
+"INSERT INTO Orders(product_id, order_date, unit)"
40+
+" VALUES (2, '2020-01-18', 30); "
41+
+"INSERT INTO Orders(product_id, order_date, unit)"
42+
+" VALUES (2, '2020-02-11', 80); "
43+
+"INSERT INTO Orders(product_id, order_date, unit)"
44+
+" VALUES (3 , '2020-02-17', 2); "
45+
+"INSERT INTO Orders(product_id, order_date, unit)"
46+
+" VALUES (3, '2020-02-24', 3); "
47+
+"INSERT INTO Orders(product_id, order_date, unit)"
48+
+" VALUES (4, '2020-03-01', 20); "
49+
+"INSERT INTO Orders(product_id, order_date, unit)"
50+
+" VALUES (4, '2020-03-04', 30); "
51+
+"INSERT INTO Orders(product_id, order_date, unit)"
52+
+" VALUES (4, '2020-03-04', 60); "
53+
+"INSERT INTO Orders(product_id, order_date, unit)"
54+
+" VALUES (5, '2020-02-25', 50); "
55+
+"INSERT INTO Orders(product_id, order_date, unit)"
56+
+" VALUES (5, '2020-02-27', 50); "
57+
+"INSERT INTO Orders(product_id, order_date, unit)"
58+
+" VALUES (5, '2020-03-01', 50); ")
59+
classMysqlTest {
60+
@Test
61+
voidtestScript(@EmbeddedDatabaseDataSourcedataSource)
62+
throwsSQLException,FileNotFoundException {
63+
try (finalConnectionconnection =dataSource.getConnection()) {
64+
try (finalStatementstatement =connection.createStatement();
65+
finalResultSetresultSet =
66+
statement.executeQuery(
67+
newBufferedReader(
68+
newFileReader(
69+
"src/main/java/g1301_1400/"
70+
+"s1327_list_the_products_"
71+
+"ordered_in_a_period/script.sql"))
72+
.lines()
73+
.collect(Collectors.joining("\n"))
74+
.replaceAll("#.*?\\r?\\n",""))) {
75+
assertThat(resultSet.next(),equalTo(true));
76+
assertThat(resultSet.getNString(1),equalTo("Leetcode Kit"));
77+
assertThat(resultSet.getInt(2),equalTo(100));
78+
assertThat(resultSet.next(),equalTo(true));
79+
assertThat(resultSet.getNString(1),equalTo("Leetcode Solutions"));
80+
assertThat(resultSet.getInt(2),equalTo(130));
81+
assertThat(resultSet.next(),equalTo(false));
82+
}
83+
}
84+
}
85+
}

0 commit comments

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