/* Database Systems, 8th Ed., Rob/Coronel */ /* Type of SQL : SQL Server */ CREATE TABLE DOCTOR ( DOC_ID varchar(5), DOC_LNAME varchar(15), DOC_FNAME varchar(15), DOC_INITIAL varchar(1), DOC_SPECIALTY varchar(15) ); INSERT INTO DOCTOR VALUES('29827','Sanchez','Julio','J','Dermatology'); INSERT INTO DOCTOR VALUES('32445','Jorgensen','Annelise','G','Neurology'); INSERT INTO DOCTOR VALUES('33456','Korenski','Anatoly','A','Urology'); INSERT INTO DOCTOR VALUES('33989','LeGrande','George','','Pediatrics'); INSERT INTO DOCTOR VALUES('34409','Washington','Dennis','F','Orthopaedics'); INSERT INTO DOCTOR VALUES('36221','McPherson','Katye','H','Dermatology'); INSERT INTO DOCTOR VALUES('36712','Dreifag','Herman','G','Psychiatry'); INSERT INTO DOCTOR VALUES('38995','Minh','Tran','','Neurology'); INSERT INTO DOCTOR VALUES('40004','Chin','Ming','D','Orthopaedics'); INSERT INTO DOCTOR VALUES('40028','Feinstein','Denise','L','Gynecology'); /* -- */ CREATE TABLE DRUG ( DRUG_CODE varchar(5), DRUG_NAME varchar(25), DRUG_PRICE numeric ); INSERT INTO DRUG VALUES('AF15','Afgapan-15','25'); INSERT INTO DRUG VALUES('AF25','Afgapan-25','35'); INSERT INTO DRUG VALUES('DRO','Droalene Chloride','111.89'); INSERT INTO DRUG VALUES('DRZ','Druzocholar Cryptolene','18.99'); INSERT INTO DRUG VALUES('KO15','Koliabar Oxyhexalene','65.75'); INSERT INTO DRUG VALUES('OLE','Oleander-Drizapan','123.95'); INSERT INTO DRUG VALUES('TRYP','Tryptolac Heptadimetric','79.45'); /* -- */ CREATE TABLE PATIENT ( PAT_NUM varchar(5), PAT_TITLE varchar(4), PAT_LNAME varchar(15), PAT_FNAME varchar(15), PAT_INITIAL varchar(1), PAT_DOB datetime, PAT_AREACODE varchar(3), PAT_PHONE varchar(8) ); INSERT INTO PATIENT VALUES('100','Mr.','Kolmycz','George','D','6/15/1942','615','324-5456'); INSERT INTO PATIENT VALUES('101','Ms.','Lewis','Rhonda','G','3/19/2005','615','324-4472'); INSERT INTO PATIENT VALUES('102','Mr.','VanDam','Rhett','','11/14/1958','901','675-8993'); INSERT INTO PATIENT VALUES('103','Ms.','Jones','Anne','M','10/16/1974','615','898-3456'); INSERT INTO PATIENT VALUES('104','Mr.','Lange','John','P','11/8/1971','901','504-4430'); INSERT INTO PATIENT VALUES('105','Mr.','Williams','Robert','D','3/14/1975','615','890-3220'); INSERT INTO PATIENT VALUES('106','Mrs.','Smith','Jeanine','K','2/12/2003','615','324-7883'); INSERT INTO PATIENT VALUES('107','Mr.','Diante','Jorge','D','8/21/1974','615','890-4567'); INSERT INTO PATIENT VALUES('108','Mr.','Wiesenbach','Paul','R','2/14/1966','615','897-4358'); INSERT INTO PATIENT VALUES('109','Mr.','Smith','George','K','6/18/1961','901','504-3339'); INSERT INTO PATIENT VALUES('110','Mrs.','Genkazi','Leighla','W','5/19/1970','901','569-0093'); INSERT INTO PATIENT VALUES('111','Mr.','Washington','Rupert','E','1/3/1966','615','890-4925'); INSERT INTO PATIENT VALUES('112','Mr.','Johnson','Edward','E','5/14/1961','615','898-4387'); INSERT INTO PATIENT VALUES('113','Ms.','Smythe','Melanie','P','9/15/1970','615','324-9006'); INSERT INTO PATIENT VALUES('114','Ms.','Brandon','Marie','G','11/2/1932','901','882-0845'); INSERT INTO PATIENT VALUES('115','Mrs.','Saranda','Hermine','R','7/25/1972','615','324-5505'); INSERT INTO PATIENT VALUES('116','Mr.','Smith','George','A','11/8/1965','615','890-2984'); /* -- */ CREATE TABLE PRESCRIPTION ( DOC_ID varchar(5), PAT_NUM varchar(5), DRUG_CODE varchar(5), PRES_DOSAGE varchar(50), PRES_DATE datetime ); INSERT INTO PRESCRIPTION VALUES('32445','102','DRZ','2 tablets every four hours -- 50 tablets total','11/12/2012'); INSERT INTO PRESCRIPTION VALUES('34409','101','KO15','1 tablet every six hours -- 30 tablets total','11/14/2012'); INSERT INTO PRESCRIPTION VALUES('32445','113','OLE','1 teaspoon with each meal -- 250 ml total','11/14/2012'); INSERT INTO PRESCRIPTION VALUES('36221','109','DRO','2 tablets with every meal -- 60 tablets total','11/14/2012'); INSERT INTO PRESCRIPTION VALUES('38995','107','KO15','1 tablet every six hours -- 30 tablets total','11/14/2012'); INSERT INTO PRESCRIPTION VALUES('36221','109','KO15','1 tablet every six hours -- 30 tablets total','11/14/2012');

-For each table from clinic.sql, revise the code to add primary keys and foreign keys constraints if required.

Implement the following queries in SQL

Retrieve the specialty of Dr. Tran Minh

Retrieve the first and last name of every dermatologist

Retrieve the names of all patients in area code 901

Retrieve the names and phone numbers of all patients born in 1970

Retrieve the names and DOB of all female patients

Retrieve the last name of all patients with are code not equal to 615

Retrieve the names of the all medications.

Retrieve the names of each medication and their price, order from most expensive to least

Retrieve all information on medication prescribed by 32445

Retrieve the patient Num for Dr 36221

Please I want a detailed complete answers for each part. Then make sure you provide clear picture(s) of solution that I can read. Thanks