Database schema:

CREATE TABLE customer (
	c_custkey INTEGER NOT NULL -- example: ['2', '1'],
	c_mktsegment TEXT DEFAULT NULL -- example: ['BUILDING', 'MACHINERY'],
	c_nationkey INTEGER DEFAULT NULL -- example: ['8', '16'],
	c_name TEXT DEFAULT NULL -- example: ['Customer#000000002', 'Customer#000000001'],
	c_address TEXT DEFAULT NULL -- example: ['ioUn,eqTTXOdo', 'KwX3hMHjZ6'],
	c_phone TEXT DEFAULT NULL -- example: ['906-965-7556', '937-241-3198'],
	c_acctbal REAL DEFAULT NULL -- example: ['7550.21', '3560.03'],
	c_comment TEXT DEFAULT NULL -- example: ['ironic excuses detect slyly silent requests. reque', 'final express accounts mold slyly. ironic accounts'],
	PRIMARY KEY (c_custkey), 
	CONSTRAINT fk_customer_nation FOREIGN KEY(c_nationkey) REFERENCES nation (n_nationkey)
)


CREATE TABLE lineitem (
	l_shipdate DATE -- example: ['1995-08-16', '1995-08-13'],
	l_orderkey INTEGER NOT NULL -- example: ['2', '1'],
	l_discount REAL NOT NULL -- example: ['0.09', '0.1'],
	l_extendedprice REAL NOT NULL -- example: ['58303.08', '16947.7'],
	l_suppkey INTEGER NOT NULL -- example: ['6296', '8776'],
	l_quantity INTEGER NOT NULL -- example: ['33', '10'],
	l_returnflag TEXT -- example: ['A', 'N'],
	l_partkey INTEGER NOT NULL -- example: ['23771', '98768'],
	l_linestatus TEXT -- example: ['O', 'F'],
	l_tax REAL NOT NULL -- example: ['0.08', '0.06'],
	l_commitdate DATE -- example: ['1995-07-12', '1995-07-09'],
	l_receiptdate DATE -- example: ['1995-09-14', '1995-08-27'],
	l_shipmode TEXT -- example: ['TRUCK', 'RAIL'],
	l_linenumber INTEGER NOT NULL -- example: ['2', '1'],
	l_shipinstruct TEXT -- example: ['TAKE BACK RETURN', 'NONE'],
	l_comment TEXT -- example: ['carefully bo', 'blithely regular pac'],
	PRIMARY KEY (l_orderkey, l_linenumber), 
	CONSTRAINT fk_lineitem_orders FOREIGN KEY(l_orderkey) REFERENCES orders (o_orderkey),
	CONSTRAINT fk_lineitem_partsupp FOREIGN KEY(l_partkey, l_suppkey) REFERENCES partsupp (ps_partkey, ps_suppkey)
)


CREATE TABLE nation (
	n_nationkey INTEGER NOT NULL -- example: ['0', '1'],
	n_name TEXT -- example: ['ALGERIA', 'ARGENTINA'],
	n_regionkey INTEGER -- example: ['0', '1'],
	n_comment TEXT -- example: ['instructions detect blithely stealthily pending pa', 'slyly express pinto beans cajole idly. deposits us'],
	PRIMARY KEY (n_nationkey), 
	CONSTRAINT fk_nation_region FOREIGN KEY(n_regionkey) REFERENCES region (r_regionkey)
)


CREATE TABLE orders (
	o_orderdate DATE -- example: ['1995-04-19', '1996-11-04'],
	o_orderkey INTEGER NOT NULL -- example: ['2', '1'],
	o_custkey INTEGER NOT NULL -- example: ['92861', '73100'],
	o_orderpriority TEXT -- example: ['1-URGENT', '4-NOT SPECIFIED'],
	o_shippriority INTEGER -- example: ['0'],
	o_clerk TEXT -- example: ['Clerk#000000373', 'Clerk#000000916'],
	o_orderstatus TEXT -- example: ['O', 'P'],
	o_totalprice REAL -- example: ['203198.56', '317719.99'],
	o_comment TEXT -- example: ['final packages sleep blithely packa', 'final excuses about the ironic even deposits detec'],
	PRIMARY KEY (o_orderkey), 
	CONSTRAINT fk_orders_customer FOREIGN KEY(o_custkey) REFERENCES customer (c_custkey)
)


CREATE TABLE part (
	p_partkey INTEGER NOT NULL -- example: ['2', '1'],
	p_type TEXT -- example: ['LARGE PLATED TIN', 'LARGE POLISHED STEEL'],
	p_size INTEGER -- example: ['4', '31'],
	p_brand TEXT -- example: ['Brand#55', 'Brand#43'],
	p_name TEXT -- example: ['burlywood plum powder puff mint', 'hot spring dodger dim light'],
	p_container TEXT -- example: ['LG BAG', 'LG CASE'],
	p_mfgr TEXT -- example: ['Manufacturer#5', 'Manufacturer#4'],
	p_retailprice REAL -- example: ['902.0', '901.0'],
	p_comment TEXT -- example: ['even ironic requests s', 'blithely busy reque'],
	PRIMARY KEY (p_partkey)
)


CREATE TABLE partsupp (
	ps_partkey INTEGER NOT NULL -- example: ['2', '1'],
	ps_suppkey INTEGER NOT NULL -- example: ['2', '2502'],
	ps_supplycost REAL NOT NULL -- example: ['400.75', '702.61'],
	ps_availqty INTEGER -- example: ['3999', '1111'],
	ps_comment TEXT -- example: ['carefully ironic deposits use against the carefull', 'slyly regular accounts serve carefully. asymptotes'],
	PRIMARY KEY (ps_partkey, ps_suppkey), 
	CONSTRAINT fk_partsupp_part FOREIGN KEY(ps_partkey) REFERENCES part (p_partkey),
	CONSTRAINT fk_partsupp_supplier FOREIGN KEY(ps_suppkey) REFERENCES supplier (s_suppkey)
)


CREATE TABLE region (
	r_regionkey INTEGER NOT NULL -- example: ['0', '1'],
	r_name TEXT -- example: ['AFRICA', 'AMERICA'],
	r_comment TEXT -- example: ['asymptotes sublate after the r', 'requests affix quickly final tithes. blithely even'],
	PRIMARY KEY (r_regionkey)
)


CREATE TABLE supplier (
	s_suppkey INTEGER NOT NULL -- example: ['2', '1'],
	s_nationkey INTEGER -- example: ['5', '13'],
	s_comment TEXT -- example: ['requests integrate fluffily. fluffily ironic depos', 'blithely final pearls are. instructions thra'],
	s_name TEXT -- example: ['Supplier#000000002', 'Supplier#000000001'],
	s_address TEXT -- example: [',wWs4pnykQOFl8mgVCU8EZMXqZs1w', 'WkXT6MSAJrp4qWq3W9N'],
	s_phone TEXT -- example: ['348-617-6055', '800-807-9579'],
	s_acctbal REAL -- example: ['3009.73', '3082.86'],
	PRIMARY KEY (s_suppkey), 
	CONSTRAINT fk_supplier_nation FOREIGN KEY(s_nationkey) REFERENCES nation (n_nationkey)
)

Question: In 1997, how many orders were shipped via mail?
Evidence: 1997 refers to year(l_shipdate) = 1997; shipped via mail refers to l_shipmode = 'MAIL'

SQL:
SELECT COUNT(l_orderkey) FROM lineitem WHERE STRFTIME('%Y', l_shipdate) = '1997' AND l_shipmode = 'MAIL'