<jupyter_start><jupyter_text>Neeps- Medium<jupyter_code># Prerequesites
import getpass
%load_ext sql
pwd = getpass.<PASSWORD>pass()
# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo
%sql postgresql://postgres:$pwd@localhost/sqlzoo
%config SqlMagic.displaylimit = 20<jupyter_output>·········<jupyter_text>6.**Show the 'size' of each of the co72010 events. Size is the total number of students attending each event.**<jupyter_code>%%sql
SELECT ut_event.id, SUM(ut_student.sze) size FROM
  ut_event JOIN ut_attends ON (ut_event.id=ut_attends.event) JOIN
    ut_student ON (ut_student.id=ut_attends.student)
    WHERE ut_event.modle='co72010'
    GROUP BY ut_event.id
    ORDER BY SUM(ut_student.sze) DESC;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
8 rows affected.<jupyter_text>7.**For each post-graduate module, show the size of the teaching team. (post graduate modules start with the code co7).**<jupyter_code>%%sql
WITH t AS (
    SELECT DISTINCT ut_modle.id, ut_teaches.staff
      FROM ut_teaches JOIN ut_event ON (ut_teaches.event=ut_event.id) JOIN
        ut_modle ON (ut_event.modle=ut_modle.id)
        WHERE ut_modle.id LIKE 'co7%'
)
SELECT id, COUNT(staff) FROM t GROUP BY id;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
14 rows affected.<jupyter_text>8.**Give the full name of those modules which include events taught for fewer than 10 weeks.**<jupyter_code>%%sql
SELECT DISTINCT ut_modle.name
  FROM ut_modle JOIN ut_event ON (ut_modle.id=ut_event.modle) JOIN
    ut_occurs ON (ut_event.id=ut_occurs.event)
    GROUP BY ut_event.id, ut_modle.name
    HAVING COUNT(ut_occurs.event) < 10
    ORDER BY ut_modle.name;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
4 rows affected.<jupyter_text>9.**Identify those events which start at the same time as one of the co72010 lectures.**<jupyter_code>%%sql
-- time: week + dow + tod
WITH t AS (
  SELECT ut_event.id, ut_event.modle, 
    CONCAT(ut_occurs.week, ut_event.dow, ut_event.tod) AS time FROM
    ut_event JOIN ut_occurs ON (ut_event.id=ut_occurs.event)
)
SELECT DISTINCT a.id
  FROM (SELECT * FROM t WHERE modle <> 'co72010') a JOIN
    (SELECT * FROM t WHERE modle = 'co72010') b ON (
     a.time=b.time)
    ORDER BY a.id;<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
33 rows affected.<jupyter_text>10.**How many members of staff have contact time which is greater than the average?**<jupyter_code>%%sql
WITH t AS (
  SELECT ut_teaches.staff, SUM(ut_event.duration) hrs
    FROM ut_event JOIN ut_teaches ON (ut_event.id=ut_teaches.event) JOIN
      ut_occurs ON (ut_occurs.event=ut_event.id)
      GROUP BY ut_teaches.staff
)
SELECT COUNT(*) FROM t
  WHERE hrs>(SELECT SUM(hrs) FROM t)/(SELECT COUNT(DISTINCT staff) FROM ut_teaches);<jupyter_output>* postgresql://postgres:***@localhost/sqlzoo
1 rows affected.
{'fraction_non_alphanumeric': 0.11396412240590925, 'fraction_numerical': 0.017587055926837847, 'mean_word_length': 4.925, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '430046', 'license': 'MIT', 'n_tokens_mistral': 1119, 'n_tokens_neox': 1033, 'n_words': 267, 'path': 'PostgreSQL/15-2 University Timetables - Medium.ipynb', 'repo_name': 'madlogos/sqlzoo', 'size': '2836'}