CODEWITHSUNDEEP
mysqlsql
helpdesk
helpdesk

Reputation: 2084

MySQL Basic Commands

I am just a new beginner in MySQL and I wanted to write the a SELECT statement that returns customers IDs and customer names(alphabetical order) for customers who live in Indiana, Ohio,Michigan, and Illinois, and whose names begin with the letters A or B.

Here is the CUSTOMER_TBL structure using the DESCRIBE CUSTOMER_TBL;

    Name                         Null?                      Type
    -------------------------------------------------------------
   CUST_ID                      NOT NULL                   VARCHAR2(10)
   CUST_NAME                    NOT NULL                   VARCHAR2(30)
   CUST_ADDRESS                 NOT NULL                   VARCHAR2(20)
   CUST_CITY                    NOT NULL                   VARCHAR2(12)
   CUST_STATE                   NOT NULL                   CHAR(2)
   CUST_ZIP                     NOT NULL                   CHAR2(5)
   CUST_PHONE                                              NUMBER(10)
   CUST_FAX                                                NUMBER(10)

Here is my solution.I just need to know if I am correct. thanks

   SELECT CUST_ID,CUST_NAME FROM CUSTOMER_TBL
   WHERE IN('Indiana','Ohio','Ohio','Michigan','Illinois') AND WHERE LIKE(A% OR B%)
   ORDER BY CUST_ID,CUST_NAME

Upvotes: 0

Views: 204

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

You have couple of things wrong in your query:

  • You should first try it, not just asking is it correct or not.
  • When nesting conditions in the WHERE statement, don't repeat the where itslef.
  • You should mention the column you are trying to match against the condition.

So, your query should look like:

SELECT CUST_ID, CUST_NAME 
FROM CUSTOMER_TBL
WHERE CUSTOMER_City IN('Indiana','Ohio','Ohio','Michigan','Illinois') 
    AND ( CUST_NAME LIKE 'A%' OR CUST_NAME LIKE 'B%' )
ORDER BY CUST_ID,CUST_NAME

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272396

Your CUST_STATE is a CHAR(2) column so I assume that it contains state codes, not state names. So instead of Indiana, Ohio etc you should use state codes:

SELECT CUST_ID, CUST_NAME
FROM CUSTOMER_TBL
WHERE CUST_STATE IN ('IN','OH','MI','IL') AND (
    CUST_NAME LIKE 'A%' OR
    CUST_NAME LIKE 'B%'
)
ORDER BY CUST_NAME

Correct the state codes in case they are wrong.

Upvotes: 0

deadrunk
deadrunk

Reputation: 14153

SELECT CUST_ID,CUST_NAME FROM CUSTOMER_TBL
WHERE CUST_STATE IN('Indiana','Ohio','Ohio','Michigan','Illinois') AND (CUST_NAME LIKE 'A%' OR CUST_NAME LIKE 'B%') 
ORDER BY CUST_ID,CUST_NAME

Upvotes: 1

Related Questions