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 Ascending Direction
  1. 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
  2. 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
  3. IT452 Unit 6 Set Operations and Data Modification

    IT452 Unit 6 Set Operations and Data Modification

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 6 Set Operations and Data Modification

    1. List the contact name, contact title, address, city, region, postal code, and country of all customer and supplier contacts. Sort the results by country, then by region, then by city. Use UNION ALL. 120 rows returned

    2. List the city, region, and country that have both a customer and a supplier. Use INTERSECT. 4 rows returned

    3. Create a table in the tempdb database using the following CREATE TABLE statement:
    USE tempdb; CREATE TABLE dbo.DOGS ( DogID int IDENTITY NOT NULL ,Name varchar(20) NOT NULL ,BirthDay date ,Alive char(1) NOT NULL -- Either 'Y' or 'N' ); GO
    Write one INSERT statement with one VALUES clause to insert information about the following dogs:
    Name Birthday Alive
    Samantha 1993-03-17 Passed away Feb 2009 (Alive = 'N')
    Misty 1993-06-20 No longer alive
    Henry the 1/8th 2003-10-21 Alive
    (Note: a small dog) Inka 2006-09-18 Alive
    Result: (4 row(s) affected) Paste your INSERT statement into your Assignment document.

    4. Use SELECT … INTO … syntax to create a Vendor table (name the table Vendor) in the tempdb database that has the exact structure and content as the purchasing.vendor table. Validate that the new vendor table was created in the tempdb (using SQL Query) and that all rows were inserted. Paste both queries into your project document. (104 rows returned)

    5. Write a DELETE statement that removes the vendors from the tempdb.vendor table that have the PreferredVendorStatus flag turned on. Result: (11 row(s) affected)

    6. The tempdb.vendor table is no longer needed. Please truncate it. Verify the table has been emptied using a SQL Query.

    7. Run the following code to create the tempdb.dbo.DimProducts table:
    USE tempdb;
    CREATE TABLE [dbo].[DimProducts]( [dimProdID] [int] NOT NULL,
    [ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
    [UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL, CONSTRAINT [PK_Products] PRIMARY KEY
    CLUSTERED ( [dimProdID] ASC ) );
    GO
    Use bcp to import data into the tempdb.dbo.DimProducts table from the DimProducts.txt file. The file may be obtained from Doc Sharing.
    You should get a message that 77 rows were imported. Put a copy of your bcp command into your Assignment document.

    8. Run the following code to create the tempdb.dbo.ProductStage table:
    USE tempdb;
    CREATE TABLE [dbo].[ProductStage]( [dimProdID] [int] NOT NULL,
    [ProductID] [int] NOT NULL, [ProductName] [nvarchar](60) NOT NULL,
    [UnitPrice] [smallmoney] NOT NULL, [BeginDate] [date] NOT NULL,
    [EndDate] [date] NOT NULL );
    GO
    Use BULK INSERT to import data into the tempdb.dbo.ProductStage table from the ProductStage.csv file. That file may be obtained from Doc Sharing.
    You should get a message that 27 rows were imported. Put a copy of your BULK INSERT command into your Assignment document.

    9. Write a query that determines if there are any product names in the tempdb.dbo.ProductStage table that are not in the tempdb.dbo.DimProducts table. Use EXCEPT. Put a copy of your query into your Assignment document. In addition, put the product name(s) you returned from this query into your Assignment document.

    10. Write a MERGE statement that modifies the tempdb.dbo.DimProducts table based on the contents of the tempdb.dbo.ProductStage table. When the dimProdID values match, update the target table based on what is different in the source row. [Examine the data in both tables to determine what row(s) will need updating.] When the dimProdID values do not match, insert the source row. Paste your MERGE query into your Assignment document. It should return a message that 27 row(s) were affected. In addition, report how many rows.

    Learn More
  4. IT452 Unit 5 Table Expressions

    IT452 Unit 5 Table Expressions

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 5 Table Expressions

    1. What is the average number of order line items over all orders? The Sales.OrderDetails table has one row for each product for each order. Each row represents a "line item" on a sales invoice for the orderid. Start by writing a query that returns the number (COUNT) of line items (rows) for each orderid. Modify that query to CAST the line item count as decimal (8,4). Then use that query as a derived table to get the average number of line items over all orders.
    (Returns 9 rows)

    2. Create a view named Sales.LOLJO_Orders that lists all the orderid values for orders placed by customer 'Customer LOLJO'. (Requires an Inner Join)
    (Returns 10 rows)

    3. Create a view named Sales.WhoSoldToLOLJO that lists the empid, lastname, and firstname of all employees who have sold to customer 'Customer LOLJO'. Execute a Select query to show the results of the view.
    (6 rows)

    4. Write a CTE to extract the employee with the most orders. Write the Outer query to display the EmployeeID and Highest Orders Sold Count for that Employee Result: (empid 4, 156 orders)

    5. Create an inline table-valued function named Sales.fCustOrders that takes a customer name as an argument and returns the orderid and orderdate values for orders placed by that customer. The orderdate should have the date only, no time (Hint: CAST). Your project should have your CREATE FUNCTION statement. Query the function to see the results from the function for the argument (page 165) for companyname 'Customer MLTDN'.
    (4 rows)

    6. Create a VIEW called TopSales that shows the TOP 10 percent of sales. Verify the results of the view in a SELECT clause.
    (216 rows)

    7. Encrypt the TopSales View and bind the schema so that it cannot be dropped or altered created in #6. Test it by trying to remove the producID from the underlying Table and show the error

    8. Change the TopSales view from # 7 so that it contains a filter that only has qty = 130 and does not allow modifications. Test it with an Update Statement by trying to change the quantity.

    Learn More
  5. IT452 Unit 4 Sub Queries

    IT452 Unit 4 Outer Joins and Subqueries

    Regular Price: $20.00

    Special Price: $15.00

    IT452 Unit 4 Outer Joins and Subqueries

    a) List all order ID's for the customer named 'Customer LOLJO'. You must use a subquery.

    b) List the lastname and firstname of all employees who have sold to customer 'Customer LOLJO'. Use a nested subquery (no joins).

    c) List the empid, lastname, and firstname of all employees who are managers. (If an employee is a manager, his/her empid will appear as someone else's mgrid.) Use a subquery. (HINT: The subquery will list all the distinct mgrid values.)

    d) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write an uncorrelated subquery that uses NOT IN.

    c) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Write a correlated subquery that uses NOT EXISTS.

    e) List the empid, lastname, and firstname of all employees who did not take an order in the month of February, 2007. Use an outer join.
    (HINT: First, write an inner join that returns all the (distinct) employees who *did* take an order during that month. Next, change WHERE to AND so that the predicate is part of the ON clause of the join. Finally, change your join to an outer join, using the WHERE clause to filter for only non-matching rows.)

    f) Write a subquery that returns the first order placed in the orders table

    g) Using a scalar subquery (returns only 1 row) to outer query, write a query to returns the customer id for orders placed by the sales rep Judy Lew

    h) Select the EmployeeID, lastname, firstname for the employee that had sales but does NOT have a manager. (requires Inner Join and subquery)

    i) Find the customer, shipcountry and number of orders that the customer made where the customer is not located in the United States. (Requires a Count, subquery and condition)

    Learn More
  6. CIS 373-30 SQL 1 Answers

    CIS 373-30 SQL 1 EXERCISE

    Regular Price: $20.00

    Special Price: $15.00

    CIS 373-30 SQL 1 EXERCISE
    What to do:
    Problem: ConstructionCo, pp. 278 – 282, Do problems 1 to 12 from page 278-280
    You are given a partially correct Oracle script file: Ch07_ConstructCo_ORA.sql (posted in the same folder).  The script is incomplete because they did not define primary keys and foreign keys when creating the tables.  As shown in class (and also in my video capture), you will create your own script file based on this one by defining the primary keys and foreign keys (refer to the text book for the ERD).
    To make it easier for you, I added several SQL Plus commands to redirect the output.  At the beginning of the Ch07_ConstructCo_ORA.sql ,  I added:

    spool j:\scripts\SQL1.lst
    set pagesize 24
    set pause on
    set linesize 60
    and at the very end, I added:
    Spool out

    The Spool command will redirect the output, in my case, to the J: drive in the scripts folder and the file will be called SQL1.lst  (lst = listing, typical extension for output list).  Make sure you know the path name to redirect the output to.  I would recommend you to use the USB drive; make your path name shorter to avoid typos.  Please also noted that I set the pause ON. It means that you have to hit “Enter” to move to the next page.
    How to do:
    (1)    Start your Notepad, copy the necessary statements from Ch07_ConstructCo_ORA.sql  with the proper PKs and FKs, save it as  Ch07_SQL_1_Answers.sql.
    (2)    Log in Oracle 11g XE
    (3)    Start SQL Command Line (SQL Plus) program.
    (4)    In SQL Plus, load and execute  Ch07_SQL_1_Answers.sql  (by using the start command in SQL Plus.)
    (5)    To answer the questions, you can type your SQL statement directly in SQL Plus.  If it works, copy and paste the statement in your script file Ch07_SQL_1_Answers.sql.  Use comment, e.g. --, or /* …. */ to separate the questions.  The following is the skeleton of your script file:

    spool j:\scripts\SQL1.lst
    set pagesize 24
    set pause on
    set linesize 60

    -- Name:  <Your Name>  SQL 1 Homework, pp. 278-280
    --
    <<<< Based on Ch07_SQL_1_Answers.sql, create the tables with proper PKs and FKs.  Copy all  INSERT statements to populate the tables.  Then you can start answering the questions.  Make sure you have created and populated  the tables correctly before moving forward.  Do it incrementally like I demonstrated in the videos. >>>>

    -- Problem 1:
    select name from student where student_ID = 1234;

    --
    -- Problem 2:
    insert into Student Values (13567, “Doe”, “John”, “CIS”);
    : : : : :

    --  Problem 12:
    UPDATE EMP_2
    SET …….
    WHERE EMP_HIREDATE ….
    AND JOB_CODE >= ….. ;

    Spool out

    After you have finished all problems and created the script file, run the script file in its entirety one last time. When you are finished, submit both Ch07_SQL_1_Answers.sql and the output file SQL1.lst in Blackboard.

    Learn More
  7. Small Access Database Customers Table

    Small Business Jewelry Store Access Database with Form and Reports

    Regular Price: $20.00

    Special Price: $15.00

    Create a database using Microsoft Access.

    The database should contain three tables: Customers, Products, and Transactions; three queries: list of customers, transactions by customer, and transactions by product; and be something that a small business, such as a jewelry store or used car lot, can use for its daily needs.

    The database must contain an input form for the transactions and one report that is based on any of the queries; the tables must each contain 10 records; and the records in the transactions table must be created by way of the input form.

    In this assignment, use Microsoft Access to create the following: Forms Tables Queries Reports

    Learn More
  8. CMIS 420 Final Project Create Star Schema

    CMIS 420 Final Project Advanced Relational Database Concepts and Applications

    Regular Price: $20.00

    Special Price: $15.00

    CMIS 420 Final Project Advanced Relational Database Concepts and Applications

    1. Description:
    In the final project, you will demonstrate your ability to design, build, and populate a small data warehouse to support decision-making, business modeling, and operations research. Using data supplied by me, prepare a star schema and several queries that support decision-making. Submit the following deliverables:
    1) SQL scripts showing the design, loading, and queries of the database.
    2) results of running the script with the SQL*Plus spool command.
    3) presentation to your fellow classmates describing the data warehouse, its design, and possible uses and applications for critical business decisions.

    2. Tasks to be completed:
    Based on our operational database, movie distributing and renting system (focus on movie distribution and sale), build a data mart (star schema) which can answer the following questions:
    1) Which movie and\or movie category have the better sale by months and\or quarters?
    2) Which distributor had best or better earnings (suppose profit margin is 12% for all the distributors) by months and\or quarters??
    3) Which store should be the better sale target for movie sale?
    The following link provides zip file containing a script to create a PL/SQL package which adds ordering records to your database. and another script to execute the procedures in the package to complete the tasks. At the end of execution, you will expect 500 to 1000 records in orders table and 3000 to 6000 records in order_items table. These data plus the existing data would serve you as operational data for your data warehousing project. The data in your dimension and fact tables should be derived from this database.
    Run create_package.sql first, and then run_all.sql to execute the procedures in the package.

    Learn More
  9. IT358 Unit 5 INNER JOIN OUTER JOIN subquery

    IT358 Unit 5 INNER JOIN OUTER JOIN subquery

    Regular Price: $20.00

    Special Price: $15.00

    IT358 Unit 5 INNER JOIN OUTER JOIN subquery

    Outcomes addressed in this activity:
    Create a query with an INNER JOIN
    Create a query with an OUTER JOIN
    Create a query that contains a subquery

    Project requirements:
    In this project, you will write joins and subqueries:
    1. Complete the Hands-on Assignments #1–5 for Chapter 9.
    2. Complete the Hands-on Assignments #1–5 for Chapter 12.

    Oracle 11G SQL Chapter 9 Hands-On assignment Solution
    Generate and test two SQL queries for each of the following tasks: (a) the SQL statement needed to perform the stated task using the traditional approach, and (b) the SQL statement needed to perform the stated task using the JOIN keyword.
    1. Create a list that displays the title of each book and the name and phone number of the person at the publisher’s office whom you would need to contact to reorder each book.
    2. Determine which orders have not yet shipped and the name of the customer who placed each order. Sort the results by the date on which the order was placed.
    3. List the customer number and names of all individuals who have purchased books in the fitness category.
    4. Determine which books Jake Lucas has purchased. Perform the search using the customer name, not the customer number.
    5. Determine the profit of each book sold to Jake Lucas. Sort the results by the date of the order. If more than one book was ordered, sort the results by the profit amount in descending order. Perform the search using the customer name, not the customer number.

    Oracle 11G SQL Chapter 12 Hands-On assignment Solution
    Use a subquery to accomplish each of the tasks. First, execute the query you will use as the subquery to verify the results.
    1. Determine which books have a retail price that is less than the average retail price of all books sold by JustLee Books.
    2. Determine which books cost less than the average cost of other books in the same category.
    3. Determine which orders were shipped to the same state as order 1014.
    4. Determine which orders had a higher total amount due than order 1008.
    5. Determine which author or authors wrote the book(s) most frequently purchased by customers of JustLee Books.

    Learn More
  10. IT358 Unit 6 Views

    IT358 Unit 6 Views

    Regular Price: $20.00

    Special Price: $15.00

    IT358 Unit 6 Views

    Outcomes addressed in this activity:
    Create a view, using CREATE
    VIEW command or the CREATE OR REPLACE VIEW command
    Explain the effect of the WITH READ ONLY option
    Demonstrate the ability to update records in a simple view
    Demonstrate the ability to re-create a view
    Identify problems associated with adding records to a complex view
    Identify the key-preserved table underlying a complex view

    Project requirements:
    In this project, you will write joins and subqueries:
    1. Complete the Review Questions for numbers 1–5 from Chapter 13.
    2. Complete the Hands-on Assignments #1-10 from Chapter 13.

    Oracle 11G SQL Chapter 13 Hands-On assignment Solution
    1. Create a view that will list the name of the contact person at each publisher and the person’s phone number. Do not include the publisher’s ID in the view. Name the view CONTACT.
    2. Change the CONTACT view so that no users can accidentally perform DML operations on the view.
    3. Create a view called HOMEWORK13 that will include the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make certain the view will be created even if the FIRSTATTEMPT table does not exist.
    4. Attempt to view the structure of the HOMEWORK13 view.
    5. Create a view that will list the ISBN and title for each book in inventory along with the name and telephone number of the individual to contact in the event the book needs to be reordered. Name the view REORDERINFO.
    6. Try to change the name of one of the individuals in the REORDERINFO view to your name. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
    7. Select one of the books in the REORDERINFO view and try to change the ISBN of the book. Was there an error message displayed when performing this step? If so, what was the cause of the error message?
    8. Delete the record in the REORDERINFO view that contains your name (if that step was not performed successfully, then delete one of the contacts already listed in the table).Was there an error message displayed when performing this step? If so, what was the cause of the error message?
    9. Issue a rollback command to undo any changes made with any previous DML operations.
    10. Delete the REORDERINFO view.

    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 Ascending Direction