Database schema:

CREATE TABLE "transaction" (
	"TransactionID" INTEGER -- example: ['100000', '100001'],
	"CreditCardNumber" INTEGER -- example: ['6011583832864739', '4716634257568793'],
	"CustomerID" INTEGER -- example: ['864896', '610766'],
	"TransactionDate" DATE -- example: ['2014-07-07', '2014-07-13'],
	"CreditCardType" TEXT -- example: ['Discover', 'Visa'],
	"LocationID" INTEGER -- example: ['2', '1'],
	"RootBeerID" INTEGER -- example: ['105798', '105661'],
	"PurchasePrice" REAL -- example: ['1.0', '3.0'],
	PRIMARY KEY ("TransactionID"), 
	CONSTRAINT fk_transaction_customers FOREIGN KEY("CustomerID") REFERENCES customers ("CustomerID"),
	CONSTRAINT fk_transaction_location FOREIGN KEY("LocationID") REFERENCES location ("LocationID"),
	CONSTRAINT fk_transaction_rootbeer FOREIGN KEY("RootBeerID") REFERENCES rootbeer ("RootBeerID")
)


CREATE TABLE customers (
	"CustomerID" INTEGER -- example: ['103508', '101811'],
	"First" TEXT -- example: ['Kenneth', 'Madeleine'],
	"Last" TEXT -- example: ['Walton', 'Jones'],
	"StreetAddress" TEXT -- example: ['6715 Commonwealth Dr', '3603 Leola Way'],
	"City" TEXT -- example: ['Sacramento', 'Orangevale'],
	"State" TEXT -- example: ['CA'],
	"ZipCode" INTEGER -- example: ['94256', '94258'],
	"Email" TEXT -- example: ['j_madeleine@gmail.com', 'walton.k76@fastmail.com'],
	"PhoneNumber" TEXT -- example: ['(916) 918-1561', '(916) 186-9423'],
	"FirstPurchaseDate" DATE -- example: ['2013-05-30', '2013-02-06'],
	"SubscribedToEmailList" TEXT -- example: ['FALSE', 'TRUE'],
	"Gender" TEXT -- example: ['M', 'F'],
	PRIMARY KEY ("CustomerID")
)


CREATE TABLE geolocation (
	"LocationID" INTEGER -- example: ['0', '1'],
	"Latitude" REAL -- example: ['0.0', '38.566129'],
	"Longitude" REAL -- example: ['0.0', '-121.426432'],
	PRIMARY KEY ("LocationID"), 
	CONSTRAINT fk_geolocation_location FOREIGN KEY("LocationID") REFERENCES location ("LocationID")
)


CREATE TABLE location (
	"LocationID" INTEGER -- example: ['0', '1'],
	"LocationName" TEXT -- example: ['Sac State American River Courtyard', 'LOST'],
	"StreetAddress" TEXT -- example: ['6000 J St'],
	"City" TEXT -- example: ['Sacramento'],
	"State" TEXT -- example: ['CA'],
	"ZipCode" INTEGER -- example: ['95819'],
	PRIMARY KEY ("LocationID"), 
	CONSTRAINT fk_location_geolocation FOREIGN KEY("LocationID") REFERENCES geolocation ("LocationID")
)


CREATE TABLE rootbeer (
	"RootBeerID" INTEGER -- example: ['100000', '100001'],
	"BrandID" INTEGER -- example: ['10002', '10001'],
	"ContainerType" TEXT -- example: ['Can', 'Bottle'],
	"LocationID" INTEGER -- example: ['2', '1'],
	"PurchaseDate" DATE -- example: ['2015-07-03', '2016-05-09'],
	PRIMARY KEY ("RootBeerID"), 
	CONSTRAINT fk_rootbeer_geolocation FOREIGN KEY("LocationID") REFERENCES geolocation ("LocationID"),
	CONSTRAINT fk_rootbeer_location FOREIGN KEY("LocationID") REFERENCES location ("LocationID"),
	CONSTRAINT fk_rootbeer_rootbeerbrand FOREIGN KEY("BrandID") REFERENCES rootbeerbrand ("BrandID")
)


CREATE TABLE rootbeerbrand (
	"BrandID" INTEGER -- example: ['10002', '10001'],
	"BrandName" TEXT -- example: ['A&W', 'A.J. Stephans'],
	"FirstBrewedYear" INTEGER -- example: ['1919', '1926'],
	"BreweryName" TEXT -- example: ['AJ Stephans Beverages', 'Dr Pepper Snapple Group'],
	"City" TEXT -- example: ['Fall River', 'Lodi'],
	"State" TEXT -- example: ['MA', 'CA'],
	"Country" TEXT -- example: ['Australia', 'United States'],
	"Description" TEXT -- example: ['After setting up the first A&W Root Beer stand in ', 'AJ Stephans Company makes \nthe finest elixirs and '],
	"CaneSugar" TEXT -- example: ['FALSE', 'TRUE'],
	"CornSyrup" TEXT -- example: ['FALSE', 'TRUE'],
	"Honey" TEXT -- example: ['FALSE', 'TRUE'],
	"ArtificialSweetener" TEXT -- example: ['FALSE', 'TRUE'],
	"Caffeinated" TEXT -- example: ['FALSE', 'TRUE'],
	"Alcoholic" TEXT -- example: ['FALSE'],
	"AvailableInCans" TEXT -- example: ['FALSE', 'TRUE'],
	"AvailableInBottles" TEXT -- example: ['FALSE', 'TRUE'],
	"AvailableInKegs" TEXT -- example: ['FALSE', 'TRUE'],
	"Website" TEXT -- example: ['http://www.rootbeer.com/', 'http://www.ajstephans.com/'],
	"FacebookPage" TEXT -- example: ['https://www.facebook.com/Dads-Old-Fashioned-Root-B', 'https://www.facebook.com/Dog-N-Suds-Bottled-Root-B'],
	"Twitter" TEXT -- example: ['https://twitter.com/ilovedads', 'https://twitter.com/1919rootbeer'],
	"WholesaleCost" REAL -- example: ['0.42', '0.98'],
	"CurrentRetailPrice" REAL -- example: ['1.0', '3.0'],
	PRIMARY KEY ("BrandID")
)


CREATE TABLE rootbeerreview (
	"CustomerID" INTEGER -- example: ['105549', '101811'],
	"BrandID" INTEGER -- example: ['10012', '10014'],
	"StarRating" INTEGER -- example: ['5', '1'],
	"ReviewDate" DATE -- example: ['2013-07-15', '2013-07-08'],
	"Review" TEXT -- example: ['You could have done better Sactown.', 'Too much bite, not enough barq.'],
	PRIMARY KEY ("CustomerID", "BrandID"), 
	CONSTRAINT fk_rootbeerreview_customers FOREIGN KEY("CustomerID") REFERENCES customers ("CustomerID"),
	CONSTRAINT fk_rootbeerreview_rootbeerbrand FOREIGN KEY("BrandID") REFERENCES rootbeerbrand ("BrandID")
)

Question: How many transactions were made to purchase a bottle of beer using American Express?
Evidence: bottle of beer refers to ContainerType = 'Bottle'; American Express refers to CreditCardType = 'American Express';

SQL:
SELECT COUNT(T1.RootBeerID) FROM rootbeer AS T1 INNER JOIN `transaction` AS T2 ON T1.RootBeerID = T2.RootBeerID WHERE T1.ContainerType = 'Bottle' AND T2.CreditCardType = 'American Express'