Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Grouped Queries > Flashcards

Grouped Queries Flashcards

1
Q

What is a grouped query

A

A query that contains
1. An aggregate function
2. A group by clause
3. Both

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

How many rows are returned per group?

A

1

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the difference between WHERE and HAVING

A
  • WHERE is evaluated at the row level
  • HAVING is evaluated at the group level

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Does WHERE or HAVING come first in logical query processing?

A
  • WHERE is before HAVING in logical query processing

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What aggregate functions does T-SQL support?

A
  1. Count(*)
  2. Count(FieldName)
  3. SUM
  4. AVG
  5. MIN
  6. MAX

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How are nulls handled by aggregate functions?

A
  • For all except Count(*), they are ignored

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the difference between count(*) and count(FieldName)

A
  • count(*) includes nulls
  • count(FieldName) DOES NOT include nulls

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Where is DISTINCT placed when using it in an aggregate function

A 
SELECT 
shipperid,
count(DISTINCT FieldName) AS         
numshippingdates 
FROM sales.orders
GROUP BY shipperid

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What aggregate function is DISTINCT most commonly used with

A

count

  • example: count(DISTINCT shippeddate)

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Why would you use a grouping set clause

A
  • To write one succinct statement instead of, for example, four separate GROUP BY queries combined using UNION ALL

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Example grouping set clause

A 
GROUP BY GROUPINGSETS
(
(shipperid, year(shippeddate)),
(shipperid                                   ),
(year(shippeddate)                   ),
(                                                   ),
);

Note: the blank parens represent an empty set

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the output of a query with multiple grouping sets

A
  • The output would be the same as if you had made a single query for each grouping set and then used UNION ALL to combine them.

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Your three columns are shipperid, shipyear, numorders. In a GROUP BY GROUPINGSETS clause, what happens if the set doesn’t use all of the columns? For example, where shipper is is by itself in parens and shipyear is by itself in parens.

GROUP BY GROUPINGSETS
(
(shipperid, shipyear),
(shipperid                  ),
(shipyear                   ),
(                           ),
);
A
  • The grouping set (shipperid ) doesn’t include shipyear so shipyear will be null in the results for this set.
  • The grouping set (shipyear ) doesn’t include shipperid so shipperid will be null in the results for this set.

Note: count (*) AS numorders is always returned

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the three clauses that allow multiple groupings in one query and where are they placed?

A
  1. GROUPINGSETS
  2. CUBE
  3. ROLLUP

Place these right after GROUP BY

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the general form of the GROUP BY CUBE expression and how is it similar to GROUP BY GROUPING SETS?

A 
GROUP BY CUBE(col1, col2, col3, etc)

GROUP BY CUBE(shipperid, year(shippeddate)

-Is the same as-

GROUP BY GROUPINGSETS
(
(shipperid, year(shippeddate)),
(shipperid ),
(year(shippeddate) ),
( ),
);

SQL Server 70-461 05-01

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the general format of the GROUP BY ROLLUP expression?

A

GROUP BY ROLLUP (col1, col2, col3, etc)

SQL Server 70-461 05-01

17
Q

How does GROUP BY ROLLUP work? And how is it different from GROUP BY CUBE?

A

GROUP BY ROLLUP accepts a list of inputs and, unlike GROUP BY CUBE, it does not return all possible combinations of the inputs as grouping sets. It assumes a hierarchy.

GROUP BY ROLLUP(shipcountry, shipregion, shipcity)

Is the same as

(shipcountry, shipregion, shipcity),
(shipcountry, shipregion                ),
(shipcountry                                     ),
(                                                          )
)

There are 8 possible combinations of the fields. ROLLUP only gives 4. CUBE gives all 8.

SQL Server 70-461 05-01

18
Q

How does GROUP BY CUBE work?

A

It takes a list of inputs and defines all possible grouping sets that could be generated, including the empty set.

SQL Server 70-461 05-01

19
Q

When using GROUPING SETS, CUBE or ROLLUP, can you put more than one in the GROUP BY clause?

A

Yes, just separate them by commas. When you do this you get a multiplying effect. ROLLUP(x,y,z), CUBE(A,B,C) would generate 32 grouping sets. 4 sets from ROLLUP and 8 sets from CUBE for a total of 32 sets.

SQL Server 70-461 05-01

20
Q

What does a multiple grouping set do?

A

It groups the data in more than one way in one query

SQL Server 70-461 05-01

21
Q

How does HAVING work?

A

Very similar to WHERE, except it is applied per group instead of per row.

GROUP BY comes before HAVING, so you can refer to aggregate computations in HAVING

SQL Server 70-461 05-01

22
Q

What does the empty set define?

A

One group with all rows for grand aggregates.

SQL Server 70-461 05-01

23
Q

HAVING, SELECT and ORDER BY are the last three clauses in the logical query processing phases. Why is this important to know?

A
  1. They are working on a grouped table since GROUP BY already happened.
  2. Expressions that appear in these clauses must guarantee a single result per group.
  3. If you want to refer to elements that aren’t in the GROUP BY clause you have to apply an aggregate function to them so you will get only one value per group.

SQL Server 70-461 05-01

24
Q

What are two ways you can distinguish between nulls that are a result of grouping sets and nulls that were in the original data prior to grouping?

A
  1. Grouping function
  2. Grouping_ID function

SQL Server 70-461 05-01

25
Q

What is the general form of the GROUPING function?

A

Example 1
SELECT
shipcountry, GROUPING(shipcountry) AS grpcountry,

shipregion, GROUPING(shipregion) AS grpregion,

shipcity, GROUPING(shipcity) AS grpcity,

count(*) AS numorders

FROM sales.orders
GROUP BY ROLLUP (shipcountry, shipregion, shipcity)

Example 2
ORDER BY GROUPING(c.custid)

SQL Server 70-461 05-01

26
Q

How does the GROUPING function work?

A

It takes one input.

It returns 0 when the input is part of the grouping set and 1 when it isn’t.

SQL Server 70-461 05-01

27
Q

What does the GROUPING_ID function do?

A

It accepts a list of grouped columns as inputs and returns a 0 when the element is part of the grouping set and a 1 when it isn’t. For the 1’s you take 2 raised to the power of the position minus 1. The far right column position is 1, the column to the left of it is 2, etc. Sum all the calculations and you get the GROUPING_ID.

SQL Server 70-461 05-01

28
Q

What is the general format of the GROUPING_ID function?

A

SELECT
GROUPID_ID(
shipcountry,
shipregion,
shipcity
) AS grpid,
shipcountry,
shipregion,
shipcity,
count(*) AS numorders

FROM sales.orders
GROUP BY ROLLUP (shipcountry, shipregion, shipcity)

SQL Server 70-461 05-01

29
Q

Example of GROUPING _ID with 3 columns as inputs

A

GROUPING_ID(
shipcountry,
shipregion,
shipcity
) AS grpid

shipcountry shipregion shipcity grpid
0 0 0 0
0 0 1 1
0 2 1 3
4 2 1 7

  • 0=it is part of the grouping set
  • 1=shipcountry and shipregion are part of the grouping set
  • 3=shipcountry only is part of the grouping set
  • 7=represents the empty set

SQL Server 70-461 05-01

30
Q

What does count(*) do?

A

Counts number of rows per group including nulls.

SQL Server 70-461 05-01

31
Q

What does a single grouping set do?

A

Groups data in only one way in one query.

SQL Server 70-461 05-01

32
Q

What happens when you invoke a group function in a query but don’t use a GROUP BY clause?

A

All rows are arranged into one group.

Example
SELECT count(*) AS numorders
FROM sales.orders

numorders
- - - - - -
830

SQL Server 70-461 05-01

33
Q

When all inputs are null what do the various aggregate functions return?
(min, max, avg, sum, count)

A

Count returns a 0. The others return null.

SQL Server 70-461 05-01

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions