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.

9 Item(s)

per page

Grid  List 

Set Descending Direction
  1. CMIS420 PROJECT 2 Mail-Order Database DML and DDL statements

    CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database

    Regular Price: $25.00

    Special Price: $20.00

    CMIS420 Advanced Relational Database PROJECT 2 Mail-Order Database


    Overview:
    Use SQL, PL/SQL, and Triggers to design and create a Mail-Order Database System. Please create your own data for testing purpose. Use the attached file "Project 2 Tables" as a guide to creating the tables. You should pre-populate the PARTS, CUSTOMERS, EMPLOYEE and ZIPCODES tables.


    Due Date:
    Check the due date in Syllabus for the exact date for this assignment. No project will be accepted after the due date.


    Deliverables:
    Turn in all SQL scripts in the form of a SQL script files. The script files should include,


    1. A script file containing all the DML and DDL statements. That is, the SQL used to create the tables and sequence and the SQL to pre-populate or insert records in the tables. Name this file XXX_PROJ2.sql, where XXX are you intials.
    2. A file containing the PL/SQL package (Specification and Body) that provides the functionality listed in the requirements below. Name this file XXX_PROJ2.pkg, where XXX are you initials.
    3. A file containing the database triggers. Name this file XXX_PROJ2.trg, where XXX are you initials.
    4. Finally, provide a test SQL*PLUS routine (PL/SQL anonymous block) that will test the PL/SQL functionality developed. Name this file XXX_PROJ2_tst.sql, where XXX are your initials.


    You should submit your assignment through WebTycho as you did for previous assignments.
    Use winzip or any zip software to package the four (4) files into one file called XXX_project2.zip, where XXX are your initials.


    Requirements:
    The Mail-Order Database consists of the following tables and attributes. Please ensure that all constraints are created when creating the tables. All constraints other than NOT NULL constraints must be named.
    1. EMPLOYEE(ENO, ENAME, ZIP, HDATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    2. PARTS(PNO, PNAME, QOH, PRICE, REORDER_LEVEL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    3. CUSTOMERS(CNO, CNAME, STREET, ZIP, PHONE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    4. ORDERS(ONO, CNO, ENO, RECEIVED, SHIPPED, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    5. ODETAILS(ONO, PNO, QTY, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    6. ZIPCODES(ZIP, CITY)
    7. ORDERS_ERRORS(TRANSACTION_DATE, ONO, MESSAGE)
    8. ODETAILS_ERRORS(TRANSACTION_DATE, ONO, PNO, MESSAGE)
    9. RESTOCK(TRANSACTION_DATE, PNO)


    • The EMPLOYEE table contains information about the employees of the company. The ENO (Employee Number) attribute is the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
    • The PARTS table keeps a record of the inventory of the company. The record for each part includes its number (PNO) and name (PNAME) as well as the quantity on hand (QOH), the unit price (PRICE) and the reorder level (REORDER_LEVEL). PNO is the primary key for this table.
    • The CUSTOMERS table contains information about the customers of the mail-order company. Each customer is assigned a customer number (CNO), which serves as the primary key. The ZIP attribute is a foreign key referring to the ZIPCODES table.
    • The ORDERS table contains information about the orders placed by customers, the employee who took the orders, and the dates the orders were received and shipped. Order number (ONO) is the primary key. The Customer number (CNO) attribute is a foreign key referring to the CUSTOMERS table, and the ENO attribute is a foreign key referring to the EMPLOYEES table.
    • The ODETAILS table contains information about the various parts order by the customer within a particular order. The combination of ONO and PNO attributes forms the primary key. The ONO attribute is a foreign key referring to the ORDERS table, and the PNO attribute is a foreign key referring to the PARTS relation.
    • The ZIPCODES table maintains information about the zip codes for various cities. ZIP is the primary key.
    • The ORDERS_ERRORS table contains information about any errors that occurred when an order is processed. Transaction date is the date of the transaction.
    • The ODETAILS_ERRORS table contains information about all errors that occur when processing an order detail. Transaction date is the date of the transaction.
    • The RESTOCK table contains information about all parts (PNO) that are below the reorder level. Transaction date is the date of the transaction.


    1. Write a package called Process_Orders to process customer orders. This package should contain four procedures and a function, namely;
    Add_order. This procedure takes as input customer number, employee number, and received date and tries to insert a new row in the Orders table. If the received date is null, the current date is used. The shipped date is left as null. If any errors occur, an entry is made in the Orders_errors table. A sequence called Order_number_seq should be used to populate the order number (ONO) column.
    Add_order_details. This procedure receives as input an order number, part number, and quantity and attempts to add a row to the Odetails table. If the quantity on hand for the part is less than what is ordered, an error message is sent to the Odetails_errors table. Otherwise, the part is sold by subtracting the quantity ordered from the quantity on hand for this part. A check is also made for the reorder level. If the updated quantity for the part is below the reorder level, an entry is made to the Restock table.
    Ship_order. This procedure takes as input an order number and a shipped date and tries to update the shipped value for the order. If the shipped date is null, the current date is used. If any errors occur, an entry is made in the Orders_errors table.
    Delete_order. This procedure takes as input an order number and tries to delete records from both the Orders and Odetails tables that match this order number. If any errors occur or there is no record that matches this order number, an entry is made in the Orders_errors table.
    Total_emp_sales. This function takes as input an employee number. It computes and returns the total sales for that employee.


    2. Create triggers on the PARTS, ORDERS, and ODETAILS tables to populate the CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY columns when an insert or update is made. Use SYSDATE and the pseudo column USER to populate these columns.


    3. Write a trigger that fires when a row in the Orders table is updated or deleted. The trigger should record the dropped order records in another table called deleted_orders. The deleted_orders table should also contain a date attribute that keeps track of the date and time the action (update or delete) was performed. This date is quite different from the CREATED_DATE and UPDATED_DATE from the Order table. Do not copy these dates to the deleted_order table. Please include the table creation script for the deleted_orders table in the script file.


    4. Create a sequence called order_number_seq that will be used to populate the order number (ONO) column.


    5. Write a PL/SQL anonymous block to test the above.

    Learn More
  2. MSCD610 Week 7 Logical ERD

    MSCD610 Week 7 Course Project Details Hotel Database

    Regular Price: $30.00

    Special Price: $25.00

    MSCD610 Week 7 Course Project Details Hotel Database


    The course project is to develop a Data Model and Database Design for a set of business case requirements. Students should informally review their ER model with the facilitator in week 4 or 5.


    This project should follow a top-down database design process and produce the following outputs:
    1. A set of business information requirements in the form of a business case abstract.


    2. A complete ERD that models those requirements.
    This model should include the following:
    a. A definition of each entity
    b. The unique identifier for each entity
    c. The attributes associated with each entity
    d. The relationships between the entities including their cardinality, optionally, and names


    3. A physical implementation of a database from the logical design (ERD).
    The tables should be normalized to 3NF. For this design include:
    a. The name for each table
    b. The primary key for the table, and any secondary keys
    c. Any foreign keys
    d. A data dictionary
    e. Any sample data available


    4. The DDL scripts for implementing the physical database design for this database. Include the referential integrity constraints for these tables. Also include the DDL for any needed indexes and/or views. If necessary, indicate any database tuning which is anticipated on this database.


    5. Create the designed Oracle database, and load it with sample data. Then print the definition of the tables, indexes, and any views. Print sample report(s) showing the query definitions in the business requirements have been met.

    Learn More
  3. Assignment 9-8 Maintaining an Audit Trail of Product Table Changes

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-5 to 9-8

    Regular Price: $30.00

    Special Price: $25.00

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-5 to 9-8

    Assignment 9-5: Processing Discount
    Brewbean's is offering a new discount for return shoppers. Every fifth completed order gets a 10% discount. The count of orders for a shopper is placed in a packaged variable named pv_disc_num during the ordering process. The count needs to be tested at checkout to determine whether a discount should be applied. Create a trigger named BB_DISCOUNT_TRG so that when an order is confirmed (the ORDERPLACED value is changed from 0 to 1), the pv_disc_num packaged variable is checked. If it's equal to 5, set a second variable named pv_disc_txt to Y. This variable is used in calculating the order summary so that a discount is applied, if necessary.
    create a package specification named DISC_PKG containing the necessary packaged variables. Use an anonymous block to initialize the packaged variables to use for testing the trigger. Test the trigger with the following UPDATE statement:
    UPDATE bb_basket
    SET orderplaced = 1
    WHERE idBasket = 13;
    If you need to test the trigger multiple times, simply reset the ORDERPLACED column to 0 for basket 13 and then run the UPDATE again. Also, disable this trigger when you're finished so that it doesn't affect other assignments.


    Assignment 9-6: Use Triggers to Maintain Referential Integrity
    At times, Brewbean's has changed the ID numbers for existing products. In the past, developers had to add a new product row with the new id to the BB_PRODUCT table, modify all the corresponding BB_BASKETITEM and BB_PRODUCTOPTION table rows, and then delete the original product row. Can a trigger be developed to avoid all these steps and handle the update of the BB_BASKETITEM and BB_PRODUCTOPTION table rows automatically for a given change in product ID? If so, create the trigger and test by issuing an update statement, which changes the IDPRODUCT of 7 to 22. Do a rollback to return the data back to its original state. Also, disable the new trigger after you have completed the assignment.


    Assignment 9-7: Updating Summary Data Tables
    The Brewbean's owner uses several summary sales data tables every day to monitor business activity. The BB_SALES_SUM table holds the product ID, total sales in dollars, and total quantity sold for each product. A trigger is needed so that every time an order is confirmed or the ORDERPLACED column is updated to 1, the BB_SALES_SUM table is updated accordingly. Create a trigger named BB_SALESUM_TRG that perform this task. Before testing, reset the ORDERPLACED column to 0 for basket 3, as shown in the following code, and use this basket to test the trigger.
    UPDATE bb_basket
    SET orderplaced = 0
    WHERE idBasket = 3;
    Notice that the BB_SALES_SUM table already contains some data. Test the trigger with the following UPDATE statement, and confirm that the trigger is working correctly:
    UPDATE bb_basket
    SET orderplaced = 1
    WHERE idBasket = 3;
    Do a rollback and disable the trigger when you're finished so it doesn't affect the other assignments.


    Assignment 9-8: Maintaining an Audit Trail of Product Table Changes
    The accuracy of product table data is critical and the Brewbean's. owner wants to have an audit file that containing information on all DML activity on the BB_PRODUCT table. This information should indicate the ID of the user performing a DML action, the date, the original values of the changed row, and the new values. This audit table needs to track specific columns of concern, including PRODUCTNAME, PRICE, SALESTART, SALEEND, and SALEPRICE. Create a table named BB_PRODCHG_AUDIT that can hold the relevant data. Then create a trigger named BB_AUDIT_TRG that fires an update to this table whenever one of the specified columns in the BB_PRODUCT table is changed.
    Be sure to issue the following command. If you created the SALES_DATE_TRG trigger in the chapter, it conflicts with this assignment.
    ALTER TRIGGER SALES_DATE_TRG DISABLE;
    Use the following update statement to test your trigger:
    UPDATE bb_product
    SET salestart = '05-MAY-03', saleend = '12-MAY-03', saleprice = 9
    WHERE idproduct = 10;
    When you have finished, do a rollback and disable the trigger so that it does not affect other assignments.

    Learn More
  4. Assignment 9-4 Updating Stock Levels When an Order ls Cancelled

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

    Regular Price: $25.00

    Special Price: $20.00

    Oracle 11g PL/SQL Joan Casteel Chapter 9 Hands-On Assignments Part 9-1 to 9-4

    Assignment 9-1: creating a Tigger to Handle Product Restocking
    Brewbean's has a couple of columns in the Product table to assist in inventory tracking. The REORDER column contains the stock level at which the product should be reordered. If the stock fall to this level. Brewbean's wants the application to insert a row in the BB_PRODUCT_REQUEST table automatically to alert the ordering clerk that additional inventory is needed. Brewbean's currently uses the reorder level amount as the quantity that should be ordered. This task can be handled by using a trigger.
    1. Take out some scrap paper and a pencil. Think about the tasks the triggers needs to perform. Including checking. whether the new stock level falls below the reorder point. If so, check whether the product is already on order by viewing the product request table; if not, enter a new product request. Try to write the trigger code on paper. Even though you learn a lot by reviewing code, you improve your skills faster when you create the code on your own.
    2. Open the c9reorder.txt file in the Chapter09 folder. Review this trigger code, and determine how it compares with your code.
    3. In SQL Developer, create the trigger with the provided code.
    4. Test the trigger with product ID 4. First, run the query shown in Figure 9-36 to verify the current stock data for this product. Notice that a sale of one more item should initiate a reorder.
    5. Run the UPDATE statement shown in Figure 9-37. It should cause the trigger to fire. Notice the query to check whether the trigger fired and weather a product stock request was inserted in the BB_PRODUCT_REQUEST table.
    6. Issue a ROLLBACK statement to undo these DML actions to restore data to its original state for use in later assignments.
    7. Run the following statement to disable this trigger so that it doesn't affect other projects:
    ALTER TRIGGER bb_reorder_trg DISABLE;


    Assignment 9-2: Updating Stock Information When a Product Is Filled
    Brewbean's has a BB_PRODUCT_REQUEST table where requests to refill stock level was inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table's DTRECD and COST columns. they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline:
    1. In SOL Developer, run the following INSERT statement to create a product request you can use in this assignment:
    INSERT INTO bb_product_requect (idRequest, idProduct, dtRequest, qty)
    VALUES (3, 5, SYSDATE, 45);
    COMMIT;
    2. Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory.
    3. Now test the trigger. First, query the stock and reorder data for product 5. as shown in Figure 9-38.
    4. Now update the product request to record it as fulfilled by using UPDATE statement shown in figure 9-39.
    5. Issue queries to verify that the trigger fired and the stock level of product 5 has been modified correctly. Then issue the ROLLBACK statement to undo modifications.
    6. If you aren't doing assignment 9-3, disable the trigger so that it doesn't affect other assignments.


    Assignment 9-3: Updating the Stock Level If a Product Fulfillment is Cancelled
    The Brewbean's developers have made progress on the inventory-handling processes; however, they hit a snag when a store clerk incorrectly recorded a product request as fulfilled. When the product request was updated to record a DTRECD value, the product stock level was updated automatically via an existing trigger, BB_REQFILL_TRG. If the clerk empties the DTRECD column to indicate that the product request has been filled, the product stock level need to be corrected or reduced, too. Modify the BB_REQFILL_TRG to solve this problem.
    1. Modify the trigger code from Assignment 9-2 as needed. Add code to check whether the DTRECD column already has a data in it and is now being set to NULL.
    2. Issue the following DML actions to create or update rows that you can use to test the trigger:
    INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty, dtRecd, cost)
    VALUES (4, 5, SYSDATE, 45, '15-JUN-2012', 225);
    UPDATE bb_product
    SET stock = 86
    WHERE idProduct = 5;
    COMMIT;
    3. Run the following UPDATE statement to test the trigger, and issue queries to verify that the data has been modified correctly.
    UPDATE bb_product_request
    SET dtRecd = NULL
    WHERE idRequest = 4;
    4. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
    ALTER TRIGGER bb_reqfill_trg DISABLE;


    Assignment 9-4: Updating Stock Levels When an Order ls Cancelled
    At times, customers make mistakes in submitting orders and call to cancel an order. Brewbean's wants to create a trigger that automatically updates the stock level of all products associated with a cancelled order and updates the ORDERPLACED column of the BB_BASKET table to zero, reflecting that the order wasn't completed. Create a trigger named BB_ORDCANCEL_TRG to perform this task, taking into account the following points:
    The trigger need to fire when a new status record is added to the BB_BASKETSTATUS table and when the IDSTAGE column is set to 4, which indicates an order has been cancelled.
    Each basket can contain multiple items in the BB_BASKETITEM table, so a CURSOR FOR loop might be a suitable mechanism for updating each item's stock level
    Keep in mind that coffee can be ordered in half or whole pounds.
    Use basket 6, which contains two items, for testing.
    1.Run this INSERT statement to test the trigger:
    INSERT INTO bb_basketstatus (idStatus, idBasket, idStage, dtStage)
    VALUES (bb_status_seq.NEXTVAL, 6, 4, SYSDATE);
    2. Issue the queries to confirm that the trigger has modified the basket's order status and product stock level correctly.
    3. Be sure to run the following statement to disable this trigger so that it doesn't affect other assignments:
    ALTER TRIGGER BB_ORDCANCEL_TRG DISABLE;

    Learn More
  5. 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
  6. IM300 Week 5 Hotel Database Part 4 Cover Sheet

    IM300 Week 5 Optimizing the Database Hotel Database Part 4

    Regular Price: $25.00

    Special Price: $20.00

    IM300 Week 5 Optimizing the Database Hotel Database Part 4


    Resources: SR-bi-003 – Hotel Database, the results from SR-bi-003 – Hotel Database Part 3, and Oracle® Database Express® Virtual Desktop


    Complete the service request by implementing referential integrity and constraints in your database:
    Create an AFTER trigger on the Hotel table that deletes any records from the Rooms table that match (:old.HOTEL_ID).
    Apply at least six constraints within the database.
    Create a newly generated DDL script.


    Create a fax cover sheet addressed to the project manager for the referential integrity and constraints work you have completed and include a newly generated DDL script to show your progress on the project.

    Learn More
  7. ITSE 2309 Oracle Olympics Database Queries

    ITSE 2309 Project Database Programming Oracle Olympics Database

    Regular Price: $30.00

    Special Price: $25.00

    ITSE 2309 Project Database Programming Oracle Olympics Database


    Project Objective: Design and implement a database from requirements and execute queries using SQL.
    1. Design a database for tracking Olympic events and results.
    2. Write scripts to create the database and populate the tables.
    3. Write SQL queries to produce reports.


    Deliverables
    1. Create an ER Diagram for your Olympics database.
    2. Write a short design document (1 or 2 pages) describing your choices and reasons for designing your database as you did.
    3. Provide scripts to create, populate, and teardown the Olympics database.
    A. makeOlympicsDb.sql – Script to create tables, constraints, & any DB objects.
    B. populateOlympicsDb.sql – Script to add records to the database tables.
    C. dropOlympicsDb.sql – Script to delete all tables and database objects.
    4. Provide one or more scripts (with .sql file extension) to execute the queries defined for the project. Each script should include comments with your name and query/question number.


    Database Requirements
    The International Olympic Committee is creating a database for their upcoming summer Olympic games. Each sport has competitions for several different events. The competitions for each sport are assigned to a specific venue. A competitor is an individual athlete representing a particular country. Each event is scheduled for a single day and time, and we save a single result for each athlete competing in the event.
    Note: For this project, there are no qualifying rounds.
    The goal of the database is to identify when and where competitions occur, the athletes competing in events, and the medalists for each competition. Medals are awarded:
    1. Gold for 1st place
    2. Silver for 2nd place
    3. Bronze for 3rd place
    For each competition, results are stored for each athlete. The results are recorded as an elapsed time, a score, or a measurement.


    Data Population
    To test the database, you need to populate with data to demonstrate that the database will meet requirements and produce desired reports. For demonstration purposes, the data should be populated with events and competitors for Gymnastics, Track and Field, and Swimming. Use your own creativity to name athletes and choose countries for the competitions. Provide at least 5 competitors for each event. Some athletes should compete in multiple events and some compete in only a single event.


    Queries
    1. List all the Olympic events in which women compete, sorted alphabetically. No duplicates.
    2. List all the Olympic sports with the earliest event date/time and latest event date/time for the events contested in the sport. Order results by the name of the sport. Each sport should only be listed once.
    3. List all events scheduled for August 3rd, with the time and venue of the event. Sort events by the time, with the earliest event listed first. If more than one event starts at the same time, sort by the name of the event.
    4. For Gymnastics, list each event and the names of all the athletes competing in those events. Sort results by the name of the event, and secondarily by the name of each athlete.
    5. List all the countries in your database and the number of individual athletes from each country. Sort by country name.
    6. List all the names of athletes who compete in more than one event along with the name of his/her event and the competition date/time. Sort results by the athlete’s name.
    7. List each sport, the names of each event (including gender), and the number of competitors entered into each event.
    8. List results for Track and Field’s 100 Meter race with times for each athlete, with fastest time first. List the athlete’s name, country, and race time.
    9. List all medal results for events that have already occurred and been entered into the Olympics database. List the sport, event, athlete’s name, country and medal (Gold/Silver/Bronze). Order results by the sport, event, and place. (Gold Medal = 1st Place, Silver = 2nd Place, Bronze = 3rd Place)
    10. List the countries that won the most cumulative medals in descending order, listing the number of gold, silver, and bronze medals, along with the total. Report should be sorted with the most medals listed first in the report down to the country with the fewest medals. If countries have the same number of medals, the most Gold/Silver/Bronze next followed by alphabetic listing by country name.

    Learn More
  8. IM300 Week 4 Hotel Database Part 3 DDL script

    IM300 Week 4 Integrity and Constraints Hotel Database Part 3

    Regular Price: $25.00

    Special Price: $20.00

    IM300 Week 4 Integrity and Constraints Hotel Database Part 3


    Resources: SR-bi-003 – Hotel Database, the results from SR-bi-003 – Hotel Database Parts 1 & 2, Oracle® Database Express® and Microsoft® Visio® Virtual Desktop.


    Complete Service Request SR-bi-003 - Hotel Database–Part 3.


    Normalize the entities and attributes to at least the third normal form.


    Use Microsoft® Visio® diagramming tools to create an entity-relationship diagram (ERD) that demonstrates the logical design of your normalized database.


    Create and alter tables in the city’s Oracle® Database Express® database to implement the ERD design.


    Generate a new DDL script.


    Create a fax cover sheet addressed to the project manager for the ERD and a newly generated DDL script to show your progress on the project.

    Learn More
  9.  MIS562 Week 2 Homework Oracle Queries

    MIS562 Week 2 Homework Oracle Queries Database

    Regular Price: $25.00

    Special Price: $20.00

    MIS562 Week 2 Homework Oracle Queries

    Create the tables from demo.sql script as follows:
    Download and copy the demo.sql file from the Doc Sharing to the C:\temp directory. At the sql prompt enter the command @V:\temp\demo.sql.  This will create some tables and insert data into them. View the script in notepad to determine the table names that were created. Use the describe command to view the structure of the tables. Please use the instructions in the Submission Requirements to create and submit a document with your solutions. Submissions that do not meet the Requirements will be returned with a grade of zero and will be accepted and graded when corrected and re-submitted. Anyone having difficulties can obtain assistance in the TDAs.



    Write SQL statements to create result tables showing the solutions to the following requests.
    1. (4 pts) List all employee information in department 30.
    2. (4 pts) List employees name, job, and salary that is a manager and has a salary > $1,000
    3. (4 pts) Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
    4. (4 pts) Show all employee names and salary that earn between $1,000 and $2,000.  Use the between operator.
    5. (4 pts) Select all employees that are in department 10 and 30. Use the IN operator.
    6. (4 pts) Select all employee names with an “A” in the first position of the employee name.  Use the substring function or a wild card.
    7. (4 pts) Select all employees with an “A” as the second character of their name.  Use a wildcard.
    8. (4 pts) List the employee names in alphabetical sequence.
    9. (4 pts) List the job, salary, and employee name in job order and then salary in descending order.
    10. (4 pts) Show a list of different jobs.  Eliminate repeating values.
    11. (4 pts) Show employee names, salary that has not earned a commission yet.
    12. (4 pts) Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
    13. (4 pts) Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order.
    14. (4 pts) Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names.
    15. (4 pts) Determine which student does not have the first letter the last name capitalized.  Show the STUDENT_ID and LAST_NAME columns.
    16. (4 pts) Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format.  Show the instructor last name and the phone number that is in the incorrect format.
    17. (4 pts) Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
    18. (4 pts) Write a SQL statement that converts a date datatype to a char datatype.
    19. (4 pts) Write a SQL statement that convert a number value to a character.

    Before doing problems 20 - 25 Download and Execute the script demoaddrows.sql from the Doc Sharing to add rows into the two tables above. Write SQL statements and produce result tables to solve the following requests.
    20. (2 pts) Show a list of different jobs.  Eliminate repeating values.
    21. (2 pts) How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
    22. (2 pts) Show the employee name with the maximum salary
    23. (2 pts) Show the average salary for all employees that are not managers.
    24. (2 pts) What is the difference between the highest and lowest salary?
    25. (2 pts) Select employee number, name for all employees with salaries above the average salary. Use a subquery.

    Before doing problems 26 through 31 Create the text Student tables with the createStudent.sql script. The script and instructions are in the schemaset.zip file available in the Doc Sharing. Download and unzip the files from the Doc Sharing to a C: drive directory such as the \temp directory. At the sql prompt enter the command @V:\temp\createStudent.sql. This will create  tables exactely as they are portrayed in the text and insert data into them.
    26. (2 pts) Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence. Use the ZIPCODES table.
    27. (2 pts) Which city has the most zip codes assigned to it? Use a TOP-N query.
    28. (2 pts) Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence and exclude all states having less than 5 zip codes.
    29. (2 pts) Show the Student_ID, last name, and the number of enrollments for the student. Show only students with more than 2 enrollments. Use an Inline view.
    30. (2 pts) Show the number of students enrolled and zip code for New York and where the city begins with 'W'. Use a Scalar subquery.
    31. (2 pts) Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.

    Learn More

9 Item(s)

per page

Grid  List 

Set Descending Direction