Return the first and last names of all of the clients in the Clients table who have a credit rating less than or equal to 795 who live in the following states: ‘NY’,’IL’,’TN’,’PA’,’WI’,’VA’. Order...

1 answer below »


  1. Return the first and last names of all of the clients in the Clients table who have a credit rating less than or equal to 795 who live in the following states: ‘NY’,’IL’,’TN’,’PA’,’WI’,’VA’. Order your output alphabetically by last name and then by first name.



  2. How many Clients are currently over 70 years of age?



  3. How many orders have been placed by customers in Ohio?



  4. What was the total cost of line number 2 of order ID 1587? Round to 2 decimal places.



  5. If prices on all products were reduced by 20%, how many products would still cost more than $9.00?



  6. Return a list of the states and the average client credit rating per state for all states with an average credit rating greater than 675. Order your output from highest to lowest average rating. Round your credit ratings to the whole number.




  7. Create a list of all of the Occupations Names and their corresponding average credit rating for only those occupations that have more than 6 Clients working in them. Order your output from the highest average credit rating to the lowest. Round your average rating output to the whole number.



  8. Increase the cost of all velvet fabrics by 25%.



  9. Delete all the OrderLines from Orders that have an order status of canceled.



  10. Cancel any orders from Edwin Ortiz that are currently Order Status “P”. (Hint – be sure to consult the data dictionary to understand how to do this.)



  11. Bonus: Cancel the latest order from the client 868.

Answered Same DayMay 02, 2021

Answer To: Return the first and last names of all of the clients in the Clients table who have a credit rating...

Deepti answered on May 02 2021
134 Votes
QUERY 1:
SELECT FirstName, LastName
FROM Clients
WHERE CreditRating<=795 AND StateCode=”NY”
OR S
tateCode=”IL” OR StateCode=”TN”
OR StateCode=”PA” OR StateCode=”WI” OR StateCode=”VA”
ORDER BY LastName, FirstName
QUERY 2:
SELECT COUNT(ClientID)
FROM Clients
Where datediff(year,Curdate(),Clients.DateofBirth)>70
Query 3:
SELECT COUNT(OrderID) AS OrdersFromOHIO
FROM Orders, Clients
WHERE Orders.ClientID=Clients.ClientID AND StateCode=”OH”
GROUP BY OrderID
QUERY 4:
SELECT...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here