Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Write a query that will return a list of all customers and their invoices. Return only rows where invoices were created by a user who

Write a query that will return a list of all customers and their invoices. Return only rows where invoices were created by a user who never contacted that particular customer.
For each row, return the invoice number, customer name and the number of contacts that started prior to the time the invoice was created. Order the result by invoice number ascending.
Table definitions and a data sample are given below.
Table: customer
column name column type key/NULL
customer_name varchar(255)
city_id int
customer_address varchar(255)
contact_person varchar(255) N
email varchar(128)
phone varchar(128)
Table: invoice
column name column type key/NULL
id int PK
invoice_number varchar(255)
customer_id int FK
user_account_id int
total_price decimal(8,2)
time_issued varchar(255) N
time_due varchar(255) N
time_paid varchar(255) N
time_canceled varchar(255) N
time_refunded varchar(255) N
invoice.customer_id references customer.id
Table: user_account
column name column type key/NULL
id int PK
first_name varchar(64)
last_name varchar(64)
user_name varchar(128)
password varchar(255)
email varchar(128)
phone varchar (128) N
Table: contact
column name column type key/NULL
id int PK
user_account_id int FK
customer_id int FK
contact_type_id int
contact_outcome_id int N
additional_comment varchar(255) N
contact_start_time varchar(255)
contact_end_time varchar(255) N
contact.user_account_id references user_account.id
contact.customer_d references customer.id

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Oracle Database Foundations Technology Fundamentals For IT Success

Authors: Bob Bryla

1st Edition

0782143725, 9780782143720

More Books

Students also viewed these Databases questions