CODEWITHSUNDEEP
sqlsql-server
Hans Rudel
Hans Rudel

Reputation: 3611

sql "Group By" and "Having"

I am trying to work through some questions and im not sure how to do the following

Q: Find the hard drive sizes that are equal among two or more PCs.

its q15 on this site.

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)

any pointers would be appreciated.

Edit: here is a list of all the hard drives in the pc table along with what the correct answer should be.

enter image description here

Upvotes: 2

Views: 2708

Answers (5)

learning
learning

Reputation: 73

SELECT hd FROM PC GROUP BY hd HAVING COUNT(hd)>1

Right.

The result of Your query:

hd
5.0
8.0
10.0
14.0
20.0

Upvotes: 0

Petar Minchev
Petar Minchev

Reputation: 47373

SELECT DISTINCT p1.hd
  FROM PC p1
  JOIN PC p2
    ON p1.code <> p2.code
 WHERE p1.hd = p2.hd

Upvotes: 3

Sudhakar B
Sudhakar B

Reputation: 1563

How about this ?

Select PC.hd From PC
group by PC.hd
Having Count(PC.hd) >= 2

Upvotes: 6

Pheonix
Pheonix

Reputation: 6052

Try this

select hd from pc group by hd having count(hd)>1

i registered and tried this, it says right.

Upvotes: 1

dcp
dcp

Reputation: 55444

Assumption: pc.code is primary key.

SELECT DISTINCT a.hd
  FROM pc a
 WHERE EXISTS
       (SELECT *
          FROM pc b
         WHERE a.hd = b.hd
           AND a.code != b.code)

Upvotes: 1

Related Questions