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

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

yeah, homework, but a couple of valiant attempts, so ...

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

neat, eh?

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

losing, not loosing

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

categories in any order? how about one per row?

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

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

urquel, brilliant!!

if i'm marking the test, you get full marks for that answer

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?

Don't forget "Cooking Children" category.... you would get one hit.... Hansel and Gretel

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

um, i would argue that UNION is a logical operator

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.

well, that's why i suggested LIKE 'c%i%n%'

it's less non-deterministic

i.e. better