Question

Oracle 12c Chapter 13 Advanced Challenge

To perform the following activity, refer to the tables in the JustLee Books database about to begin an aggressive ma The Marketing Department of JustLee Books i campaign to generate sales to repeat customers. The strategy is to look at existing customers' past purchases,  which these customers have made purchases, JustLee Books will send promotional information about other books in the category that are highly profitable books for the company. The Marketing Department has requested that you identify the five most frequently purchased books and the percentage of profit each book generates. The percentage of profit can be calculated by using the formula ((retail-cost)/cost 100). The employees in the Marketing Department will use the potential profitability of the marketing campaign to determing how much money to budget for the campaign. Provide an SQL statement along with the output to respond to the Marketing Departments request

This is what I got but I'm getting this error ORA-00933: SQL command not properly ended

select b.Title as book_name,(100.0 * (b.retail-b.cost)) /( select b.cos from Books b ) as percent_profit from Books b inner join ORDERITEMS oi on b.isbn=oi.orderitems_isbn_fk order by oi.Quantity desc limit 5;

Answer 1

Solution:

There is a minor issue with your query :

select b.Title as book_name,(100.0 * (b.retail-b.cost)) /( select b.cos from Books b ) as percent_profit from Books b inner join ORDERITEMS oi on b.isbn=oi.orderitems_isbn_fk order by oi.Quantity desc limit 5;

This should be cost. So the correct query would be :

select b.Title as book_name,(100.0 * (b.retail-b.cost)) /( select b.cost from Books b ) as percent_profit from Books b inner join ORDERITEMS oi on b.isbn=oi.orderitems_isbn_fk order by oi.Quantity desc limit 5;