Database

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

Items 31 to 40 of 189 total

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

Grid  List 

Set Descending Direction
  1. CMIS 420 Project 2 Create Script

    CMIS 420 Project 2 Advanced Relational Database Concepts and Applications

    Regular Price: $20.00

    Special Price: $15.00

    CMIS 420 Project 2 Advanced Relational Database Concepts and Applications

    1. Description:
    Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security logs for all transactions by user, product, and date. I will provide specific requirements and design details for this project below and we could have more discussion about the project in the Conferences area. Submit the scripts with all of your function SQL and PL/SQL code, and provide the results of running your scripts with the SQL*Plus spool command.

    2. Functional requirement:
    We will develop a small online transaction application supported by our database, movie distributing and renting system. In the application, we should meet three functional requirements:
    1) Track transaction events, and track runtime errors.
    2) Handle either movie distributing business process.
    3) Application interface which allows users to process either orders.
    4) No additional database tables are required unless you think it is necessary to enhance your application.

    3. Track transaction events, and track runtime errors:
    The link below provides a script which allow you to create two tables for tracking purposes One table for tracking events and the other for errors.
    Script to create logs tables
    Track errors or exceptions during runtime.
    Track any error or exception occurs during your application execution. this means that you should include tracking code in every program unit you develop.
    Take a look of the table for error logging and see what data should be inserted into the table when error occurs. The code should be part of error handling in exception handle section.
    Track event or application process during runtime.
    This type of logs for recording the events during the code execution. Content of the log may contain part of the data being changed and who changed it. The code to track events should be located at the end of a process.
    Take a look of the table for event logging and see what data should be inserted into these tables after event occurs.

    4. Handle movie distributing business process.
    In this part of the project, you are asked to design a few stored procedure or functions to handle online movie distribution.
    These procedures or functions should be able to
    1)process orders for distributors. When a customer (movie store) places order, ordering information should be stored in database tables.
    2)allow customers (movie stores) to check movie availability. if it is available, provide number in stock and unit price. If unavailable message the customer the movie requested is not available.
    3) if the order has been filled, generate an invoice to send to the customer.
    Note: This is one of the place where tracking event is necessary.
    Errors and events should be both tracked as described before.

    5. Application interface which allows users to process either orders:
    In general, the interface should provide GUI to users. However, PL/SQL does not have that feature. What we are going to do is to develop script which performs the similar functionality.
    Script should be able to
    1) take an order (ordering data)
    2) place an order by calling all the procedures used to process order.
    3) generate and print invoice based on order id.
    4) query event_logs and error_logs to show the result of the order transaction and/or any potential issues within the order.

    6. Submit requirement:
    The scripts to create procedures and function.
    The script to execute the procedures to place orders
    The text file for output of execution results.

    Learn More
  2. CMIS 420 Homework Assignment 3 Task 1

    CMIS 420 Homework Assignment 3 Advanced Relational Database Concepts and Applications

    Regular Price: $20.00

    Special Price: $15.00

    CMIS 420 Homework Assignment 3 Advanced Relational Database Concepts and Applications

    Homework Task 1:
    Create a PL/SQL block to complete the followings.  
    Output the cheapest movie information.  You can use the view you created in project 1 - task 1
    In your block, referential type should be used to receive the cursor return.
    Use DBMS_OUTPUT to output your result.
    Possible Exception should be handled in exception handling section.  You are not required to use user-defined exception. Use Oracle predefined exception. When exception occurs, you need to output error code and error message.
    Spool the output to a text file.  Don't forget to use "Set serveroutput on"
    Submit your code as .sql file and spooled output

    Homework Task 2:
    Create a stored procedure based on task 1 with an input parameter movie_id. Modify your cursor to use movie_id to select desired movie information. And execute the procedure and spool the execution result.
    Spool the output to a text file.  Don't forget to use "set serveroutput on"
    Submit your code as .sql file and spooled output

    Homework Task 3:
    Create a statement trigger on orders table. The trigger fires after updating the table. When the trigger fires one record insert into the following temp table using the insert statement shown below:
    Note: you need to create temp_table and its sequence using the following code.

    PROMPT creating table temp_table ...............
    DROP TABLE temp_table;
    CREATE TABLE temp_table
    ( num_col NUMBER(5) not null primary key,
    char_col VARCHAR2(30),
    date_col  VARCHAR2(30));

    PROMPT creating SEQUENCE trigger_seq ...............
    DROP SEQUENCE trigger_seq;
    CREATE SEQUENCE trigger_seq
    START WITH 1
    INCREMENT BY 1;

    INSERT INTO temp_table (num_col, char_col, date_col)
    VALUES (trigger_seq.NEXTVAL, 'After Statement trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
    Submit your code as .sql file

    Homework Task 4:
    Create a row trigger on order_items table to fire after inserting the data into order_items table. When the trigger fires it inserts a record into temp_table using the following insert statement:
    INSERT INTO temp_table (num_col, char_col, date_col)VALUES (trigger_seq.NEXTVAL, 'After Row Trigger', TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'));
    Submit your code as .sql file

    Homework Task 5:
    Test your triggers.
    Update orders table to set total payment to 300. fire statement trigger on orders table.
    Insert one record into order_items. Fire row trigger on order_items table.
    Query temp_table to get the inserted records for trigger firing.
    Spool the output to a text file and submit it.

    Learn More
  3. Chapter 3 Case Problem Level 1 Antiques

    Succeeding in Business with Access Chapter 3 Case Problem 1 and 3

    Regular Price: $25.00

    Special Price: $20.00

    Succeeding in Business with Access 2010
    Chapter 3 Analyzing Data for Effective Decision Making

    Case Problem Level 1 - managing Customer Information for NHD Development Group Inc.
    Antiques.accdb complete solution from Step 1 - Step 12.

    Case Problem Level 2 - Retrieving Employee Information for MovinOn Inc.
    MovinOn.accdb complete solution from Step 1 - Step 12.

    Learn More
  4. IT452 Unit: 7 Programmable Objects

    IT452 Unit 7 Programmable Objects

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 7 Programmable Objects

    1. Create a variable for the region column, birthdate column and empid column.

    2. Create and use a variable to pull the category name for categoryID 8. Write a separate query to return the output.

    3. Write an IF ELSE element that checks for the current month and prints ‘This Month is November’ if it is November or prints ‘This Month is not November’ if the month is not November.
    Prints This Month is November if you enter 11

    4. Write a WHILE Loop that returns ProductName, CategoryID and UnitPrice and compares the CategoryID to 4. As long as it is less than 4 it should print the product name, categoryid and unitprice.
    12 rows for Category 1
    12 rows for Category 2
    13 rows for Category 3

    5. Write a script that performs the following tasks:
    a. Define a variable to hold the maximum database ID value.
    b. Set that variable equal to the maximum database ID value.  To determine the maximum database ID value, use the database_id column of the sys.databases table in the master database.
    c. Use PRINT to print the value of the variable. Result: [Note:  PRINT writes to the Messages tab of the query window.]
    [Note:  Your result may vary.]

    6. Create a cursor that updates only to and from the ProductName column of the Production.Products table and print that column out. Returns all of the productnames upon successful execution

    7. Create a table variable called work table with the following structure. Insert the row of data listed and execute the batch.
    Worktable
    PK int Primary Key
    Col1 int Not Null
    Insert 1 row with the following data:
    1, 101
    Execute the batch
    Returns 1 row

    8. Create a global temporary table called GlobalTempWork with the following structure
    GlobalTempWork
    Test nvarchar primary key,
    Col1 int
    Insert 1 row with the following data
    10
    Execute the batch to return the 1 row

    Learn More
  5. IT452 Unit 8 Programmable Objects II

    IT452 Unit 8 Programmable Objects II

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 8 Programmable Objects II

    1. Write a stored procedure called sp_GetCompanyName. This procedure requiring a join should get the customer name (not individual) and the city the customer gets items shipped to for those customers that have items shipped to Madrid (this is a hint on the table it should be joined with in the query). Execute the stored procedure and submit results with query.
    Returns 2 rows

    2. Write a stored procedure called sp_GetEmployees to dynamically retrieve the city that each employee lives in when it is executed. In addition, please return the lastname and firstname. Execute the procedure and submit the code and results for employees living in London.
    Returns 4 rows  

    3. [TSQLFundamentals2008 and tempdb]  Scenario: You want to test a stored procedure that will delete the oldest orders. You will run your tests in the tempdb database.

    a. Create an exact copy of the TSQLFundamentals2008.Sales.Orders table in the tempdb database (as table tempdb.dbo.Orders) by using SELECT INTO.
    The tempdb.dbo.Orders table should have 830 rows.

    b. In tempdb, create a stored procedure named dbo.pDeleteOldestOrders that (1) determines the earliest year and month of orderdate values in the Orders table, and then (2) deletes all orders with an orderdate in that year and month. (Hint: Use YEAR and MONTH functions in subqueries to determine the year and month of the oldest order. Place these values in variables, then use the variables in the WHERE clause of your DELETE statement.)
    Should show 22 rows affected

    4. Use Northwind (create one if one does not exist) and Master  Create a rollback (prevent changes if attempt to change occurs and prints a system message and the custom message using RAISERRORThese Procs may not be altered or dropped!) Trigger called NoTouchDaProc that prevents any stored procedure from being altered or dropped in the database (Hint: in the database tells you which type of trigger).
    Submit code and printscreen and drop the trigger after getting results.

    5. Use the tempdb and using SELECT INTO create a table called Person using all of the data from the Adventureworks.Person.Contact Table.
    You should have 76 rows of individuals with the LastName Carter and 88 rows of individuals with the LastName Johnson before you begin.
    1. Create a Trigger called PersonTrigger that works on data after it is inserted into the table and checks for a change to the LastName field. If there is a change to the LastName field print to the screen 'You might have modified the LastName column'. Otherwise have it print 'The LastName column is untouched.' (Hint: IF Else would work here). Make print screens of successful completion and copy and paste your code into your submission.
    2. Change the LastName of the person with the contactID of 32. This should update 1 row and fire the PersonTrigger with the correct message. Make print screens of successful completion and copy and paste your code into your submission.
    3. Alter the PersonTrigger to check the Inserted and Deleted tables (those that get created when data is inserted or deleted for tables/rows included within a trigger) so that that the trigger now looks in the Inserted and Deleted tables for data updated in the LastName field of the person table. Here is what the SELECT clause should look like: SELECT D.LastName + ‘ changed to ‘ + I.LastName. Complete the balance of the query including the required JOIN between Inserted and Deleted. At this point you should now have 89 people with the LastName Johnson. Make print screens of successful completion and copy and paste your code into your submission.
    4. Change anyone with the LastName of Carter to Johnson. This should fire the altered trigger and use the new SELECT clause to report the results to the screen with the number of rows changed to Johnson. 89 rows should be changed from Carter to Johnson. Make print screens of successful completion and copy and paste your code into your submission.

    6. [TSQLFundamentals2008 and tempdb]  Scenario: You want to archive deleted orders. You will create a DML trigger to accomplish writing all columns of a deleted order and the time the deletion occurred to an OrdersArchive table. You will test this trigger in the tempdb database.
    a. "Reset" the dbo.Orders table in the tempdb database by dropping the table (DROP TABLE tempdb.dbo.Orders; ) and then re-running your code from step a. of the previous problem.
    b. Create the tempdb.dbo.OrdersArchive table by executing the following code:
    USE tempdb;
    GO
    CREATE TABLE [dbo].[OrdersArchive]
    (
    [orderid] [int] NOT NULL CONSTRAINT PK_OrdersArchive PRIMARY KEY,
    [custid] [int] NULL,
    [empid] [int] NOT NULL,
    [orderdate] [datetime] NOT NULL,
    [requireddate] [datetime] NOT NULL,
    [shippeddate] [datetime] NULL,
    [shipperid] [int] NOT NULL,
    [freight] [money] NOT NULL,
    [shipname] [nvarchar](40) NOT NULL,
    [shipaddress] [nvarchar](60) NOT NULL,
    [shipcity] [nvarchar](15) NOT NULL,
    [shipregion] [nvarchar](15) NULL,
    [shippostalcode] [nvarchar](10) NULL,
    [shipcountry] [nvarchar](15) NOT NULL
    [whenarchived] [datetime] NOT NULL
       CONSTRAINT DF_WhenArchived DEFAULT CURRENT_TIMESTAMP
    );

    c. Create a trigger named dbo.tdArchiveOrders that writes rows deleted from dbo.Orders to the dbo.OrdersArchive table. Note that the when archived column should contain the current datetime value for the deleted row.
    d. Execute the dbo.pDeleteOldersOrders stored procedure that you wrote in the previous problem.

    In your Assignment document show:
    • The code that creates the dbo.tdArchiveOrders trigger
    • The contents of the messages tab of the query window after executing the stored procedure

    Learn More
  6. IT452 Unit 10 Final Project Creating Tables

    IT452 Unit 10 Final Project Misty Tutoring Business

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 10 Final Project
    In this project, you will create an end-to-end solution to meet a business need. This project is worth 200 points.

    Scenario
    Your good friend, Misty, has a tutoring business in Southern California. It is quite successful. A client will call with a tutoring need, and Misty will look on her Excel spread sheet of tutors for those qualified in the tutoring area the client needs, and who live near the client.
    Misty now has approximately 20 freelance tutors registered with her. It has become quite difficult for her to tell what tutors are qualified and live near the client. You have told her you will develop a way for her to get such a list.
    She has sent you the TutorList.xls file with the current list of tutors. (This file is available in Doc Sharing.) The Subject column has values of A (tutor is proficient in English and history), B (tutor is proficient in math and science), or C (tutor is proficient in all basic subjects). Hint: You need to create a database that consists of the data in these to files. The DateQualified column has the latest date the tutor was interviewed and checked out by Misty. In addition, she has provided the current term’s client list (Clients.xls). You will need to use BULK INSERT to load the data from the sheets into the tutoring database and write queries that find tutors in the same zip code as the client and provide the tutor in the same zip code in a report. Misty should be able to select the zip code as a parameter from the tutors in the report and the zip code as a parameter from the clients as a parameter to execute the reports.
    Misty also requires that she should be able to interact with a Web page, because she does not want to know how to "run database software". This will require creating a web based report that she can access via a web link.
    The first thing you should do is list the steps in your plan. What will be the major steps you need to take to accomplish your goal? The first step will be to install SQL Server 2008 Express with Advanced Services on Misty's computer. (She has agreed to that part of the plan.) The last step will be to create a report that asks for parameter values for a stored procedure or query at run time. This does not literally need to be done but should be a step in the plan if you advise her that it is needed on her machine.
    For the purpose of this project, you will use your computer to work out the steps and create the report.
    The first item to place in your final project document is your outline of major steps that need to be accomplished. Label this section:

    1. Outline – 20 points
    <Put your plan step list here.> (The outline will be worth 20 points.)
    Then, for each step after the first (the installation step), make a section in your project, e.g.

    2. <Whatever Step 2 is in your plan outline> - 80 points
    In that section, state how you accomplished that step and/or give the code you used. Enough detail should be given so a knowledgeable person could completely recreate what you did. Show at least some of the results you obtained when you tested to see whether your step had been accomplished. (For
    queries, 3 or 4 lines of output, and stating the number of lines returned is sufficient.) If a screen shot is the best way to demonstrate the results, include it in your project. Please use ALT+PrintScreen so that only the in-focus or active window, not the entire desktop, is copied.
    [Note: You are starting with step 2, because step 1 of your plan is the installation, and it will be assumed that you know how to do that.]
    [Hint: One of your steps should be the creation of a stored procedure that uses a variable for the zip code and determines which tutors live in the same zip code as the client. But, this step will be carried out in #3 below so you might want to letter your steps to avoid confusion.

    3. Create the query or stored proc. that will produce the result set for the report - 40 points (Creation and Alter)
    Create Procedure – 20 points
    Alter Procedure – 20 points
    The query / stored procedure that generates the result set for the report will be worth 20 points each. You then will also Alter the Stored Procedure one time and execute it. You should determine that it gives the desired results before configuring your report. Hint: The SELECT query portion of the stored procedure will be the query you use in your report datasource when you get to the portion of the design wizard (if you use the wizard instead of manually creating everything).

    4. [The last step.] Configuring the Report and the Report Parameters – 60 points
    Query for Report 20 points
    Configuring Parameters – 20 points
    Configuring/Designing 2 reports – 20 points
    The final 60 points will be for the last step in your plan – configuring the report and the parameters for the report. The tutor subject choices should be A, B, or C. The date selection should be from the DateQualified values, with a default value of the earliest DateQualified. The customer zip code must be one of those in the TutorList. [Hint: The latter two parameter items require creation of additional data sets, one for the DateQualified values, and one for the zip code values. Hint: Each of the datasets should use a SELECT query as well to provide the data for the parameter. Look at what you did in Project 9 as an example. The DateQualified data set will have datetime data type. Each parameter will become a drop down on your report.
    You should place four screen shots under this final step in your project document.
    1. For the parameter that specifies Misty's choice of a tutor that resides in the same zip code as the client show one such client/tutor example.
    2. For the parameter that specifies Misty's choice of minimum DateQualified, show how you have configured the Available Values for that parameter.
    3. Show the Report Data pane of BIDS, showing all parameters and all data sets with their fields.
    4. Deploy the report. Run the first report that does not contain parameters for zip code 91016. This one should show only the tutors available for that zip code. Run the second report with parameters for a customer zip code of 91803 for the report with parameters showing which client they qualify to work with, what their skill set is, and date equal to the earliest DateQualified value. Paste the screen shot of this report into your project document.
    Because the entire project involves sequential steps, if you become hung somewhere in the middle and cannot proceed, you may send details to your instructor, and receive a hint in exchange for an appropriate point "cost" or deduction. If you cannot figure out what query you need to create the report, the instructor will send it to you, on request from you, for a "cost" of 20 points so you can move on.
    Your screen shot of the report without parameters should look similar to this on the Report BIDS screen. It should be for ZipCode 91016 regardless to subject and qualified date:
    Your screen shot of the report without parameters should look similar to this on the Report Manager screen:
    Your screen shot of the report with parameters should look similar to this on the Report BIDS screen It should be for ZipCode 91803 regardless to subject and qualified date:
    Your screen shot of the report with parameters should look similar to this on the Report Manager screen:

    Learn More
  7. Microsoft Access 2010 Chapter 4 Lab 1 Customer Balance Report

    Microsoft Access 2010 Chapter 4 Lab 1 Presenting Data in the ECO Clothesline Database

    Regular Price: $20.00

    Special Price: $15.00

    Microsoft Access 2010 Chapter 4 Lab 1 Presenting Data in the ECO Clothesline Database


    Problem: The management of ECO Clothesline already has realized the benefits from the database of customers and sales reps that you created. The management now would like to prepare reports and forms for the database.


    Instructions: If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the ECO Clothesline database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Open in Layout view the Customer Balance Report you created in Chapter 1 and revised in Chapter 3. Modify the report to create the report shown in Figure 4–81. Group the report by Customer Type and sort by Customer Number within Customer Type. Add the Amount Paid field to the report and include totals for the Balance and Amount Paid fields.


    2. Create the Customers by Sales Rep report shown in Figure 4–82. Include a total for the Balance field. Change the orientation to landscape. Make sure the total control displays completely. (Hint: Use Layout view to make this adjustment.)


    3. Create the Customer Financial Form shown in Figure 4–83. The form includes the date.


    4. Create mailing labels for the Customer table. Use Avery labels C2163 and format the labels with customer name on the first line, street on the second line, and city, state, and postal code on the third line. Include a comma and a space after city and a space between state and postal code. Sort the labels by postal code.


    5. Submit the revised database in the format specified by your instructor.

    Learn More
  8. Microsoft Access 2010 CHAPTER 4 Client Financial Form

    Microsoft Access 2010 CHAPTER 4 Apply your Knowledge AC 261 and AC 262

    Regular Price: $20.00

    Special Price: $15.00

    Microsoft Access 2010 CHAPTER 4 Apply your Knowledge AC 261 and AC 262


    Creating a Report and a Form
    Instructions: Start Access. If you are using the Microsoft Access 2010 Complete or the Microsoft Access 2010 Comprehensive text, open the Babbage CPA Firm database that you used in Chapter 3. Otherwise, see your instructor for information on accessing the files required in this book.


    Perform the following tasks:
    1. Create the Clients by Bookkeeper report shown in Figure 4–75. The report is grouped by bookkeeper number and sorted by client number within bookkeeper number. Include totals for the Amount Paid and Balance Due fields. Change the orientation to landscape.


    2. Create the Client Financial Form shown in Figure 4–76 for the Client table. The form includes the current date and is similar in style to that shown in Figure 4–3 on page AC 212.


    3. Submit the revised database in the format specified by your instructor.

    Learn More
  9. MS Access Chapter 2 Lab 1 Answer Lab 2-1 Step 1 Query

    Microsoft Access 2010 Chapter 2 Lab 1: Querying the ECO Clothesline Database

    Regular Price: $20.00

    Special Price: $15.00

    Microsoft Access 2010 Chapter 2 Lab 1: Querying the ECO Clothesline Database

    Problem: The management of ECO Clothesline has determined a number of questions it wants the database management system to answer. You must obtain answers to the questions posed by management.
    Instructions: Use the database modified in the In the Lab 1 of Chapter 1 on page AC 66 for this assignment, or see your instructor for information on accessing the files required for this book.
    Perform the following tasks:
    1. Open the ECO Clothesline database and create a new query for the Customer table that includes the Customer Number, Customer Name, Amount Paid, and Sales Rep Number fields in the design grid for all customers where the sales rep number is 49. Save the query as Lab 2-1 Step 1 Query.
    2. Create a query that includes the Customer Number, Customer Name, and Amount Paid fields for all customers located in Virginia (VA) with a paid amount greater than $1,000.00. Save the query as Lab 2-1 Step 2 Query.
    3. Create a query that includes the Customer Number, Customer Name, Street, and City fields for all customers whose names begin with T. Save the query as Lab 2-1 Step 3 Query.
    4. Create a query that lists all cities in ascending order. Each city should appear only once. Save the query as Lab 2-1 Step 4 Query.
    5. Create a query that allows the user to enter the city to search when the query is run. The query results should display the Customer Number, Customer Name, Balance, and Amount Paid fields. Test the query by searching for those records where the client is located in Ashton. Save the query
    as Lab 2-1 Step 5 Query.
    6. Include the Customer Number, Customer Name, and Balance fields in the design grid. Sort the records in descending order by the Balance field. Display only the top 25 percent of the records in the query result. Save the query as Lab 2-1 Step 6 Query.
    7. Join the Sales Rep and the Customer table. Include the Sales Rep Number, First Name, and Last Name fields from the Sales Rep table. Include the Customer Number, Customer Name, and Balance from the Customer table. Sort the records in ascending order by sales rep’s last name and customer name. All sales reps should appear in the result even if they currently have no customers. Save the query as Lab 2-1 Step 7 Query.
    8. Open the Lab 2-1 Step 7 Query in Design view and remove the Sales Rep table. Add the Amount Paid field to the design grid. Calculate the total of the balance and amount paid amounts. Assign the alias Total Amount to the calculated fi eld. Change the caption for the Balance field to Due. Save the query as Lab 2-1 Step 8 Query.
    9. Create a query to display the average balance amount for all customers. Save the query as Lab 2-1 Step 9 Query.
    10. Create a query to display the average balance amount for sales rep 51. Save the query as Lab 2-1 Step 10 Query.
    11. Create a query to display the average balance amount for each sales rep. Save the query as Lab 2-1 Step 11 Query.
    12. Create the crosstab shown in Figure 2 – 92. The crosstab groups the total of customers amount paid amounts by state and sales rep number. Save the crosstab as State-Sales Rep Crosstab.
    13. Submit the revised database in the format specified by your instructor.

    Learn More
  10.  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

Items 31 to 40 of 189 total

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

Grid  List 

Set Descending Direction