Aggregate functions

Question 1

Use the SUM() function to get the total duration of all films.

TABLE: films
Field; duration

Answer 1

Question 2

Get the average duration of all films.
TABLE: films
Field; duration

Answer 2

Question 3

Get the duration of the shortest film.
TABLE: films
Field; duration

Answer 3

Question 4

Get the duration of the longest film
TABLE: films
Field; duration

Answer 4

Question 5

Use the SUM() function to get the total amount grossed by all films.
TABLE: films
Field; gross

Answer 5

Question 6

Get the average amount grossed by all films.
TABLE: films
Field; gross

Answer 6

Question 7

Get the amount grossed by the worst performing film.
TABLE: films
Field; gross

Answer 7

Question 8

Get the amount grossed by the best performing film.
TABLE; films
Field; gross

Answer 8

Question 9

Use the SUM() function to get the total amount grossed by all films made in the year 2000 or later.

TABLE; films
Fields; gross, release_year

Answer 9

Question 10

Get the average amount grossed by all films whose titles start with the letter ‘A’.

TABLE; films
Field; gross, title

Answer 10

Question 11

Get the amount grossed by the worst performing film in 1994.

TABLE; films
FIELD; gross, release_year

Answer 11

Question 12

Get the amount grossed by the best performing film between 2000 and 2012, inclusive.

TABLE; films
FIELD; gross, release_year

Answer 12

Question 13

what is (4/3) in sql

Answer 13

1. Carful!!!
   because both are stated as integers you will get an integer back
   if you want the decimal back you need to do
   (3.0/4.0)

Question 14

Get the title and net profit (the amount a film grossed, minus its budget) for all films. Alias the net profit as net_profit.

TABLE; films
FIELD; gross, budget, title

Answer 14

Question 15

Get the title and duration in hours for all films. The duration is in minutes, so you’ll need to divide by 60.0 to get the duration in hours. Alias the duration in hours as duration_hours.

TABLE; films
FIELD; duration, title

Answer 15

Question 16

Get the average duration in hours for all films, aliased as avg_duration_hours.

TABLE; films
FIELD; duration

Answer 16

Question 17

Get the percentage of people who are no longer alive. Alias the result as percentage_dead. Remember to use 100.0 and not 100!

TABLE; people
FIELDS; deathdate

Answer 17

Question 18

Get the number of years between the newest film and oldest film. Alias the result as difference.

TABLE; films
FIELDS; release_year

Answer 18

Question 19

Get the number of decades the films table covers. Alias the result as number_of_decades. The top half of your fraction should be enclosed in parentheses.

TABLE; films
FIELD; release_year

Answer 19