// SQL exercise with solutions (to be run on MySQL)


table: ships (國家,戰艦)
table: battles (戰役,戰艦,狀態)

國家:	O,W,X,Y,Z
戰艦:	shipA,shipB,...
戰役:	B1,B2,B3,B4
狀態:	良好 OK
	損毀 Damaged
	沉沒 Sunk

create table ships(
	country	char(1),
	ship	char(5)
);

insert into ships (country,ship) values 
("W","shipJ"), 
("W","shipK"), 
("W","shipM"), 
("W","shipN"), 
("X","shipA"), 
("X","shipB"), 
("X","shipC"), 
("X","shipD"), 
("Y","shipP"), 
("Y","shipQ"), 
("Y","shipR"), 
("Y","shipS"), 
("Y","shipT"), 
("Z","shipE"), 
("Z","shipF"), 
("Z","shipG"), 
("Z","shipH"), 
("O","shipO");

create table battles (
	battle	char(2),
	ship	char(5),
	status	char(7)
);

insert into battles values ("B1","shipA","s");
insert into battles values ("B1","shipB","d");
insert into battles values ("B1","shipC","ok");
insert into battles values ("B1","shipD","d");
insert into battles values ("B1","shipP","s");
insert into battles values ("B1","shipQ","d");
insert into battles values ("B1","shipS","s");
insert into battles values ("B1","shipT","d");
insert into battles values ("B2","shipP","d");
insert into battles values ("B2","shipQ","s");
insert into battles values ("B2","shipR","ok");
insert into battles values ("B2","shipS","d");
insert into battles values ("B2","shipT","s");
insert into battles values ("B2","shipF","s");
insert into battles values ("B2","shipG","s");
insert into battles values ("B2","shipH","d");
insert into battles values ("B3","shipA","d");
insert into battles values ("B3","shipB","s");
insert into battles values ("B3","shipC","d");
insert into battles values ("B3","shipD","s");
insert into battles values ("B3","shipQ","s");
insert into battles values ("B3","shipR","s");
insert into battles values ("B3","shipS","s");
insert into battles values ("B3","shipT","s");
insert into battles values ("B3","shipE","ok");
insert into battles values ("B3","shipF","d");
insert into battles values ("B3","shipG","s");
insert into battles values ("B3","shipH","ok");
insert into battles values ("B4","shipA","ok");
insert into battles values ("B4","shipB","s");
insert into battles values ("B4","shipC","s");
insert into battles values ("B4","shipD","d");
insert into battles values ("B4","shipE","s");
insert into battles values ("B4","shipF","d");
insert into battles values ("B4","shipG","ok");
insert into battles values ("B4","shipH","ok");

update battles set status="OK"      where status="ok";
update battles set status="Damaged" where status="d";
update battles set status="Sunk"    where status="s";

* Write SQL statements for the following queries:
寫SQL語句，執行以下查詢


(1) total number of ships involved in each battle
每場戰役中，參與戰艦數目
battle	count(*)
B1	8
B2	8
B3	12
B4	8

(2) number of ships involved in each battle for each country
每場戰役中，每個參與國家名稱，及戰艦數目
battle country nShips
B1 X 4
B1 Y 4
B2 Y 5
B2 Z 3
B3 X 4
B3 Y 4
B3 Z 4
B4 X 4
B4 Z 4

(3) number of countries involved in each battle
每場戰役中，參與國家數目
battle nCountry
B1 2
B2 2
B3 3
B4 2

(4) count the no.of sunk, damaged & survived ships for each country
in different battles 每場戰役中，參與國家的每艘戰艦狀況及數目
battle country status count(*)
B1 X Damaged 2		B3 X Sunk    2
B1 X OK	     1		B3 Y Sunk    4
B1 X Sunk    1		B3 Z Damaged 1
B1 Y Damaged 2		B3 Z OK      2
B1 Y Sunk    2		B3 Z Sunk    1
B2 Y Damaged 2		B4 X Damaged 1
B2 Y OK      1		B4 X OK      1
B2 Y Sunk    2		B4 X Sunk    2
B2 Z Damaged 1		B4 Z Damaged 1
B2 Z Sunk    2		B4 Z OK      2
B3 X Damaged 2		B4 Z Sunk    1

(5) which country has not involved in any battle
沒有參與任何戰役的國家
country
W
O

(6) total number of ships (not)sunk in each battle
每個戰役，(未)沉沒船隻的總數
battle nSunk
B1 3
B2 4
B3 7
B4 3

(7) number of battles involved for each country
每個國家參與戰役的數目
country nBattles
X 3
Y 3
Z 3
O 0
W 0

(8) total number of ships sunk (not sunk) for each country in each battle
每個戰役，每個國家，沉沒船隻的總數
battle country nSunk
B1 X 1
B1 Y 2
B2 Y 2
B2 Z 2
B3 X 2
B3 Y 4
B3 Z 1
B4 X 2
B4 Z 1

(9) name of ships which have never sunk in any battle
從未在任何戰役中，沉沒船隻的名稱
shipH

(10) the countries of which some ships have not sunk in battle "B3"
哪些國家，在戰役B3中，有至少一艘船隻，沒有沉沒?
X
Z

(11) name of ships which sank in each battle 每個戰役，沉沒船隻的名稱
battle country ship nSunk
B1 X shipA	B3 X shipB	B4 X shipC	B3 X shipD
B1 Y shipP	B2 Y shipQ	B3 Y shipR	B1 Y shipS
B2 Y shipT	B4 Z shipE	B2 Z shipF	B2 Z shipG

(12) the country of which all ships have sunk in battle "B3"
哪些國家，在戰役B3 中，全部船隻沉沒?
Y

(13) find the total (average) score of each country in each battle 計算總分
* according to the following rules: 遊戲規則
* 2 points 良好 OK
* 1 point  損毀 Damaged
* 0 point  沉沒 Sunk

country battle total
X B1 4		X B3 2		X B4 3
Y B1 2		Y B2 4		Y B3 0
Z B2 1		Z B3 5		Z B4 5