Search results for 'shipping'

IT358 Unit 7 Synonyms Index and Sequence

Outcomes addressed in this activity:
Describe how the schema objects work
Describe how sequences and indexes are created, modified, and removed within Oracle
Describe when to use an Index
Utilize Synonyms

Project requirements:
In this project, you will create a view, synonym, and index:
1. Answer the Review Questions #1–5 from Chapter 6.
2. Complete Hands-on Assignments #1-10.

Oracle 11G SQL Chapter 6 Hands-On assignment Solution
1. Create a sequence to use for populating the Customer# column of the CUSTOMERS table. When setting the start and the increment values, keep in mind that data already exists in this table. The options should be set to not cycle the values, not cache any values, and no minimum or maximum values should be declared.
2. Add a new customer row using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name = Frank, and zip = 23567.
3. Create a sequence that will generate integers starting with the value 5. Each value should be three less than the previous value generated.The lowest possible value should be 0, and the sequence should not be allowed to cycle. Name the sequence MY_FIRST_SEQ.
4. Issue a SELECT statement that will display NEXTVAL for MY_FIRST_SEQ three times. Because the value is not being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the last SELECT?
5. Change the setting of MY_FIRST_SEQ so the minimum value that can be generated is –1000.
6. Create a private synonym that will allow you to reference the MY_FIRST_SEQ object as NUMGEN.
7. Use a SELECT statement to view the CURRVAL of NUMGEN. Delete the NUMGEN synonym and MY_FIRST_SEQ.
8. Create a Bitmap index on the CUSTOMERS table to speed up queries that search for customers based on their state of residence. Verify that the index exists, and then delete the index.
9. Create a B-tree index on the customer’s Last Name column. Verify that the index exists by querying the data dictionary. Remove the index from the database.
10. Many queries search by the number of days to ship (number of days between the order and shipping dates). Create an appropriate index that might improve the performance of these queries.

CMIS420 Advanced Relational Database Final Programming Project

In this project, you will design a secure database with several tables using good relational database design techniques.

Project Objectives:
After completing this Project, you should be able to:
Explain the purposes of advanced database objects including indexes, clusters, sequences, and views
Develop and implement functions, triggers, and stored procedures/packages

Project Requirements:
1. Using the Oracle database engine, design and develop a relational database to store user information to support a large online shipping company. Information that needs to be stored includes user name, detailed mailing address, and shipping information including number of packages, weight, cost, destination, origin, and tracking information allowing users to determine where a package is, and when and who signed for the package once it arrives. Other business data should be present showing how much the shipment costs and invoicing capability to send bills to customers. Your design should be normalized and well-designed for scalability for additional customers and shipment locations.

2. Your design should allow for inserting, updating, selecting and deleting information

3. A trigger should be used to generate invoices once a customer reaches $200 in shipping costs.

4. An instead-of trigger should be employed to update and/or populate multi-table views.

5. You should include packages, indexes, views and other helpful database components that will assist you in your design.

6. Your scripts should work perfectly allowing the creation of all of the tables, triggers, views and other database components and provide test scripts that demonstrate the use of the system.

7. You do not need a GUI component but your scripts should demonstrate all of the functionality of the system.

Deliverables:
All sql scripts and run scripts and instructions for successfully building your database and testing your database.
An ERD diagram of your database design showing primary keys, foreign keys and other constraints.
3. Well-written word document describing:
a. The database design
b. How to set up and test your database
c. The test data and results you used for your database,
d. Your approach, lessons learned, design strengths, limitations and suggestions for future improvement and alternative approaches, the most challenging aspects of developing this application.

Oracle 11g SQL Joan Casteel Chapter 12 Hands-On Assignments

To perform these activities, refer to the tables in the JustLee Books database. Use a subquery to accomplish each task. Make sure you execute the query you plan to use as the subquery to verify the results before writing the entire query.

1. List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books.
2. Determine which books cost less than the average cost of other books in the same category.
3. Determine which orders were shipped to the same state as order 1014.
4. Determine which orders had a higher total amount due than order 1008.
5. Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books.
6. List the title of all books in the same category as books previously purchased by customer 1007. Don't include books this customer has already purchased.
7. List the shipping city and state for the order that had the longest shipping delay.
8. Determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by JustLee Books.
9. Determine the number of different customers who have placed an order for books written or cowritten by James Austin.
10. Determine which books were published by the publisher of The Wok Way to Cook.

ITSE 2309 LAB 1 Database and SQL Queries

Submit: Query Statements Query Output
Chapters 2,3,4,5 11g SQL book
Objectives: Be able to access ORACLE/SQL Plus Be able to do simple SQL operations

Steps:
- Log onto the sample Oracle 11g database created.
- See Chapter 7 in "Database Systems" for information on coding, saving, and executing your queries.
- Save the output of the queries by using the SPOOL option ( see chpt 14 in 11g SQL in lab resources tab)
- Save both the queries code and the output of the queries as "Lab1_LastName.txt and submit via Blackboard—using the - Attach file – Upload function

Do the following queries:
1. List all columns and rows in the stock table.
2. List the last name, first name, and company of all customers (List the columns in that order). Place the list in alphabetical order by company name.
3. List the company names for all customers from Sunnyvale, Redwood City, or San Francisco.
4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List order number, order date, customer number, ship date, and paid date. (Hint: Specify year in single quotes 'DD-MMM-YYYY')
5. List the order number, order date, and shipping charges for all orders that are not on backlog and for which the shipping charge is over $15.00.
6. List all stock items which are baseball items which have a unit price greater than $200.00 and a manufacturer code which starts with 'H'. (Hint: use LIKE)
7. List the company name for all customers who have orders. Don not list a company more than once.
8. List the customer number and the description (from the stock table) of all items ordered by customers with customer numbers 104-108. Order the output by customer number and description. (There should be no duplicate rows in your output).
9. List the number of (distinct) customers having an order. Label the column "Total_Customers_with_Orders".
10. For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. (Hint: You must use a GROUP BY clause in this query).