Script for query:
create or replace procedure FindProducts(category1 in varchar, keyword1 in varchar) is
p1# varchar;
price1 number;
begin
select p# into p1#
from keyword, category1
where keyword.kword = keyword1
and category1.p# = p1#;
--
select price into price1
from product
where p# = p1#;
--
update userchoice
set p# = p1#
set price = price1
commit;
end FindProducts;
execute FindProducts(‘book’, ‘database’);
select * from userchoice;
Question:
| TNAME | TABTYPE | CLUSTERID |
| AUTHOR | TABLE | - |
| BOOK | TABLE | - |
| CDROM | TABLE | - |
| CEVALUATION | TABLE | - |
| CUSTOMER | TABLE | - |
| DBMS_XPLAN | SYNONYM | - |
| JISSUE | TABLE | - |
| JOURNAL | TABLE | - |
| KEYWORD | TABLE | - |
| PBASKET | TABLE | - |
| PP | TABLE | - |
| PRODUCT | TABLE | - |
| V$SESSION | SYNONYM | - |
| V$SQL | SYNONYM | - |
| V$SQLSTATS | SYNONYM | - |
| V$SQL_PLAN | SYNONYM | - |
| V$SQL_PLAN_STATISTICS_ALL | SYNONYM | - |
| VIDEO | TABLE | - |
script for table:
CREATE TABLE Product(
p# VARCHAR(8) NOT NULL, /* Product number */
price NUMBER(7,2) NOT NULL, /* Price per item */
manufacturer VARCHAR(30), /* Manufacturer name */
pcomment LONG , /* Brief comments */
CONSTRAINT Product_PK PRIMARY KEY(p#)
);
CREATE TABLE Keyword(
p# VARCHAR(8) NOT NULL, /* product number */
kword VARCHAR(30) NOT NULL, /* Keyword */
CONSTRAINT Keyword_PK PRIMARY KEY(p#, kword),
CONSTRAINT Keyword_FK1 FOREIGN KEY(p#) REFERENCES Product(p#)
);
CREATE TABLE Customer(
c# NUMBER(20) NOT NULL, /* Customer number */
fname VARCHAR(30) NOT NULL, /* First name */
lname VARCHAR(30) NOT NULL, /* Last name */
email VARCHAR(100) NOT NULL, /* E-mail address */
fax# NUMBER(20), /* Fax number */
country VARCHAR(30) NOT NULL, /* Country part of address */
state VARCHAR(30), /* State part of address */
city VARCHAR(30) NOT NULL, /* City part of address */
pcode VARCHAR(10), /* Post code part of address */
street VARCHAR(30) NOT NULL, /* Street part of addres */
house# NUMBER(6) NOT NULL, /* House number part fo adress */
flat# NUMBER(6), /* Flat number part fo address */
CONSTRAINT Customer_PK PRIMARY KEY(c#)
);
CREATE TABLE Pbasket(
whencreated DATE NOT NULL, /* Transferred from Abasket */
whenfinalised DATE NOT NULL, /* Date time when finalised */
ccard number(20) NOT NULL, /* Credit card used */
c# NUMBER(20) NOT NULL, /* Customer number */
CONSTRAINT Pbasket_PK PRIMARY KEY(whenfinalised),
CONSTRAINT Pbasket_CK UNIQUE(whencreated),
CONSTRAINT PBasket_FK1 FOREIGN KEY(c#) REFERENCES Customer(c#)
);
CREATE TABLE PP(
whenfinalised DATE NOT NULL, /* Date time when finalised */
p# VARCHAR(8) NOT NULL, /* Product number */
CONSTRAINT PP_PK PRIMARY KEY(whenfinalised, p#),
CONSTRAINT PP_FK1 FOREIGN KEY(whenfinalised)
REFERENCES Pbasket(whenfinalised),
CONSTRAINT PP_FK2 FOREIGN KEY(p#)
REFERENCES Product(p#)
);
CREATE TABLE Cevaluation(
c# NUMBER(20) NOT NULL, /* Customer number */
etext LONG NOT NULL, /* Text of evaluation */
erank NUMBER(1) NOT NULL, /* Rank provided by customer */
p# VARCHAR(8) NOT NULL, /* Product number */
CONSTRAINT Cevaluation_PK PRIMARY KEY(c#, p#),
CONSTRAINT Cevaluation_FK1
FOREIGN KEY(c#) REFERENCES Customer(c#),
CONSTRAINT Cevaluation_FK2
FOREIGN KEY(p#) REFERENCES Product(p#),
CONSTRAINT Cevaluation_CHK CHECK
( erank IN (0, 1, 2, 3, 4, 5) )
);
CREATE TABLE Book(
ISBN VARCHAR(20) NOT NULL, /* ISBN */
title VARCHAR(256) NOT NULL, /* Book title */
publisher VARCHAR(50) NOT NULL, /* Publisher company */
pyear NUMBER(4) NOT NULL, /* Publication year */
description LONG NOT NULL, /* Description */
topic VARCHAR(30) NOT NULL, /* Topic */
p# VARCHAR(8) NOT NULL, /* Product number */
CONSTRAINT Book_PK PRIMARY KEY(p#),
CONSTRAINT Book_CK1 UNIQUE(ISBN),
CONSTRAINT Book_CK2 UNIQUE(title),
CONSTRAINT Book_FK1 FOREIGN KEY(p#)
REFERENCES Product(p#),
CONSTRAINT Book_CHK1 CHECK
(topic IN ('ART', 'ARCHITECTURE', 'BIOGRAPHY', 'CHILDREN', 'COMPUTER', 'COOKING', 'FOOD AND WINE',
'HEALTH', 'HISTORY', 'GEOGRAPHY', 'SCIENCE', 'RELIGION', 'ROMANCE', 'SPORT', 'OTHER' )),
CONSTRAINT Book_CHK2 CHECK
( pyear > 1900 )
);
CREATE TABLE Author(
ISBN VARCHAR(20) NOT NULL, /* ISBN */
fname VARCHAR(30) NOT NULL, /* First name */
lname VARCHAR(30) NOT NULL, /* Last name */
CONSTRAINT Author_PK PRIMARY KEY(ISBN, fname, lname),
CONSTRAINT Author_FK1 FOREIGN KEY(ISBN)
REFERENCES Book(ISBN)
);
CREATE TABLE Journal(
ISSN VARCHAR(20) NOT NULL, /* ISSN */
title VARCHAR(256) NOT NULL, /* Title */
publisher VARCHAR(50) NOT NULL, /* Publisher company */
p# VARCHAR(8) NOT NULL, /* Product number */
CONSTRAINT Journal_PK PRIMARY KEY(p#),
CONSTRAINT Journal_CK1 UNIQUE(ISSN),
CONSTRAINT Journal_CK2 UNIQUE(title),
CONSTRAINT Journal_FK1 FOREIGN KEY(p#)
REFERENCES Product(p#)
);
CREATE TABLE Jissue(
ISSN VARCHAR(20) NOT NULL, /* ISNN */
vol# NUMBER(2) NOT NULL, /* Volume number */
issue# NUMBER(2) NOT NULL, /* Issue number */
p# VARCHAR(8) NOT NULL, /* Product number */
CONSTRAINT Jissue_PK PRIMARY KEY(p#),
CONSTRAINT Jissue_CK1 UNIQUE(ISSN, vol#, issue#),
CONSTRAINT Jissue_FK1 FOREIGN KEY(p#)
REFERENCES Product(p#),
CONSTRAINT Jissue_FK2 FOREIGN KEY(ISSN)
REFERENCES Journal(ISSN)
);
CREATE TABLE CDROM(
p# VARCHAR(8) NOT NULL, /* Product number */
title VARCHAR(256) NOT NULL, /* Title */
producer VARCHAR(50) NOT NULL, /* Producer */
pyear NUMBER(4) NOT NULL, /* Production year */
category VARCHAR(30) NOT NULL, /* Category */
CONSTRAINT CDROM_PK PRIMARY KEY(p#),
CONSTRAINT CDROM_CK1 UNIQUE(title),
CONSTRAINT CDROM_FK1 FOREIGN KEY(p#)
REFERENCES PRODUCT(p#),
CONSTRAINT CROM_CHK1 CHECK
( category IN ('CLASSIC', 'POP', 'ROCK','JAZZ',
'KIDS', 'OTHER' ) )
);
CREATE TABLE Video(
p# VARCHAR(8) NOT NULL, /* Product number */
title VARCHAR(256) NOT NULL, /* Title */
producer VARCHAR(50) NOT NULL, /* Producer */
pyear NUMBER(4) NOT NULL, /* Production year */
topic VARCHAR(30) NOT NULL, /* Topic */
CONSTRAINT Video_PK PRIMARY KEY(p#),
CONSTRAINT Video_CK1 UNIQUE(title),
CONSTRAINT Video_FK1 FOREIGN KEY(p#)
REFERENCES PRODUCT(p#),
CONSTRAINT Video_CHK1 CHECK
( topic IN ('ACTION', 'ADVENTURE', 'CLASSIC',
'COMEDY', 'HORROR', 'DRAMA', 'SCIENCE FICTION',
'KIDS AND FAMILY', 'OTHER' ) )
);
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- Populating the sample database
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* BOOKSHOP */
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
INSERT INTO Product VALUES
('P01', 23.95, 'Prentice Hall', 'Magnificent colours !!!' );
INSERT INTO Product VALUES
('P02', 50.00, 'Wiley', 'Proven in the last to decades UNIX is here to stay. Unix is the backbone of Internet. This book is the best book about Unix ever published');
INSERT INTO Product VALUES
('P03', 5.90, 'Wiley', 'The best SQL book ever !!!' );
INSERT INTO Product VALUES
('P04', 49.95, 'Oracle', 'This journal soars above and beyond the world of traditional journals' );
INSERT INTO Product VALUES
('P05', 8.95, NULL, NULL );
INSERT INTO Product VALUES
('P06', 5.00, NULL, NULL );
INSERT INTO Product VALUES
('P07', 25.30, 'Andromeda Pty Ltd', 'Very impressive !!!' );
INSERT INTO Product VALUES
('P08', 12.56, 'New Impressions', 'This is a dazzling display of vivid orchestral colour and dashing rhytmic exuberance');
INSERT INTO Product VALUES
('P09', 10.05, 'Very Heavy Metal Corp.', '... Remarkable, powerful, ... dynamic. ... superior ...' );
INSERT INTO Product VALUES
('P10', 15.00, NULL, NULL);
INSERT INTO Product VALUES
('P11', 27.00, 'Prentice Hall', 'Unbelievable!' );
INSERT INTO Keyword VALUES
('P01', 'FLOWER');
INSERT INTO Keyword VALUES
('P01', 'GARDENING');
INSERT INTO Keyword VALUES
('P01', 'COLOUR');
INSERT INTO Keyword VALUES
('P02', 'UNIX');
INSERT INTO Keyword VALUES
('P02', 'OPERATING SYSTEM');
INSERT INTO Keyword VALUES
('P03', 'SQL');
INSERT INTO Keyword VALUES
('P04', 'DATABASE');
INSERT INTO Keyword VALUES
('P04', 'SQL');
INSERT INTO Keyword VALUES
('P07', 'OVERTURE');
INSERT INTO Keyword VALUES
('P07', 'GARDENING');
INSERT INTO Keyword VALUES
('P07', 'COMPUTER');
INSERT INTO Keyword VALUES
('P08', 'SYMPHONY');
INSERT INTO Keyword VALUES
('P09', 'SYMPHONY');
INSERT INTO Keyword VALUES
('P09', 'ORCHESTRA');
INSERT INTO Keyword VALUES
('P10', 'VOYAGER');
INSERT INTO Keyword VALUES
('P10', 'STARSHIP');
INSERT INTO Keyword VALUES
('P11', 'SPORT');
INSERT INTO Keyword VALUES
('P11', 'DRUG');
INSERT INTO Keyword VALUES
('P11', 'CYCLING');
INSERT INTO Keyword VALUES
('P11', 'CANCER');
INSERT INTO Customer VALUES
(100, 'Janusz', 'Getta', 'jrg@cs.uow.edu.au', NULL, 'Australia', 'NSW', 'Wollongong', '2530', 'Barham Pl.', 6, NULL );
INSERT INTO Customer VALUES
(101, 'Kevin', 'Corbley', 'kc@cs.wisc.edu', NULL, 'USA', 'Winsconsin', 'Northpool', 'WI567-23', '234', 4, 6);
INSERT INTO Customer VALUES
(102, 'Ann', 'Boleyn', 'ann@ozemail.coma.au', 610242347819, 'Australia', 'Wagga', '3489',NULL, 'Station', 34, NULL);
INSERT INTO Pbasket VALUES
( TO_DATE('02-APR-2019:10:00', 'DD-MON-YYYY:HH:MI'), TO_DATE('02-APR-2019:10:40', 'DD-MON-YYYY:HH:MI'), 4594567829, 100 );
INSERT INTO Pbasket VALUES
( TO_DATE('12-APR-2019:01:00', 'DD-MON-YYYY:HH:MI'), TO_DATE('12-APR-2019:01:10', 'DD-MON-YYYY:HH:MI'), 1294522229, 101 );
INSERT INTO Pbasket VALUES
( TO_DATE('08-MAY-2019:05:00', 'DD-MON-YYYY:HH:MI'), TO_DATE('08-MAY-2019:05:55', 'DD-MON-YYYY:HH:MI'), 1294522229, 101 );
INSERT INTO Pbasket VALUES
( TO_DATE('16-JAN-2019:06:00', 'DD-MON-YYYY:HH:MI'), TO_DATE('16-JAN-2019:06:15', 'DD-MON-YYYY:HH:MI'), 6781394229, 102 );
INSERT INTO PP VALUES
( TO_DATE('12-APR-2019:01:10', 'DD-MON-YYYY:HH:MI'), 'P02' );
INSERT INTO PP VALUES
( TO_DATE('08-MAY-2019:05:55', 'DD-MON-YYYY:HH:MI'), 'P05' );
INSERT INTO PP VALUES
( TO_DATE('08-MAY-2019:05:55', 'DD-MON-YYYY:HH:MI'), 'P06' );
INSERT INTO PP VALUES
( TO_DATE('08-MAY-2019:05:55', 'DD-MON-YYYY:HH:MI'), 'P03' );
INSERT INTO PP VALUES
( TO_DATE('08-MAY-2019:05:55', 'DD-MON-YYYY:HH:MI'), 'P02' );
INSERT INTO PP VALUES
( TO_DATE('16-JAN-2019:06:15', 'DD-MON-YYYY:HH:MI'), 'P02' );
INSERT INTO PP VALUES
( TO_DATE('16-JAN-2019:06:15', 'DD-MON-YYYY:HH:MI'), 'P07' );
INSERT INTO PP VALUES
( TO_DATE('16-JAN-2019:06:15', 'DD-MON-YYYY:HH:MI'), 'P08' );
INSERT INTO PP VALUES
( TO_DATE('16-JAN-2019:06:15', 'DD-MON-YYYY:HH:MI'), 'P09' );
INSERT INTO PP VALUES
( TO_DATE('16-JAN-2019:06:15', 'DD-MON-YYYY:HH:MI'), 'P10' );
INSERT INTO Cevaluation VALUES
( 100, 'Excellent !!!', 5, 'P01' );
INSERT INTO Cevaluation VALUES
( 100, 'Very, very average, nothing special', 2, 'P02' );
INSERT INTO Cevaluation VALUES
( 102, 'Magnificent', 5, 'P10' );
INSERT INTO Book VALUES
(18752020860, 'Flowers by Colour', 'Weldon Russel Pty. Ltd.', 1990, 'Flowers by Colour is a comprehensive guide to more than 1,000 popular garden flower', 'OTHER', 'P01' );
INSERT INTO Book VALUES
(12224602080, 'Unix for cops', 'Prentice Hall Pty. Ltd.', 2007, 'This is an ultimate Unix manual', 'COMPUTER', 'P02' );
INSERT INTO Book VALUES
(12755520369, 'SQL for 2nd year students', 'Morgan Kaufman', 2006, 'Comprehensive SQL guide', 'COMPUTER', 'P03' );
INSERT INTO Book VALUES
(12755520370, 'Lance Armstrong Saga', 'Prentice Hall Pty. Ltd.', 2012, 'Based on a true story', 'SPORT', 'P11' );
INSERT INTO Author VALUES
(18752020860, 'Valerie', 'Swan');
INSERT INTO Author VALUES
(12224602080, 'Keith', 'Morgan');
INSERT INTO Author VALUES
(12224602080, 'Mark', 'Thornthwaite');
INSERT INTO Author VALUES
(12755520369, 'Edgar', 'Codd');
INSERT INTO Journal VALUES
(77520368899, 'Oracle Magazine', 'Oracle Publishers', 'P04' );
INSERT INTO Jissue VALUES
(77520368899, 8, 4, 'P05' );
INSERT INTO Jissue VALUES
(77520368899, 8, 5, 'P06' );
INSERT INTO CDROM VALUES
( 'P07', 'Romeo and Juliet, Fantasy Overture', 'Crystal Collection', 1986, 'CLASSIC' );
INSERT INTO CDROM VALUES
( 'P08', 'Tchaikovsky Symphony no. 1', 'Crystal Collection', 1989, 'CLASSIC' );
INSERT INTO CDROM VALUES
( 'P09', 'Tchaikovsky Symphony no. 2', 'Telarc', 1990, 'CLASSIC' );
INSERT INTO Video VALUES
('P10', 'Startrek Voyager', 'Columbia', 1997, 'SCIENCE FICTION' );
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
Appreciate if someone can advise what wrong with my code, thanks.