Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: ORACLE SQL Query

    Here are the tables that I am working with
    Create table Book_customer
    (CustomerID NUMBER(4) CONSTRAINT PK_BOOKCUSTOMER PRIMARY KEY,
    LastName VARCHAR2(10),
    FirstName VARCHAR2(10),
    Address VARCHAR2(20),
    City VARCHAR2(20),
    State VARCHAR2(2),
    Zip VARCHAR2(5),
    Referred NUMBER(4));

    Create Table Book_order
    (OrderID NUMBER(4) CONSTRAINT PK_BOOKORDER_ORDERID PRIMARY KEY,
    CustomerID NUMBER(4) CONSTRAINT FK_BookOrder_BookCustomer REFERENCES Book_Customer (CustomerID),
    OrderDate DATE,
    ShipDate DATE,
    ShipStreet VARCHAR2(20),
    ShipCity VARCHAR2(20),
    ShipState VARCHAR2(2),
    ShipZip VARCHAR2(5));

    Create Table Publisher
    (PubID NUMBER(2) CONSTRAINT PK_PUBLISHER PRIMARY KEY,
    PublisherName VarCHAR2(23),
    ContactName VARCHAR2(20),
    Phone VARCHAR2(12));


    Create Table Author
    (AuthorID Number(4) CONSTRAINT PK_AUTHOR PRIMARY KEY,
    Lname VARCHAR2(10),
    Fname VARCHAR2(10));


    Create table Books
    (BOOKID NUMBER(15) CONSTRAINT PK_BOOKS PRIMARY KEY,
    ISBN VARCHAR2(10),
    Title VARCHAR2(30),
    PubDate DATE,
    PubID NUMBER (2) CONSTRAINT FK_BOOKS_PUBLISHER REFERENCES PUBLISHER (PUBID),
    Cost NUMBER (5,2),
    Retail NUMBER (5,2),
    Category VARCHAR2(12));


    CREATE TABLE ORDER_ITEMS
    (ORDERID NUMBER(4) NOT NULL CONSTRAINT FK_ORDERITEMS_BOOKORDER REFERENCES Book_Order (OrderID),
    ITEMNUM NUMBER(2) NOT NULL,
    BOOKID NUMBER(15) CONSTRAINT FK_ORDERITEMS_BOOKS REFERENCES BOOKS (BOOKID),
    QUANTITY NUMBER(3),
    constraint pk_ORDER_ITEMS PRIMARY KEY (orderid, bookid));


    CREATE TABLE BOOK_AUTHOR
    (BOOKID NUMBER(15) CONSTRAINT fk_BookAuthor_Books REFERENCES BOOKS (BOOKID),
    AUTHORid NUMBER(4) CONSTRAINT fk_BookAuthor_Author REFERENCES Author (AUTHORID),
    CONSTRAINT pk_BOOK_AUTHOR PRIMARY KEY (BOOKID,AUTHORID));


    create table promotion
    (PromotionID NUMBER(3) CONSTRAINT pk_promotion PRIMARY KEY,
    gift varchar2(15),
    minretail number(5,2),
    maxretail number(5,2));


    And this is what the query needs to accomplish:
    Using the correct tables, create a sub query using either join operation you wish that will list the customer number, first and last name concatenated together, city and number of copies ordered for all customers who have placed an order for the most expensive book (based on retail price). Give the combined customer names column and alias of "Customer Name".

    Can someone help?!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What would your teacher say if he/she knew some strangers did your homework (and you didn't put any effort in it)?

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    I'm just lost on even a starting point. It's not that I am after a total answer, just some place on how to begin answering.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Start with
    Code:
    SELECT

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >for all customers who have placed an order for the most expensive book (based on retail price).
    MAX() function will be helpful do above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by anacedent View Post
    >for all customers who have placed an order for the most expensive book (based on retail price).
    MAX() function will be helpful do above
    Thank you...that is a tremendous help!!!! I will for future posts, ask a specific question similar to pertaining to the MAX() function as opposed to the question in it's entirity.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •