Q1. Consider the following Relational Schema for a Hospital: Doctor (ID, DoctorName, Speciality, JoinDate) Where ID is the unique identifier for the doctor (Primary Key), and Speciality indicates the doctor's area of expertise. Patient (PID, PatientName, Phone, ID) Where PID is the unique identifier for the patient (Primary Key), and ID links the patient to their primary care doctor (Foreign Key). Note: Make suitable assumptions, if any.
- (a)) Write the SQL statements to create these tables. You must include definitions for Primary Keys, Foreign Keys, and necessary constraints. Insert a few rows of meaningful data into each table. (320 words)
- (b.i)) List the names of doctors who joined before January 1st, 2026. (80 words)
- (b.ii)) List the names of all patients in alphabetical order. (80 words)
- (b.iii)) Find the name of every patient along with the name of their assigned doctor. (80 words)
- (b.iv)) List the ID of all doctors who have been assigned to more than five patients. (80 words)
- (b.v)) Find the total number of patients assigned to each doctor. (80 words)
- (b.vi)) Find the total number of doctors in the hospital. (80 words)
Key Points:
- Primary Key uniquely identifies each record in a table, ensuring data integrity.
- Foreign Key establishes a link between two tables, enforcing referential integrity.
- SQL `CREATE TABLE` defines table structure, data types, and constraints.
- `INSERT INTO` adds new rows of data into a specified table.
Answer: This response comprehensively addresses the given relational database problem for a hospital schema using SQL. It covers table creation with appropriate constraints, data insertion, and various querying scenarios demonstrating fundamental SQL operations such as filtering, ordering, joining, grouping, and aggregation. Each sub-question adheres to its specific word limit, providing clear and concise SQL statements along with brief explanations suitable for an IGNOU laboratory course. Relational s...