# FUNCTIONS (friends.db) Flashcards

1
Q

SPECIAL ANNOUNCEMENT

Are you getting the feeling someone is trying to trick you? No no NO! All these questions are very typical questions that you can be expected to find in real-world situations. Some people will be able to answer them more quickly than other people - not everyone is equally fast - but please do not feel like you are being tricked. These are very typical SQL questions . . . so just practice them until they feel “natural” to you!

A

Please use these flash cards in good health and good happiness!

2
Q

How many rows does the table have?

A

SELECT COUNT ( * ) FROM friends;

3
Q

How many total friends are in the table?

A

SELECT SUM ( nr_facebook ) FROM friends;

4
Q

How many Twitter followers are in the table

A

SELECT SUM ( nr_twitter ) FROM friends;

5
Q

What is the average number of friends that are in the table?

A

SELECT AVG ( nr_facebook ) FROM friends;

6
Q

How many men are in the table?

A

SELECT COUNT ( * ) FROM friends WHERE geschlecht = “M”;

7
Q

How many women are in the table?

A

SELECT COUNT ( * ) FROM friends WHERE geschlecht = “W”;

8
Q

What is the average number of friends that the men have?

A

SELECT AVG ( nr_facebook ) FROM friends WHERE geschlecht = “M”;

9
Q

Create a table that looks like
[ stadt, sum of friends ]
in order to show how many total friends are had by people in each city

A

FROM friends
GROUP BY ( stadt ) ;

Please note this is the typical “paradigm” or “design pattern” where we use a function in SELECT but then also GROUP BY

10
Q

How many total friends do the mean in each city have?
but only for the men

A

FROM friends
WHERE geschlecht = “M”
GROUP BY ( stadt ) ;

11
Q

Which person has the maximum number of facebook friends?

A

SELECT vname, MAX ( nr_facebook ) FROM friends;

12
Q

A

13
Q

Print out a table that looks like

[ person, total number of friends & followers]

A

14
Q

Often people think that COUNT ( ) is just used for the overall table length. But be careful - it is very useful for COUNTing occurances.

How many people are in which city? In other words, we are looking for
[stadt, total number of people (not friends)]

A

FROM friends

15
Q

Which city has the most number of people (not friends or twitter followers)?

A

There are probably lots of ways to do this.

One easy way is to first prepare a table of how many people live in which city, which we did as an earlier flashcard:

Then we can embed that query as a “sub-query” into a very simple query that searches for the MAX:

```SELECT
FROM
(
);```
16
Q

Create a list of all the unique cities in the table.

Hint: DISTINCT keywords

A

You can get a list of all the cities with

but the problem is that it contains duplicates. To print out just the unique (distinct) city names, you can just use the keyword DISTINCT:

17
Q

TRICKY. In SQLite there is a function that will cause all of the different output values to be “appended” to each other in a long string, separated by commas. Have a look at the documentation https://www.sqlite.org/lang_aggfunc.html.

Now write a query that prints a SINGLE ROW that contains all the city names, one after the other, separated by commas.

A

You can get the list of unique city names like this:

The desired function is called GROUP_CONCAT, so just use it like this:

SELECT
FROM friends;

18
Q

TRICKY

Let’s “pretend” that friends did not have names but rather the email adresses - that’s reasonable, right? And let’s assume you want a long string of all the email addresses, separated by “;” so that you can cut-and-paste this string into an email - either by hand or by your incredible JDBC application. How can you do this using GROUP_CONCAT and SQLite?

A

SELECT
GROUP_CONCAT ( vname, “;” )
FROM friends;

19
Q

Print a list of how many people (vname, not friends or twitter followers) there are in each city. Then, print a similar list for just the men, and a similar list for just the women.

A

We can get the number of occurances total like this:

FROM friends

If we just want to count the men or the women, we can use a WHERE statement to narrow the results, like this:

FROM friends
WHERE geschlecht = “M”

Be careful, because the “order” is always SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, so that means WHERE must occur before the GROUP BY.

```Same with the women:
FROM friends
WHERE geschlecht = "W"
20
Q

Print a longer list of how many men are in each city and how many women are in each city. It is OK if the city name occurs twice (once for the men, once for the women)

A

That last bit tells us that UNION is a good choice, and we can just link the 2 SQLs together:

```SELECT stadt, COUNT ( stadt )
FROM friends
WHERE geschlecht = "W"
UNION
FROM friends
WHERE geschlecht = "M"
21
Q

Print out a list of how many cities are in each country, but only print out the cases where there are more than 500 cities in a country.

A

This is not so tricky. At first you might think by putting in WHERE COUNT(city) > 500 will solve your problems - but you would only be half-right. You need to do exactly this, but add it via a HAVING, as shown here:

```SELECT country, COUNT ( city )
FROM world_cities
GROUP BY country
HAVING COUNT( city ) > 500;```
22
Q

INTERESTING FACT

A

Many people believe that William Shakespeare helped to write the King James version of the Bible. As proof of this, they claim he finished the job on his 46th birthday. if you look at Psalm 46, the 46th word from the beginning is “Shake” and the 46th word from the ending is “Spear.”

23
Q

Print a list of all cities and the total number of facebook friends matching that city - but only for cities with more than 700 total friends

A

If you get stuck, take things one step at a time. First, you can print out the list of cities and total number of friends mapped to that city:

FROM friends

Now, think about what you need to do to restrict the answers to those cities with more than 700. If you thought of WHERE you are close. Because we are using GROUP BY, we need something similar but after the GROUP BY. The needed qualifier is HAVING, like this:

FROM friends
HAVING SUM ( nr_facebook ) > 700;

24
Q

Print out a table of the cities and the total number of facebook friends + twitter followers mapped to that city

A

SELECT
FROM friends

25
Q

This is quite a common request. Print out the total number of facebook friends and twitter followers

A

The question is a bit confusing - but that’s life, sometimes questions are confusing!

If you just want a single number that is a sum of everything, then use this:

```SELECT SUM ( nr_facebook ) + SUM ( nr_twitter )
FROM friends;```

But if you want two separate sums, then use this:

```SELECT SUM ( nr_facebook ), SUM ( nr_twitter )
FROM friends;```
26
Q

Print out a list of all the people in Zurich and the number of their facebook friends and twitter followers

A

FROM friends
WHERE

27
Q

Print out a list of all the people in Zurich and the number of their facebook friends and twitter followers, and then in the final column print out the SUM of the facebook friends and twitter followers.

A

This is not meant to be tricky, but it could be confusing, so be careful. It says to print out the SUM, so you might think of using the function SUM. But you’d be wrong. SUM works by summing more than one row. In this case, we are interested in the sum of the value in a single row - and for this we do not need a function, just the operator “+”, like this:

```SELECT
FROM friends
WHERE
28
Q

Print out the list of people in Zurich, the number of their facebook friends, the number of their twitter followers, the SUM of both, and the RATIO of their facebook friends to twitter followers (e.g. facebook/twitter).

A

This is not meant to be tricky, but it could be confusing, so be careful. It says to print out the SUM, so you might think of using the function SUM. But you’d be wrong. SUM works by summing more than one row. In this case, we are interested in the sum of the value in a single row - and for this we do not need a function, just the operator “+”. And the same goes for RATIO, there is no need for a ratio function between you can use “/”, like this:

```SELECT
```SELECT