6.2 List, in alphabetical order, the names of all patients who had consultations after 01 January 2018. 6.3 Which practice (by name) has the most recent hire (i.e. doctor with latest employment date)? Which doctor has had the least number of consultations? Give the doctor's 6.4 name and the number of consultation s/he has given.
"6.1,6.2 & 6.1 are answered"
The given problem has an entity relationship diagram where the requirement is to implement the diagram in the
**As per Bartleby guidelines, solving the first 3 sub-parts as the question contains many parts and the required parts to be answered is not mentioned.
It will involve the below steps:
The database will need to create below tables:
**Primary key is bold and underlined, Foreign key is italic
-
- Patient(Pat_ID, Pat_Name, Pat_Age, Pat_Gender)
- Doctor(Doc_ID, DOc_Name, Doc_EmployDate, Prc_ID)
- Consultation(Doc_ID, Pat_ID, Con_Date)
- Practice(Prc_ID, Prc_Name, Prc_YearEstablished, Prc_Province)
Below is the script to create the tables with attributes and foreign key constraints:
//Script will create the database named TrueHealth and then populate the tables:
create database trueHealth;
use truehealth;
CREATE TABLE Patient(Pat_ID CHAR(4) primary key, Pat_Name varchar(50), Pat_Age int, Pat_Gender varchar(10));
CREATE TABLE Practice(Prc_ID int primary key, Prc_Name varchar(50), Prc_YearEstablished int, Prc_Province varchar(50));
CREATE TABLE Doctor(Doc_ID CHAR(4) primary key, Doc_Name varchar(50), Doc_EmployDate date, Prc_ID int,
constraint doc_prc foreign key (Prc_ID) references Practice(Prc_ID));
CREATE TABLE Consultation(Doc_ID char(4), Pat_ID char(4), Con_Date date,
primary key (Doc_id, Pat_id),
constraint doc_cons foreign key (Doc_ID) references Doctor(Doc_ID),
constraint pat_cons foreign key (Pat_ID) references Patient(Pat_ID) );
Result:
Tables will get created with database:
Below SQL will insert the required data in the tables as below:
INSERT INTO PATIENT values('P001', 'PatientName1', 15,'Male');
INSERT INTO PATIENT values('P002', 'PatientName2', 35,'Female');
INSERT INTO Practice values(101, 'Padetrician', 2010, 'Gauteng');
INSERT INTO Practice values(102, 'General', 2015, 'Rohtik');
INSERT INTO DOCTOR VALUES('D001', 'DOctorname1', '2020-12-12', 101);
INSERT INTO DOCTOR VALUES('D002', 'DOctorname2', '2016-01-12', 102);
INSERT INTO CONSULTATION values('D001', 'P001', '2021-01-01');
INSERT INTO CONSULTATION values('D002', 'P001', '2016-01-01');
Data is each table is now as below:
Patient:
Doctor:
Practice:
Consultation:
LOGIC: The requirement is to retrieve all Practice records that are having Prc_Province = Gauteng
SQL: Below SQL will return the required data:
Select * from Practice where Prc_Province = 'Gauteng'
Output:
Step by step
Solved in 2 steps
