Sub-selects in SQLite

Question

The Problem

I was recently completing a technical test, which required me to carry out a rather convoluted SQL query, and I'd like to know, ahead of any feedback I might receive, whether the answer I gave was correct, and how it might be improved.

Here's my paraphrase of the problem:

Suppose we have a website selling hats, for which we want to build a database. Each type of hat has a size, price, etc. Each type of hat can also fall into none, one or more categories, and these categories can themselves be public or private.

Do the following:

1. Write pseudo-SQL code creating the tables necessary to model the above.
2. Write a query which will return the records for types of hats which belong to at least 5 public categories.

My Attempt

Since the bulk of my experience is with SQLite, I tackled this from an SQLite point of view, and I wrote this in a comment in my answer.

Regarding point (1) above, I created three tables: Hat, Category and BelongsToCategory, with the last being a join table for the first two. I made public a column of the Category table, of INTEGER type: 0 for false, 1 for true.

As for point (2), this is my query:

SELECT *
FROM Hat
WHERE
    (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = 1))
    >= 5

Testing My Attempt

I tested my attempt in the following fashion:

• I made my own .db file.
• I opened said file using the Database Browser for SQLite.
• I created my tables, as above.
• I added data to those tables, as follows:
  • I added three new records to the Hat table: hat1, hat2 and hat3.
  • I added six new records to the Category table: cat1 to cat6, where cat1 to cat5 were public, and cat6 was private.
  • I added records to the BelongsToCategory table, such that hat1 belonged to categories cat1 to cat5, hat2 belonged to cat1 only, and hat3 belonged to categories cat1 to cat4 and also to cat6.
• I ran the above SQL code.

When I ran the above SQL code, it returned the record for hat1. Job done! Not so fast... When I tried playing around with it a little, changing the 5 to a 4, or a 3, 2, etc, I kept getting the record for hat1, and nothing else. It was only when I got to 1 that hat3 started popping up, and I never saw hat2.

What's going here? Is there something wrong with my query? Or is this a bug in the SQLite browser?

What I Want to Know

• Is the above SQL query correct?
• How might it be improved?

Answer 1

I don't believe that there is anything wrong with your query (I've tested it and it works as expected). As such I believe you might not be inserting the data as expected.

If you add the count sub query as an additional column to the main query and use >= 0 you can see the counts and see what is going on count wise

e.g. :-

SELECT *, (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true)) AS cat_count
FROM Hat
WHERE
    (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true))
    >= 0
;

The following was used to test the above :-

PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS BelongsToCategory;
DROP TABLE IF EXISTS Hat;
DROP TABLE IF EXISTS Category;
CREATE TABLE IF NOT EXISTS Hat (code INTEGER PRIMARY KEY, hattype TEXT, hatprice REAL, hatsize REAL, hatetc TEXT);
CREATE TABLE IF NOT EXISTS Category (code INTEGER PRIMARY KEY, category TEXT, public INTEGER);
CREATE TABLE IF NOT EXISTS BelongsToCategory (
    hat INTEGER REFERENCES Hat(code) ON DELETE CASCADE ON UPDATE CASCADE
    , category INTEGER REFERENCES Category(code) ON DELETE CASCADE ON UPDATE CASCADE
    , PRIMARY KEY(hat,category)
    )   
;

INSERT INTO Category VALUES(1,'cat1',true),(2,'cat2',true),(3,'cat3',true),(4,'cat4',true),(5,'cat5',true),(6,'cat6',false);
INSERT INTO Hat VALUES(1,'hat1',100,7,'other'),(2,'hat2',90,6,'other'),(3,'hat3',95,5,'other'),(4,'hat4',110,8,'other'),(5,'hat5',120,8,'other');
INSERT INTO BelongsToCategory VALUES
    (1,1),(1,2),(1,3),(1,4),(1,5),(1,6)
    ,(2,1)
    ,(3,1),(3,2),(3,3),(3,4)
    ,(5,6),(5,1)
;

SELECT *, (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true)) AS cat_count
FROM Hat
WHERE
    (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true))
    >= 0
;

The result of the above (using >=0) :-

(using >=5) :-

(using >=4) :-