r9eGWCaY

1ALTER TABLE student_grupa
2	ADD CONSTRAINT student_fk2
3		FOREIGN KEY (student_id)
4		REFERENCES student (student_id),
5	ADD CONSTRAINT group_fk2
6		FOREIGN KEY (group_id)
7		REFERENCES grupa (group_id);
8		
9		
10		ALTER TABLE reservation
11ADD CONSTRAINT CHK_PersonAge CHECK (duaration<8)
12		
13		
14		INSERT INTO ship (ship_id, cost,capacity,max_person) VALUES (1,222,23,3)
15		
16
17
18 
19 
20
21CREATE TABLE IF NOT EXISTS client(
22    client_id int,
23    name TEXT,
24    last_name TEXT,
25    CONSTRAINT client
26    PRIMARY KEY (client_id)
27    )
28	
29	
30	ALTER TABLE client
31ADD pole6 INT
32CHECK (pole6<8)
33
34
35	
36	CREATE TABLE IF NOT EXISTS service(
37    service_id int,
38    name TEXT,
39    cost float,
40    duration int,
41    CONSTRAINT service
42    PRIMARY KEY (service_id)
43    )
44	
45	CREATE TABLE IF NOT EXISTS employee(
46    employee_id int,
47    service_id int,
48    name TEXT,
49    last_name TEXT,
50    salary float,
51    CONSTRAINT employee
52    PRIMARY KEY (employee_id),
53    FOREIGN KEY (service_id) REFERENCES service(service_id)
54    )
55	
56	CREATE TABLE IF NOT EXISTS reservation(
57    reservation_id int,
58    client_id int,
59    employee_id int,
60	date_of_reservation DATE,
61    CONSTRAINT reservation
62    PRIMARY KEY (reservation_id),
63    FOREIGN KEY (client_id) REFERENCES client(client_id),
64    FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
65    )
66	
67	
68	//WYpełniam czymś baze żeby cos było
69	INSERT INTO client (client_id,name,last_name) VALUES (1,'Tomasz','Nowak')
70	INSERT INTO client (client_id,name,last_name) VALUES (2,'Jan','Kowalski')
71	INSERT INTO client (client_id,name,last_name) VALUES (3,'Stanisałw','Kowal')
72	INSERT INTO client (client_id,name,last_name) VALUES (4,'Tomasz','Ogór')
73	
74	INSERT INTO employee (employee_id,name,last_name,salary) VALUES (1,'Praconwik1','Pracownik1',2000.42)
75	INSERT INTO employee (employee_id,name,last_name,salary) VALUES (2,'Praconwik2','Pracownik2',3000.42)
76	INSERT INTO employee (employee_id,name,last_name,salary) VALUES (3,'Praconwik3','Pracownik3',4000.42)
77	INSERT INTO employee (employee_id,service_id,name,last_name,salary) VALUES (4,1,'Praconwik1','Pracownik1',2000.42)
78		
79	
80	INSERT INTO service (service_id,name,cost,duration) VALUES (1,'usluga1',100,10)
81	INSERT INTO service (service_id,name,cost,duration) VALUES (2,'usluga2',200,10)
82	INSERT INTO service (service_id,name,cost,duration) VALUES (3,'usluga3',300,10)
83	
84	
85	// 3.
86	
87	INSERT INTO reservation (reservation_id,client_id,employee_id,date_of_reservation) VALUES (1,1,1,'2019-04-05 08:02:02')
88	INSERT INTO reservation (reservation_id,client_id,employee_id,date_of_reservation) VALUES (2,1,2,'2019-04-05 08:02:02')
89	//4.
90	
91	
92	//5.
93
94SELECT employee.name,employee.salary, service.name
95FROM employee //Tutaj co ma wyświetlić wszsytskich jakie są
96LEFT JOIN service ON employee.service_id=service.service_id; //tu musze JOIN service bo tam wżaiłem z pracownika to tu mosze ta drugą 
97	
98//albo lepiej
99SELECT employee.name,employee.salary, service.name
100FROM employee 
101JOIN service ON employee.service_id=service.service_id; 
102	
103	
104	
105	
106			4. Wybierz imiona i nazwiska klientów, którzy zamawiali ciągle tą samą usługę
107		 SELECT DISTINCT client.name, client.last_name, service.name FROM client 
108 JOIN reservation ON client.client_id=reservation.client_id 
109 JOIN employee ON reservation.employee_id=employee.employee_id 
110 JOIN service ON employee.service_id=service.service_id
111	
112	
113	
114	
115	
116	//inne
117	UPDATE employee SET service_id=1 WHERE employee_id=1