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., a, e, i, o, 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...)