IS3280 Data Management

Assignment 5

Course Assignment SQL with One Table

Submit via D2L. Please put your name in your file.

You can do this assignment in Access. What is required to be turned in is the SQL statements

Use this file as your template and place your SQL code 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)

An Access version is available under the Sample Database Folder the name of the file is: IS3280sampleorg.accdb . It is also attached to the assignment.

For each problem, you need to turn in:

the problem statement that is given below

the sql query that solved the problem.

Please turn in the assignment via D2L and be sure to put your name in the file.

Provide the SQL Queries that answer the following questions:

[Hint: the biggest challenge in some of these queries is to be sure to use the correct table. All queries require the use of only ONE table. Anything related to ordering or buying a product would be in the ORDERS table, etc.]

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

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

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

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

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

6) List pid of all products ordered NO duplicates (SQL Distinct)

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

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

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

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

11) List cid of customers that ordered p01 or p02 ( SQL And & Or)

12) List aid of Agents that placed an order for customer c003 or customer c006

( SQL And & Or)

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

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

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

***Please be sure to turn in both the question as listed above and the SQL statement that answers each question above [no need to turn in data results.]