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!

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

How many rows does the table have?

A

SELECT COUNT ( * ) FROM friends;

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

How many total friends are in the table?

A

SELECT SUM ( nr_facebook ) FROM friends;

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

How many Twitter followers are in the table

A

SELECT SUM ( nr_twitter ) FROM friends;

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

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

A

SELECT AVG ( nr_facebook ) FROM friends;

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

How many men are in the table?

A

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

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

How many women are in the table?

A

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

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

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

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

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

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

A

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

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

Which person has the maximum number of facebook friends?

A

SELECT vname, MAX ( nr_facebook ) FROM friends;

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

Which people have more Twitter followers than Facebook friends?

A

SELECT vname, nr_facebook, nr_twitter FROM friends WHERE

nr_twitter > nr_facebook;

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

Print out a table that looks like

[ person, total number of friends & followers]

A

SELECT vname, nr_facebook + nr_twitter FROM friends;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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:

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
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

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;

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:

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;

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:

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

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;

24
Q

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

A

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

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

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

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