Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I have an error for number 7 and 8. Please Help Using the Northwind Database, create the following MySQL queries. 1. List the CustomerID, CompanyName

I have an error for number 7 and 8. Please Helpimage text in transcribed

Using the Northwind Database, create the following MySQL queries. 1. List the CustomerID, CompanyName and complete address for all customers that have a CustomerID that starts with the letter J,K,L,M or N. Concatenate the customer's Address, City, Region, PostalCode, and Country in one column named "Complete Address". Ensure to add spaces between each one. Sort the list in alphabetical order by CompanyName. (Hints: Since the Region and PostalCode columns used in the complete address may have a Null value, vou must use the IFNULL function for these columns. Use a replacement value of an empty string. If you do not, the result of the CONCAT function will be Null. See pages 286-287 in textbook. Also, see the use of the REGEXP operator on pages 100-101.) Here is an example of using the IFNULL function with the CONCAT function. In this example if SampleColumn is Null, the IFNULL function will return as space: CONCAT(IFNULL(SampleColumn," "), ", AnotbenColumn) 2. List each HOURLY employee's first and last name and their estimated yearly salary. Use 2080 as the number of hours worked annually. Concatenate the employee last and first names separated by a comma. Name the columns appropriately. Display the Yearly Salary with zero decimal places. Sort in alphabetical order by LastName. (Hint. This query will require the use of two tables.) 3. List the ten ProductNames with the highest inventory values (UnitPrice x UnitsInStock). Name the columns "Products" and "Inventory Value". Round the inventory values to two decimal places. (Hint: When referring to column aliases in the ORDER BY clause, use tick marks instead of quotation marks.) 4. List all customers with no fax number on record. Include the CompanyName, ContactName, Country and Phone. ORDER BY Country. 5. List all products and the supplier for every product that have units on order. Include columns that show the ProductName, supplier's CompanyName and number of units on order. Sort the list first by supplier name and then by product name. (Hint. This will require two tables.) Table: suppliers 6. List all products that have never been ordered. Show the ProductID and the ProductName in your results. Use a subquery in the WHERE clause. 7. Northwind would like to know the total compensation paid to each HOURLY employee in 2020. List the employee's last name, Commission Paid, Estimated Salary (see \#2 above) and the Total Compensation (Commission Paid + Estimated Salary). Commission is earned on the pre-discount total of all orders completed by that employee multiplied by the commission rate of 5%. Use appropriate column names. Sort in descending order by Total Compensation. (Hints. This query will require four tables. Use a subquery in the SELECT clause in order to get 'Total Compensation'. When referring to column aliases in the ORDER BY clause, use tick marks instead of quotation marks.) 8. List the shippers and the total number of orders each shipped and the total freight charges for these orders. There should be four columns named "Shipper ID", Shipper Name", "Number of Orders" and Total Freight Cost. (Hint: Two tables are required.)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Inductive Databases And Constraint Based Data Mining

Authors: Saso Dzeroski ,Bart Goethals ,Pance Panov

2010th Edition

1489982175, 978-1489982179

More Books

Students also viewed these Databases questions

Question

How to reverse a Armstrong number by using double linked list ?

Answered: 1 week ago