
CREATE TABLE battle (
	id INTEGER, 
	name TEXT, 
	date TEXT, 
	bulgarian_commander TEXT, 
	latin_commander TEXT, 
	result TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE death (
	caused_by_ship_id INTEGER, 
	id INTEGER, 
	note TEXT, 
	killed INTEGER, 
	injured INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(caused_by_ship_id) REFERENCES ship (id)
)


CREATE TABLE ship (
	lost_in_battle INTEGER, 
	id INTEGER, 
	name TEXT, 
	tonnage TEXT, 
	ship_type TEXT, 
	location TEXT, 
	disposition_of_ship TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(lost_in_battle) REFERENCES battle (id)
)

CREATE TABLE battle (
	id INTEGER, 
	name TEXT, 
	date TEXT, 
	bulgarian_commander TEXT, 
	latin_commander TEXT, 
	result TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from battle table:
id	name	date	bulgarian_commander	latin_commander	result
1	Battle of Adrianople	14 April 1205	Kaloyan	Baldwin I	Bulgarian victory
2	Battle of Serres	June 1205	Kaloyan	Unknown	Bulgarian victory
*/


CREATE TABLE death (
	caused_by_ship_id INTEGER, 
	id INTEGER, 
	note TEXT, 
	killed INTEGER, 
	injured INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(caused_by_ship_id) REFERENCES ship (id)
)

/*
2 rows from death table:
caused_by_ship_id	id	note	killed	injured
1	1	Dantewada, Chhattisgarh	8	0
2	2	Dantewada, Chhattisgarh	3	0
*/


CREATE TABLE ship (
	lost_in_battle INTEGER, 
	id INTEGER, 
	name TEXT, 
	tonnage TEXT, 
	ship_type TEXT, 
	location TEXT, 
	disposition_of_ship TEXT, 
	PRIMARY KEY (id), 
	FOREIGN KEY(lost_in_battle) REFERENCES battle (id)
)

/*
2 rows from ship table:
lost_in_battle	id	name	tonnage	ship_type	location	disposition_of_ship
8	1	Lettice	t	Brig	English Channel	Captured
7	2	Bon Accord	t	Brig	English Channel	Captured
*/
List the name and date the battle that has lost the ship named 'Lettice' and the ship named 'HMS Atalanta'
SELECT T1.name ,  T1.date FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle WHERE T2.name  =  'Lettice' INTERSECT SELECT T1.name ,  T1.date FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle WHERE T2.name  =  'HMS Atalanta'
[{'name': 'Siege of Constantinople', 'date': '1235'}]