Certainly! Let's go through each part of the question step-by-step.
### Part a: Create a database and tables
First, you need to create a database named "Regit" and then create the tables "Doctors" and "Patients".
### Creating the Tables
1. Doctors Table
- Columns: `DocID` (Primary Key), `DocName`, `Department`, `NoofOpdDays`
2. Patients Table
- Columns: `PatNo` (Primary Key), `PatName`, `Department`, `DocID` (Foreign Key referencing `DocID` in Doctors Table)
### SQL statements to create the database and tables
```sql
-- Create the Doctors table
CREATE TABLE Doctors (
DocID INTEGER PRIMARY KEY,
DocName TEXT,
Department TEXT,
NoofOpdDays INTEGER
);
-- Create the Patients table
CREATE TABLE Patients (
PatNo INTEGER PRIMARY KEY,
PatName TEXT,
Department TEXT,
DocID INTEGER,
FOREIGN KEY (DocID) REFERENCES Doctors (DocID)
);
```
### Part b: Insert the details into the tables
You need to insert the given details into the "Doctors" and "Patients" tables.
### Inserting into Doctors Table
```sql
INSERT INTO Doctors (DocID, DocName, Department, NoofOpdDays) VALUES
(101, 'JK Mrshar', 'Ortho', 3),
(102, 'Mahesh Tripathi', 'ENT', 4),
(103, 'Ravi Kumar', 'Neuro', 5),
(104, 'Mukesh Jain', 'Physio', 3);
```
### Inserting into Patients Table
```sql
INSERT INTO Patients (PatNo, PatName, Department, DocID) VALUES
(1, 'Payal', 'ENT', 102),
(2, 'Naveen', 'Ortho', 101),
(3, 'Rakesh', 'Neuro', 103),
(4, 'Atul', 'Physio', 104);
```
### Part c: List all doctors and their departments
This involves selecting the `DocName` and `Department` from the "Doctors" table.
```sql
SELECT DocName, Department FROM Doctors;
```
### Part d: Find the names of all patients along with their respective doctor's names
This requires a join between the "Patients" and "Doctors" tables.
```sql
SELECT Patients.PatName, Doctors.DocName
FROM Patients
JOIN Doctors ON Patients.DocID = Doctors.DocID;
```
### Part e: List details of doctors who have more than 3 OPD days and their patients' names
Here you need to filter doctors with more than 3 OPD days and join with patients.
```sql
SELECT Doctors.DocName, Patients.PatName
FROM Doctors
JOIN Patients ON Doctors.DocID = Patients.DocID
WHERE Doctors.NoofOpdDays > 3;
```
### Part f: Find the number of patients for each doctor
You need to count the number of patients per doctor using `GROUP BY`.
```sql
SELECT Doctors.DocName, COUNT(Patients.PatNo) AS NumberOfPatients
FROM Doctors
JOIN Patients ON Doctors.DocID = Patients.DocID
GROUP BY Doctors.DocID;
```
### Summary of SQL Queries
1. Create Tables:
```sql
CREATE TABLE Doctors (
DocID INTEGER PRIMARY KEY,
DocName TEXT,
Department TEXT,
NoofOpdDays INTEGER
);
CREATE TABLE Patients (
PatNo INTEGER PRIMARY KEY,
PatName TEXT,
Department TEXT,
DocID INTEGER,
FOREIGN KEY (DocID) REFERENCES Doctors (DocID)
);
```
2. Insert Data:
```sql
INSERT INTO Doctors (DocID, DocName, Department, NoofOpdDays) VALUES
(101, 'JK Mrshar', 'Ortho', 3),
(102, 'Mahesh Tripathi', 'ENT', 4),
(103, 'Ravi Kumar', 'Neuro', 5),
(104, 'Mukesh Jain', 'Physio', 3);
INSERT INTO Patients (PatNo, PatName, Department, DocID) VALUES
(1, 'Payal', 'ENT', 102),
(2, 'Naveen', 'Ortho', 101),
(3, 'Rakesh', 'Neuro', 103),
(4, 'Atul', 'Physio', 104);
```
3. List all doctors and their departments:
```sql
SELECT DocName, Department FROM Doctors;
```
4. Find names of all patients along with respective doctor's names:
```sql
SELECT Patients.PatName, Doctors.DocName
FROM Patients
JOIN Doctors ON Patients.DocID = Doctors.DocID;
```
5. List details of doctors with more than 3 OPD days and patients' names:
```sql
SELECT Doctors.DocName, Patients.PatName
FROM Doctors
JOIN Patients ON Doctors.DocID = Patients.DocID
WHERE Doctors.NoofOpdDays > 3;
```
6. Find the number of patients for each doctor:
```sql
SELECT Doctors.DocName, COUNT(Patients.PatNo) AS NumberOfPatients
FROM Doctors
JOIN Patients ON Doctors.DocID = Patients.DocID
GROUP BY Doctors.DocID;
```
By following these queries, you should be able to create the desired database, insert the required data, and retrieve the information as specified in the question parts (c), (d), (e), and (f).
