Forum Mjukvara Programmering och digitalt skapande Tråd

Skapa databas

1 2 3 4
Skriv svar
Permalänk
Medlem ★
●

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;
Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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
Redigera
Citera flera Citera
Permalänk
Medlem
●
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);
Redigera
Citera flera Citera
Permalänk
Medlem ★
●
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

DROP VIEW inkomst, utgift;

Avsikten med "WITH ... SELECT" är just att slippa lagra vyer.

Om du i postgresql ger kommandot "\d" ser du vilka tabeller, vyer, sekvenser, mm som lagrats i databasen.

Släng alla vyer och använd WITH istället. Jag säger inte att det är ett bättre sätt, bara att man kan göra så för att slippa vyhanteringen.

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av Tryckvagen:

...
DÅ till frågan, TROR ni att det är möjligt för mig att lära mig skapa en sådan databas till 10 januari och hur avancerat är det egentligen?

Gå till inlägget

Hur gick det? Blev de impade av din mestadels kopierade lösning ?

Redigera
Citera flera Citera
1 2 3 4
Skriv svar