CREATE TABLE Ref_Characteristic_Types (
    characteristic_type_code TEXT PRIMARY KEY,
    characteristic_type_description TEXT
);
CREATE TABLE Ref_Colors (
    color_code TEXT PRIMARY KEY,
    color_description TEXT
);
CREATE TABLE Ref_Product_Categories (
    product_category_code TEXT PRIMARY KEY,
    product_category_description TEXT,
    unit_of_measure TEXT
);
CREATE TABLE Characteristics (
    characteristic_id NUMBER PRIMARY KEY,
    characteristic_type_code TEXT,
    characteristic_data_type TEXT,
    characteristic_name TEXT,
    other_characteristic_details TEXT,
    FOREIGN KEY ("characteristic_type_code") REFERENCES Ref_Characteristic_Types("characteristic_type_code")
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    color_code TEXT,
    product_category_code TEXT,
    product_name TEXT,
    typical_buying_price TEXT,
    typical_selling_price TEXT,
    product_description TEXT,
    other_product_details TEXT,
    FOREIGN KEY ("color_code") REFERENCES Ref_Colors("color_code"),
    FOREIGN KEY ("product_category_code") REFERENCES Ref_Product_Categories("product_category_code")
);
CREATE TABLE Product_Characteristics (
    product_id NUMBER,
    characteristic_id NUMBER,
    product_characteristic_value TEXT,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("characteristic_id") REFERENCES Characteristics("characteristic_id")
);

Question: Give the color description that is least common across products.
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) ASC LIMIT 1

Question: What is the characteristic name used by most number of the products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1

Question: Return the name of the characteristic that is most common across all products.
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1

Question: What are the names, details and data types of the characteristics which are never used by any product?
SQL: SELECT characteristic_name ,  other_characteristic_details ,  characteristic_data_type FROM CHARACTERISTICS EXCEPT SELECT t1.characteristic_name ,  t1.other_characteristic_details ,  t1.characteristic_data_type FROM CHARACTERISTICS AS t1 JOIN product_characteristics AS t2 ON t1.characteristic_id  =  t2.characteristic_id

Question: Give the names, details, and data types of characteristics that are not found in any product.
SQL: SELECT characteristic_name ,  other_characteristic_details ,  characteristic_data_type FROM CHARACTERISTICS EXCEPT SELECT t1.characteristic_name ,  t1.other_characteristic_details ,  t1.characteristic_data_type FROM CHARACTERISTICS AS t1 JOIN product_characteristics AS t2 ON t1.characteristic_id  =  t2.characteristic_id

Question: What are characteristic names used at least twice across all products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name HAVING count(*)  >= 2

Question: Give the names of characteristics that are in two or more products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name HAVING count(*)  >= 2

Question: How many colors are never used by any product?
SQL: SELECT count(*) FROM Ref_colors WHERE color_code NOT IN ( SELECT color_code FROM products )

Question: Count the number of colors that are not used in any products.
SQL: SELECT count(*) FROM Ref_colors WHERE color_code NOT IN ( SELECT color_code FROM products )