Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2003
    Location
    san jose, CA
    Posts
    68

    Unanswered: using wildcard character in select statement

    Dear forums members:

    I am doing a SQL select statement exercise. the table is books which has ISBN, title, pubdate, pubid, category columns in it.

    The question ask me to select all books that are in children and cooking category. (using wildcard characters % and _ are suggested for this problem, no logical operators: and, or, not are allowed in this exercise)

    I tried the following way to generate query.

    a)select * from books
    where category like 'C%N'; --> only catched books in children category

    b) select * from books
    where category like '%NG'; --> only catched books in cooking category

    I am kind of out of ideas here. Please give me some suggestion on how to use combination of wildcard characters to complete this problem. Thanks!

    sjgrad03
    1-17-05

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is this problem from a book, a class, or something different? I suspect that some important pieces are missing from your problem specification, and I'm wondering if I can find the original problem specification either online or published in a book somewhere.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, homework, but a couple of valiant attempts, so ...

    ... where category like 'c%i%n%'

    neat, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What if Chicken is a category, or if the categories can be in any order? I've seen a spec something like this a couple of years ago for a university level class, but I can't remember them very well.

    That's what happens when you get old, like me... You start loosing details that you don't use!

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    losing, not loosing

    actually, like 'c%i%n%' will find chicken as well

    categories in any order? how about one per row?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One category per row would be kinda-sorta normalized. That would make things easy!

    The problem spec that I saw was a comma delimited list, where you had to construct a wildcard pattern to cope with the list. The problem was part of a suite of related problems, and it was intended for very early in a university class, as a springboard to the concept of first normal form.

    The series of problems was really well designed, and helped tie the course together from a practical/real world perspective. The problems helped the students understand the real world implications of good database design really well. The guy that wrote the curriculum was named Keith, but I can't remember his last name or University, although I think he was either extreme NorthEast or MidWest United States. I'll have to see if I can coax my feeble gray cells into coughing up more contact information.

    -PatP

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    How about this:

    select * from books


    It will bring back all results, therfore satisfying the requrement "all books that are in children and cooking category". You will also get books that are not in the children and cooking category, but you may get that anyway if you use the % and _.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    urquel, brilliant!!

    if i'm marking the test, you get full marks for that answer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose it depends on the precise wording of the question, or beeing pedantic the precise interpreation you have put on the question
    is it ALL childrens books AND ALL cooking books or is it all Childrens Cooking Books
    as ORs are excluded
    wonder if the SQL engine will support POSIX style regular expressions?

  10. #10
    Join Date
    Aug 2004
    Posts
    330
    Don't forget "Cooking Children" category.... you would get one hit.... Hansel and Gretel

  11. #11
    Join Date
    Aug 2003
    Location
    san jose, CA
    Posts
    68

    thanks for the help!

    Hello PatP, urquel, r937:

    Thanks for the replying. I solved problem by using union operation for this problem.

    The original problem states: use a search pattern to list all books in the children and cooking categories. Do not use any logical operators in the where clause.

    My solution is: select * from books
    where category like '%N'

    union

    select * from books
    where category like '%G'

    This union operation wil list all the books in children and cooking category for this exercise.

    P.S The books I am using is "Oracle 9i: SQL with an introduction to PL/SQL"

    sincerely,
    sjgrad03
    1-18-05

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, i would argue that UNION is a logical operator
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2004
    Posts
    330
    This %N OR(Logical equivalent to UNION) %G solution is NON DETERMINISTIC. In other words. You will get rows back for "CHILDREN" and for "COOKING" but also for "MEN", "WOMEN" and "GARDENING" and no results for "Children" or "Cooking" (Assuming that your DBMS is case-sensetive). In other words, you are better off using SELECT * FROM BOOKS; At least you know that all rows will be returned. Maybe if you knew the range and domain of the CATEGORY column, you could better formulate an answer.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, that's why i suggested LIKE 'c%i%n%'

    it's less non-deterministic

    i.e. better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •