Database schema:

CREATE TABLE address (
	address_id INTEGER -- example: ['2', '1'],
	street_number TEXT -- example: ['86', '57'],
	street_name TEXT -- example: ['Glacier Hill Avenue', 'Dottie Junction'],
	city TEXT -- example: ['Beaumont', 'Torbat-e Jām'],
	country_id INTEGER -- example: ['37', '95'],
	PRIMARY KEY (address_id), 
	CONSTRAINT fk_address_country FOREIGN KEY(country_id) REFERENCES country (country_id)
)


CREATE TABLE address_status (
	status_id INTEGER -- example: ['2', '1'],
	address_status TEXT -- example: ['Active', 'Inactive'],
	PRIMARY KEY (status_id)
)


CREATE TABLE author (
	author_id INTEGER -- example: ['2', '1'],
	author_name TEXT -- example: ['A. Elizabeth Delany', 'A. Bartlett Giamatti'],
	PRIMARY KEY (author_id)
)


CREATE TABLE book (
	book_id INTEGER -- example: ['2', '1'],
	title TEXT -- example: ["The World's First Love: Mary  Mother of God", 'The Illuminati'],
	isbn13 TEXT -- example: ['8987059752', '20049130001'],
	language_id INTEGER -- example: ['2', '1'],
	num_pages INTEGER -- example: ['352', '276'],
	publication_date DATE -- example: ['1996-09-01', '2004-10-04'],
	publisher_id INTEGER -- example: ['1010', '1967'],
	PRIMARY KEY (book_id), 
	CONSTRAINT fk_book_book_language FOREIGN KEY(language_id) REFERENCES book_language (language_id),
	CONSTRAINT fk_book_publisher FOREIGN KEY(publisher_id) REFERENCES publisher (publisher_id)
)


CREATE TABLE book_author (
	book_id INTEGER -- example: ['2', '1'],
	author_id INTEGER -- example: ['2', '1'],
	PRIMARY KEY (book_id, author_id), 
	CONSTRAINT fk_book_author_author FOREIGN KEY(author_id) REFERENCES author (author_id),
	CONSTRAINT fk_book_author_book FOREIGN KEY(book_id) REFERENCES book (book_id)
)


CREATE TABLE book_language (
	language_id INTEGER -- example: ['2', '1'],
	language_code TEXT -- example: ['eng', 'en-US'],
	language_name TEXT -- example: ['English', 'United States English'],
	PRIMARY KEY (language_id)
)


CREATE TABLE country (
	country_id INTEGER -- example: ['2', '1'],
	country_name TEXT -- example: ['Afghanistan', 'Netherlands Antilles'],
	PRIMARY KEY (country_id)
)


CREATE TABLE cust_order (
	order_id INTEGER -- example: ['2', '1'],
	order_date DATETIME -- example: ['2021-07-14 10:47:19', '2020-08-16 17:26:41'],
	customer_id INTEGER -- example: ['1256', '387'],
	shipping_method_id INTEGER -- example: ['2', '4'],
	dest_address_id INTEGER -- example: ['2', '1'],
	PRIMARY KEY (order_id), 
	CONSTRAINT fk_cust_order_address FOREIGN KEY(dest_address_id) REFERENCES address (address_id),
	CONSTRAINT fk_cust_order_shipping_method FOREIGN KEY(shipping_method_id) REFERENCES shipping_method (method_id),
	CONSTRAINT fk_cust_order_customer FOREIGN KEY(customer_id) REFERENCES customer (customer_id)
)


CREATE TABLE customer (
	customer_id INTEGER -- example: ['2', '1'],
	first_name TEXT -- example: ['Ursola', 'Ruthanne'],
	last_name TEXT -- example: ['Vatini', 'Purdy'],
	email TEXT -- example: ['upurdy0@cdbaby.com', 'rvatini1@fema.gov'],
	PRIMARY KEY (customer_id)
)


CREATE TABLE customer_address (
	customer_id INTEGER -- example: ['2', '1'],
	address_id INTEGER -- example: ['266', '606'],
	status_id INTEGER -- example: ['2', '1'],
	PRIMARY KEY (customer_id, address_id), 
	CONSTRAINT fk_customer_address_address FOREIGN KEY(address_id) REFERENCES address (address_id),
	CONSTRAINT fk_customer_address_customer FOREIGN KEY(customer_id) REFERENCES customer (customer_id)
)


CREATE TABLE order_history (
	history_id INTEGER -- example: ['2', '1'],
	order_id INTEGER -- example: ['2', '1'],
	status_id INTEGER -- example: ['2', '1'],
	status_date DATETIME -- example: ['2021-07-14 17:04:28', '2020-08-16 20:23:19'],
	PRIMARY KEY (history_id), 
	CONSTRAINT fk_order_history_order_status FOREIGN KEY(status_id) REFERENCES order_status (status_id),
	CONSTRAINT fk_order_history_cust_order FOREIGN KEY(order_id) REFERENCES cust_order (order_id)
)


CREATE TABLE order_line (
	line_id INTEGER -- example: ['1024', '1025'],
	order_id INTEGER -- example: ['2051', '899'],
	book_id INTEGER -- example: ['10105', '10720'],
	price REAL -- example: ['3.19', '1.24'],
	PRIMARY KEY (line_id), 
	CONSTRAINT fk_order_line_book FOREIGN KEY(book_id) REFERENCES book (book_id),
	CONSTRAINT fk_order_line_cust_order FOREIGN KEY(order_id) REFERENCES cust_order (order_id)
)


CREATE TABLE order_status (
	status_id INTEGER -- example: ['2', '1'],
	status_value TEXT -- example: ['Pending Delivery', 'Order Received'],
	PRIMARY KEY (status_id)
)


CREATE TABLE publisher (
	publisher_id INTEGER -- example: ['2', '1'],
	publisher_name TEXT -- example: ['10/18', '1st Book Library'],
	PRIMARY KEY (publisher_id)
)


CREATE TABLE shipping_method (
	method_id INTEGER -- example: ['2', '1'],
	method_name TEXT -- example: ['Priority', 'Standard'],
	cost REAL -- example: ['5.9', '8.9'],
	PRIMARY KEY (method_id)
)

Question: Count the number of books written by Orson Scott Card.
Evidence: "Orson Scott Card" is the author_name

SQL:
SELECT COUNT(*) FROM book AS T1 INNER JOIN book_author AS T2 ON T1.book_id = T2.book_id INNER JOIN author AS T3 ON T3.author_id = T2.author_id WHERE T3.author_name = 'Orson Scott Card'