Database Schema:

Hospital $($hid$,$ name, city$)//$ hospital $($hid$,$ name, city$)$

Doctor $($did$,$ name, specialty, hid$)//$ doctor $($did$,$ name, specialtyArea, hid$)$

Patient $($pid$,$ name, status:$\{$Pos$,$Neg$\})//$ patient$($pid$,$ name, lastTestResult:$\{$Pos$,$Neg$\})$

Admitted $($pid$,$ hid, admissionDate, entubationDate, deathDate, did$)//$ patientAdmission $($pid$,$ hid, admissionDate, entubationDate, deathDate, did$)$

TestType $($tid$,$ name, price, accuracy, origin:$\{$ch$,$gr$,...,$tr$\})//$ testType $($tid$,$ name, price, reliability, origin:$\{$ch$,$gr$,...,$tr$\})$

Test $($pid$,$ tid, date, result:$\{$Pos$,$Neg$\})//$test$($pid$,$ tid, date, result:$\{$Pos$,$Neg$\})$

Using the above database schema, write the following questions in SQL:

$(10$ points$-$IN$/$EXISTS$/=$SOME$/$ALL$)$ Write a query in $3$ different ways using IN$,$ EXISTS, and SOME$/$ALL to return the record $($all columns of the hospital table$)$ of the hospital where the doctor named 'Ali KURT' works.

$(10$ points$-$EXISTS$)$ Write a query in $3$ different ways using NOT IN$,$ NOT EXISTS, and SOME$/$ALL to return the records $($all columns of the hospital table$)$ of the hospitals where the doctor named 'Ali KURT' does not work.

$(10$ points$-$UNION$/$EXCEPT$/$INTERSECT$)$

a$.$ List the records of patients who have tested Negative for both 'Biontech' $($use testType.name field$)$ and 'Moderna' $($use testType.name field$)$ tests using INTERSECT.

b$.$ List the records of patients who have tested Positive for either a Turkish $($testType$.$origin$=$tr$)$ or German $($testType$.$origin$=$gr$)$ vaccine using UNION.

c$.$ List the records of patients who have not undergone any tests.

$(10$ points$-$GROUP BY$-$HAVING$)$ Group the tests by their origin country $($testType$.$origin$)$ and list the number of tests done for each country $($found from the test table$),$ the total amount paid for these tests $($testType$.$price$),$ and the percentage of tests that are positive and negative, but only for tests with an accuracy $($testType$.$accuracy $>80)$ over $80\%$ and excluding $\text{'}$tr$\text{'}$ origin countries.

$(10$ points$-$UNIQUE$)$ List the records of patients who have not taken the same named test $($testType$.$name$)$ more than once $($i$.$e$.,$ have taken different tests in terms of name$)$ using UNIQUE.

$(10$ points WITH$/$FROM subquery$)$ List the hids of the patients who have undergone more tests than the average number of tests per patient. Do this by first calculating the $$average number of tests per patient$$ in a WITH subquery and then the $$average of these numbers$$ in another WITH subquery.

$(10$ points SELECT subquery$)$ List the hospital records. But while listing the records, calculate the number of doctors and inpatients in each hospital by adding a subquery each inside SELECT. $($SELECT $*,($subquery$1)$ number$-$of$-$doctors, $($subquery$2)$ number$-$of$-$patients FROM hospital$)$

$(10$ points$-$HAVING subquery$)$ List the hids of hospitals that have at least one $($i$.$e$.,$ any$)$ doctor specializing in 'internal medicine' $($specialty$=$'dahiliye'$)$ and the total number of doctors working in those hospitals $($HAVING should be used to check if the hospital employs an internal medicine specialist$).$

$(10$ points$-$VIEWS$)$ Create a view named statistics $($date$,$ noOfTests, noOfPositives, noOfDeaths, noOfEntubed$)[$statistics $($date$,$ dailyTestCount, dailyPositiveCount, dailyDeathCount, dailyEntubationCount$)].$ To create this view, first find the daily positive and negative counts by GROUP BY the date field in the test table. Then find the dailyDeathCount and dailyEntubationCount by GROUP BY the admissionDate field in the admitted table. In the final step, join the results of these two steps based on the date field of the grouping. Since there might be days with only tests or only deaths$/$entubations$,$ this join should be a FULL OUTER JOIN.

List the records of patients who have taken tests after the last test of the patient named 'Ali KURT'. $($In a subquery, return the test dates of Ali KURT. Compare the test dates of the patients with these dates using the $>$SOME$/$ALL operator$).$

NO CHATGPT ANSWER IS ACCEPTED, PLEASE.