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 Two:

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 I want Full ANswer for all elements

please Don't miss Anything

Author_ID

FIRST_NAME

LAST_NAME

111

Ali

Sami

222

Mohammed

Khalid

333

Saad

Ahmed

Explanation / Answer

Hi,

Ans 1-

create table author (Author_ID varchar(10), FIRST_NAME varchar(20), LAST_NAME varchar(20));

create table Book_authors_books (ISBN varchar(20), Author_ID varchar(10) references author(author_id));

create table Book_descriptions (ISBN varchar(20) references book_authors_books(isbn), Title varchar(30), Price number(10,2),Publisher varchar(20), Pub_date varchar(20), Edition number10), Pages number(5));

Ans 2-

insert into author values('111','Ali','Sami');

insert into author values('222','Mohammed','Khalid');

insert into author values('333','Saad','Ahmed');

insert into book_authors_books values('15111','111');

insert into book_authors_books values('16222','222');

insert into book_authors_books values('17222','222');

insert into book_authors_books values('18111','111');

insert into book_authors_books values('15111','333');

insert into book_descriptions values('15111',' Math 1',37.99,'New Math','January 20, 2016',1,256);

insert into book_descriptions values('16222','The Art of strategy',26.94,'AWP','June 21, 2014',3,250);

insert into book_descriptions values('17222','Calculus',25,'New Math','January 11, 2017',1,753);

insert into book_descriptions values('18111',' Math 1',37.99,'New Math','January 01, 2016',2,333);

insert into book_descriptions values('15111',' Graph Theory',100,'John Wiley','January 01, 2018',1,320);

Ans 3-

1- select * from book_descriptions where price<35;

2- select * from author order by first_name;

3- select * from book_descriptions where price=(select max(price) from

book_descriptions where publisher='New Math';

4- update book_descriptions set price=price+price*0.10

where RIGHT(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.