Dzień 1

baza danych


SELECT 5 , 4+6

select 4-7 ,   10.0/3

select 4-7,  imie, nazwisko, waga
from zawodnicy

select imie, nazwisko, waga, wzrost  
from zawodnicy

select *, imie from zawodnicy

select imie, waga [xx] , 5 [liczba] , waga+5 [nowa waga]
from zawodnicy

-- komentarz jednoliniowy 

/* komentarz 
	wieloliniowy 
*/

-- operacje arytemtyczne + - *  / 
--  4,5 cw str 8 , 

select imie, nazwisko, wzrost*1.46 [max dł nart]
from zawodnicy

select wzrost/100.0, wzrost from zawodnicy

select imie, nazwisko, 
		round(waga / (wzrost/100.0*wzrost/100.0),2) [bmi]
from zawodnicy

select round(10.0/3,2)

-- str 14 funkcje matematyczne

select abs(-6), power(2,3), sign(10)

select imie,nazwisko, 
	round(waga / power(wzrost/100.0,2),2)
from zawodnicy

select 'nazywam się '+ imie + ' ' + nazwisko
from zawodnicy

--cw 6 str8 

select nazwisko + ', ' + imie + ' ('+ kraj + ')' 
from zawodnicy

select FORMAT(10.0/3,'000.0000') 

select imie, nazwisko,
 format(waga/power(wzrost/100.0,2),'0.00')
from zawodnicy

select format(12.75,'0.0')

select imie, nazwisko, data_ur,waga,
	FORMAT(data_ur,'yyyy dd MM "miesiac"')
from zawodnicy

-- str 14 , cw 24,25

select imie, nazwisko , FORMAT(data_ur,'dd.MM.yyyy r.')
from zawodnicy

select imie, nazwisko , 
	FORMAT(data_ur,'dddd dd.MM.yyyy MMMM','pl-pl')
from zawodnicy

select imie, nazwisko , 
	FORMAT(data_ur,'MMMM','pl-pl')
from zawodnicy
-- funkcje zwiazane z przetwarzaniem dat 

select getdate()

select imie, nazwisko, DATEADD(M,2,data_ur)
from zawodnicy

select imie, nazwisko, DATEDIFF(YYYY,data_ur,GETDATE())
from zawodnicy

select DATEDIFF(d,getdate(), DATEFROMPARTS(2018,6,1))

select day(GETDATE()),month(GETDATE()),year(GETDATE())

select format(GETDATE(),'dd'),
	   format(GETDATE(),'MM'),
	   format(GETDATE(),'yyyy')
-- zadania:
-- 1) dla kazdego zawodnika wypisz ile ma miesięcy od urodzenia

select imie, nazwisko , DATEDIFF(MM,data_ur,getdate())
from zawodnicy

-- 2) dla kazdego zawodnika wypisz kiedy przypada data jego
-- urodzin w aktualnym roku (2018) 

select imie, nazwisko , DATEFROMPARTS(year(getdate()),month(data_ur),day(data_ur))
from zawodnicy

-- 3) dla kazdego zawodnika wypisz ile dni przed końcem roku
    -- się urodził 

select imie, nazwisko , DATEDIFF(d, data_ur, datefromparts(year(data_ur),12,31))
from zawodnicy

/* dodatkowe
-- ile zostało nam dni do konca roku od dzisiaj
*/
select DATEDIFF(D,GETDATE(),datefromparts(year(getdate()),12,31))

-- podaj nazwe miesiaca w ktorym urodzil się dany zawodnik

select imie, nazwisko, FORMAT(data_ur,'MMMM','pl-pl')
from zawodnicy

-- (pomocnicze) podaj datę równą pierwszemu dniu miesiąca po miesiącu
--  urodzenia zawodnika

  select DATEFROMPARTS(2018,12,31)

   select imie, nazwisko, data_ur, DATEFROMPARTS(
									year(DATEADD(m,1,data_ur)),
									month(DATEADD(m,1,data_ur)),
									1)
   from zawodnicy

-- ile dni ma miesiac w ktorym sie urodzil dany zawodnik
 select imie, nazwisko, data_ur, DATEFROMPARTS(
									year(DATEADD(m,1,data_ur)),
									month(DATEADD(m,1,data_ur)),
									1) ,
     DATEFROMPARTS(year(data_ur),month(data_ur),1),

	 DATEDIFF(d,
			DATEFROMPARTS(year(data_ur),month(data_ur),1),	
			DATEFROMPARTS(
									year(DATEADD(m,1,data_ur)),
									month(DATEADD(m,1,data_ur)),
									1))
   from zawodnicy
 

-- w jaki dzien tygodnia wypada w aktualnym roku wigila
select  FORMAT(DATEFROMPARTS(year(getdate()),12,24),'dddd','pl-pl')

-- jaki dzien tygodnia bedzie za 2 miesiace i 5 dni

select FORMAT( DATEADD(d,5, DATEADD(m,2,getdate())),'dddd','en-us')


-- matematyczne, datowe, format, na napisach 

select LEN('ala ma kota')

select LEN(imie+nazwisko) from zawodnicy

select UPPER('ala Ma KOTA'),lower('ala Ma KOTA'),
       CHARINDEX('ma','ala ma kota'),
	   Rtrim(LTRIM('   ala ma kota  ')),
	   LEFT('ala ma kota',2),
	   SUBSTRING('ala ma kota',5,2)

-- str  15 , cw 29-31
select FORMAT(waga,'0') + 'a' from zawodnicy

select imie + ' ' + left(nazwisko,1) + LOWER(substring(nazwisko,2,len(nazwisko)))
from zawodnicy

select imie + ' ' + LEFT(nazwisko,1) + '.'
from zawodnicy

select imie + ' '+ nazwisko + ' waży ' + format(waga,'0') + ' kg'
from zawodnicy

select CONVERT(varchar,23), CONVERT(varchar,getdate())


-- dodatkowe 
--1) dla kazdego zawodnika wypisz środkową literę jego imienia i nazwiska
select substring(imie,len(imie)/2,1) + ' ' +substring(nazwisko,len(nazwisko)/2,1)  
,imie , nazwisko from zawodnicy

--2) Dla kazdego zawodnika wypisz 3 pierwsze litery miesiąca w którym się urodził
select imie, nazwisko , LEFT( format(data_ur,'MMMM','pl-pl'),3)
from zawodnicy

--3) Jeśli zawodnik urodził się w miesiącu w którym jest litera 'r' to dodaj do 
---jego wagi 1 kg . (użyj TYLKO funkcji, które poznaliśmy)

select imie, nazwisko, data_ur, FORMAT(data_ur,'MMMM','pl-pl'),
 SIGN(CHARINDEX('r',FORMAT(data_ur,'MMMM','pl-pl')))+waga
from zawodnicy


--4) wiedząc, że jest funkcja Replace np: select REPLACE('ala','a','o'), która zamienia jeden
-- ciąg na drugi podmień w imionach wszystkie litery a na o natomiast litery o na a
-- np: ala stanie się olo a olo stanie się ala
 select replace(replace(REPLACE(imie,'a','!'),'o','a'),'!','o'), imie
 from zawodnicy
 
 --5) wiedząc, że jest funkcja select REVERSE('xyz'), która odwraca nam ciag
-- podaj podciąg z imienia od pierwszej litery 'e' do ostatniej litery 'e'
-- jezeli imie nie ma wcale litery 'e' - wypisz cale imie
 select imie from zawodnicy

 select imie, CHARINDEX('e',imie),
 LEN(imie)- CHARINDEX('e',REVERSE(imie))+1 - CHARINDEX('e',imie)+1,
  SUBSTRING(imie, CHARINDEX('e',imie),LEN(imie)- CHARINDEX('e',REVERSE(imie))+1 - CHARINDEX('e',imie)+1)
 from zawodnicy

 select imie, nazwisko, wzrost
 from zawodnicy
 where wzrost < 177 and len(waga)> 170
 
 -- str 11 


select imie, nazwisko, kraj 
from zawodnicy
where kraj = 'pol'
 
select imie, nazwisko, kraj 
from zawodnicy
where kraj = 'ger' or kraj = 'aut'
 
 
select imie, nazwisko, kraj 
from zawodnicy
where kraj in ('ger','aut')

select imie, nazwisko, kraj , waga/POWER(wzrost/100.0,2) [bmi]
from zawodnicy
where waga/POWER(wzrost/100.0,2)<20
 
 select imie, nazwisko, kraj ,data_ur
from zawodnicy
where DATEDIFF(YYYY,data_ur,getdate())>35
  
   select imie, nazwisko, kraj ,data_ur
from zawodnicy
where LEN(IMIE)=4

select imie_t, nazwisko_t, data_ur_t
from trenerzy
where data_ur_t is not null

select imie, nazwisko
from zawodnicy
where MONTH(data_ur) >=11 or MONTH(data_ur) <=3


select imie, nazwisko
from zawodnicy
where MONTH(data_ur) >10 or MONTH(data_ur) <4

select imie, nazwisko
from zawodnicy
where MONTH(data_ur) >5 and MONTH(data_ur) <10

select imie, nazwisko
from zawodnicy
where MONTH(data_ur) between 6 and 9

select imie, nazwisko
from zawodnicy
where MONTH(data_ur) not between 4 and 10 

select imie, nazwisko
from zawodnicy
where CHARINDEX('n',nazwisko) > 0 or CHARINDEX('w',nazwisko) > 0  

select imie, nazwisko
from zawodnicy
where CHARINDEX('n',nazwisko) + CHARINDEX('w',nazwisko) > 0  
 
 -- wypisz tylko tych zawodników, którzy dopiero będą mieli w tym roku urodziny 

 select imie, nazwisko ,data_ur
 from zawodnicy
 where DATEFROMPARTS(year(getdate()),month(data_ur),day(data_ur)) > GETDATE()

-- wypisz zawodników, których imie zaczyna się i kończy na taką samą literę
select imie 
from zawodnicy
where LEFT(imie,1)=RIGHT(imie,1)

-- wypisz zawodników, których wzrost jest od 160 do 180 ale bez zawodników ze wzrostem od 167-172
select imie, nazwisko, wzrost
from zawodnicy
where (wzrost >= 160 and wzrost < 167) or (wzrost>172 and wzrost<=180) 

select imie, nazwisko, wzrost
from zawodnicy
where wzrost between 160 and 180 and wzrost not between 167 and 172

-- wypisz zawodników urodzonych w tym samym miesiącu co aktualny lub miesiąc wcześniej lub później (pamiętaj o grudniu) 
select imie, nazwisko , data_ur
from zawodnicy
where MONTH(data_ur) = MONTH(GETDATE()) or 
      MONTH(data_ur) = MONTH(dateadd(m,-1,getdate())) or
      MONTH(data_ur) = MONTH(dateadd(m,+1,getdate()))


select imie, nazwisko , data_ur
from zawodnicy
where MONTH(data_ur) in (  MONTH(GETDATE()) , 
                       MONTH(dateadd(m,-1,getdate())) ,
                       MONTH(dateadd(m,+1,getdate())))

-- wypisz tylko zawodników, którzy urodzili się w miesiącu parzystym 

select imie, nazwisko, data_ur
from zawodnicy
where MONTH(data_ur) /2.0 - MONTH(data_ur)/2=0

select imie, nazwisko, data_ur
from zawodnicy
where MONTH(data_ur) % 2 = 0

select imie, nazwisko, data_ur
from zawodnicy
where RIGHT(MONTH(data_ur),1) in (0,2,4,6,8)



select * 
from zawodnicy
order by kraj , wzrost desc 
-- str 10 

select AVG(waga) from zawodnicy

/* -- posortuj zawodników według miesiąca poprzedzającego miesiąc urodzenia
  -- posortuj zawodników według tego jak bardzo jego waga różni się od 61*/

select *
from zawodnicy
order by  waga, kraj

select * from zawodnicy
order by data_ur 

select imie + ' ' + nazwisko + '(' + kraj + ')'
 from zawodnicy
order by kraj

select *
from trenerzy
where data_ur_t is not null
order by data_ur_t

select imie, nazwisko, waga/POWER(wzrost/100.0,2) bmi
from zawodnicy	
order by 3

/* -- posortuj zawodników według miesiąca poprzedzającego miesiąc urodzenia
  -- posortuj zawodników według tego jak bardzo jego waga różni się od 61*/


  select * ,MONTH(dateadd(m,-1,data_ur))
  from zawodnicy
  order by MONTH(dateadd(m,-1,data_ur))

  select * 
  from zawodnicy
  order by abs(waga - 61)


  select imie, nazwisko, wzrost,
	IIF(wzrost>170,iif(wzrost<175,'średni','wysoki'),'niski')
  from zawodnicy


  -- str 17 



select IIF(kraj='pol','pan ',
		iif(kraj in ('ger','aut'),'herr ',
		   iif(kraj = 'usa','mr ', ''))) +
imie + ' '+ nazwisko
from zawodnicy

select 
	case 
		when kraj = 'pol' then 'pan '
		when kraj in ('ger','aut') then 'her '
		when kraj = 'usa' then 'mr '
		else ''
	end +
imie + ' ' + nazwisko
from zawodnicy

select imie, nazwisko, data_ur, 
	IIF(datefromparts(year(getdate()),month(data_ur),day(data_ur)) > getdate(),'jeszcze nie','juz mial')
from zawodnicy 

  
-- dla kazdego zawodnika wypisz jego albo imie albo nazwisko, w zależności co jest dłuższe
select IIF(len(imie)>len(nazwisko),imie, nazwisko)
from zawodnicy

-- dla kazdego zawodnika wypisz czy urodził się w dniu parzystym czy nieparzystym
select imie, nazwisko, data_ur, IIF(DAY(data_ur) %  2=0,'parzysty','np')
from zawodnicy

-- dla każdego zawodnika wypisz porę roku w jakiej się urodził
select imie, nazwisko, data_ur,
case
	when MONTH(data_ur) in (3,4,5) then 'wiosna'
	when  MONTH(data_ur) between 6 and 8 then 'lato'
	when MONTH(data_ur) > 8 and MONTH(data_ur) < 12 then 'jesien'
	else 'zima'
end
from zawodnicy


-- dla każdego zawodnika wypisz datę najbliższych urodzin 

select imie, nazwisko, data_ur,
	IIF(datefromparts(year(getdate()),month(data_ur),day(data_ur)) <= getdate(),
		datefromparts(year(getdate())+1,month(data_ur),day(data_ur)),
		datefromparts(year(getdate()),month(data_ur),day(data_ur)))
from zawodnicy

select imie, nazwisko, kraj,
	 case
		when kraj = 'pol' then 'Polska'
		when kraj = 'ger' then 'niemcy'
		when kraj = 'usa' then 'stany'
	 end  
from zawodnicy 


select imie, nazwisko, kraj,
	 case kraj
		when  'pol' then 'Polska'
		when  'ger' then 'niemcy'
		when  'usa' then 'stany'
	 end  
from zawodnicy