Ordering Management System
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:
- List name and city for all customers (w3schools -> SQL Select)
- List name and price for all products (w3schools -> SQL Select)
- List the names of all agents in alphabetical order. (w3schools -> SQL ORDER BY)
- List all information for all Customers that are located in Dallas. (w3schools -> SQL Where)
- List all information for all Customers that are located in Dallas and have a discount greater than 9. (w3schools -> SQL And & Or)
- List pid of all products ordered – NO duplicates (w3schools -> SQL Distinct)
- List the sum in dollars of all orders (w3schools -> SQL Functions – SQL SUM).
- List the sum in quantity for each different product pid ordered (w3schools -> SQL Functions and SQL Group By).
- How many orders were made in the month of January (jan)? (w3schools -> SQL Function and SQL Where)
- List name and price of products in descending order by price (w3schools -> SQL Order By)
- List cid of customers that ordered ‘p01’ or ‘p02’ (w3schools -> SQL And & Or)
- List aid of Agents that placed an order for customer c003 or customer c006 (w3schools -> SQL And & Or)
- What is the price of the most expensive product? (w3schools -> SQL Function).
- What was the lowest amount (dollar) spent on an order? (w3schools -> SQL Function).
- What are the different levels of commissions that agents can receive? (w3schools -> SQL Function -> Group by OR SQL Distinct).
Buy now
CONTACT DETAILS
For any other questions or other tasks please feel free to contact mevia email: mhassnainjamil@gmail.com
via WhatsApp: +92-324-7042178
via skype: hassnainjamil1
Comments
Post a Comment