<jupyter_start><jupyter_text>Activity 2-3:-------------Multi-table queries Exercise 1:-----------For three tables $R,S,T$ that only have one attribute $A$:* R = {1,2,3,4,5}* S = {1,3,5,7,9}* T = {1,4,7,10} Can you write a query to select $R \cap (S \cup T)$ - in other words elements that are in $R$ and either $S$ or $T$?Write your query here:<jupyter_code>%%sql
SELECT DISTINCT R.A
FROM R, S, T
WHERE R.A = S.A OR R.A = T.A;<jupyter_output>Done.<jupyter_text>Now test your query above for the case where $S = \emptyset$- what happens and why?Execute the below, then re-run your query above<jupyter_code>%%sql
delete from S;
%%sql
SELECT DISTINCT R.A
FROM R, S, T
WHERE R.A = S.A OR R.A = T.A;<jupyter_output>Done.<jupyter_text>Actually, the query above was not the correct one to solve the problem. If S = {}, then the cross product of R, S, T = {}, and the query result = {}!The right way to solve the problem is by applying to distributive property of set intersection, $$R \cap (S \cup T) = (R \cap S) \cup (R \cap T). $$The distributed formula translates directly into SQL query concepts:<jupyter_code>%%sql
SELECT DISTINCT U.A FROM 
(SELECT R.A
    FROM R, S
    WHERE R.A = S.A 
UNION
SELECT R.A
    FROM R, T
    WHERE R.A = T.A 
) AS U;<jupyter_output>Done.<jupyter_text>Exercise 2-----------* Schema is same as before> Product (pname,  price, category, manufacturer)> Company (cname, stockPrice, country)* Our goal is to answer the following question:> Find all categories of products that are made by Chinese companiesWrite your query here:<jupyter_code>%%sql
SELECT DISTINCT p.category
FROM product p, company c
WHERE p.manufacturer = c.cname 
  AND c.country = 'China';<jupyter_output>Done.
{'fraction_non_alphanumeric': 0.13380281690140844, 'fraction_numerical': 0.011150234741784037, 'mean_word_length': 3.8163841807909606, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 16, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '381623', 'license': 'MIT', 'n_tokens_mistral': 603, 'n_tokens_neox': 580, 'n_words': 236, 'path': 'Activity-2-3-Solutions.ipynb', 'repo_name': 'frangoose/cmpe138', 'size': '1704'}