vgGF7F1W

1/*
2Created        17.05.2019
3Modified        17.05.2019
4Project        
5Model        
6Company        
7Author        
8Version        
9Database        mySQL 5 
10*/
11
12
13drop table IF EXISTS `Dzieci`;
14drop table IF EXISTS `Rodzice`;
15
16
17Create table `Rodzice` (
18    `Rodzic_ID` Int NOT NULL AUTO_INCREMENT,
19    `Imie` Varchar(20) NOT NULL,
20    `Nazwisko` Varchar(30) NOT NULL,
21    `Zarobki` Decimal(10,2) DEFAULT 0,
22    `PESEL` Decimal(11,0),
23    UNIQUE (`PESEL`),
24 Primary Key (`Rodzic_ID`)) ENGINE = InnoDB;
25
26Create table `Dzieci` (
27    `Dziecko_ID` Int NOT NULL AUTO_INCREMENT,
28    `Rodzic_ID` Int NOT NULL,
29    `Imie` Varchar(20) NOT NULL,
30    `Data_urodzenia` Date NOT NULL,
31    `Plec` Enum('M', 'K') NOT NULL,
32    `Czy_zasilek` Decimal(1,0) DEFAULT 0,
33 Primary Key (`Dziecko_ID`)) ENGINE = InnoDB;
34
35
36ALTER TABLE `Dzieci` add Constraint `rodzice_dzieci_FK` Foreign Key (`Rodzic_ID`) references `Rodzice` (`Rodzic_ID`) on delete  restrict on update  restrict;
37
38INSERT INTO Rodzice VALUES
39    (1, 'Sebastian', 'Korcz', 5000.00, 98071256789),
40    (2, 'Maciej', 'Kaluga', 4550.50, 98030578901),
41    (3, 'Jakub', 'Choroszczak', 6200.00, 97111112345);
42
43INSERT INTO Dzieci VALUES
44    (1, 1, 'Patryk', '2000-07-01', 'M', 0),
45    (2, 1, 'Karolina', '2002-12-05', 'K', 0),
46    (3, 2, 'Stefan', '2002-06-24', 'M', 1),
47    (4, 2, 'Hubert', '2004-10-17', 'M', 1),
48    (5, 2, 'Karol', '2007-01-14', 'M', 1),
49    (6, 2, 'Iza', '2009-06-29', 'K', 1),
50    (7, 3, 'Sebastian', '2011-05-10', 'M', 0),
51    (8, 3, 'Alicja', '2012-11-12', 'K', 0);
52
53SELECT 
54    r.Imie AS "Imiê rodzica", 
55    r.Nazwisko AS "Nazwisko rodzica", 
56    d.Imie AS "Imiê dziecka", 
57    d.Data_urodzenia AS "Data urodzenia dziecka", 
58    d.Plec as "P³eæ dziecka", 
59    d.Czy_zasilek AS "Czy pobiera zasi³ek" 
60FROM 
61    Rodzice AS r, 
62    Dzieci AS d
63WHERE
64    d.Rodzic_ID = r.Rodzic_ID;
65
66/* ------------------------------------------------------------------------------------------------------- */
67
68SELECT 
69    r.name AS "Nazwa regionu", 
70    SUM(i.max_in_stock - i.amount_in_stock) AS "Sumaryczna sprzeda¿" 
71FROM 
72    region AS r, 
73    inventory AS i,
74    warehouse AS w 
75WHERE 
76    i.warehouse_id = w.id 
77AND 
78    w.region_id = r.id 
79GROUP BY 
80    r.name 
81ORDER BY 
82    SUM(i.max_in_stock - i.amount_in_stock) 
83DESC;