Database

Question

Considering the following schema, create the appropriate tables and insert at least 5 records:

AUTHOR(author-id,name, city,country)

PUBLISHER (publisher-id, name,city, country)

CATALOG (book-id, title, author-id, publisher-id, category-id, year, price)

CATEGORY (category-id, description)

ORDER-DETAILS (order-no, book-id,quantity)

Write each of the following queries in SQL and relational algebra:

(a) Obtain the names of authors who have 2 or more books in the catalog.

(b) Find the author of the book which has maximum sales.

(c) Obtain the names of author who have maximum number of publisher.

(d) Obtain the name of the city, author,publisher where publisher and author belong to same city.

(e) Obtain the title of books which has maximum sales.

(f) Obtain the book-id, description for the author who have exactly 3 books in the catalog.

(g) Obtain the author and publisher who have published books in more than or 14 equal to 2 categories.

Answer 1

1. SELECT DISTINCT $name$ FROM $author,catalog$ WHERE
   SELECT $count(author\_id)$ FROM $catalog$ WHERE $catalog.author\_id=author.author\_id$)>$1$
    
2. SELECT $name$ FROM $author$ WHERE $author\_id$ IN 
   (SELECT $author\_id$ FROM $catalog$ WHERE $book\_id$ IN 
   (SELECT $book\_id$ FROM $order\_details$ WHERE $quantity$ IN 
   (SELECT MAX$(quantity)$ FROM $order\_details$)))
    
3. SELECT $name$ FROM $author$ INNER JOIN
   (SELECT $author\_id$, COUNT($publisher\_id$) AS `$occurrence$` FROM $catalog$
   GROUP BY $author\_id$
   ORDER BY `$occurrence$` DESC LIMIT $1$) AS $t1$
   $on$ $author.author\_id=t1.author\_id$;
    
4. SELECT DISTINCT $author.city,author.name, publisher.name$ FROM $author$
   INNER JOIN $catalog$ ON $author.author\_id = catalog.author\_id$
   INNER JOIN $publisher$ ON $publisher.publisher\_id = catalog.publisher\_id$
   WHERE $author.city = publisher.city$
    
5. SELECT $title$ FROM $catalog$ INNER JOIN
   (SELECT $*$ FROM $order\_details$ WHERE $quantity$ IN
   (SELECT MAX($quantity$) FROM $order\_details$)) AS $t1$
   ON $catalog.book\_id = t1.book\_id$;
    
6. SELECT $book\_id,description$ FROM $catalog$
   INNER JOIN $category$ ON $catalog.category\_id = category.category\_id$
   GROUP BY $author\_id$ HAVING COUNT($*$)$=3$;
   (Here only one record is retrieved, some one please update)
    
7. The question is not clear(?)
   Hoping someone would come up with the full solution and the relational algebra expressions.