[SOLVED] Ordering Management System Database SQL with ONE table.PDF

[SOLVED] Ordering Management System Database SQL with ONE table

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)


Provide the SQL Queries that answer the following questions:

  1. List name and city for all customers (w3schools - SQL Select)

  2. List name and price for all products (w3schools - SQL Select)

  3. List the names of all agents in alphabetical order. (w3schools - SQL ORDER BY)

  4. List all information for all Customers that are located in Dallas. (w3schools - SQL Where)

  5. List all information for all Customers that are located in Dallas and have a discount greater than 9. (w3schools - SQL And & Or)

  6. List pid of all products ordered – NO duplicates (w3schools - SQL Distinct)

  7. List the sum in dollars of all orders (w3schools - SQL Functions – SQL SUM).

  8. List the sum in quantity for each different product pid ordered (w3schools - SQL Functions and SQL Group By).

  9. How many orders were made in the month of January (jan)? (w3schools - SQL Function and SQL Where)

  10. List name and price of products in descending order by price (w3schools - SQL Order By)

  11. List cid of customers that ordered ‘p01’ or ‘p02’ (w3schools - SQL And & Or)

  12. List aid of Agents that placed an order for customer c003 or customer c006 (w3schools - SQL And & Or) 

  13. What is the price of the most expensive product? (w3schools - SQL Function). 

  14. What was the lowest amount (dollar) spent on an order? (w3schools - SQL Function). 

  15. What are the different levels of commissions that agents can receive? (w3schools - SQL Function - Group by OR SQL Distinct). 

Powered by