Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


CREATE TABLE theatre
( 
  theatre_id   INTEGER      NOT NULL PRIMARY KEY,
  theatre_name VARCHAR (25) NOT NULL
);

CREATE TABLE


CREATE TABLE show
(
  show_id         INTEGER      NOT NULL PRIMARY KEY,
  show_theatre_id INTEGER      NOT NULL,
  show_name       VARCHAR (25) NOT NULL,
  CONSTRAINT show_theatre_fk FOREIGN KEY (show_theatre_id) REFERENCES theatre (theatre_id)
);

CREATE TABLE


CREATE TABLE ticket
(
  ticket_id SERIAL PRIMARY KEY,
  ticket_show_id INTEGER NOT NULL,
  CONSTRAINT ticket_show_fk FOREIGN KEY (ticket_show_id) REFERENCES show (show_id),
  price DOUBLE PRECISION NOT NULL
  --
  -- Various other fields - seat_no, is_sold, discount... whatever
);


CREATE TABLE


INSERT INTO theatre VALUES (1, 'Theatre_1'), (2, 'Theatre_2'); 

INSERT 0 2


INSERT INTO show (show_id, show_theatre_id, show_name) VALUES 
(10, 1, 'show_10'), (11, 1, 'show_11'), (20, 2, 'show_20'), (21, 2, 'show_21');

INSERT 0 4


INSERT INTO ticket (ticket_show_id, price) VALUES 
(10, 10.0), (10, 10), (10, 10), (10, 10), (10, 10),                     -- 5 tickets for show 10 
(11, 11), (11, 11), (11, 11), (11, 11), (11, 11), (11, 11), (11, 11), -- 7/show 11 &c
(20, 20), (20, 20), (20, 20), (20, 20), (20, 20),                     
(20, 20), (20, 20), (20, 20), (20, 20), (20, 20),                     -- 10
(21, 21), (21, 21), (21, 21),                                         -- 3
(10, 500.56), (10, 500.56), (10, 500.56), (10, 500.56);        -- 4 EXPENSIVE tickets for show_10

INSERT 0 29


SELECT * FROM theatre;


theatre_id
theatre_name


1
Theatre_1

2
Theatre_2



SELECT 2


SELECT * FROM show ORDER BY show_theatre_id, show_id;


show_id
show_theatre_id
show_name


10
1
show_10

11
1
show_11

20
2
show_20

21
2
show_21



SELECT 4


SELECT * FROM ticket t ORDER BY t.ticket_show_id, t.price;


ticket_id
ticket_show_id
price


1
10
10

2
10
10

3
10
10

4
10
10

5
10
10

29
10
500.56

26
10
500.56

27
10
500.56

28
10
500.56

10
11
11

11
11
11

12
11
11

6
11
11

7
11
11

8
11
11

9
11
11

17
20
20

18
20
20

19
20
20

20
20
20

21
20
20

22
20
20

14
20
20

15
20
20

16
20
20

13
20
20

23
21
21

24
21
21

25
21
21



SELECT 29


SELECT 
  th.theatre_id, th.theatre_name AS th_name,
  s.show_id AS s_id, s.show_theatre_id AS s_th, s.show_name AS s_name,
  t.ticket_id AS t_id, t.ticket_show_id AS t_show, t.price AS t_p
FROM 
  theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
ORDER BY th.theatre_id, s.show_id, t.price;


theatre_id
th_name
s_id
s_th
s_name
t_id
t_show
t_p


1
Theatre_1
10
1
show_10
1
10
10

1
Theatre_1
10
1
show_10
2
10
10

1
Theatre_1
10
1
show_10
3
10
10

1
Theatre_1
10
1
show_10
4
10
10

1
Theatre_1
10
1
show_10
5
10
10

1
Theatre_1
10
1
show_10
29
10
500.56

1
Theatre_1
10
1
show_10
26
10
500.56

1
Theatre_1
10
1
show_10
27
10
500.56

1
Theatre_1
10
1
show_10
28
10
500.56

1
Theatre_1
11
1
show_11
10
11
11

1
Theatre_1
11
1
show_11
11
11
11

1
Theatre_1
11
1
show_11
12
11
11

1
Theatre_1
11
1
show_11
6
11
11

1
Theatre_1
11
1
show_11
7
11
11

1
Theatre_1
11
1
show_11
8
11
11

1
Theatre_1
11
1
show_11
9
11
11

2
Theatre_2
20
2
show_20
17
20
20

2
Theatre_2
20
2
show_20
18
20
20

2
Theatre_2
20
2
show_20
19
20
20

2
Theatre_2
20
2
show_20
20
20
20

2
Theatre_2
20
2
show_20
21
20
20

2
Theatre_2
20
2
show_20
22
20
20

2
Theatre_2
20
2
show_20
14
20
20

2
Theatre_2
20
2
show_20
15
20
20

2
Theatre_2
20
2
show_20
16
20
20

2
Theatre_2
20
2
show_20
13
20
20

2
Theatre_2
21
2
show_21
23
21
21

2
Theatre_2
21
2
show_21
24
21
21

2
Theatre_2
21
2
show_21
25
21
21



SELECT 29


SELECT 
    th.theatre_id, 
    th.theatre_name, 
    SUM(SUM(t.price)) OVER (PARTITION BY th.theatre_id ORDER BY theatre_id) 
      AS "Total/theatre",
    s.show_name,
    t.price, 
    SUM(t.price)/t.price AS "No. tickets",
    SUM(SUM(t.price)) OVER (PARTITION BY th.theatre_id, s.show_name ORDER BY theatre_id, s.show_name)
      AS "Total/show"
FROM theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_id, th.theatre_name, s.show_name, t.price
ORDER BY th.theatre_id, s.show_name, t.price;


theatre_id
theatre_name
Total/theatre
show_name
price
No. tickets
Total/show


1
Theatre_1
2129.24
show_10
10
5
2052.24

1
Theatre_1
2129.24
show_10
500.56
4
2052.24

1
Theatre_1
2129.24
show_11
11
7
77

2
Theatre_2
263
show_20
20
10
200

2
Theatre_2
263
show_21
21
3
63



SELECT 5


SELECT
  th.theatre_name,
  SUM(SUM(t.price)) OVER (PARTITION BY th.theatre_id ORDER BY theatre_id) 
    AS "Total/theatre"
FROM theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_id
ORDER BY th.theatre_name;


theatre_name
Total/theatre


Theatre_1
2129.24

Theatre_2
263



SELECT 2


I need to write a query where I need to first count the tickets sold for shows in 
a theater, 

then calculate the average number of tickets sold in each theater and 
to be round it to two decimal place and these values must to be sort by the number 
of tickets sold. 

In principle, I want to find the average number of tickets sold for each theater. 

ERROR:  syntax error at or near "I"
LINE 1: I need to write a query where I need to first count the tick...
        ^


SELECT 
  th.theatre_name AS th_name,
  COUNT(t.ticket_id) AS t_id,
  SUM(t.price),
  ROUND(SUM(t.price)::NUMERIC/COUNT(t.ticket_id), 2) AS "Avg price/ticket"
FROM 
  theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_name
ORDER BY th.theatre_name;


th_name
t_id
sum
Avg price/ticket


Theatre_1
16
2129.24
133.08

Theatre_2
13
263
20.23



SELECT 2


C

ERROR:  syntax error at or near "C"
LINE 1: C
        ^


EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    th.theatre_id, 
    th.theatre_name, 
    SUM(SUM(t.price)) OVER (PARTITION BY th.theatre_id ORDER BY theatre_id) 
      AS "Total/theatre",
    s.show_name,
    t.price, 
    SUM(t.price)/t.price AS "No. tickets",
    SUM(SUM(t.price)) OVER (PARTITION BY th.theatre_id, s.show_name ORDER BY theatre_id, s.show_name)
      AS "Total/show"
FROM theatre th
JOIN show s ON th.theatre_id = s.show_theatre_id
JOIN ticket t ON s.show_id = t.ticket_show_id
GROUP BY th.theatre_id, th.theatre_name, s.show_name, t.price
ORDER BY th.theatre_id, s.show_name, t.price;


QUERY PLAN


WindowAgg  (cost=194.43..323.93 rows=1850 width=176) (actual time=0.168..0.178 rows=5 loops=1)

  Buffers: shared hit=3

  ->  WindowAgg  (cost=194.43..277.68 rows=1850 width=168) (actual time=0.153..0.160 rows=5 loops=1)

        Buffers: shared hit=3

        ->  GroupAggregate  (cost=194.43..245.30 rows=1850 width=160) (actual time=0.130..0.140 rows=5 loops=1)

              Group Key: th.theatre_id, s.show_name, t.price

              Buffers: shared hit=3

              ->  Sort  (cost=194.43..199.05 rows=1850 width=148) (actual time=0.116..0.119 rows=29 loops=1)

                    Sort Key: th.theatre_id, s.show_name, t.price

                    Sort Method: quicksort  Memory: 27kB

                    Buffers: shared hit=3

                    ->  Hash Join  (cost=55.78..94.03 rows=1850 width=148) (actual time=0.072..0.088 rows=29 loops=1)

                          Hash Cond: (s.show_theatre_id = th.theatre_id)

                          Buffers: shared hit=3

                          ->  Hash Join  (cost=27.55..60.93 rows=1850 width=80) (actual time=0.051..0.060 rows=29 loops=1)

                                Hash Cond: (t.ticket_show_id = s.show_id)

                                Buffers: shared hit=2

                                ->  Seq Scan on ticket t  (cost=0.00..28.50 rows=1850 width=12) (actual time=0.005..0.007 rows=29 loops=1)

                                      Buffers: shared hit=1

                                ->  Hash  (cost=17.80..17.80 rows=780 width=76) (actual time=0.040..0.041 rows=4 loops=1)

                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                      Buffers: shared hit=1

                                      ->  Seq Scan on show s  (cost=0.00..17.80 rows=780 width=76) (actual time=0.028..0.030 rows=4 loops=1)

                                            Buffers: shared hit=1

                          ->  Hash  (cost=18.10..18.10 rows=810 width=72) (actual time=0.016..0.017 rows=2 loops=1)

                                Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                Buffers: shared hit=1

                                ->  Seq Scan on theatre th  (cost=0.00..18.10 rows=810 width=72) (actual time=0.007..0.008 rows=2 loops=1)

                                      Buffers: shared hit=1

Planning Time: 0.232 ms

Execution Time: 0.248 ms



EXPLAIN

ticket_id	ticket_show_id	price
1	10	10
2	10	10
3	10	10
4	10	10
5	10	10
29	10	500.56
26	10	500.56
27	10	500.56
28	10	500.56
10	11	11
11	11	11
12	11	11
6	11	11
7	11	11
8	11	11
9	11	11
17	20	20
18	20	20
19	20	20
20	20	20
21	20	20
22	20	20
14	20	20
15	20	20
16	20	20
13	20	20
23	21	21
24	21	21
25	21	21

theatre_id	th_name	s_id	s_th	s_name	t_id	t_show	t_p
1	Theatre_1	10	1	show_10	1	10	10
1	Theatre_1	10	1	show_10	2	10	10
1	Theatre_1	10	1	show_10	3	10	10
1	Theatre_1	10	1	show_10	4	10	10
1	Theatre_1	10	1	show_10	5	10	10
1	Theatre_1	10	1	show_10	29	10	500.56
1	Theatre_1	10	1	show_10	26	10	500.56
1	Theatre_1	10	1	show_10	27	10	500.56
1	Theatre_1	10	1	show_10	28	10	500.56
1	Theatre_1	11	1	show_11	10	11	11
1	Theatre_1	11	1	show_11	11	11	11
1	Theatre_1	11	1	show_11	12	11	11
1	Theatre_1	11	1	show_11	6	11	11
1	Theatre_1	11	1	show_11	7	11	11
1	Theatre_1	11	1	show_11	8	11	11
1	Theatre_1	11	1	show_11	9	11	11
2	Theatre_2	20	2	show_20	17	20	20
2	Theatre_2	20	2	show_20	18	20	20
2	Theatre_2	20	2	show_20	19	20	20
2	Theatre_2	20	2	show_20	20	20	20
2	Theatre_2	20	2	show_20	21	20	20
2	Theatre_2	20	2	show_20	22	20	20
2	Theatre_2	20	2	show_20	14	20	20
2	Theatre_2	20	2	show_20	15	20	20
2	Theatre_2	20	2	show_20	16	20	20
2	Theatre_2	20	2	show_20	13	20	20
2	Theatre_2	21	2	show_21	23	21	21
2	Theatre_2	21	2	show_21	24	21	21
2	Theatre_2	21	2	show_21	25	21	21

theatre_id	theatre_name	Total/theatre	show_name	price	No. tickets	Total/show
1	Theatre_1	2129.24	show_10	10	5	2052.24
1	Theatre_1	2129.24	show_10	500.56	4	2052.24
1	Theatre_1	2129.24	show_11	11	7	77
2	Theatre_2	263	show_20	20	10	200
2	Theatre_2	263	show_21	21	3	63