SQL ( ORACLE SQL DEVELOPER ) : THIS IS FIRST , SECOND AND THIRD TABLE for the Task 1 to solve : create table author_45( auth_name char(50), gender char(1), age number(3), nationality char(50), PRIMARY KEY (auth_name) ); create table book_45( isbn number(10), title char(50), ver_no number(2), publisher char(50), PRIMARY KEY (isbn) ); create table writtenBy_45( isbn number(10), auth_name char(50), yr_published number(4), price number(3), no_copy number(4), PRIMARY KEY (isbn,auth_name), foreign key (isbn) references book_45, foreign key (auth_name) references author_45 ); insert into author_45 values ('Benoit','F',42,'French'); insert into author_ 45 values ('Antonini','M',33,'Italian'); insert into author_ 45 values ('McAven','F',31,'Australian'); insert into author_ 45 values ('Williams','M',90,'British'); insert into book_45 values (101,'atabases',1,'Possum'); insert into book_45 values (202,'SQL Primer',2,'Hall'); insert into book_45 values (246,'Databases',1,'West'); insert into book_45 values (500,'Informatics',4,'Hall'); insert into writtenby_45 values (101,'Benoit',1983,100,100); insert into writtenby_45 values (246,'Williams',2000,200,120); insert into writtenby_45 values (500,'McAven',2002,150,90); insert into writtenby_45 values (202,'McAven',2005,300,4); NOW : TASK 1 : Write a function to display appropriate message for a given book based on the criteria given below: if (price <= 50) then ‘Cheap’ if (price> 50 and price <=100) then ‘Affordable’ if (price > 100 and price <= 150) then ‘Expensive’ else ‘Extravagant’

SQL ( ORACLE SQL DEVELOPER ) :

THIS IS FIRST , SECOND AND THIRD TABLE for the Task 1 to solve :

create table author_45(

auth_name char(50),

gender char(1),

age number(3),

nationality char(50),

PRIMARY KEY (auth_name)

);

create table book_45(

isbn number(10),

title char(50),

ver_no number(2),

publisher char(50),

PRIMARY KEY (isbn)

);

create table writtenBy_45(

isbn number(10),

auth_name char(50),

yr_published number(4),

price number(3),

no_copy number(4),

PRIMARY KEY (isbn,auth_name),

foreign key (isbn) references book_45,

foreign key (auth_name) references author_45

);

insert into author_45 values ('Benoit','F',42,'French');

insert into author_ 45 values ('Antonini','M',33,'Italian');

insert into author_ 45 values ('McAven','F',31,'Australian');

insert into author_ 45 values ('Williams','M',90,'British');

insert into book_45 values (101,'atabases',1,'Possum');

insert into book_45 values (202,'SQL Primer',2,'Hall');

insert into book_45 values (246,'Databases',1,'West');

insert into book_45 values (500,'Informatics',4,'Hall');

insert into writtenby_45 values (101,'Benoit',1983,100,100);

insert into writtenby_45 values (246,'Williams',2000,200,120);

insert into writtenby_45 values (500,'McAven',2002,150,90);

insert into writtenby_45 values (202,'McAven',2005,300,4);

NOW :

TASK 1 :

Write a function to display appropriate message for a given book based on the criteria given below:

• if (price <= 50) then ‘Cheap’
• if (price> 50 and price <=100) then ‘Affordable’
• if (price > 100 and price <= 150) then ‘Expensive’
• else ‘Extravagant’