In going through some homework, I was able to do some of the problems, but not others. For example, one problem was "Produce a list of employee last name, first name, job function, department name and department location. Use appropriate, user friendly column aliases." I finished this with this code:
SELECT EMPLOYEE.LAST_NAME "Last Name", EMPLOYEE.FIRST_NAME "First Name", JOB.FUNCTION "Job Function", DEPARTMENT.NAME "Department", LOCATION.REGIONAL_GROUP "Location" FROM DEMO.EMPLOYEE, DEMO.JOB, DEMO.DEPARTMENT, DEMO.LOCATION WHERE JOB.JOB_ID=EMPLOYEE.JOB_ID AND EMPLOYEE.DEPARTMENT_ID=DEPARTMENT.DEPARTMENT_ID AND DEPARTMENT.LOCATION_ID=LOCATION.LOCATION_ID ORDER BY EMPLOYEE.LAST_NAME
However, I have several similar problems that do not work. They all give me the same error: "ERROR at line 1: ORA-00937: not a single-group group function". For example, one problem states to "Produce a report that shows the description of the product and the sales price for the product with the highest unit sales price. Use appropriate, user friendly column aliases." I tried to solve this in a similar fashion, but got the above error. Here is what I did:
SELECT PRODUCT.DESCRIPTION "Product", (MAX(PRICE.LIST_PRICE)) "Item Price" FROM DEMO.PRODUCT, DEMO.PRICE WHERE PRICE.PRODUCT_ID=PRODUCT.PRODUCT_ID
PRODUCT has columns
PRODUCT_ID, DESCRIPTION and table
PRICE has columns
PRODUCT_ID, LIST_PRICE, MIN_PRICE, START_DATE, END_DATE . The
MAX function works on the column
PRICE.LIST_PRICE by itself, but not when the tables are joined. I also can join the tables using
SELECT PRODUCT.DESCRIPTION "Product", PRICE.LIST_PRICE "Item Price" FROM DEMO.PRODUCT, DEMO.PRICE WHERE PRICE.PRODUCT_ID=PRODUCT.PRODUCT_ID ORDER BY PRICE.LIST_PRICE DESC
, but I only need the top entry, not the entire list.
If anyone can see how I can fix this, that would be great. I have 2 other problems that I have exactly the same problem with that I haven't been able to figure out through my textbook or google. Thanks.