-2

Get the makers who produce only one product type and more than one model.

Wrong Answer: 

`SELECT` `DISTINCT` maker
from Product `GROUP BY` maker 
`HAVING` (`COUNT`(`DISTINCT` TYPE) = 1 `AND` `COUNT`(`DISTINCT` model) > 1)

Right Answer: 

`SELECT` `DISTINCT` maker, type 
`from` Product 
`WHERE` maker in (SELECT DISTINCT maker from Product 
`GROUP BY` maker `HAVING` `COUNT`(`DISTINCT` TYPE) = 1 `AND` `COUNT`( model) > 1)

Short database description "Computer firm":

The database scheme consists of four tables: Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, screen, price) Printer(code, model, color, type, price) The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price. The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen. For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field, printing technology ('Laser', 'Jet', or 'Matrix') – type, and price are specified.

Danilo Bustos
  • 1,083
  • 1
  • 7
  • 9
sapio_l
  • 21
  • 7
  • 1
    Good luck with your homework! Please feel free to come back and tell us if you succeeded to do it! – Lelio Faieta Feb 21 '17 at 13:50
  • Funny that the "right" answer is also wrong... `SELECT DISTINCT maker, type...... GROUP BY maker ....` type is not named within the GROUP BY -> read https://www.psce.com/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/ – Raymond Nijland Feb 21 '17 at 13:57

1 Answers1

2

(1) Note that what you marked as the "right" answer is in contradiction with the problem statement as given. The problem statement as given says "get the makers", not "get the makers and the type". The "wrong" answer should therefore not be considered "wrong", because it does exactly what the problem statement asks for.

(2) Assuming, however, that you have not been accurate in rendering the actual problem statement, and the actual problem was indeed "get the makers who make only one type, and the type that it is", it is indeed necessary to write the query the second way.

The reason is that the GROUP BY clause internally creates an "intermediate table", and the SELECT clause actually references this "intermediate table" instead of the table named in the FROM clause, and the only columns that are included in this intermediate table are the ones mentioned in the GROUP BY clause (and TYPE is not, in your case, for the obvious reason that you have to count the number of distinct types per maker).

So you must first get the set of concerned makers, and then "connect that back" to the original table. The former is what the subquery does, the latter is what the "WHERE ... IN ..." does.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52