
CREATE TABLE "Claims" (
	"Claim_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	PRIMARY KEY ("Claim_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Customer_Policies" ("Policy_ID")
)


CREATE TABLE "Customer_Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_Type_Code" CHAR(15) NOT NULL, 
	"Start_Date" DATE, 
	"End_Date" DATE, 
	PRIMARY KEY ("Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID")
)


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Customer_ID")
)


CREATE TABLE "Payments" (
	"Payment_ID" INTEGER NOT NULL, 
	"Settlement_ID" INTEGER NOT NULL, 
	"Payment_Method_Code" VARCHAR(255), 
	"Date_Payment_Made" DATE, 
	"Amount_Payment" INTEGER, 
	PRIMARY KEY ("Payment_ID"), 
	FOREIGN KEY("Settlement_ID") REFERENCES "Settlements" ("Settlement_ID")
)


CREATE TABLE "Settlements" (
	"Settlement_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	"Customer_Policy_ID" INTEGER NOT NULL, 
	PRIMARY KEY ("Settlement_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claims" ("Claim_ID")
)

CREATE TABLE "Claims" (
	"Claim_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	PRIMARY KEY ("Claim_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Customer_Policies" ("Policy_ID")
)

/*
2 rows from Claims table:
Claim_ID	Policy_ID	Date_Claim_Made	Date_Claim_Settled	Amount_Claimed	Amount_Settled
143	744	2017-03-11	2017-11-03	43884	1085
423	552	2016-08-12	2018-01-27	79134	1724
*/


CREATE TABLE "Customer_Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_Type_Code" CHAR(15) NOT NULL, 
	"Start_Date" DATE, 
	"End_Date" DATE, 
	PRIMARY KEY ("Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID")
)

/*
2 rows from Customer_Policies table:
Policy_ID	Customer_ID	Policy_Type_Code	Start_Date	End_Date
119	1	Car	2018-01-21	2017-12-15
141	2	Life	2017-08-21	2017-09-29
*/


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Customer_ID")
)

/*
2 rows from Customers table:
Customer_ID	Customer_Details
1	America Jaskolski
2	Ellsworth Paucek
*/


CREATE TABLE "Payments" (
	"Payment_ID" INTEGER NOT NULL, 
	"Settlement_ID" INTEGER NOT NULL, 
	"Payment_Method_Code" VARCHAR(255), 
	"Date_Payment_Made" DATE, 
	"Amount_Payment" INTEGER, 
	PRIMARY KEY ("Payment_ID"), 
	FOREIGN KEY("Settlement_ID") REFERENCES "Settlements" ("Settlement_ID")
)

/*
2 rows from Payments table:
Payment_ID	Settlement_ID	Payment_Method_Code	Date_Payment_Made	Amount_Payment
384	516	MasterCard	2018-02-16	241730
435	476	MasterCard	2017-05-28	448613
*/


CREATE TABLE "Settlements" (
	"Settlement_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	"Customer_Policy_ID" INTEGER NOT NULL, 
	PRIMARY KEY ("Settlement_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claims" ("Claim_ID")
)

/*
2 rows from Settlements table:
Settlement_ID	Claim_ID	Date_Claim_Made	Date_Claim_Settled	Amount_Claimed	Amount_Settled	Customer_Policy_ID
357	486	2018-08-07	2018-01-16	38543	1181	515
412	621	2017-08-27	2018-02-04	57669	1427	617
*/
How many settlements does each claim correspond to? List the claim id and the number of settlements.
SELECT T1.Claim_id ,  count(*) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id
[{'Claim_ID': 486, 'count(*)': 2}, {'Claim_ID': 563, 'count(*)': 1}, {'Claim_ID': 569, 'count(*)': 1}, {'Claim_ID': 571, 'count(*)': 1}, {'Claim_ID': 621, 'count(*)': 2}, {'Claim_ID': 761, 'count(*)': 2}, {'Claim_ID': 801, 'count(*)': 4}, {'Claim_ID': 843, 'count(*)': 1}, {'Claim_ID': 957, 'count(*)': 1}]