-- dla kazdego zawodnika wypisz porę roku (słownie) w jakiej się urodził
-- zakładając, że zima to miesiac: 12,1,2 itd...
select *
from
(select kraj, wzrost,
case
when month(data_ur) in (12,1,2) then 'zima'
when month(data_ur) between 3 and 5 then 'wiosna'
when month(data_ur) > 5 and month(data_ur)<9 then 'lato'
else 'jesien'
end pr
from zawodnicy) t
pivot
(
sum(wzrost)
for pr in ([wiosna],[lato],[jesien])
) pvt
-- zrób zestawienie, które dla każdego kraju wyświetli ile razy zawodnicy z tego kraju odwiedzili
-- jakie miasto (kraje to kolumny a miasta to wiersze)
select *
from
(select kraj, nazwa_miasta, COUNT(*) ile
from
(select z.kraj, m.nazwa_miasta
from zawodnicy z left join uczestnictwa u on u.id_zawodnika = z.id_zawodnika
left join zawody w on w.id_zawodow = u.id_zawodow
left join skocznie s on s.id_skoczni = w.id_skoczni
left join miasta m on m.id_miasta = s.id_miasta) t
group by t.kraj, t.nazwa_miasta) k
pivot
(
sum(ile)
for nazwa_miasta in (["Lahti"],["Oberstdorf"],["Zakopane"])
) pvt
-- zrób zestawienie pokazujace ile osób jakiego kraju urodzilo sie w jakim miejsiacu.
-- wypisz wszystkie (12) nazwy miesiecy jako wiersze, nazwy krajow jako kolumny.
-- na przecieciu liczba urodzonych osob w danym miesiacu/kraju
-- UWAGA! W zestawieniu uwzglednij takze
-- trenerow, zakladajac, ze kraj trenera jest taki sam jak kraj zawodnikow ktorych trenuje
select *
from
(select kraj, data, count(*) ile
from
(select kraj, FORMAT(data,'MMMM','pl-pl') data
from
(select distinct t.nazwisko_t, z.kraj, data_ur_t data
from zawodnicy z right join trenerzy t on t.id_trenera= z.id_trenera
union all
select nazwisko , kraj, data_ur from zawodnicy) t
union all
select null, FORMAT(DATEFROMPARTS(2000,id_zawodnika,1),'MMMM','pl-pl')
from zawodnicy where id_zawodnika<13) k
group by kraj, data) p
pivot (
sum(ile)
for kraj in ([pol],[ger],[usa],[fin],[aut],[nor])
) pvt
-- funkcje okienkowe
select imie, nazwisko, wzrost, kraj,
dense_RANK() over (order by wzrost desc )
from zawodnicy
-- wypisz zawodnikow, ktorych wzrost jest 4 co wielkosci
select * from
(select imie, nazwisko, wzrost, kraj,
dense_RANK() over (order by wzrost desc ) r
from zawodnicy) t
where r=4
select imie, nazwisko, wzrost,
NTILE(3) over (order by wzrost)
from zawodnicy
select imie, nazwisko, wzrost,
LAG(nazwisko,2) over (order by wzrost)
from zawodnicy
select imie, nazwisko, wzrost,
lead(nazwisko,2) over (order by wzrost)
from zawodnicy
select imie, nazwisko, wzrost,
SUM(wzrost) over (order by id_zawodnika rows between unbounded preceding and unbounded following)
from zawodnicy
select imie, nazwisko, wzrost,
SUM(wzrost) over (order by id_zawodnika rows between 2 preceding and 2 following)
from zawodnicy
select imie, nazwisko, wzrost,
SUM(wzrost) over (order by id_zawodnika rows between 2 preceding and current row)
from zawodnicy
select imie, nazwisko, wzrost,
SUM(wzrost) over (order by wzrost rows between unbounded preceding and current row)
from zawodnicy
select imie, nazwisko, wzrost,
SUM(wzrost) over (order by wzrost rows between current row and unbounded following)
from zawodnicy
-- wypisz wszystkich zawodnikow , sortujac ich po wzroscie
-- dla kazdego napisz o ile jest on wyzszy od zawodnika od niego bezposrednio nizszego
select imie, nazwisko, wzrost,
DENSE_RANK() over (order by wzrost) r,
wzrost-LAG(wzrost,1) over (order by wzrost) p
from zawodnicy
select imie, nazwisko, wzrost, kraj,
max(wzrost) over (partition by kraj order by wzrost )
from zawodnicy
-- dla kazdego kraju oddzielnie wypisz numery
--porzadkowe zawodnikow od 1 do x gdzie x to liczba zawodnikow
-- wzgledem sortowania po wzroscie
select kraj, nazwisko,wzrost,
DENSE_RANK() over (partition by kraj order by wzrost) r
from zawodnicy
--dla kazdego zawodnika wypisz imie i nazwsiko zawodnika najwyzszego z jego kraju
select t.*, z.nazwisko
from
(select imie, nazwisko,kraj, wzrost,
MAX(wzrost) over (partition by kraj) mw
from zawodnicy) t
left join zawodnicy z on t.mw = z.wzrost and t.kraj = z.kraj
select imie, nazwisko,kraj, wzrost,
first_value(nazwisko) over (partition by kraj order by wzrost desc) mw
from zawodnicy
-- dla kazdego zawodnika napisz id_zawodnika, który jest jego szefem
-- szefem zawodnika X jest zawodnik Y z tego samego kraju
--taki ,ze Y jest bezposrednio wyzszy od X
select *, coalesce(r,id_zawodnika)
from
(select id_zawodnika,imie, nazwisko, kraj, wzrost,
LAG(id_zawodnika,1) over (partition by kraj order by wzrost desc) r
from zawodnicy) t
-- wypisz tylko nazwiska zawodnikow najwyzszych z kraju
select distinct kraj, n
from
(select kraj,nazwisko, wzrost,
first_value(nazwisko) over (partition by kraj order by wzrost desc ) n
from zawodnicy) t
select kraj,nazwisko, wzrost,
last_value(nazwisko) over (partition by kraj order by wzrost desc
) n
from zawodnicy
<hr />
create table zawodnicy2
(
id int primary key identity(1,1),
imie varchar(255),
nazwisko varchar(255),
kraj varchar(3),
wzrost int,
waga decimal(5,2),
data datetime2
)
drop table zawodnicy2
select * from zawodnicy2
insert into zawodnicy2
values ('jan','kowalski','pol',180,56.65,'20190222')
select * from zawodnicy2
insert into zawodnicy2 (nazwisko,imie)
values ('nowak','adam')
select * from zawodnicy2
insert into zawodnicy2
values ('jan1','kowalski1','pol',180,56.65,'20190222')
insert into zawodnicy2
values ('jan2','kowalski2','pol',180,56.65,'20190222')
insert into zawodnicy2 values
('jan3','kowalski2','pol',180,56.65,'20190222'),
('jan4','kowalski2','pol',180,56.65,'20190222'),
('jan5','kowalski2','pol',180,56.65,'20190222')
select * from zawodnicy2
delete zawodnicy2
truncate table zawodnicy2
delete zawodnicy2 where imie = 'adam'
select * from zawodnicy where ...
delete zawodnicy2 where id in (1,4,6)
-- select --> insert -- przeniesc dane z jednej tablki do drugiej
select * from zawodnicy2
insert into zawodnicy2
select imie,nazwisko,kraj,wzrost,waga,data_ur
from zawodnicy
where kraj='pol'
select * from zawodnicy2
update zawodnicy2 set imie='x' where waga<60
update zawodnicy2 set imie=UPPER(imie) where waga>60
select * from zawodnicy2
-- 1) Stworz nową tabelę "Miasta2". Przenies dane z tabeli Miasta do miasta2
-- tylko te miasta, któych długość nazwy jest większa od 6
-- i usuń znaki "" z początku i końca każdego rekordu w tabeli Miasta2
select * from miasta
create table Miasta2 (id int primary key identity(1,1), nazwa varchar(255))
insert into Miasta2
select nazwa_miasta from miasta
select * from Miasta2
update Miasta2 set nazwa = SUBSTRING(nazwa,2,len(nazwa)-2)
select * from zawodnicy2
update zawodnicy2 set nazwisko = 'a', kraj='b' where id=1
-- optymalizacja struktury tabel
select * from zawodnicy
--słownikowanie danych
-- co zawiera prosty słownik typu kraje ? odp : klucz , wartosc
-- gdybyśmy chcieli stworzyć uniwersalny słownik
-- klucz, wartosc, rodzaj
-- potrzebujemy nowej tabeli: opis słownika : klucz i rodzaj
/* przykładwo słownik:
1 pol 1
2 ger 1
3 fin 1
4 czerwony 2
5 niebieski 2
6 warszawa 3
7 krakow 3
*/
/* opisSlownika
1 kraje
2 kolory
3 miasta
-- 1) usun dane z zawodnicy2
-- 2) przekopiuj dane z zawodnicy do zawodnicy2
-- 3) stworz tabele slownik i slownikOpis
-- 4) dodaj do slownikOpis wpis: Kraje
-- 5) dodaj do tabeli slownik wszystkie uniklne kraje
-- 6) wykonaj update na tabeli zawodnicy2 zamienieniajc kraje na odpowiednie identyfikatory
-- 7) wykonaj testowego select, który z join polaczy zawodnicy2 i kraje
*/
truncate table zawodnicy2
select *from zawodnicy2
INSERT INTO zawodnicy2
select imie,nazwisko,kraj,wzrost,waga,data_ur from zawodnicy
select * from zawodnicy2
create table slownik(id int primary key identity(1,1), nazwa varchar(255), typ int)
create table slownikOpis(id int primary key identity(1,1), nazwa varchar(255))
insert into slownik
select distinct kraj,1 from zawodnicy
insert into slownikOpis values ('kraje')
select * from slownik
select * from slownikOpis
update zawodnicy2 set kraj=s.id
from slownik s where zawodnicy2.kraj=s.nazwa
select * from zawodnicy2
SELECT imie, nazwisko, s.nazwa from zawodnicy2 z left join slownik s on s.id = z.kraj
select * from zawodnicy2
alter table zawodnicy2
add data2 date
update zawodnicy2 set data2 = data
alter table zawodnicy2
drop column data
alter table zawodnicy2 add data date
EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'
EXEC sp_RENAME 'zawodnicy2.data2' , 'data', 'COLUMN'
select * from zawodnicy2
<hr />
create view polacy as
select imie, nazwisko, waga/power(wzrost/100.0,2) bim
from zawodnicy
where kraj = 'pol'
go
select * from polacy
with polacy2(imie,nazwisko, bmi) as
(select imie, nazwisko, waga/power(wzrost/100.0,2) bim
from zawodnicy
where kraj = 'pol')
select * from polacy2
where bmi > 20
declare @kraj varchar(3)
set @kraj='pol'
select imie, nazwisko, @kraj
from zawodnicy
where kraj = @kraj
select * from zawodnicy
declare @tekst varchar(max)
set @tekst = '';
select @tekst=@tekst+ ' '+ imie from zawodnicy
select @tekst
-- zadanie: wyswietl wszystkie nazwy krajow w jednej komorce
declare @kraje varchar(max)
set @kraje= ''
select @kraje= @kraje+' '+kraj from
(select distinct kraj from zawodnicy) t
select @kraje
select imie, nazwisko , waga+wzrost
from zawodnicy
go
create function dodawanie(@liczbaA int, @liczbaB int) returns int
as
begin
declare @wynik int
set @wynik = @liczbaA+@liczbaB
return @wynik
end
go
select imie, nazwisko, dbo.dodawanie(wzrost,waga)
from zawodnicy
-- stwórz własną funkcję liczącą BMI
go
create function bmi(@waga decimal(5,2), @wzrost decimal(5,2)) returns decimal(5,2)
as
begin
declare @wynik decimal(5,2)
set @wynik = @waga/power(@wzrost/100.0,2)
return @wynik
end
go
select imie, nazwisko, dbo.bmi(waga,wzrost) bmi
from zawodnicy
drop function dbo.dodawanie
-- napisz funkcję ,która na wejściu otrzymuje id zawodnika
-- a na wyjściu zwraca nazwisko trenera tego zawodnika
go
create function jegoTener(@id int) returns varchar(255)
as
begin
declare @nazwisko varchar(255)
select @nazwisko= t.nazwisko_t
from zawodnicy z join trenerzy t on t.id_trenera = z.id_trenera
where id_zawodnika= @id
return @nazwisko
end
go
select imie, nazwisko, dbo.jegoTener(id_zawodnika) trener from zawodnicy
-- napisz funkcje , ktora na wejsciu otrzymuje idTrenera i zwraca
-- liste nazwisk zawodnikow oddzielonych przecinkiem
go
create function jegoZawodnicy(@id int) returns varchar(255)
as
begin
declare @nazwiska varchar(255)
set @nazwiska=''
select @nazwiska = @nazwiska + ', ' + z.nazwisko
from zawodnicy z join trenerzy t on z.id_trenera= t.id_trenera
where t.id_trenera = @id
return @nazwiska
end
go
select imie_t,nazwisko_t, dbo.jegoZawodnicy(id_trenera) zawodnicy from trenerzy