· 6 years ago · May 13, 2019, 07:54 AM
1drop table IF EXISTS `zespoly`;
2drop table IF EXISTS `funkcje`;
3drop table IF EXISTS `projekty`;
4drop table IF EXISTS `pracownicy`;
5
6
7Create table `pracownicy` (
8 `prac_id` Int NOT NULL AUTO_INCREMENT,
9 `imie` Varchar(20) NOT NULL,
10 `nazwisko` Varchar(30) NOT NULL,
11 `data_ur` Date NOT NULL,
12 `pesel` Decimal(11,0) NOT NULL,
13 `zarobki` Decimal(10,2) NOT NULL DEFAULT 2500,
14 `plec` Enum('M', 'F'),
15 `szef_id` Int,
16 UNIQUE (`pesel`),
17 Primary Key (`prac_id`)) ENGINE = InnoDB;
18
19Create table `projekty` (
20 `proj_id` Int NOT NULL AUTO_INCREMENT,
21 `kierownik_id` Int NOT NULL,
22 `nazwa` Varchar(200) NOT NULL,
23 `kod` Varchar(20) NOT NULL,
24 `data_rozp` Date NOT NULL,
25 `data_zak` Date,
26 UNIQUE (`nazwa`),
27 UNIQUE (`kod`),
28 Primary Key (`proj_id`)) ENGINE = InnoDB;
29
30Create table `funkcje` (
31 `funkcja_id` Int NOT NULL AUTO_INCREMENT,
32 `nazwa` Varchar(100) NOT NULL,
33 UNIQUE (`nazwa`),
34 Primary Key (`funkcja_id`)) ENGINE = InnoDB;
35
36Create table `zespoly` (
37 `prac_id` Int NOT NULL,
38 `proj_id` Int NOT NULL,
39 `data_rozp` Date NOT NULL,
40 `data_zak` Date,
41 `funkcja_id` Int NOT NULL,
42 Primary Key (`prac_id`,`proj_id`)) ENGINE = InnoDB;
43
44Alter table `pracownicy` add Constraint `Relationship4` Foreign Key (`szef_id`) references `pracownicy` (`prac_id`) on delete restrict on update restrict;
45Alter table `projekty` add Constraint `Relationship5` Foreign Key (`kierownik_id`) references `pracownicy` (`prac_id`) on delete restrict on update restrict;
46Alter table `zespoly` add Constraint `Relationship6` Foreign Key (`prac_id`) references `pracownicy` (`prac_id`) on delete restrict on update restrict;
47Alter table `zespoly` add Constraint `Relationship7` Foreign Key (`proj_id`) references `projekty` (`proj_id`) on delete restrict on update restrict;
48Alter table `zespoly` add Constraint `Relationship8` Foreign Key (`funkcja_id`) references `funkcje` (`funkcja_id`) on delete restrict on update restrict;
49
50
51INSERT INTO funkcje (nazwa) VALUES ('Anal Blaster');
52INSERT INTO funkcje (nazwa) VALUES ('Master of Disaster');
53INSERT INTO funkcje (nazwa) VALUES ('Manager');
54INSERT INTO funkcje (nazwa) VALUES ('CEO');
55INSERT INTO funkcje (nazwa) VALUES ('CFO');
56INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Anna','Karenina', '1980-12-01', 1111111111, 10000, 'F', NULL );
57INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Unga','Bunga', '1900-12-01', 1111111112, 8000, 'F', 1 );
58INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Tajga','Tundra', '1980-11-11', 1111111113, 8000, 'F', 1);
59INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Janusz','Cebula', 1942-12-13', 1111111114, 8000, 'M',1 );
60INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Kristod','Jamik', '1943-12-11', 1111111115, 4000, 'M',1 );
61INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Juma','Niespi', '1944-12-01', 1111111116, 4000, 'F',2 );
62INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Welt','Shmertz', '1945-02-01', 1111111117, 4000, 'M',2 );
63INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Agna','Dupa', '1965-12-01', 1111111118, 4000, 'F',2 );
64INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Umbra','Prime', '1947-02-01', 1111111119, 4000, F',2 );
65INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Krystyna','Czubowna', '1987-07-26', 1111111110, 3500, 'F',3);
66INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Klimat','Nietaki', '1930-02-09', 1111111124, 3500, 'M',3 );
67INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Krowa','Niekobieta', '1920-12-01', 1111111134, 3500, 'F',3);
68INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Nadia','Imprezka', '1910-12-01', 1111111144, 35000, 'F',3);
69INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Wladzimir','Kononowicz', '1984-12-01', 1111111154, 3500, 'M',4 );
70INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Dua','NieMaLipy', '1964-12-01', 1111111164, 3000.21, 'F',4 );
71INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Switek','Chrystus', '1978-03-01', 1111111174, 3000, 'M',4 );
72INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Aga','Potrzeba', '1996-12-01', 1111111194, 3000, 'F',4 );
73INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Izmir','AllahuAkbar', '1999-11-01', 1111211114, 3000, 'M',5 );
74INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Louise','Sruiz', '1994-07-15', 1171111114, 3000, 'M',5 );
75INSERT INTO pracownicy (imie, nazwisko, data_ur, pesel, zarobki, plec, szef_id ) VALUES ('Mama','Tata', '1957-06-17', 1111811114, 2500, 'M',5 );
76
77
78INSERT INTO projekty (nazwa, kod, kierownik_id, data_rozp) VALUES ('Hallelujah','amunRa', 2, '2018-01-24');
79INSERT INTO projekty (nazwa, kod, kierownik_id, data_rozp) VALUES ('Apokalipsa','okon', 3, '2018-01-24');
80INSERT INTO projekty (nazwa, kod, kierownik_id, data_rozp) VALUES ('HujeMujeDzikieWeze','kod', 4, '2018-01-24');
81
82INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (1,1, '2010-01-25', 1);
83INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (2,1, '2010-01-29', 1);
84INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (3,1, '2010-01-25', 1);
85INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (4,1, '2010-01-26', 5);
86INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (5,1, '2010-02-01', 4);
87INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (6,1, '2010-02-14', 3);
88INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (7,1, '2010-02-13', 1);
89INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (8,1, '2010-03-03', 2);
90INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (9,1, '2010-03-05', 2);
91INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (10,1, '2010-03-10', 1);
92INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (11,1, '2010-03-11', 5);
93INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (12,2, '2012-01-25', 1);
94INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (13,2, '2012-07-14', 2);
95INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (14,2, '2013-11-20', 3);
96INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (15,2, '2014-09-03', 4);
97INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (16,3, '2015-04-13', 5);
98INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (17,3, '2015-02-05', 2);
99INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (18,3, '2015-02-05', 3);
100INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (19,3, '2016-01-02', 4);
101INSERT INTO zespoly(prac_id, proj_id, data_rozp, funkcja_id) VALUES (20,3, 2016-01-19', 4);
102
103
104alter table projekty
105add column poziom_trudnosci ENUM('latwy', 'sredni', 'trudny')
106NOT NULL CHECK(poziom_trudnosci IN('latwy', 'sredni','trudny'));
107
108
109create or replace view zamowienia_view AS
110select c.name as Klient, p.name as Produkt,
111 p.suggested_price as 'Cena jedn', i.quantity as Ilosc, i.quantity * p.suggested_price as Razem
112 from customer c , ord o, item i, product p
113 where c.id = o.customer_id AND o.id = i.ord_id AND i.product_id = p.id;
114
115
116select * from zamowienia_view
117where razem > 10000
118order by Klient;
119
120
121
122CREATE OR REPLACE VIEW emp_desc_view AS
123SELECT
124e.first_name, e.last_name, d.name as Departament , r.name as Region
125FROM
126emp e, dept d, region r
127WHERE
128e.dept_id = d.id AND r.id = d.region_id