human
Schema:
chip_model (Model_name, Launch_year, RAM_MiB, ROM_MiB, Slots, WiFi, Bluetooth)
screen_mode (Graphics_mode, Char_cells, Pixels, Hardware_colours, used_kb, map, Type)
phone (Company_name, Hardware_Model_name, Accreditation_type, Accreditation_level, Date, chip_model, screen_mode)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name of the company that has the least number of phone models. List the company name and the number of phone model produced by that company.
assistant
SELECT Company_name , count(*) FROM phone GROUP BY Company_name ORDER BY count(*) ASC LIMIT 1;
human
Find the pixels of the screen modes that are used by both phones with full accreditation types and phones with Provisional accreditation types.
assistant
SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode = t2.screen_mode WHERE t2.Accreditation_type = 'Provisional' INTERSECT SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode = t2.screen_mode WHERE t2.Accreditation_type = 'Full'
human
Find the names of the chip models that are not used by any phone with full accreditation type.
assistant
SELECT model_name FROM chip_model EXCEPT SELECT chip_model FROM phone WHERE Accreditation_type = 'Full'
human
Find all phones that have word 'Full' in their accreditation types. List the Hardware Model name and Company name.
assistant
SELECT Hardware_Model_name , Company_name FROM phone WHERE Accreditation_type LIKE 'Full';
human
Find the accreditation level that more than 3 phones use.
assistant
SELECT Accreditation_level FROM phone GROUP BY Accreditation_level HAVING count(*) > 3
human
Find the number of phones for each accreditation type.
assistant
SELECT Accreditation_type , COUNT( * ) FROM phone GROUP BY Accreditation_type ;