IS3280 Data Management Assignment 5 Course Assignment – SQL with One Table Submi

Question

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.]

Explanation / Answer

PLEASE VERIFY THE SPELLS OF THE DATABASE AND THE SOLUTION POSSIBLE TRY TO WORK OUT ON DATABASE WITH THESE BEFOR SUBMITTING TO AVOID ANY SPELL ERRORS.

1. SELECT cname,city FROM CUSTOMERS;

2. SELECT pname,price FROM PRODUCTS;

3. SELECT aname FROM AGENTS ORDER BY aname;

4. SELECT * FROM CUSTOMERS WHERE city='Dallas';

5. SELECT * FROM CUSTOMERS WHERE city='Dallas' AND dscnt>9;

6. SELECT DISTINCT pid FROM ORDERS ;

7. SELECT SUM(dollars) FROM ORDERS;

8. SELECT pid,SUM(quantity) FROM PRODUCTS GROUP BY pid;

9. SELECT COUNT(ordno) FROM ORDERS WHERE ordmonth='jan';

10. SELECT pname,price FROM PRODUCTS ORDER BY price DESC;

11. SELECT cid FROM ORDERS WHERE pid='p01' OR pid='p02';

12. SELECT aid FROM ORDERS WHERE cid='c003' OR cid='c006';

13. SELECT MAX(price) AS ExpensiveProduct FROM PRODUCTS ;

14. SELECT MIN(dollars) AS LowestOrder FROM ORDERS;

15. SELECT DISTINCT commission FROM AGENTS ;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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