Include only customers residing in washington state

This preview shows page 1 - 3 out of 3 pages.

We have textbook solutions for you!
The document you are viewing contains questions related to this textbook.
Database Systems: Design, Implementation, & Management
The document you are viewing contains questions related to this textbook.
Chapter 7 / Exercise 36
Database Systems: Design, Implementation, & Management
Coronel/Morris
Expert Verified
List the average balance of customers by city. Include only customers residing in Washington state (WA).2.List the average balance and number of customers by city. Only include customers residing in Washington State (WA). Eliminate cities in the result with less than two customers.1.SELECT CustNo, CustFirstName, CustLastName, CustBalFROM Customer2.SELECT CustNo, CustFirstName, CustLastName, CustBalFROM CustomerWHERE CustState = 'CO'
We have textbook solutions for you!
The document you are viewing contains questions related to this textbook.
Database Systems: Design, Implementation, & Management
The document you are viewing contains questions related to this textbook.
Chapter 7 / Exercise 36
Database Systems: Design, Implementation, & Management
Coronel/Morris
Expert Verified
7/1/2017Solutions for the Extra Problems in Module 43.SELECT *FROM ProductWHERE ProdPrice > 50ORDER BY ProdMfg, ProdName4. The parentheses are necessary when mixing the logical AND and OR connectors.SELECT CustNo, CustFirstName, CustLastName, CustCity, CustBalFROM CustomerWHERE (CustCity = 'Denver' AND CustBal > 150) OR (CustCity = 'Seattle' AND CustBal > 300)5.Oracle solutions:SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastNameFROM OrderTbl, CustomerWHERE OrdState = 'CO' AND OrdDate BETWEEN '1-Jan-2013' AND '31-Jan-2013'AND OrderTbl.CustNo = Customer.CustNo;SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastNameFROM OrderTbl INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNoWHERE OrdState = 'CO' AND OrdDate BETWEEN '1-Jan-2013' AND '31-Jan-2013';MySQL solutionSELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastNameFROM OrderTbl, CustomerWHERE OrdState = 'CO' AND OrdDate BETWEEN '2013-01-01' AND '2013-01-31'AND OrderTbl.CustNo = Customer.CustNo;SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastNameFROM OrderTbl INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNoWHERE OrdState = 'CO' AND OrdDate BETWEEN '2013-01-01' AND '2013-01-31';6.SELECT CustCity, AVG(CustBal) AS AvgBalFROM CustomerWHERE CustState = 'WA'GROUP BY CustCity;7. SELECT CustCity, AVG(CustBal) AS AvgBal, COUNT(*) AS NumCustomersFROM CustomerWHERE CustState = 'WA'GROUP BY CustCity2
7/1/2017Solutions for the Extra Problems in Module 4HAVING COUNT(*) > 1;3

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture