$1.$ Create a a database DB$\_$consultation

$2.$ Create the following tables

$$ Patient $($PatientID$,$ Name, Age, Gender$)$

$$ Doctor $($DoctorID$,$ Name, Speciality$)$

$$ Treatment $($ TreatmentID, DoctorID, PatientID, Nature$\_$of$\_$treatment, Date$\_$of$\_$treatment$)$

$3.$ Add field salary to Doctor Table.

$4.$ Insert values in the tables

$5.$ Write an SQL statement to display all the data from Doctor Table.

$6.$ Write an SQL statement to display all the records from Doctor and sort them in a descending order

$7.$ Write an SQL statement to display doctors earning a salary between $2500$ and $5000$

$8.$ Write an SQL statement to display the total salary of the doctors.

Solutions

$1.$ Create database DB$\_$consultation

$2.$ Create table Patient $($

PatientID int CONSTRAINT pkPatientID Primary key,

Patname varchar $(30)$ Not null,

Age int not null,

Gender varchar $(10))$;

Create table Doctor $($

DoctorID int CONSTRAINT pkDoctorID Primary key,

Docname varchar $(30)$ Not null,

Speciality varchar $(30)$ not null$)$;

Create table treatment $($

TreatmentID int CONSTRAINT pkTreatmentID Primary key,

DoctorID int Foreign Key References Doctor$($DoctorID$),$

PatientID int Foreign Key References Patient$($PatientID$),$

Nature$\_$of$\_$treatment varchar $(50)$ Not Null,

Date$\_$of$\_$treatment Date$)$;

$3.$ Add field salary to Doctor Table.

ALTER TABLE Doctor

ADD salary money not null;

$4.$ Insert values in the tables

INSERT into Doctor

values $(201,$ 'Sara Adams', 'General', $2500)$;

INSERT into Doctor

values $(200,$ 'Mary jones', 'Dentist', $2000)$;

INSERT into Doctor

values $(202,$ 'Wiliam Prince', 'Cardiologist', $5000)$;

INSERT into Doctor

values $(204,$ 'Mary Charles', 'Residence Doctor', $2000)$;

INSERT into Doctor

values $(205,$ 'Charles Adams', 'General', $2500)$;

$5.$ Write an SQL statement to display all the data from Doctor table

SELECT $*$ From Doctor

$6.$ Write an SQL statement to display all the records from Doctor and sort them in a descending order

SELECT $*$ From Doctor ORDER BY DoctorID DESC;

$7.$ Write an SQL statement to display doctors earning a salary between $2500$ and $5000$

SELECT $*$ FROM DOCTOR WHERE salary BETWEEN $2500$ AND $5000$;

$8.$ Write an SQL statement to display the total salary of the doctors.

SELECT SUM$($salary$)$ FROM$$ Doctor