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

Commitf414bf5

Browse files
authored
Improved tasks 175-184
1 parentc7aa0c5 commitf414bf5

File tree

8 files changed

+76
-32
lines changed

8 files changed

+76
-32
lines changed
Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,12 @@
11
# Write your MySQL query statement below
22
# #Easy #Database #SQL_I_Day_5_Union #2022_06_26_Time_491_ms_(32.30%)_Space_0B_(100.00%)
3-
SELECT FirstName, LastName, City, State
4-
FROM PersonLEFT JOIN Address USING (PersonId)
3+
SELECT
4+
FirstName,
5+
LastName,
6+
City,
7+
State
8+
FROM
9+
Person
10+
LEFT JOIN
11+
Address
12+
USING (PersonId);
Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,13 @@
11
# Write your MySQL query statement below
22
# #Medium #Database #SQL_I_Day_4_Union_and_Select
33
# #2022_07_10_Time_225_ms_(73.10%)_Space_0B_(100.00%)
4-
SELECT ifnull(
5-
(SELECT distinct(Salary)
6-
FROM Employee
7-
ORDER BY SalaryDESC
8-
LIMIT1
9-
OFFSET1),NULL) SecondHighestSalary;
4+
SELECT
5+
IFNULL(
6+
(
7+
SELECT DISTINCT Salary
8+
FROM Employee
9+
ORDER BY SalaryDESC
10+
LIMIT1 OFFSET1
11+
),
12+
NULL
13+
)AS SecondHighestSalary;
Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,10 @@
11
# Write your MySQL query statement below
22
# #Medium #Database #2022_06_26_Time_290_ms_(66.73%)_Space_0B_(100.00%)
3-
select Score, DENSE_RANK() OVER(order by ScoreDesc)as"Rank"from Scoresorder by"Rank"Asc;
3+
SELECT
4+
Score,
5+
DENSE_RANK() OVER (ORDER BY ScoreDESC)AS Rank
6+
FROM
7+
Scores
8+
ORDER BY
9+
RankASC;
10+
Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,11 @@
11
# Write your MySQL query statement below
22
# #Medium #Database #2024_07_15_Time_469_ms_(89.19%)_Space_0B_(100.00%)
3-
SELECT DISTINCTl1.numAS ConsecutiveNums
4-
FROM Logs l1
5-
JOIN Logs l2ONl1.id=l2.id-1
6-
JOIN Logs l3ONl1.id=l3.id-2
7-
WHEREl1.num=l2.numANDl2.num=l3.num;
3+
SELECT DISTINCT
4+
l1.numAS ConsecutiveNums
5+
FROM
6+
Logs l1
7+
JOIN Logs l2ONl1.id=l2.id-1
8+
JOIN Logs l3ONl1.id=l3.id-2
9+
WHERE
10+
l1.num=l2.num
11+
ANDl2.num=l3.num;
Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,10 @@
11
# Write your MySQL query statement below
22
# #Easy #Database #2022_06_27_Time_315_ms_(94.44%)_Space_0B_(100.00%)
3-
selecta.Nameas Employeefrom Employee aleft join Employee bona.ManagerId=b.Id
4-
wherea.Salary>b.Salaryanda.ManagerIdis not null
3+
SELECT
4+
a.NameAS Employee
5+
FROM
6+
Employee a
7+
LEFT JOIN Employee bONa.ManagerId=b.Id
8+
WHERE
9+
a.Salary>b.Salary
10+
ANDa.ManagerIdIS NOT NULL;
Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,11 @@
11
# Write your MySQL query statement below
22
# #Easy #Database #SQL_I_Day_10_Where #2022_06_27_Time_303_ms_(92.08%)_Space_0B_(100.00%)
3-
SELECT EmailFROM PersonGROUP BY EmailHAVINGCOUNT(Email)>1;
3+
SELECT
4+
Email
5+
FROM
6+
Person
7+
GROUP BY
8+
Email
9+
HAVING
10+
COUNT(Email)>1;
11+
Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,9 @@
11
# Write your MySQL query statement below
22
# #Easy #Database #SQL_I_Day_1_Select #2022_06_27_Time_376_ms_(98.73%)_Space_0B_(100.00%)
3-
SELECTc.Nameas Customers
4-
FROM Customersas c
5-
LEFT JOIN Ordersas o
6-
ONc.Id=o.CustomerId
7-
WHEREo.CustomerId isnull
3+
SELECT
4+
c.NameAS Customers
5+
FROM
6+
CustomersAS c
7+
LEFT JOIN OrdersAS oONc.Id=o.CustomerId
8+
WHERE
9+
o.CustomerId ISNULL;

‎src/main/java/g0101_0200/s0184_department_highest_salary/script.sql

Lines changed: 15 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -5,13 +5,18 @@ SELECT
55
Sel.NameAS Employee,
66
Sel.SalaryAS Salary
77
FROM
8-
(
9-
SELECT
10-
Name,
11-
Salary,
12-
DepartmentId,
13-
DENSE_RANK() OVER (PARTITION BY DepartmentIdORDER BY SalaryDESC)AS dr
14-
FROM Employee
15-
)AS Sel
16-
INNER JOIN Department dONd.Id=Sel.DepartmentId
17-
WHERESel.dr=1
8+
(
9+
SELECT
10+
Name,
11+
Salary,
12+
DepartmentId,
13+
DENSE_RANK() OVER (
14+
PARTITION BY DepartmentId
15+
ORDER BY SalaryDESC
16+
)AS dr
17+
FROM
18+
Employee
19+
)AS Sel
20+
INNER JOIN Department dONd.Id=Sel.DepartmentId
21+
WHERE
22+
Sel.dr=1;

0 commit comments

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