What are the sql queries for the following based on the tables? a) Produce list

Question

What are the sql queries for the following based on the tables?

a) Produce list of product names that have never been ordered

b) Produce list of customer id that have never placed any orders

c) Produce list of all customer id, and number of different products they have ordered (sorted descending order by number of different products)

Productlt ProductName ProductNumber ProductLine ProductClass SALES ORDERS CUSTOMER SalesOrderNumber CustomerID OrderDate DueDate ShipDate SubTotal TaxAmt Freight TotalDue CustomerID CustomerType AddressLinel AddressLine2 Product Color PostalCode ProductlistPrice ProductSize ProductSizeUnitMeasureCode Productweight ProductWeightUnitMeasureCode ORDER_DETAILS SalesOrderDetailID SalesOrderNumber ProductID OrderQty UnitPrice UnitPriceDiscount LineTotal

Explanation / Answer

create table customer(

customerid number,

customertype varchar2(100),

addressline1 varchar2(100),

addressline2 varchar2(100),

city varchar2(50),

postalcode number,

primary key (customerId)

);

create table sales_orders(

salesordernumber number,

customerid number,

orderdate date,

duedate date,

shipdate date,

subtotal number,

taxamount number,

freight varchar2(10),

totaldue number,

primary key (salesordernumber),

foreign key customerid refrences customer(customerid)

);

create table product(

productID number,

productname varchar2(100),

productnumber number,

productline varchar2(100),

productclass varchar2(100),

productstyle varchar2(100),

productcolor varchar2(100),

productstandardCost number,

productlistprice number,

productsize varchar2(10),

productsizemeasurecode varchar2(100),

productweight number,

productweightmeasurecode varchar2(10),

primary key (productid)

);

create table order_details(

salesorderdetailid number,

salesordernumber number,

productid number,

orderqty number,

unitprice number,

unitpricediscount number,

linetotal number,

primary key(salesorderdetailid),

foreign key productid refrences product(productid)

);

a) select productId, productname, productnumber from product where productid not in (select distinct productid from order_details)

b) select customerid from customer where customerid not in (select distinct customerid from sales_orders)

c) select c.customerid, count(*) as TotalProduct from customers c, sales_orders s, order_details o, product p where c.customerid = s.customerid and s.salesordernumber = o.salesordernumber and o.productId = p.productId group by (c.customerid)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.