0

I have to write a query with the following criteria:

Write a query to list the title and artist of ONLY the items that have been ordered. Only list each title once.

Here are the CREATE tables that were made that cannot be changed. 

CREATE TABLE artists
(
    artist_id   INT NOT NULL,
    artist_name VARCHAR(30),

    CONSTRAINT artist_pk PRIMARY KEY (artist_id)
);

CREATE TABLE items
(  
    item_id    INT NOT NULL,  
    title      VARCHAR(50) NOT NULL,
    artist_id  INT NOT NULL,
    unit_price DECIMAL(9,2) NOT NULL,  

    CONSTRAINT items_pk PRIMARY KEY (item_id),
    CONSTRAINT items_fk_artists
        FOREIGN KEY (artist_id) REFERENCES artists (artist_id)
);

CREATE TABLE orders
(  
    order_id     INT NOT NULL,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,  
    shipped_date DATE, 
    employee_id  INT,  

    CONSTRAINT orders_pk PRIMARY KEY (order_id),
    CONSTRAINT orders_fk_customers
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT orders_fk_employees
        FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

Here is what I have done for my script. 

SELECT 
    items.title, artists.artist_name, orders.order_date
FROM 
    items
JOIN 
    orders
JOIN 
    artists;

Please let me know how I can consolidate.

Community
  • 1
  • 1
Eric Franklin
  • 45
  • 4

1 Answers1

0

You should say the structures of tables with respect to keys between tables.

sorineatza
  • 106
  • 7