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,'
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’
Step by step
Solved in 2 steps
