Database schema:

CREATE TABLE authors (
	au_id TEXT -- example: ['172-32-1176', '213-46-8915'],
	au_lname TEXT NOT NULL -- example: ['White', 'Green'],
	au_fname TEXT NOT NULL -- example: ['Marjorie', 'Johnson'],
	phone TEXT NOT NULL -- example: ['415 986-7020', '408 496-7223'],
	address TEXT -- example: ['309 63rd St. #411', '10932 Bigge Rd.'],
	city TEXT -- example: ['Oakland', 'Menlo Park'],
	state TEXT -- example: ['CA', 'KS'],
	zip TEXT -- example: ['94618', '94025'],
	contract TEXT NOT NULL -- example: ['0'],
	PRIMARY KEY (au_id)
)


CREATE TABLE discounts (
	discounttype TEXT NOT NULL -- example: ['Initial Customer', 'Volume Discount'],
	stor_id TEXT -- example: ['8042'],
	lowqty INTEGER -- example: ['100'],
	highqty INTEGER -- example: ['1000'],
	discount REAL NOT NULL -- example: ['10.5', '6.7'],
	CONSTRAINT fk_discounts_stores FOREIGN KEY(stor_id) REFERENCES stores (stor_id)
)


CREATE TABLE employee (
	emp_id TEXT -- example: ['A-R89858F', 'A-C71970F'],
	fname TEXT NOT NULL -- example: ['Aria', 'Annette'],
	minit TEXT -- example: ['', 'M'],
	lname TEXT NOT NULL -- example: ['Roulet', 'Cruz'],
	job_id INTEGER NOT NULL -- example: ['6', '10'],
	job_lvl INTEGER -- example: ['152', '87'],
	pub_id TEXT NOT NULL -- example: ['9999', '1389'],
	hire_date DATETIME NOT NULL -- example: ['1990-02-21 00:00:00', '1991-10-26 00:00:00'],
	PRIMARY KEY (emp_id), 
	CONSTRAINT fk_employee_jobs FOREIGN KEY(job_id) REFERENCES jobs (job_id),
	CONSTRAINT fk_employee_publishers FOREIGN KEY(pub_id) REFERENCES publishers (pub_id)
)


CREATE TABLE jobs (
	job_id INTEGER -- example: ['2', '1'],
	job_desc TEXT NOT NULL -- example: ['Chief Executive Officer', 'New Hire - Job not specified'],
	min_lvl INTEGER NOT NULL -- example: ['200', '10'],
	max_lvl INTEGER NOT NULL -- example: ['250', '10'],
	PRIMARY KEY (job_id)
)


CREATE TABLE pub_info (
	pub_id TEXT -- example: ['1389', '0877'],
	logo BLOB -- example: ['0x4749463839618B002F00B30F000000008000000080008080', '0x474946383961C2001D00B30F000000008000000080008080'],
	pr_info TEXT -- example: ['This is sample text data for Binnet & Hardley, pub', 'This is sample text data for Algodata Infosystems,'],
	PRIMARY KEY (pub_id), 
	CONSTRAINT fk_pub_info_publishers FOREIGN KEY(pub_id) REFERENCES publishers (pub_id)
)


CREATE TABLE publishers (
	pub_id TEXT -- example: ['0736', '0877'],
	pub_name TEXT -- example: ['New Moon Books', 'Binnet & Hardley'],
	city TEXT -- example: ['Washington', 'Boston'],
	state TEXT -- example: ['MA', 'DC'],
	country TEXT -- example: ['Germany', 'USA'],
	PRIMARY KEY (pub_id)
)


CREATE TABLE roysched (
	title_id TEXT NOT NULL -- example: ['BU1032', 'PC1035'],
	lorange INTEGER -- example: ['0', '5001'],
	hirange INTEGER -- example: ['5000', '50000'],
	royalty INTEGER -- example: ['10', '12'],
	CONSTRAINT fk_roysched_titles FOREIGN KEY(title_id) REFERENCES titles (title_id)
)


CREATE TABLE sales (
	stor_id TEXT NOT NULL -- example: ['6380', '7066'],
	ord_num TEXT NOT NULL -- example: ['6871', '722a'],
	ord_date DATETIME NOT NULL -- example: ['1994-09-13 00:00:00', '1994-09-14 00:00:00'],
	qty INTEGER NOT NULL -- example: ['5', '3'],
	payterms TEXT NOT NULL -- example: ['Net 30', 'Net 60'],
	title_id TEXT NOT NULL -- example: ['PS2091', 'BU1032'],
	PRIMARY KEY (stor_id, ord_num, title_id), 
	CONSTRAINT fk_sales_stores FOREIGN KEY(stor_id) REFERENCES stores (stor_id),
	CONSTRAINT fk_sales_titles FOREIGN KEY(title_id) REFERENCES titles (title_id)
)


CREATE TABLE stores (
	stor_id TEXT -- example: ['6380', '7066'],
	stor_name TEXT -- example: ['Eric the Read Books', "Barnum's"],
	stor_address TEXT -- example: ['788 Catamaugus Ave.', '567 Pasadena Ave.'],
	city TEXT -- example: ['Tustin', 'Seattle'],
	state TEXT -- example: ['WA', 'CA'],
	zip TEXT -- example: ['92789', '98056'],
	PRIMARY KEY (stor_id)
)


CREATE TABLE titleauthor (
	au_id TEXT NOT NULL -- example: ['172-32-1176', '213-46-8915'],
	title_id TEXT NOT NULL -- example: ['BU1032', 'PS3333'],
	au_ord INTEGER -- example: ['2', '1'],
	royaltyper INTEGER -- example: ['40', '100'],
	PRIMARY KEY (au_id, title_id), 
	CONSTRAINT fk_titleauthor_authors FOREIGN KEY(au_id) REFERENCES authors (au_id),
	CONSTRAINT fk_titleauthor_titles FOREIGN KEY(title_id) REFERENCES titles (title_id)
)


CREATE TABLE titles (
	title_id TEXT -- example: ['BU1032', 'BU1111'],
	title TEXT NOT NULL -- example: ["The Busy Executive's Database Guide", 'Cooking with Computers: Surreptitious Balance Shee'],
	type TEXT NOT NULL -- example: ['mod_cook', 'business'],
	pub_id TEXT -- example: ['0736', '1389'],
	price REAL -- example: ['11.95', '19.99'],
	advance REAL -- example: ['5000.0', '10125.0'],
	royalty INTEGER -- example: ['24', '10'],
	ytd_sales INTEGER -- example: ['3876', '4095'],
	notes TEXT -- example: ['Helpful hints on how to use your electronic resour', 'An overview of available database systems with emp'],
	pubdate DATETIME NOT NULL -- example: ['1991-06-09 00:00:00', '1991-06-12 00:00:00'],
	PRIMARY KEY (title_id), 
	CONSTRAINT fk_titles_publishers FOREIGN KEY(pub_id) REFERENCES publishers (pub_id)
)

Question: Name the top five titles that sold more than average and list them in descending order of the number of sales in California stores?
Evidence: qty is abbreviation for quantity; sold more than average refers to qty > AVG(qty); california refers to state = 'CA"

SQL:
SELECT T1.title FROM titles AS T1 INNER JOIN sales AS T2 ON T1.title_id = T2.title_id INNER JOIN publishers AS T3 ON T1.pub_id = T3.pub_id WHERE T2.qty > ( SELECT CAST(SUM(qty) AS REAL) / COUNT(title_id) FROM sales ) AND T3.state = 'CA' ORDER BY T2.qty DESC LIMIT 5