361EeiQn

1create database if not exists Bilete_20;
2
3use Bilete_20;
4
5drop table piegadataji;
6drop table preces;
7drop table Precu_piegade;
8
9create table if not exists piegadataji(
10piegadataja_id integer auto_increment primary key,
11piegadatajs char(40), 
12parstavjis char(40),
13talrunis char(15));
14
15create table if not exists preces (
16preces_id integer auto_increment primary key,
17P_raksturojums char(30), 
18izgat_gads integer, 
19cena float, 
20kategorija char(30));
21
22create table if not exists Precu_piegade (
23ID integer auto_increment primary key,
24daudzums integer,
25Piegadatajs char(40), 
26Prece char(30), 
27foreign key (Piegadatajs) references piegadataji (piegadataja_id) on delete cascade,
28foreign key (Prece) references preces (preces_id) on delete cascade
29);
30
31
32INSERT into piegadataji (piegadatajs, parstavjis, talrunis) VALUES
33("SIA Atea","Svetlana", "236-556-78"),
34("SIA Datoru apgads","Maris", "123-456-87"),
35("AS Electron","Sergejs", "296-369-15"),
36("SIA Datoru apgads","Maris", "123-456-87"),
37("AS Electron","Sergejs", "296-369-15"),
38("AS Electron","Sergejs", "296-369-15");
39
40
41INSERT into preces (P_raksturojums, izgat_gads, cena, kategorija) VALUES
42("Dators Intel IBM","2005","322","Datoru tehnika"),
43("Skeneris IBM","2010","54","Periferijas ierices"),
44("Printeris Intel","1987","45","Periferijas ierices"),
45("Windows 10 Microsoft","2010","120","Datora programmatura"),
46("Dators AM Duron","2000","250","Datoru tehnika"),
47("Dators Celeron","1999","150","Datoru tehnika");
48
49
50INSERT into Precu_piegade (daudzums, Piegadatajs, Prece) VALUES
51("5",1,1),
52("2",2,2),
53("3",3,3),
54("4",4,4),
55("6",5,5),
56("5",6,6);
57
58delimiter //
59CREATE FUNCTION min_price (kategorija char(30), piegadatajs char(40))
60begin
61select concat (
62"Prece: ",preces.P_raksturojums, 
63"  Cena: ", preces.cena, 
64"  Izgatavosanas gads: ", preces.izgat_gads, 
65"  Ekspluatacijas laiks: ", YEAR(CURDATE())-preces.izgat_gads as "Funkcijas rezultats" )
66FROM Precu_piegade
67	INNER JOIN Preces ON preces_id = Precu_piegade.Prece
68	INNER JOIN Piegadataji ON piegadataja_id = Precu_piegade.Piegadatajs
69WHERE (preces.kategorija = kategorija) AND (Piegadataji.piegadatajs = piegadatajs) AND (preces.cena = ( SELECT MIN(preces.cena) FROM Precu_piegade
70	INNER JOIN Preces ON preces_id = Precu_piegade.Prece
71	INNER JOIN Piegadataji ON piegadataja_id = Precu_piegade.Piegadatajs) ) ;
72END//
73
74
75select 
76piegadataji.piegadatajs as "Piegadatajs", 
77preces.P_raksturojums as "Precu raksturojums", 
78preces.izgat_gads as "Izgatavosanas gads", 
79preces.cena as "Cena par vienibu", 
80Precu_piegade.daudzums as "Daudzums", 
81preces.kategorija as "Kategorija",
82concat(piegadataji.parstavjis, ", ",piegadataji.talrunis) as "Piegadataja parstavjis"
83FROM Precu_piegade
84	INNER JOIN Preces ON preces_id = Precu_piegade.Prece
85	INNER JOIN Piegadataji ON piegadataja_id = Precu_piegade.Piegadatajs
86WHERE preces.cena = ( SELECT MIN(preces.cena) FROM Precu_piegade
87	INNER JOIN Preces ON preces_id = Precu_piegade.Prece
88	INNER JOIN Piegadataji ON piegadataja_id = Precu_piegade.Piegadatajs);
89
90
91
92a.vecums, p.stundu_skaits
93from audzekni as a inner join eksamenu_rezultati as ek on a.audzekna_id=ek.audzeknis
94inner join prieksmeti as p on p.prieksmeta_id=ek.prieksmets;
95
96desc piegadataji;
97desc preces;
98desc Precu_piegade;
99
100select * from piegadataji;
101select * from preces;
102select * from Precu_piegade;