MIS 385 ASSIGNMENT 4 (SQL 1)



MIS 385 ASSIGNMENT 6 (SQL 3 – Triggers, Atomic Transactions Timestamps & Other Platforms)

See syllabus for due date.

This assignment is an individual assignment – you are to do it yourself, only receiving help from the instructor.

Triggers and Multiple Platforms – The first four are basically the same kinds of queries you’ve done already, but I will require that you do them in Teradata. . Further, you will be trying out things such as transaction atomicity, triggers and selecting timestamps in MySQL.

To complete the assignment, you should submit the actual querys you wrote for each problem, in a text document (or MS Word document with smart quotes turned off) to salisbury@udayton.edu via email with the subject DATABASE ASSIGNMENT 6. The tables are fairly small, so you should be able to self-check to see if you’re queries are working right. The answers will have all fields from the table, unless otherwise stated.

1. Create a list of all customer names (without repeats) for Pine Valley Furniture from Florida who have placed orders (Teradata SQL Assistant)

2. Create a list of customers without any orders, listed in reverse alphabetical order. (Teradata SQL Assistant)

3. Create a query that generates an invoice for order #1006 (looks rather like the one on page 332 of your text, yes?). (Teradata SQL Assistant)

4. We just got a recall on Entertainment Centers; write a query to display the customer ID, customer name, order ID, order date, product ID and product description for all orders that contain Entertainment Centers. (Teradata SQL Assistant)

The rest of these are for you to have a chance to play a bit and see how stuff works. Just follow the instructions carefully (I tried all of them out) and all will go well (eventually).

5. This one is to learn about COMMIT and ROLLBACK, which are features designed to help ensure transaction atomicity. Take the query described below in bold italics (from Assignment 2) and try to execute all of the necessary steps as a single transaction that can either be committed or rolled back. First, you’ll need to disable MySQL’s auto-commit feature (SET AUTOCOMMIT =-0;). Next, you’ll need to preface the statements with “START TRANSACTION;”. Then, you’ll need to end with either “COMMIT;” to commit the data to the table, or “ROLLBACK;” to back out of it. Try this two ways; first with a ROLLBACK, then next with a COMMIT. Run a select from both the order_t and orderline_t to see that it works. Be certain to use an orderID (in both tables) that doesn’t already exist in the table. Put the code for both ways in your answer submission. (MySQL)

FROM ASSIGNMENT 2: “Create a batch query to add a new order (#1011) to for Contemporary Casuals. This order should have 3 Dinner Tables, 2 Computer Desks and 4 End Tables. The statements in this query should be in order.”

6. Write a query to select the current system date and time. Give it a meaningful name (e.g. “Current_Date_And_Time”. Dig around on Google and you’ll find out how. Really. (MySQL)

7. Create the sample table and necessary triggers as described at: . Make sure to drop the trigger and tables after you’re done. Show me the SQL for all steps. Include a brief (2-3 sentences) write-up of what it does. (2 points MySQL)

8. Create the sample table and necessary trigger(s) as described at:

. Before you can start you need to create a table that looks like this (what the heck – it’s in SQL and you can copy/run it), and insert one record into it (I’ll let you figure that part out):

CREATE TABLE employees (

id int(11) NOT NULL AUTO_INCREMENT,

employeeNumber int(11) NOT NULL,

lastname varchar(50) NOT NULL,

PRIMARY KEY (id))

Other than that, do the steps at the URL listed. Be certain to drop the trigger and the extra tables once you’re done. Show me the SQL for all steps. Include a brief (2-3 sentences) write-up of what it does. (2 points My SQL)

9. Take a point (yup, it’s a bonus) and have a nice day.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download