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 31 to 40 of 63 total

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

Grid  List 

Set Descending Direction
  1. 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
  2. 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
  3. MSCD610 Exam SQL

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Oracle Database Exam Oracle 11g SQL 2nd Casteel


    True/False (2 points each)
    Indicate whether the sentence or statement is true or false.
    1. A one-to-many relationship means that an occurrence of a specific entity can only exist once in each table.
    2. A table name can consist of numbers, letters, and blank spaces.
    3. A constraint can only be created as part of the CREATE TABLE command.
    4. The MODIFY clause is used with the ALTER TABLE command to add a PRIMARY KEY constraint to an existing table.
    5. If a FOREIGN KEY constraint exists, then a record cannot be deleted from the parent table if that row is referenced by an entry in the child table.
    6. By default, the lowest value that can be generated by a sequence is 0.
    7. Search conditions for data contained in non-numeric columns must be enclosed in double quotation marks.
    8. Data stored in multiple tables can be reconstructed through the use of an ORDER BY clause.
    9. Rows can be updated through a simple view as long as the operation does not violate existing constraints and the view was created with the WITH READ ONLY option.
    10. By default, the column headings displayed in a report are in upper-case characters.


    Multiple Choice (3 points each)
    Identify the letter of the choice that best completes the statement or answers the question.
    11. Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
    a. one-to-many c. one-to-all
    b. many-to-many d. one-to-one


    Contents of the BOOKS table
    12. Which of the following will display the new retail price of each book as 20 percent more than it originally cost?
    a. SELECT title, cost+.20 "New Retail Price" FROM books;
    b. SELECT title, cost*.20 "New Retail Price" FROM books;
    c. SELECT title, cost*1.20 "New Retail Price" FROM books;
    d. none of the above


    Structure of the CUSTOMERS table
    13. Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
    a. ALTER TABLE customers
    MODIFY (city VARCHAR2(+8), lastname VARCHAR2(+4));
    b. ALTER TABLE customers
    MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
    c. ALTER TABLE customers
    MODIFY (city (+8), lastname (+4));
    d. ALTER TABLE customers
    MODIFY (city (20), lastname (14));


    14. Which of the following statements about the FOREIGN KEY constraint is incorrect?
    a. The constraint exists between two tables, called the parent table and the child table.
    b. When the constraint exists, by default a record cannot be deleted from the parent table if matching entries exist in the child table.
    c. The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.
    d. When the keywords ON DELETE CASCADE are included in the constraint definition, a corresponding child record will automatically be deleted when the parent record is deleted.


    15. Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
    a. ALTER USER rthomas PASSWORD EXPIRE ;
    b. ALTER USER rthomas CHANGE PASSWORD;
    c. ALTER USER rthomas UPDATE PASSWORD;
    d. ALTER USER rthomas EXPIRE PASSWORD;


    16. To instruct Oracle to sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
    a. Asc c. ascending
    b. A d. either a or c


    17. Which of the following is an accurate statement?
    a. When the LOWER function is used in a SELECT clause, it will automatically store the data in lower-case letters in the database table.
    b. When the LOWER function is used in a SELECT clause, the function stays in affect for the remainder of that user's session.
    c. When the LOWER function is used in a SELECT clause, the function only stays in affect for the duration of that SQL statement.
    d. none of the above


    18. Which of the following functions allows for different options, depending upon whether a NULL value exists?
    a. NVL c. IFNVL
    b. IFNL d. NVL2


    Contents of the ORDERS table
    19. Based on the contents of the ORDERS table, which of the following SQL statements will display the number of orders that have not been shipped?
    a. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    b. SELECT order#, COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL
    GROUP BY order#;
    c. SELECT COUNT(shipdate)
    FROM orders
    WHERE shipdate IS NULL;
    d. SELECT COUNT(*)
    FROM orders
    WHERE shipdate IS NULL;


    20. Which of the following is not an example of formatting code available with the FORMAT option of the COLUMN command?
    a. Z
    b. 9
    c. ,
    d. .



    Completion (4 points each)
    Complete each sentence or statement.
    21. A(n) ____________________ is a group of interrelated files.
    22. In an arithmetic expression, multiplication and ____________________ are always solved first in Oracle.
    23. If a constraint applies to more than one column, the constraint must be created at the ______Table______________ level.
    24. After a value is generated, it is stored in the ____________________ pseudocolumn so it can be referenced again by a user.
    25. The ____________________ function is used to round numeric fields to a stated position.



    SQL
    26. (5 points) Consider an employee database with relations where the primary keys are underlined defined as:
    EMPLOYEE (employee name, street, city)
    WORKS (employee name, company_name, salary)
    A – Using sql functions as appropriate, write a query to find companies whose employees earn a higher salary, on average, than the average salary at ABC Corporation


    27. (7 points) Write a SQL script to create this relational schema. Execute the script against the ORACLE database to implement physical database tables. Integrity constraints are listed below.
    EMPLOYEE (name, SSN, BDate, Sex, Salary, SuperSSN, DNO)
    DEPARTMENT (DName, DNumber, MGRSSN, MGRStartDate)
    DEPTLOCATION (DNumber, DLocation)
    PROJECT (PName, PNumber, PLocation, DNum)
    WORKSON (ESSN, PNO, Hours)
    DEPENDENT (ESSN, DEPENDENT_NAME, Sex, BDate, Relationship)


    Integrity Constraints:
    Primary key = Foreign Key
    EMPLOYEE.SSN = DEPENDENT.ESSN
    EMPLOYEE.SSN = WORKSON.ESSN
    EMPLOYEE.SSN = DEPARTMENT.MGRSSN
    EMPLOYEE.SSN = EMPLOYEE.SuperSSN
    DEPARTMENT.DNumber = EMPLOYEE.DNO
    DEPARTMENT.DNumber = DEPTLOCATION.DNumber
    DEPARTMENT.DNumber = PROJECT.DNum
    PROJECT.PNumber = WORKSON.PNO


    28. (18 points) Write SQL syntax to resolve the following queries.
    - Find the names of all employees who are directly supervised by the employee named “John Doe”
    - List the name of employees whose salary is greater than the average salary of his or her corresponding department
    - For each department, retrieve the department name and the average salary of all employees working in that department.

    Learn More
  4. Student Oracle Database Part1 Create Table

    Student Database Oracle DDL and Queries

    Regular Price: $40.00

    Special Price: $30.00

    Student Database Oracle DDL and Queries


    Create the following tables.
    STUDENT
    Student Number (PK)
    Student Last Name
    Student Major
    Department ID (FK)
    Student GPA
    Student Hours
    Student Class
    Advisor ID (FK)


    ADVISOR
    Advisor ID (PK)
    Advisor Last Name
    Advisor Office
    Advisor Building
    Advisor Phone


    DEPARTMENT
    Department ID (PK)
    Department Code
    Department Name
    Department Phone


    *NOTE* You will have to decide on how to handle the Department and Advisor ID foreign keys in terms of a numbering system as well as appropriate field widths and types for the fields.
    The business rules which govern this database are: A student may have one advisor while an advisor may advise multiple students. A student belongs to only one department but each department can have many students.
    Populate the table with data from Table P6.4 (p. 217).

    There is an error in the book! Change Ortiz’s student number to be 200888. In addition, add these three students to your database.
    STU_NUM 123984 995133 367181
    STU_LNAME Freeman Wilder Green
    STU_MAJOR CIT CIT BIS
    DEPT_CODE CS CS IS
    DEPT_NAME Computer Science Computer Science Business Informatics
    DEPT_PHONE 5234 3951 3951
    COLLEGE_NAME Informatics Informatics Informatics
    ADVISOR_LNAME Strand Zhang Goh
    ADVISOR_BLDG Griffin Griffin Griffin
    ADVISOR_OFFICE 5132 3451 5612
    ADVISOR_PHONE 1603 3512 7922
    STU_GPA 2.5 3.9 2.3
    STU_HOURS 97 58 63
    STU_CLASS Senior Junior Junior


    Part 1
    Provide all DDL-related code. This includes table definition and creation, fk/pk creation, and populating the tables with data. Please include the output from Oracle that shows that everything was created correctly.


    Part 2
    Queries – For each query provide 1) What the output, specifically, should be based upon eyeballing the data 2) the SQL code used to generate the query, and 3) the output from Oracle. Please include any relevant fields you think the user of the query would need to interpret the output.
    1. Advisors need the capability to generate a query that returns *all* student information based upon a student number. This will help them in the advisement process. Create a query that returns all information for student Freeman from each table.
    2. Each year, college administrators need to know how many students are in each major. Create a query that counts the number of students in each major while displaying the major name.
    3. Kroger has approached NKU with an internship opportunity! The business department chair needs to generate a mailing list to inform great students about a job opportunity. Create a query that shows all the student numbers, last names, majors, department names, and advisor’s last name for students in Business Admin. Students should have a minimum GPA of 2.5 or greater to be on this mailing list. List in ascending order of GPA.
    4. To get an idea of the adequacy of admission standards, NKU needs to have an idea of the breakdown of students. By each college, show the number of students in each classification (freshmen, sophomore, junior, senior).
    5. Due to a fire, Griffin Hall has burnt down. Write a SQL statement that updates all faculty who had offices in Griffin Hall to now be housed in the University Center.
    6. New funding may be able to pay for an advising center. The dean would like to get an idea of how many students each faculty member currently advises. Create a query that shows the names and majors of students for each advisor along with the advisor’s name.
    7. (Extra credit) Create a query that counts the number of students who are eligible for the Dean’s list (GPA >= 3.5) in each department.

    Learn More
  5. DBM 405 Lab 7 Front End GUI

    DBM 405 Lab 7 Study Case Front-End GUI

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 7 Study Case front-end GUI

    Scenario/Summary
    The More Movies company has hired you to redesign a database system for them that can facilitate the process of renting out and returning movies.
    They already have an Oracle database that stores information about movies, members who rent the movies, and the rentals. This is the database that you already have become familiar with and the one which includes tables: MM_MOVIE, MM_MOVIE_TYPE, MM_MEMBER, MM_RENTAL, and MM_PAY_TYPE. The machine on which this database is running has both the server and client Oracle9i software installed on it. Every night, a clerk updates data to account for the day's activities, and periodically the reports are run to summarize business, show renting trends, etc. Access to the database is accomplished using a SQL*Plus environment that is very similar to the iSQL*Plus that you know from the previous database course. This business process worked okay for as long as More Movies stayed a very small business.
    However, the company has grown substantially, expanding its operations to more movie selection and more members, and consequently, it has moved to a larger location. It occupies a two-story shop now. It became very impractical to record rentals at the end of the day. They also do not want to rely on clerks knowing any SQL programming in order to record updates and run reports.
    In short, there is a need for a more convenient database system. The machine on which the database is currently running is powerful enough to host the database server. The database should be accessible from four checkout stations that process renting out and returning movies. This system should have an easy-to-use graphical user interface access.
    For the lab, you will be creating several documents to be submitted for the lab. Be sure that you save the documents with your last name and lab7 in the file name. Place all documents into a single ZIP file and submit for grading.

    LAB STEP
    Step 1:
    Describe what software you propose to use to develop the front-end GUI application for the new system. Be sure to justify your choice. Keep in mind portability, ease of use, scalability, and ability to update. What other options have you considered?

    Step 2:
    In setting up the servers and environment, do you propose to use middleware? If so, what kind, and where would you deploy it?

    Step 3:
    Provide a system diagram of the proposed system. Be sure to include such things as servers (application and database), user clients, and any other special pieces to the puzzle that you might think of.

    Step 4:
    Provide a detailed design of the GUI screen that facilitates renting out and returning movies. For every button, or other component that provides reaction to user's events, give detailed pseudocode. Also, clearly indicate where you would use any of the PL/SQL code that you developed for the labs in this course. If the application platform you have selected does not support PL/SQL then describe how you would take the processing developed in the procedures and functions and incorporate it into the system.

    This concludes the Lab for Week 7.

    Learn More
  6. DBM 405 Lab 2 Simple PL SQL Applications

    DBM 405 Lab 2 Simple PL/SQL Applications Advanced Database Oracle

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 2 Simple PL/SQL Applications Advanced Database Oracle

    Scenario/Summary
    The purpose of this week's lab is to work with basic PL/SQL syntax to create an anonymous block of code. In the lab, you will be using SQL*Plus to modify one of the tables in the MovieRental schema and then write a simple block of code to update the table with some new data and then execute the code in SQL*Plus. As an additional task in the lab, you will be asked to modify the existing PL/SQL block of code given to you to add exception handling and then execute it in SQL*Plus. Both of these concepts will help enforce the material covered in this second week.
    For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. To successfully test the code in Step 3, you will need to copy/paste your code into SQL*Plus for each movie ID as you change the value for the host variable. Spool your output to a file named with your last name plus lab 2 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab2.txt. Submit both the spooled output AND the script file for grading of the lab.

    LAB STEP
    Step 1:
    As business is becoming strong and the movie stock is growing for More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise, the value should be NULL. Add the new column to the MM_MOVIE table as a CHAR data type.
    Execute a DESC MM_MOVIE on the table both before you add the new column and after the column is added.
    Note: Since this is code will be in your script file, you will need to comment it out after the first time you have execute the ALTER TABLE statement successfully to avoid getting errors each additional time your script file is run.

    Step 2:
    Create an anonymous block of PL/SQL code that contains a CURSOR FOR loop to accomplish the task described above in Step 1. Your loop will need to interrogate the value (using an IF statement) found in the movie_qty field of the cursor loop variable to see if it is >= 75. If this is true then you will need to update the new column in the table with an '*' WHERE CURRENT OF the table. If the quantity is not >= 75 (the ELSE side of the IF statement) then update the new column with a NULL.
    Execute a SELECT * from MM_MOVIE both before and after you execute the new PL/SQL block of code to show that the process works.

    Step 3:
    Here is a block that retrieves the movie title and rental count based on a movie ID provided via a host variable.
    SET SERVEROUTPUT ON
    VARIABLE g_movie_id NUMBER
    BEGIN
    :g_movie_id := 4;
    END;
    /
    DECLARE
    v_count NUMBER;
    v_title mm_movie.movie_title%TYPE;
    BEGIN
    SELECT m.movie_title, COUNT(r.rental_id)
    INTO v_title, v_count
    FROM mm_movie m, mm_rental r
    WHERE m.movie_id = r.movie_id
    AND m.movie_id = :g_movie_id
    GROUP BY m.movie_title;
    DBMS_OUTPUT.PUT_LINE(v_title || ': ' || v_count);
    END;
    /
    Modify the block of code to add exception handlers for errors that you can and cannot anticipate. You will need to execute the entire code listing shown above each time you wish to test it by changing the value of :g_movie_id for each test.
    Once finished, test your exception handling by running the modified block for the following values of :g_movie_id. Be sure that you can capture the value in the :g_movie_id host variable.
    •    12 - normal output will display title and number of rentals
    •    13 - exception - there is no movie ID for 13
    •    1 - exception - Movie with ID 1 has never been rented

    This concludes the Lab for Week 2.

    Learn More
  7. DBM 405 Lab 1 SQL Review

    DBM 405 Lab 1 SQL Review Advanced Database Oracle

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 1 SQL Review Advanced Database Oracle

    Scenario/Summary
    The purpose of the first week's lab is to review the SQL language, and familiarize you with the database example that we will use in labs through this course. If you have not done it yet, please download the MovieRentals.SQL file to your computer from Doc Sharing. This script file will need to be run once logged into your Oracle user account to create the tables and data that will be used for this lab and subsequent labs in the course.
    Setting up your environment:
    Before attempting Lab 1, you need to be sure you have read through the SQL*Plus tutorial which can be found in Doc Sharing as well as under the SQL*Plus Tutorial tab in Week 1. This tutorial describes the functionality of the editor and will step you through the process of setting up and using the SQL*Plus in the iLab environment so that it will best serve your needs for the remainder of the labs required for this course. Once you have logged into Oracle through iLab then execute the MovieRental.SQL script and create the tables and data for the lab.
    For the lab, you will need to create a script file containing the eight queries that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the query and result set. Spool your output to a file named with your last name plus lab 1 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab1.txt. Submit both the spooled output AND the script file for grading of the lab.

    LAB STEP
    Step 1:
    Within SQL*Plus, list names of the tables that you have created whose name starts with MM (Hint: use data dictionary view USER_TABLES).

    Step 2:
    Use DESCRIBE (in short: DESC) command in SQL*Plus for each of these tables to show columns and their datatypes.

    Step 3:
    Use SELECT * command to display all data from each of the tables in the MoreMovies schema. Make sure that the LINESIZE and PAGESIZE have large enough values, and that you format columns so that the report looks good. You should end up with five queries and result sets.

    Step 4:
    Using the mm_movie and mm_movie_type tables, write a query that will list all movie categories together with the count of movies in each category. Give the column with the count in it a meaningful name such as IN STOCK.

    Step 5:
    Using the mm_movie and mm_rental tables, write a query that will list titles and checkout dates for all movies that were signed out by Wild Coyote (MEMBER_ID=13).

    Step 6:
    Using the same two tables used in Step 5, write an SQL sub-query that will list all movies (movie ids and titles) of all movies that have never been rented.

    Step 7:
    Using the mm_member and mm_rental tables, write a query that will list all members (member ID, first name, and last name) and the number of movies they have rented, for all members who have rented at least one movie. Order the result set so that it shows the largest number of movies rented as the first row.

    Step 8:
    Write a query that will display the largest number of movies rented by one member and that member's name. Give the output column a meaningful name such as MAXIMUM NUMBER.

    Step 9:
    Using the mm_member and mm_rental tables, write a query that will display member ID, last name, first name, and the number of movies rented for each member. Give the column with the number of movies rented a meaningful name such as NUMBER RENTED.

    Step 10:
    Using the mm_member, mm_movie and mm_rental tables, write the query that will prepare a report that shows who rented which movie. Use member names (first and last) and movie title rather than the corresponding IDs. Order the report by member names, and for a single member by the movie titles.

    This concludes the Lab for Week 1.

    Learn More
  8. DBM 405 Lab 6 Step 3 to Step 8 MOVIE_RENT_SP Procedure

    DBM 405 Lab 6 Reading and Writing to External Files

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 6 Reading and Writing to External Files

    Scenario/Summary
    This week, we are going to make a change to the processing in our schema by altering one of the procedures contained in the package previously created. At this point, we have dealt with procedures that required us to pass parameter values each time the procedure was executed. This type of processing greatly limits the ability to process large amounts of data efficiently. The lab this week will introduce you to a couple of the procedures contained in the UTL_FILE package that is part of Oracle's group of built-in packages. By using the GET_LINE and PUT_LINE procedures contained in UTL_FILE, you will be able to process multiple records from a file and create a results report on the events performed by your movie rental procedure with one call to execute the procedure.
    For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. You will only need to recompile both the package specifications and the package body since you will be making a change to the movie_rental_sp procedure in the specifications. You will be running tests to verify that the changes to your procedure are working as they should. Spool your output and name your files with your last name plus lab 6 and give the file a text (txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab6.txt. Submit both the spooled output files AND the script file for grading of the lab.
    IMPORTANT: Before beginning the lab, you will want to refresh the tables in your schema by running the movierental.sql script.

    LAB STEP
    Step 1: Setting Up Your Environment
    Before you can effectively use the procedures in UTL_FILE to work with external files for this lab, you will need to set up your environment. The first step of this process will involve creating a directory folder under the R: drive which is the mapped drive for DBM405. The second part of the process will involve creating the Directory Object in Oracle to point to this folder. To accomplish this task, please follow the steps listed here.
    1. Create the directory folder (You can refer to the SQL*Plus Tutorial in Week 1 for visuals of this process).
    •    First, log into the iLab and open up your Oracle folder. Next, open Windows Explorer and expand the R: drive tree.
    •    Under the R: drive, create your own folder by selecting File/New/Folder from the main menu bar. Name your folder using a unique name such as DBM405_lastname_lab6 or some other unique name. Make sure that there are no other folders under the R: drive with the same name as yours. You are the only person who has access to your folder, but the name must be unique.
    2. Create the Directory Object.
    •    In Windows Explorer (the one in the Oracle folder in the iLab), you want to take note of the path name for the R: drive. It will be DBM405\ followed by the session you are in. For example, if you are in Spring A, the path would be DBM405\SPRINGA, or if Spring B, it would be DBM405\SPRINGB. This is important because this will be used to define the path for the Directory Object. Remember that the R: drive is really mapped to the Oracle server F: drive.
    •    Start up a session in SQL*Plus and log into your user schema.
    •    Use the following SQL command to create the Directory Object that you will use for this lab.
    Create or Replace Directory DBM405_YOURNAME_DIR As 'F:\DBM405\session\YourFolderName';
    IMPORTANT: Replace the word YOURNAME with your actual last name, the word session with the session name found in the path for the R: drive (see first bullet above), and the word YourFolderName with the name of the folder you created in Part 1 above. For example, if your last name were SMITH and you were taking this course is Spring session A and the folder you create was named DBM405_SMITH_lab6, the statement would be:
    Create or Replace Directory DBM405_SMITH_DIR As 'F:\DBM405\SPRINGA\DBM405_SMITH_LAB6';
    3. Set up the data file.
    •    Download the file "movierentaldata.txt" from Doc Sharing and place this file in the directory folder you created above in Part 1.
    You have now set up Oracle to work with the procedures in UTL_FILE that we will use for this lab.

    Step 2: Changing the Package Specifications
    We are now ready to start updating the package that we modified in Lab 5 so that it will allow us to read a file and make changes to the database. To do this, we will need to make changes to the parameter list of the MOVIE_RENT_SP procedure to reflect a new process. Since we will be changing the parameter list, we will need to make changes to the Package Specifications as well as to the Package Body.
    For the change to the Package Specifications, you will need to define a parameter for the Directory Object, one for the input file, and one for an output file that will be used for a verifications report. Each of these parameters can be defined as VARCHAR2 data types.

    Step 3: The DECLARE (IS) Section of the Procedure
    Now, we can move on to the Package Body and the MOVIE_RENT_SP procedure itself. The first thing you want to do is make sure that the parameter list in the procedure matches the parameter list that you just changed in the Specifications in Step 2. The two parameter lists must match exactly or you will get errors when trying to compile the Package Body.
    When you read in the file, you will need several variables to handle both the record and the various pieces of data in the record. When the record is read in, you will need to substring each piece of data from the record into a variable which then can be used like you were using the parameters before. To keep from having to make numerous changes in the code, you might want to consider using the actual parameter names from your parameter list in the code from Lab 5. For example, if the parameter name for the movie ID was P_MOVIE_ID then you would create a variable named P_MOVIE_ID. Each of the variables for movie ID, member ID, and payment method can be cast to the data type in the MM_RENTAL table. For example:
    p_movie_id mm_rental.movie_id%TYPE;
    You will also need a variable to hold the record data that will be read in and a variable to hold record data that will be written out. Check the length of the data in the movierentaldata.txt file to determine the length for the input data variable. For the output data variable, you can use your own judgment, but using VARCHAR2(80) would probably be more than adequate.
    The last thing you will need to add is a variable to act as the handle for the input record and one for the output record. Remember that the name for these is not important, but they must be based on the FILE_TYPE data type.

    Step 4: Opening the Files
    Now, we move to the body of the code and the BEGIN section. The first thing that needs to happen is to open both the files. Remember that we are going to set up our processing just like we would any IPO logic, i.e., we need to open the files, read a record, process the data, read the next record, and repeat this process until there is no more data.
    To accomplish this first step of opening the files, you will need to initialize the two variables you declare to handle the files. For the input file, you will need to use the FOPEN program. Remember that you have to pass this program three variables: the name of the parameter for the directory object, the name of the parameter for the input file, and the mode the file is to be in. For the input file, the mode needs to be "r", and for the output file, the mode needs to be "w".
    An example of what your code might look like can be found in the lecture material for this week.

    Step 5: Setting Up the LOOP
    Since our code in its current format is designed for only one set of input values (remember that in previous labs we were using an EXECUTE command and then passing data to the procedure) and we now want to process multiple records of data, we need to make a change to the overall structure of the code. We need to put all of the processing in the body of the procedure after the files are opened into a basic LOOP using the LOOP/END LOOP commands. Also, we will need a new BEGIN section inside this LOOP that all of the existing code will go into. The basic structure of body of your code should look like the following when you complete this step.
    BEGIN
    open files
    LOOP
    BEGIN
    file processing
    BEGIN
    existing process to increment rental_id
    EXCEPTION
    END
    EXCEPTION
    END
    END LOOP
    END

    Step 6: Setting Up the File Read and Process
    Now, we need to get our data record so that we can process the data brought into the program. To do this, we need to read a record using the GET_LINE program in UTL_FILE. This involves using the UTL_FILE.GET_LINE process while passing the file handle variable and input record variable to the GET_LINE program.
    Once the record is in the input variable, we then need to parse out the individual pieces of data (movie ID, customer ID, and payment method) from the record using the SUBSTR function. Remember that the parameters for the SUBSTR function are the record variable name, the value for the first byte of data we want to pull, and the length of the data. For example, to get the movie ID from our record, the code might look like the following.
    p_movie_id := SUBSTR(v_rental_record, 1, 2);
    This would pull two bytes of data from the data record and put them in the variable p_movie_id. Now we can use the variable in our code that follows to see if that movie ID exists in the mm_rental table. You will need to repeat the process above for the member ID and the payment method. Do all three together between the line of code that reads the file and the first SELECT statement.

    Step 7: Setting Up the Output Process
    If you have used the same names for your three data variables that you used for the original parameter names in the previous lab then you should not have to make any changes at all to any of the SELECT statements or the INSERT statement in the main body of code. Now, we need to set up the processing that will create the output data that will go into our output validation record. To do this, you will be using the PUT_LINE program within UTL_FILE.
    There are going to be six different places you will want to write out a record. The first will be after the insert statement when a new rental record is added to the mm_rental table. To create this output line you can concatenate variables and character strings together to create the data record. For example, to create an output line that would read "Rental record 13 for member 10 has been added" after the new record has been inserted, you would use code similar to the following (keep in mind that your variable names might be different).
    v_report_record := 'Rental record '||v_rental_id||' for member '||p_member_id||
    ' has been added';
    UTL_FILE.PUT_LINE(v_report_filehandle, v_report_record);
    This same type of process and format will need to be repeated for each exception handler that in the previous lab used the DBMS_OUTPUT.PUT_LINE package and procedure. In these cases, your output line is already formatted and set, you just need to replace the DBMS_OUTPUT.PUT_LINE with the initialization of your output record and then add the UTL_FILE.PUT_LINE code.

    Step 8: Getting Out of the LOOP and Closing the Output File
    There is one final step that has to be taken care of before you can start testing your code. Since we are reading a record file within a LOOP we need to be able to EXIT out of the loop after the last record has been processed. To do this you will need to add a NO_DATA_FOUND exception to your main exception handling section. This exception handle must be added before the WHEN OTHERS exception handle (remember, that on has to be last). This exception handle will have three pieces to process.
    The first will be some form of message stating that all the records have been processed and this is the end of the report, i.e., ALL RECORDS PROCESSED - END OF REPORT. The second piece will be a line that will close the output file using UTL_FILE.FCLOSE. The only parameter you will need to pass is the name of the output file variable. This must be done or your output file will not have any data in it. The third thing will be the word EXIT which will tell the program to exit out of the loop.
    You are now ready to compile your package specifications (must be done first) and then the package body. Debug any errors you might have and then run your test.

    Step 9: Testing Your New Procedure
    For this lab, you only need to test the MOVIE_RENT_SP procedure of the package. To do this, enter an EXECUTE command for the package.procedure and pass the name of your Directory Object, input file name, and output file name to the procedure. IMPORTANT: The Directory Object name MUST BE IN UPPERCASE! This is the way it is stored in the data dictionary in Oracle and if it is not in uppercase, an error will be generated. Your call to the new procedure should look similar to the following.
    execute MM_RENTALS_PKG.movie_rent_sp('DBM405_SMITH_DIR','movierentaldata.txt','rentalreport.txt');
    The output in your output file should look similar to the following (the wording may be different but the processes recorded should be the same).
    There is no movie with id 13. Cannot proceed with rental.
    There is no member with id 20. Cannot proceed with rental.
    There is no payment method with id 7. Cannot proceed with rental.
    Rental record 13 for member 10 has been added.
    Movie id 5 is not available at this time. Cannot proceed with rental.

    This concludes the Lab for Week 6.

    Learn More
  9. DBM 405 Lab 5 Step 1 RENTING_MOVIE

    DBM 405 Lab 5 User Defined Database Triggers

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 5 User Defined Database Triggers

    Scenario/Summary
    This week, we are going to continue to expand the functionality of our database schema by adding a couple of triggers to that will help us automate some of the processing we already have in place. Triggers can be used to automate repetitive tasks within the database, such as adjusting inventory levels based on other actions taken in the database. Once you have created and tested your triggers, you will need to make some adjustments to the code in the package that was created in Lab 4.
    For the lab, you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. You will be running tests to verify that your triggers are working once your package has been updated. Spool your output and name your files with your last name plus lab 5 and give the file a text (.txt) extension. For example, if your last name was Johnson then the file would be named johnson_lab5.txt. Submit both the spooled output files AND the script file for grading of the lab.

    LAB STEP
    Step 1: Creating the First Trigger
    The first trigger you are going to create is to be named RENTING_MOVIE and is going to take care of the process of updating the mm_movie table to reflect a change downward in the quantity column for a movie when it is rented. Keep the following in mind:
    1. The trigger needs to be an AFTER INSERT trigger on the mm_rental table. We want it to be an AFTER trigger so that, in case there are any exceptions raised, the trigger will not fire.
    2. The trigger needs to be able to fire for each row that is inserted into the table.
    3. The trigger process will only involve the update statement to lessen the quantity amount in the mm_movie table by one for the referenced movie ID.
    Test your code by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to Step 2.

    Step 2: Creating the Second Trigger
    The second trigger you are going to create is to be named RETURNING_MOVIE and is going to take care of the process of updating the mm_movie table to reflect a change upward in the quantity column for a movie when it is returned. Keep the following in mind:
    1. The trigger needs to be an AFTER UPDATE trigger on the mm_rental table based on the updating of the check in date in the mm_rental table.
    2. The trigger needs to be able to fire for each row that is updated.
    3. The trigger process will only involve the update statement to increase the quantity amount in the mm_movie table by one for referenced movie ID.
    Test your code by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to step 2.

    Step 3: Modifying the Package Code
    Now, we have two triggers that will handle changes in our movie rental stock each time a movie is rented or returned. Since that same process currently exists in the procedures in our package then we need to make some changes.
    To keep from having repetitive processes, and thus have a scenario for generating invalid inventory data, we need to take the processes out of the procedures in the package body by:
    1. removing the update statement in the MOVIE_RENT_SP that decreases the quantity by one in the mm_movie table; and
    2. removing the update statement in the MOVIE_RETURN_SP that increases the quantity by one in the mm_movie table.

    Recompile the package body (you do not have to recompile the package specifications). If you have any errors, debug them and once you have a clean compile, move on to Step 4.

    Step 4: Testing
    To test your changes, you will only need to test a valid movie rental and a valid movie return. The following steps will help you with the process.
    1. Query the mm_movie table to see all data for movie ID 1.
    2. Execute the movie_rent_sp procedure in the package and use 1, 13, and 2 for the parameters.
    3. Query the mm_movie table to verify the change in quantity for movie ID 1.
    4. Query the mm_rental table to get the current rental ID for movie ID 1.
    5. Execute the movie_return_sp procedure in the package using the rental ID from Step 4.
    6. Query the mm_movie table to verify the change in quantity for movie ID 1.

    This concludes the Lab for Week 5.

    Learn More
  10. DBM 405 Lab 4 PL SQL Packages Body Step 2

    DBM 405 Lab 4 PL/SQL Packages Advanced Database Oracle

    Regular Price: $20.00

    Special Price: $15.00

    DBM 405 Lab 4 PL/SQL Packages Advanced Database Oracle

    Step 1: Creating the Package Specifications
    Before you begin, there are several things you will want to do to get ready for the lab.
    1.  Refresh your database tables by running the movierental.sql script. This will restore all of the data back to where it was in the beginning for the course.
    2.  Drop the two procedures and the function created in Lab 3. Be sure that you have your script file from Lab 3 so you can copy the procedure and function code from it.

    Now you are ready to create your package specification. Your package name should be MM_RENTALS_PKG and it will contain the two procedures and the one function that were created in Lab 3.
    Remember that for the specification, you only need to list the procedure and function header data (CREATE statement with parameters).

    Test your package specification by running the script in SQL*Plus. If you have any errors, debug them and once you have a clean compile, move on to Step 2.

    Step 2: Creating the Package Body
    Creating the package body should be simple since you already have the code for the two procedures and the function, and you know it works.
    Remember that the name for the package body must match the name of the specifications, and that the procedure and function header in the body must match that of the specification exactly.
    Once you have created the body then run the script in your SQL*Plus session. Once you have a clean compile then move on to Step 3 to do your testing.

    Step 3: Testing the Package
    To test your package, you will need to run the same exact tests you did in Lab 3. The following outlines what you will test for:
    Testing the first procedure -
    1.  No movie for the ID supplied (use 13, 10, and 2 for the parameters).
    2.  No member for the ID supplied (use 10, 20, and 2 for the parameters).
    3.  No payment method for the ID supplied (use 10, 10, and 7 for the parameters).
    4.  A successful rental (use 5, 10, and 2 for the parameters).
    5.  No movie available for the ID supplied (use 5, 11, and 2 for the parameters). Since there is only one movie available for ID 5, you will get this exception.
    Testing the second procedure -
    1.  No rental for the ID supplied (use 20 for the parameter).
    2.  A successful rental return (use 1 for the parameter).
    3.  Try to return the same rental in Step 2.
    Testing the function -
    1.  Test for a movie in stock using movie ID 11.
    2.  Test for a movie not in stock using movie ID 5 (from your tests of the second procedure above, the quantity should be 0).
    3.  Test for an invalid movie ID using movie ID 20.
    IMPORTANT: Remember that all of your testing needs to be saved in a spool session so that it can be submitted to the Dropbox for grading.

    Step 4: Determining Dependencies
    Having created a package that contains program units to support the movie rental process is a major step in customizing the new database.
    As application modifications are made in the future, however, we need to be able to identify all object dependencies to test changes.
    For this step in the lab, you are to use either data dictionary views or the dependency tree utility found in Doc Sharing (utldtree.sql file) to compile a list of dependencies for all the More Movies database objects.
    Remember that an object is anything that was created using the CREATE statement.
    Present your finding in a separate Word document in a tabular format as in the following sample.
    Each dependency type should be listed as either direct or indirect.

    Learn More

Items 31 to 40 of 63 total

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

Grid  List 

Set Descending Direction