Answered step by step
Verified Expert Solution
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 Help
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started