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

Time series residual analysis - Shear stress in beams - Bread by samuel menashe analysis - 4.380 kg in pounds and ounces - My diet analysis project - Using pairs of opposites, such as helena and hermia, is called - 2845 kj to calories - Lemon v kurtzman definition - Are you Interested In Learning how to Write A Effective Personal Statement For Admission Board? - Microsoft and Google team up to make windows Spellcheck for Chrome and Edge - Symphony new mobile 2018 - Cisco erp implementation case study - Calcium nitrate flame test - Who inhabited iceland first - Mcknight's physical geography third california edition pdf - Griffith university admissions contact - Ableism Reflection Essay - Ronalds cottage south uist - What is the relationship between sentence fluency and conventions - Econ Project - The experience machine essay - War of the worlds study guide - Finance Dissertation Topics - Randall corporation plans to borrow - Bp texas city explosion - Dui checkpoints tonight illinois 2019 - What does fx mean in maths frequency table - Incidental and intentional learning - Informative Speech Outline Rough Draft - Safeassign odu - Cwv origins powerpoint - Christian Ethics Essay - Takeuchi tb290 lifting chart - Australian government cloud policy - What happened to brainetics - Robert stephens geek squad net worth - Technical Writing in Criminal Justice - Chapter 7 - Differences between samurai and knights training - Musyokiones - LV outlet - Describe your mother and father's parenting style - What are exclusive concurrent and residual powers - My cousin vinny essay - Walmart global statement of ethics - Project management a managerial approach 9th edition instructor's resource guide - What happened to the dallas sheep rancher worksheet answers - Brainpop carbon cycle worksheet answers - What substance in msa confers selectivity? why? - Operations management - Chris21 self service login - Help - Examples of opaque objects - Fundamentals of management by ricky griffin pdf - English-Romantic novels - Ato agent fast key codes - Kahoot make your own - Seven areas of learning - Self and Others- APA format for in-text citations and ( at least 2 references) - Summary of the movie too big to fail - Digital signage white paper - I need 4000 words on an article - Write a 1 page response to the text answering the following questions: - Jay z song cry live jaguar wright - It business requirements document - Steven b belkin - Lección 1 lesson test vhlcentral answers - The tensile strength of portland cement is being studied - Parton v milk board - Perception and critical thinking powerpoint - Commercial livestock farming definition - Which of the following has the highest viscosity - 4 ps marketing mix - What were aztec houses made of - Weeek-4 - Types of secondary industry - What does the word christian mean - Which of the following best defines television genres - Legal glossary english spanish - Elevation of a shape - Olet psychology of crime - 11b rutledge crescent quakers hill - Post- tania - Performance appraisal - Cisco proximity app download - Timberly construction negotiates a lump sum purchase of several assets - Due tomorrow - Reflection paper on business continuity and disaster recovery plan. - Billy elliot ending scene - Calcium carbonate + hydrochloric acid - Leadership MG401 - Mali to memphis an african american odyssey - IT SOURCING AND DEVELOPMENT IN A GLOBAL ECONOMY - Safety assessment test answers - Chapter 2 The Cultural Context - Advanced Pathophysiology. Response to a discussion post - Find the best point estimate of the population proportion p - NEED IN 12 HOURS or LESS - Danebridge medical centre northwich - Change management discussion questions - Coastal landforms erosion and deposition