FUNCTIONS (friends.db) Flashcards
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!
Please use these flash cards in good health and good happiness!
How many rows does the table have?
SELECT COUNT ( * ) FROM friends;
How many total friends are in the table?
SELECT SUM ( nr_facebook ) FROM friends;
How many Twitter followers are in the table
SELECT SUM ( nr_twitter ) FROM friends;
What is the average number of friends that are in the table?
SELECT AVG ( nr_facebook ) FROM friends;
How many men are in the table?
SELECT COUNT ( * ) FROM friends WHERE geschlecht = “M”;
How many women are in the table?
SELECT COUNT ( * ) FROM friends WHERE geschlecht = “W”;
What is the average number of friends that the men have?
SELECT AVG ( nr_facebook ) FROM friends WHERE geschlecht = “M”;
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
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 many total friends do the mean in each city have?
[ stadt, sum of friends]
but only for the men
SELECT stadt, SUM ( nr_facebook )
FROM friends
WHERE geschlecht = “M”
GROUP BY ( stadt ) ;
Which person has the maximum number of facebook friends?
SELECT vname, MAX ( nr_facebook ) FROM friends;
Which people have more Twitter followers than Facebook friends?
SELECT vname, nr_facebook, nr_twitter FROM friends WHERE
nr_twitter > nr_facebook;
Print out a table that looks like
[ person, total number of friends & followers]
SELECT vname, nr_facebook + nr_twitter FROM friends;
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)]
SELECT stadt, COUNT ( stadt )
FROM friends
GROUP BY stadt;
Which city has the most number of people (not friends or twitter followers)?
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 );