Database schema:

CREATE TABLE city (
	city_id INTEGER -- example: ['101', '100'],
	city_name TEXT -- example: ['Union City', 'Huntington Park'],
	state TEXT -- example: ['New Jersey', 'California'],
	population INTEGER -- example: ['61348', '67088'],
	area REAL -- example: ['1.3', '3.0'],
	PRIMARY KEY (city_id)
)


CREATE TABLE customer (
	cust_id INTEGER -- example: ['304', '193'],
	cust_name TEXT -- example: ["Pard's Trailer Sales", 'Advanced Fabricators'],
	annual_revenue INTEGER -- example: ['17158109', '39588651'],
	cust_type TEXT -- example: ['manufacturer', 'wholesaler'],
	address TEXT -- example: ['5910 South 300 West', '5141 Summit Boulevard'],
	city TEXT -- example: ['West Palm Beach', 'Salt Lake City'],
	state TEXT -- example: ['UT', 'FL'],
	zip REAL -- example: ['84107.0', '33415.0'],
	phone TEXT -- example: ['(561) 683-3535', '(801) 262-4864'],
	PRIMARY KEY (cust_id)
)


CREATE TABLE driver (
	driver_id INTEGER -- example: ['20', '21'],
	first_name TEXT -- example: ['Andrea', 'Sue'],
	last_name TEXT -- example: ['Simons', 'Newell'],
	address TEXT -- example: ['3574 Oak Limb Cv', '268 Richmond Ave'],
	city TEXT -- example: ['Memphis'],
	state TEXT -- example: ['TN'],
	zip_code INTEGER -- example: ['38135', '38106'],
	phone TEXT -- example: ['(901) 774-6569', '(901) 384-0984'],
	PRIMARY KEY (driver_id)
)


CREATE TABLE shipment (
	ship_id INTEGER -- example: ['1000', '1001'],
	cust_id INTEGER -- example: ['3660', '2001'],
	weight REAL -- example: ['3528.0', '11394.0'],
	truck_id INTEGER -- example: ['2', '1'],
	driver_id INTEGER -- example: ['23', '27'],
	city_id INTEGER -- example: ['186', '137'],
	ship_date TEXT -- example: ['2016-01-18', '2016-01-08'],
	PRIMARY KEY (ship_id), 
	CONSTRAINT fk_shipment_customer FOREIGN KEY(cust_id) REFERENCES customer (cust_id),
	CONSTRAINT fk_shipment_city FOREIGN KEY(city_id) REFERENCES city (city_id),
	CONSTRAINT fk_shipment_driver FOREIGN KEY(driver_id) REFERENCES driver (driver_id),
	CONSTRAINT fk_shipment_truck FOREIGN KEY(truck_id) REFERENCES truck (truck_id)
)


CREATE TABLE truck (
	truck_id INTEGER -- example: ['2', '1'],
	make TEXT -- example: ['Mack', 'Peterbilt'],
	model_year INTEGER -- example: ['2005', '2006'],
	PRIMARY KEY (truck_id)
)

Question: Determine the number of shipments delivered by Andrea Simons to Huntsville in 2016.
Evidence: "Huntsville" is the city_name; in 2016 refers to Cast(ship_date AS DATE) = 2016; number of shipment refers to Count(ship_id)

SQL:
SELECT COUNT(*) FROM shipment AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.city_id INNER JOIN driver AS T3 ON T3.driver_id = T1.driver_id WHERE T3.first_name = 'Andrea' AND T3.last_name = 'Simons' AND T2.city_name = 'Huntsville' AND STRFTIME('%Y', T1.ship_date) = '2016'