2

I have the two following tables:

CREATE TABLE PC (
    model ..., 
    speed ..., 
    ram...,
    ...
);

CREATE TABLE Laptop (
    model ..., 
    speed ..., 
    ram ..., 
    screen...,
    ...
);

And am trying to create a query that gives the model numbers of all laptops with a speed slower than that of any PC.

I came up with:

SELECT model
FROM Laptop, (SELECT MIN(speed) AS speed FROM PC)slowpcs
WHERE Laptop.speed < slowpcs.speed;

However even after double checking that I had added a laptop that meets this criterion and running the code, it's giving me an empty results list.

INSERT INTO Laptop
(model, speed, ram)
VALUES
(2006, 3.00, 2048),
(2007, 1.73, 1024),
(2009, 2.80, 512);

INSERT INTO PC
(model, speed, ram)
VALUES
(1070, 2.00, 1024,
(1071, 2.10, 512),
(1050, 1.90, 512);
3

From your post:

INSERT INTO PC    
(model, speed, ram)
VALUES
(1070, 2.00, 1024, --- Missing closing parenthesis ')'
(1071, 2.10, 512),
(1050, 1.90, 512);

Once that was corrected, your query worked for me.

2

Does this get you your expected results?

USE tempdb;

CREATE TABLE #Laptop
(
    model INT,
    speed NUMERIC(9, 2),
    ram INT
);

INSERT INTO #Laptop ( model, speed, ram )
VALUES ( 2006, 3.00, 2048 ), ( 2007, 1.73, 1024 ), ( 2009, 2.80, 512 );

CREATE TABLE #PC
(
    model INT,
    speed NUMERIC(9, 2),
    ram INT
);

INSERT INTO #PC ( model, speed, ram )
VALUES ( 1070, 2.00, 1024 ), ( 1071, 2.10, 512 ), ( 1050, 1.90, 512 );


SELECT l.*
FROM   #Laptop AS l
WHERE  EXISTS ( SELECT 1 FROM #PC AS p WHERE p.speed > l.speed );

DROP TABLE #PC, #Laptop;
1

However even after double checking that I had added a laptop that meets this criterion and running the code, it's giving me an empty results list.

Your INSERT INTO PC ... statement has syntactic errors, and consequently, the PC table would be empty.

So the subquery

SELECT  min(speed) AS speed FROM PC;

is NULL. (try SELECT min(speed) IS NULL AS speed FROM PC; to verify)

In turn, when you compare Laptop.speed to NULL, which is equivalent to:

SELECT model FROM Laptop
WHERE Laptop.speed < (SELECT MIN(speed) AS speed FROM PC);

you get an empty result table, no matter what the values of Laptop.speed in the laptop table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.