SQL Problem Solving
Sun 24 Feb 2019

1. Searching the products which have duplicated names.

Product.select("name, count(*) as num").group(:name).having("num > 1")
SELECT
    name, COUNT(*)
FROM
    products
GROUP BY
    name
HAVING 
    COUNT(*) > 1

# Counting them only.
Product.select("(count(name) - count(distinct(name))) as num").first.num
SELECT COUNT(NAME) - COUNT(DISTINCT(NAME)) FROM PRODUCTS;

2. Searching the product which has the longest name (number of characters in the name).

Product.select("NAME, LENGTH(NAME) AS LEN").having("LEN = (SELECT MAX(LENGTH(NAME)) FROM PRODUCTS)").first.NAME
SELECT NAME, LENGTH(NAME) AS LEN FROM PRODUCTS
HAVING LEN = (SELECT MAX(LENGTH(NAME)) FROM PRODUCTS)

# In rails queries, NAME and name are different.

3. Query the list of CITY names starting with vowels (i.e., aeio, or u) from STATION. Your result cannot contain duplicates.

SELECT DISTINCT(CITY) FROM STATION WHERE LEFT(CITY,1) IN ('a','e','o','u','i');

4. SQL substring.

Product.select(:name).where("substring(name, length(name) - 2, 2) = 'me'")
# => #<ActiveRecord::Relation [#<Product id: nil, name: "HP OMEN">, #<Product id: nil, name: "Audio Note Tomei">, #<Product id: nil, name: "Belkin Wemo WiFi Smart Dimmer">]>

5. Calculating in SQL. Challenge.

SELECT 
CASE 
    WHEN A + B <= C OR A + C <= B OR B + C <= A THEN 'Not A Triangle'
        WHEN A = B AND B = C THEN 'Equilateral'
        WHEN A = B OR A = C OR B = C THEN 'Isosceles'
        ELSE 'Scalene'
    END
FROM TRIANGLES;

(to be continued...)