Необис / Sql
.docxСсылки: 0) Card Number Sort https://www.solveway.club/db/problem/460 Select Distinct Name, CardNumber, Sort =
Case CardNumber
When '6664856782286363' Then '2233456666667888'
When '5381925824255524' Then '1222234455555889'
When '4142432091351965' Then '0111223344455699'
When '5583041964294348' Then '0123344445568899'
When '2970918509622358' Then '0012223556788999'
When '6754555546023955' Then '0234455555556679'
When '4411528382564741' Then '1112234444556788'
When '4411528382564741' Then '1112234444556788'
When '3783637036401597' Then '0013333456677789'
When '9097582709483169' Then '0012345677889999'
When '9456377764433537' Then '3333444556677779'
End
From Employees
Where Education = 'Incomplete HE'
Order By Name ASC
1) Names of Dishes of 3 or More Words - String functions https://www.solveway.club/db/problem/48
Select Distinct Name From Dishes
Where Name like '% _%_ %' 2) The Full English Breakfast - Select, WHERE https://www.solveway.club/db/problem/53
Select Distinct Name as IngredientName From Ingredients
Where Name In ('Egg', 'Sausage', 'Bacon', 'Tomato', 'Mushroom', 'Bread')
Order By Name ASC 3) Names of Dishes Starting with S II - String functions, Order by, LIKE https://www.solveway.club/db/problem/44
Select Name From Dishes
Where Name Like 'S%'
ORDER BY SUBSTRING(Name, 3, 1) ASC, Name ASC
4) Triangle Sides - Subqueries https://www.solveway.club/db/problem/10
SELECT ID, IIF ( SideA + SideB > SideC and SideA + SideC > SideB and SideB + SideC > SideA, 'YES', 'NO' ) AS Answer From TriangleSides 5) Find Dishes I - SELECT https://www.solveway.club/db/problem/25
Select Name From Dishes
Where Weight <= 150 6) Find Dishes II - WHERE, ORDER BY https://www.solveway.club/db/problem/49
Select Name From Dishes
Where Weight = 400 or Weight = 450 or Weight = 500
Order By Weight ASC, Name ASC 7) Names of Dishes Starting with S - STRING LIKE https://www.solveway.club/db/problem/43
Select Name From Dishes
Where Name Like 'S%' 8) Names of Dishes Ending with r-t - STRING LIKE https://www.solveway.club/db/problem/47
Select Name From Dishes
Where Name Like '%[r-t]' 9) Names Transformation I - STRING functions https://www.solveway.club/db/problem/73 Select Case
When Name Like '%, %' Then Concat(Stuff(Right(Name, Charindex(' ', Reverse(Name))), 1, 1, ''), ' ',
Left(Name, Len(Name) - Charindex(',', Reverse(Name))))
Else Concat('!', Name) End As NewName
From Employees
10) Universal Ingredients - Select, inner join, subqueries, union https://www.solveway.club/db/problem/58
11) Best visiting teams - Select, inner join, subqueries, aggregates, unions https://www.solveway.club/db/problem/2
SELECT Teams.Name, (Visitorscores.Visitorscores * 100.0 / (Visitorscores.Visitorscores + Homescores.Homescores)) AS Column1
FROM (SELECT Hometeamid As Id, Sum(Homescore) Homescores
FROM Matches
GROUP BY Hometeamid) AS Homescores Join
(SELECT Visitorteamid AS Id, Sum(Visitorscore) Visitorscores
FROM Matches Group BY Visitorteamid) AS Visitorscores ON Visitorscores.id = Homescores.Id JOIN
Teams ON Visitorscores.Id = Teams.Id
Order By Column1 DESC
12) Привередливый клиент - Select, string functions, subqueries https://www.solveway.club/db/problem/88
13) Zodiac signs I https://www.solveway.club/db/problem/72
SELECT Name, DateOfBirth,
CASE
WHEN (MONTH(DateOfBirth) = 3 AND DAY(DateOfBirth) >= 21) OR (MONTH(DateOfBirth) = 4 AND DAY(DateOfBirth) <= 20) THEN 'Aries'
WHEN (MONTH(DateOfBirth) = 4 AND DAY(DateOfBirth) >= 21) OR (MONTH(DateOfBirth) = 5 AND DAY(DateOfBirth) <= 21) THEN 'Taurus'
WHEN (MONTH(DateOfBirth) = 5 AND DAY(DateOfBirth) >= 22) OR (MONTH(DateOfBirth) = 6 AND DAY(DateOfBirth) <= 21) THEN 'Gemini'
WHEN (MONTH(DateOfBirth) = 6 AND DAY(DateOfBirth) >= 22) OR (MONTH(DateOfBirth) = 7 AND DAY(DateOfBirth) <= 22) THEN 'Cancer'
WHEN (MONTH(DateOfBirth) = 7 AND DAY(DateOfBirth) >= 23) OR (MONTH(DateOfBirth) = 8 AND DAY(DateOfBirth) <= 22) THEN 'Leo'
WHEN (MONTH(DateOfBirth) = 8 AND DAY(DateOfBirth) >= 23) OR (MONTH(DateOfBirth) = 9 AND DAY(DateOfBirth) <= 23) THEN 'Virgo'
WHEN (MONTH(DateOfBirth) = 9 AND DAY(DateOfBirth) >= 24) OR (MONTH(DateOfBirth) = 10 AND DAY(DateOfBirth) <= 23) THEN 'Libra'
WHEN (MONTH(DateOfBirth) = 10 AND DAY(DateOfBirth) >= 24) OR (MONTH(DateOfBirth) = 11 AND DAY(DateOfBirth) <= 22) THEN 'Scorpio'
WHEN (MONTH(DateOfBirth) = 11 AND DAY(DateOfBirth) >= 23) OR (MONTH(DateOfBirth) = 12 AND DAY(DateOfBirth) <= 21) THEN 'Sagittarius'
WHEN (MONTH(DateOfBirth) = 12 AND DAY(DateOfBirth) >= 22) OR (MONTH(DateOfBirth) = 1 AND DAY(DateOfBirth) <= 20) THEN 'Capricorn'
WHEN (MONTH(DateOfBirth) = 1 AND DAY(DateOfBirth) >= 21) OR (MONTH(DateOfBirth) = 2 AND DAY(DateOfBirth) <= 19) THEN 'Aquarius'
WHEN (MONTH(DateOfBirth) = 2 AND DAY(DateOfBirth) >= 20) OR (MONTH(DateOfBirth) = 3 AND DAY(DateOfBirth) <= 20) THEN 'Pisces'
END AS Zodiac
FROM Employees
WHERE ID NOT IN (SELECT LeaderID FROM Employees WHERE LeaderID IS NOT NULL);
14) Lhung-Zi Сivilization https://www.solveway.club/db/problem/608
Select Replace(Name, 'L', 'r') AS NewName from Dishes
Where Name Not Like '%a%a%a%' And SectionID Not In (Select ID From Sections Where Name Like
'%drink%')
Выполнено: 13/15 (80%)