1)Show client (name, surname ) who bought books by French authors and also show author name and country. Each client should appear only once in the result.

2)show the number of books purchased in each shop (show the city) between 2015 and 2020(included)

3) for each book display title, author name, surname, and show the total amount of money earned by it based on the price of purchases of that book. Sort the result in ascending order by total amount of money earned by each book.

4) find the book with the highest price among all the books and display its id, title and publishing year.

5)display the clients id, name, and surname who bought the books of USA authors only and also the books names( hint use subquery)

CREATE TABLE Author(

id int primary key ,

name VARCHAR(50),

surname VARCHAR(50),

country VARCHAR(50)

) ;

CREATE TABLE Book(

id int primary key ,

title VARCHAR(50),

author_id int,

year int,

foreign key (author_id) references Author(id)

) ;

CREATE TABLE Shop(

id int primary key ,

city VARCHAR(50)

) ;

CREATE TABLE Client(

id int primary key ,

name VARCHAR(50),

surname VARCHAR(50)

) ;

CREATE TABLE Purchase(

client_id int,

book_id int,

shop_id int,

price int,

date date,

foreign key (client_id) references Client(id),

foreign key (book_id) references Book(id),

foreign key (shop_id) references Shop(id)

) ;

Answer rating: 100% (QA)

To answer the questions you can use SQL queries Here are the queries for each question Show client n... View the full answer