FUNCTIONS (friends.db)

Question 1

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!

Answer 1

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

Question 2

How many rows does the table have?

Answer 2

SELECT COUNT ( * ) FROM friends;

Question 3

How many total friends are in the table?

Answer 3

SELECT SUM ( nr_facebook ) FROM friends;

Question 4

How many Twitter followers are in the table

Answer 4

SELECT SUM ( nr_twitter ) FROM friends;

Question 5

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

Answer 5

SELECT AVG ( nr_facebook ) FROM friends;

Question 6

How many men are in the table?

Answer 6

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

Question 7

How many women are in the table?

Answer 7

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

Question 8

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

Answer 8

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

Question 9

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

Answer 9

SELECT stadt, SUM ( nr_facebook )
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

Question 10

How many total friends do the mean in each city have?
[ stadt, sum of friends]
but only for the men

Answer 10

SELECT stadt, SUM ( nr_facebook )
FROM friends
WHERE geschlecht = “M”
GROUP BY ( stadt ) ;

Question 11

Which person has the maximum number of facebook friends?

Answer 11

SELECT vname, MAX ( nr_facebook ) FROM friends;

Question 12

Which people have more Twitter followers than Facebook friends?

Answer 12

SELECT vname, nr_facebook, nr_twitter FROM friends WHERE

nr_twitter > nr_facebook;

Question 13

Print out a table that looks like

[ person, total number of friends & followers]

Answer 13

SELECT vname, nr_facebook + nr_twitter FROM friends;

Question 14

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)]

Answer 14

SELECT stadt, COUNT ( stadt )
FROM friends
GROUP BY stadt;

Question 15

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

Answer 15

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:

SELECT stadt, COUNT ( stadt ) AS Nr_People
FROM friends GROUP BY stadt;

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

SELECT 
Stadt, MAX ( Nr_People)
FROM
(
SELECT  stadt, COUNT ( stadt ) AS Nr_People 
FROM friends GROUP BY stadt
);

Question 16

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

Hint: DISTINCT keywords

Answer 16

You can get a list of all the cities with

SELECT stadt FROM friends;

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

SELECT DISTINCT stadt FROM friends;

Question 17

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.

Answer 17

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

SELECT DISTINCT stadt FROM friends;

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

SELECT
GROUP_CONCAT ( DISTINCT stadt )
FROM friends;

Question 18

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?

Answer 18

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

Question 19

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.

Answer 19

We can get the number of occurances total like this:

SELECT stadt, COUNT ( stadt )
FROM friends
GROUP BY stadt;

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

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

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:
SELECT stadt, COUNT ( stadt )
FROM friends
WHERE geschlecht = "W"
GROUP BY stadt;

Question 20

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)

Answer 20

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"
GROUP BY stadt
UNION
SELECT stadt, COUNT ( stadt )
FROM friends
WHERE geschlecht = "M"
GROUP BY stadt;

Question 21

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.

Answer 21

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;

Question 22

INTERESTING FACT

Answer 22

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.”

Question 23

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

Answer 23

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:

SELECT stadt, SUM ( nr_facebook )
FROM friends
GROUP BY stadt;

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:

SELECT stadt, SUM ( nr_facebook )
FROM friends
GROUP BY stadt
HAVING SUM ( nr_facebook ) > 700;

Question 24

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

Answer 24

SELECT
stadt, SUM ( nr_facebook ) + SUM ( nr_twitter)
FROM friends
GROUP BY stadt;

Question 25

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

Answer 25

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;

Question 26

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

Answer 26

SELECT vname, nr_facebook, nr_twitter
FROM friends
WHERE
stadt = “Zurich”;

Question 27

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.

Answer 27

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 
vname, nr_facebook, nr_twitter,
nr_facebook + nr_twitter
FROM friends
WHERE
stadt = "Zurich";

Question 28

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).

Answer 28

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 
vname, nr_facebook, nr_twitter,
nr_facebook + nr_twitter,
nr_facebook / nr_twitter
FROM friends
WHERE
stadt = "Zurich";

BUT BE CAREFUL! Here’s the tricky part! In the above code, you divided two integers. You can do that - no problem - but the accuracy is very poor. A better way is to CAST them first to REAL, then carry out the division. You can either do this with the CAST command, or there is this useful trick:

SELECT 
vname, nr_facebook, nr_twitter,
nr_facebook + nr_twitter,
nr_facebook*1.0 / nr_twitter*1.0
FROM friends
WHERE
stadt = "Zurich";