Skapa databas

2017-01-06 10:02

Permalänk

serafim

Medlem ★

Registrerad: Feb 2015

●

Såg att en del fallit bort vilket gör att man bör få ett felmeddelande

Skrivet av serafim:

create view utgift as
select b.reg_nr, sum(k.summa) as kostnad
from bil b, kostnad k

Gå till inlägget

måste vara

create view utgift as
select b.reg_nr, sum(k.summa) as kostnad
from bil b, kostnad k
where b.id = k.bil_id
group by b.reg_nr;

Rapportera Redigera

Citera flera Citera

2017-01-06 10:23

Permalänk

serafim

Medlem ★

Registrerad: Feb 2015

●

Skrivet av serafim:

Lösningsförslag till några frågor.

Man kan skapa vyer genom

create view inkomst as
select b.reg_nr, sum(u.kostnad) as summa
from bil b, uthyrning u
where b.id = u.bil_id group by b.reg_nr;

och

create view utgift as
select b.reg_nr, sum(k.summa) as kostnad
from bil b, kostnad k
where b.id = k.bil_id
group by b.reg_nr

och då kan man hitta bilen som kostat minst med vyn utgift:

select * from utgift where kostnad =
(select min(kostnad) from utgift);

 reg_nr | kostnad 
--------+---------
 FTE543 | 5380.00

Man kan hitta bilen som ger största vinsten genom att använda båda vyerna:

select reg_nr, summa - kostnad as vinst
from inkomst natural join utgift
where summa - kostnad =
(select max (summa - kostnad) from
 inkomst natural join utgift);
 
 reg_nr |  vinst  
--------+---------
 FTE543 | 3145.00

Som koll på att man gjort rätt kan man använda båda vyerna så:

select reg_nr, summa as inkomst, kostnad, summa - kostnad as vinst
from inkomst natural join utgift;
 
 reg_nr | inkomst | kostnad |  vinst   
--------+---------+---------+----------
 FTE543 | 8525.00 | 5380.00 |  3145.00
 USB220 | 1697.00 | 7684.00 | -5987.00
 UUZ445 |  401.00 | 6591.00 | -6190.00

Och om man med "kostar minst" menar kostnad för kunden är det ju lätt
utan vyer:

select b.reg_nr, k.dygnspris, k.kilometerpris from
bil b, modell m, kategori k
where b.model_id = m.id and m.kategori_id = k.id
and k.dygnspris = (select min (dygnspris) from kategori);

 reg_nr | dygnspris | kilometerpris 
--------+-----------+---------------
 FTE543 |    299.00 |          2.00

Gå till inlägget

När man skapar vyer så lagras dessa i databasen och med många långa och krångliga vyer blir det mycket extra i databasen så, speciellt i applikationsprogram med mycket komplicerade frågeställningar brukar man istället använda "WITH" för att skapa konstruktioner som bara existerar under exekvering av SQL-satsen. Dessa, ofta svåröverblickbara, SQL-satser syns ju inte för användarna utan ligger gömda i programkoden så det gör inget att de är svåra att läsa och förstå för gemene man. Några av frågorna ovan blir då som följer.

Vilken bil har kostat företaget minst? :

with utgift as (select b.reg_nr, sum(k.summa) as kostnad
from bil b, kostnad k where b.id = k.bil_id
group by b.reg_nr)
select * from utgift where kostnad =
(select min(kostnad) from utgift); 

 reg_nr | kostnad 
--------+---------
 FTE543 | 5380.00

Vilken bil ger störst vinst? :

with inkomst as (select b.reg_nr, sum(u.kostnad) as summa
from bil b, uthyrning u
where b.id = u.bil_id group by b.reg_nr),
utgift as (select b.reg_nr, sum(k.summa) as kostnad
from bil b, kostnad k
where b.id = k.bil_id
group by b.reg_nr)
select reg_nr, summa - kostnad as vinst
from inkomst natural join utgift
where summa - kostnad =
(select max (summa - kostnad) from
 inkomst natural join utgift);
 reg_nr |  vinst  
--------+---------
 FTE543 | 3145.00

Och

Skrivet av serafim:

Frågan "Ta ut totalkostnad för en viss bränsletyp över en period" kan besvaras ganska enkelt genom att vara lite finurlig när man skapar en vy för bilar och deras bränsletyp:

create view bilbränsle as select b.id as bil_id, m.bränsle_typ
from bil b, modell m where b.model_id = m.id;

Jag har alltså döpt om bil.id till bil_id, samma namn som används i kostnadstabellen för att referera till bil.id. Då kan jag använda naturlig join för att kombinera mig fram till kostnaden för en viss bränsletyp. Alltså kan jag få svar på den mer explicita frågan "Vad var totalkostnaden för bensin under mars månad 2016?" med SQL-satsen:

select sum(summa) from kostnad natural join bilbränsle
where typ = 'Bränsle' and bränsle_typ = 'bensin'
and datum > '2016-02-29' and datum < '2016-04-01';

   sum   
---------
 6425.00

Gå till inlägget

Kan också besvaras med en "SELECT WITH"-sats:

with bilbränsle as (select b.id as bil_id, m.bränsle_typ
from bil b, modell m where b.model_id = m.id)
select sum(summa) from kostnad natural join bilbränsle
where typ = 'Bränsle' and bränsle_typ = 'bensin'
and datum > '2016-02-29' and datum < '2016-04-01';

   sum   
---------
 6425.00

Rapportera Redigera

Citera flera Citera (1)

2017-01-06 11:14

Permalänk

Tryckvagen

Medlem

Registrerad: Nov 2016

●

Skrivet av serafim:

Vilken bil ger störst vinst? :

with inkomst as (select b.reg_nr, sum(u.kostnad) as summa
from bil b, uthyrning u
where b.id = u.bil_id group by b.reg_nr),
utgift as (select b.reg_nr, sum(k.summa) as kostnad
from bil b, kostnad k
where b.id = k.bil_id
group by b.reg_nr)
select reg_nr, summa - kostnad as vinst
from inkomst natural join utgift
where summa - kostnad =
(select max (summa - kostnad) from
 inkomst natural join utgift);
 reg_nr |  vinst  
--------+---------
 FTE543 | 3145.00

Gå till inlägget

Jag får samma resultat om jag gör såhär:

select reg_nr, summa as inkomst, kostnad, summa - kostnad as vinst
from inkomst
natural join utgift
where summa - kostnad = (select max( summa - kostnad) from inkomst natural join utgift);

Rapportera Redigera

Citera flera Citera

2017-01-06 14:58

Permalänk

serafim

Medlem ★

Registrerad: Feb 2015

●

Skrivet av Tryckvagen:

Jag får samma resultat om jag gör såhär:

select reg_nr, summa as inkomst, kostnad, summa - kostnad as vinst
from inkomst
natural join utgift
where summa - kostnad = (select max( summa - kostnad) from inkomst natural join utgift);

Gå till inlägget

Men då har du väl kvar vy-definitionerna? Släng dem med