Database schema:

CREATE TABLE "Air Carriers" (
	"Code" INTEGER -- example: ['19031', '19032'],
	"Description" TEXT -- example: ['Munz Northern Airlines Inc.: XY', 'Mackey International Inc.: MAC'],
	PRIMARY KEY ("Code")
)


CREATE TABLE "Airlines" (
	"FL_DATE" TEXT -- example: ['2018/8/2', '2018/8/1'],
	"OP_CARRIER_AIRLINE_ID" INTEGER -- example: ['19805', '20427'],
	"TAIL_NUM" TEXT -- example: ['N956AN', 'N973AN'],
	"OP_CARRIER_FL_NUM" INTEGER -- example: ['1588', '1587'],
	"ORIGIN_AIRPORT_ID" INTEGER -- example: ['12478', '14107'],
	"ORIGIN_AIRPORT_SEQ_ID" INTEGER -- example: ['1247805', '1410702'],
	"ORIGIN_CITY_MARKET_ID" INTEGER -- example: ['31703', '30466'],
	"ORIGIN" TEXT -- example: ['PHX', 'JFK'],
	"DEST_AIRPORT_ID" INTEGER -- example: ['11618', '14107'],
	"DEST_AIRPORT_SEQ_ID" INTEGER -- example: ['1161802', '1410702'],
	"DEST_CITY_MARKET_ID" INTEGER -- example: ['31703', '30466'],
	"DEST" TEXT -- example: ['EWR', 'PHX'],
	"CRS_DEP_TIME" INTEGER -- example: ['1512', '1640'],
	"DEP_TIME" INTEGER -- example: ['1541', '1649'],
	"DEP_DELAY" INTEGER -- example: ['9', '29'],
	"DEP_DELAY_NEW" INTEGER -- example: ['9', '29'],
	"ARR_TIME" INTEGER -- example: ['2006', '2350'],
	"ARR_DELAY" INTEGER -- example: ['53', '44'],
	"ARR_DELAY_NEW" INTEGER -- example: ['53', '44'],
	"CANCELLED" INTEGER -- example: ['0', '1'],
	"CANCELLATION_CODE" TEXT -- example: ['A', 'B'],
	"CRS_ELAPSED_TIME" INTEGER -- example: ['342', '285'],
	"ACTUAL_ELAPSED_TIME" INTEGER -- example: ['377', '309'],
	"CARRIER_DELAY" INTEGER -- example: ['9', '0'],
	"WEATHER_DELAY" INTEGER -- example: ['0', '2'],
	"NAS_DELAY" INTEGER -- example: ['53', '35'],
	"SECURITY_DELAY" INTEGER -- example: ['0', '42'],
	"LATE_AIRCRAFT_DELAY" INTEGER -- example: ['0', '6'],
	CONSTRAINT fk_airlines_airports FOREIGN KEY("ORIGIN") REFERENCES "Airports" ("Code"),
	CONSTRAINT fk_airlines_airports FOREIGN KEY("DEST") REFERENCES "Airports" ("Code"),
	FOREIGN KEY("OP_CARRIER_AIRLINE_ID") REFERENCES "Air Carriers" ("Code")
)


CREATE TABLE "Airports" (
	"Code" TEXT -- example: ['03A', '01A'],
	"Description" TEXT -- example: ['Granite Mountain, AK: Bear Creek Mining Strip', 'Afognak Lake, AK: Afognak Lake Airport'],
	PRIMARY KEY ("Code")
)

Question: How many flights from American Airlines were cancelled due to a type A cancellation code?
Evidence: American Airlines refers to Description = 'American Airlines Inc.: AA'; cancelled refers to Cancelled = 1; cancelled due to type A cancellation code refers to CANCELLATION_CODE = 'A';

SQL:
SELECT COUNT(*) FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.CANCELLATION_CODE = 'A' AND T2.Description = 'American Airlines Inc.: AA' AND T1.CANCELLED = 1