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!
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.
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.
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 _.
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?
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.