Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Write a select statement that returns these columns from the invoices table

18/12/2020 Client: saad24vbs Deadline: 14 Days

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.



Applied Sciences

Architecture and Design

Biology

Business & Finance

Chemistry

Computer Science

Geography

Geology

Education

Engineering

English

Environmental science

Spanish

Government

History

Human Resource Management

Information Systems

Law

Literature

Mathematics

Nursing

Physics

Political Science

Psychology

Reading

Science

Social Science

Home

Blog

Archive

Contact

google+twitterfacebook

Copyright © 2019 HomeworkMarket.com

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

University Coursework Help
Helping Hand
Top Essay Tutor
Homework Guru
Writer Writer Name Offer Chat
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$122 Chat With Writer
Helping Hand

ONLINE

Helping Hand

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$120 Chat With Writer
Top Essay Tutor

ONLINE

Top Essay Tutor

I have more than 12 years of experience in managing online classes, exams, and quizzes on different websites like; Connect, McGraw-Hill, and Blackboard. I always provide a guarantee to my clients for their grades.

$125 Chat With Writer
Homework Guru

ONLINE

Homework Guru

Hi dear, I am ready to do your homework in a reasonable price and in a timely manner.

$122 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Sample nursing family case study - Ap psychology practice test - 3x 15 2x 24 - Entrepreneurship Course Homework - Thermo king alarm code 89 - Becoming a coach requires a shift from old behaviors - Trade - Introduction to modern behaviorism - Ashworth preschool and kindergarten - Wholistic applications of counselling with the aging in dialogue with pastoral care concerns: - WHITE LIKE ME - Sed regex cheat sheet - Kaskas auto sales exeter ca - Assignment - Slope intercept form - Maths - Tv guide melbourne tonight - Td o connell wichita ks - Application for consent orders kit - What is Minecraft MOD APK? - Ib biology lab report template - Royal roads university admissions - Bailey - Discussion #8 - Maths - Assignment 1 - Project management audit checklist - Fighting dog breeds book - Starbucks human resource management strategy - Matlab state space simulation - Quant 8 - Pleasantville discussion questions and answers - What is the complement of 60 degrees - Green cross code uk - Lab Assignment: Differential Diagnosis for Skin Conditions - Signature Assignment: Financial Plan - Pte test centre near me - Data & file structures - Palo alto ad group mapping - I need help with my ethical approval - Baekjul black belt academy - Compare and contrast piaget and bruner - Discussion - Strathfield car radio adelaide - Claudine trillo jason webb split - In the jailhouse now karaoke - Internal rhyme poems by famous poets - Discussion 2: Preparation for Licensure - In a closed economy national saving equals - Eastman kodak future - Chemistry - Yes - The strange case of anna stubblefield - Ngha.med.sa login - For don m banned line by line analysis - 4 questions 350 word each - Relationship between centripetal force and velocity - The future of nursing leading change advancing health report recommendations - Jblcourses - Nab merchant services help - To kill a mockingbird chapter themes - Inferential research and statistics project part 1 - Prepare a summary journal entry to record raw materials used - Pennine valleys federation of homing societies - Initial construction control document 9th edition - Escience lab 2 answers - The cat sat on the mat poem - Official beer pong rules - Economics study design vce - Light and optics worksheet - Virtual museum project - Nurse practitioner scholarships canada - Wireless security - Case Analysis - 30mm internal diameter pipe - Family reunion committee responsibilities - Conventional representation of an element - The vietnam wars marilyn young sparknotes - Essentials of business communication 11th edition chapter 1 - Writing with the method: Narration - Doing the truth in love summary - 05.04 understanding 20th century poetry - Municipal bond index mbi futures - Fossil meaning in latin - What is the yield to maturity for the bond issued by xenon, inc.? - Sherman alexie on the amtrak from boston to new york - Creating a company culture for security design document - Hound of the baskervilles extract - Reverse return piping diagram - Udacity digital marketing nanodegree free download - Power world simulator introduction - Time warner cable mission statement and goals - Develop workplace policy and procedures for sustainability assessment 1 - Converter watt to amp - Mitel 5320 headset mode - Preparation of benzoic acid from benzonitrile lab report - Comprehensive soap note example nurse practitioner - Answer discussion question in 175 words or less - James lull hegemony - Nh3 h20 acid base - Vortex spa control panel manual