Course Fundamental Databases (IT403) Using the following relations to write SQL
ID: 3715835 • Letter: C
Question
Course Fundamental Databases (IT403)
Using the following relations to write SQL statement that follows:
Author (Author_ID, FIRST_NAME, LAST_NAME)
Book_authors_books (ISBN, Author_ID)
Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages)
1- Retrieve all the books that cost less than 35.
2- Retrieve all the authors in alphabetical order(A-Z).
3- Retrieve all the possible information about the most expensive book that published by "New Math" publisher (the data should include authors).
4- Increase price value by 10% of all books that published in 2015 (use update).
Explanation / Answer
1. Select * from Book_descriptions where price < 35;
2. Select * from Author order by FIRST_NAME,LAST_NAME;
3. Select Book_descriptions.*,FIRST_NAME,LAST_NAME from Book_descriptions inner join Book_authors_books on Book_descriptions .ISBN = Book_authors_books.ISBN inner join Author on book_authors_books.AuthorID = Author.AuthorID where Price = (Select max(Price) from Book_descriptions where Publisher = 'New Math');
4. Update Book_descriptions set Price = Price + Price*0.1 where year(Pub_date) = 2015;
Do ask if any doubt. Please upvote.