[SOLVED] Database SQL Queries for Ordering System - Multiple Tables.PDF

[SOLVED] Database SQL Queries for Ordering System - Multiple Tables

Use this file as your template and place your SQL code {not the results} beneath each question.

Use the following tables to answer the questions:
CUSTOMERS(cid, cname, city, discnt)
AGENTS(aid, aname, city, commission)
PRODUCTS(pid, pname, city, quantity, price)
ORDERS(ordno, ordmonth, cid, aid, pid, qty, dollars)

Construct queries to answer the following:

  1. List the name of all products for which an order was placed. (w3schools -- SQL Inner Join)

  2. List the name of customers that ordered product ‘p07’ (W3schools- SQL Inner Join & SQL Where)

  3. List name of agents that placed an order for customer c003 or customer c006 (w3schools- SQL Inner Join and SQL IN -- or SQL And & Or)

  4. List name of customers that ordered product ‘p01’ through agent 'a01' (w3schools- SQL Inner Join and SQL And & Or)

  5. List the name of each customer that placed an order, the pid of what they ordered and also the customers that did not place an order. (w3schools-- SQL Left Join)

  6. List the name of each customer that placed an order and the product name for each product they ordered. (w3schools-- SQL Inner Join)

  7. List the name of each customer and the total amount ordered by the customers and also list the customers that did not place an order. (w3schools- SQL Left Join, SQL Functions, SQL Group By)

  8. List the name and the sum of dollars for each customer that ordered more than $1,000 (w3schools -- SQL Inner Join, SQL Functions, SQL Having)

  9. List the agent name, product name and customer name for each product ordered. (w3schools - Inner Join)

  10. In words, explain the result of the following SQL Statement: SELECT * from CUSTOMERS, PRODUCTS 

Powered by