Question One : Write SQL DDL corresponding to the following schema. Make any rea

Question

Question One :

Write SQL DDL corresponding to the following schema. Make any reasonable assumptions about data types and be sure to declare primary and foreign keys.

Author (Author_ID, FIRST_NAME, LAST_NAME)

Book_authors_books (ISBN, Author_ID)

Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages)

Question 2 :

Based on the four tables that are created in the first question, you should store (insert) the following data using SQL statements:

Author_ID

FIRST_NAME

LAST_NAME

111

Ali

Sami

222

Mohammed

Khalid

333

Saad

Ahmed

ISBN

Author_ID

15111

111

16222

222

17222

222

18111

111

15111

333

ISBN

Title

Price

publisher

Pub_date

Edition

Pages

15111

Math 1

37.99

New Math

January 20, 2016

1

256

16222

The Art of Strategy

26.94

AWP

June 21, 2014

3

250

17222

Calculus

25

New Math

January 11, 2017

1

753

18111

Game Theory

40.99

MIT

January 01, 2016

2

333

15111

Graph Theory

100

John Wiley

January 01, 2018

1

320

Question Three:

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).

Please Answer All Question , And don't miss AnyThing

Please I want FUll Answer For All Queation

Author_ID

FIRST_NAME

LAST_NAME

111

Ali

Sami

222

Mohammed

Khalid

333

Saad

Ahmed

Explanation / Answer

Question 1

create table Author (Author_ID number(10) primary key, FIRST_NAME varchar2(50), LAST_NAME varchar2(50));

create table Book_authors_books (ISBN number(20) FOREIGN KEY REFERENCES Book_descriptions (ISBN), Author_ID number(10) FOREIGN KEY REFERENCES Author(Author_ID));

create table Book_descriptions (ISBN number(20) primary key, Title varchar2(50), Price number(10,2), Publisher varchar2(50), Pub_date date, Edition varchar2(50), Pages number(10));

Question 2

Insert into Author(Author_ID, FIRST_NAME, LAST_NAME) values (111,' Ali', 'Sami')

Insert into Author(Author_ID, FIRST_NAME ,LAST_NAME) values (222,' Mohammed', 'Khalid')

Insert into Author(Author_ID ,FIRST_NAME, LAST_NAME) values (333,'Saad', 'Ahmed')

insert into Book_authors_books (ISBN, Author_ID) values (15111,111)

insert into Book_authors_books (ISBN, Author_ID) values (16222, 222)

insert into Book_authors_books (ISBN, Author_ID) values (17222 ,222)

insert into Book_authors_books (ISBN, Author_ID) values (18111, 111)

insert into Book_authors_books (ISBN, Author_ID) values (15111 ,333)

insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values(15111 ,'Math' , 37.99, 'New Math', 'January 20, 2016', 1,256)

insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values(16222, 'The Art of Strategy', 26.94, 'AWP', 'June 21, 2014', 3, 250)

insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values(17222, 'Calculus', 25, 'New Math', 'January 11, 2017', 1 ,753)

insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values(18111, 'Game Theory', 40.99 'MIT', 'January 01, 2016' ,2, 333)

insert into Book_descriptions (ISBN, Title, Price, Publisher, Pub_date, Edition, Pages) values(15111 ,'Graph Theory', 100, 'John Wiley', 'January 01, 2018', 1, 320)

Question 3

1. SELECT Title FROM Book_descriptions WHERE Price < 35;

2. SELECT FIRST_NAME, LAST_NAME FROM Author ORDER BY FIRST_NAME;

3. SELECT MAX(Price) FROM Book_descriptions WHERE Publisher = "New Math";

4. UPDATE Book_descriptions SET Price = Price * 1.35
WHERE Pub_date = 2015;

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.