/***************************************************************************/
/*    Uebung 9                                                             */
/*                                                                         */
/*    Select-Anweisungen fuer Beispiel 1                                   */
/*                                                                         */
/*    Version 3  fuer MySQL 5.1                                            */
/*                                                                         */
/***************************************************************************/
create or replace view uebung9_1 as (
select  distinct ANR 
from    liefert);

select * from uebung9_1;

create or replace view uebung9_2 as (
select LNR 
from   Lieferant 
where  Status > 20);

select * from uebung9_2;

create or replace view uebung9_3 as (
select LNR 
from   Lieferant 
where  Status > 20  and  Ort = 'Jena');

select * from uebung9_3;

create or replace view uebung9_4 as (
select   LNR, Status 
from     Lieferant 
where    Ort = 'Jena');

select * from uebung9_4 
order by Status DESC;

create or replace view uebung9_5 as (
Select Name,Lieferzeit 
from   liefert natural join Lieferant
where  ANR = 'T1'   
and    Ort = 'Jena'  
and    Lieferzeit <= 5);

select * from uebung9_5;

create or replace view uebung9_6 as (
select AVG(Status) Mittelwert_Status 
from   Lieferant);

select * from uebung9_6;

create or replace view uebung9_7 as (
select    Ort,AVG(Status) Mittelwert_Status 
from      Lieferant
group by  Ort);

select * from uebung9_7;

create or replace view uebung9_8 as (
select    Ort,AVG(Status) Mittelwert_Status
from      Lieferant
group by  Ort
having    Count(Status) > 1);

select * from uebung9_8;

create or replace view uebung9_9 as (
select AVG(Lieferzeit) Mittelwert_Lieferzeit
from   liefert natural join Lieferant
where  Ort = 'Jena');

select * from uebung9_9;

create or replace view uebung9_10 as (
Select LNR, Name
from   Lieferant
where  Name like '%e_');

select * from uebung9_10;

create or replace view uebung9_11 as (
Select LNR,Name
from   Lieferant
where  Status = ( select MIN(Status)
                  from   Lieferant));

select * from uebung9_11;

create or replace view uebung9_11a as (
select MIN(Status) as Status from  Lieferant);

select * from uebung9_11a;

create or replace view uebung9_11b as (
select LNR, Name from Lieferant natural join uebung9_11a);

select * from uebung9_11b;

create or replace view uebung9_12a as (
select Name, Ort from Lieferant natural join liefert
where ANR='T1');
  
select * from uebung9_12a;

create or replace view uebung9_12b as (
select Name, Ort
from   Lieferant
where  LNR  IN ( Select  LNR 
                 from    liefert
                 where   ANR='T1')) ;

select * from uebung9_12b;

create or replace view uebung9_13 as (
select Name, Status 
from   Lieferant 
where  LNR not in ( select  LNR 
                    from    liefert 
                    where   liefert.ANR = 'T1'));

select * from uebung9_13;

