An Introductory Guide to SQL/

...

/

Solution Practice Set 5

Solution Practice Set 5

Get the solution to the exercise of viewing the information queried from a database.

We'll cover the following...

Solution Practice Set 5

For the purpose of demonstrating analytic functions, we added a new MoviesScreening table and added some columns to the existing Movies table. The relevant portion of the database relationship model is printed below.

widget

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/quiz2.sh and wait for the MySQL prompt to start-up.

Press + to interact
-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 
-- Question # 1, Query 1
SELECT Weekend, RevenueInMillions
FROM MovieScreening
WHERE MovieId = 10
ORDER BY Weekend;
-- Question # 1, Query 2
SELECT T1.Weekend, T2.Weekend, T1.RevenueInMillions, T2.RevenueInMillions
FROM MovieScreening T1 
INNER JOIN MovieScreening T2
ON T1.MovieId = T2.MovieId
AND T1.Weekend >= T2.Weekend
WHERE T1.MovieId = 10
ORDER BY T1.Weekend, T1.RevenueInMillions;
-- Question # 1, Query 3
SELECT T1.Weekend, T1.RevenueInMillions, SUM(T2.RevenueInMillions) AS RunningTotal
FROM MovieScreening T1 
INNER JOIN MovieScreening T2
ON T1.MovieId = T2.MovieId
AND T1.Weekend >= T2.Weekend
WHERE T1.MovieId = 10
GROUP BY T1.Weekend, T1.RevenueInMillions
ORDER BY T1.Weekend, T1.RevenueInMillions;
-- Question # 2, Query 1
CREATE VIEW CollectionPerGenre AS
SELECT Genre, Sum(CollectionInMillions) as GenreTotal
From Movies
GROUP BY Genre
ORDER BY 2 DESC;
SELECT * FROM CollectionPerGenre;
-- Question # 2, Query 2
SELECT T1.Genre, T2.Genre, T1.GenreTotal, T2.GenreTotal
FROM CollectionPerGenre T1 
INNER JOIN CollectionPerGenre T2
WHERE T1.GenreTotal <= T2.GenreTotal
GROUP BY T1.Genre, T2.Genre, T1.GenreTotal
ORDER BY T1.GenreTotal DESC, T2.GenreTotal DESC;
-- Question # 2, Query 3
SELECT T1.Genre, T1.GenreTotal, 
       SUM(T2.GenreTotal) AS RunningTotal
FROM CollectionPerGenre T1 
INNER JOIN CollectionPerGenre T2
WHERE T1.GenreTotal <= T2.GenreTotal
GROUP BY T1.Genre, T1.GenreTotal
ORDER BY T1.GenreTotal DESC;
-- Question # 2, Query 4
SELECT  d1.Genre AS Genre,  
        d1.GenreTotal AS TotalRevenueInMillions,
        (d1.RunningTotal / d2.TotalSum) * 100 AS PercentageOfTotalRevenues
FROM
( SELECT T1.Genre as Genre, 
         T1.GenreTotal AS GenreTotal, 
         SUM(T2.GenreTotal) AS RunningTotal
  FROM CollectionPerGenre T1 
  INNER JOIN CollectionPerGenre T2
  WHERE T1.GenreTotal <= T2.GenreTotal
  GROUP BY T1.Genre, T1.GenreTotal
  ORDER BY T1.GenreTotal DESC ) d1,
( SELECT SUM(GenreTotal) AS TotalSum 
  FROM CollectionPerGenre) d2;
-- Question # 3, Query 1
SELECT   Weekend, RevenueInMillions
FROM  MovieScreening
WHERE  MovieId = 5
ORDER BY Weekend;
-- Question # 3, Query 2
SELECT @counter := @counter + 1 AS RowNum, 
       T1.Weekend, T1.RevenueInMillions
FROM MovieScreening T1, (SELECT @counter := 0) c
WHERE MovieId = 5
ORDER BY T1.Weekend;
-- Question # 3, Query 3
SELECT * 
FROM (
       SELECT @counter1 := @counter1+ 1 AS RowNum,
              T1.Weekend, T1.RevenueInMillions
       FROM MovieScreening T1, (SELECT @counter1 := 0) c1
       WHERE MovieId=5
       ORDER BY T1.Weekend ) AS table1
JOIN  
( SELECT @counter2 := @counter2 + 1 AS RowNum,
         T2.Weekend, T2.RevenueInMillions
  FROM MovieScreening T2, (SELECT @counter2 := 0) c2
  WHERE MovieId=5
  ORDER BY T2.Weekend ) AS table2
ON table2.RowNum <= table1.RowNum AND table2.RowNum > table1.RowNum - 3
ORDER BY table1.Weekend, table2.Weekend;
-- Question # 3, Query 4
SELECT table1.Weekend AS Weekend,
       table1.RevenueInMillions AS Revenue,
       SUM(table2.RevenueInMillions) AS 3WeekTotal,
       AVG(table2.RevenueInMillions) AS 3WeekAverage
FROM (
       SELECT @counter1 := @counter1+ 1 AS RowNum,
              T1.Weekend, T1.RevenueInMillions
       FROM MovieScreening T1, (SELECT @counter1 := 0) c1
       WHERE MovieId=5
       ORDER BY T1.Weekend ) AS table1
       JOIN  
       (SELECT @counter2 := @counter2 + 1 AS RowNum,
               T2.Weekend, T2.RevenueInMillions
        FROM MovieScreening T2, (SELECT @counter2 := 0) c2
        WHERE MovieId=5
        ORDER BY T2.Weekend ) AS table2
        ON table2.RowNum <= table1.RowNum AND table2.RowNum > table1.RowNum - 3
GROUP BY table1.RowNum, table1.Weekend, table1.RevenueInMillions
HAVING COUNT(table1.RowNum) > 2;
-- Question # 4, Query 1
SELECT Weekend, MONTH(Weekend) AS Month, ROUND(RevenueInMillions,2) As Revenue
FROM MovieScreening
WHERE MovieId = 2;
-- Question # 4, Query 2
SELECT MONTH(Weekend) As Month, group_concat(ROUND(RevenueInMillions,2))
FROM MovieScreening 
WHERE MovieId = 2
GROUP BY MONTH(Weekend);
-- Question # 4, Query 3
SELECT MONTH(Weekend) AS Month, 
       GROUP_CONCAT(ROUND(RevenueInMillions,2)) AS List,
       SUBSTRING_INDEX(GROUP_CONCAT(ROUND(RevenueInMillions,2)),  ',' , 1) AS FirstValue 
FROM MovieScreening 
WHERE MovieId = 2 
GROUP BY MONTH(Weekend);
-- Question # 4, Query 4
SELECT Weekend As Date, MONTH(Weekend) AS Month, 
       ROUND(RevenueInMillions,2) AS RevenueInMillions, FirstValue 
FROM MovieScreening t1,
     (SELECT MONTH(Weekend) AS Month, 
             SUBSTRING_INDEX(GROUP_CONCAT(ROUND(RevenueInMillions,2)),  ',' , 1) AS FirstValue 
      FROM MovieScreening 
      WHERE MovieId = 2 
      GROUP BY MONTH(Weekend) ) t2
WHERE t1.MovieId = 2 AND MONTH(t1.Weekend) = t2.Month;
-- Question # 4, Query 5
SELECT MONTH(Weekend) AS Month, 
       GROUP_CONCAT(ROUND(RevenueInMillions,2)) As List, 
       SUBSTRING_INDEX(GROUP_CONCAT(ROUND(RevenueInMillions,2)),  ',' , -1) AS LastValue 
FROM MovieScreening 
WHERE MovieId = 2 
GROUP BY MONTH(Weekend);
-- Question # 5, Query 1
SELECT MONTH(Weekend) AS Month, SUM(RevenueInMillions) AS TotalRevenueInMillions 
FROM MovieScreening 
WHERE MovieId = 3 
GROUP BY MONTH(Weekend);
-- Question # 5, Query 2
SELECT Month, TotalRevenueInMillions, 
       IF(@PrevVal = 0, 0, ROUND(((TotalRevenueInMillions - @PrevVal) / @PrevVal) * 100, 2))  "Growth %",
       @PrevVal := TotalRevenueInMillions         
FROM
      ( SELECT @PrevVal := 0) d1,
      ( SELECT MONTH(Weekend) AS Month, 
               SUM(RevenueInMillions) as TotalRevenueInMillions 
        FROM MovieScreening 
        WHERE MovieId = 3 
        GROUP BY MONTH(Weekend) ) d2;
Terminal 1
Terminal
Loading...

Question # 1

Calculate the running total of the revenue generated per week for the first 10 weeks for the movie Avengers.

Running total provides the summation of the current value and the previous ones and shows the periodic growth of a value. It changes whenever a new value is added. Taking the MovieScreening table into account, for each date, the running total is equal to the previous running total plus the current revenue.

Running total can be easily calculated using the analytical window function SUM() OVER() that has been made available in MySQL 8.0. For previous versions, the same functionality can be emulated using INNER JOINs.

To calculate the running total, we need the MovieId, Weekend, and RevenueInMillions columns from the MovieScreening table. The running total will be calculated using the RevenueInMillions column. The data required to answer this question is shown in the query below:

SELECT Weekend, RevenueInMillions
FROM MovieScreening
WHERE MovieId = 10
ORDER BY Weekend;

To calculate the running total of the revenue, we need to add the revenue of the current row with the previously calculated sum. This can be achieved by joining the MovieScreening table with itself. Since we only need a handful of rows from the MovieScreening table, we will add a join condition on MovieId to ensure that only rows for the movie Avengers are considered:< ON T1.MovieId = T2.MovieId WHERE T1.MovieId = 10

But that alone won’t serve our purpose. We need another join condition to consider only those rows preceding the current row: T1.Weekend >= T2.Weekend

SELECT T1.Weekend, T2.Weekend, T1.RevenueInMillions, T2.RevenueInMillions

FROM MovieScreening T1 
INNER JOIN MovieScreening T2

ON T1.MovieId = T2.MovieId
AND T1.Weekend >= T2.Weekend

WHERE T1.MovieId = 10
ORDER BY T1.Weekend, T1.RevenueInMillions;

The output of the above query matches every date to all the preceding dates for the same MovieId. This is shown by the date in the first column being greater than or equal to the date in the second column. The running total will be calculated by adding up the values in the fourth column as shown below:

The final step is applying the SUM function to the appropriate group of rows. The SUM function is used with T2.RevenueInMillions as this column lists all the values for the RevenueInMillion column of the prior rows. The final query is:

SELECT T1.Weekend, T1.RevenueInMillions, SUM(T2.RevenueInMillions) AS RunningTotal

FROM MovieScreening T1 
INNER JOIN MovieScreening T2

ON T1.MovieId = T2.MovieId
AND T1.Weekend >= T2.Weekend

WHERE T1.MovieId = 10
GROUP BY T1.Weekend, T1.RevenueInMillions
ORDER BY T1.Weekend, T1.RevenueInMillions;

For the sake of thoroughness, we will show how to accomplish the above task using analytic window function SUM () OVER(). The OVER() clause indicates that this is a window function and not a simple aggregate function.

SELECT Weekend, RevenueInMillions, 
       SUM( RevenueInMillions) OVER (ORDER BY Weekend) AS RunningTotal
FROM MovieScreening
GROUP BY Weekend, RevenueInMillions
ORDER BY Weekend;

We specify an ORDER BY element in OVER() ...

Access this course and 1400+ top-rated courses and projects.