L24 - SQL 2

Question 1

What are SQL ISO standard functions?

Answer 1

1. COUNT - number of vales in specified column
2. SUM - Sum of values in specified column
3. AVG - Average of values in specified column
4. MIN - Lowest Value in specified column
5. MAX - highest value in specfied column

Question 2

What is general SQL aggregate utility?

Answer 2

• Operates on single column
• Return single value
• COUNT, MIN, MAX - numeric/non-numeric
• SUM, AVG - numeric
• All (Excluding COUNT) work via
  
  • Eliminate null
  • Work on non-null values

Question 3

How does the count function work?

Answer 3

• COUNT(*) counts all rows
  
  • Regardless of Null
• DISTINCT used to eliminate any duplicated
  
  • No effect with MIN/MAX
  • May have some effect with SUM/AVG

Question 4

How do you use COUNT and SUM

Answer 4

Question 5

How do you use MIN, MAX, AVG?

Answer 5

Question 6

How can you group results?

Answer 6

• GROUP BY
• Used to get sub totals
  
  • Closely integrated with SELECT
• SELECT list must be single-valued per group
• SELECT clause may only have:
  
  • Column names
  • Aggregate functions
  • Constants
  • Or a combination of the three

Question 7

How do you use GROUP BY?

Answer 7

Question 8

What is a restricted returned group?

Answer 8

• HAVING to be paired with GROUP BY
  
  • Restrict groups that appear in final result table
• Adds filter groups
• Column names in the HAVING clause:
  
  • Must also appear in the GROUP BY list
  • Contained within an aggregate function

Question 9

How do you utilise a simple join?

Answer 9