Data Management

| December 14, 2015

IS3280 Data Management

Course Assignment – SQL with One Table

 

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

 

To complete this assignment, you must have Oracle 11 g installed. You can also do them in Access. What is required to be turned in is the SQL statements [NO SCREEN SHOTS FOR THIS ASSIGNMENT.]

 

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)

 

Instructions for creating these tables and inserting data are included in the Oracle 11g Express Launch document. An Access version is also available under the Sample Database Folder – the name of the file is: IS3280sampleorg.accdb

 

For each problem, you need to turn in:

  1. the problem statement that is given below
  2. the sql query that solved the problem.

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

 

You can use Oracle 11g to complete this assignment. You may use the command line or SQL Developer. In either case, you will need to connect using the username and password you created previously.

 

If you need to see the names of the Tables (metadata):

In Oracle, the command is: SELECT table_name FROM user_tables;

If you need to know the datatypes of all attributes:

In Oracle, the command is DESCRIBE Products; [for the Products Table]

 

For those who really want to use Access 2010/2013, follow the notes in the file: Using Access for SQL

 


 

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 (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 (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).

 

 

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

Get a 5 % discount on an order above $ 150
Use the following coupon code :
2018DISC
changes in the workforce
Finance homework help

Category: Completed Assignments

Our Services:
Order a customized paper today!