Section $1$

Using SQL$,$ create tables according to the given schema shown in Figure $1.$

Figure $1$:

doctor $($dssn$,$ dname, specialty$)$ prescription$($ dssn$,$ pre$\_$no$,$ pdate$)$ foreign key $($dssn$)$ references to doctor$($dssn$)$ ph$\_$company$($ pname, phone$)$ drug $($pname$,$ trade$\_$name, formula$)$ foreign key $($pname$)$ references to ph$\_$company$($pname$)$ consist$\_$of $($ dssn$,$ pre$\_$no$,$ pname, trade$\_$name$)$ foreign key $($dssn$,$ pre$\_$no$)$ references prescription$($dssn$,$ pre$\_$no$),$ foreign key $($pname$,$ trade$\_$name$)$ references drug$($panme$,$ trade$\_$name$)$ pharmacy $($pcy$\_$name, address, phone$)$ sell $($pharmacy$\_$name, pname, trade$\_$name, price$)$ foreign key $($pname$,$ trade$\_$name$)$ references drug $($pname$,$ trade$\_$name$),$ foreign key $($pharmacy$\_$name$)$ references pharmacy$($pcy$\_$name$)$

Section $2$

Populate the database. Please check sections $3$ for details.

Section $3$

Use SQL to specify the following queries. When you populate the database, insert data such that at least two rows will be display as the result of running each query.

$1)$ Retrieve the drugs$$ information $($trade name and formula$)$ which are manufactured by company $$Pfizer$.$ Order the result by trade name in descending order.

$2)$ List drug information $($Pharmaceutical company name, trade name, formula$)$ that has been prescribed by at least one doctor.

$3)$ Obtain all the prescription drugs $($trade name, company name and prescription date$)$ given by Doctor $$Kate Elmasri$.$

$4)$ Retrieve all the drugs $($trade$\_$name, formula$)$ sold by $$Walmart$.($Walmart$$ is pharmacy name$).$

$5)$ Retrieve number of type of drugs made by $$Pfizer$.($Type is distinguished by $$trade$\_$name$).$

$6)$ Obtain information $($pharmacy name, address$)$ of pharmacy that sells $$Ibuprofen$$ with the highest price among all pharmacies. $($Ibuprofen$$ is a trade name$)$

$7)$ Retrieve the pharmacies that sell more than $2$ types of drugs. List pharmacy name and number of types of drugs sold. $($Type is distinguished by $$trade$\_$name$.$ However, if two drugs with the same trade name are produced by two different pharmaceutical companies, they are considered as two different types of drugs.$)$

Section $4$

Specify the statements to drop all the tables. Pay attention to the order of the drop statements in order to drop everything successfully.