/***************************************************************************/
/*    Uebung 10                                                            */
/*                                                                         */
/*    Select-Anweisungen fuer Beispiel 1                                   */
/*                                                                         */
/*    Version 2  fuer MySQL                                                */
/*                                                                         */
/***************************************************************************/

create or replace view uebung10_1 as
select ANR, Bezeichnung
from Artikel
where Material != 'Metall';

select * from uebung10_1;

create or replace view uebung10_2 as
select COUNT(ANR) AS Anzahl
from Artikel
where Breite = 80 or Laenge = 80;

select * from uebung10_2;

create or replace view uebung10_3 as
select COUNT(ANR) AS Anzahl
from Artikel;

select * from uebung10_3;

create or replace view uebung10_4 as
select LNR, Count(ANR) AS Anzahl
from liefert
group by LNR;

select * from uebung10_4;

create or replace view uebung10_5a as
select LNR, Name
from Lieferant natural join liefert
group by LNR,Name
having COUNT(ANR) = (select COUNT(ANR) from Artikel);

select * from uebung10_5a;

create or replace view uebung10_5b as
select LNR, Name
from Lieferant natural join uebung10_4 natural join uebung10_3;

select * from uebung10_5b;

create or replace view uebung10_6 as
select LNR from Lieferant where Ort = 'Jena'
UNION
select LNR from uebung10_5a;

select * from uebung10_6
order by LNR DESC;

create or replace view uebung10_7 as
select LNR, Name, COUNT(ANR) as Anzahl_Artikel
from Lieferant left outer join Artikel using(LNR) group by LNR, Name;

select * from uebung10_7
order by LNR;

create or replace view uebung10_8 as
select CONCAT(L1.Name, ' fuer ', L2.Name) Ersatzlieferant
from Lieferant L1 inner join Lieferant L2 using(Ort)
where   L1.LNR != L2.LNR;

select * from uebung10_8;

create or replace view uebung10_9 as
select * from vendor
where lnr not in (select LNR from Lieferant);

select * from uebung10_9;

create or replace view uebung10_10a as
select Artikel.ANR, 'X' Artikel, 'X' Product from Artikel, product
where Artikel.ANR=product.ANR
union
select ANR, 'X' Artikel, ' ' Product from Artikel
where ANR not in (select ANR from product)
union
select ANR, ' ' Artikel, 'X' Product from product
where ANR not in (select ANR from Artikel);

select * from uebung10_10a
order by Artikel DESC, ANR ASC;

create or replace view uebung10_10b as
select ANR, 'X' Artikel, 'X' Product from Artikel inner join product using(ANR)
union
select Artikel.ANR, 'X' Artikel, ' ' Product from Artikel left outer join product on Artikel.ANR=product.ANR
where product.ANR is NULL
union
select product.ANR, ' ' Artikel, 'X' Product from Artikel right outer join product on Artikel.ANR=product.ANR
where Artikel.ANR is NULL;

select * from uebung10_10b
order by Artikel DESC, ANR ASC;
