Poslední úpravy - Vyhledat:

SQL

O modelování

Power Designer

Oracle Data Modeler

Zdroje...

edit SideBar

SQL /

SQL kódy řešení úloh

1.
   select LOG as ZAKAZNIK,EMAIL
   from ZAK
   where MEST like '%Praha%';
2.
   select NAZ as ZBOZI,JCEN*SKLAD as SKLAD_SUMA
   from ZBOZ
   where KAT='limo';
3.
   select ZAK
   from OBJ
   where DAT between to_date('01102008','ddmmyyyy') and to_date('30112008','ddmmyyyy');
4.
   select NAZ,MNOZ
   from POLOZ join ZBOZ using (KOD)
   where CISO=15;
5.
   select CISO,POZN
   from OBJ
   where POZN is not null;
6.
   select distinct NAZ
   from ZAK join OBJ on (LOG=ZAK) join POLOZ using (CISO) join ZBOZ using (KOD)
   where ZAK.JMEN like 'Bohuslav%' and ZAK.PRIJM like 'Rejholec%';
7.
   select KOD,NAZ
   from ZBOZ join KAT using (KAT)
   where NAZK like 'limonády%'
   and KOD not in 
     (select KOD
     from ZAK join OBJ on (LOG=ZAK) join POLOZ using (CISO)
     where ZAK.JMEN like 'Bohuslav%' and ZAK.PRIJM like 'Rejholec%')
   order by 1;
8.
   select KOD,NAZ
   from ZBOZ
   where KOD not in 
     (select KOD
     from ZAK join OBJ on (LOG=ZAK) join POLOZ using (CISO)
     where ZAK.MEST like '%Praha%')
   order by 1;
9.
   select CISO
   from OBJ
   where CISO not in 
     (select CISO
     from POLOZ join ZBOZ using (KOD) join KAT using (KAT)
     where NAZK like 'limonády%')
   order by 1;
10.
   select KAT,NAZK,KOD,NAZ
   from KAT left join ZBOZ using (KAT)
   order by 1,3;
11.
   select KAT,NAZ,JCEN
   from ZBOZ
   order by 2,3 DESC;
12.
   select KAT, count(KOD) as pocet_zb, avg(JCEN) as prum_jcena
   from KAT left join ZBOZ using (KAT)
   group by KAT;
13.
   select KAT.KAT, count(KOD) as pocet_akti, avg(JCEN) as prum_cena_akti
   from KAT left join ZBOZ on (KAT.KAT=ZBOZ.KAT and AKTI=1)
   group by KAT.KAT;
14.
   select NAD.KAT, count(POD.KAT) as POCET_PODRIZ
   from KAT NAD left join KAT POD ON (NAD.KAT=POD.NADR)
   group by NAD.KAT;
15.
   select KAT, NAZK 
   from KAT
   where KAT not in (select NADR from KAT where NADR is not null);
16.
   select NAD.KAT
   from KAT NAD join KAT POD ON (NAD.KAT=POD.NADR)
   group by NAD.KAT
   having count(*)>2;
17.
   select LOG
   from ZAK join OBJ ON (LOG=ZAK)
   group by LOG
   having count(*)>2;
18.
   select KOD
   from POLOZ
   group by KOD
   having count(*)>3;
19.
   select KOD
   from ZBOZ left join POLOZ using (KOD)
   group by KOD
   having count(CISO)<4;
20.
   select KOD
   from OBJ join POLOZ using (CISO)
   where DAT between '01012008' and '31122008'
   group by KOD
   having sum(VCEN)>200;
21.
   select KOD
   from OBJ join POLOZ on (OBJ.CISO=POLOZ.CISO and DAT between '01012008' and '31122008') right join ZBOZ using (KOD)
   group by KOD
   having sum(VCEN)<=200 or count(OBJ.CISO)=0
   order by 1;
22.
   select LOG, count(CISO) as POCET_OBJ
   from ZAK left join OBJ on (ZAK.LOG=OBJ.ZAK and DAT between '01012009' and '31122009')
   group by LOG;
Upravit - Historie - Tisk - Poslední úpravy - Vyhledat
Poslední úprava stránky: 09.12.2013, 14:41