Andrew J Winkler Andrew J Winkler - 7 months ago 9
SQL Question

factoring SQL code - MS SQL Server

SQL Fiddle is currently down regarding MS SQL Server code, so here is a dropbox link to a

.txt
containing the DDL to create the schema I'm using:

https://www.dropbox.com/s/6si4r37449q3ajb/DDL.txt?dl=0

I'm studying for an exam and am wondering if there's a more efficient way to code this. My code works, but it feels wet..

Find out the software package installed on more than one computer.

Here's my solution:

select software_packs_on_more_than_one_pc
from(
Select software.PACK as software_packs_on_more_than_one_pc,
count(pc.tagnum) as num_installs_per_pack
from software
inner join pc on software.TAGNUM=pc.tagnum
group by software.PACK
) as SubQuery
where num_installs_per_pack > 1

Answer

You can get rid of the subquery by using HAVING to filter software packages installed on more than one computer:

SELECT
    s.PACK AS software_packs_on_than_on_pc
FROM software s
INNER JOIN pc p ON
    s.TAGNUM = p.tagnum
GROUP BY s.PACK
HAVING COUNT(p.tagnum) > 1

Note:

  • It is a good practice to use meaningful alias to your tables and columns to improve readability.