MariaDB [sakila]> explain SELECT
    ->    p.customer_id AS customer,
    ->    sum(p.amount) AS "Total Payment",
    ->    sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment"
    -> FROM payment as p
    -> GROUP BY p.customer_id;
+------+-------------+-------+-------+---------------+--------------------+---------+------+-------+-------+
| id   | select_type | table | type  | possible_keys | key                | key_len | ref  | rows  | Extra |
+------+-------------+-------+-------+---------------+--------------------+---------+------+-------+-------+
|    1 | SIMPLE      | p     | index | NULL          | idx_fk_customer_id | 2       | NULL | 16086 |       |
+------+-------------+-------+-------+---------------+--------------------+---------+------+-------+-------+
1 row in set (0.002 sec)

MariaDB [sakila]> SELECT * FROM customer_payment LIMIT 20;
+----------+---------------+---------------------+
| customer | Total Payment | Film Rental Payment |
+----------+---------------+---------------------+
|        1 |        118.68 |              118.68 |
|        2 |        128.73 |              128.73 |
|        3 |        135.74 |              135.74 |
|        4 |         81.78 |               81.78 |
|        5 |        144.62 |              144.62 |
|        6 |         93.72 |               93.72 |
|        7 |        151.67 |              151.67 |
|        8 |         92.76 |               92.76 |
|        9 |         89.77 |               89.77 |
|       10 |         99.75 |               99.75 |
|       11 |        106.76 |              106.76 |
|       12 |        103.72 |              103.72 |
|       13 |        131.73 |              131.73 |
|       14 |        117.72 |              117.72 |
|       15 |        134.68 |              134.68 |
|       16 |        120.71 |              118.72 |
|       17 |         98.79 |               98.79 |
|       18 |         91.78 |               91.78 |
|       19 |        125.76 |              125.76 |
|       20 |        115.70 |              115.70 |
+----------+---------------+---------------------+
20 rows in set (0.033 sec)

MariaDB [sakila]> SELECT * 
    -> FROM (SELECT
    ->    p.customer_id AS customer,
    ->    sum(p.amount) AS "Total Payment",
    ->    sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment"
    -> FROM payment as p
    -> GROUP BY p.customer_id ) AS s
    -> LIMIT 20;
+----------+---------------+---------------------+
| customer | Total Payment | Film Rental Payment |
+----------+---------------+---------------------+
|        1 |        118.68 |              118.68 |
|        2 |        128.73 |              128.73 |
|        3 |        135.74 |              135.74 |
|        4 |         81.78 |               81.78 |
|        5 |        144.62 |              144.62 |
|        6 |         93.72 |               93.72 |
|        7 |        151.67 |              151.67 |
|        8 |         92.76 |               92.76 |
|        9 |         89.77 |               89.77 |
|       10 |         99.75 |               99.75 |
|       11 |        106.76 |              106.76 |
|       12 |        103.72 |              103.72 |
|       13 |        131.73 |              131.73 |
|       14 |        117.72 |              117.72 |
|       15 |        134.68 |              134.68 |
|       16 |        120.71 |              118.72 |
|       17 |         98.79 |               98.79 |
|       18 |         91.78 |               91.78 |
|       19 |        125.76 |              125.76 |
|       20 |        115.70 |              115.70 |
+----------+---------------+---------------------+
20 rows in set (0.012 sec)

MariaDB [sakila]> CREATE TEMPORAREY TABLE t
    -> AS SELECT
    ->    p.customer_id AS customer,
    ->    sum(p.amount) AS "Total Payment",
    ->    sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment"
    -> FROM payment as p
    -> GROUP BY p.customer_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TEMPORAREY TABLE t
AS SELECT
   p.customer_id AS customer,
   sum(p.amount) AS "' at line 1
MariaDB [sakila]> 
MariaDB [sakila]> CREATE TEMPORARY TABLE t
    -> AS SELECT
    ->    p.customer_id AS customer,
    ->    sum(p.amount) AS "Total Payment",
    ->    sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment"
    -> FROM payment as p
    -> GROUP BY p.customer_id;
Query OK, 599 rows affected (0.029 sec)
Records: 599  Duplicates: 0  Warnings: 0

MariaDB [sakila]> SELECT * FROM t;
+----------+---------------+---------------------+
| customer | Total Payment | Film Rental Payment |
+----------+---------------+---------------------+
|        1 |        118.68 |              118.68 |
|        2 |        128.73 |              128.73 |
|        3 |        135.74 |              135.74 |
|        4 |         81.78 |               81.78 |
|        5 |        144.62 |              144.62 |
|        6 |         93.72 |               93.72 |
|        7 |        151.67 |              151.67 |
|        8 |         92.76 |               92.76 |
|        9 |         89.77 |               89.77 |
|       10 |         99.75 |               99.75 |
|       11 |        106.76 |              106.76 |
|       12 |        103.72 |              103.72 |
|       13 |        131.73 |              131.73 |
|       14 |        117.72 |              117.72 |
|       15 |        134.68 |              134.68 |
|       16 |        120.71 |              118.72 |
|       17 |         98.79 |               98.79 |
|       18 |         91.78 |               91.78 |
|       19 |        125.76 |              125.76 |
|       20 |        115.70 |              115.70 |
|       21 |        155.65 |              155.65 |
|       22 |        113.78 |              113.78 |
|       23 |        119.70 |              119.70 |
|       24 |         95.75 |               95.75 |
|       25 |        115.71 |              115.71 |
|       26 |        152.66 |              152.66 |
|       27 |        126.69 |              126.69 |
|       28 |        111.68 |              111.68 |
|       29 |        140.64 |              140.64 |
|       30 |        123.66 |              123.66 |
|       31 |        104.74 |              104.74 |
|       32 |        127.71 |              127.71 |
|       33 |         91.79 |               91.79 |
|       34 |         89.76 |               89.76 |
|       35 |        129.68 |              129.68 |
|       36 |         92.73 |               92.73 |
|       37 |         95.77 |               95.77 |
|       38 |        127.66 |              127.66 |
|       39 |        141.71 |              141.71 |
|       40 |        110.73 |              110.73 |
|       41 |        118.75 |              118.75 |
|       42 |        117.70 |              117.70 |
|       43 |         99.76 |               99.76 |
|       44 |        114.74 |              114.74 |
|       45 |        127.73 |              127.73 |
|       46 |        142.66 |              142.66 |
|       47 |        108.78 |              108.78 |
|       48 |         76.83 |               76.83 |
|       49 |        130.72 |              130.72 |
|       50 |        169.65 |              169.65 |
|       51 |        138.67 |              138.67 |
|       52 |        107.71 |              107.71 |
|       53 |        115.70 |              115.70 |
|       54 |        128.71 |              128.71 |
|       55 |        100.78 |              100.78 |
|       56 |        135.70 |              135.70 |
|       57 |        114.72 |              114.72 |
|       58 |        115.73 |              115.73 |
|       59 |        133.73 |              133.73 |
|       60 |         98.75 |               98.75 |
|       61 |         58.86 |               58.86 |
|       62 |         84.77 |               84.77 |
|       63 |        112.75 |              112.75 |
|       64 |        100.67 |              100.67 |
|       65 |        103.78 |              103.78 |
|       66 |        144.66 |              144.66 |
|       67 |         99.78 |               99.78 |
|       68 |         94.78 |               94.78 |
|       69 |         96.75 |               96.75 |
|       70 |         80.82 |               80.82 |
|       71 |        129.70 |              129.70 |
|       72 |         99.70 |               99.70 |
|       73 |         97.76 |               97.76 |
|       74 |        103.73 |              103.73 |
|       75 |        155.59 |              155.59 |
|       76 |         77.77 |               77.77 |
|       77 |        100.72 |              100.72 |
|       78 |        141.69 |              141.69 |
|       79 |         84.78 |               84.78 |
|       80 |        137.70 |              137.70 |
|       81 |         93.78 |               93.78 |
|       82 |        130.74 |              130.74 |
|       83 |        101.75 |              101.75 |
|       84 |        141.67 |              141.67 |
|       85 |         87.77 |               87.77 |
|       86 |        148.67 |              148.67 |
|       87 |        145.70 |              145.70 |
|       88 |         87.79 |               87.79 |
|       89 |        134.68 |              134.68 |
|       90 |        110.72 |              110.72 |
|       91 |        113.65 |              113.65 |
|       92 |        133.72 |              133.72 |
|       93 |         91.77 |               91.77 |
|       94 |         79.79 |               79.79 |
|       95 |         77.82 |               77.82 |
|       96 |        105.73 |              105.73 |
|       97 |         58.82 |               58.82 |
|       98 |        106.75 |              106.75 |
|       99 |         91.76 |               91.76 |
|      100 |        102.76 |              102.76 |
|      101 |         96.76 |               96.76 |
|      102 |        137.67 |              137.67 |
|      103 |        146.69 |              146.69 |
|      104 |         92.76 |               92.76 |
|      105 |        120.74 |              120.74 |
|      106 |        100.77 |              100.77 |
|      107 |        126.70 |              126.70 |
|      108 |        132.70 |              132.70 |
|      109 |        107.74 |              107.74 |
|      110 |         59.86 |               59.86 |
|      111 |         97.74 |               97.74 |
|      112 |        133.70 |              133.70 |
|      113 |        113.71 |              113.71 |
|      114 |        139.67 |              139.67 |
|      115 |         91.70 |               91.70 |
|      116 |        111.73 |              111.73 |
|      117 |         71.81 |               71.81 |
|      118 |         82.79 |               82.79 |
|      119 |        153.66 |              153.66 |
|      120 |        143.68 |              143.68 |
|      121 |        109.74 |              109.74 |
|      122 |        126.68 |              126.68 |
|      123 |        100.76 |              100.76 |
|      124 |         73.82 |               73.82 |
|      125 |        135.68 |              135.68 |
|      126 |        117.72 |              117.72 |
|      127 |        107.76 |              107.76 |
|      128 |        127.68 |              127.68 |
|      129 |        124.66 |              124.66 |
|      130 |         93.76 |               93.76 |
|      131 |        128.70 |              128.70 |
|      132 |         95.72 |               95.72 |
|      133 |         98.73 |               98.73 |
|      134 |         94.77 |               94.77 |
|      135 |        110.70 |              110.70 |
|      136 |         62.85 |               62.85 |
|      137 |        194.61 |              194.61 |
|      138 |        110.66 |              110.66 |
|      139 |        113.73 |              113.73 |
|      140 |         83.82 |               83.82 |
|      141 |        130.68 |              130.68 |
|      142 |         94.74 |               94.74 |
|      143 |         89.78 |               89.78 |
|      144 |        195.58 |              195.58 |
|      145 |        108.72 |              108.72 |
|      146 |        139.71 |              139.71 |
|      147 |        127.66 |              127.66 |
|      148 |        216.54 |              216.54 |
|      149 |        121.74 |              121.74 |
|      150 |        105.75 |              105.75 |
|      151 |         92.73 |               92.73 |
|      152 |         83.79 |               83.79 |
|      153 |         94.76 |               94.76 |
|      154 |        130.70 |              130.70 |
|      155 |        109.75 |              109.75 |
|      156 |        101.75 |              101.75 |
|      157 |        113.69 |              113.69 |
|      158 |        126.68 |              126.68 |
|      159 |         68.79 |               68.79 |
|      160 |        106.73 |              106.73 |
|      161 |        112.70 |              112.70 |
|      162 |         71.80 |               71.80 |
|      163 |        122.71 |              122.71 |
|      164 |         66.84 |               66.84 |
|      165 |         86.79 |               86.79 |
|      166 |        123.72 |              123.72 |
|      167 |        119.68 |              119.68 |
|      168 |        135.66 |              135.66 |
|      169 |         95.78 |               95.78 |
|      170 |        119.74 |              119.74 |
|      171 |        114.74 |              114.74 |
|      172 |        145.67 |              145.67 |
|      173 |        119.71 |              119.71 |
|      174 |         92.79 |               92.79 |
|      175 |         98.76 |               98.76 |
|      176 |        173.63 |              173.63 |
|      177 |         71.77 |               71.77 |
|      178 |        194.61 |              194.61 |
|      179 |        133.71 |              133.71 |
|      180 |         98.77 |               98.77 |
|      181 |        174.66 |              174.66 |
|      182 |         97.74 |               97.74 |
|      183 |         76.77 |               76.77 |
|      184 |         90.77 |               90.77 |
|      185 |         84.77 |               84.77 |
|      186 |        114.69 |              114.69 |
|      187 |        159.72 |              159.72 |
|      188 |         92.75 |               92.75 |
|      189 |         93.78 |               93.78 |
|      190 |        110.73 |              110.73 |
|      191 |         74.80 |               74.80 |
|      192 |         99.77 |               99.77 |
|      193 |        109.75 |              109.75 |
|      194 |         87.82 |               87.82 |
|      195 |         86.81 |               86.81 |
|      196 |        151.65 |              151.65 |
|      197 |        154.60 |              154.60 |
|      198 |        141.63 |              141.63 |
|      199 |        103.75 |              103.75 |
|      200 |        136.73 |              136.73 |
|      201 |        108.75 |              108.75 |
|      202 |        103.74 |              103.74 |
|      203 |         88.80 |               88.80 |
|      204 |        147.65 |              147.65 |
|      205 |         80.82 |               80.82 |
|      206 |        126.73 |              126.73 |
|      207 |        137.66 |              137.66 |
|      208 |         91.74 |               91.74 |
|      209 |        161.68 |              161.68 |
|      210 |        137.69 |              137.69 |
|      211 |        151.66 |              151.66 |
|      212 |         91.80 |               91.80 |
|      213 |        111.72 |              111.72 |
|      214 |        116.69 |              116.69 |
|      215 |         91.74 |               91.74 |
|      216 |         95.77 |               95.77 |
|      217 |         98.77 |               98.77 |
|      218 |         67.82 |               67.82 |
|      219 |        101.74 |              101.74 |
|      220 |        114.73 |              114.73 |
|      221 |        132.72 |              132.72 |
|      222 |         91.79 |               91.79 |
|      223 |         80.83 |               80.83 |
|      224 |         76.78 |               76.78 |
|      225 |        120.74 |              120.74 |
|      226 |         87.79 |               87.79 |
|      227 |         87.76 |               87.76 |
|      228 |         92.73 |               92.73 |
|      229 |         93.75 |               93.75 |
|      230 |        124.67 |              124.67 |
|      231 |        110.74 |              110.74 |
|      232 |         95.75 |               95.75 |
|      233 |         89.77 |               89.77 |
|      234 |        111.74 |              111.74 |
|      235 |         93.75 |               93.75 |
|      236 |        175.58 |              175.58 |
|      237 |        144.67 |              144.67 |
|      238 |         94.79 |               94.79 |
|      239 |        142.66 |              142.66 |
|      240 |        117.74 |              117.74 |
|      241 |        122.66 |              122.66 |
|      242 |        140.68 |              140.68 |
|      243 |         82.76 |               82.76 |
|      244 |        129.68 |              129.68 |
|      245 |        132.68 |              132.68 |
|      246 |        107.77 |              107.77 |
|      247 |        104.78 |              104.78 |
|      248 |         50.85 |               50.85 |
|      249 |        107.77 |              107.77 |
|      250 |         73.80 |               73.80 |
|      251 |        120.69 |              120.69 |
|      252 |         64.78 |               64.78 |
|      253 |        127.71 |              127.71 |
|      254 |        116.68 |              116.68 |
|      255 |         70.82 |               70.82 |
|      256 |        112.70 |              112.70 |
|      257 |        151.63 |              151.63 |
|      258 |        110.76 |              110.76 |
|      259 |        170.67 |              168.68 |
|      260 |        122.69 |              122.69 |
|      261 |        107.74 |              107.74 |
|      262 |        119.72 |              119.72 |
|      263 |        122.71 |              122.71 |
|      264 |         98.75 |               98.75 |
|      265 |        133.71 |              133.71 |
|      266 |        118.72 |              118.72 |
|      267 |        159.64 |              159.64 |
|      268 |        123.71 |              123.71 |
|      269 |        129.70 |              129.70 |
|      270 |        104.75 |              104.75 |
|      271 |         68.82 |               68.82 |
|      272 |         98.80 |               98.80 |
|      273 |        157.65 |              157.65 |
|      274 |        152.65 |              152.65 |
|      275 |        108.70 |              108.70 |
|      276 |        122.72 |              122.72 |
|      277 |        144.68 |              144.68 |
|      278 |         88.74 |               88.74 |
|      279 |        135.69 |              135.69 |
|      280 |        118.73 |              118.73 |
|      281 |         50.86 |               50.86 |
|      282 |        103.73 |              103.73 |
|      283 |         86.72 |               86.72 |
|      284 |        126.72 |              126.72 |
|      285 |        135.74 |              135.74 |
|      286 |        117.73 |              117.73 |
|      287 |        115.71 |              115.71 |
|      288 |         80.76 |               80.76 |
|      289 |        142.70 |              142.70 |
|      290 |        109.72 |              109.72 |
|      291 |         95.77 |               95.77 |
|      292 |        118.73 |              118.73 |
|      293 |        158.69 |              158.69 |
|      294 |        113.74 |              113.74 |
|      295 |        162.62 |              162.62 |
|      296 |        129.70 |              129.70 |
|      297 |        128.67 |              128.67 |
|      298 |        101.74 |              101.74 |
|      299 |        131.70 |              131.70 |
|      300 |        137.69 |              137.69 |
|      301 |         92.79 |               92.79 |
|      302 |        127.71 |              127.71 |
|      303 |        100.74 |              100.74 |
|      304 |        115.74 |              115.74 |
|      305 |        109.75 |              109.75 |
|      306 |        138.68 |              138.68 |
|      307 |        134.70 |              134.70 |
|      308 |        105.75 |              105.75 |
|      309 |        147.69 |              147.69 |
|      310 |         97.80 |               97.80 |
|      311 |        120.77 |              120.77 |
|      312 |        104.74 |              104.74 |
|      313 |         89.77 |               89.77 |
|      314 |        131.67 |              131.67 |
|      315 |         84.83 |               84.83 |
|      316 |        132.71 |              132.71 |
|      317 |        114.72 |              114.72 |
|      318 |         52.88 |               52.88 |
|      319 |        132.70 |              132.70 |
|      320 |         71.80 |               71.80 |
|      321 |        116.78 |              116.78 |
|      322 |        128.72 |              128.72 |
|      323 |        114.69 |              114.69 |
|      324 |        103.75 |              103.75 |
|      325 |         91.78 |               91.78 |
|      326 |         96.75 |               96.75 |
|      327 |        112.74 |              112.74 |
|      328 |         90.77 |               90.77 |
|      329 |        127.68 |              127.68 |
|      330 |         94.75 |               94.75 |
|      331 |        122.73 |              122.73 |
|      332 |        118.72 |              118.72 |
|      333 |        109.73 |              109.73 |
|      334 |        135.70 |              135.70 |
|      335 |         78.77 |               78.77 |
|      336 |        119.70 |              119.70 |
|      337 |        143.71 |              143.71 |
|      338 |        114.72 |              114.72 |
|      339 |        127.70 |              127.70 |
|      340 |        119.69 |              119.69 |
|      341 |        106.77 |              106.77 |
|      342 |        130.68 |              130.68 |
|      343 |        114.75 |              114.75 |
|      344 |         73.82 |               73.82 |
|      345 |        106.77 |              106.77 |
|      346 |        155.68 |              155.68 |
|      347 |        142.70 |              142.70 |
|      348 |        146.64 |              146.64 |
|      349 |        138.71 |              138.71 |
|      350 |         73.77 |               73.77 |
|      351 |         89.76 |               89.76 |
|      352 |         99.77 |               99.77 |
|      353 |         72.82 |               72.82 |
|      354 |        129.64 |              129.64 |
|      355 |         72.80 |               72.80 |
|      356 |        121.70 |              121.70 |
|      357 |         89.74 |               89.74 |
|      358 |         80.79 |               80.79 |
|      359 |        101.75 |              101.75 |
|      360 |        150.66 |              150.66 |
|      361 |        100.69 |              100.69 |
|      362 |        145.68 |              145.68 |
|      363 |        143.71 |              143.71 |
|      364 |         95.77 |               95.77 |
|      365 |        103.77 |              103.77 |
|      366 |        152.63 |              152.63 |
|      367 |        101.78 |              101.78 |
|      368 |        157.65 |              157.65 |
|      369 |         88.75 |               88.75 |
|      370 |         70.81 |               70.81 |
|      371 |        149.65 |              149.65 |
|      372 |        158.66 |              158.66 |
|      373 |        161.65 |              161.65 |
|      374 |        103.72 |              103.72 |
|      375 |        110.76 |              110.76 |
|      376 |        110.72 |              110.72 |
|      377 |         93.74 |               93.74 |
|      378 |         71.81 |               71.81 |
|      379 |        106.77 |              106.77 |
|      380 |        136.64 |              136.64 |
|      381 |        106.65 |              106.65 |
|      382 |         91.76 |               91.76 |
|      383 |        103.73 |              103.73 |
|      384 |         89.75 |               89.75 |
|      385 |        101.74 |              101.74 |
|      386 |        117.71 |              117.71 |
|      387 |        101.74 |              101.74 |
|      388 |        124.70 |              124.70 |
|      389 |        124.74 |              124.74 |
|      390 |        152.67 |              152.67 |
|      391 |        104.70 |              104.70 |
|      392 |         91.77 |               91.77 |
|      393 |        121.69 |              121.69 |
|      394 |         84.78 |               84.78 |
|      395 |         57.81 |               57.81 |
|      396 |        111.73 |              111.73 |
|      397 |        105.71 |              105.71 |
|      398 |         78.84 |               78.84 |
|      399 |         91.79 |               91.79 |
|      400 |        124.72 |              124.72 |
|      401 |         74.78 |               73.79 |
|      402 |         79.80 |               79.80 |
|      403 |        166.65 |              166.65 |
|      404 |        139.70 |              139.70 |
|      405 |        109.68 |              109.68 |
|      406 |        122.68 |              122.68 |
|      407 |        112.73 |              112.73 |
|      408 |        116.70 |              116.70 |
|      409 |        104.77 |              104.77 |
|      410 |        167.62 |              167.62 |
|      411 |         80.74 |               80.74 |
|      412 |         85.79 |               85.79 |
|      413 |         75.79 |               75.79 |
|      414 |        105.75 |              105.75 |
|      415 |         93.77 |               93.77 |
|      416 |        121.69 |              121.69 |
|      417 |        110.75 |              110.75 |
|      418 |        107.70 |              107.70 |
|      419 |         89.75 |               89.75 |
|      420 |         79.79 |               79.79 |
|      421 |        119.73 |              119.73 |
|      422 |         97.74 |               97.74 |
|      423 |        120.74 |              120.74 |
|      424 |        110.70 |              110.70 |
|      425 |        104.74 |              104.74 |
|      426 |        126.73 |              126.73 |
|      427 |        114.76 |              114.76 |
|      428 |         78.80 |               78.80 |
|      429 |        105.79 |              105.79 |
|      430 |         79.78 |               79.78 |
|      431 |         95.77 |               95.77 |
|      432 |        116.77 |              116.77 |
|      433 |        133.75 |              133.75 |
|      434 |        128.73 |              128.73 |
|      435 |         91.75 |               91.75 |
|      436 |        144.70 |              144.70 |
|      437 |         99.77 |               99.77 |
|      438 |        134.67 |              134.67 |
|      439 |        151.64 |              151.64 |
|      440 |         88.78 |               88.78 |
|      441 |        112.72 |              112.72 |
|      442 |        118.68 |              118.68 |
|      443 |         98.78 |               98.78 |
|      444 |        115.70 |              115.70 |
|      445 |        102.74 |              102.74 |
|      446 |        116.69 |              116.69 |
|      447 |        113.71 |              113.71 |
|      448 |        135.71 |              135.71 |
|      449 |         99.80 |               99.80 |
|      450 |         89.74 |               89.74 |
|      451 |        128.67 |              128.67 |
|      452 |        107.68 |              107.68 |
|      453 |        111.77 |              111.77 |
|      454 |        151.67 |              151.67 |
|      455 |         87.76 |               87.76 |
|      456 |        100.75 |              100.75 |
|      457 |        114.72 |              114.72 |
|      458 |         66.81 |               66.81 |
|      459 |        186.62 |              186.62 |
|      460 |        109.75 |              109.75 |
|      461 |         97.72 |               97.72 |
|      462 |        159.67 |              159.67 |
|      463 |         91.75 |               91.75 |
|      464 |         73.84 |               73.84 |
|      465 |         69.83 |               69.83 |
|      466 |        104.77 |              104.77 |
|      467 |        139.71 |              139.71 |
|      468 |        175.61 |              175.61 |
|      469 |        177.60 |              177.60 |
|      470 |        160.68 |              160.68 |
|      471 |        109.73 |              109.73 |
|      472 |        141.70 |              141.70 |
|      473 |        134.66 |              134.66 |
|      474 |        100.74 |              100.74 |
|      475 |        103.76 |              103.76 |
|      476 |         95.78 |               95.78 |
|      477 |        109.78 |              109.78 |
|      478 |         86.81 |               86.81 |
|      479 |        146.69 |              146.69 |
|      480 |        101.78 |              101.78 |
|      481 |        115.71 |              115.71 |
|      482 |        138.71 |              138.71 |
|      483 |         88.82 |               88.82 |
|      484 |        139.70 |              139.70 |
|      485 |        115.71 |              115.71 |
|      486 |        113.74 |              113.74 |
|      487 |        119.74 |              119.74 |
|      488 |         95.78 |               95.78 |
|      489 |         99.79 |               99.79 |
|      490 |        117.76 |              117.76 |
|      491 |        124.73 |              124.73 |
|      492 |         65.84 |               65.84 |
|      493 |        112.77 |              112.77 |
|      494 |        140.69 |              140.69 |
|      495 |        120.74 |              120.74 |
|      496 |         88.79 |               88.79 |
|      497 |        129.72 |              129.72 |
|      498 |         97.73 |               97.73 |
|      499 |        123.70 |              123.70 |
|      500 |        115.72 |              115.72 |
|      501 |         89.79 |               89.79 |
|      502 |        138.66 |              138.66 |
|      503 |        115.68 |              115.68 |
|      504 |        133.72 |              133.72 |
|      505 |         97.79 |               97.79 |
|      506 |        152.65 |              152.65 |
|      507 |         95.75 |               95.75 |
|      508 |        127.75 |              127.75 |
|      509 |        100.80 |              100.80 |
|      510 |        122.74 |              122.74 |
|      511 |         99.76 |               99.76 |
|      512 |        115.74 |              115.74 |
|      513 |        148.69 |              148.69 |
|      514 |         74.78 |               74.78 |
|      515 |        126.72 |              126.72 |
|      516 |         98.74 |               98.74 |
|      517 |        105.75 |              105.75 |
|      518 |        111.74 |              111.74 |
|      519 |        103.77 |              103.77 |
|      520 |        134.68 |              134.68 |
|      521 |         80.77 |               80.77 |
|      522 |        167.67 |              167.67 |
|      523 |         93.78 |               93.78 |
|      524 |         87.81 |               87.81 |
|      525 |         74.81 |               74.81 |
|      526 |        221.55 |              221.55 |
|      527 |         81.76 |               81.76 |
|      528 |        111.75 |              111.75 |
|      529 |        118.71 |              118.71 |
|      530 |         76.77 |               76.77 |
|      531 |        118.75 |              118.75 |
|      532 |        152.68 |              152.68 |
|      533 |        141.67 |              141.67 |
|      534 |         88.76 |               88.76 |
|      535 |        135.68 |              135.68 |
|      536 |        117.75 |              117.75 |
|      537 |        103.75 |              103.75 |
|      538 |        117.71 |              117.71 |
|      539 |         90.78 |               90.78 |
|      540 |        112.76 |              112.76 |
|      541 |        108.76 |              108.76 |
|      542 |         94.82 |               94.82 |
|      543 |         82.78 |               82.78 |
|      544 |         98.78 |               98.78 |
|      545 |         89.79 |               89.79 |
|      546 |        107.74 |              103.75 |
|      547 |         99.77 |               99.77 |
|      548 |         79.81 |               79.81 |
|      549 |         77.80 |               77.80 |
|      550 |        159.68 |              159.68 |
|      551 |         96.74 |               96.74 |
|      552 |         90.79 |               90.79 |
|      553 |        107.76 |              107.76 |
|      554 |        101.78 |              101.78 |
|      555 |         74.83 |               74.83 |
|      556 |         83.79 |               83.79 |
|      557 |         73.76 |               73.76 |
|      558 |        135.72 |              135.72 |
|      559 |        110.72 |              110.72 |
|      560 |        132.70 |              132.70 |
|      561 |         96.73 |               96.73 |
|      562 |        109.75 |              109.75 |
|      563 |        110.71 |              110.71 |
|      564 |         91.76 |               91.76 |
|      565 |        126.71 |              126.71 |
|      566 |        141.66 |              141.66 |
|      567 |         85.80 |               85.80 |
|      568 |         66.79 |               66.79 |
|      569 |        134.68 |              134.68 |
|      570 |         99.74 |               99.74 |
|      571 |        121.76 |              121.76 |
|      572 |        108.75 |              108.75 |
|      573 |        120.71 |              120.71 |
|      574 |        109.72 |              109.72 |
|      575 |        126.71 |              126.71 |
|      576 |        139.66 |              139.66 |
|      577 |        118.72 |              117.73 |
|      578 |         96.78 |               96.78 |
|      579 |        111.73 |              111.73 |
|      580 |         99.73 |               99.73 |
|      581 |        107.73 |              107.73 |
|      582 |        113.75 |              113.75 |
|      583 |        117.77 |              117.77 |
|      584 |        129.70 |              129.70 |
|      585 |        117.76 |              117.76 |
|      586 |         64.81 |               64.81 |
|      587 |        108.74 |              108.74 |
|      588 |        115.71 |              115.71 |
|      589 |        129.72 |              129.72 |
|      590 |        112.75 |              112.75 |
|      591 |        134.73 |              134.73 |
|      592 |        111.71 |              111.71 |
|      593 |        113.74 |              113.74 |
|      594 |        130.73 |              130.73 |
|      595 |        117.70 |              117.70 |
|      596 |         96.72 |               96.72 |
|      597 |         99.75 |               99.75 |
|      598 |         83.78 |               83.78 |
|      599 |         83.81 |               83.81 |
+----------+---------------+---------------------+
599 rows in set (0.001 sec)

MariaDB [sakila]> SELECT * FROM t LIMIT 20;
+----------+---------------+---------------------+
| customer | Total Payment | Film Rental Payment |
+----------+---------------+---------------------+
|        1 |        118.68 |              118.68 |
|        2 |        128.73 |              128.73 |
|        3 |        135.74 |              135.74 |
|        4 |         81.78 |               81.78 |
|        5 |        144.62 |              144.62 |
|        6 |         93.72 |               93.72 |
|        7 |        151.67 |              151.67 |
|        8 |         92.76 |               92.76 |
|        9 |         89.77 |               89.77 |
|       10 |         99.75 |               99.75 |
|       11 |        106.76 |              106.76 |
|       12 |        103.72 |              103.72 |
|       13 |        131.73 |              131.73 |
|       14 |        117.72 |              117.72 |
|       15 |        134.68 |              134.68 |
|       16 |        120.71 |              118.72 |
|       17 |         98.79 |               98.79 |
|       18 |         91.78 |               91.78 |
|       19 |        125.76 |              125.76 |
|       20 |        115.70 |              115.70 |
+----------+---------------+---------------------+
20 rows in set (0.000 sec)

MariaDB [sakila]> use supply;
Database changed
MariaDB [supply]>    SELECT COUNT(*) INTO numberPartsSupplied
    ->    FROM Supply S
    ->    WHERE S.SNUM = snum;
ERROR 1327 (42000): Undeclared variable: numberPartsSupplied
MariaDB [supply]> 
MariaDB [supply]> SELECT * FROM Supply;
+------+------+----------+
| SNUM | PNUM | QUANTITY |
+------+------+----------+
| S1   | P1   |       10 |
| S1   | P2   |        3 |
| S2   | P1   |       11 |
| S2   | P2   |        1 |
| S2   | P4   |        6 |
| S3   | P4   |        1 |
| S3   | P5   |        2 |
| S3   | P6   |       12 |
| S3   | P7   |        5 |
| S4   | P2   |        1 |
| S4   | P5   |       10 |
| S4   | P7   |        4 |
| S4   | P8   |       10 |
| S5   | P1   |       11 |
| S5   | P3   |        5 |
| S5   | P4   |       10 |
| S5   | P5   |       14 |
+------+------+----------+
17 rows in set (0.005 sec)

MariaDB [supply]> use sakila;
Database changed
MariaDB [sakila]> 
MariaDB [sakila]> DROP FUNCTION num_same_film_rented;
ERROR 1305 (42000): FUNCTION sakila.num_same_film_rented does not exist
MariaDB [sakila]> 
MariaDB [sakila]> DELIMITER ##
MariaDB [sakila]> 
MariaDB [sakila]> -- define the delimiter to $$
MariaDB [sakila]> 
MariaDB [sakila]> CREATE FUNCTION num_same_film_rented(cid_1 INT, cid_2 INT) RETURNS FLOAT
    -> READS SQL DATA
    -> BEGIN
    ->    DECLARE result INTEGER DEFAULT 0;
    ->   
    ->    SELECT COUNT(DISTINCT i1.film_id) INTO result
    ->    FROM rental r1, rental r2, inventory i1, inventory i2
    ->    WHERE r1.inventory_id = i1.inventory_id
    ->    AND r2.inventory_id = i2.inventory_id
    ->    AND i1.film_id = i2.film_id
    ->    AND r1.customer_id = cid_1
    ->    AND r2.customer_id = cid_2;
    -> 
    ->    return result;
    -> END ##
Query OK, 0 rows affected (0.016 sec)

MariaDB [sakila]> 
MariaDB [sakila]> DELIMITER ;
MariaDB [sakila]> 
MariaDB [sakila]> SELECT num_same_film_rented(1, 558);
+------------------------------+
| num_same_film_rented(1, 558) |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.102 sec)

MariaDB [sakila]> 
MariaDB [sakila]> DROP FUNCTION num_same_film_rented;
Query OK, 0 rows affected (0.012 sec)

MariaDB [sakila]> 
MariaDB [sakila]> DELIMITER ##
MariaDB [sakila]> 
MariaDB [sakila]> -- define the delimiter to $$
MariaDB [sakila]> 
MariaDB [sakila]> CREATE FUNCTION num_same_film_rented(cid_1 INT, cid_2 INT) RETURNS FLOAT
    -> -- parameter passing mode: default: IN only for function.
    -> -- modes: OUT, IN OUT; not allowed in SQL function: side effect.
    -> READS SQL DATA
    -> BEGIN
    ->    DECLARE result INTEGER DEFAULT 0;
    ->   
    ->    SELECT COUNT(DISTINCT i1.film_id) -- INTO result
    ->    FROM rental r1, rental r2, inventory i1, inventory i2
    ->    WHERE r1.inventory_id = i1.inventory_id
    ->    AND r2.inventory_id = i2.inventory_id
    ->    AND i1.film_id = i2.film_id
    ->    AND r1.customer_id = cid_1
    ->    AND r2.customer_id = cid_2;
    -> 
    ->    return result;
    -> END ##
ERROR 1415 (0A000): Not allowed to return a result set from a function
MariaDB [sakila]> 
MariaDB [sakila]> DELIMITER ;
MariaDB [sakila]>    SELECT COUNT(DISTINCT i1.film_id) -- INTO result
    ->    FROM rental r1, rental r2, inventory i1, inventory i2
    ->    WHERE r1.inventory_id = i1.inventory_id
    ->    AND r2.inventory_id = i2.inventory_id
    ->    AND i1.film_id = i2.film_id
    ->    AND r1.customer_id = 1
    ->    AND r2.customer_id = 2;
+----------------------------+
| COUNT(DISTINCT i1.film_id) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.016 sec)

MariaDB [sakila]> DROP PROCEDURE most_similar_customers;
ERROR 1305 (42000): PROCEDURE sakila.most_similar_customers does not exist
MariaDB [sakila]> 
MariaDB [sakila]> DELIMITER $$
MariaDB [sakila]> 
MariaDB [sakila]> CREATE PROCEDURE most_similar_customers(cid INT, min_count INT)
    -> READS SQL DATA
    -> BEGIN
    ->    SELECT r2.customer_id, COUNT(DISTINCT i1.film_id) AS "Number of same rented films"
    ->    FROM rental r1, rental r2, inventory i1, inventory i2
    ->    WHERE r1.inventory_id = i1.inventory_id
    ->    AND r2.inventory_id = i2.inventory_id
    ->    AND i1.film_id = i2.film_id
    ->    AND r1.customer_id = cid
    ->    AND r2.customer_id <> cid
    ->    GROUP BY r2.customer_id
    ->    HAVING COUNT(DISTINCT i1.film_id) >= min_count
    ->    ORDER BY COUNT(DISTINCT i1.film_id) DESC;
    -> END $$
Query OK, 0 rows affected (0.012 sec)

MariaDB [sakila]> 
MariaDB [sakila]> DELIMITER ;
MariaDB [sakila]>  
MariaDB [sakila]> CALL most_similar_customers(1,3);
+-------------+-----------------------------+
| customer_id | Number of same rented films |
+-------------+-----------------------------+
|         406 |                           4 |
|         447 |                           4 |
|         383 |                           4 |
|         155 |                           4 |
|          52 |                           4 |
|         161 |                           3 |
|         189 |                           3 |
|         297 |                           3 |
|         503 |                           3 |
|          56 |                           3 |
|         380 |                           3 |
|           5 |                           3 |
|          59 |                           3 |
|         102 |                           3 |
|          45 |                           3 |
|         277 |                           3 |
|         448 |                           3 |
|         479 |                           3 |
|         253 |                           3 |
|         426 |                           3 |
|         460 |                           3 |
|         173 |                           3 |
|         230 |                           3 |
|         269 |                           3 |
|         372 |                           3 |
|          16 |                           3 |
|         303 |                           3 |
|         375 |                           3 |
|         485 |                           3 |
|         558 |                           3 |
|          28 |                           3 |
|         112 |                           3 |
|         187 |                           3 |
|         513 |                           3 |
+-------------+-----------------------------+
34 rows in set (0.034 sec)

Query OK, 0 rows affected (0.526 sec)

MariaDB [sakila]>