STEP 1: Using the BOOK_CUSTOMER table and the NVL function, create a query that

Question

STEP 1:
Using the BOOK_CUSTOMER table and the NVL function, create a query that will return a list containing the customer number, first name, last name, and the characters

Explanation / Answer

1. select customerid,firstname,lastname,nvl(to_char(referred),'NOT REFERRED') "REFERRED BY" from book_customer where referred is null or referred not in (select referred from book_customer where referred is not null) 2. select substr(isbn,1,1)||'-'||substr(isbn,2,3)||'-'||substr(isbn,5,5)||'-'||substr(isbn,10,1), title from books where category='COMPUTER' 3. column "Total Retail" format $999.99 column "Average Retail" format $999.99 select distinct category,sum(retail) "Total Retail",avg(retail) "Average Retail" from books group by category having sum(retail)>40 4. select distinct title "Book Title",count(*) "Number of Authors" from books a, book_author b where a.bookid=b.bookid group by title having count(*)>1 5. select bookid,fname,lname from book_author a,author b where a.authorid=b.authorid and bookid in (select bookid from order_items where quantity=(select max(quantity) from order_items) ) 6. select a.customerid,a.firstname||' '||lastname "Customer Name",a.city from book_customer a, book_order b,order_items c where a.customerid=b.customerid and b.orderid=c.orderid and c.itemnum = (select itemnum from books where retail=(select max(retail) from books) ) 7. column "Average" format 999.99 select distinct itemnum,count(*) "Total",avg(quantity) "Average", min(quantity) "Minimum",max(quantity) "Maximum" from order_items group by itemnum 8. select title,pubdate,decode(pubid,1,'PRINTING IS US', 2,'PUBLISH OUR WAY', 3,'AMERICAN PUBLISHING', 4,'READING MATERIALS INC.', 5,'REED-N-RITE', 6,'LITTLE HOUSE',null) "Publisher Name" from books order by "Publisher Name" DESC 9. select 'The contact person for',initcap(publishername)||' is '||initcap(contactname)||'.' from publisher 10. select lastname,city,state,quantity "Number Purchased" from book_customer a,book_order b, order_items c where a.customerID=b.customerid and b.orderid=c.orderid and quantity>2 Here is 11: REM Verification statement (simple) select isbn,pubid,retail from books REM Verification statement (with join to show publisher name): select publishername,retail from books a, publisher b REM Update statement update books set retail = retail*1.05 where pubid = (select pubid from publisher where publishername='PRINTING IS US') Here is 12: select firstname,lastname from book_customer where referred=(select referred from book_customer where firstname='JORGE' and lastname='PEREZ') and firstname != 'JORGE' and lastname != 'PEREZ'; where a.pubid=b.pubid Here is 13: column cost format $999.99 select distinct category,count(*) "Category Total",sum(cost) "Cost" from books group by category

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.