Database schema:
CREATE TABLE author (
  Author_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Age NUMBER,
  Gender TEXT
);
CREATE TABLE press (
  Press_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Month_Profits_billion NUMBER,
  Year_Profits_billion NUMBER
);
CREATE TABLE book (
  Book_ID NUMBER PRIMARY KEY,
  Title TEXT,
  Book_Series TEXT,
  Author_ID NUMBER,
  Press_ID NUMBER,
  Sale_Amount TEXT,
  Release_date TEXT,
  FOREIGN KEY (Press_ID) REFERENCES press(Press_ID),
  FOREIGN KEY (Author_ID) REFERENCES author(Author_ID)
);

Question: How many authors are of age above 30 for each gender?
SQL: SELECT count(*) ,  gender FROM author WHERE age  >  30 GROUP BY gender

Question: Which authors have never published under the "Accor" press? Give me their names.
SQL: SELECT name FROM author EXCEPT SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id JOIN press AS t3 ON t2.press_id  =  t3.press_id WHERE t3.name  =  'Accor'

Question: find the number of authors who are older than 30 for each gender.
SQL: SELECT count(*) ,  gender FROM author WHERE age  >  30 GROUP BY gender

Question: how many authors are under age 30?
SQL: SELECT count(*) FROM author WHERE age  <  30

Question: Find the press that published more than two books, and return its name and yearly profit in billion.
SQL: SELECT t2.name ,  t2.Year_Profits_billion FROM book AS t1 JOIN press AS t2 ON t1.press_id  =  t2.press_id GROUP BY t2.press_id HAVING count(*)  >  2

Question: find the name and gender of the author who published the most books.
SQL: SELECT t1.name ,  t1.gender FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id GROUP BY t2.author_id ORDER BY count(*) DESC LIMIT 1

Question: What are the 3 best selling books? Show their titles, author names, and press names.
SQL: SELECT t1.name ,  t2.title ,  t3.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id JOIN press AS t3 ON t2.press_id  =  t3.press_id ORDER BY t2.sale_amount DESC LIMIT 3

Question: Find the book series that have some book selling more than 1000 and some book less 500.
SQL: SELECT book_series FROM book WHERE sale_amount  >  1000 INTERSECT SELECT book_series FROM book WHERE sale_amount  <  500

Question: Sort all the books in descending order of release date, and return the book titles.
SQL: SELECT title FROM book ORDER BY release_date DESC

Question: Find the press whose yearly profit is more than 15 billion or whose monthly profit is more than 1 billion. Return the press names.
SQL: SELECT name FROM press WHERE Year_Profits_billion  >  15 OR Month_Profits_billion  >  1

Question: Find the name and age of the authors who do not have any book in the record.
SQL: SELECT name ,  age FROM author WHERE author_id NOT IN (SELECT author_id FROM book)

Question: Find the name of the publisher whose monthly profit is the highest or the lowest.
SQL: SELECT name FROM press WHERE Month_Profits_billion  =  (SELECT min(Month_Profits_billion) FROM press) OR Month_Profits_billion  =  (SELECT max(Month_Profits_billion) FROM press)

Question: Find the average and maximum yearly profit for each press.
SQL: SELECT avg(Year_Profits_billion) ,  max(Year_Profits_billion) FROM press

Question: Find the name of the publisher whose monthly profit is the highest.
SQL: SELECT name FROM press ORDER BY Month_Profits_billion DESC LIMIT 1

Question: Count the number of authors of age below 30.
SQL: SELECT count(*) FROM author WHERE age  <  30

Question: Find the name and the yearly profit in billion for press that published more than two books.
SQL: SELECT t2.name ,  t2.Year_Profits_billion FROM book AS t1 JOIN press AS t2 ON t1.press_id  =  t2.press_id GROUP BY t2.press_id HAVING count(*)  >  2

Question: For each gender, return gender and the average age of authors.
SQL: SELECT avg(age) ,  gender FROM author GROUP BY gender

Question: Find the names of authors who have more than one book in the database.
SQL: SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id GROUP BY t2.author_id HAVING count(*)  >  1

Question: find the average age of authors for each gender.
SQL: SELECT avg(age) ,  gender FROM author GROUP BY gender

Question: Which authors have published more than 1 book according to the database? Give me their names.
SQL: SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id GROUP BY t2.author_id HAVING count(*)  >  1

Question: Sorted all the press by year profits in descending order, and return press names.
SQL: SELECT name FROM press ORDER BY Year_Profits_billion DESC

Question: Find the number of books for each series.
SQL: SELECT count(*) ,  book_series FROM book GROUP BY book_series

Question: Which authors in the record have not published any books ? Give me their names .
SQL: select name from author where author_id not in (select author_id from book)

Question: Find the name of authors who publish their books in both "MM" and "LT" series.
SQL: SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id WHERE t2.book_series  =  'MM' INTERSECT SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id WHERE t2.book_series  =  'LT'

Question: Who wrote the largest number of books? Give me the author name and gender.
SQL: SELECT t1.name ,  t1.gender FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id GROUP BY t2.author_id ORDER BY count(*) DESC LIMIT 1

Question: Who wrote the best selling book? Give me the author name.
SQL: SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id ORDER BY t2.sale_amount DESC LIMIT 1

Question: Which press has the largest monthly profit? Give me the press name.
SQL: SELECT name FROM press ORDER BY Month_Profits_billion DESC LIMIT 1

Question: For each press, return its name and the number of books that have sale amount above 1000.
SQL: SELECT count(*) ,  t2.name FROM book AS t1 JOIN press AS t2 ON t1.press_id  =  t2.press_id WHERE sale_amount  >  1000 GROUP BY t2.name

Question: What are the 5 best books in terms of sale amount? Give me their titles and release dates.
SQL: SELECT title ,  release_date FROM book ORDER BY sale_amount DESC LIMIT 5

Question: Find the names of the authors who did not have any book with the "Accor" press.
SQL: SELECT name FROM author EXCEPT SELECT t1.name FROM author AS t1 JOIN book AS t2 ON t1.author_id  =  t2.author_id JOIN press AS t3 ON t2.press_id  =  t3.press_id WHERE t3.name  =  'Accor'

Question: what are the average and maximum profit of a year for all presses?
SQL: SELECT avg(Year_Profits_billion) ,  max(Year_Profits_billion) FROM press

Question: Which book series contain both books with sale amount above 1000 and books with sale amount below 500?
SQL: SELECT book_series FROM book WHERE sale_amount  >  1000 INTERSECT SELECT book_series FROM book WHERE sale_amount  <  500