Question

I need to create a SQL query that displays the names of the customers who purchased the book with the highest retail price in the database. Also I need to capitalize the first and last names.

CUSTOMERS Table:

CUSTOMER#, LASTNAME, FIRSTNAME, ADDRESS, CITY, STATE, ZIP, REFERRED, REGION, EMAIL

ORDERS Table:

ORDER#, CUSTOMER#, ORDERDATE, SHIPDATE, SHIPSTREET, SHIPCITY, SHIPSTATE, SHIPZIP, SHIPCOST

ORDERITEMS Table:

ORDER#, ITEM#, ISBN, QUANTITY, PAIDEACH

Answer 1

-- initlai letter cap INITCaP function in oracle

Select c.Customer#,INITCAP(c.lastame) AS LastName,INITCAP(c.firstname),Max(ol.paideach) as MaxRetail
from customers c inne join orders o on c.customer#=o.customer# inner join orderitems ol on o.order#=ol.order#

--Initial Letter cap in Sql server
Select c.Customer#,
UPPER(LEFT(c.lastame,1))+LOWER(SUBSTRING(c.lastame,2,LEN(c.lastame))) As LastName,
UPPER(LEFT(c.firstname,1))+LOWER(SUBSTRING(c.firstname,2,LEN(c.firstname))) As firstname,
Max(ol.paideach) as MaxRetail
from customers c inne join orders o on c.customer#=o.customer# inner join orderitems ol on o.order#=ol.order#