Scribd
Upload
20 views2 pages

SQL Aggregate Function Practice Problems

The document contains 6 practice problems involving aggregate functions. The problems involve counting rows, finding averages, finding maximum and minimum values, and calculating a sum using a join between two tables while filtering for values greater than 0. The solutions to the problems are also provided using the appropriate aggregate functions like COUNT, AVG, MAX, SUM.

Uploaded by

Tiffany Holmes
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views2 pages

SQL Aggregate Function Practice Problems

The document contains 6 practice problems involving aggregate functions. The problems involve counting rows, finding averages, finding maximum and minimum values, and calculating a sum using a join between two tables while filtering for values greater than 0. The solutions to the problems are also provided using the appropriate aggregate functions like COUNT, AVG, MAX, SUM.

Uploaded by

Tiffany Holmes
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Aggregate

 Function  Practice  Problems  


 
1) How  many  rows  are  in  the  [Link]  table?    Use  an  aggregate  function  NOT  
“SELECT  *”.  
2) How  many  rows  in  the  [Link]  table  do  not  have  a  NULL  value  in  the  
MiddleName  column?  
3) What  is  the  average  StandardCost  (located  in  [Link])  for  each  product  
where  the  StandardCost  is  greater  than  $0.00?  
4) What  is  the  average  Freight  amount  for  each  sale  (found  in  [Link])  
where  the  sale  took  place  in  TerritoryID  4?  
5) How  expensive  is  the  most  expensive  product,  by  ListPrice,  in  the  table  
[Link]?  
6) Join  the  [Link]  table  and  the  [Link]  table  for  
only  the  products  that  appear  in  both  table.    Use  the  ProductID  as  the  joining  
column.    [Link]  contains  the  quantity  of  each  product  
(several  rows  can  appear  for  each  product  to  indicate  the  product  appears  in  
multiple  locations).    Your  goal  is  to  determine  how  much  money  we  would  earn  if  
we  sold  every  product  for  its  list  price  for  each  product  with  a  ListPrice  greater  than  
$0.    That  is,  if  you  summed  the  product  of  each  product’s  inventory  by  its  list  price,  
what  would  that  value  be?    (Hint:  This  is  intentionally  challenging.    You  must  use  an  
aggregate  function  with  a  mathematical  expression  to  accomplish  your  goal)  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Aggregate  Function  Practice  Problem  Solutions  
 
Question  1:  
SELECT  COUNT(*)  
FROM  [Link]  
 
Question  2:  
SELECT  COUNT(MiddleName)  
FROM  [Link]  
 
Question  3:  
SELECT  AVG(StandardCost)  
FROM  [Link]  
WHERE  StandardCost  >  0  
 
Question  4:  
SELECT  AVG(Freight)  
FROM  [Link]  
WHERE  TerritoryID  =  4  
 
Question  5:  
SELECT  MAX(ListPrice)  
FROM  [Link]  
 
 
Question  6:  
SELECT  SUM([Link]*[Link])  
FROM  [Link]  P  
INNER  JOIN  [Link]  I  
ON  [Link]  =  [Link]  
WHERE  [Link]  >  0  
 
 

You might also like