Search results for 'oracle'

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

To perform these assignments, refer to the tables in the JustLee Books database.
1. Determine how many books are in the Cooking category.
2. Display the number of books with a retail price of more than $30.00.
3. Display the most recent publication date of all books sold by JustLee Books.
4. Determine the total profit generated by sales to customer 1017. Note: Quantity should be reflected in the total profit calculation.
5. List the retail price of the least expensive book in the Computer category.
6. Determine the average profit generated by orders in the ORDERS table. Note: The total profit by order must be calculated before finding the average profit.
7. Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn't recently placed an order with JustLee Books.
8. Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
9. List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.
10. What's the retail price of the most expensive book written by Lisa White?

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

To perform the following assignments, refer to the tables in the JustLee Books database.
1. Produce a list of all customer names in which the first letter of the first and last names is in uppercase and the rest are in lowercase.

2. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text "NOT REFERRED" if the customer wasn't
referred to JustLee Books by another customer or "REFERRED" if the customer was referred.

3. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.

4. Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amounts as a percent of the cost.)

5. Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you're using.

6. Create a list of all book titles and costs. Precede each book's cost with asterisks so that the width of the displayed Cost field is 12.

7. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).

8. Using today's date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title,
publication date, current date, and age.

9. Determine the calendar date of the next occurrence of Wednesday, based on today's date.

10. Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists.

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.

CMIS 420 Homework 5 Online Vehicle Sales Data Warehouse

This is a database design for the data warehouse you will create, populate, and query for Project #2 and the Authentic Assessment final project.
This week you will ensure that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIME tables are created and populated.
1) Your FINANCING_PLANS table should already be created and populated. Your Plan_ID primary key is the Plan_Code column. Don't worry about changing this or any other column names you already have.

2) Your DEALERSHIPS table should already be created and populated. Don't worry about changing any column names you already have.

3) Create the VEHICLES table via SQL. If you already have a table with this name, use the SQL RENAME command to change it to something else. For the Vehicle_Code primary key column use an Oracle sequence to populate the values. For the Description column use all concatenated combinations of Make and Model of vehicles you have. Use a PL/SQL block to populate the Description column by SELECTing the combinations from your current vehicles table and then INSERTing the combinations into your new VEHICLES table, which would best be performed via a cursor in a loop. After populating your VEHICLES table execute a "SELECT * FROM vehicles ORDER BY vehicle_code" SQL statement to display the entire contents. Show all your SQL and PL/SQL and the results.

4) Create the TIME table via SQL. The Sale_Day primary key column values should be all dates from your first sale date through and including your last sale date from your SALES table. The Day_Type values should be 'Weekday', 'Weekend', or 'Holiday' (this trumps Weekday and Weekend). Use a PL/SQL block to populate the TIME table. After populating your TIME table execute the SQL statement "SELECT day_type,COUNT(*),MIN(sale_day),MAX(sale_day) FROM time GROUP BY day_type ORDER BY day_type" to show the summarized contents of your table. Show all your SQL and PL/SQL and the results.

All SQL and PL/SQL should be executed via one or more SQL script files. The best approach is to have separate SQL script files for Steps #3 and #4 and then an SQL script file that calls both of them. Submit SQL*Plus SPOOL files produced by your SQL and PL/SQL showing all your SQL and PL/SQL code and the results, or if using iSQL*Plus or other GUI (e.g. SQL Developer), a single Word or PDF file of screen snapshots showing both your SQL and PL/SQL and the results. Do NOT submit your SQL script file.

CMIS 420 Homework 2 Online Vehicle Sales Database

1) Complete the creation and population of your Homework #1 tables and include all necessary integrity constraints including primary keys, foreign keys, CHECK constraints, UNIQUE constraints, and NOT NULL constraints. All tables must have a primary key and you must have a foreign key on the many side of each one-to-many relationship in the instructor-supplied ERD. Note that you must use Oracle for your database and and you must use actual SQL statements vs. a graphical approach. You may use Nova or any other Oracle instance you wish. You may use SQL*Plus, iSQL*Plus, or other command line tool (e.g. SQL Developer).
2) Add 20 more rows to both your VEHICLES and CUSTOMERS tables to have 25 rows total in each table and display the row counts of these tables.
3) Add 40 more rows to your SALES table to have 50 rows total and display the row count.
4) Create the SALE_FINANCINGS table if necessary, populate it as necessary, and show the row count.
5) Execute a multi-table join SELECT statement to display the number of sales by make of car, sorted by make.
6) Execute a multi-table join SELECT statement to display the number of sales by customer zip code, sorted by zip code.
7) Execute a multi-table join SELECT statement to display the number of sales by make of car and customer zip code, sorted by make and then by zip code.
8) Using a set operator execute a single SELECT statement to display together all the customer last names and all the salesperson last names, with no duplicates.
9) Using a set operator, subquery, and/or join query execute a SELECT statement to display the customers by customer ID, last name, and zip code who have not been involved in a sale, sorted by customer ID.
10) Using a correlated subquery execute a SELECT statement to display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID.

The SQL for Steps #5 through #10 above must all be a single SELECT statement.
You must show ALL your SQL statements and ALL your results, together, for ALL SQL statements in Steps #1 through #10 above.
If using SQL*Plus then submit a single SPOOL file with everything. If using iSQL*Plus or another interface tool then include a single Word or PDF file of screen snapshots for everything.
In your single file label your SQL statements (#1 through #10) and results and put them in order.
You must use an SQL script file vs. typing ad hoc SQL, but do NOT submit your SQL script file.

CMIS 420 Homework 1 Online Vehicle Sales Database

You are part of a development team with Ace Software, Inc. who has recently been contracted to develop various database capabilities for Online Vehicle Sales, Inc. (OVS). OVS is a startup "dotcom" with about 10 dealership locations in Maryland, Virginia and Washington, D.C. They sell new and used cars (compacts, midsizes and full-sizes), sport utility vehicles (SUVs) and light trucks. Currently their business is just based on customers visiting one of the 10 dealership locations in person, but soon they plan to move the bulk of their business the Internet. Initially they have expressed a desire to have a custom OLTP database, and a custom DSS database, designed and built by your company. Each dealership has a staff of salespersons who assist customers in the purchase of different types of vehicles for which various financing plans are available. New and used vehicles are provided to each dealership based on sales and inventory needs.
An ERD for a 3NF normalized online transaction processing (OLTP) relational database for this application is provided.
Using an SQL script file create Oracle tables for the entities shown in the ERD. Use the plural form of the entity name for your table names.
You can create your database on Nova or any other Oracle system you wish.
Populate the VEHICLES and CUSTOMERS tables in your OLTP database with at least five rows each. Your DEALERSHIPS, SALESPERSONS and FINANCING_PLANS tables should have at least three rows each. Your SALES table should have at least 10 rows, using a variety of different customers, vehicles, salespersons, dealerships and financing plans. All other tables should have at least one row each. Run SELECT * statements on all your tables after they are populated to show all contents.
Submit the following in either an SQL*Plus SPOOL file or screen snapshots of the output if using iSQL*Plus:
1) The names of all your tables from the output of the SQL statement “SELECT table_name FROM user_tables”.
2) A DESC (i.e. DESCRIBE) of all tables to show the column names.
3) The contents of all tables from SELECT * statements
4) Do NOT submit your SQL script files. Only submit the output specified in Steps #1 though #3 above.

Oracle Database to Track Students Grade

Build an Oracle database to track students grade in a class:
The database tracks
*Student Information
*Instructor Information
*Class information
*Grading Breakdown
*Students' Grades

Different queries will show
*List of students with semester grades
*List of students who received an A
*Average grades
*Above average grades

MIS 562 Week 5 Homework Query Optimization

Using the student schema from week 2, provide answers to the following questions.

Question
SQL statement or Answer
1. Generate statistics for the student, enrollment, grade, and zipcode tables (15 pts)

2. Write a query that performs a join, a subquery, a correlated subquery using the student, enrollment, grade, and zipcode tables. Execute each query to show that it produces the same results. (15 pts)

3. Produce an autotrace output and explain plan for each query. (10 pts)

4. Analyze the results and state which performs best and why. Write an analysis of what operations are being performed for each query. Determine which query is the most efficient and explain why (10 pts)

CMIS 320 Lab 4 Homework 4 Choose Oracle datatypes

Instructions
Choose Oracle datatypes for the following attributes from a normalized relation including:
Vendor(VendorID, Address, ContactName)
Item (ItemID, Description)
PriceQuote(VendorID, ItemID, Price)
Describe why you selected the datatypes for each attribute.