SQL: 4 conditions combined with OR

Question

Question:

Define the names of all ships in the Ships table that match the combination of at least four criteria from the following list: numGuns = 8 bore = 15 displacement = 32000 type = bb launched = 1915 class = Kongo country = USA.

I found the answer for this exercise.

Answer:

SELECT s.name from ship s,classes c WHERE s.class=c.class AND ((numGuns = 8 AND bore = 15 AND displacement = 32000 AND type = 'bb') OR (numGuns = 8 AND bore = 15 AND displacement = 32000 AND launched = 1915) OR (numGuns = 8 AND bore = 15 AND displacement = 32000 AND c.class = 'Kongo') OR (numGuns = 8 AND bore = 15 AND displacement = 32000 AND country = 'USA') OR (numGuns = 8 AND bore = 15 AND type = 'bb' AND launched = 1915) OR (numGuns = 8 AND bore = 15 AND type = 'bb' AND c.class = 'kongo') OR (numGuns = 8 AND bore = 15 AND type = 'bb' AND country = 'USA') OR (numGuns = 8 AND bore = 15 AND launched = 1915 AND c.class = 'Kongo') OR (numGuns = 8 AND bore = 15 AND launched = 1915 AND country = 'USA') OR (numGuns = 8 AND bore = 15 AND c.class = 'Kongo' AND country = 'USA') OR (numGuns = 8 AND displacement = 32000 AND type = 'bb' AND launched = 1915) OR (numGuns = 8 AND displacement = 32000 AND type = 'bb' AND c.class = 'kongo') OR (numGuns = 8 AND displacement = 32000 AND type = 'bb' AND country = 'USA') OR (numGuns = 8 AND displacement = 32000 AND launched = 1915 AND c.class = 'Kongo') OR (numGuns = 8 AND displacement = 32000 AND launched = 1915 AND country = 'USA') O 

My question is:

Is there any other easy way to check the conditions.

Answer 1

Listing from boolean to integer gives 0 or 1:

 select s.name from ship s inner join classes c using (class) where (numguns = 8)::int + (bore = 15)::int + (displacement = 32000)::int + (type = 'bb')::int + (launched = 1915)::int + (class = 'Kongo')::int + (country = 'USA')::int >= 4 

Answer 2

You can use CASE WHEN statements to count the number of matching columns for each record, and then wrap this query to only get records with 4 or more corresponding columns.

 SELECT t.name FROM ( SELECT s.name, CASE WHEN s.numGuns = 8 THEN 1 ELSE 0 END AS c1, CASE WHEN s.dbore = 15 THEN 1 ELSE 0 END AS c2, CASE WHEN s.displacement = 32000 THEN 1 ELSE 0 END AS c3, CASE WHEN s.type = 'bb' THEN 1 ELSE 0 END AS c4, CASE WHEN s.launched = 1915 THEN 1 ELSE 0 END AS c5, CASE WHEN c.class = 'Kongo' THEN 1 ELSE 0 END AS c6, CASE WHEN s.country = 'USA' THEN 1 ELSE 0 END AS c7 FROM ship s INNER JOIN classes c ON s.class = c.class ) t WHERE (t.c1 + t.c2 + t.c3 + t.c4 + t.c5 + t.c6 + t.c7) >= 4 

Answer 3

Strictly in terms of the predicate, yes, for example, the last 5 predicates:

 OR (numGuns = 8 AND displacement = 32000 AND type = 'bb' AND launched = 1915) OR (numGuns = 8 AND displacement = 32000 AND type = 'bb' AND c.class = 'kongo') OR (numGuns = 8 AND displacement = 32000 AND type = 'bb' AND country = 'USA') OR (numGuns = 8 AND displacement = 32000 AND launched = 1915 AND c.class = 'Kongo') OR (numGuns = 8 AND displacement = 32000 AND launched = 1915 AND country = 'USA') 

may be:

 OR ((numGuns = 8 AND displacement = 32000) AND ((type = 'bb' AND launched = 1915) OR (type = 'bb' AND c.class = 'kongo') OR (type = 'bb' AND country = 'USA') OR (launched = 1915 AND c.class = 'Kongo') OR (launched = 1915 AND country = 'USA')))