sql-3x.ru exercise 18 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql-3x.ru exercise 18

Author

Topic

lcblank
Starting Member

10 Posts

Posted - 2015-04-24 : 10:25:04

The following is the database description:

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 table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

Now the problem is "Find the makers of the cheapest color printers.Result set: maker, price."

Now my code is


Select Distinct product.maker, printer.price
from product inner join printer on product.model=printer.model
where product.model in
   (Select model from printer
   where price = 
      (Select min(price) from printer
      group by color 
      having color='y')     
      and color='y') 
   and product.type='printer'

It checks the solution against two databases. In the first i receive the correct solution, but not in the second. I don't know where the error is coming from. Any ideas? Thanks for the help!!!

Kristen
Test

22859 Posts

Posted - 2015-04-24 : 12:36:40

Some reason that you cannot do this?


SELECT product.maker, MIN(printer.price) AS [price]
from product 
    inner join printer 
         on printer.model = product.model
where     product.type='printer'
      and printer.color='y'
GROUP BY product.maker
ORDER BY [price]

lcblank
Starting Member

10 Posts

Posted - 2015-04-24 : 14:31:14

That retrieves two values. I just started today so my guess is that it retrieves every maker and its min price rather than the maker of the minimum priced printer of all printers. How could i retrieve what i need from your subquery?

lcblank
Starting Member

10 Posts

Posted - 2015-04-24 : 14:44:28

[code]
select distinct product.maker, printer.price
from product inner join printer
on printer.model=product.model
where printer.price=
(SELECT MIN(price) AS [price]
from printer
where printer.color='y') and printer.color='y'
[/code]
This worked, my guess is that by making the function check model there may have been a discrepancy between individual units in the product table vs. the printer table. Since, were using the product table to solve this problem we can however assume there will be a printer product of makers at the correct price.

Kristen
Test

22859 Posts

Posted - 2015-04-25 : 02:41:31

quote:
Originally posted by lcblank

I just started today so my guess is that it retrieves every maker and its min price rather than the maker of the minimum priced printer of all printers.

Actually, if my client gave me a Brief like your question I'll tell them to rewrite it!!!!

"Find the makers"

So multiple "makers"

"of the cheapest color printers"

Which ones are expensive, and which are cheap>? Must be a "line" somewhere between them.

Maybe one Maker makes all the Cheapest printers?

Maybe one make makes most of them, and another make makes a few of them, and the rest are all expensive.

It is very vague.

You seem to think that you should not display a Maker and a Price (combination) more than once. That sounds reasonable - but in the real world I might buy Model A or Model B if they were both the same price ....

quote:
How could i retrieve what i need from your subquery?


SELECT DISTINCT product.maker, MIN(printer.price) AS [price]
from product 
    inner join printer 
         on printer.model = product.model
where     product.type='printer'
      and printer.color='y'
GROUP BY product.maker
ORDER BY [price]

Kristen
Test

22859 Posts

Posted - 2015-04-25 : 02:43:00

If you wanted, say, the 10 cheapest prices then:


SELECT DISTINCT TOP 10 product.maker, printer.price
from product 
    inner join printer 
         on printer.model = product.model
where     product.type='printer'
      and printer.color='y'
ORDER BY printer.price DESC

lcblank
Starting Member

10 Posts

Posted - 2015-04-25 : 09:39:02

Sorry for the lack of clarity and thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources