Oracle

Need Help in Oracle Assignment?
We can help you if you are having difficulty with your Oracle Assignment. Just email your Oracle Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Oracle Assignment.

Items 21 to 30 of 63 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Grid  List 

Set Ascending Direction
  1. Oracle 11g SQL Joan Casteel Chapter 12 Hands-On Assignments

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

    Regular Price: $10.00

    Special Price: $8.00

    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.

    Learn More
  2. Oracle 11g SQL Joan Casteel Chapter 11 Hands-On Assignments

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

    Regular Price: $10.00

    Special Price: $8.00

    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?

    Learn More
  3. Oracle 11g SQL Joan Casteel Chapter 10 Hands-On Assignments

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

    Regular Price: $8.00

    Special Price: $5.00

    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.

    Learn More
  4. CMIS420 Advanced Relational Database Final Programming Project

    CMIS420 Advanced Relational Database Final Programming Project

    Regular Price: $20.00

    Special Price: $15.00

    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.

    Learn More
  5. CMIS420 Project 3 Star Schema for OVS Database Spool FIle

    CMIS420 Project 3 Star Schema for OVS Database

    Regular Price: $20.00

    Special Price: $15.00

    CMIS420 Project 3 Star Schema for OVS Database


    1) Complete Project #2 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIME (or TIMES if you desire) dimension tables and the SALES_FACTS fact table are created and populated.
    2) Create a user-defined function called VEHICLES_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total vehicles sold by that combination. Execute your function for a sample input value of your choosing to demonstrate that it works correctly.
    3) Create a user-defined function called DOLLARS_BY_VEHICLE_TYPE that receives an input parameter of a concatenated make and model and then queries the VEHICLES and SALES_FACTS tables to return the total gross sales amount of the sales by that combination. Execute your function for a sample input value of your choosing to demonstrate that it works correctly.
    4) Create a stored procedure called STATS_BY_VEHICLE_TYPE that receives an input parameter of the concatenated make and model and then calls your two user-defined functions VEHICLES_BY_VEHICLE_TYPE and DOLLARS_BY_VEHICLE_TYPE. Execute your stored procedure for the same sample input values used earlier to demonstrate that it works correctly.
    5) Develop an SQL query to determine which holiday had the most sales and then drill down via another query to determine for that holiday which dealership, by zip code, had the most sales. Then drill down by another query to determine for that holiday which dealership, by zip code, and make had the most sales. Describe any correlation to the results of the query in Step #9 of Project #1 to see if dealership location matches customer location.
    6) Develop an SQL query to determine how many days, by day type, didn’t have more than 5 vehicles and $100,000 total sales. In order to achieve a fair comparison, analyze your results by the total number of weekdays, weekend days, and holidays in your TIMES table.

    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 each step and then an SQL script file that calls all of them in sequence. 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.

    Learn More
  6. CMIS420 Project 2 Star Schema for OVS Database Spool File

    CMIS420 Project 2 Star Schema for OVS Database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS420 Project 2 Star Schema for OVS Database


    In this assignment you will complete the creation and population of the Star Schema tables for the OVS, Inc. data warehouse database. In the Authentic Assessment (i.e. Final Project) you will be performing a number of decision support queries against this database.
    1) Complete Homework #5 by ensuring that the FINANCING_PLANS, DEALERSHIPS, VEHICLES, and TIME (or TIMES if you desire) dimension tables are created and populated.


    2) Create the SALES_FACTS fact table via SQL. Ensure that you have declared foreign keys of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID to reference your TIME(S), VEHICLES, FINANCING_PLANS, and DEALERSHIPS tables, respectfully. Ensure that you have a primary key for the SALES_FACTS table that is a composite of the Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID columns. Do a DESC (i.e. DESCRIBE) of your SALES_FACTS table after it’s created.


    3) Using PL/SQL populate the SALES_FACTS table. One way to do this is to use a cursor to go through all of your SALES table rows, joined with other tables as necessary, to determine each combination of Sale_Day, Vehicle_Code, Plan_Code, and Dealer_ID. Then for that combination of the four columns, calculate the total Vehicles_Sold and the Gross_Sales_Amount. If these values are zero then don’t INSERT a row into the SALES_FACT table. Only insert rows for combinations of the four foreign key columns where there were some vehicles sold. After populating your SALES_FACTS table execute the query “SELECT COUNT(*) FROM sales_facts;” to show the row count. Also execute the query “SELECT SUM(vehicles_sold) FROM sales_facts;” to ensure that you have included all of your 500 or more sales.


    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 each step and then an SQL script file that calls all of them in sequence. 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.

    Learn More
  7. CMIS 420 Homework 5 Spool File

    CMIS 420 Homework 5 Online Vehicle Sales Data Warehouse

    Regular Price: $15.00

    Special Price: $12.00

    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.

    Learn More
  8. CMIS 420 Project 1 Spool File

    CMIS 420 Project 1 Online Vehicle Sales OVS Database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 420 Project 1 Online Vehicle Sales OVS Database


    This first project is a combination of the four homeworks preceeding it. Therefore, if you've successfully completed all the homeworks you will have little to do this week. If not, you will have some catching up to do. Take a look at your fellow students' shared solutions to see their approaches to setting up SQL script files for the homeworks.
    1) Demonstrate that you have successfully created the 10 OVS database tables by executing the following query "SELECT table_name FROM user_tables". Then perform a DESC (i.e. DESCRIBE) of each table to show its columns, data types, and NOT NULL constraints. Number this step and show both the SQL and the results in your SPOOL/HTML file.
    2) Show that you have 100 total customers in your database by executing the query "SELECT COUNT(*) FROM customers" (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
    3) Show that you have 200 total vehicles in your database by executing the query "SELECT COUNT(*) FROM vehicles" (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
    4) Show that you have 500 total sales in your database by executing the query "SELECT COUNT(*) FROM sales" (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
    5) Show that you have 500 total sales_financings in your database by executing the query "SELECT COUNT(*) FROM sales_financings" (your table name may be different). Number this step and show both the SQL and the results in your SPOOL/HTML file.
    6) Perform the following query for your other 6 tables "SELECT COUNT(*) FROM <table_name>" (insert the appropriate table name). Number this step and show both the SQL and the results in your SPOOL/HTML file.
    7) Via a single SELECT query display the total count of sales, by model and then by zip code, with the highest values first. Use SQL99 syntax for your join conditions. Number this step and show both the SQL and the results in your SPOOL/HTML file.
    8) Via a single SELECT query with a correlated subquery display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID. Number this step and show both the SQL and the results in your SPOOL/HTML file.
    9) Via a single SELECT query display the zip code, make, and count of the largest car purchases (there may be a tie with two or more). One way to do this is to have a subquery that produces the counts by zip code and make, take the maximum, and then have the outer query display the zip code and make combination(s), with the count, that match this maximum count. Number this step and show both the SQL and the results in your SPOOL/HTML file.

    All SQL should be executed via a single SQL script file. Submit a single SQL*Plus SPOOL file produced by the above SQL in your SQL script file showing all your SQL 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 the results. Do NOT submit your SQL script file.

    Learn More
  9. CMIS 420 Homework 4 Spool File

    CMIS 420 Homework 4 Online Vehicle Sales OVS Database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 420 Homework 4 Online Vehicle Sales OVS Database


    1) Complete your OVS database through the Homework #3 requirements.
    2) Add 50 customers so that you now have 100 total. Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
    3) Add 150 vehicles so that you now have 200 total. Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
    4) Add 250 sales so that you now have 500 total (the same vehicle can be sold multiple times). Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
    5) Ensure that you add enough sales financing rows so that you now have 500 total. Consider using a copy, paste, and edit of your SQL statements in your overall SQL script file to make this process easier. Show all your INSERT SQL statements and the results in your SPOOL/HTML file. Execute a SELECT COUNT(*) statement to show the total row count after all data has been added.
    6) Add data to other tables as necessary, show all the SQL, and execute a SELECT COUNT(*) statement to show the total row counts after all data has been added.
    7) Via a single SELECT query display the total count of sales, by model and then by zip code, with the highest values first. Use SQL99 syntax for your join conditions. Show the SQL and the results in your SPOOL/HTML file.
    8) Via a single SELECT query with a correlated subquery display the sale ID and plan ID of all sales in the last 30 days, sorted by sale ID.
    9) Via a single SELECT query display the zip code, make, and count of the largest car purchases (there may be a tie with two or more). One way to do this is to have a subquery that produces the counts by zip code and make, take the maximum, and then have the outer query display the zip code and make combination(s), with the count, that match this maximum count.

    All SQL should be executed via a single SQL script file. Submit a single SQL*Plus SPOOL file produced by the above SQL in your SQL script file showing all your SQL 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 the results. Do NOT submit your SQL script file.

    Learn More
  10. CMIS 420 Homework 3 Spool File

    CMIS 420 Homework 3 Online Vehicle Sales OVS Database

    Regular Price: $15.00

    Special Price: $12.00

    CMIS 420 Homework 3 Online Vehicle Sales OVS Database


    1) Complete your OVS database through the Homework #2 requirements.
    2) Add 25 customers so that you now have 50 total.
    3) Add 25 vehicles so that you now have 50 total.
    4) Add 200 sales so that you now have 250 total.
    5) Via a single SELECT query display the total count of sales, and the lowest, average, and highest sale price.
    6) Via a single SELECT query display all customer IDs, names, and zip codes of customers having purchased 5 or more vehicles.
    7) Via a single SELECT query display the institution and loan type of the financing plan used for the most sales.
    8) Via a single SELECT query display the vehicle make involved in the most sales and all customers having purchased a vehicle of that make.
    9) Produce a detailed and formatted report showing the customer IDs, names, and zipcodes along with makes and models sold with summarized customer counts and make and model counts.

    Learn More

Items 21 to 30 of 63 total

per page
Page:
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Grid  List 

Set Ascending Direction