SQL Problem Solving

Posted on 05/07/2022

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

0