Database schema:
CREATE TABLE Client (
  IdClient TEXT PRIMARY KEY,
  Name TEXT,
  Address TEXT,
  NumCC TEXT
);
CREATE TABLE Orders (
  IdOrder TEXT PRIMARY KEY,
  IdClient TEXT,
  DateOrder TIME,
  DateExped TIME
);
CREATE TABLE Author (
  idAuthor NUMBER PRIMARY KEY,
  Name TEXT
);
CREATE TABLE Book (
  ISBN TEXT PRIMARY KEY,
  Title TEXT,
  Author TEXT,
  PurchasePrice NUMBER,
  SalePrice NUMBER
);
CREATE TABLE Author_Book (
  ISBN TEXT PRIMARY KEY,
  Author NUMBER,
  FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);
CREATE TABLE Books_Order (
  ISBN TEXT PRIMARY KEY,
  IdOrder TEXT,
  amount NUMBER,
  FOREIGN KEY (IdOrder) REFERENCES Orders(IdOrder),
  FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);

Question: List names and addresses of all clients in alphabetical order by their names.
SQL: SELECT name ,  address FROM Client ORDER BY name

Question: Give the titles of books authored by Plato that have a sale price lower than the average sale price across all books.
SQL: SELECT T1.title FROM Book AS T1 JOIN Author_book AS T2 ON T1.isbn  =  T2.isbn JOIN Author AS T3 ON T2.Author  =  T3.idAuthor WHERE T3.name  =  "Plato" AND T1.saleprice  <  (SELECT avg(saleprice) FROM Book)

Question: What are the titles of books with sale prices above the average sale price across all books?
SQL: SELECT title FROM book WHERE saleprice  >  (SELECT avg(saleprice) FROM book)

Question: Show the client names and their total amounts of books ordered.
SQL: SELECT T2.name ,  sum(T3.amount) FROM Orders AS T1 JOIN Client AS T2 ON T1.idClient  =  T2.idClient JOIN Books_Order AS T3 ON T3.idOrder  =  T1.idOrder GROUP BY T1.idClient

Question: Show all book titles for books that have no orders.
SQL: SELECT title FROM book EXCEPT SELECT T1.title FROM book AS T1 JOIN books_order AS T2 ON T1.isbn  =  T2.isbn

Question: How many clients are there?
SQL: SELECT count(*) FROM Client

Question: What are the titles of books that have never been ordered?
SQL: SELECT title FROM book EXCEPT SELECT T1.title FROM book AS T1 JOIN books_order AS T2 ON T1.isbn  =  T2.isbn

Question: List all book titles which have sale prices higher than the average.
SQL: SELECT title FROM book WHERE saleprice  >  (SELECT avg(saleprice) FROM book)

Question: Find the title of books which are ordered by client Peter Doe but not client James Smith.
SQL: SELECT T4.title FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient JOIN book AS T4 ON T2.ISBN  =  T4.isbn WHERE T3.name  =  "Peter Doe" EXCEPT SELECT T4.title FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient JOIN book AS T4 ON T2.ISBN  =  T4.isbn WHERE T3.name  =  "James Smith"

Question: What are the names of all the clients, and how many times has each of them ordered?
SQL: SELECT T2.name ,  count(*) FROM Orders AS T1 JOIN Client AS T2 ON T1.idClient  =  T2.idClient GROUP BY T1.idClient

Question: Show all book isbns which were ordered by both client Peter Doe and client James Smith.
SQL: SELECT T2.isbn FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient WHERE T3.name  =  "Peter Doe" INTERSECT SELECT T2.isbn FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient WHERE T3.name  =  "James Smith"

Question: What are the names of clients who have never made an order?
SQL: SELECT name FROM Client EXCEPT SELECT T1.name FROM Client AS T1 JOIN Orders AS T2 ON T1.idClient  =  T2.idClient

Question: List all the author names.
SQL: SELECT name FROM Author

Question: List all book titles which have the lowest sale price .
SQL: select title from book order by saleprice asc limit 1

Question: What are the different titles of books that have been ordered in the past?
SQL: SELECT DISTINCT T1.title FROM book AS T1 JOIN books_order AS T2 ON T1.isbn  =  T2.isbn

Question: Show the titles of books that have been ordered.
SQL: SELECT DISTINCT T1.title FROM book AS T1 JOIN books_order AS T2 ON T1.isbn  =  T2.isbn

Question: What is the average purchase price and the average sale price?
SQL: SELECT avg(purchaseprice) ,  avg(saleprice) FROM Book

Question: Show all author names and the numbers of books each has written.
SQL: SELECT T1.name ,  count(*) FROM Author AS T1 JOIN Author_Book AS T2 ON T1.idAuthor  =  T2.Author GROUP BY T1.idAuthor

Question: What are sale prices of books written by Plato?
SQL: SELECT saleprice FROM Book AS T1 JOIN Author_book AS T2 ON T1.isbn  =  T2.isbn JOIN Author AS T3 ON T2.Author  =  T3.idAuthor WHERE T3.name  =  "Plato"

Question: What are the names of clients who have ordered Pride and Prejudice?
SQL: SELECT T3.name FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient JOIN Book AS T4 ON T4.isbn  =  T2.isbn WHERE T4.title  =  "Pride and Prejudice"

Question: Give the name of the client who has made the most orders.
SQL: SELECT T2.name FROM Orders AS T1 JOIN Client AS T2 ON T1.idClient  =  T2.idClient GROUP BY T1.idClient ORDER BY count(*) DESC LIMIT 1

Question: What are the titles of books that the client Peter Doe ordered, but the client James Smith did not?
SQL: SELECT T4.title FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient JOIN book AS T4 ON T2.ISBN  =  T4.isbn WHERE T3.name  =  "Peter Doe" EXCEPT SELECT T4.title FROM Orders AS T1 JOIN Books_Order AS T2 ON T1.idOrder  =  T2.idOrder JOIN Client AS T3 ON T1.idClient  =  T3.idClient JOIN book AS T4 ON T2.ISBN  =  T4.isbn WHERE T3.name  =  "James Smith"

Question: Show the client name who has the most total amount of books ordered.
SQL: SELECT T2.name FROM Orders AS T1 JOIN Client AS T2 ON T1.idClient  =  T2.idClient JOIN Books_Order AS T3 ON T3.idOrder  =  T1.idOrder GROUP BY T1.idClient ORDER BY sum(T3.amount) DESC LIMIT 1

Question: What are the names of all books and their corresponding authors?
SQL: SELECT T3.title ,  T1.name FROM Author AS T1 JOIN Author_Book AS T2 ON T2.Author  =  T1.idAuthor JOIN Book AS T3 ON T2.isbn  =  T3.isbn

Question: Show all order ids and their client names.
SQL: SELECT T1.idOrder ,  T2.name FROM Orders AS T1 JOIN Client AS T2 ON T1.idClient  =  T2.idClient

Question: Show all book titles and corresponding author names.
SQL: SELECT T3.title ,  T1.name FROM Author AS T1 JOIN Author_Book AS T2 ON T2.Author  =  T1.idAuthor JOIN Book AS T3 ON T2.isbn  =  T3.isbn

Question: What is the title of the book that has been ordered the greatest number of times?
SQL: SELECT T2.title FROM Books_Order AS T1 JOIN Book AS T2 ON T1.isbn  =  T2.isbn GROUP BY T1.isbn ORDER BY count(*) DESC LIMIT 1

Question: Give the name of the author who wrote the book titled Pride and Prejudice.
SQL: SELECT T3.name FROM Book AS T1 JOIN Author_book AS T2 ON T1.isbn  =  T2.isbn JOIN Author AS T3 ON T2.Author  =  T3.idAuthor WHERE T1.title  =  "Pride and Prejudice"

Question: Return the number of clients.
SQL: SELECT count(*) FROM Client

Question: What are all isbns for each book, and how many times has each been ordered?
SQL: SELECT isbn ,  count(*) FROM Books_Order GROUP BY isbn

Question: Give the title of book by George Orwell that has the lowest saleprice.
SQL: SELECT T1.title FROM Book AS T1 JOIN Author_book AS T2 ON T1.isbn  =  T2.isbn JOIN Author AS T3 ON T2.Author  =  T3.idAuthor WHERE T3.name  =  "George Orwell" ORDER BY T1.saleprice LIMIT 1

Question: What is the title of the book written by Plato has price lower than the average sale price of all books?
SQL: SELECT T1.title FROM Book AS T1 JOIN Author_book AS T2 ON T1.isbn  =  T2.isbn JOIN Author AS T3 ON T2.Author  =  T3.idAuthor WHERE T3.name  =  "Plato" AND T1.saleprice  <  (SELECT avg(saleprice) FROM Book)