Questions $3-5$

For each SQL query below, do the following:

Write a query for the pg$\_$class table to get the number of pages and tuples in each relation. Show the query and the results. No need to repeat answers if a table is used multiple times in the questions below. That is$,$ for each question, just get information about $$new$$ tables.

List two types of joins that could be used for the query.

Using the formulas provided in the slides, calculate the cost of doing each type of join listed in b$.$

Use EXPLAIN to identify which join algorithm postgresql uses. $($See Documentation: $14$: EXPLAIN Also, see slide $19,20$ in Slides $12$ for for information about Explain$)$

Report if your calculation of which join is cheapest matches postgresql$$s choice or not

Use work$\_$mem $/8$k to find # of buffer pages available to join $($Documentation: $13$: $19\mathrm{.}4.$ Resource Consumption$)$

Question $3(20$ points$)$:

SELECT A$.$first, A$.$last, A$.$clearance$\_$id

FROM agent A$,$ securityclearance S

WHERE A$.$clearance$\_$id $=$ S$.$sc$\_$id

Question $4(20$ points$)$:

SELECT L$.$language L$.$lang$\_$id

FROM language L$,$ languagerel LR

WHERE L$.$lang$\_$id $=$ LR$.$lang$\_$id;

Question $5(20$ points$)$:

SELECT A$1.$agent$\_$id$,$ A$2.$agent$\_$id

FROM agent A$1,$ agent A$2$

WHERE A$1.$salary $>$ A$2.$salary