H3A8D675

1drop database if exists equestria;
2
3create database equestria;
4	
5use equestria;
6
7create table państwo (
8id_miasto int not null auto_increment,
9miasto varchar(30),
10primary key(id_miasto) 
11);
12
13insert into  państwo values
14(1,'Canterlot'),
15(2,'Ponyville');
16
17create table rasa (
18id_nazwa_rasy int not null auto_increment,
19nazwa_rasy varchar(30),
20primary key(id_nazwa_rasy)
21 );
22insert into rasa values
23(1,'alicorn'),
24(2,'jednorożec'),
25(3,'pegaz'),
26(4,'ziemny');
27
28create table kucyk (
29id_kucyk int not null auto_increment,
30id_nazwa_rasy int not null,
31imie varchar(30),
32kolor varchar(30),
33id_miasto int not null,
34primary key(id_kucyk),
35FOREIGN KEY (id_miasto) REFERENCES państwo(id_miasto),
36foreign key (id_nazwa_rasy) references rasa(id_nazwa_rasy)
37 );
38insert into kucyk values
39(1,4,'Apple Jack','pomarańczowy',2),
40(2,4,'Pinkie Pie','różowy',2),
41(3,3,'Rainbow Dash','niebieski',2),
42(4,3,'Fluttershy','żółty',2),
43(5,2,'Twilight Sparkle','fioletowy',2),
44(6,2,'Rarity','biały',2),
45(7,1,'Celestia','biały',1),
46(8,1,'Luna','niebieski',1),
47(9,1,'Golden Shadow','złoty',1);
48
49create table kończyny (
50id_kończyny int auto_increment,
51id_nazwa_rasy int,
52róg varchar(30),
53skrzydła varchar(30),
54primary key(id_kończyny),
55foreign key(id_nazwa_rasy) references rasa (id_nazwa_rasy)
56);
57
58insert into kończyny values
59(1,4,'nie','nie'),
60(2,3,'nie','tak'),
61(3,2,'tak','nie'),
62(4,1,'tak','tak');
63
64-- 1. sprawdamy czy tabela kucyk istnieje
65describe kucyk;
66
67-- 2. wyświetlamy tabele w której podana jest id kucyka imie kolor rasa słownie i miasto słownie
68select id_kucyk, imie, kolor, rasa.nazwa_rasy, państwo.miasto
69from kucyk
70INNER JOIN rasa ON kucyk.id_nazwa_rasy = rasa.id_nazwa_rasy
71INNER JOIN państwo ON kucyk.id_miasto = państwo.id_miasto
72;
73
74-- 3. sprawdzamy  które rasy mają róg
75SELECT  rasa.nazwa_rasy
76FROM kończyny
77INNER JOIN rasa ON kończyny.id_nazwa_rasy = rasa.id_nazwa_rasy
78where róg = 'tak';
79
80-- 4. wyświetlamy liste kucyków mieszkających w canterlot
81 select imie , państwo.miasto
82 from kucyk
83 INNER JOIN państwo ON kucyk.id_miasto = państwo.id_miasto
84 where państwo.miasto= 'canterlot' ;
85
86-- 5. wyświetlamy liste kucyków pegazów
87 select imie , rasa.nazwa_rasy
88 from kucyk
89 INNER JOIN rasa ON kucyk.id_nazwa_rasy = rasa.id_nazwa_rasy
90 where rasa.nazwa_rasy ='pegaz';
91 
92 -- 6. pokazuje kture kucyki mie mają róg lub mają skrzydła
93SELECT  rasa.nazwa_rasy , róg , skrzydła
94FROM kończyny
95INNER JOIN rasa ON kończyny.id_nazwa_rasy = rasa.id_nazwa_rasy
96where skrzydła= 'tak'
97union
98SELECT  rasa.nazwa_rasy , róg , skrzydła
99FROM kończyny
100INNER JOIN rasa ON kończyny.id_nazwa_rasy = rasa.id_nazwa_rasy
101where róg= 'nie';
102
103-- 7. poprawienie zamieszkania kucyka '9' z '1' na '2'
104UPDATE kucyk
105SET id_miasto=2
106WHERE id_kucyk=9;
107select imie from kucyk
108 where id_miasto = 1;
109
110-- 8. usunięcie mieszkanca
111DELETE FROM kucyk
112WHERE id_kucyk=9 ;
113select * from kucyk;
114
115-- 9. grupuje miejsca zamieszkania kucyków 
116SELECT  państwo.miasto
117FROM kucyk
118INNER JOIN państwo ON kucyk.id_miasto = państwo.id_miasto
119GROUP BY państwo.id_miasto ;
120
121-- 10.  dołączenie kolumny do istniejącej tabeli 
122ALTER TABLE kucyk
123ADD pupil varchar(30);
124select * from kucyk ;
125
126-- 11. dodanie danych o pupilach do kolumny pupil
127
128UPDATE kucyk
129SET pupil ='tak'
130where id_miasto=2;
131UPDATE kucyk
132SET pupil ='nie'
133where id_miasto=1;
134select pupil from kucyk;
135
136-- 12. podzapytanie
137
138SELECT  nazwa_rasy, imie, kolor
139FROM kucyk
140INNER JOIN rasa ON kucyk.id_nazwa_rasy = rasa.id_nazwa_rasy 
141where nazwa_rasy= 'jednorożec'
142in
143 (
144 select nazwa_rasy
145 from kończyny
146 where róg = 'tak'
147 )
148;