Write a select statement that returns three columns

1.1 MySQL and MySQL Workbench install

1. Go to www.mysql.com and download and install the Community Edition of MySQL. Note that you do not have to register to complete the download.

2. Go to www.mysql.com and download and install MySQL Workbench. Both of these software products are available for Windows and Mac OS X.

3. Start up the database using the MySQL Workbench tool.

4. To show that you have completed these steps, perform a screen capture and upload it to this drop box.

5. Download the create_databases.sql script from the create_databases.sql.zip file and then open up the script in MySQL Workbench using the second icon from the left.

6. Then execute the script using the lightning bolt icon. This will create many of the tables that you will need for the rest of the course.

7. Complete the following chapter 2 exercises:

1. Make sure the MySQL Workbench and open a connection for the root user.

1. Check whether the MySQL server is running. If it isn't, start it. When you're done, close the Startup/Shutdown window.

2. Use MySQL Workbench to review the Account Payable (AP) database

1. In the Navigator window, expand the node for the AP database so you can see all of the database objects it contains.

2. View the data for the Vendors and Invoices tables.

3. Navigate through the database objects and view the column definitions for at least the Vendors and Invoices tables.

3. Use MySQL Workbench to enter and run SQL statements

1. Double-click the AP database to select it. When you do that, MySQL Workbench should display the database in bold.

2. Open a SQL Editor tab. Then, enter and run this SQL statement: SELECT vendor_name FROM vendors

3. Delete the e at the end of vendor_name and run the statement again. Note the error number and the description of the error.

4. Open another SQL Editor tab. Then enter and run this statement:

SELECT COUNT(*) AS number_of_invoices,

SUM(invoice_total) AS grad_invoice_total

FROM invoices

2.1 Chapter 4 Exercises
Submit proof of completion of the following Chapter 4 exercises to the dropbox:

4. Write SELECT statement that returns these five columns:

vendor_name

The vendor_name column from the Vendors table

invoice_date

The invoice_date column from the Invoices table

invoice_number

The invoice_number column from the Invoices table

li_sequence

The invoice_sequence column from the Invoice_Line_Items table

li_amount

The line_item_amount column from the Invoice_Line_Items table

Use aliases for the tables. This should return 118 rows

Sort the final result set by vendor_name, invoice_date, invoice_number and invoice_sequence.

5. Write SELECT statement that returns three columns:

vendor_id

The vendor_id column from the Vendors table

vendor_name

The vendor_name column from the Vendors table

contact_name

A concatenation of the vendor_contact_first_name and vendor_contact_last_name columns with a space between

Return one row for each vendor whose contact has the same last name as another vendor's contact. This should return 2 rows. Hint: Use a self-join to check that the vendor_id columns aren't equal but the vendor_contact_last_name columns are equal.

Sort the result set by vendor_contact_last_name.

6. Write a SELECT statement that returns these three columns:

account_number

The account_number column from the General_Ledger_Accounts table

account_description

The account_description column from the General_Ledger_Accounts table

invoice_id

The invoice_id column from the Invoice_Line_Items table

Return one row for each account number that has never been used. This should return 54 rows. Hint: Use an outer join and only return rows where the invoice_id column contains a null value.

Remove the invoice_id column from the SELECT clause.

Sort the final result set by the account_number column.

3.1 Chapter 5 and 6 Exercises
Submit proof of completion of the following exercises from Chapter 5 to the dropbox.

To test whether a table has been modified correctly as you do these exercises, you can write and run an appropriate SELECT statement. Or, when you're usingMySQL Workbench, you can right-click on a table name in the Object Browser window and select the Select Rows - Limit 1000 command to display the data for the table in a Result tab. To refresh the data in this tab after modifying the table data, click the Refresh button in the toolbar at the top of the tab.

1. Write an INSERT statement that adds this row to the Terms table:

terms_id:

6

terms_description:

Net due 120 days

terms_due_days:

120

Use MySQL Workbench to review the column definitions for the Terms table, and include a column list with the required columns in the INSERT statement.

2. Write an UPDATE statement that modifies the row you just added to the Terms table. This statement should change the terms_description column to "Net due 125 days", and it should change the terms_due_days column to 125.

3. Write a DELETE statement that deletes the row you added to the Terms table in exercise 1.

Submit proof of completion of the following exercises from Chapter 6 to the dropbox:

1. Write a SELECT statement that returns one row for each vendor in the Invoices table that contains these columns:

The vendor_id column from the Vendors table The sum of the invoice_total columns in the Invoices table for that vendor

This should return 34 rows.

3. Write a SELECT statement that returns one row for each vendor that contains three columns:

The vendor_name column from the Vendors table The count of the invoices in the Invoices table for each vendor The sum of the invoice_total columns in the Invoices table for each vendor

6. Write a SELECT statement that answers this questions: What is the total amount invoiced for each general ledger account number? Return these columns:

The account number from the Invoice_Line_Items table The sum of the line item amounts from the Invoice_Line_Items table

Use the WITH ROLLUP operator to include a row that gives the grand total. This should return 22 rows.

Note: Once you add the WITH ROLLUP operator, you may need to use MySQL Workbench's Execute SQL Script button instead of its Execute Current Statement button to execute this statement.

4.1 Code of Ethics
Submit a 2 page paper responding to this article:

http://www.dba-oracle.com/t_oracle_dba_code_of_ethics.htm

Address the adequacy of this code of ethics. What ethical framework drives this proposed code of ethics? (Recall the various ethical approaches from your CSC 510 or 810 class). To what extent does each of the following ethical theories inform this proposed code?

· moral relativism

· utilitarianism

· deontological ethics

· virtue ethics

Are there additional dimensions that the doctrine of vocation brings into the discussion? See http://blogs.lcms.org/2011/technology-vocation-2-2011

5.1 Chapter 9 and 12 Exercises
Submit proof of completion of the following exercises from Chapter 9 to the dropbox.

1. Write a SELECT statement that returns these columns from the Invoices table:

The invoice_total column A column that uses the ROUND function to return the invoice_total column with 1 decimal digit A column that uses the ROUND function to return the invoice_total column with no decimal digits

2. Write a SELECT statement that returns these columns from the Date_Sample table in the EX database:

The start_date column A column that uses the DATE_FORMAT function to return the start_date column with its month name abbreviated and its month, day, and two-digit year separated by slashes A column that uses the DATE_FORMAT function to return the start_date column with its month and day returned as integers with no leading zeros, a two-digit year, and all date parts separated by slashes A column that uses the DATE_FORMAT function to return the start_date column with only the hours and minutes on a 12-hour clock with an am/pm indicator A column that uses the DATE_FORMAT function to return the start_date column with its month returned as an integer with no leading zeros, its month, day and two-digit year separated by slashes and its hours and minutes on a 12-hour clock with an am/pm indicator

3. Write a SELECT statement that returns these columns from the Vendors table:

The vendor_name column The vendor_name column in all capital letters The vendor_phone column A column that displays the last four digits of each phone number

When you get that working right, add the columns that follow to the result set. This is more difficult because these columns require the use of functions within functions.

The vendor_phone column with the parts of the number separated by dots, as in 555.555.5555 A column that displays the second word in each vendor name if there is one and blanks if there isn't

Submit proof of completion of the following exercises from Chapter 12 to the dropbox.

1. Create a view named open_items that shows the invoices that haven't been paid.

This view should return four columns from the Vendors and Invoices tables:

vendor_name, invoice_number, invoice_total, and balance_due (invoice_total - payment_total - credit_total).

A row should only be returned when the balance due is greater than zero, and the rows should be in sequence by vendor_name.

3. Create a view named open_items_summary that returns one summary row for each vendor that has invoices that haven't been paid.

Each row should include vendor_name, open_item_count (the number of invoices with a balance due), and open_item_total (the total of the balance due amounts)

The rows should be sorted by the open item totals in the descending sequence.

6. Write an UPDATE statement that changes the address for the row with a vendor ID of 4 so the suite number (Ste 260) is stored in the vendor_address2 column instead of the vendor_address1 column.

6.1 Chapter 13 and 14 Exercises
Submit proof of completion of the following exercise from Chapter 13 to the dropbox.

1. Write a script following the same structure as figure 13-1 that creates and calls a stored procedure named test. This stored procedure should declare a variable and set it to the count of all rows in the Invoices table that have a balance due that's greater than or equal to $5,000. Then, the stored procedure should display a result set that displays the variable in a message like this:

3 invoices exceed $5,000.

Submit proof of completion of the following exercise from Chapter 14 to the dropbox.

1. Write a script that creates and calls a stored procedure named test. This procedure should include a set of three SQL statements coded as a transaction to reflect the following change: United Parcel Service has been purchased by Federal Express Corporation and the new company is named FedUP. Rename one of the vendors and delete the other after updating the vendor_id column in the Invoices table.

If these statements execute successfully, commit the changes. Otherwise, roll back the changes.

7.1 Chapter 15 and 16 Exercises

Submit proof of completion of the following exercise from Chapter 15 to the dropbox.

1. Write a script that creates and calls a stored procedure named insert_glaccount. First, code a statement that creates a procedure that adds a new row to the General_Ledger_Accounts table in the AP schema. To do that, this procedure should have two parameters, one for each of the two columns in this table. Then, code a CALL statement that tests this procedure. (Note that this table doesn't allow duplicate account descriptions.)

Submit proof of completion of the following exercise from Chapter 16 to the dropbox.

2. Create a trigger named invoices_after_update. This trigger should insert the old data about the invoice into the Invoices_Audit table after the row is updated. Then, test this trigger with an appropriate UPDATE statement. If the Invoices_Audit table doesn't exist, you can use the code shown in figure 16-3 to create it.

8.1 Summary of learning
Submit a 3-page course summary describing and summarizing what you have learned in this course. Your paper should include two paragraphs on ethical use of databases and administrative rights.