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