2.sql

CREATE DATABASE LABORATORY;

USE LABORATORY;

CREATE TABLE EMPLOYEE(
	name varchar(20),
	address varchar(20),
	Eaadharno BIGINT CONSTRAINT twelvedigits CHECK(Eaadharno BETWEEN 100000000000 and 999999999999),
	mobno BIGINT,emailid varchar(20),
	PRIMARY KEY(Eaadharno)
	);
INSERT INTO EMPLOYEE VALUES ('Harikrishnan','123 Main St',613842823588,9876543210,'hari@gmail.com');
INSERT INTO EMPLOYEE VALUES ('Ram','123 sub St',613842823000, 9876543222, 'ram@example.com');
INSERT INTO EMPLOYEE VALUES ('Raju', '231 section St',613842823500, 9876543215, 'raju@example.com');
INSERT INTO EMPLOYEE VALUES ('Ravi', '222 delhi street',613842823555, 9876543220, 'ravi@example.com');

SELECT * FROM EMPLOYEE;

CREATE TABLE PATIENT(
	name varchar(20),
	address varchar(20),
	Paadharno BIGINT CONSTRAINT tweldigits CHECK(Paadharno BETWEEN 100000000000 AND 999999999999),
	age int CHECK(age>0),
	mobno BIGINT,
	emailid varchar(20),
	PRIMARY KEY(Paadharno)
	);
 INSERT INTO PATIENT VALUES ('Jane Smith', '456 Elm St', 123456789012, 30, 9123456789, 'janesmith@gmail.com');
 INSERT INTO PATIENT VALUES ('Chris Brown', '987 Maple St', 234567890123, 45, 8112233445, 'chrisbrown@gmail.com');
 INSERT INTO PATIENT VALUES ('Olivia Martinez', '654 Cedar St', 345678901234, 27, 8223344556, 'olivia@gmail.com');
 INSERT INTO PATIENT VALUES ('Liam Wilson', '852 Willow St', 456789012345, 50, 8334455667, 'liamwilson@gmail.com');

SELECT * FROM PATIENT;

CREATE TABLE TEST(testname varchar(20),
	testdescription varchar(50),
	PRIMARY KEY(testname)
	);
 INSERT INTO TEST VALUES('Blood Test', 'Complete blood count');
 INSERT INTO TEST VALUES('X-Ray', 'Chest X-ray scan');
 INSERT INTO TEST VALUES('MRI', 'Magnetic Resonance Imaging');
 INSERT INTO TEST VALUES('Ultrasound', 'Abdominal ultrasound scan');

SELECT * FROM TEST;


CREATE TABLE SCAN(scanname varchar(20),
	scandescription varchar(60),
	scanamt float,
	PRIMARY KEY(scanname)
	);
 INSERT INTO SCAN VALUES('CT Scan', 'Computed Tomography scan', 5000.00);
 INSERT INTO SCAN VALUES('PET Scan', 'Positron Emission Tomography scan', 7000.00);
 INSERT INTO SCAN VALUES('Mammogram', 'Breast X-ray imaging', 3000.00);
 INSERT INTO SCAN VALUES('Bone Scan', 'Nuclear imaging test for bones', 4500.00);

SELECT * FROM SCAN;

CREATE TABLE DOCTOR(Did varchar(20),
	name varchar(20),
	address varchar(20),
	specialization varchar(50),
	hospitalname varchar(50),
	PRIMARY KEY(Did,hospitalname)
	);
 INSERT INTO DOCTOR VALUES('D001', 'Dr. Alice Smith', '101 Maple St', 'Cardiology', 'City Hospital');
 INSERT INTO DOCTOR VALUES('D002', 'Dr. Robert Johnson', '202 Oak St', 'Neurology', 'Metro Medical Center');
 INSERT INTO DOCTOR VALUES('D003', 'Dr. Emily Davis', '303 Pine St', 'Orthopedics', 'General Hospital');
 INSERT INTO DOCTOR VALUES('D004', 'Dr. Michael Brown', '404 Birch St', 'Pediatrics', 'Community Health Clinic');

SELECT * FROM DOCTOR;


CREATE TABLE PTEST(Paadharno BIGINT,
	testname varchar(20),
	Did varchar(20),
	testdate datetime,
	testresult varchar(20),
	testamt float,
	hospitalname varchar(50),
	FOREIGN KEY(Paadharno) REFERENCES PATIENT(Paadharno) ON DELETE cascade ON UPDATE cascade,
	FOREIGN KEY(testname) REFERENCES TEST(testname) ON DELETE cascade ON UPDATE cascade,
	FOREIGN KEY(Did,hospitalname) REFERENCES DOCTOR(Did,hospitalname) ON DELETE cascade ON UPDATE cascade,
	PRIMARY KEY(Paadharno,testname,Did,hospitalname,testdate)
	);
 INSERT INTO PTEST VALUES(123456789012, 'Blood Test', 'D001', '2024-08-01 09:00:00', 'Normal', 500.00, 'City Hospital');
 INSERT INTO PTEST VALUES(234567890123, 'X-Ray', 'D002', '2024-08-02 10:00:00', 'Clear', 1200.00, 'Metro Medical Center');
 INSERT INTO PTEST VALUES(345678901234, 'MRI', 'D003', '2024-08-03 11:30:00', 'No Issues', 7000.00, 'General Hospital');
 INSERT INTO PTEST VALUES(456789012345, 'Ultrasound', 'D004', '2024-08-04 14:00:00', 'Normal', 1500.00, 'Community Health Clinic');
 INSERT INTO PTEST VALUES(234567890123, 'Blood Test', 'D002', '2024-08-10 09:30:00', 'Normal', 500.00, 'Metro Medical Center');

SELECT * FROM PTEST;

CREATE TABLE PSCAN(Paadharno BIGINT,
	scanname varchar(20),
	Did varchar(20),
	scandate datetime,
	scanresult varchar(20),
	hospitalname varchar(50),
	PRIMARY KEY(Paadharno,scanname,Did,hospitalname,scandate),
	FOREIGN KEY(Paadharno) REFERENCES PATIENT(Paadharno) ON DELETE cascade ON UPDATE cascade,
	FOREIGN KEY(scanname) REFERENCES SCAN(scanname) ON DELETE cascade ON UPDATE cascade,
	FOREIGN KEY(Did,hospitalname) REFERENCES DOCTOR(Did,hospitalname) ON DELETE cascade ON UPDATE cascade
	);
 INSERT INTO PSCAN VALUES(123456789012, 'CT Scan', 'D001', '2024-08-05 09:30:00', 'Normal', 'City Hospital');
 INSERT INTO PSCAN VALUES(234567890123, 'PET Scan', 'D002', '2024-08-06 10:45:00', 'No Issues', 'Metro Medical Center');
 INSERT INTO PSCAN VALUES(345678901234, 'Mammogram', 'D003', '2024-08-07 11:15:00', 'Benign', 'General Hospital');
 INSERT INTO PSCAN VALUES(456789012345, 'Bone Scan', 'D004', '2024-08-08 13:30:00', 'Osteoporosis', 'Community Health Clinic');

SELECT * FROM PSCAN;


SELECT test.testname,test.testdescription FROM TEST JOIN PTEST 
on ptest.testname=test.testname where 
ptest.paadharno=123456789012 AND 
ptest.testdate='2024-08-01 09:00:00';


SELECT patient.name,patient.address,patient.Paadharno,patient.age, patient.emailid FROM PATIENT JOIN PTEST ON 
ptest.Paadharno=patient.Paadharno JOIN TEST 
ON ptest.testname=test.testname;

SELECT testname,COUNT(DISTINCT Paadharno) AS 
patient_count FROM PTEST GROUP BY testname 
ORDER BY patient_count DESC LIMIT 1;

SELECT testname,COUNT(DISTINCT Paadharno) AS 
patient_count FROM PTEST GROUP BY testname 
ORDER BY patient_count ASC LIMIT 1;

SELECT P.name AS patient_name, P.address, P.Paadharno, P.age, P.emailid, SCAN.scanname, COUNT(PS.Paadharno) AS scan_count
FROM PATIENT P
JOIN PSCAN PS ON P.Paadharno = PS.Paadharno
JOIN SCAN ON PS.scanname = SCAN.scanname
GROUP BY P.Paadharno, P.name, P.address, P.age, P.emailid, SCAN.scanname
HAVING COUNT(PS.Paadharno) <= 2
ORDER BY SCAN.scanname, P.name;

SELECT T.testname, SUM(PT.testamt) AS total_revenue
FROM TEST T
JOIN PTEST PT ON T.testname = PT.testname
GROUP BY T.testname
ORDER BY total_revenue DESC;

SELECT S.scanname, SUM(S.scanamt) AS total_revenue
FROM SCAN S
JOIN PSCAN PS ON S.scanname = PS.scanname
GROUP BY S.scanname
ORDER BY total_revenue DESC;

SELECT PS.Paadharno, P.name AS patient_name, P.address, P.age, P.emailid, S.scanname, S.scandescription, PS.scandate, PS.scanresult
FROM PSCAN PS
JOIN PATIENT P ON PS.Paadharno = P.Paadharno
JOIN SCAN S ON PS.scanname = S.scanname
ORDER BY PS.scandate ASC
LIMIT 1;

SELECT PS.Paadharno, P.name AS patient_name, P.address, P.age, P.emailid, S.scanname, S.scandescription, PS.scandate, PS.scanresult
FROM PSCAN PS
JOIN PATIENT P ON PS.Paadharno = P.Paadharno
JOIN SCAN S ON PS.scanname = S.scanname
ORDER BY PS.scandate DESC
LIMIT 1;

SELECT D.Did, D.name AS doctor_name, D.specialization, D.hospitalname, COUNT(*) AS test_count
FROM DOCTOR D
JOIN PTEST PT ON D.Did = PT.Did AND D.hospitalname = PT.hospitalname
WHERE PT.testdate BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY D.Did, D.name, D.specialization, D.hospitalname
HAVING COUNT(*) = (
    SELECT MAX(test_count)
    FROM (
        SELECT COUNT(*) AS test_count
        FROM DOCTOR D
        JOIN PTEST PT ON D.Did = PT.Did AND D.hospitalname = PT.hospitalname
        WHERE PT.testdate BETWEEN '2024-08-01' AND '2024-08-31'
        GROUP BY D.Did, D.name, D.specialization, D.hospitalname
    ) AS MaxCounts
)
ORDER BY test_count DESC;

SELECT D.Did, D.name AS doctor_name, D.specialization, D.hospitalname, COUNT(*) AS test_count
FROM DOCTOR D
JOIN PTEST PT ON D.Did = PT.Did AND D.hospitalname = PT.hospitalname
WHERE PT.testdate BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY D.Did, D.name, D.specialization, D.hospitalname
HAVING COUNT(*) = (
    SELECT MIN(test_count)
    FROM (
        SELECT COUNT(*) AS test_count
        FROM DOCTOR D
        JOIN PTEST PT ON D.Did = PT.Did AND D.hospitalname = PT.hospitalname
        WHERE PT.testdate BETWEEN '2024-08-01' AND '2024-08-31'
        GROUP BY D.Did, D.name, D.specialization, D.hospitalname
    ) AS MinCounts
)
ORDER BY test_count ASC;

SELECT D.Did AS doctor_id, SUM(PT.testamt) AS total_amount, 'TEST' AS Test_or_Scan
FROM PTEST PT
JOIN DOCTOR D ON PT.Did = D.Did
GROUP BY D.Did UNION ALL
SELECT D.Did AS doctor_id, SUM(S.scanamt) AS 
total_amount, 'SCAN' AS Test_or_Scan
FROM PSCAN PS
JOIN DOCTOR D ON PS.Did = D.Did
JOIN SCAN S ON PS.scanname = S.scanname
GROUP BY D.Did;