Day 4 – Tomasz Leś

use tomasz1_zawodnicy

— calculate when are next birth day of person

select player_name, player_surname, birth_date,
(sign(
DATEDIFF(d,
DATEFROMPARTS(year(getdate()),MONTH(birth_date),day(birth_date)),
GETDATE()))+1)/2

from players

select player_name, player_surname, birth_date,

DATEFROMPARTS(year(getdate())
                +(sign(
                    DATEDIFF(d,
                        DATEFROMPARTS(year(getdate()),MONTH(birth_date),day(birth_date)),
                        GETDATE()))+1)/2,
        MONTH(birth_date),
        day(birth_date)) [next birthday]

from players

select player_name, player_surname, birth_date,
YEAR(
DATEFROMPARTS(year(birth_date),MONTH(birth_date),day(birth_date)))
+1
+ datediff(year,birth_date,getdate())
from players

select player_name, player_surname, birth_date,

DATEFROMPARTS(year(getdate()),MONTH(birth_date),day(birth_date))

from players

select player_name, player_surname, birth_date,
datediff(year,birth_date,getdate())
from players

select player_name, player_surname,
DATEFROMPARTS(
year(dateadd
(year ,1,
dateadd(year,
datediff(year,birth_date,getdate()),
dateadd(year,
1,
datefromparts(year(birth_date)
,month(birth_date),
day(birth_date)))))),
month(birth_date),day(birth_date))
from players

select
dateadd(year,
datediff(year,birth_date,getdate()),
dateadd(year,
1,
datefromparts(year(birth_date)
,month(birth_date),
day(birth_date))))

from players

select
year (getdate())

from players

select DATEDIFF(year,birth_date,getdate())

from players

select player_name, player_surname, country, len(player_name)
from players
where country=’ger’ or country=’aut’ — the result of this statement must be true or false, you can use „AND” or „OR”

select player_name, player_surname, country, len(player_name)
from players
where country in (’ger’,’aut’)

select player_name, player_surname, country
from players
where (len(player_name)>5 and country=’pol’)

— ex1 list players with bmi less than 20

select player_name,player_surname, weight/POWER(height/100.0,2) bmi
from players
where weight/POWER(height/100.0,2) < 20

— ex2 list players with length of surname equals 5

select player_name,player_surname
from players
where len(player_surname)=5

— ex3 list players born in the season from November to March

select player_name,player_surname, birth_date
from players
where MONTH(birth_date) >= 11 or MONTH(birth_date) <= 3

select player_name,player_surname, birth_date
from players
where MONTH(birth_date) > 10 or MONTH(birth_date) < 4

select player_name,player_surname, birth_date
from players
where MONTH(birth_date) in (11,12,1,2,3)

select player_name,player_surname, birth_date
from players
where MONTH(birth_date) = 11 or
MONTH(birth_date) =12 or
MONTH(birth_date) =1 or
MONTH(birth_date) =2 or
MONTH(birth_date) =3

— ex4 List players who contain the letters N or W

select player_name,player_surname, birth_date, CHARINDEX(’N’,player_name)
from players

select player_name,player_surname, birth_date
from players
where CHARINDEX(’N’,player_name) >0 or CHARINDEX(’W’,player_name) >0 or
CHARINDEX(’N’,player_surname) >0 or CHARINDEX(’W’,player_surname) >0

select player_name,player_surname, birth_date
from players
where CHARINDEX(’N’,player_name) + CHARINDEX(’W’,player_name) +
CHARINDEX(’N’,player_surname)+ CHARINDEX(’W’,player_surname) >0

select player_name,player_surname, birth_date
from players
where player_surname like '%N%’ or player_surname like '%W%’
— in access we have * instead of %

select player_name,player_surname, birth_date
from players
where player_surname like '%en’

select player_name,player_surname, birth_date
from players
where player_surname like 'HO%’

select player_name,player_surname, birth_date
from players
where player_name like 'Mar_in’

select player_name,player_surname, birth_date
from players
where player_name like 'Mar[tcw]in’

— ex5 list the trainers without a date of birth (date of birth is empty)

select *
from trainers
where birth_date is null

select *
from trainers
where birth_date is not null

/* if you want to compare your column to null you can only use „is” or „is not” null */

/* we can use if we compare collumn to other value :
>
<

=
<= = <> not equlas
*/

select player_name,player_surname,len(player_surname)
from players
where len(player_surname) <> 5

–ex1 calculate the age of the players

select player_name, player_surname,
DATEDIFF(year,birth_date,getdate()) [year],
year(getdate()) – year(birth_date) [year]
from players

select player_name, player_surname, year(getdate()) – year(birth_date) [year]
from players

select player_name, player_surname, country, height
from players
–where
order by country , height desc

select player_name, player_surname, country, height
from players
order by len(player_surname)

select player_name,player_surname, weight/POWER(height/100.0,2) bmi
from players
order by bmi

/* 3/select player_name,player_surname, weight/POWER(height/100.0,2) bmi / 1/ from players / 2/ where bmi < 20 — it doesnt works! / 4*/order by bmi

select distinct country, weight
from players

select * from players

select top 3 player_name,player_surname, country, height
from players
order by height desc

select top 50 percent player_name,player_surname, country, height
from players
order by height desc

select distinct country, weight
from players

select distinct country
from players

–select weight, distinct country
–from players
— now its not possible but in the future we will use group by option

select *
from players
order by height
offset 3 rows
fetch next 4 rows only

select player_name,player_surname, country, height,

    iif(height>175, 'big',  'small')

from players

select player_name,player_surname, country, height,

    iif( weight/POWER(height/100.0,2)>20, 'ok',  'not ok') [proper bmi]

from players

— for people taht height is higher than 175 cm please write 'big’
— if not please wrtie 'small’

— please list players and
— if height <170 then 'small’ — if height > 180 then 'big’
— if hegiht > 185 then 'very big’

select player_name,player_surname, country, height,

iif(
height<170, 'small’, iif( height>185,
’very big’,
’big’))

from players

— ex : please write the names of seasons when player born

— spring 3,4,5 (number of months)
— summer 6,7,8,
— autumn 9,10,11
— winter 12,1,2

select player_name,player_surname, country, birth_date,

iif(month(birth_date) >=3 and month(birth_date) <=5 , 'spring',
    iif(month(birth_date) in (6,7,8),'summer',
        iif(month(birth_date) between 9 and 11, 'autumn','winter'))) [season]

from players

/*
Spring starts on March 20
Summer begins on June 21

Autumn begins on September 22
Winter begins on the 21st December
*/

select player_name,player_surname, country, birth_date,

iif(datefromparts(year(getdate()),month(birth_date),day(birth_date)) >
    datefromparts(year(getdate()),3,20),'yes','no')

from players

–http://tomaszles.pl/2021/07/06/
–http://tomaszles.pl/2021/07/08/
–http://tomaszles.pl/2021/07/13/
–http://tomaszles.pl/2021/07/15/

select* from players