Codewars - KYU 8 - SQL Practice (10題)

Multiply

Describe -

The code does not execute properly. Try to figure out why.

SELECT price + amount AS total FROM items

Answer -

SELECT price * amount AS total FROM items

SELECT
CAST(price As float) * CAST(amount AS float) total
FROM items

SELECT (price::float * amount::float) AS total FROM items

#

cast是SQL標準中定義的轉換函數，其語法格式為：
cast(expression as data_type[(length)])
expression為任何有效的表達式，可以是常量、變量、表達式、欄位等等。
data_type為強制轉換到的數據類型。
length為指定目標數據類型長度的可選整數。默認值為30。

:: is CAST operation(cast to a date type).

Even or Odd

Describe -

You will be given a table, numbers, with one column number.

Return a table with a column is_even containing "Even" or "Odd" depending on number column values.

numbers table schema
number INT
output table schema
is_even STRING

Answer -

select Case when number%2 = 0 then 'Even' else 'Odd' end as is_even from numbers;

注意用餘1的話 有正負號問題

select (case
when (number % 2 = 1) then 'Odd'
when (number % 2 = -1) then 'Odd'
else 'Even' end)
is_even
from numbers

Opposite number

Describe -

Very simple, given a number, find its opposite.

Examples:

1: -1
14: -14
-34: 34

Answer -

select - number as res from opposite

SELECT -1*O.number AS res
FROM opposite O

將opposite table取別名為 O , 前面的column要加上O. 指定table 多個table資料合併時有用

Century From Year

Describe -

The first century spans from the year 1 up to and including the year 100, The second - from the year 101 up to and including the year 200, etc.

Task :
Given a year, return the century it is in.

Input , Output Examples ::
centuryFromYear(1705) returns (18)
centuryFromYear(1900) returns (19)
centuryFromYear(1601) returns (17)
centuryFromYear(2000) returns (20)
In SQL, you will be given a table years with a column yr for the year. Return a table with a column century.

Hope you enjoy it .. Awaiting for Best Practice Codes

Enjoy Learning !!!

Answer -

1.
select floor((yr+99)/100) as century from years

2.
select (yr+99)/100 as century from years

3.
SELECT CEILING(yr/100.00) AS Century FROM years

1.
floor無條件捨去 , 年份先加99再除100後等於世紀
ex: 2.5 + 99 = 101.5
101.5 / 100 = 1.015
1.015 捨去後為 1 世紀
2.
SQL中因為數值都是int , 除完仍是int所以可以直接除
ex: 2 + 99 = 101
101 / 100 = 1
3.
ceiling無條件進位 , 將int除完float後會是float
ex: 2 / 100.00 = 0.02
0.02無條件進位後為 1 世紀

Is n divisible by x and y?

Describe -

Create a function that checks if a number n is divisible by two numbers x AND y. All inputs are positive, non-zero digits.

Examples:
n = 3, x = 1, y = 3 => true because 3 is divisible by 1 and 3
n = 12, x = 2, y = 6 => true because 12 is divisible by 2 and 6
n = 100, x = 5, y = 3 => false because 100 is not divisible by 3
n = 12, x = 7, y = 5 => false because 12 is neither divisible by 7 nor 5

you will be given a table 'kata' with columns 'n', 'x', and 'y'. Return the 'id' and your result in a column named 'res'.

Answer -

1.
select
row_number() over() as id,
case when n%x=0 and n%y=0then true
else false end res
from kata

2.
select id ,
case
when n % x = 0 and n % y = 0 then True
else false end as res from kata

3.
SELECT id, n % x = 0 AND n % y = 0 AS res FROM kata

#

1.row_number()搭配 over()可自建遞增序列 , 或是在over()內指定條件排序

Keep Hydrated!

Describe -

Nathan loves cycling.

Because Nathan knows it is important to stay hydrated, he drinks 0.5 litres of water per hour of cycling.

You get given the time in hours and you need to return the number of litres Nathan will drink, rounded to the smallest value.

For example:

time = 3 ----> litres = 1

time = 6.7---> litres = 3

time = 11.8--> litres = 5

You have to return 3 columns: id, hours and liters (not litres, it's a difference from the kata description)

Answer -

SELECT id,hours,floor(hours/2) as liters FROM cycling

SELECT *, floor(hours / 2) as liters FROM cycling

Beginner Series #2 Clock

Describe -

Clock shows 'h' hours, 'm' minutes and 's' seconds after midnight.

Your task is to make 'Past' function which returns time converted to milliseconds.

Example:
{h: 0, m: 1, s: 1} => res: 61000
Input constraints: 0 <= h <= 23, 0 <= m <= 59, 0 <= s <= 59

Answer -

select (h3600 + m60 + s)*1000 as res from past

select 1000*extract(epoch from make_time(h, m, s))::int as res from past

#

MAKETIME(hour,minute,second)
返回從小時，分鐘和秒參數計算出的時間值。

SELECT MAKETIME(12,15,30);
MAKETIME(12,15,30)
'12:15:30'

epoch
對於 date 和 timestamp 數值而言， 是自 1970-01-01 00:00:00 以來的秒數(結果可能是負數。)； 對於 interval 數值而言，它是時間間隔的總秒數。

SELECT EXTRACT(EPOCH from TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320

SELECT EXTRACT(EPOCH from INTERVAL '5 days 3 hours');
Result: 442800

extract 函數從日期/時間數值裡抽取 子域，比如年或者小時等。source 必須是一個類型 timestamp，time，或者 interval 的值資料表達式。 （類型為 date 的資料表達式將轉換為 timestamp，因此也可以用。） field 是一個標識符 或者字串，它指定從源資料中抽取的數域。extract 函數返回類型為double precision 的數值。

Expressions Matter

Describe -

Task
Given three integers a ,b ,c, return the largest number obtained after inserting the following operators and brackets: +, , ()
In other words , try every combination of a,b,c with [+()] , and return the Maximum Obtained
Consider an Example :
With the numbers are 1, 2 and 3 , here are some ways of placing signs and brackets:

1 (2 + 3) = 5
1 2 3 = 6
1 + 2 3 = 7
(1 + 2) * 3 = 9
So the maximum value that you can obtain is 9.

Notes
The numbers are always positive.
The numbers are in the range (1  ≤  a, b, c  ≤  10).
You can use the same operation more than once.
It's not necessary to place all the signs and brackets.
Repetition in numbers may occur .
You cannot swap the operands. For instance, in the given example you cannot get expression (1 + 3) 2 = 8.
Input >> Output Examples:
expressionsMatter(1,2,3) ==> return 9
Explanation:
After placing signs and brackets, the Maximum value obtained from the expression (1+2) 3 = 9.

expressionsMatter(1,1,1) ==> return 3
Explanation:
After placing signs, the Maximum value obtained from the expression is 1 + 1 + 1 = 3.

expressionsMatter(9,1,1) ==> return 18
Explanation:
After placing signs and brackets, the Maximum value obtained from the expression is 9 * (1+1) = 18.

Answer -

SELECT greatest(
a+b+c,
(a+b)c,
a(b+c),
abc)
AS res FROM expression_matter;

Adults only (SQL for Beginners #1)

Describe -

In your application, there is a section for adults only. You need to get a list of names and ages of users from the users table, who are 18 years old or older.

users table schema

name
age
NOTE: Your solution should use pure SQL. Ruby is used within the test cases just to validate your answer.

Answer -

select name , age from users where age >= 18

Grasshopper - Messi goals function

Describe -

Messi goals function
Messi is a soccer player with goals in three leagues:

LaLiga
Copa del Rey
Champions
Complete the function to return his total number of goals in all three leagues.

Note: the input will always be valid.

For example:

5, 10, 2 --> 17

you will be given a table, goals,
with columns la_liga_goals, copa_del_rey_goals, and champions_league_goals.
Return a table with a column, res.

Answer -

select la_liga_goals + copa_del_rey_goals + champions_league_goals
as res from goals;