MySQL Basic Commands

Question

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

"Where " clause is completely wrong , the query is simple learn the syntax. i just u to learn from here "w3schools.com/sql/sql_where.asp". It will be better than giving u the answer — gout
– gout, Commented Apr 9, 2012 at 11:59
... and if you need to learn something about w3schools.com itself, you need to go here: w3fools.com — Salman Arshad
– Salman Arshad, Commented Apr 9, 2012 at 12:04
first of all clear your basics, you missed to write column name after where — Ankit Sharma
– Ankit Sharma, Commented Apr 9, 2012 at 12:06

deadrunk · Accepted Answer · 2012-04-09 11:57:25Z

1

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

answered Apr 9, 2012 at 11:57

deadrunk

14.2k4 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salman Arshad · Accepted Answer · 2012-04-09 11:58:18Z

0

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.

answered Apr 9, 2012 at 11:58

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

2 Comments

Salman Arshad Over a year ago

@deadrunk: I noticed the typo (if that is what you were referring to) and fixed it.

deadrunk Over a year ago

yeah, i was talking about the first like expression

Mahmoud Gamal · Accepted Answer · 2012-04-09 11:58:42Z

0

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

answered Apr 9, 2012 at 11:58

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

Collectives™ on Stack Overflow

MySQL Basic Commands

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related