A trouble with excercise - 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

A trouble with excercise

Author

Topic

red_kent
Starting Member

3 Posts

Posted - 2006-12-29 : 09:41:18

Hi all.
A trouble with solving an exsercise.

A short description of the database:

The database scheme consists of four relations:

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 relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "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.

The task is:

For each group of laptops with the identical model number, add following record into PC table:
code: minimal code among laptops in the group +20;
model: laptop's model number +1000;
speed: maximal speed among laptops in the group;
ram: maximal ram size among laptops in the group *2
hd: maximal hd capacity among laptops in the group *2;
cd: default value;
price: maximal price among laptops in the group divided by 1.5;

I have performed the following request

insert into pc(code,model,speed,ram,hd,cd,price)
SELECT distinct (Min(b.code)+20),l.model+1000, max(l.speed),(max(l.ram))*2,(max(l.hd))*2,CAST(MAX(cast(left(pc.cd,len(pc.cd)-1)as int))as char(2))+'x',max(l.price)/1.5
from Laptop as l,PC,(
select a.code from laptop as a, laptop as b
where a.model = b.model ) as b
GROUP BY l.model

... but it returns incorrect result on several positions. Where is mistake ?

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 10:59:32

A couple of points...

First, you've created a nasty little thing called a "cross-join" between the "l", "PC", and "b" tables because you have no WHERE clause or ON statement to join the tables and derived tables. That may be where the spurious rows are coming from.

Second, no where in the problem description did I see anything requiring a JOIN at all... it simply states to group the laptops by model and to calculate certain info... then insert the result set into the PC table... It even says to use the default value of the CD which means no calculation and no insert on the column for CD... the default will take care of it. Perhaps I'm reading it wrong but I'm thinking "no joins required", my friend.

Since this is an "exercise", I can only assume that you're kinda new to SQL and are probably taking a class... so I'll offer some friendly advice as both a person who does technical interviews and as an instructor... the first letter of "SQL" stands for "Structured"... if you "Structure" your code, it'll be a heck of a lot easier to read, troubleshoot, and maintain... there are several formatting paradigms that you can choose from... here's one...

 INSERT INTO PC(Code,Model,Speed,Ram,HD,Price) 
 SELECT MIN(Code)+20   AS Code,
        Model+1000     AS Model, 
        MAX(Speed)     AS Speed,
        MAX(Ram)*2     AS Ram,
        MAX(HD)*2      AS HD,
        MAX(Price)/1.5 AS Price
   FROM dbo.Laptop
  GROUP BY Model

Your instructor will appreciate the format as will any new employer you may be seeking out in the future. Dunno about other folks, but when I interview someone for an SQL position, even if it's for a "Java programmer with a reasonable SQL background", I ask them to bring a printed copy of what they consider to be production code. If they give me the normal mumbo-jumbo about they can't because it's all proprietary, then I give them a "homework" problem to bring the answer in for with the caveat that it must be "production quality".

I know that you only posted the code you tried sans any comments. If you really want to impress your instructor or future employer, you code should look something like this...

/**************************************************************************************************
 Purpose:
 For each group of laptops with the identical model number, add following record into PC table:
   Code: minimal code among laptops in the group +20;
  Model: laptop's model number +1000;
  Speed: maximal speed among laptops in the group;
    Ram: maximal ram size among laptops in the group *2
     HD: maximal hd capacity among laptops in the group *2;
     CD: default value;
  Price: maximal price among laptops in the group divided by 1.5;

 Revision History:
 Rev 00 - 12/29/2006 - Red Kent - Initial creation for class _________, problem # ________
**************************************************************************************************/

 INSERT INTO PC(Code,Model,Speed,Ram,HD,Price) --Note: CD will be the DEFAULT
 SELECT MIN(Code)+20   AS Code,
        Model+1000     AS Model, 
        MAX(Speed)     AS Speed,
        MAX(Ram)*2     AS Ram,
        MAX(HD)*2      AS HD,
        MAX(Price)/1.5 AS Price
   FROM dbo.Laptop
  GROUP BY Model

When I have the opportunity to teach SQL, I use a grading system where a full 15% of the grade is based on following a formatting guideline along with proper embedded documentation where appropriate.

--Jeff Moden

red_kent
Starting Member

3 Posts

Posted - 2007-01-01 : 08:13:41

Thanks Jeff

red_kent
Starting Member

3 Posts

Posted - 2007-01-01 : 09:08:30

Another one:

Short database description "Recycling firm":

The firm has a few outlets that receive items for recycling. Each of the outlets receives funds to be paid to deliverers. Information on received funds is registered in a table:
Income_o(point, date, inc)
The primary key is (point, date), thus receipt of money (inc) takes place not more than once a day. Information on payments to deliverers is registered in the table:
Outcome_o(point, date, out)
In this table the primary key (point, date) also ensures bookkeeping of the funds distribution at each point not more than once a day.
In case income and expenses may occur more than once a day, the following tables (primary key is code) are used:
Income(code, point, date, inc)
Outcome(code, point, date, out)

The task is:
Under the assumption that the income (inc) and expenses (out) of the money at each outlet are written not more than once a day, get a result set with the fields: point, date, income, expense.
Use Income_o and Outcome_o tables.

I don't need a distinct solution of this problem. It will be better to do it myself, but I need somebody to prompt an approximate method (how to solve this). My opinion (maybe, incorrect) is that it's impossible to solve this using (left/full join or union constructions)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources