SQL Plus homework:

The sql script is not necessarily needed to write the code.

1. Run the provided Script The provided script PublicationScript.sql allows to create tables representing a given publisher publishing a set of books, and every book contains a number of chapters. From Oracle SQL plus command prompt, run the provided script PublicationScript.sql using one of these commands:

start E:\Lab6\PublicationScript.sql

@ E:\Lab6\PublicationScript.sql

2. a) Add PublishDate column of DATE type on Book table using DDL (ALTER TABLE) command.

b) Update table Book with instance of data shown in the figure below using DML (UPDATE SET) command.

3.

a) Execute an appropriate UPDATE SQL command on book table using the substitution variable & as shown in Figure 1.

b) Define a variable named ISBN set to 123-456 using Define SQL*Plus command. Using the defined variable, execute an appropriate UPDATE query on book table as in Figure 2.

c) Execute an appropriate UPDATE query on book table using the Case structure as follow: If b_type=BG, set the price increase to 10%. If b_type=MD, set the price increase to 20%. If b_type=EX, set the price increase to 30%. You should get the data of Figure 3.

d) Execute an appropriate selection query on book table using a suitable character function to display the list of books as shown in Figure 4.

e) Execute an appropriate selection query on publisher table to display the list of publishers, with respect to the length of publisher name as shown in Figure 5.

f) Execute an appropriate selection query on publisher table to display the list of publisher, along with the position of blank character of publishers name, and with respect to the length of publishers name as shown in Figure 6 (Hint: use INSTR character function).

g) Execute an appropriate selection query on publisher table to display the list of publisher, where substrings of publishers name are displayed in separated columns as in Figure 7 (Hint: use SUBSTR character function).

h) Execute an appropriate selection query on book table using a suitable numeric function to display the price of books rounded to their next higher integer as shown in Figure 8.

i) Execute an appropriate selection query on book table using DECODE function to display the level of books, with respect to field b_type, and where substrings of authors name are displayed in separated columns as shown in Figure 9.

j) Execute an appropriate selection query on book table using a TO_CHAR conversion function to display the list of books as shown in Figure 10.

k) Execute an appropriate selection query on book table using the Case structure to display the list of books getting a discount as follow: If b_type=BG, set the price discount to 15%. If b_type=MD, set the price discount to 25%. If b_type=EX, set the price discount to 35%. You should get the data shown in Figure 11.

l) Execute an appropriate selection query on book table using MAX, MIN and AVG group functions to display the maximum and minimum of books price as in Figure 12.

m) Execute SQL Command on chapter table to display the list of books (using GROUP clause) and the sum of their chapter price. You should use the HAVING clause for restricting the group of b_id, where the number of chapters is higher or equal than 3. Display the output shown in Figure 13. 3/5

n) Execute an appropriate selection query on Book table to display the list of books and their publishing date using conversion function TO_CHAR and character functions UPPER, LOWER as shown in Figure 14.

o) Execute an appropriate selection query on book table to display the list of books, their publishing date, along with their price with a given format using conversion function TO_CHAR and character function UPPER as shown in Figure 15.

SQL> selectfron book; TITLE Jaua Programning Oracle Database History of Art PHP Progranning Econony and Wealth Business principles B_ID B_AUTHOR B ISBN B PRICE P_ID PUBLISHDA 1 Steu Jeff 2 Anine Khan 3 Eduard Becker 4 James Peter 5 Paul Tremblay 6 Paul Henry 1234987 345675 998766 765432 1209845 654321 BG EX EX MD BG BG 42.4 252.4 282.3 66.7 43.3 12.6 4 13-JUL-97 1 06-JAN-05 5 31-MAY-19 2 16-0CT-11 3 17-AUG-09 4 24-SEP-07 SQL> selectfron book; TITLE Jaua Programning Oracle Database History of Art PHP Progranning Econony and Wealth Business principles B_ID B_AUTHOR B ISBN B PRICE P_ID PUBLISHDA 1 Steu Jeff 2 Anine Khan 3 Eduard Becker 4 James Peter 5 Paul Tremblay 6 Paul Henry 1234987 345675 998766 765432 1209845 654321 BG EX EX MD BG BG 42.4 252.4 282.3 66.7 43.3 12.6 4 13-JUL-97 1 06-JAN-05 5 31-MAY-19 2 16-0CT-11 3 17-AUG-09 4 24-SEP-07