Test2 - Vyber dat z vice tabulek

5. ledna 2012 v 0:25
--Jméno, příjmení zaměstnance a jeho oddělení, seřazeno podle oddělení
select Jmeno, Prijmeni,Nazev
from Zamestnanci Z join Oddeleni O
on Z.ID_Oddeleni=O.ID_Oddeleni
order by O.Nazev;
--Jeden sloupec Jmeno prijmeni - oddeleni
select Jmeno + ' ' + Prijmeni + ' - ' + Nazev
from Zamestnanci Z join Oddeleni O
on Z.ID_Oddeleni=O.ID_Oddeleni
order by O.Nazev;
--Zaměstnanci z technického v jednom sloupci ve tvaru Jmeno Prijmeni - technicke
select Jmeno + ' ' + Prijmeni + ' - ' + Nazev
from Zamestnanci Z join Oddeleni O
on Z.ID_Oddeleni=O.ID_Oddeleni
where O.Nazev = 'Technické';
--Zaměstnanci co byli na školení PHP
select Jmeno, Prijmeni,Nazev
from Zamestnanci Z join ZamestnanciSkoleni ZS
on Z.ID_Zamestnance=ZS.ID_Zamestnance
join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
where S.Nazev like '%PHP%';
--Produkty na objednávce č.500
select Nazev
from Objednavky O join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where O.ID_objednavky='500';
--Kdo zpracoval objednávku č. 500 a jak dlouho trvala
select Jmeno,Prijmeni,ID_objednavky,DATEDIFF(dd,datum_prijeti,datum_odeslani)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
where O.ID_objednavky = '500';
--Kdo objednal objednávku č.500 (zákazník)
select Jmeno,Prijmeni,ID_objednavky
from Zakaznik Z join Objednavky O
on Z.ID_zakaznika=O.ID_zakaznika
where O.ID_objednavky = '500';
--Kteří zaměstnanci nezpracovali objednávku do 7 dnů
select Jmeno,Prijmeni,DATEDIFF(dd,datum_prijeti,datum_odeslani)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
where DATEDIFF(dd,datum_prijeti,datum_odeslani) >= 7;
--Kdo zadal a kdo zpracoval objednávku č.500
select Z.Jmeno + ' ' + Z.Prijmeni as 'Zaměstnanec', rtrim(ZK.Jmeno) + ' ' + rtrim(ZK.Prijmeni) as 'Zákazník'
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika
where O.ID_objednavky='500';
--Na jakých školeních byli zaměstnanci z technického oddělení
select Jmeno,Prijmeni,O.Nazev,S.Nazev
from Oddeleni O join Zamestnanci Z
on O.ID_Oddeleni=Z.ID_Oddeleni
join ZamestnanciSkoleni ZS
on Z.ID_Zamestnance=ZS.ID_Zamestnance
join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
where O.Nazev='Technické';
--Seznam pracovníků a počet objednávek,které odeslali Chovancové, seřazeno podle počtu
select Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.jmeno) + ' ' + rtrim(ZK.prijmeni) as Zakaznik,COUNT(id_objednavky)
from Zamestnanci Z left join Objednavky O
on O.ID_zamestnance=Z.ID_Zamestnance
join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika
where RTRIM(ZK.prijmeni)='Chovancová'
group by Z.Jmeno,Z.Prijmeni,ZK.jmeno,ZK.prijmeni
order by COUNT(id_objednavky) desc;
--Sestava vyřizování objednávek-číslo objednávky,jméno příjmení zák,data,jméno zam.
--seřazeno podle data odeslání,příjmení zam,čísla objednávky
select O.id_objednavky, rtrim(ZK.Jmeno) + ' ' + rtrim(ZK.Prijmeni) as Zakaznik, convert(varchar,datum_prijeti,104) as Prijato,convert(varchar,datum_odeslani,104) as Odeslano,Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika
order by datum_odeslani desc,Z.Prijmeni asc,O.ID_objednavky desc;
--Objednávky, které odesílala Jana Nová
select Jmeno,Prijmeni,id_objednavky,convert(varchar,datum_prijeti,104)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
where Z.Jmeno='Jana' and Z.Prijmeni='Nová'
order by datum_prijeti desc;
--Které objednávky objednal Jan Jahoda
select Jmeno,Prijmeni,id_Objednavky,convert(varchar,datum_prijeti,104)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
where ZK.jmeno='Jan' and ZK.prijmeni='Jahoda';
--Nevim :-D
select distinct Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.Jmeno) + ' ' + rtrim(ZK.Prijmeni) as Zakaznik
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
join Zakaznik ZK
on O.ID_zakaznika = ZK.ID_zakaznika;
--Které objednávky odeslala Anna Novotná Karle Erbenové
select Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.Jmeno) + ' ' + rtrim(ZK.Prijmeni) as Zakaznik,O.id_objednavky,convert(varchar,O.datum_prijeti,104)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika
where Z.Jmeno='Jana' and Z.Prijmeni='Nová' and ZK.jmeno='Anna' and ZK.prijmeni='Novotná';
--Vyberte školení na kterých byli zaměstnanci z technického, každé školení pouze jednou
select distinct O.Nazev, S.Nazev
from Oddeleni O join Zamestnanci Z
on O.ID_Oddeleni=Z.ID_Oddeleni
join ZamestnanciSkoleni ZS
on Z.ID_Zamestnance=ZS.ID_Zamestnance
join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
where O.Nazev='Technické';
--Které produkty si objednal Jan Jahoda - pouze názvy a jen jednou
select distinct P.Nazev
from Zakaznik ZK join Objednavky o
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where ZK.jmeno='Jan' and ZK.prijmeni='Jahoda';
--Zobrazte názvy oddělení zaměstnanců Chocholouš, Pečlivá a Smutná
select Jmeno,Prijmeni, O.Nazev
from Oddeleni O join Zamestnanci Z
on O.ID_Oddeleni=Z.ID_Oddeleni
where Z.Prijmeni = 'Chocholouš' or Z.Prijmeni ='Pečlivá'or Z.Prijmeni ='Smutná';
--Kdo si objednal Obal na 4CD, CD 8cm Verbatim, Diskety Verbatim 10ks
select ZK.Jmeno, ZK.Prijmeni, P.Nazev
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where P.Nazev='Obal na 4CD' or P.Nazev='CD 8cm Verbatim' or P.Nazev='Diskety Verbatim 10ks';
--Kolik stály jednotlivé objednávky od Jahody
select O.ID_objednavky, convert(varchar,datum_prijeti,104),cena
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where jmeno='Jan' and prijmeni='Jahoda';
--Zobrazte zboží které si nikdo neobjednal
select ID_objednavky,nazev
from objednavka_detail od right join Produkty p
on od.Id_produkt=p.id_produkt
where ID_objednavky is null;
--Zobrazte názvy pracovišť kde nikdo nepracuje
select O.Id_oddeleni,Nazev
from Oddeleni O left join Zamestnanci Z
on O.ID_Oddeleni=Z.ID_Oddeleni
where ID_Zamestnance is null;
--Na kolika školeních nikdo nebyl
select count(Nazev)
from Skoleni S left join ZamestnanciSkoleni ZS
on S.ID_Skoleni=ZS.ID_Skoleni
where ZS.ID_Skoleni is null;
--Na kterých školeních nikdo nebyl
select Nazev
from Skoleni S left join ZamestnanciSkoleni ZS
on S.ID_Skoleni=ZS.ID_Skoleni
where ZS.ID_Skoleni is null
--Kdo ještě nebyl na školení
select Jmeno,Prijmeni
from ZamestnanciSkoleni ZS right join Zamestnanci Z
on ZS.ID_Zamestnance = Z.ID_Zamestnance
where ZS.ID_Skoleni is null;
--Vypište seznam pracovníků a počet objednávek, které odeslali zákaznici Chovancové (jméno pracovníka, počet odeslaných objednávek). Zahrňte i pracovníky, kteří neodeslali žádné objednávky.
select Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.jmeno) + ' ' + rtrim(ZK.prijmeni) as Zakaznik
from Zamestnanci Z left join Objednavky O
on O.ID_zamestnance=Z.ID_Zamestnance
left join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika
where RTRIM(ZK.prijmeni)='Chovancová';
--Kdo nevytvořil (Zaměstnanec) ani jednu objednávku?
select Jmeno,Prijmeni
from Zamestnanci Z left join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
where O.ID_Zamestnance is null;
--Kdo si nic neobjednal
select Jmeno,prijmeni
from Zakaznik ZK left join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
where O.ID_zakaznika is null;
--Kdo si objednal kolik objednávek včetně nulových, seřadit podle počtu
select Jmeno,prijmeni,COUNT(ID_objednavky)
from Zakaznik ZK left join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
group by jmeno,prijmeni
order by COUNT(ID_objednavky)desc;
--Kteří pracovníci odeslali objednávky kterému zákazníkovi? Zahrňte i pracovníky, kteří nic neodeslali!
select Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.jmeno) + ' ' + rtrim(ZK.prijmeni) as Zakaznik
from Zamestnanci Z left join Objednavky O
on O.ID_zamestnance=Z.ID_Zamestnance
left join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika;
Počet komentářů: 1
 

Test2 - Agregacni fce nad vice tabulkami

5. ledna 2012 v 0:25
--Pro každé oddělení průměrný plat
select Nazev, AVG(Plat)
from Oddeleni O join Zamestnanci Z
on O.ID_Oddeleni=Z.ID_Oddeleni
group by Nazev;
--Kolik zaměstnanců bylo na jednotlivých školení
select S.Nazev, COUNT(ID_Zamestnance)
from ZamestnanciSkoleni ZS join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
group by S.Nazev;
--Kolik hodin strávila na školení Kamila Koláčková
select Jmeno,Prijmeni,sum(Pocet_hodin)
from Zamestnanci Z join ZamestnanciSkoleni ZS
on Z.ID_Zamestnance=ZS.ID_Zamestnance
join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
where Jmeno = 'Kamila' and Prijmeni = 'Koláčková'
group by Jmeno,Prijmeni;
--Kolik kusů produktu Box-peněženka na 24CD si objednali jednotlivý zákazníci podle počtu a jména a příjmení A-Z
select Jmeno,Prijmeni,SUM(mnozstvi)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where P.Nazev='Box-peněženka na 24CD'
group by Jmeno,Prijmeni
order by SUM(mnozstvi) desc, 2;
--Kolik peněz utratili jednotlivý zákazníci
select Jmeno,Prijmeni,sum(cena*mnozstvi)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
group by jmeno,prijmeni;
--Počet zaměstnanců s platem nad 9000 v každém oddělení podle počtu sestupně
select Nazev,COUNT(ID_Zamestnance)
from Oddeleni O join Zamestnanci Z
on O.ID_Oddeleni=Z.ID_Oddeleni
where Plat>9000
group by Nazev
order by COUNT(ID_Zamestnance)desc;
--Kolik zaměstnanců bylo na školení PHP
select COUNT(ID_Zamestnance)
from ZamestnanciSkoleni ZS join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
where Nazev like '%PHP%';
--Kolik zaměstnanců nastoupilo v kterém roce
select YEAR(datum_nastupu),COUNT(ID_Zamestnance)
from Zamestnanci
group by YEAR(datum_nastupu)
order by 1 desc;
--Kolik objednávek odeslala Anna Novotná
select Jmeno,Prijmeni,COUNT(ID_Objednavky)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
where Jmeno='Anna' and Prijmeni='Novotná'
group by Jmeno,Prijmeni;
--Jaká je celková cena za objednávku 500
select sum(mnozstvi*cena)
from objednavka_detail OD join Produkty P
on OD.id_produkt=P.Id_produkt
where ID_objednavky=500;
--Kteří zaměstnanci odeslali víc než 120 objednávek
select Jmeno,Prijmeni,COUNT(ID_objednavky)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
group by Jmeno,Prijmeni
having COUNT(ID_objednavky)>120;
--Jaká je průměrná cena za objednávky od Jahody
select Jmeno,Prijmeni,AVG(cena)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where jmeno='Jan' and prijmeni='Jahoda'
group by jmeno,prijmeni;
--Nejvyšší cena za objednávky od Jahody
select Jmeno,Prijmeni,max(cena)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
where jmeno='Jan' and prijmeni='Jahoda'
group by jmeno,prijmeni;
--Kolik objednávek si objednal Jahoda
select Jmeno,Prijmeni,COUNT(id_objednavky)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
where jmeno='Jan' and prijmeni='Jahoda'
group by jmeno,prijmeni;
--Kolik vyplatíme na mzdách, pokud za každou odeslanou objednávku je 1500
select count(datum_odeslani) * 1500
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance;
--Kolik objednávek odeslali jednotlivý zaměstnanci
select Jmeno,Prijmeni,COUNT(datum_odeslani)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
group by Jmeno,Prijmeni;
--Kolik objednávek odeslali jednotlivý zaměstnanci jednotlivých zákazníkům
select Z.Jmeno + ' ' +Z.Prijmeni, rtrim(Zk.jmeno) + ' ' +rtrim(ZK.prijmeni),count(datum_odeslani)
from Zamestnanci Z join Objednavky O
on Z.ID_Zamestnance=O.ID_zamestnance
join Zakaznik ZK
on O.ID_zakaznika=ZK.ID_zakaznika
group by Z.Jmeno,Z.Prijmeni,rtrim(Zk.jmeno),rtrim(ZK.prijmeni);
--Jaká je cena jednotlivých objednávek
select O.ID_objednavky, SUM(mnozstvi * cena)
from Objednavky O join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
group by O.ID_objednavky;
--Jaká je cena jednotlivých objednávek (vyšší než 40 000)
select O.ID_objednavky, SUM(mnozstvi * cena)
from Objednavky O join objednavka_detail OD
on O.ID_objednavky=OD.ID_objednavky
join Produkty P
on OD.id_produkt=P.Id_produkt
group by O.ID_objednavky
having SUM(mnozstvi * cena)> 40000;
Počet komentářů: 1

Test1- zakladni funkce

5. ledna 2012 v 0:23
--Jméno,příjmení,plat
select Jmeno + ' ' + Prijmeni + ' ' + cast(Plat as varchar) + ' Kč'
from Zamestnanci;
--Zobrazte zaměstnance po vzoru P.Novák - Praha, 18100
select SUBSTRING(upper(Jmeno),1,1) + '.' + UPPER(Prijmeni) + ' - ' + Mesto +', ' + CAST(Plat as varchar)
from Zamestnanci;
--Seřaďte zákazníky podle délky příjmení
select jmeno,prijmeni
from Zakaznik
order by LEN(Prijmeni) desc;
--Vytvořte ceník, kde bude uveden název produktu a cena spolu s měnovou jednotkou - Bronzo,55Kč
select Nazev + ', '+ CAST(cena as varchar) + ' Kč'
from Produkty;
--Zobrazte jméno,příjmení a rodné číslo zákazníků ve tvaru XXXXXX/XXXX
select jmeno,prijmeni,substring(rodnecislo,1,6) + '/' + SUBSTRING(rodnecislo,7,LEN(rodnecislo))
from Zakaznik;
--Zobrazte jméno,příjmení,psč zákazníků ve tvaru XXX XX
select jmeno,prijmeni,substring(psc,1,3) + ' ' + substring(psc,4,LEN(psc))
from Zakaznik;
--Zobrazte jméno,příjmení a telefon zákazníků ve tvaru +420 XXX XXX XXX
select jmeno,prijmeni,'+420' + ' ' + SUBSTRING(telefon,1,3) + ' ' + SUBSTRING(telefon,4,3) + ' ' + SUBSTRING(telefon,5,3)
from Zakaznik;
--Jaký by byl plat zaměstnance v eurech
select Jmeno + ' ' + Prijmeni as Jméno,Plat as 'Plat v Kč', cast(Plat * 23 as varchar) + ' €' as 'Plat v €'
from Zamestnanci;

go

-- sin 1 na druhou plus cos 1 na druhou
select POWER(sin(1),2) + POWER(cos(1),2);
--Zobrazte jméno,příjmení a nový sloupec příspěvek na Vánoce - za každé dítě + 500
select Jmeno,Prijmeni,Pocet_deti, cast(Pocet_deti *500 as varchar) as 'Příspěvek na Vánoce'
from Zamestnanci;
--Zvýšení zaměstnaneckého platu o 3%
select Jmeno,Prijmeni,Plat, CAST(plat * 1.03 as varchar) as 'Zvýšený plat'
from Zamestnanci;
--Dvouprocentní zdražení produktů
select Nazev,Cena as 'Cena před zvýšením', CAST(cena * 1.02 as varchar) as 'Cena po zvýšení', CAST((cena *1.02)-Cena as varchar) as Zvýšení
from Produkty;
-- Zobrazte jméno, příjmení, plat, plat navýšený o bonus ve výši 25%procent platu zaoukrouhlený na tisíce
select Jmeno,Prijmeni,Plat, round(CAST((Plat * 0.25)+plat as varchar),3)
from Zamestnanci;
--Kteží zaměstnanci by měli po navýšení platu o 20% vyšší plat než 21000
select Jmeno,Prijmeni,Plat,round(CAST(Plat *1.02 as varchar),4)
from Zamestnanci
where Plat *1.02 >=21000 ;
--10/7
select 10/7;
--125.828 zaokrouhlit dolů
select ROUND(125.828,0);

go

--Zobrazte dnešní datum
select 'Dnes je: ' + cast(DATEPART(Dd,getdate()) as varchar) + '.' + cast(DATEPART(mm,getdate()) as varchar) + '.' + cast(DATEPART(yy,getdate()) as varchar);
--Zobrazte jen objednávky od 15.července 2009 do 20.července 2009

--Zobrazte kolik je vám let
select DATEDIFF(yy,'11.11.1989',getdate());
--Za každý odpracovaný celý měsíc dostane zaměstnanec bonus 50Kč
select Jmeno,Prijmeni,Plat,DATEDIFF(MM,datum_nastupu,GETDATE())*50 as Bonus
from Zamestnanci;
--Za každný celý odpracovaný rok dostane zaměstnanec 500Kč
select Jmeno,Prijmeni,Plat,DATEDIFF(yy,datum_nastupu,GETDATE())*500,datum_nastupu,GETDATE()
from Zamestnanci;
--Zobrazte věk zaměstnanců, seřaďte je podle pohlaví a podle věku
select jmeno,prijmeni,pohlavi,Datum_narozeni
from Zamestnanci
order by Pohlavi,SUBSTRING(cast(Datum_narozeni as varchar),1,4) desc;
--Zobrazte zaměstnance, kteří se narodili v roce 1967 nebo 1959
select Jmeno,Prijmeni,Datum_narozeni
from Zamestnanci
where CONVERT(varchar,Datum_narozeni,104) like '%1967' or CONVERT(varchar,Datum_narozeni,104) like '%1957';
--Vypište seznam objednávek ve tvaru dd.mm.rrrr
select ID_objednavky,convert(varchar,datum_prijeti,104)
from Objednavky;
--Kolik dnů trvalo vyřídit jednotlivé objednávky
select ID_objednavky,datum_prijeti,datum_odeslani,DATEDIFF(dd,datum_prijeti,datum_odeslani)
from Objednavky;
--Které objednávky se nepodařilo vyřídit následujíci den - který pracovník
select ID_objednavky,ID_zamestnance
from Objednavky
where DATEDIFF(dd,datum_prijeti,datum_odeslani)>1;
--Které objednávky odeslal zaměstnanec s číslem 12
select ID_objednavky,datum_prijeti,datum_odeslani
from Objednavky
where ID_zamestnance='12';
--Které objednávky si objednal zákazník s číslem 1020
select ID_zakaznika,ID_objednavky,datum_odeslani
from Objednavky
where ID_zakaznika='1020';
--Zobrazte všechny zaměstnance starší než 30 let
select Jmeno,Prijmeni,Datum_narozeni
from Zamestnanci
where DATEDIFF(yy,datum_narozeni,getdate())>30;
--Vyberte muže z Ostravy starší než 30 a ženy mladší než 50
select Jmeno,Prijmeni,Datum_narozeni
from Zamestnanci
where (Pohlavi='m' and DATEDIFF(yy,datum_narozeni,getdate())>30) or (Pohlavi='ž' and DATEDIFF(yy,datum_narozeni,getdate())<50)
order by Pohlavi;
--Zobrazte seznam objednávek seřazených podle data
select ID_objednavky,ID_zakaznika,datum_prijeti
from Objednavky
order by DATEDIFF(dd,datum_Prijeti,getdate())asc;
--Zobrazte jméno a příjmení zákazníka a délku jeho jména a příjmení
select jmeno,LEN(jmeno),prijmeni,LEN(prijmeni)
from Zakaznik;
--Zobrazte jméno,příjmení,věk zaměstnanců,seřaďte sestupně
select Jmeno,Prijmeni,Datum_narozeni
from Zamestnanci
order by DATEDIFF(yy,datum_narozeni,getdate()) desc;
--Zobrazte id zákazníků,kteří si něco objednali v listopadu
select ID_zakaznika,datum_prijeti
from Objednavky
where datepart(mm,datum_prijeti)='11';

go

--Kolik zaměstnanců má titul a kolik ne - pomocí union
select Jmeno from Zamestnanci
union
select Titul from Zamestnanci;
--Průměrný plat mužů a žen a jejich počet
SELECT 'Muzi',
Round(AVG(plat), 0) AS "Prumerny plat",
COUNT(*) AS pocet
FROM zamestnanci
WHERE pohlavi LIKE'm'
UNION
SELECT 'Zeny',
Round(AVG(plat), 0) AS "Prumerny plat",
COUNT(*) AS pocet
FROM zamestnanci
WHERE pohlavi LIKE'ž';
--Neplatná rodná čísla zákazníků
select jmeno,prijmeni,rodneCislo
from Zakaznik
where CAST(rodnecislo as bigint)%11<>0;
--Slovní zhodnocení rodného čísla
select jmeno,prijmeni,rodneCislo,case
when CAST(rodnecislo as bigint)%11<>0 then 'neplatné'
else 'platné'
end as 'Platnost'
from Zakaznik
order by Platnost;
--Zaměstnanci a zákazníci společně podle příjmení
select Jmeno,Prijmeni from Zamestnanci
union
select jmeno,prijmeni from Zakaznik
order by Prijmeni;
Počet komentářů: 1

Test1 - jednoduchy select

5. ledna 2012 v 0:22
--Vypište jméno a příjmení a plat zákazníků abecedně podle příjmení
select jmeno,prijmeni
from Zakaznik
order by prijmeni asc;
--Vypište číslo a název školení setříděný podle názvu školení sestupně
select ID_Skoleni,Nazev
from Skoleni
order by Nazev desc;
--Jak se jmenují a kdy nastoupili zaměstnanci z oddělení 5
Select Jmeno,Prijmeni,datum_nastupu,ID_Oddeleni
from Zamestnanci
where ID_Oddeleni = 2;
--Jaké má osobní číslo zaměstnanec Dana Nová
select Jmeno, ID_Zamestnance
from Zamestnanci
where Jmeno ='Dan Nový';
--Kdy nastoupil zaměstnanec č.5
select Jmeno,Prijmeni,datum_nastupu,ID_Zamestnance
from Zamestnanci
where ID_Zamestnance in( 5);
--Kdy vzniklo oddělení č.4
select ID_Oddeleni,Nazev,Datum_zalozeni
from Oddeleni
where ID_Oddeleni in (4);
-- Jak se jmenují zaměstnanci z oddělení 2
select Jmeno,Prijmeni,ID_Oddeleni
from Zamestnanci
where ID_Oddeleni = 2;
--Ze kterých měst jsou naši zákazníci
select distinct mesto
from Zakaznik;
--Zobrazte jména zaměstnanců, zbavte se duplicit, seřaďte podle abecedy
select distinct Jmeno
from Zamestnanci
order by Jmeno ASc;
--Vypište pouze názvy výrobků seřazené podle ceny sestupně
select Nazev
from Produkty
order by Cena desc;
--Seřaďte zaměstnance nejprve podle pohlaví, poté podle příjmení
select Jmeno,Prijmeni,Pohlavi
from Zamestnanci
order by Pohlavi,Prijmeni;
--Vypište jméno a příjmení zákazníků seřazených abecedně podle příjmení,pak podle jména
select jmeno,prijmeni
from Zakaznik
order by prijmeni asc, jmeno asc;
--Vyberte pouze ženy z Prahy
select Jmeno,Prijmeni,Mesto
from Zamestnanci
where Pohlavi='ž' and Mesto like ('%Praha%');
--Vyberte zaměstnance z Prahy a Brna
select Jmeno,Prijmeni,Mesto
from Zamestnanci
where Mesto ='Praha' or Mesto= 'Brno';
--Vyberte zaměstnance;všechny muže z Brna a všechny ženy z Prahy
select Jmeno,Prijmeni,Mesto
from Zamestnanci
where (Pohlavi='m' and Mesto='Brno') or (Pohlavi='ž' and Mesto='Praha');

go

--Vypište zaměstnance, kteří nemají titul, výběr seřaďte podle platu sestupně
select Jmeno,Prijmeni,Titul,Plat
from Zamestnanci
where Titul is null
order by Plat desc;
--Zobrazte muže,kteří se zajímají o tenis
select Jmeno,Prijmeni,Zajmy
from Zamestnanci
where Zajmy like ('%Tenis%');
--Vyberte příjmení končící na -á
select Prijmeni
from Zamestnanci
where Prijmeni like ('%á');
--Vyberte příjmení, které obsahuje ocho
select Prijmeni
from Zamestnanci
where Prijmeni like ('%ocho%');
--Vyberte zaměstnance, kteří mají příjmení z pěti znaků
select Prijmeni
from Zamestnanci
where Prijmeni like ('_____');
--Vyberte příjmení zaměstnanců od N
select Prijmeni
from Zamestnanci
where Prijmeni like ('N%');
--Jaký mají název a kolika hodinová jsou školení s č.3 a č.4
select Nazev,Pocet_hodin
from Skoleni
where ID_Skoleni in(3,4);
--Zobrazte jméno, příjmení, plat zaměstnanců, jejichž plat je 15000 až 20000 včetně; sestupně
select Jmeno,Prijmeni,Plat
from Zamestnanci
where Plat between 15000 and 20000
order by plat desc;
--Která školení trvají více než 5 hodin a méně než 10 včetně
select Nazev, Pocet_hodin
from Skoleni
where Pocet_hodin>=5 and Pocet_hodin<=10;
--Zobrazte výrobky, které stojí mezi 100 a 150Kč
select Nazev,Cena
from Produkty
where Cena between 100 and 150;
--Jaké je číslo zaměstnance,který byl na školení č.5,6,7
select ID_Zamestnance,ID_Skoleni
from ZamestnanciSkoleni
where ID_Skoleni in(1);
--moje nadstavba - jméno zaměstnanců
select Jmeno,Prijmeni
from Zamestnanci
where ID_Zamestnance in(1,2,3,9,10,20);
--Zobrazte jméno a příjmení těch zaměstnanců, jejichž jméno je Helena, Hana nebo Zuzana
select Jmeno,Prijmeni
from Zamestnanci
where Jmeno = 'Helena' or Jmeno='Hana' or Jmeno='Zuzana';
--Zobrazte pracovníky, jejichž příjmení nezačíná na písmena A,B nebo C
select Jmeno,Prijmeni
from Zamestnanci
where Prijmeni not like('A%') and Prijmeni not like('B%') and Prijmeni not like('C%');
--Vyberte zaměstnance, kteří nejsou z Prahy ani Brna
select Jmeno,Prijmeni,Mesto
from Zamestnanci
where Mesto not in('Praha','Brno');
--Zobrazte prvních šest zaměstnanců podle výše platu
select top 6 Jmeno,Prijmeni,Plat
from Zamestnanci
order by Plat desc;
--Zobrazte prvních 7 produktů podle ceny
select top 7 Nazev,Cena
from Produkty
order by Cena desc;
--Náhodně vyberte jednoho zákazníka
select top 1 jmeno,prijmeni
from Zakaznik
order by NEWID();
--Náhodně vyberte 3 produkty
select top 3 *
from Produkty
order by NEWID();
--Náhodně vyberte 3 zákazníky
select top 3 *
from Zakaznik
order by NEWID();
Přidat komentář

uhu

20. prosince 2011 v 15:03
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<H1></H1>
.
<?php
mysql_connect("195.113.78.236", "student", "student");
 mysql_select_db("student_cviceni");
 mysql_query("SET NAMES 'cp1250'");
 $dotaz = "SELECT";
echo($dotaz);
$vysledekDotazu = mysql_query($dotaz);
 $pocet_radku = mysql_num_rows($vysledekDotazu);
echo "<p>Pocet radku ve vysledku: $pocet_radku </p>";
echo"
<table border=1>
<tr>
<td></td> <td></td> <td></td> <td></td>
</tr>
";
for ($i = 0; $i < $pocet_radku; $i++) {
$row = mysql_fetch_array($vysledekDotazu);
echo "<tr >
 <td> $row[0] </td>
 <td>$row[1] </td>
 <td>$row[2] </td>
 <td>$row[3] </td>
 </tr>";
}
 ?>
 </table>
</BODY>
</HTML>
Počet komentářů: 1

hhh

20. prosince 2011 v 14:18

-- Pro zadané rodné číslo zobrazte jméno a příjmení zákazníka
select jmeno, Prijmeni
from Zakaznik
where rodneCislo = 9407174705
-- Pro zadané číslo objednávky zobrazte položky.
select nazev
from Produkty p join objednavka_detail od
on p.Id_produkt = od.id_produkt
where ID_objednavky = 2
-- Po zadání jména a příjmení zobrazte, kolik objednávek zákazník odeslal.
select COUNT(datum_odeslani)
from Zakaznik z join Objednavky o
on z.ID_zakaznika = o.ID_zakaznika
where jmeno = 'Jan' and prijmeni='Jahoda'
select * from Zakaznik
-- Po zadání části názvu produktu se zobrazí zákazníci, kteří si ho koupili.
select jmeno, prijmeni
from Zakaznik z join Objednavky o
on z.ID_zakaznika = o.ID_zakaznika
join objednavka_detail od
on o.ID_objednavky = od.ID_objednavky
join Produkty p on od.id_produkt = p.Id_produkt
where Nazev like '%Premium%'
select * from Produkty
-- Seznam klientů začínající nezadané písmeno.
select jmeno, prijmeni
from Zakaznik
where prijmeni like 'J%'
-- Zobrazte seznam produktů, uživatel bude moci zvolit podle jakého kritéria se seznam seřadí.
select *
from Produkty
order by Cena
-- Pro zadané číslo objednávky zobrazte její položky, kdo si zboží koupi, a kolik objednávka stála.
-- (Nápověda: pod sebou budou 3 selecty a jejich výstupy)
select p.Nazev
from Produkty p join objednavka_detail od
on p.Id_produkt = od.id_produkt
join Objednavky o on o.ID_objednavky = od.ID_objednavky
where o.ID_objednavky = 12
select z.jmeno, z.prijmeni
from Produkty p join objednavka_detail od
on p.Id_produkt = od.id_produkt
join Objednavky o on o.ID_objednavky = od.ID_objednavky
join Zakaznik z on z.ID_zakaznika = o.ID_zakaznika
where o.ID_objednavky = 12
select sum(mnozstvi*cena)
from Produkty p join objednavka_detail od
on p.Id_produkt = od.id_produkt
join Objednavky o on o.ID_objednavky = od.ID_objednavky
where o.ID_objednavky = 12
-- Počet zaměstnanců s platem menším než plat průměrný.
select COUNT(Id_zamestnance) as pocet
from zamestnanci
where plat < (select avg(plat)
from zamestnanci);
-- Ověřte platnost rodných čísel zákazníků: vypište všechny, kteří uvedli neplatné rodné číslo.
select jmeno, prijmeni
from Zakaznik
where CAST(rodnecislo as bigint)%11 <> 0
-- Názvy oddělení s více než 5 zaměstnanci.
select nazev, COUNT(ID_Zamestnance) as pocet
from Oddeleni o join Zamestnanci z on o.ID_Oddeleni = z.ID_Oddeleni
group by nazev
having COUNT(ID_Zamestnance) > 5
Přidat komentář

kkk

22. listopadu 2011 v 14:06
-- PROCEDURY
-- 1.Vytvorte proceduru,ktera do tabulky lide umozni vlozit hodnoty. Po vlozeni se vypise text "data vlozena".
drop procedure sh_lide
go
create procedure sh_lide (@clovek varchar (20))
as
declare @id int
begin
set @id =
(select MAX (id) from lide) +1
insert into sh_lide values (@id,@clovek,'Nová data')
Print 'Data vložena'
end;

-- 2.Napiste proceduru,ktera vypise datum od ktereho je ve firme zamestnan zamestnanec,ktery ve firme pracuje nejdele.
drop procedure sh_nastup
go
create procedure sh_nastup
as
declare
@den int,
@mesic int,
@rok int
begin
set @den = (select DAY(min(datum_nastupu)) from Zamestnanci)
set @mesic = (select MONTH(min(datum_nastupu))from Zamestnanci)
set @rok = (select YEAR(min(datum_nastupu)) from Zamestnanci)
print 'První zaměstnanec nastoupil ' + ltrim(@den)+'. '+ltrim(@mesic)+'. '+ltrim(@rok)+'.'
end
go
sh_nastup


-- 3.Zobrazi,kdo byl na nejvice skolenich.
drop procedure sh_skoleni
go
create procedure sh_skoleni
as
declare
@zamestnanec varchar (20)
begin
set @zamestnanec =
(select Jmeno+' '+Prijmeni from Zamestnanci z inner join ZamestnanciSkoleni zs on z.ID_Zamestnance=zs.ID_Zamestnance
group by Jmeno+' '+Prijmeni
having COUNT(zs.ID_Skoleni)=
(select MAX (pom.pocet) from
(select jmeno, prijmeni,COUNT(zs.id_skoleni) as pocet from Zamestnanci z inner join ZamestnanciSkoleni zs on zs.ID_Zamestnance = z.ID_Zamestnance
group by Jmeno, prijmeni )pom))
print @zamestnanec
end
go
sh_skoleni

-- je zde vice než jeden zaměstnanec tzn. jde o víceřádkovou proceduru.

-- 4.Kdo odeslal nejvice objednavek
-- pokud se udela nejvice jedna se o viceradkovou proceduru, udelame tedy nejmene
drop procedure sh_nejmene
go
create procedure sh_nejmene
as
declare
@zakaznik varchar (30)
begin
set @zakaznik=
(select RTRIM(zak.jmeno)+' '+zak.prijmeni from Zakaznik zak inner join Objednavky o on zak.ID_zakaznika=o.ID_zakaznika
group by RTRIM(zak.jmeno)+' '+zak.prijmeni
having COUNT(o.ID_objednavky)=
(select MIN(pom.pocet)from
(select zak.jmeno,zak.prijmeni, count (o.ID_objednavky) as pocet
from Zakaznik zak inner join Objednavky o on zak.ID_zakaznika=o.ID_zakaznika
group by jmeno,prijmeni)pom))
print @zakaznik
end
go
sh_nejmene

-- 5.Napiste proceduru,ktera zobrazi, kdo odeslal nejvice objednavek
-- stejny priklad jako 4.

-- 6.Napiste proceduru, ktera po zadani cisla objednavky zobrazi formatovane: Kdo si ji objednal,kolik stala, kdo ji zpracoval,kolik na ni bylo polozek.
drop procedure sh_obj
go
create procedure sh_obj (@cislo_obj int)
as
declare
@zakaznik varchar (30),
@cena int,
@zamestnanec varchar (30),
@ks int
begin
set @zakaznik=
(select RTRIM(zak.jmeno)+' '+RTRIM(zak.prijmeni)from Zakaznik zak inner join Objednavky o on zak.ID_zakaznika=o.ID_zakaznika
where o.ID_objednavky=@cislo_obj)
set @cena=
(select SUM (cena*mnozstvi) from objednavka_detail od inner join Produkty p on od.id_produkt=p.Id_produkt
where od.ID_objednavky=@cislo_obj)
set @zamestnanec=
(select z.Jmeno+' '+z.Prijmeni from Zamestnanci z inner join Objednavky o on z.ID_Zamestnance=o.ID_zamestnance
where o.ID_objednavky=@cislo_obj)
set @ks=
(select SUM(mnozstvi) from objednavka_detail od inner join Produkty p on od.id_produkt=p.Id_produkt
where od.ID_objednavky=@cislo_obj)
print 'Objednávku číslo '+ltrim(@cislo_obj)+' si objednal/a '+@zakaznik+' a zpracoval/a ji '+@zamestnanec+'. Objednávka obsahuje '+ltrim(@ks)+' kusů a její celková cena činní '+ltrim(@cena)+ ' Kč'
end
go
Objednavka 17


-- 7.Po zadani ID zamestnance se zobrazi(formatovane): jmeno a prijmeni zamestnance a nazev pracoviste,kde pracuje
drop procedure sh_zam
go
create procedure sh_zam(@cislo_z int)
as
declare
@zamestnanec varchar (30),
@pracoviste varchar (30)
begin
set @zamestnanec=
(select z.Jmeno+' '+z.Prijmeni from Zamestnanci z where ID_Zamestnance=@cislo_z);
set @pracoviste=
(select o.Nazev from Oddeleni o inner join Zamestnanci z on o.ID_Oddeleni=z.ID_Oddeleni
where ID_Zamestnance=@cislo_z);
print 'Zaměstnanec číslo '+ltrim(@cislo_z)+' se jmenuje '+@zamestnanec+' a pracuje na oddělení '+@pracoviste+'.'
end
go
zamestnanec 1

-- 8.Po zadani RC zakaznika se zobrazi (formatovane) informace jestli je to zakaznik nebo zakaznice,jmeno a prijmeni,jestli je rodne cislo platne
drop procedure sh_rc
go
create procedure sh_rc (@sh_rc varchar (10))
as
declare
@pohlavi varchar (20),
@zakaznik varchar (30),
@platne varchar(5),
@vyskyt varchar (3)
begin
set @pohlavi=
(select case when SUBSTRING (@sh_rc,3,2)>=1 and SUBSTRING(@sh_rc,3,2)
else 'Zákaznice' end from Zakaznik where rodneCislo=@sh_rc)
set @zakaznik=
(select RTRIM(jmeno)+' '+RTRIM(prijmeni) from Zakaznik where rodneCislo=@sh_rc)
set @platne=
(select case when CAST (@sh_rc as bigint)%11=0 then 'Platné' else 'Neplatné'end)
if (@vyskyt = 'ano') print @pohlavi+' '+@zakaznik+' zadal/a '+@platne+' rodné číslo';
else print 'Zadané rodné číslo v databázi není';
end;
go
sh_rc 9407174705

-- 9.Slovnik

create table slovnik
(id_slovo int primary key,
cesky char (25),
english char (25)
);
insert into slovnik values(1,'Ahoj','Hi');

drop procedure preklad
go
create procedure preklad (@v_slovo varchar (15))
as
declare
@v_english char(25)
begin
set @v_english=
(select distinct english from slovnik
where LTRIM(rtrim(cesky))=@v_slovo)
if exists
(select distinct english from slovnik
where LTRIM(rtrim(cesky))=@v_slovo)
begin
print (@v_english)
end
else
print 'Slovo neexistuje'
end

go
preklad Ahoj

-- 10.Priklady ze skript
-- a)Vytvorte proceduru, ktera umozni zadat skoleni a zobrazi ID_Skoleni a pocet hodin skoleni

drop procedure sh_s
go
create procedure sh_s (@Nazev varchar (20))
as
declare
@hodin int,
@id int
begin
set @hodin=
(select Pocet_hodin from Skoleni where Nazev=@Nazev);
set @id=
(select ID_Skoleni from Skoleni where Nazev=@Nazev);
print (@Nazev+'( id='+cast (@id as varchar)+')'+ ' trvá '+cast (@hodin as varchar)+' hodin');
end;

go
sh_s Access

-- b)Kdo ma jake zajmy?
drop procedure sh_zajmy
go
create procedure sh_zajmy(@sh_zajmy varchar (20))
as
select jmeno+' '+prijmeni
from Zamestnanci
where Zajmy like '%'+@sh_zajmy+'%';

sh_zajmy Tenis

-- c)Deleni dvou cisel
drop procedure sh_deleni;
go
create procedure sh_deleni(@deleni1 float, @deleni2 float)
as
if (@deleni2=0)
begin
print 'Nulou se nedělí'
end;
if(@deleni2<>0)
begin
select @deleni1/@deleni2 as 'Výsledek'
end;

sh_deleni 10,0
sh_deleni 20,10

-- c) Po zadání roku narození se určí věk
drop procedure sh_vek;
go
create procedure sh_vek(@datum_narozeni datetime)
as
declare
@dnes datetime,
@vek int
begin
set @dnes=GETDATE();
set @vek=(select DATEDIFF(yy,@datum_narozeni,@dnes));
print @vek ;
end;

sh_vek '17.09.1989';

-- d) Zobrazení jména po zadání příjmení
drop procedure sh_zadani;
go
create procedure sh_zadani(@prijmeni varchar (50))
as
declare
@jmeno varchar(50)
begin
set @jmeno=(select Jmeno from Zamestnanci where Prijmeni=@prijmeni);
print @jmeno + ' ' + @prijmeni;
end;

sh_zadani 'Krásná'

-- e)Zobrazení pracoviště po zadání příjmení
drop procedure sh_pracoviste;
go
create procedure sh_pracoviste(@prijmeni varchar(30))
as
declare
@pracoviste varchar (30)
begin
set @pracoviste=
(select O.Nazev from Zamestnanci Z left join Oddeleni O on Z.ID_Oddeleni=O.ID_Oddeleni
where Z.Prijmeni=@prijmeni);
print @prijmeni + ' pracuje na tomto oddělení: ' + @pracoviste;
end;

sh_pracoviste 'Krásná';


--TRIGGERY
--1. Vytvořte trigger, který po vložení dat do tabulky vypíše hlášení, že data byla vložena a ID vloženého záznamu
drop trigger vkladani_zaznamu
go
create trigger vkladani_zaznamu on Zaznamy
after insert as
declare @nove_id int
begin
set @nove_id=
(select id_zaznamu from inserted )
print 'Záznam vložen. ID nového záznamu: '+ltrim(@nove_id)
end;
insert into Zaznamy values ('Kachna')

--2. Vytvořte trigger, který po vložení dat do tabulky ZAM - tu vytvořte, vytvoří přihlašovací

jméno (login name: Xprijmeni)
drop table ZAM
go
create table ZAM
(id int primary key identity,
prijmeni varchar (20),
xname varchar (8) default null)
go

insert into ZAM values
('Kapustňačka',DEFAULT)

drop trigger sh_xname
go
create trigger sh_xname on ZAM
after insert as
declare
@idd int
begin
set @idd=(select id from inserted)
update ZAM
set xname = 'X'+upper(substring(prijmeni,1,7) collate SQL_LATIN1_GENERAL_CP1251_ci_as)
where id=@idd
select * from ZAM
end
go
select * from ZAM

--3. Vytvořte trigger, který při aktualizace dat v tabulce vloží do tabulky Historie: čas změny, původní hodnotu a novou hodnotu
-- Nevím, jestli je to opravdu správně, ale jako bonusovku mi to uznal :-)

-- a) Vytvoreni tabulky
 drop table silvieha;
go
create table silvieha
(id_osob int primary key identity,
prijmeni varchar(40),
znacka varchar(40));

drop table silvieha_history;
go
create table silvieha_history
(cas datetime default getdate(),
puvodni_hodnota varchar(40),
nova_hodnota varchar (40));

select *
from silvieha

-- b)Naplneni tabulky
insert into silvieha
values ('Hanzelová','Playboy');


-- c)Aktualizace dat
update silvieha
set znacka='Versace'
where id_osob='1'

-- d)Trigger
create trigger aktual on silvieha
after update as
declare
@old varchar(40),
@new varchar(40)
begin
set @old =(select znacka from deleted)
set @new =(select znacka from inserted)
insert into table_his
values (getdate(),@old,@new)
select * from silvieha_history
end


--4. Vytvořte trigger, který po smazání dat z tabulky (tu vytvořte), zobrazí hlášení: "Záznam odstraněn"
--a) Vytvoreni tabulky
drop table silvie;
go
create table silvie
(id_osoba int primary key identity,
prijmeni varchar(40),
city varchar(40));

-- b)Naplneni tabulky
insert into silvie values ('Hanzelová','Praha');

-- c)Trigger
drop trigger pryc_hanzesil;
go
create trigger pryc_hanzesil on silvie
after delete as
begin
print ('Záznam smazan');
end;

select *
from silvie

delete silvie
where id_osoba ='1'

--- Vytvořte trigger, který při aktualizaci dat v tabulce vloří do tabulky Historie: čas změny, puvodní hodnotu a novou hodnotu

DROP TABLE badatele_89;

GO

CREATE TABLE badatele_89
(
id_prac INT PRIMARY KEY IDENTITY,
jmeno VARCHAR(15),
prijmeni VARCHAR(15),
mesto VARCHAR(25),
plat VARCHAR(20)
);

SELECT *
FROM badatele_89

INSERT INTO badatele_89
VALUES('Kate?ina',
'Trefná',
'Ostrava',
'25 000');

INSERT INTO badatele_89
VALUES('Martina',
'Nováková',
'P?íbram',
'19 800');

INSERT INTO badatele_89
VALUES('Jan',
'Bodný',
'Opava',
'21 852');

INSERT INTO badatele_89
VALUES('Tomá?',
'Bou?ka',
'Brno',
'28 456');

GO

DROP TABLE historie_89;

GO

CREATE TABLE historie_89
(
cas_zmeny DATETIME DEFAULT Getdate(),
puvodni_hodnota VARCHAR(20),
nova_hodnota VARCHAR(20)
);

DROP TRIGGER zmena_89;

GO

CREATE TRIGGER zmena_89
ON badatele_89
AFTER UPDATE
AS
DECLARE
@puvodni VARCHAR(25),
@nova VARCHAR(25)

BEGIN
SET @puvodni=(SELECT mesto
FROM deleted);
SET @nova=(SELECT mesto
FROM inserted);

INSERT INTO historie_89
VALUES(Getdate(),
@puvodni,
@nova);
END;

UPDATE badatele_89
SET mesto='Pardubice'
WHERE id_prac=3

SELECT *
FROM badatele_89

SELECT *
FROM historie_89

--- Vytvořte trigger, který po smazání dat z tabulky (tu vytvořte), zobrazí hláření: "Záznam odstraněn"

DROP TABLE pracovnici_89;

GO

CREATE TABLE pracovnici_89
(
id_prac INT PRIMARY KEY IDENTITY,
jmeno VARCHAR(15),
prijmeni VARCHAR(15),
mesto VARCHAR(25)
);

SELECT *
FROM pracovnici_89

INSERT INTO pracovnici_89
VALUES('Karel',
'Pospí?il',
'Brno');

INSERT INTO pracovnici_89
VALUES('Martin',
'Novotný',
'Praha');

INSERT INTO pracovnici_89
VALUES('Jan',
'Bedná?',
'Olomouc');

INSERT INTO pracovnici_89
VALUES('Jana',
'Pospí?ilová',
'Brno');

DROP TRIGGER smazani_dat_89;

GO

CREATE TRIGGER smazani_dat_89
ON pracovnici_89
AFTER DELETE
AS
BEGIN
PRINT'Záznam odstran?n'
END;

DELETE pracovnici_89
WHERE prijmeni='Pospí?il'
Přidat komentář

kli

24. října 2011 v 13:26

-- Kdo ma vice nez je prumerny plat?
SELECT *
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci);

-- Kdo nastoupil ve stejny den jako
-- Hana Peterkova?
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_nastupu = (SELECT datum_nastupu
FROM zamestnanci
WHERE jmeno = 'Hana'
AND prijmeni = 'Peterkova')
AND jmeno <> 'Hana'
AND prijmeni <> 'Peterkova';

-- Kdo ma nejvetsi mzdu v podniku
SELECT jmeno,
prijmeni,
plat
FROM zamestnanci
WHERE plat = (SELECT MAX(plat)
FROM zamestnanci);

-- CVICENI
-- 1. Kteri zakaznici si objednali 40x
-- a vicekrat?
SELECT jmeno,
prijmeni,
COUNT(o.id_objednavky)
FROM zakaznik z
JOIN objednavky o
ON z.id_zakaznika = o.id_zakaznika
GROUP BY jmeno,
prijmeni
HAVING COUNT(o.id_objednavky) > 40

-- 3. Pocet zamestnancu
-- s platem mensim nez plat prumerny.
SELECT COUNT(*)
FROM zamestnanci
WHERE plat < (SELECT AVG(plat)
FROM zamestnanci);

-- 2. Kteri zamestnanci nastoupili
-- jako prvni?
-- (Pokud ve stejny den nastoupilo vice zamestnancu, zobrazte je vsechny).
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_nastupu = (SELECT MIN (datum_nastupu)
FROM zamestnanci)

-- 8. Kdo se narodil ve stejny den jako Adela Smutna?
-- (poddotaz: kdy se narodila Adela Smutna)
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_narozeni = (SELECT datum_narozeni
FROM zamestnanci
WHERE jmeno = 'Adela'
AND prijmeni = 'Smutna')
AND ( jmeno + prijmeni <> 'Adela' + 'Smutna' )

-- 6. Kdo absolvoval takova skoleni
-- jako Jan Novy?
-- (Poddotaz: na jakych skolenich byl Novy?)
SELECT jmeno,
prijmeni
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
WHERE id_skoleni IN (SELECT id_skoleni
FROM zamestnanciskoleni zs
JOIN zamestnanci z
ON zs.id_zamestnance = z.id_zamestnance
WHERE jmeno + prijmeni = 'Jan' + 'Novy')

-- 9. Kdo zpracovaval objednavky ve
-- stejne dny jako Hana Peterkova?
SELECT DISTINCT jmeno + ' ' + prijmeni
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
WHERE datum_odeslani IN (SELECT datum_odeslani
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
WHERE jmeno + ' ' + prijmeni = 'Hana Peterkova')
AND ( jmeno + ' ' + prijmeni ) <> 'Hana Peterkova';

-- Jake je procento zen?
SELECT Round(CAST((SELECT COUNT (*)
FROM zamestnanci
WHERE pohlavi = 'z')AS FLOAT) / (SELECT COUNT (*)
FROM zamestnanci) * 100,
2) AS
"procento zen"

--- TEZKE PODDOTAZY
--Ktere pracoviste ma nejvyssi pocet pracovniku?
-- Poddotaz (id_prac s max zam)
SELECT MAX(pom.pocetprac) AS pocetprac
FROM (SELECT COUNT(*) AS pocetprac
FROM zamestnanci
GROUP BY id_oddeleni) pom;

-- Ktere oddeleni ma 9 zam?
SELECT nazev,
COUNT(z.id_zamestnance)
FROM oddeleni o
JOIN zamestnanci z
ON o.id_oddeleni = z.id_oddeleni
GROUP BY nazev
HAVING COUNT(z.id_zamestnance) = (SELECT MAX(pom.pocetprac) AS pocetprac
FROM (SELECT COUNT(*) AS pocetprac
FROM zamestnanci
GROUP BY id_oddeleni) pom)



-- Kdo ma plat vetsi nez je prumerny plat?
SELECT jmeno,
prijmeni,
plat
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci);

-- Kdo ma plat vetsi nez
-- je prumerny plat muzu?
SELECT jmeno,
prijmeni,
plat
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci
WHERE pohlavi = 'm');

-- Kteri muzi ma plat vetsi nez
-- je prumerny plat muzu?
SELECT jmeno,
prijmeni,
plat
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci
WHERE pohlavi = 'm')
AND pohlavi = 'm';

-- Kdo nastoupil ve stejny den jak Hana Peterkova?
--(Poddotaz: den nastupu Hany Peterkove)
SELECT *
FROM zamestnanci
WHERE datum_nastupu = (SELECT datum_nastupu
FROM zamestnanci
WHERE jmeno = 'Hana'
AND prijmeni = 'Peterkova');

-- S vyloucenim toho, na koho se ptam
SELECT *
FROM zamestnanci
WHERE datum_nastupu = (SELECT datum_nastupu
FROM zamestnanci
WHERE jmeno = 'Hana'
AND prijmeni = 'Peterkova')
AND ( jmeno <> 'Hana'
AND prijmeni <> 'Peterkova' );

-- Kdo ma nejvyssi mzdu v podniku?
-- (Poddotaz: maximalni plat)
SELECT jmeno,
prijmeni,
plat
FROM zamestnanci
WHERE plat = (SELECT MAX(plat)
FROM zamestnanci);

---- CVICENI
-- 1. Kteri zakaznici si objednali
--40x a vicekrat?
--Seradte podle poctu objednavek.
SELECT Rtrim(jmeno) + ' ' + prijmeni,
COUNT(id_objednavky)
FROM zakaznik z
JOIN objednavky o
ON z.id_zakaznika = o.id_zakaznika
GROUP BY Rtrim(jmeno) + ' ' + prijmeni
HAVING COUNT(id_objednavky) > 40;

-- 3. Pocet zamestnancu s platem mensim
-- nez plat prumerny.
SELECT COUNT(*)
FROM zamestnanci
WHERE plat < (SELECT AVG(plat)
FROM zamestnanci)

-- 2. Kteri zamestnanci nastoupili
-- jako prvni?
-- (Pokud ve stejny den nastoupilo
-- vice zamestnancu, zobrazte je vsechny).
-- (Poddotaz: datum nastupu prvniho)
SELECT *
FROM zamestnanci
WHERE datum_nastupu = (SELECT MIN(datum_nastupu)
FROM zamestnanci)

-- 8. Kdo se narodil ve stejny den
-- jako Adela Smutna?
-- (Poddotaz: datum nar. A S)
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_narozeni = (SELECT datum_narozeni
FROM zamestnanci
WHERE jmeno + prijmeni = 'Adela' + 'Smutna')
AND jmeno + prijmeni <> 'Adela' + 'Smutna';

-- 6. Kdo absolvoval takova skoleni
-- jako Jan Novy?
-- (Poddotaz: id_skolenich Jana Noveho)
SELECT DISTINCT jmeno,
prijmeni
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
WHERE zs.id_skoleni IN(SELECT zs.id_skoleni
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
WHERE jmeno + prijmeni = 'Jan' + 'Novy')
AND jmeno + prijmeni <> 'Jan' + 'Novy'

-- 7. Kdo nastoupil ve stejny
-- den jako Adela Krasna?
SELECT jmeno + ' ' + prijmeni
FROM zamestnanci
WHERE datum_nastupu = (SELECT datum_nastupu
FROM zamestnanci
WHERE jmeno + prijmeni = 'Adela' + 'Krasna')
AND jmeno + prijmeni <> 'Adela' + 'Krasna';

-- 9. Kdo zpracovaval objednavky ve
-- stejne dny(datum odeslani)
-- jako Hana Peterkova?
SELECT DISTINCT jmeno,
prijmeni
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
WHERE datum_odeslani IN (SELECT datum_odeslani
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
WHERE jmeno + prijmeni = 'HanaPeterkova')
AND jmeno + prijmeni <> 'HanaPeterkova';

-- Jake je procencto zen ve firme.
-- Poddotaz muze byt i v SELECTu
SELECT Round(CAST((SELECT COUNT (*)
FROM zamestnanci
WHERE pohlavi = 'z') AS FLOAT) / (SELECT COUNT (*)
FROM zamestnanci) * 100,
2);

-- TEZKE PODDOTAZY
-- Ktere pracoviste ma nejvetsi pocet zamestnancu?
-- (Poddotaz: Maximalni pocet zam. na
-- jednom pracovisti)
-- Reseni pomoci poddotazu v kauzuli FROM
SELECT MAX(pom.poctyzam)
FROM (SELECT COUNT(*) AS poctyzam
FROM zamestnanci
GROUP BY id_oddeleni) pom

-- Jake prac ma pocet zam. 9?
SELECT nazev
FROM oddeleni o
JOIN zamestnanci z
ON o.id_oddeleni = z.id_oddeleni
GROUP BY nazev
HAVING COUNT(id_zamestnance) = 9

-- Vysledek
SELECT nazev
FROM oddeleni o
JOIN zamestnanci z
ON o.id_oddeleni = z.id_oddeleni
GROUP BY nazev
HAVING COUNT(id_zamestnance) = (SELECT MAX(pom.poctyzam)
FROM (SELECT COUNT(*) AS poctyzam
FROM zamestnanci
GROUP BY id_oddeleni) pom);



-- Kdo ma plat vyssi, nez je prumerny plat v podniku
SELECT *
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci);

-- Kdo ma plat vyssi nez je
-- prumerny plat muzu?
SELECT *
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci
WHERE pohlavi = 'm');

-- Ktere zeny berou vice nez je prumerny plat muzu?
SELECT *
FROM zamestnanci
WHERE plat > (SELECT AVG(plat)
FROM zamestnanci
WHERE pohlavi = 'm')
AND pohlavi = 'z';

-- Kdo nastoupil ve stejny den jako Hana Peterkova?
-- (Poddotaz: datum nastupu Peterkove)
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_nastupu = (SELECT datum_nastupu
FROM zamestnanci
WHERE jmeno = 'Hana'
AND prijmeni = 'Peterkova')
AND ( jmeno <> 'Hana'
OR prijmeni <> 'Peterkova' );

-- Kdo ma nejvyssi mzdu v podniku?
-- Podotaz: jaky je max plat?
SELECT jmeno + ' ' + prijmeni
FROM zamestnanci
WHERE plat = (SELECT MAX(plat)
FROM zamestnanci)

--- CVICENI
-- 1. Kteri zakaznici si objednali
-- 40x a vicekrat?
-- Seradte podle poctu objednavek.
SELECT jmeno,
prijmeni,
COUNT(id_objednavky)
FROM zakaznik z
JOIN objednavky o
ON z.id_zakaznika = o.id_zakaznika
GROUP BY jmeno,
prijmeni
HAVING COUNT(id_objednavky) > 40
ORDER BY COUNT(id_objednavky)

-- 3. Pocet zamestnancu s platem mensim
-- nez plat prumerny.
SELECT COUNT(*)
FROM zamestnanci
WHERE plat < (SELECT AVG(plat)
FROM zamestnanci)

-- 2. Kteri zamestnanci nastoupili
--jako prvni?
--(Pokud ve stejny den nastoupilo
-- vice zamestnancu, zobrazte je vsechny).
-- Poddotaz: datum nastupu prviho zam.
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_nastupu = (SELECT MIN(datum_nastupu)
FROM zamestnanci);

-- 8. Kdo se narodil ve stejny den
-- jako Adela Smutna, krome ni?
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE datum_narozeni = (SELECT datum_narozeni
FROM zamestnanci
WHERE jmeno + prijmeni = 'Adela' + 'Smutna')
AND jmeno + prijmeni <> 'AdelaSmutna'

-- 6. Kdo absolvoval takova skoleni
-- jako Jan Novy?
SELECT DISTINCT jmeno,
prijmeni
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
WHERE id_skoleni IN (SELECT id_skoleni
FROM zamestnanciskoleni zs
JOIN zamestnanci z
ON zs.id_zamestnance = z.id_zamestnance
WHERE jmeno + prijmeni = 'JanNovy')
AND jmeno + prijmeni <> 'JanNovy'

-- 9. Kdo zpracovaval objednavky ve
-- stejne dny(datum odeslani)jako Hana Peterkova?
SELECT DISTINCT jmeno,
prijmeni
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
WHERE datum_odeslani IN (SELECT datum_odeslani
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
WHERE jmeno + prijmeni = 'HanaPeterkova')
AND jmeno + prijmeni <> 'HanaPeterkova';

-- Jake je procento zen v podniku???
-- Poddotaz v SELECTu
SELECT Round (CAST((SELECT COUNT(*)
FROM zamestnanci
WHERE pohlavi = 'z') AS FLOAT) / (SELECT COUNT(*)
FROM zamestnanci) * 100
, 2)

--TEZKE PODDOTAZY
-- Ktere pracoviste,
-- ma nejvetsi pocet zamestnancu?
-- Poddotaz: Nejtetsi pocet zam na pracovisti
-- Pocty zamestnancu
-- PODDOTAZ VE FROM
SELECT MAX(pom.pocetzam)
FROM (SELECT COUNT (*) AS pocetzam
FROM zamestnanci
GROUP BY id_oddeleni) pom

-- Odd s poctem zam 9.
SELECT nazev
FROM oddeleni o
JOIN zamestnanci z
ON o.id_oddeleni = z.id_oddeleni
GROUP BY nazev
HAVING COUNT (id_zamestnance) = 9

-- Vysledek dotazu
SELECT nazev
FROM oddeleni o
JOIN zamestnanci z
ON o.id_oddeleni = z.id_oddeleni
GROUP BY nazev
HAVING COUNT (id_zamestnance) = (SELECT MAX(pom.pocetzam)
FROM (SELECT COUNT (*) AS pocetzam
FROM zamestnanci
GROUP BY id_oddeleni) pom)



--2. Jake oddeleni (nazev)ma nejvice
-- zamestnancu?
-- max pocet zam na jednom prac.
SELECT MAX(pom.cislo)
FROM (SELECT COUNT(*) AS cislo
FROM zamestnanci
GROUP BY id_oddeleni) pom

-- Pocty zamestnancu na jednotlivych
-- Pracovictich
SELECT nazev,
COUNT(id_zamestnance)
FROM zamestnanci z
JOIN oddeleni o
ON z.id_oddeleni = o.id_oddeleni
GROUP BY nazev
HAVING COUNT(id_zamestnance) = (SELECT MAX(pom.cislo)
FROM (SELECT COUNT(*) AS cislo
FROM zamestnanci
GROUP BY id_oddeleni) pom)

--5. Kteri pracovnici absolvovali
-- nejvetsi pocet skoleni?
SELECT MAX(pom.aa)
FROM (SELECT COUNT(id_skoleni) AS aa
FROM zamestnanciskoleni
GROUP BY id_zamestnance) pom

SELECT jmeno,
prijmeni,
COUNT(id_skoleni)
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
GROUP BY jmeno,
prijmeni
HAVING COUNT(id_skoleni) = (SELECT MAX(pom.aa)
FROM (SELECT COUNT(id_skoleni) AS aa
FROM zamestnanciskoleni
GROUP BY id_zamestnance) pom)

--5. Kdo (zakaznik) nejvice objednavek
-- produktu ?Box-penezenka na 24CD??
-- Poddotaz: Maximalni pocet kusu, ktery si
-- kdo objednal kusu
SELECT MIN(pom.aa)
FROM (SELECT SUM(mnozstvi) AS aa
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
WHERE nazev = 'Box-penezenka na 24CD'
GROUP BY id_zakaznika) pom

-- Kdo objednal 28 kusu Box-penezenka na 24CD?
SELECT jmeno,
prijmeni,
SUM(mnozstvi)
FROM zakaznik z
JOIN objednavky o
ON z.id_zakaznika = o.id_zakaznika
JOIN objednavka_detail od
ON od.id_objednavky = o.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
WHERE nazev = 'Box-penezenka na 24CD'
GROUP BY jmeno,
prijmeni
HAVING SUM(mnozstvi) = (SELECT MIN(pom.aa)
FROM (SELECT SUM(mnozstvi) AS aa
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
WHERE nazev = 'Box-penezenka na 24CD'
GROUP BY id_zakaznika) pom)

--1. Zobrazte, ve kterem roce nastoupilo nejvice zamestnancu.
-- 1. Zobrazte, ve kterém roce
-- nastoupilo nejvíce zam?stnanc?.
-- Poddotaz: rok, po?et nastoupiv?ích zam
SELECT MAX(aa)
FROM (SELECT COUNT(id_zamestnance) aa
FROM zamestnanci
GROUP BY YEAR(datum_nastupu)) pom

SELECT YEAR(datum_nastupu),
COUNT(id_zamestnance)
FROM zamestnanci
GROUP BY YEAR(datum_nastupu)
HAVING COUNT(id_zamestnance) = (SELECT MAX(aa)
FROM (SELECT COUNT(id_zamestnance) aa
FROM zamestnanci
GROUP BY YEAR(datum_nastupu)) pom)

--6. Kdo (zakaznik) udelal nejvice objednavek?

--16. Kteri zamestnanci
-- (zobrazte: ID zamestnance,
-- jmeno a prijmeni)
-- NEBYLI na skoleni SQL??
-- NESPRAVNE RESENI
-- KDO BYL NA JAKEM SKLENI?
SELECT jmeno,
prijmeni,
nazev
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
JOIN skoleni s
ON s.id_skoleni = zs.id_skoleni
WHERE nazev <> 'SQL'
ORDER BY 3

-- Kdo byl na SQL
SELECT jmeno + prijmeni
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
JOIN skoleni s
ON s.id_skoleni = zs.id_skoleni
WHERE nazev = 'SQL'

-- Vsichni, kdome vyse uvedenych
SELECT jmeno,
prijmeni
FROM zamestnanci
WHERE jmeno + prijmeni NOT IN (SELECT jmeno + prijmeni
FROM zamestnanci z
JOIN zamestnanciskoleni zs
ON z.id_zamestnance = zs.id_zamestnance
JOIN skoleni s
ON s.id_skoleni = zs.id_skoleni
WHERE nazev = 'SQL')

Pohledy

-- KDO PRACUJE NA JAKÉM odd?
SELECT jmeno + ' ' + prijmeni AS zamestnanec,
nazev
FROM zamestnanci z
JOIN oddeleni o
ON z.id_oddeleni = o.id_oddeleni

/*
create view KdoKde1_99 as
SELECT jmeno + ' '+ prijmeni as zamestnanec, nazev
from Zamestnanci z join Oddeleni o on z.ID_Oddeleni = o.ID_Oddeleni
*/
SELECT *
FROM kdokde1_99
WHERE nazev = 'Technické'

-- Zam?stnanci a jejich vedoucí
SELECT id_zamestnance,
jmeno,
prijmeni,
id_nadrizeny
FROM zamestnanci

SELECT zam.jmeno + ' ' + zam.prijmeni AS zamestnanec,
ved.jmeno + ' ' + ved.prijmeni AS vedouci
FROM zamestnanci zam
JOIN zamestnanci ved
ON zam.id_nadrizeny = ved.id_zamestnance

/*
create view PracZam1_99 as
select
zam.Jmeno + ' ' +zam.Prijmeni as zamestnanec,
ved.Jmeno+ ' ' +ved.Prijmeni as Vedouci
from Zamestnanci zam join Zamestnanci ved
on zam.ID_nadrizeny = ved.ID_Zamestnance
*/

-- Kdo je vedoucí Hany Nováková
SELECT vedouci
FROM praczam1_99
WHERE zamestnanec = 'Hana Nováková'

-- Komu Šéfuje Šimon Novotný?
SELECT zamestnanec
FROM praczam1_99
WHERE vedouci = '?imon Novotný'
Přidat komentář

opa

24. října 2011 v 13:16
-- 1. Vytvorte poddotaz, ktery zobrazi,
-- kdo (zamestnanec) ziskal nejvetsi pocet objednavek. (0,25b)
-- PODDOTAZ: jaky je max pocet obj. od jednogho zam.
SELECT MAX(poc)
FROM (SELECT COUNT(id_objednavky) AS poc
FROM objednavky
GROUP BY id_zamestnance) AS pom

-- Kdo udelal 140 obj.?
SELECT jmeno,
prijmeni,
COUNT(id_objednavky)
FROM zamestnanci z
JOIN objednavky o
ON z.id_zamestnance = o.id_zamestnance
GROUP BY jmeno,
prijmeni
HAVING COUNT(id_objednavky) = (SELECT MAX(poc)
FROM (SELECT COUNT(id_objednavky) AS poc
FROM objednavky
GROUP BY id_zamestnance) AS pom);

-- 2. Vyplatilo se nam byt otevreni v patky? (Z hlediska obratu?)
-- (Zobrazte, jaky mame obrat v jednotlivych
-- dnech tydne - pouzit datumove funkce ) (0.5b)
SELECT Datepart(dw, Getdate());

SELECT Datename(dw, Getdate());

-- Cena objednavek v jednotlive dny
SELECT SUM(mnozstvi * cena) AS obrat,
Datename(dw, datum_prijeti) AS den
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
GROUP BY Datename(dw, datum_prijeti)
ORDER BY obrat DESC;

-- Nejrusnejsi den podle počtu přijatých objednávek?
-- Pocet objednavek v jednotlive dny
SELECT COUNT(id_objednavky) AS "Počet obj za den",
Datename(dw, datum_prijeti) AS den
FROM objednavky
GROUP BY Datename(dw, datum_prijeti)
ORDER BY obrat DESC;

-- 3. Ktere dny jsou pro nas z hlediska
--obratu nejlepsi?
--Ktery den v tydnu vydelavame nejvice?(1.25b)
SELECT MAX(obrat)
FROM (SELECT SUM(mnozstvi * cena) AS obrat,
Datename(dw, datum_prijeti) AS den
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
GROUP BY Datename(dw, datum_prijeti))pom

SELECT SUM(mnozstvi * cena) AS obrat,
Datename(dw, datum_prijeti) AS den
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
GROUP BY Datename(dw, datum_prijeti)
HAVING SUM(mnozstvi * cena) = (SELECT MAX(obrat)
FROM (SELECT SUM(mnozstvi * cena) AS
obrat,
Datename(dw, datum_prijeti) AS den
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky =
od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
GROUP BY Datename(dw, datum_prijeti))pom
)

-- 5. O kolik by vzrostl rocni naklady na podnik,
-- kdyby se vsem bez titulu pridalo 5%
-- a s titulem o 10%?
-- Kolik pridame zam. bez titulu?
SELECT SUM(plat) * 0.05
FROM zamestnanci
WHERE titul IS NULL

-- Kolik pridame zam. s tutulem?
SELECT SUM(plat) * 0.1
FROM zamestnanci
WHERE titul IS NOT NULL

-- SECTEME
SELECT (SELECT SUM(plat) * 0.05
FROM zamestnanci
WHERE titul IS NULL) + (SELECT SUM(plat) * 0.1
FROM zamestnanci
WHERE titul IS NOT NULL) AS navyseni

-- 10. Sestavte zebricek produktu podle jejich
-- ziskovosti. Predpokladejte, ze ziskovost
-- je 10% z obratu (celkovych trzeb za produkt).
SELECT TOP 10 nazev,
SUM(mnozstvi * cena) * 0.10 AS ziskovost
FROM produkty p
JOIN objednavka_detail od
ON p.id_produkt = od.id_produkt
GROUP BY nazev
ORDER BY ziskovost DESC

-- 11. Sestavte zebricek objednavek podle
-- jejich ziskovosti. Predpokladejte,
-- ze ziskovost je 15% z celkove castky za
-- objednavku.
SELECT SUM(mnozstvi * cena) * 0.15 AS zisk,
o.id_objednavky,
COUNT(p.id_produkt) AS "pocet polozek"
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
GROUP BY o.id_objednavky
ORDER BY zisk DESC;

-- Vytvorime pohled pro dotaz vyse
/*
create view ObjPodleZisku_99 as
select SUM(mnozstvi*cena)*0.15 as zisk,
o.ID_objednavky,
COUNT(p.id_produkt)as "pocet polozek"
from Objednavky o join objednavka_detail od
on o.ID_objednavky = od.ID_objednavky
join Produkty p on p.Id_produkt= od.id_produkt
group by o.ID_objednavky
*/
SELECT *
FROM objpodlezisku_99
ORDER BY 1 DESC

-- 35. Pocet mesicu mezi
-- prvnim a podlednim nastupem
-- Prvni zam:
SELECT MIN(datum_nastupu)
FROM zamestnanci

SELECT MAX(datum_nastupu)
FROM zamestnanci

SELECT Datediff(yy, (SELECT MIN(datum_nastupu)
FROM zamestnanci), (SELECT MAX(datum_nastupu)
FROM zamestnanci))

-- 31 Datum prvni objednavky zakazniku
SELECT MIN(datum_prijeti),
jmeno,
prijmeni
FROM objednavky o
JOIN zakaznik z
ON o.id_zakaznika = z.id_zakaznika
GROUP BY jmeno,
prijmeni

-- 30 Kolik zamestnancu ma plat
-- nad 20000 a kolik pod 20000
SELECT COUNT(CASE
WHEN plat > 20000 THEN id_zamestnance
ELSE NULL
END) AS "Nad 20000",
COUNT(CASE
WHEN plat
ELSE NULL
END) AS "POD 20000",
COUNT(id_zamestnance) AS poczam
FROM zamestnanci;

NEBO

SELECT (SELECT COUNT(id_zamestnance)
FROM zamestnanci
WHERE plat >= 20000),
(SELECT COUNT(id_zamestnance)
FROM zamestnanci
WHERE plat < 20000)

-- 21 Kteri zakaznici a zamestnanci
-- jsou ze stejneho mesta?
SELECT zam.jmeno + ' ' + zam.prijmeni AS zamestnanec,
Rtrim(zak.jmeno) + ' ' + Rtrim(zak.prijmeni) AS zakaznik,
zam.mesto
FROM zamestnanci zam
JOIN zakaznik zak
ON zam.mesto = zak.mesto;

-- 18 Kteri zamestnanci maji
-- stejne jmeno a prijmeni jako zakaznici?
SELECT zam.jmeno + ' ' + zam.prijmeni AS zamestnanec,
Rtrim(zak.jmeno) + ' ' + Rtrim(zak.prijmeni) AS zakaznik
FROM zamestnanci zam
JOIN zakaznik zak
ON zam.jmeno = zak.jmeno
AND zam.prijmeni = zak.prijmeni
Přidat komentář

slo

24. října 2011 v 1:32
--Vypište pracovníky a jejich nadřízené
select Zam.Jmeno + ' ' +Zam.Prijmeni as zamestnanec,
Ved.Jmeno + ' ' + Ved.Prijmeni as nadrizeny
from Zamestnanci Zam join Zamestnanci Ved
on Zam.ID_nadrizeny= Ved.id_Zamestnance;

--Které křestní jméno se vyskytuje nejčastěji
select jmeno, COUNT(jmeno) as pocet
from Zamestnanci
group by Jmeno
having COUNT(jmeno) = (select
MAX(pom.poc)
from (select jmeno, COUNT(jmeno) as poc
from Zamestnanci
group by Jmeno) pom) ;

--Kteří zaměstnanci nebyli na školení SQL
select Jmeno+ ' '+Prijmeni
from Zamestnanci
where Jmeno+ ' ' +Prijmeni not in (select Jmeno+' ' +Prijmeni
from Zamestnanci Z join ZamestnanciSkoleni ZS
on Z.ID_Zamestnance=ZS.ID_Zamestnance
join Skoleni S
on ZS.ID_Skoleni=S.ID_Skoleni
where Nazev ='SQL');

--Kolik zákazníků uvedlo správné rodné číslo a kolik nesprávné
select(
select COUNT(*) as Platné from(
select CAST(rodnecislo as bigint)%11 as platne
from Zakaznik
where CAST(rodnecislo as bigint)%11 =0)pom) as Platné,

(select COUNT(*) as Neplatné from(
select CAST(rodnecislo as bigint)%11 as neplatne
from Zakaznik
where CAST(rodnecislo as bigint)%11 <>0)pom2) as Neplatné;

-- 5. O kolik by vzrostl roční náklady na podnik, kdyby se všem bez titulu přidalo 5% a s titulem o 10%?
--Zvýšení pro bez titulu
select SUM(plat)*0.05
from Zamestnanci
where Titul is null;
--Zvýšení pro s titulem
select SUM(plat)*0.1
from Zamestnanci
where Titul is not null;
--Součet
select(select SUM(plat)*0.05
from Zamestnanci
where Titul is null) +
(select SUM(plat)*0.1
from Zamestnanci
where Titul is not null) as Zvyseni;

--21 Kteří zákazníci a zaměstnanci jsou ze stejného města
select Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.jmeno) + ' ' + rtrim(ZK.prijmeni) as Zakaznik, z.mesto
from Zamestnanci Z join Zakaznik ZK
on Z.Mesto=ZK.mesto;

--18 Kteří zaměstnanci mají stejné jméno a příjmení jako zákazníci
select Z.Jmeno + ' ' + Z.Prijmeni as Zamestnanec, rtrim(ZK.jmeno) + ' ' + rtrim(ZK.prijmeni) as Zakaznik
from Zamestnanci Z join Zakaznik ZK
on Z.Jmeno=ZK.jmeno and Z.Prijmeni = ZK.prijmeni;

--Vypište názvy produktů kterých se utržilo víc než 20 000Kč a cenu
select nazev, SUM(mnozstvi * cena) as Cena
from objednavka_detail OD join Produkty P
on OD.id_produkt=P.Id_produkt
group by Nazev
having SUM(mnozstvi * cena)>=20000
order by SUM(mnozstvi * cena) desc ;

--Zákazníci kteří utratili více než 200 000
select jmeno,prijmeni,SUM(mnozstvi * cena) as Cena,Nazev
from
Zakaznik ZK join Objednavky O on
ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD on
O.ID_objednavky=OD.ID_objednavky join Produkty P
on OD.id_produkt=P.Id_produkt
group by jmeno,prijmeni,Nazev
having SUM(mnozstvi * cena)>=20000
order by SUM(mnozstvi * cena) desc ;

-- 10. Sestavte žebříček produktů podle jejich ziskovosti. Předpokládejte, že ziskovost je 10% z obratu (celkových tržeb za produkt).
select SUM(cena * mnozstvi) * 0.1 as Ziskovost, Nazev
from objednavka_detail OD join Produkty P
on OD.id_produkt=P.Id_produkt
group by Nazev
order by Ziskovost desc;

--Kniha objednávek
select OD.ID_objednavky,datum_prijeti, jmeno,prijmeni, mnozstvi,SUM(mnozstvi * cena) as Cena,Nazev
from
Zakaznik ZK join Objednavky O on
ZK.ID_zakaznika=O.ID_zakaznika
join objednavka_detail OD on
O.ID_objednavky=OD.ID_objednavky join Produkty P
on OD.id_produkt=P.Id_produkt
group by OD.ID_objednavky,datum_prijeti, jmeno,prijmeni, mnozstvi,Nazev;

-- 11. Sestavte žebříček objednávek podle jejich ziskovostí. Předpokládejte, že ziskovost je 15% z celkové částky za objednávku.
SELECT SUM(mnozstvi * cena) * 0.15 AS zisk,
o.id_objednavky,
COUNT(p.id_produkt) AS 'pocet polozek'
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt
GROUP BY o.id_objednavky
ORDER BY zisk DESC;

--Průměrná tržba z objednávek
select AVG(pom.Ziskovost) from(
select cena * mnozstvi as Ziskovost
from objednavka_detail OD join Produkty P
on OD.id_produkt=P.Id_produkt ) pom;

--Celový zisk pokud je ziskovost 15%
SELECT SUM(mnozstvi * cena) * 0.15 AS zisk
FROM objednavky o
JOIN objednavka_detail od
ON o.id_objednavky = od.id_objednavky
JOIN produkty p
ON p.id_produkt = od.id_produkt;

--Zákazníci s neplatným rodným číslem
select jmeno,prijmeni from Zakaznik
where CAST(rodnecislo as bigint)%11 <>0;

--Pohlaví zákazníků seraženo dle pohlaví
select jmeno,prijmeni
from Zakaznik

--Vypište všechny objednávky které mají celkovou částku vyšší než 10 000
select ID_objednavky,SUM(mnozstvi * cena) as Castka
from objednavka_detail OD join Produkty P
on OD.id_produkt=P.Id_produkt
group by ID_objednavky
having SUM(mnozstvi * cena)>=10000
order by Castka desc;

--Jaká byla průměrná tržba za objednávku
select AVG(castka) as PrumTrzba from(
select ID_objednavky, SUM(mnozstvi * cena) as Castka
from objednavka_detail OD join Produkty P
on OD.id_produkt=P.Id_produkt
group by ID_objednavky)pom

--Kolik zaměstnanců má plat vyšší než 20 000 a kolik menší
select(
select COUNT(Jmeno) as Vyšší from(
select Jmeno,Prijmeni,Plat
from Zamestnanci
where Plat>20000)pom) as Vyšší,

(select count(Jmeno) as Nižší from(
select Jmeno,Prijmeni,Plat
from Zamestnanci
where Plat

--Datum první objednývky jednotlivých zákazníků
select jmeno,prijmeni,MIN(datum_prijeti)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
group by jmeno,prijmeni;

--Zákazníci a poslední objednávky ve tvaru dd.mm.rrrr
select jmeno,prijmeni,convert(varchar,max(datum_prijeti),104)
from Zakaznik ZK join Objednavky O
on ZK.ID_zakaznika=O.ID_zakaznika
group by jmeno,prijmeni;

--Kolik měsíců uplynulo mezi nástupem prvního a posledního zaměstnance
select DATEDIFF(mm,min(datum_nastupu),MAX(datum_nastupu))
from Zamestnanci;
Přidat komentář

Kam dál