SQL querie

Kinky Slinky

List all details for the laptop that weighs the least?

LAPTOP: LPModel, Memory,HdDr,Screen,Col,Weight,CPrice, ProductId,



Can I use the MIN function for this querie. How can I use the MIN function and get all data associated with that value

Any help appreciated.

biko

query, queries

select latop
from laptoplist
where weight = (select min(weight) from laptoplist)

sorry, changed that a bit. This is how I think it would work, try it out.

Kinky Slinky

'LAPTOP' is the table name.
Is this the same thing :

SELECT *
FROM LAPTOP
WHERE weight = (SELECT MIN(weight) FROM LAPTOP)

TouchingVirus

Kinky Slinky wrote: »

'LAPTOP' is the table name.
Is this the same thing :

SELECT *
FROM LAPTOP
WHERE weight = (SELECT MIN(weight) FROM LAPTOP)

Yes

Procasinator

Kinky Slinky wrote: »

'LAPTOP' is the table name.
Is this the same thing :

SELECT *
FROM LAPTOP
WHERE weight = (SELECT MIN(weight) FROM LAPTOP)

Just so you know, unless weight is unique, this could return more than one laptop (i.e. more than one laptop shares the lowest weight).

Beano

Depending on the flavour of SQL you are using you could try

select top 1 *
from LAPTOP
order by weight

You would be guaranteed to return one row.

yenoah

SELECT TOP 1 * FROM LAPTOP ORDERBY weight ASC

This will return just one record

amen

Try not use * in your select statements. Better to explicity specify the column names as you generally get better query performance.

Also helps if someone adds an extra column and you use select * then the extra column comes back and you code may not handle it.

yenoah

amen wrote: »

Try not use * in your select statements. Better to explicity specify the column names as you generally get better query performance.

Also helps if someone adds an extra column and you use select * then the extra column comes back and you code may not handle it.

Context!

The question states "list all details" for a single laptop. So in that context
select top 1 * . . . is perfect.

If the table of laptops reaches a certain tipping point number (amount of records) however, one may do well to go back to an embedded select as my solution requires a sort on a non indexed column.

Common sense and readability are what I tend to go for. Common sense should take of optimization given any particular context and requirement set, while good readability for maintenance, upgrade, support etc

Kinky Slinky

Another Problem I have, don't think my answer is correct.

Who manufactures printer model ‘prn135col’?

Product : ProductId, Manufacturer, Description, RRP SupplierId
PRINTER : PRModel, type,fax,colour,price,ProductId,

SELECT manufacturer
FROM Product
INNER JOIN PRINTER
ON Product.ProductId = PRINTER.ProductId
WHERE PRModel = 'prn135col'

yenoah

Kinky Slinky wrote: »

Another Problem I have, don't think my answer is correct.

Who manufactures printer model ‘prn135col’?

Product : ProductId, Manufacturer, Description, RRP SupplierId
PRINTER : PRModel, type,fax,colour,price,ProductId,

SELECT manufacturer
FROM Product
INNER JOIN PRINTER
ON Product.ProductId = PRINTER.ProductId
WHERE PRModel = 'prn135col'

The tables don't look well normalised, A printer is a product, no?

What about a new table
Products
=======
-ProdID
-Type (Here a list including Printer, Computer, whatever etc, perhaps normalised to another table (Product Types)
-Manufacturer,
-Description,
-RRP
-SupplierId
-Make
-Model
-Colour
etc

pippam

Kinky Slinky wrote: »

Another Problem I have, don't think my answer is correct.

Who manufactures printer model ‘prn135col’?

Product : ProductId, Manufacturer, Description, RRP SupplierId
PRINTER : PRModel, type,fax,colour,price,ProductId,

SELECT manufacturer
FROM Product
INNER JOIN PRINTER
ON Product.ProductId = PRINTER.ProductId
WHERE PRModel = 'prn135col'

Might be a stupid suggestion but is your SQL set-up case sensitive? If it is, your query has to look like this:

SELECT Manufacturer
FROM Product
INNER JOIN PRINTER
ON Product.ProductId = PRINTER.ProductId
WHERE PRModel = 'prn135col'

Kinky Slinky

yenoah wrote: »

The tables don't look well normalised, A printer is a product, no?

What about a new table
Products
=======
-ProdID
-Type (Here a list including Printer, Computer, whatever etc, perhaps normalised to another table (Product Types)
-Manufacturer,
-Description,
-RRP
-SupplierId
-Make
-Model
-Colour
etc

I'm given these tables, there's no information contained in them. I just have to write the SQL query for the question asked.

pippam wrote: »

Might be a stupid suggestion but is your SQL set-up case sensitive? If it is, your query has to look like this:

SELECT Manufacturer
FROM Product
INNER JOIN PRINTER
ON Product.ProductId = PRINTER.ProductId
WHERE PRModel = 'prn135col'

Yeah I meant to have it like that, I typed it in wrong.

yenoah

I would raise my point about normalisation with your lecturer. If nothing else, it'll show an ability to think. Apart from that, whats the matter with your sql? is it not working? Have you checked that sufficient data exists in both tables to return a result based on your statement?

Kinky Slinky

yenoah wrote: »

I would raise my point about normalisation with your lecturer. If nothing else, it'll show an ability to think. Apart from that, whats the matter with your sql? is it not working? Have you checked that sufficient data exists in both tables to return a result based on your statement?

I just wasn't sure if it was correct or not. I cannot test the query the question is on paper. There's no data in the tables.

yenoah

Kinky Slinky wrote: »

I just wasn't sure if it was correct or not. I cannot test the query the question is on paper. There's no data in the tables.

well then, your original answer will do fine. As a student you have access to Dreamspark so why not download Sql Server and try out your answers and have a play around? Its the only way to learn.